First attempts with in-place upgrade from MySQL 5.7 to 8.0

I have previously written, that in-place upgrade is not possible from MySQL 8.0.4 to 8.0.11, but it's officially supported between GA releases (but not through MySQL Installer - see #90619 and more likely #79315). So today I've made my first attempts to migrate my MySQL 5.7 server to MySQL 8.0 by copying data and configuration in a Slackware virtual machine. Let's start...

• First attempt:

2018-05-30T16:42:28.485076Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 18078
2018-05-30T16:42:28.727435Z 1 [ERROR] [MY-012526] [InnoDB] InnoDB: Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.22. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html
2018-05-30T16:42:28.727509Z 1 [ERROR] [MY-012930] [InnoDB] InnoDB: Plugin initialization aborted with error Generic error.
2018-05-30T16:42:29.228125Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2018-05-30T16:42:29.228520Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-05-30T16:42:29.228554Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-05-30T16:42:29.235976Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.11)  Source distribution.

Well, I really took my chances by just copying the data directory of the running MySQL 5.7 server :-)

Solution: Make a clean copy of the data directory when the server is stopped.

• Second attempt:

2018-05-30T16:53:06.475573Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 18236
2018-05-30T16:53:08.332393Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
InnoDB MEMCACHED: Memcached uses atomic increment
 InnoDB_Memcached: Unable to open table 'innodb_memcache/containers'
 InnoDB_Memcached: Please create config table'containers' in database 'innodb_memcache' by running 'innodb_memcached_config.sql. error Table not found'
Failed to initialize instance. Error code: 13
2018-05-30T16:53:13.256478Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-05-30T16:53:13.257272Z 0 [ERROR] [MY-010262] [Server] Can't start server: Bind on TCP/IP port: Address already in use
2018-05-30T16:53:13.257283Z 0 [ERROR] [MY-010257] [Server] Do you already have another mysqld server running on port: 3306 ?
2018-05-30T16:53:13.257298Z 0 [ERROR] [MY-010119] [Server] Aborting
 InnoDB_Memcached: Memcached plugin is still initializing. Can't shut down it.
2018-05-30T16:53:29.161133Z 0 [Warning] [MY-011068] [Server] The syntax 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead.
2018-05-30T16:53:30.304586Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.11)  Source distribution.

Sure, I have another MySQL 8.0 server listening on port 3306, so let's fix the configuration.

Solution: Change port in configuration file.

• Third attempt:

2018-05-30T17:04:57.144549Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 18571
2018-05-30T17:04:57.523116Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2018-05-30T17:04:57.630947Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-05-30T17:04:57.633668Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2018-05-30T17:04:57.633699Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
2018-05-30T17:04:57.633776Z 0 [ERROR] [MY-011071] [Server] unknown option '--secure-auth'
2018-05-30T17:04:57.633787Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.
2018-05-30T17:04:57.633840Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-05-30T17:04:58.636827Z 0 [Warning] [MY-011068] [Server] The syntax 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead.

Yeah, data dictionary is not yet upgraded, but for this I need a running server and there is also error about unknown configuration option.

Solution: Remove secure-auth option from configuration file as it was removed in 8.0.3 (see also its release notes).

• Fourth attempt:

2018-05-30T17:06:21.153201Z 0 [ERROR] [MY-011071] [Server] unknown variable 'max_tmp_tables=64'

Solution: Same case as above. Remove max_tmp_tables option from configuration file.

• Fifth attempt:

2018-05-30T17:10:36.750614Z 0 [ERROR] [MY-011071] [Server] unknown variable 'query_cache_type=1'

Yep, I forgot that query cache is removed too...

Solution: Remove query_cache_type and query_cache_size options from configuration file as Query Cache was removed in MySQL 8.

• Sixth attempt:

