Specifically, the idea was the there is no need to run mysql_upgrade from each intermediate release (10.0, 10.1, 10.2, 10.3 and 10.4) if your goal is to eventually upgrade from MariaDB (or MySQL) 5.5 to MariaDB 10.4, and that mysql_upgrade is designed and coded in a way to take all possible differences for the version it is coming from into account. It supposedly includes and does all intermediate changes needed for all versions since the first release of the tool, and skips errors (that may appear only if some specific change was already done and is not strictly needed). He added a (new) KB article explaining this also.
This was a kind of surprise for me, as until now MySQL manual, for example, clearly says:
"Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.5 to 5.7 is not supported."As a support engineer I always highlighted the need to upgrade step by step, one major version at a time, and check how the entire setup and, most importantly, applications work after each step. I prefer to know from the very beginning in what exact version the problem appeared during upgrade. The problem may be of any kind, and of them corrupted data and different execution plan for some important query are probably the worst if we do not find them early...
But without such checks and in cases when Galera or async replication is not involved and we do not know for sure that there is incompatible change introduced that is NOT fixed by mysql_upgrade, upgrading step by step may, indeed, be considered mostly a waste of time.
I do not trust statements, even if they come from Monty himself, without checking them. So I tried to search MariaDB and MySQL bugs databases for still active bugs that happens when major version is skipped (but mysql_upgrade from the target final version is properly executed and returned no errors). I found no such bugs so far.
Next steps before I agree with the idea of skipping major versions as a valid and supported way for some cases (when proper applications testing is not possible in the process for whatever reason and no replication and HA expectations are directly involved) are to try such upgrades for my testing instances and check what mysql_upgrade really does.
Today, for the purpose of this blog post, I tried (again, as I did such things in the past) to upgrade MySQL 5.5.54 I have at hand among numerous other versions directly to MariaDB 10.4.13 (current from GitHub), and check what happens. This is a very basic test and my hope, honestly, was that it may show some problem, as mysql.user implementation details, for example, are very different.
So, I started with initializing new 5.5 data directory (to clean up 10.4 databases I had there based on previous upgrade tests with more real data):
openxs@ao756:~/dbs/5.5$ rm -rf data/Then to start MySQL 5.5 and create a simple table:
openxs@ao756:~/dbs/5.5$ scripts/mysql_install_db --no-defaults
Installing MySQL system tables...
200418 12:59:37 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
200418 12:59:37 [Note] ./bin/mysqld (mysqld 5.5.54) starting as process 28166 ...
OK
Filling help tables...
200418 12:59:39 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
200418 12:59:39 [Note] ./bin/mysqld (mysqld 5.5.54) starting as process 28172 ...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h ao756 password 'new-password'
Alternatively you can run:
./bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
openxs@ao756:~/dbs/5.5$ ls data
mysql performance_schema test
openxs@ao756:~/dbs/5.5$ bin/mysqld_safe --no-defaults --port=3309 --socket=/tmp/mysql55.sock &As you can see, I added some data and checked that there are no mysql.innodb% tables (that store InnoDB persistent statistics) in 5.5.54. Then I proceeded to shutdown (without setting innodb_fast_shutdown = 0 explicitly, a mistake that could cost me a lot of troubles if the server was under load) and started 10.4.13 with the same datadir. As simple as that:
[1] 28615
openxs@ao756:~/dbs/5.5$ 200418 13:00:22 mysqld_safe Logging to './data/ao756.err'.
200418 13:00:22 mysqld_safe Starting mysqld daemon with databases from ./data
openxs@ao756:~/dbs/5.5$ bin/mysql -uroot test --socket=/tmp/mysql55.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.54 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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 '\h' for help. Type '\c' to clear the current input statement.
mysql> create table t1(id int auto_increment primary key, c1 timestamp default current_timestamp(), c2 varchar(20));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into t1(c2) values ('abc'), ('def');Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+---------------------+------+
| id | c1 | c2 |
+----+---------------------+------+
| 1 | 2020-04-18 13:02:47 | abc |
| 2 | 2020-04-18 13:02:47 | def |
+----+---------------------+------+
2 rows in set (0.00 sec)
mysql> show tables from mysql like 'innodb%';
Empty set (0.00 sec)
mysql> exit
Bye
openxs@ao756:~/dbs/5.5$ bin/mysqladmin -uroot shutdown --socket=/tmp/mysql55.sockNote that I just started 10.4 with default settings (and general log enables, so that I know what SQL statements were executed), and there is no obvious problem to start. I can even read the data from the table, even though InnoDB persistent statistics tables do not exist.
200418 13:03:37 mysqld_safe mysqld from pid file ./data/ao756.pid ended
[1]+ Done bin/mysqld_safe --no-defaults --port=3309 --socket=/tmp/mysql55.sock
openxs@ao756:~/dbs/5.5$ cd ../maria10.4/
openxs@ao756:~/dbs/maria10.4$ bin/mysqld_safe --no-defaults --datadir=/home/openxs/dbs/5.5/data --general_log=1 --port=3309 --socket=/tmp/mariadb.sock &
[1] 30832
openxs@ao756:~/dbs/maria10.4$ 200418 13:04:50 mysqld_safe Logging to '/home/openxs/dbs/5.5/data/ao756.err'.
200418 13:04:50 mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/5.5/data
openxs@ao756:~/dbs/maria10.4$ bin/mysql -uroot test --socket=/tmp/mariadb.sock
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.13-MariaDB-log Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [test]> show tables from mysql like 'innodb%';
Empty set (0,001 sec)
MariaDB [test]> select * from t1;+----+---------------------+------+
| id | c1 | c2 |
+----+---------------------+------+
| 1 | 2020-04-18 13:02:47 | abc |
| 2 | 2020-04-18 13:02:47 | def |
+----+---------------------+------+
2 rows in set (0,001 sec)
MariaDB [test]> show variables like 'general%';
+------------------+-----------+
| Variable_name | Value |
+------------------+-----------+
| general_log | ON |
| general_log_file | ao756.log |
+------------------+-----------+
2 rows in set (0,003 sec)
MariaDB [test]> exit
Bye
Surely I had plenty of suspicious messages in the error log:
openxs@ao756:~/dbs/maria10.4$ tail -200 ~/dbs/5.5/data/ao756.errThere are notes, warnings and errors to pay attention to! Every other day I get error log from some customer with messages like:
...
200418 13:00:25 [Note] ./bin/mysqld: ready for connections.
Version: '5.5.54' socket: '/tmp/mysql55.sock' port: 3309 MySQL Community Server (GPL)
200418 13:03:35 [Note] ./bin/mysqld: Normal shutdown
200418 13:03:35 [Note] Event Scheduler: Purging the queue. 0 events
200418 13:03:35 InnoDB: Starting shutdown...
200418 13:03:37 InnoDB: Shutdown completed; log sequence number 1599156
200418 13:03:37 [Note] ./bin/mysqld: Shutdown complete
200418 13:03:37 mysqld_safe mysqld from pid file ./data/ao756.pid ended
2020-04-18 13:04:51 0 [Note] InnoDB: Using Linux native AIO
2020-04-18 13:04:51 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-04-18 13:04:51 0 [Note] InnoDB: Uses event mutexes
2020-04-18 13:04:51 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-04-18 13:04:51 0 [Note] InnoDB: Number of pools: 1
2020-04-18 13:04:51 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-04-18 13:04:51 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
2020-04-18 13:04:51 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-04-18 13:04:51 0 [Note] InnoDB: Completed initialization of buffer pool
2020-04-18 13:04:51 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-04-18 13:04:51 0 [Note] InnoDB: Upgrading redo log: 2*50331648 bytes; LSN=1599156
2020-04-18 13:04:51 0 [Note] InnoDB: Starting to delete and rewrite log files.
2020-04-18 13:04:51 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes
2020-04-18 13:04:51 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes
2020-04-18 13:04:51 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2020-04-18 13:04:51 0 [Note] InnoDB: New log files created, LSN=1599156
2020-04-18 13:04:51 0 [Note] InnoDB: Creating tablespace and datafile system tables.
2020-04-18 13:04:51 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-04-18 13:04:51 0 [Note] InnoDB: Creating sys_virtual system tables.
2020-04-18 13:04:51 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-04-18 13:04:51 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-04-18 13:04:51 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-04-18 13:04:51 0 [Note] InnoDB: Waiting for purge to start
2020-04-18 13:04:51 0 [Note] InnoDB: 10.4.13 started; log sequence number 1599156; transaction id 777
2020-04-18 13:04:51 0 [Note] InnoDB: Loading buffer pool(s) from /home/openxs/dbs/5.5/data/ib_buffer_pool
2020-04-18 13:04:51 0 [Note] InnoDB: Cannot open '/home/openxs/dbs/5.5/data/ib_buffer_pool' for reading: No such file or directory
2020-04-18 13:04:51 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-04-18 13:04:51 0 [Note] Server socket created on IP: '::'.
2020-04-18 13:04:51 0 [ERROR] Missing system table mysql.roles_mapping; please run mysql_upgrade to create it
2020-04-18 13:04:51 0 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','N...
2020-04-18 13:04:51 0 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2020-04-18 13:04:51 6 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
2020-04-18 13:04:52 0 [Note] Reading of all Master_info entries succeeded
2020-04-18 13:04:52 0 [Note] Added new Master_info '' to hash table
2020-04-18 13:04:52 0 [Note] /home/openxs/dbs/maria10.4/bin/mysqld: ready for connections.
Version: '10.4.13-MariaDB-log' socket: '/tmp/mariadb.sock' port: 3309 Source distribution
2020-04-18 13:05:11 8 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
2020-04-18 13:05:11 8 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.and others similar to those above, and it means that they had never run mysql_upgrade at all after some major version upgrade!
Next step was to run mysql_upgrade as some of the messages suggest:
openxs@ao756:~/dbs/maria10.4$ bin/mysql_upgrade -uroot test --socket=/tmp/mariadb.sockThe output is not that long for the case of only one additional table created, but the process took few seconds of time. I have missing tables now:
MariaDB upgrade detected
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Upgrading from a version before MariaDB-10.1
Phase 2/7: Installing used storage engines
Checking for tables with unknown storage engine
Phase 3/7: Fixing views from mysql
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
information_schema
performance_schema
test
test.t1 OK
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK
openxs@ao756:~/dbs/maria10.4$ bin/mysql --no-defaults -uroot --socket=/tmp/mariadb.sockThe final upgrade step would be to restart and check the error log after restart:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 24
Server version: 10.4.13-MariaDB-log Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show tables from mysql like 'innodb%';
+---------------------------+
| Tables_in_mysql (innodb%) |
+---------------------------+
| innodb_index_stats |
| innodb_table_stats |
+---------------------------+
2 rows in set (0,001 sec)
openxs@ao756:~/dbs/maria10.4$ bin/mysql --no-defaults -uroot --socket=/tmp/mariadb.sockTo confirm that running mysql_upgrade really helped, let me check the error log content since last startup:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.13-MariaDB Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from t1;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [test]> select * from t1;
+----+---------------------+------+
| id | c1 | c2 |
+----+---------------------+------+
| 1 | 2020-04-18 13:02:47 | abc |
| 2 | 2020-04-18 13:02:47 | def |
+----+---------------------+------+
2 rows in set (0,002 sec)
openxs@ao756:~/dbs/maria10.4$ tail -80 ~/dbs/5.5/data/ao756.errI see just notes, no errors or warnings above after the restart. It means I can continue with applications testing and consider upgrade successful so far. Do NOT assume you are done with upgrade until you see such a clean startup messages log!
...
2020-04-18 13:04:52 0 [Note] /home/openxs/dbs/maria10.4/bin/mysqld: ready for connections.
Version: '10.4.13-MariaDB-log' socket: '/tmp/mariadb.sock' port: 3309 Source distribution
2020-04-18 13:05:11 8 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
2020-04-18 15:21:01 0 [Note] /home/openxs/dbs/maria10.4/bin/mysqld (initiated by: root[root] @ localhost []): Normal shutdown
2020-04-18 15:21:01 0 [Note] Event Scheduler: Purging the queue. 0 events
2020-04-18 15:21:01 0 [Note] InnoDB: FTS optimize thread exiting.
2020-04-18 15:21:01 0 [Note] InnoDB: Starting shutdown...
2020-04-18 15:21:01 0 [Note] InnoDB: Dumping buffer pool(s) to /home/openxs/dbs/5.5/data/ib_buffer_pool
2020-04-18 15:21:01 0 [Note] InnoDB: Buffer pool(s) dump completed at 200418 15:21:01
2020-04-18 15:21:03 0 [Note] InnoDB: Shutdown completed; log sequence number 1617395; transaction id 791
2020-04-18 15:21:03 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-04-18 15:21:03 0 [Note] /home/openxs/dbs/maria10.4/bin/mysqld: Shutdown complete
2020-04-18 15:21:13 0 [Note] InnoDB: Using Linux native AIO
2020-04-18 15:21:13 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-04-18 15:21:13 0 [Note] InnoDB: Uses event mutexes
2020-04-18 15:21:13 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-04-18 15:21:13 0 [Note] InnoDB: Number of pools: 1
2020-04-18 15:21:13 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-04-18 15:21:13 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
2020-04-18 15:21:13 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-04-18 15:21:13 0 [Note] InnoDB: Completed initialization of buffer pool
2020-04-18 15:21:13 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-04-18 15:21:13 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-04-18 15:21:13 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-04-18 15:21:13 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-04-18 15:21:13 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-04-18 15:21:13 0 [Note] InnoDB: Waiting for purge to start
2020-04-18 15:21:13 0 [Note] InnoDB: 10.4.13 started; log sequence number 1617395; transaction id 791
2020-04-18 15:21:13 0 [Note] InnoDB: Loading buffer pool(s) from /home/openxs/dbs/5.5/data/ib_buffer_pool
2020-04-18 15:21:13 0 [Note] InnoDB: Buffer pool(s) load completed at 200418 15:21:13
2020-04-18 15:21:13 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-04-18 15:21:13 0 [Note] Server socket created on IP: '::'.
2020-04-18 15:21:13 0 [Note] Reading of all Master_info entries succeeded
2020-04-18 15:21:13 0 [Note] Added new Master_info '' to hash table
2020-04-18 15:21:13 0 [Note] /home/openxs/dbs/maria10.4/bin/mysqld: ready for connections.
Version: '10.4.13-MariaDB' socket: '/tmp/mariadb.sock' port: 3310 Source distribution
openxs@ao756:~/dbs/maria10.4$
Now let me check the general query log generated:
openxs@ao756:~/dbs/maria10.4$ cat ~/dbs/5.5/data/ao756.log | moreConnection 8 above was my user session. Starting from 9, we see queries executed by mysql_upgrade (and maybe other utilities it may call, more on them in the next part.. We see thjat it gets the datadir and current serverr version, and in all cases by default it disables writing changes to the binary log or replicating them to other Galera nodes, if any. Let me continue:
/home/openxs/dbs/maria10.4/bin/mysqld, Version: 10.4.13-MariaDB-log (Source dist
ribution). started with:
Tcp port: 3309 Unix socket: /tmp/mariadb.sock
Time Id Command Argument
200418 13:05:11 8 Connect root@localhost as anonymous on test
8 Query show databases
8 Query show tables
8 Field List t1
8 Query select @@version_comment limit 1
200418 13:05:14 8 Query show tables from mysql like 'innodb%'
200418 13:05:19 8 Query select * from t1
200418 13:05:33 8 Query show variables like 'general%'
200418 13:05:39 8 Quit
200418 13:05:49 9 Connect root@localhost as anonymous on mysql
9 Query SET SQL_LOG_BIN=0, WSREP_ON=OFF
9 Query show variables like 'datadir'
9 Quit
10 Connect root@localhost as anonymous on mysql
10 Query SET SQL_LOG_BIN=0, WSREP_ON=OFF
10 Query show variables like 'version'
10 Quit
11 Connect root@localhost as anonymous on mysql
11 Query SET SQL_LOG_BIN=0, WSREP_ON=OFF 11 Query show create table mysql.event
11 Quit
12 Connect root@localhost as anonymous onWe see running CHECK TABLE ... FOR UPGRADE for each table that exists in the mysql database and then checking structure of the mysql.user table and looking for the list of unknown storage engines.
12 Query SET WSREP_ON=0
12 Query SET SQL_LOG_BIN=0
12 Init DB mysql
12 Query SHOW /*!50002 FULL*/ TABLES
12 Query CHECK TABLE `columns_priv` FOR UPGRADE
12 Query CHECK TABLE `db` FOR UPGRADE
12 Query CHECK TABLE `event` FOR UPGRADE
...
12 Query CHECK TABLE `user` FOR UPGRADE
12 Quit
13 Connect root@localhost as anonymous on mysql
13 Query SET SQL_LOG_BIN=0, WSREP_ON=OFF
13 Query show create table mysql.user
13 Quit
14 Connect root@localhost as anonymous on mysql
14 Query SET SQL_LOG_BIN=0, WSREP_ON=OFF
14 Query SELECT DISTINCT LOWER(engine) AS c1 FROM information_schema.tables WHERE table_comment LIKE 'Unknown storage engine%' ORDER BY c1
14 Quit
Then we get lists of tables in each real database and proceed with changes to table structures in the mysql database:
15 Connect root@localhost as anonymous onAmong other things we switch tables to Aria engine as this is one of the ideas of MariaDB 10.4. We also care about proper authentication plugin for the case of upgrade from MySQL:
15 Query SET WSREP_ON=0
15 Query SET SQL_LOG_BIN=0
15 Query SHOW DATABASES
15 Init DB mysql
15 Query SHOW /*!50002 FULL*/ TABLES 15 Init DB test
15 Query SHOW /*!50002 FULL*/ TABLES
15 Quit
200418 13:05:50 16 Connect root@localhost as anonymous on mysql
16 Query SET SQL_LOG_BIN=0, WSREP_ON=OFF
16 Query set sql_mode=''
16 Query set storage_engine=Aria
16 Query set enforce_storage_engine=NULL
16 Query set @have_innodb= (select count(engine) from inf
ormation_schema.engines where engine='INNODB' and support != 'NO')
16 Query ALTER TABLE user add File_priv enum('N','Y') COL
LATE utf8_general_ci DEFAULT 'N' NOT NULL
16 Query SET @hadGrantPriv:=0
16 Query SELECT @hadGrantPriv:=1 FROM user WHERE Grant_pr
iv IS NOT NULL
16 Query ALTER TABLE user add Grant_priv enum('N','Y') CO
LLATE utf8_general_ci DEFAULT 'N' NOT NULL,
add References_priv enum('N','Y') COLLATE utf8_general_ci DEFAU
LT 'N' NOT NULL,
add Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N
' NOT NULL,
add Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N
' NOT NULL
16 Query ALTER TABLE db add Grant_priv enum('N','Y') COLL
ATE utf8_general_ci DEFAULT 'N' NOT NULL,
add References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT
'N' NOT NULL,
add Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N'
NOT NULL,
add Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N'
NOT NULL
16 Query UPDATE user SET Grant_priv=File_priv,References_
priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0
16 Query UPDATE db SET References_priv=Create_priv,Index_
priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0
16 Query ALTER TABLE user
ADD ssl_type enum('','ANY','X509', 'SPECIFIED') DEFAULT '' NOT NULL,
ADD ssl_cipher BLOB NOT NULL,
ADD x509_issuer BLOB NOT NULL,
ADD x509_subject BLOB NOT NULL
16 Query ALTER TABLE user MODIFY ssl_type enum('','ANY','
X509', 'SPECIFIED') DEFAULT '' NOT NULL
16 Query ALTER TABLE tables_priv
ADD KEY Grantor (Grantor)
16 Query ALTER TABLE tables_priv
MODIFY Host char(60) NOT NULL default '',
MODIFY Db char(64) NOT NULL default '',
MODIFY User char(80) binary NOT NULL default '',
MODIFY Table_name char(64) NOT NULL default '',
MODIFY Grantor char(141) COLLATE utf8_bin NOT NULL default '',
ENGINE=Aria,
CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin
16 Query ALTER TABLE tables_priv
MODIFY Column_priv set('Select','Insert','Update','References')
COLLATE utf8_general_ci DEFAULT '' NOT NULL,
MODIFY Table_priv set('Select','Insert','Update','Delete','Create',
'Drop','Grant','References','Index','Alter',
'Create View','Show view','Trigger','Delete versioning r
ows')
COLLATE utf8_general_ci DEFAULT '' NOT NULL,
COMMENT='Table privileges'
200418 13:05:51 16 Query ALTER TABLE columns_priv
CHANGE Type Column_priv set('Select','Insert','Update','References')
COLLATE utf8_general_ci DEFAULT '' NOT NULL
16 Query ALTER TABLE columns_priv
MODIFY Host char(60) NOT NULL default '',
MODIFY Db char(64) NOT NULL default '',
MODIFY User char(80) binary NOT NULL default '',
MODIFY Table_name char(64) NOT NULL default '',
MODIFY Column_name char(64) NOT NULL default '',
ENGINE=Aria,
CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin,
COMMENT='Column privileges'
16 Query ALTER TABLE columns_priv
MODIFY Column_priv set('Select','Insert','Update','References')
COLLATE utf8_general_ci DEFAULT '' NOT NULL
200418 13:05:52 16 Query ALTER TABLE func add type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL
16 Query SET @hadShowDbPriv:=0
16 Query SELECT @hadShowDbPriv:=1 FROM user WHERE Show_db_priv IS NOT NULL
16 Query ALTER TABLE user
ADD Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_priv,
ADD Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER
Show_db_priv,
ADD Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT
NULL AFTER Super_priv,
ADD Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL
AFTER Create_tmp_table_priv,
...
...
16 Query ALTER TABLE user
ADD Password char(41) character set latin1 collate latin1_bin NOT NULL default
'' AFTER User
16 Query UPDATE user
SET plugin='unix_socket' WHERE plugin='auth_socket'
16 Query DELETE FROM plugin
WHERE name='auth_socket'
...
A lot of changes are done by that connection 16, they are all coming mostly from one script that we'll check in the next part.
There are statements to create the tables needed as well:
...'password_lifetime', ifnull(password_lifetime, -1),
CREATE TABLE IF NOT EXISTS global_priv (Host char(60) binary DEFAULT '', User char(80) binary DEFAULT '', Priv JSON NOT NULL DEFAULT '{}' CHECK(JSON_VALID(Priv)), PRIMARY KEY Host (Host,User)) engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'
SELECT Host, User, JSON_COMPACT(JSON_OBJECT('access',
1*('Y'=Select_priv)+
2*('Y'=Insert_priv)+
4*('Y'=Update_priv)+
8*('Y'=Delete_priv)+
16*('Y'=Create_priv)+
32*('Y'=Drop_priv)+
64*('Y'=Reload_priv)+
128*('Y'=Shutdown_priv)+
256*('Y'=Process_priv)+
512*('Y'=File_priv)+
1024*('Y'=Grant_priv)+
2048*('Y'=References_priv)+
4096*('Y'=Index_priv)+
8192*('Y'=Alter_priv)+
16384*('Y'=Show_db_priv)+
32768*('Y'=Super_priv)+
65536*('Y'=Create_tmp_table_priv)+
131072*('Y'=Lock_tables_priv)+
262144*('Y'=Execute_priv)+
524288*('Y'=Repl_slave_priv)+
1048576*('Y'=Repl_client_priv)+
2097152*('Y'=Create_view_priv)+
4194304*('Y'=Show_view_priv)+
8388608*('Y'=Create_routine_priv)+
16777216*('Y'=Alter_routine_priv)+
33554432*('Y'=Create_user_priv)+
67108864*('Y'=Event_priv)+
134217728*('Y'=Trigger_priv)+
268435456*('Y'=Create_tablespace_priv)+
536870912*('Y'=Delete_history_priv),
'ssl_type', ssl_type-1,
'ssl_cipher', ssl_cipher,
'x509_issuer', x509_issuer,
'x509_subject', x509_subject,
'max_questions', max_questions,
'max_updates', max_updates,
'max_connections', max_connections,
'max_user_connections', max_user_connections,
'max_statement_time', max_statement_time,
'plugin', if(plugin>'',plugin,if(length(password)=16,'mysql_
old_password','mysql_native_password')),
'authentication_string', if(plugin>'' and authentication_str
ing>'',authentication_string,password),
'password_last_changed', if(password_expired='Y', 0, UNIX_TIMESTAMP(password_last_changed)),
'account_locked', 'Y'=account_locked,
'default_role', default_role,
'is_role', 'Y'=is_role)) as Priv
FROM user;
DROP TABLE user;
END IF
200418 13:06:41 16 Query set sql_mode=''
16 Query set @orig_storage_engine=@@storage_engine
16 Query set storage_engine=Aria
16 Query set system_versioning_alter_history=keep
16 Query set @have_innodb= (select count(engine) from inf
ormation_schema.engines where engine='INNODB' and support != 'NO')
16 Query SET @innodb_or_aria=IF(@have_innodb <> 0, 'InnoD
B', 'Aria')
16 Query CREATE TABLE IF NOT EXISTS db ( Host char(60)
binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(80
) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci
DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT
'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT
NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Cr
eate_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv
...
Note that CREATE TABLE IF NOT EXISTS syntax is used, so if ALTERs failed before as some table was missing at a later step it is created with proper structure for 10.4.
Here is the infamous mysql.user view creation later:
...Finally, of those details I am interested in, later in the general query log we see missing statistics tables creation, also for engine-independent persistent statistics:
16 Query set @had_db_table= @@warning_count != 0
16 Query CREATE TABLE IF NOT EXISTS global_priv (Host cha
r(60) binary DEFAULT '', User char(80) binary DEFAULT '', Priv JSON NOT NULL DEF
AULT '{}' CHECK(JSON_VALID(Priv)), PRIMARY KEY Host (Host,User)) engine=Aria tra
nsactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privi
leges'
16 Query CREATE DEFINER=root@localhost SQL SECURITY DEFIN
ER VIEW IF NOT EXISTS user AS SELECT
Host,
User,
IF(JSON_VALUE(Priv, '$.plugin') IN ('mysql_native_password', 'mysql_old_passwo
rd'), IFNULL(JSON_VALUE(Priv, '$.authentication_string'), ''), '') AS Password,
IF(JSON_VALUE(Priv, '$.access') & 1, 'Y', 'N') AS Select_priv,
IF(JSON_VALUE(Priv, '$.access') & 2, 'Y', 'N') AS Insert_priv,
IF(JSON_VALUE(Priv, '$.access') & 4, 'Y', 'N') AS Update_priv,
IF(JSON_VALUE(Priv, '$.access') & 8, 'Y', 'N') AS Delete_priv,
IF(JSON_VALUE(Priv, '$.access') & 16, 'Y', 'N') AS Create_priv,
IF(JSON_VALUE(Priv, '$.access') & 32, 'Y', 'N') AS Drop_priv,
IF(JSON_VALUE(Priv, '$.access') & 64, 'Y', 'N') AS Reload_priv,
IF(JSON_VALUE(Priv, '$.access') & 128, 'Y', 'N') AS Shutdown_priv,
IF(JSON_VALUE(Priv, '$.access') & 256, 'Y', 'N') AS Process_priv,
IF(JSON_VALUE(Priv, '$.access') & 512, 'Y', 'N') AS File_priv,
IF(JSON_VALUE(Priv, '$.access') & 1024, 'Y', 'N') AS Grant_priv,
IF(JSON_VALUE(Priv, '$.access') & 2048, 'Y', 'N') AS References_priv,
IF(JSON_VALUE(Priv, '$.access') & 4096, 'Y', 'N') AS Index_priv,
IF(JSON_VALUE(Priv, '$.access') & 8192, 'Y', 'N') AS Alter_priv,
IF(JSON_VALUE(Priv, '$.access') & 16384, 'Y', 'N') AS Show_db_priv,
IF(JSON_VALUE(Priv, '$.access') & 32768, 'Y', 'N') AS Super_priv,
IF(JSON_VALUE(Priv, '$.access') & 65536, 'Y', 'N') AS Create_tmp_table_pri
v,
IF(JSON_VALUE(Priv, '$.access') & 131072, 'Y', 'N') AS Lock_tables_priv,
IF(JSON_VALUE(Priv, '$.access') & 262144, 'Y', 'N') AS Execute_priv,
IF(JSON_VALUE(Priv, '$.access') & 524288, 'Y', 'N') AS Repl_slave_priv,
IF(JSON_VALUE(Priv, '$.access') & 1048576, 'Y', 'N') AS Repl_client_priv,
IF(JSON_VALUE(Priv, '$.access') & 2097152, 'Y', 'N') AS Create_view_priv,
IF(JSON_VALUE(Priv, '$.access') & 4194304, 'Y', 'N') AS Show_view_priv,
IF(JSON_VALUE(Priv, '$.access') & 8388608, 'Y', 'N') AS Create_routine_priv,
IF(JSON_VALUE(Priv, '$.access') & 16777216, 'Y', 'N') AS Alter_routine_priv,
IF(JSON_VALUE(Priv, '$.access') & 33554432, 'Y', 'N') AS Create_user_priv,
IF(JSON_VALUE(Priv, '$.access') & 67108864, 'Y', 'N') AS Event_priv,
IF(JSON_VALUE(Priv, '$.access') & 134217728, 'Y', 'N') AS Trigger_priv,
IF(JSON_VALUE(Priv, '$.access') & 268435456, 'Y', 'N') AS Create_tablespace_pr
iv,
IF(JSON_VALUE(Priv, '$.access') & 536870912, 'Y', 'N') AS Delete_history_priv,
ELT(IFNULL(JSON_VALUE(Priv, '$.ssl_type'), 0) + 1, '', 'ANY','X509', 'SPECIFIE
D') AS ssl_type,
IFNULL(JSON_VALUE(Priv, '$.ssl_cipher'), '') AS ssl_cipher,
IFNULL(JSON_VALUE(Priv, '$.x509_issuer'), '') AS x509_issuer,
IFNULL(JSON_VALUE(Priv, '$.x509_subject'), '') AS x509_subject,
CAST(IFNULL(JSON_VALUE(Priv, '$.max_questions'), 0) AS UNSIGNED) AS max_questi
ons,
CAST(IFNULL(JSON_VALUE(Priv, '$.max_updates'), 0) AS UNSIGNED) AS max_updates,
CAST(IFNULL(JSON_VALUE(Priv, '$.max_connections'), 0) AS UNSIGNED) AS max_conn
ections,
CAST(IFNULL(JSON_VALUE(Priv, '$.max_user_connections'), 0) AS SIGNED) AS max_u
ser_connections,
IFNULL(JSON_VALUE(Priv, '$.plugin'), '') AS plugin,
IFNULL(JSON_VALUE(Priv, '$.authentication_string'), '') AS authentication_stri
ng,
'N' AS password_expired,
ELT(IFNULL(JSON_VALUE(Priv, '$.is_role'), 0) + 1, 'N', 'Y') AS is_role,
IFNULL(JSON_VALUE(Priv, '$.default_role'), '') AS default_role,
CAST(IFNULL(JSON_VALUE(Priv, '$.max_statement_time'), 0.0) AS DECIMAL(12,6)) A
S max_statement_time
FROM global_priv
16 Query set @had_user_table= @@warning_count != 0
16 Query CREATE TABLE IF NOT EXISTS roles_mapping ( Host
char(60) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Role char(80) binary DEFAULT '' NOT NULL, Admin_option enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, UNIQUE (Host, User, Role)) engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Granted roles'
...
...So, one way ot the other, with direct SQL or with prepared statements etc, either by changing structure multiple times or by adding missing tables we should end up with the content MariaDB 10.4 really needed.
16 Query SET @create_innodb_table_stats="CREATE TABLE IF
NOT EXISTS innodb_table_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(199) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMES
TAMP ON UPDATE CURRENT_TIMESTAMP,
n_rows BIGINT UNSIGNED NOT NULL,
clustered_index_size BIGINT UNSIGNED NOT NULL,
sum_of_other_index_sizes BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (database_name, table_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0"
16 Query SET @create_innodb_index_stats="CREATE TABLE IF
NOT EXISTS innodb_index_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(199) NOT NULL,
index_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMES
TAMP ON UPDATE CURRENT_TIMESTAMP,
/* there are at least:
stat_name='size'
stat_name='n_leaf_pages'
stat_name='n_diff_pfx%' */
stat_name VARCHAR(64) NOT NULL,
stat_value BIGINT UNSIGNED NOT NULL,
sample_size BIGINT UNSIGNED,
stat_description VARCHAR(1024) NOT NULL,
PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0"
...
16 Query SET @str=IF(@have_innodb <> 0, @create_innodb_ta
ble_stats, "SET @dummy = 0")
16 Query PREPARE stmt FROM @str
16 Prepare CREATE TABLE IF NOT EXISTS innodb_table_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(199) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMES
TAMP ON UPDATE CURRENT_TIMESTAMP,
n_rows BIGINT UNSIGNED NOT NULL,
clustered_index_size BIGINT UNSIGNED NOT NULL,
sum_of_other_index_sizes BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (database_name, table_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0
16 Query EXECUTE stmt
16 Execute CREATE TABLE IF NOT EXISTS innodb_table_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(199) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMES
TAMP ON UPDATE CURRENT_TIMESTAMP,
n_rows BIGINT UNSIGNED NOT NULL,
clustered_index_size BIGINT UNSIGNED NOT NULL,
sum_of_other_index_sizes BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (database_name, table_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0
16 Query DROP PREPARE stmt
16 Query SET @str=IF(@have_innodb <> 0, @create_innodb_in
dex_stats, "SET @dummy = 0")
16 Query PREPARE stmt FROM @str
16 Prepare CREATE TABLE IF NOT EXISTS innodb_index_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(199) NOT NULL,
index_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMES
TAMP ON UPDATE CURRENT_TIMESTAMP,
/* there are at least:
stat_name='size'
stat_name='n_leaf_pages'
stat_name='n_diff_pfx%' */
stat_name VARCHAR(64) NOT NULL,
stat_value BIGINT UNSIGNED NOT NULL,
sample_size BIGINT UNSIGNED,
stat_description VARCHAR(1024) NOT NULL,
PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0
16 Query EXECUTE stmt
200418 13:06:42 16 Execute CREATE TABLE IF NOT EXISTS innodb_index_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(199) NOT NULL,
index_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
/* there are at least:
stat_name='size'
stat_name='n_leaf_pages'
stat_name='n_diff_pfx%' */
stat_name VARCHAR(64) NOT NULL,
stat_value BIGINT UNSIGNED NOT NULL,
sample_size BIGINT UNSIGNED,
stat_description VARCHAR(1024) NOT NULL,
PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0
16 Query DROP PREPARE stmt
...
16 Query CREATE TABLE IF NOT EXISTS table_stats (db_name
varchar(64) NOT NULL, table_name varchar(64) NOT NULL, cardinality bigint(21) un
signed DEFAULT NULL, PRIMARY KEY (db_name,table_name) ) engine=Aria transactional=0 CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Tables'
16 Query CREATE TABLE IF NOT EXISTS column_stats (db_name
varchar(64) NOT NULL, table_name varchar(64) NOT NULL, column_name varchar(64)
NOT NULL, min_value varbinary(255) DEFAULT NULL, max_value varbinary(255) DEFAULT NULL, nulls_ratio decimal(12,4) DEFAULT NULL, avg_length decimal(12,4) DEFAULT
NULL, avg_frequency decimal(12,4) DEFAULT NULL, hist_size tinyint unsigned, his
t_type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB'), histogram varbinary(255), PRIMARY KEY (db_name,table_name,column_name) ) engine=Aria transactional=0 CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'
16 Query CREATE TABLE IF NOT EXISTS index_stats (db_name
varchar(64) NOT NULL, table_name varchar(64) NOT NULL, index_name varchar(64) NOT NULL, prefix_arity int(11) unsigned NOT NULL, avg_frequency decimal(12,4) DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) engine=Aria
transactional=0 CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes'
...
Final steps for connection 16 are:
...So, it creates performance_schema if needed. Then we see the following:
16 Query SET @broken_routines = (select count(*) from mysql.proc where db='performance_schema')
16 Query SET @broken_events = (select count(*) from mysql.event where db='performance_schema')
16 Query SET @broken_pfs= (select @broken_tables + @broken_views + @broken_routines + @broken_events)
16 Query SET @cmd= "DROP DATABASE IF EXISTS performance schema"
16 Query SET @str = IF(@broken_pfs = 0, @cmd, 'SET @du = 0')
16 Query PREPARE stmt FROM @str
16 Prepare DROP DATABASE IF EXISTS performance_schema
16 Query EXECUTE stmt
16 Execute DROP DATABASE IF EXISTS performance_schema
16 Query DROP PREPARE stmt
16 Query SET @cmd= "CREATE DATABASE performance_schema character set utf8"
16 Query SET @str = IF(@broken_pfs = 0, @cmd, 'SET @du = 0')
16 Query PREPARE stmt FROM @str
16 Prepare CREATE DATABASE performance_schema character set utf8
16 Query EXECUTE stmt
16 Execute CREATE DATABASE performance_schema character set utf8
16 Query DROP PREPARE stmt
16 Quit
17 Connect root@localhost as anonymous onThat is, we check every table in every other database for upgrade, so some finalr steps and eventually execute FLUSH PRIVILEGES.
17 Query SET WSREP_ON=0
17 Query SET SQL_LOG_BIN=0
17 Query SHOW DATABASES
17 Init DB mysql
17 Query SHOW /*!50002 FULL*/ TABLES
17 Init DB test
17 Query SHOW /*!50002 FULL*/ TABLES
17 Quit
18 Connect root@localhost as anonymous on
18 Query SET WSREP_ON=0
18 Query SET SQL_LOG_BIN=0
18 Query SHOW DATABASES
18 Init DB test
18 Query SHOW /*!50002 FULL*/ TABLES
18 Query CHECK TABLE `t1` FOR UPGRADE
18 Quit
19 Connect root@localhost as anonymous on mysql
19 Query SET SQL_LOG_BIN=0, WSREP_ON=OFF
19 Query SELECT COUNT(*) AS c1 FROM mysql.slave_master_info
19 Quit
20 Connect root@localhost as anonymous on mysql
20 Query SET SQL_LOG_BIN=0, WSREP_ON=OFF
20 Query SELECT COUNT(*) AS c1 FROM mysql.slave_relay_log_info
20 Quit
21 Connect root@localhost as anonymous on mysql
21 Query SET SQL_LOG_BIN=0, WSREP_ON=OFF
21 Query FLUSH PRIVILEGES
21 Quit
22 Connect root@localhost as anonymous on mysql
22 Query SET SQL_LOG_BIN=0, WSREP_ON=OFF
22 Query show variables like 'datadir'
22 Quit
23 Connect root@localhost as anonymous on mysql
23 Query SET SQL_LOG_BIN=0, WSREP_ON=OFF
23 Query show variables like 'datadir'
23 Quit
200418 15:19:34 24 Connect root@localhost as anonymous on
24 Query select @@version_comment limit 1
200418 15:19:38 24 Query show tables from mysql like 'innodb%'
200418 15:21:01 24 Query shutdown
Theoretically we can continue to work then, as we did in connection 24, but the only reasonable action when mysql_upgrade was executed without errors would be to shut down and restart, as we actually did.
After restart we should have 10.4 up, running and ready to use, with all new features, colum,ns and tables avaiable and existing data upgraded to 10.4 "expectations".
 |
| Sunset of the step by step major MariaDB major versions upgrade era... |
- Running mysql_upgrade after any major version upgrade is a must. MariaDB may work for years without that, but you may end up with all kinds of troubles, including bad performance for queries due to missing/non-usable statistcis etc. Do not ignore related messages in the error log!
- I was not able to find any still active bug reports about simple cases when direct upgrade skipping some major version(s) fails while the same upgrade done step by step does not fail.
- Missing tables etc are really created at later stages of the process, no matter what was the version we upgrade from. In SQL code at least I see no checks for what the older version was. Looks like SQL is executed no matter if we get the error, and we just continue to the next statement that is going to fix the problem, if any. Something similar happens to applying binary log DML events from the earlier position than needed - if we ignore errors and apply them all, eventually we should end up in consistent state.
- The SQL code executed, with checks etc, is complex enough. mysql_upgrade tries to deal with many details and fix many things, so one can not exclude bugs in the process withouth extensive testing.
- It seems to be safe to run mysql_upgrade multiple times even if it was already run and has nothing to do. Some waste of time and no other harm expected. SQL is written that way, with all that IF NOT EXISTS and other checks.
- Assuming that CHECK TABLE ... FOR UPGRADE from MariaDB 10.4 is able to find and fix all problems in tables originating from MariaDB or MySQL version x.y.z so that the result is usable, the process of direct upgrade skipping intermediate major versions may even work well, as Monty stated, with a set of assumptions and restrictions presented above. One still has to check the code and bugs to be sure about that part.
I skipped versions while upgrading MySQL at two web-scale companies (4.0 to 5.0, 5.1 to 5.6). You can decide whether this was easy.
ReplyDelete1) Dump & reload
2) Test for months
3) Switch to new version a few shards at a time
I know it is never as easy as installing new binaries and running mysql_upgrade in practice. This is just the beginning. Dump of the data on older version is surely needed both for quick downgrades, binary upgrade problems and to be sure data are at least readable, and long testing with real queries and data is unavoidable for those who care...
ReplyDeleteThe scope of this recent discussion was limited: do we have to start each intermediate major version and run its mysql_upgrade, or it is enough to start with the target version and run mysql_upgrade once.