2018-05-30T17:11:18.941821Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 18803
2018-05-30T17:11:19.333818Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-05-30T17:11:19.335972Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2018-05-30T17:11:19.336010Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
2018-05-30T17:11:19.348988Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.user].
2018-05-30T17:11:19.349014Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.db].
2018-05-30T17:11:19.349020Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.tables_priv].
2018-05-30T17:11:19.349026Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.columns_priv].
2018-05-30T17:11:19.349031Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.procs_priv].
2018-05-30T17:11:19.349035Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.proxies_priv].
2018-05-30T17:11:19.352944Z 0 [ERROR] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 49, found 45. The table is probably corrupted
2018-05-30T17:11:19.352980Z 0 [Warning] [MY-010966] [Server] ACL table mysql.role_edges missing. Some operations may fail.
2018-05-30T17:11:19.352987Z 0 [Warning] [MY-010966] [Server] ACL table mysql.default_roles missing. Some operations may fail.
2018-05-30T17:11:19.352992Z 0 [Warning] [MY-010966] [Server] ACL table mysql.global_grants missing. Some operations may fail.
2018-05-30T17:11:19.352997Z 0 [Warning] [MY-010966] [Server] ACL table mysql.password_history missing. Some operations may fail.
2018-05-30T17:11:19.353420Z 0 [ERROR] [MY-010965] [Server] Missing system table mysql.global_grants; please run mysql_upgrade to create it.
2018-05-30T17:11:19.365069Z 0 [Warning] [MY-010727] [Server] System table 'func' is expected to be transactional.
2018-05-30T17:11:19.366847Z 0 [Warning] [MY-010405] [Repl] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2018-05-30T17:11:19.366874Z 0 [ERROR] [MY-010422] [Repl] Error in checking mysql.slave_master_info repository info type of TABLE.
2018-05-30T17:11:19.368238Z 0 [ERROR] [MY-010415] [Repl] Error creating master info: Error checking repositories.
2018-05-30T17:11:19.368273Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2018-05-30T17:11:19.368280Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
2018-05-30T17:11:19.373821Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.11'  socket: '/var/lib/mysql/mysqld57.sock'  port: 3316  Source distribution.

Success! The server is already running, but apparently needs upgrade as system tables are either missing or still in MyISAM. However, should not this be done automatically? At least I'm getting this impression when reading Step 8 for In-Place Upgrade in the manual. Anyway, step 9 is to run mysql_upgrade, so after running the command the errors in the log are gone.

Of course, I'm was playing here, but at least I now have a configuration file ready for 8.0 and have validated the upgrading procedure for my use case. However, I have to check also my applications compatibility, so the real upgrade would happen later.

P.S. A nice way to check for configuration problems is described in How to check MySQL Config files by Peter Zaitsev.

MariaDB 10.3 adds even more development features than MySQL 8.0

Last Friday (May 25-th) MariaDB 10.3.7 was released as stable (General Availability). I was finally been able to check the release notes and (not surprisingly) I found some features that we're still missing in MySQL (including the released a month ago 8.0.11 GA for which I wrote). I'd like to highlight the following notable changes related to development (of which I'm interested the most):

• System-versioned tables (a.k.a. AS OF);
• Aggregate stored functions (CREATE AGGREGATE FUNCTION and FETCH GROUP NEXT ROW inside loops);
• FOR loop (FOR ... DO ... END FOR statement and FOR ... LOOP ... END LOOP statement in sql_mode=ORACLE, see MDEV-10581 and MDEV-12098);
• Sequences (as alternative to AUTO INCREMENT for "more control of how the numbers are generated");
• INTERSECT and EXCEPT set operations;
• ROW data type (for variables representing group of fields - i.e. rows or records - in stored routines);
• TYPE OF and ROW TYPE OF anchored data types (for variables representing columns or rows in stored routines);
• DELETE and UPDATE statements based on a subquery on the same table;
• Stored packages (with Oracle-like CREATE PACKAGE and CREATE PACKAGE BODY statements in sql_mode=ORACLE).

Wow. They've been working hard, that's for sure. I'll have yet to test all these new features myself, but I'm quite excited to see them available in "one of the most popular database servers". Unfortunately, neither one of these is available in MySQL. Here below is my list of what's still missing in MySQL (and MariaDB):

Features		MariaDB	MySQL	Standard
SQL	Modify table data based on subquery on the same table	10.3.2 (2017-11-09)	n/a
	EXCEPT or MINUS and INTERSECT set operators	10.3.0 (2017-04-16)	n/a	SQL:2003
	Temporal queries (AS OF)	10.3.4 (2018-01-18)	n/a	SQL:2011
DDL	CHECK constraints	10.2.1 (2016-07-04)	n/a	SQL:1999
	Partitioning of tables with foreign keys	n/a	n/a
	Temporal tables (WITH SYSTEM VERSIONING)	10.3.4 (2018-01-18)	n/a	SQL:2011
	CREATE OR REPLACE for tables, triggers and stored routines	10.0.8 (2014-02-10), 10.1.4 (2015-04-13) and 10.1.3 (2015-03-02)	n/a
	Enable/disable for triggers	n/a	n/a
	Sequences	10.3.0 (2017-04-16)	n/a	SQL:2003
	A working schema rename (i.e. RENAME {DATABASE | SCHEMA}) statement	n/a	n/a
Views	Saving of the exact view statement in the server	n/a	n/a	n/a
	Fully updatable views with INSTEAD OF triggers	n/a	n/a	SQL:2008
	Materialized views	n/a	n/a
Stored routines	Default arguments	n/a	n/a
	FOR loop	10.3.3 (2017-12-23)	n/a
	Anonymous code blocks (or compound statements outside routines)	10.1.1 (2014-10-17)	n/a
	Records, column and row anchored types	10.3.0 (2017-04-16)	n/a
	Stored modules or packages	10.3.5 (2018-02-26)	n/a
	Statement and schema level triggers	n/a	n/a
	Debugging facilities	n/a	n/a	n/a
	External routines in JavaScript, Perl, PHP, etc.	n/a	n/a

It's quite apparent from the above table that MySQL has more gray spots than MariaDB. I've added also the related SQL standard where applicable, so in this regards MariaDB has broader standards support. It seems to me than in its latest stable version MariaDB has implemented more "Oracle like" features, than MySQL which is developed by Oracle.

So is it worth it changing the database? I believe I'll continue using both MySQL and MariaDB (which is default database in most Linux distributions) and the reasons for this are that these are becoming more and more different with the time. There are development features in MySQL (e.g. JSON data type, the Document store), which do not exist in MariaDB and vice versa. I'm still quite used using MySQL Workbench for daily work with MySQL databases, but the support for MariaDB in it is fading and unless MySQL adds the same SQL features, I doubt the new development functionalities would be usable from the Workbench, which impacts my work.

MySQL Shell 8 and automatically generated document IDs

Why I'm getting error ERROR: 5115: Document is missing a required field in MySQL Shell 8.0.11 connected to MySQL Server 5.7.22? Consider the following session in which I'm  first connecting to the server, creating a new schema, then a collection inside the schema and finally trying to create a document without explicitly providing identifier:

MySQL Shell 8.0.11

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.


MySQL JS> \connect root@server
Creating a session to 'root@server'
Enter password: ************
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1027578 (X protocol)
Server version: 5.7.22-log Source distribution
No default schema selected; type \use <schema> to set one.

MySQL [server+ ssl] JS> session.createSchema('test')
<schema:test>

MySQL [server+ ssl] JS> \use test
Default schema `test` accessible through db.

MySQL [server+ ssl/test] JS> per_coll = db.createCollection('persons')
<collection:persons>

MySQL [server+ ssl/test] JS> per_coll.add({name:'Georgi'})
ERROR: 5115: Document is missing a required field

MySQL [server+ ssl/test] JS> \py
Switching to Python mode...

MySQL [server+ ssl/test] Py> pcol = db.get_collection('persons')

MySQL [server+ ssl/test] Py> pcol.add({'name':'Georgi'})
ERROR: 5115: Document is missing a required field

In this case MySQL Shell should generate the identifier as specified in the manual (see MySQL 5.7 Reference manual, chapter 19.4.4.2 Add Documents), where it's written:

Each document requires an identifier field called _id. The value of the _id field must be unique among all documents in the same collection. If the document passed to the add() method does not contain the _id field, MySQL Shell automatically inserts a field into the document and sets the value to a generated universal unique identifier (UUID).

When connected to MySQL 8.0 Server there is no such problem:

MySQL Shell 8.0.11

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.


MySQL JS> \connect root@mysql8
Creating a session to 'root@mysql8'
Enter password: ************
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 6646 (X protocol)
Server version: 8.0.11 Source distribution
No default schema selected; type \use <schema> to set one.

MySQL [mysql8+ ssl] JS> session.createSchema('test')
<schema:test>

MySQL [mysql8+ ssl] JS> \use test
Default schema `test` accessible through db.

MySQL [mysql8+ ssl/test] JS> per_coll = db.createCollection('persons')
<collection:persons>

MySQL [mysql8+ ssl/test] JS> per_coll.add({name:'Georgi'})
Query OK, 1 item affected (0.2450 sec)

MySQL [192.168.79.46+ ssl/test2] JS> per_coll.find()
[
    {
        "_id": "00005adc69630000000000000002",
        "name": "Georgi"
    }
]
1 document in set (0.2035 sec)

I also tried connecting MySQL Server 5.7 with MySQL Shell 1.0 and again no problem:

$ mysqlsh
MySQL Shell 1.0.11

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.

Currently in Python mode. Use \sql to switch to SQL mode and execute queries.
mysql-py> \connect root@localhost
Creating a Session to 'root@localhost'
Enter password:
Your MySQL connection id is 1028442 (X protocol)
Server version: 5.7.22-log Source distribution
No default schema selected; type \use <schema> to set one.
mysql-py> session.create_schema('test')
<schema:test>
mysql-py> \use test
Schema `test` accessible through db.
mysql-py> pcol = db.create_collection('persons')
mysql-py> pcol.add({'name':'Georgi'})
Query OK, 1 item affected (0.16 sec)
mysql-py> pcol.find()
[
    {
        "_id": "aa0960462c58e811b27610feed07afc3",
        "name": "Georgi"
    }
]
1 document in set (0.00 sec)

The post MySQL Document Store Document IDs. by Dave Stokes is the top (and in fact only) result in Google for the error (when searching for exact match with double quotes). It points to chapter 5.1.1 Understanding Document IDs of X DevAPI User Guide, in which it's written:

X DevAPI relies on server based document ID generation, added in MySQL version 8.0.11, which results in sequentially increasing document IDs across all clients.
...
Whenever an _id field value is not present in an inserted document, the server generates an _id value.

Which quite explains the issue. Apparently, MySQL Shell 8.0.11 expects that the server would generate the identifier (for  _id column) without considering whether the Server is 5.7 or 8.0, which for me is a bug to report later.

Update 2018-05-16: Oracle verified my bug report (see bug 90876). Hope it would be fixed.

Migration to Git and GitHub

Several years ago I decided to migrate all my personal projects to a distributed version control system (DVCS), but simply didn't found the time. Nowadays Git is de facto standard for version control, so I decided to migrate to Git and put all my personal projects to GitHub. So this year I would be gradually migrating my projects, starting with the least active ones. Most of them are still on CVS, which makes the migration more complex given CVS and Git are quite different in many aspects, but also because for me it's important to keep the history of my modifications (i.e. I do not want just to copy the source and initialize and new Git repository inside). Wish me luck and cross your fingers for me ;-)