MySQL container doesn't migrate system table schemas

[almereyda]

When upgrading the MySQL container with docker-compose pull, it seems no migration procedure is in place for upgrading the system tables. One will either want to have one, or circumvent this behaviour by using tagged releases.

Attaching to knownyalamerde_db_1
db_1 | 2017-02-20T12:30:11.760650Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
db_1 | 2017-02-20T12:30:11.761491Z 0 [Note] mysqld (mysqld 5.7.17) starting as process 1 ...
db_1 | 2017-02-20T12:30:11.763645Z 0 [Note] InnoDB: PUNCH HOLE support available
db_1 | 2017-02-20T12:30:11.763691Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
db_1 | 2017-02-20T12:30:11.763704Z 0 [Note] InnoDB: Uses event mutexes
db_1 | 2017-02-20T12:30:11.763705Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
db_1 | 2017-02-20T12:30:11.763707Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
db_1 | 2017-02-20T12:30:11.763709Z 0 [Note] InnoDB: Using Linux native AIO
db_1 | 2017-02-20T12:30:11.763893Z 0 [Note] InnoDB: Number of pools: 1
db_1 | 2017-02-20T12:30:11.764015Z 0 [Note] InnoDB: Using CPU crc32 instructions
db_1 | 2017-02-20T12:30:11.765070Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
db_1 | 2017-02-20T12:30:11.771110Z 0 [Note] InnoDB: Completed initialization of buffer pool
db_1 | 2017-02-20T12:30:11.772549Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
db_1 | 2017-02-20T12:30:11.783619Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
db_1 | 2017-02-20T12:30:11.899586Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
db_1 | 2017-02-20T12:30:11.899655Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
db_1 | 2017-02-20T12:30:12.062190Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
db_1 | 2017-02-20T12:30:12.063086Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
db_1 | 2017-02-20T12:30:12.063111Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
db_1 | 2017-02-20T12:30:12.063438Z 0 [Note] InnoDB: 5.7.17 started; log sequence number 218178081
db_1 | 2017-02-20T12:30:12.063762Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
db_1 | 2017-02-20T12:30:12.063834Z 0 [Note] Plugin 'FEDERATED' is disabled.
db_1 | 2017-02-20T12:30:12.065565Z 0 [Warning] System table 'plugin' is expected to be transactional.
db_1 | 2017-02-20T12:30:12.065822Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170220 12:30:12
db_1 | 2017-02-20T12:30:12.065872Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
db_1 | 2017-02-20T12:30:12.065911Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
db_1 | 2017-02-20T12:30:12.066143Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
db_1 | 2017-02-20T12:30:12.066165Z 0 [Note] IPv6 is available.
db_1 | 2017-02-20T12:30:12.066171Z 0 [Note]   - '::' resolves to '::';
db_1 | 2017-02-20T12:30:12.066181Z 0 [Note] Server socket created on IP: '::'.
db_1 | 2017-02-20T12:30:12.121822Z 0 [Warning] Failed to open optimizer cost constant tables
db_1 | 
db_1 | 2017-02-20T12:30:12.122539Z 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
db_1 | 2017-02-20T12:30:12.122585Z 0 [Warning] 'proxies_priv' entry '@ root@511d678ebd7d' ignored in --skip-name-resolve mode.
db_1 | 2017-02-20T12:30:12.123000Z 0 [Warning] System table 'time_zone_leap_second' is expected to be transactional.
db_1 | 2017-02-20T12:30:12.123037Z 0 [Warning] System table 'time_zone_name' is expected to be transactional.
db_1 | 2017-02-20T12:30:12.123057Z 0 [Warning] System table 'time_zone' is expected to be transactional.
db_1 | 2017-02-20T12:30:12.123064Z 0 [Warning] System table 'time_zone_transition_type' is expected to be transactional.
db_1 | 2017-02-20T12:30:12.123071Z 0 [Warning] System table 'time_zone_transition' is expected to be transactional.
db_1 | 2017-02-20T12:30:12.123423Z 0 [Warning] System table 'servers' is expected to be transactional.
db_1 | 2017-02-20T12:30:12.123768Z 0 [ERROR] Incorrect definition of table performance_schema.events_waits_current: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('TRANSACTION','STATEMENT','STAGE','WAIT', found type enum('STATEMENT','STAGE','WAIT').
db_1 | 2017-02-20T12:30:12.123880Z 0 [ERROR] Incorrect definition of table performance_schema.events_waits_history: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('TRANSACTION','STATEMENT','STAGE','WAIT', found type enum('STATEMENT','STAGE','WAIT').
db_1 | 2017-02-20T12:30:12.123943Z 0 [ERROR] Incorrect definition of table performance_schema.events_waits_history_long: expected column 'NESTING_EVENT_TYPE' at position 15 to have type enum('TRANSACTION','STATEMENT','STAGE','WAIT', found type enum('STATEMENT','STAGE','WAIT
').
db_1 | 2017-02-20T12:30:12.124122Z 0 [ERROR] Incorrect definition of table performance_schema.events_waits_summary_by_user_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).
db_1 | 2017-02-20T12:30:12.124198Z 0 [ERROR] Incorrect definition of table performance_schema.events_waits_summary_by_account_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).
db_1 | 2017-02-20T12:30:12.124611Z 0 [ERROR] Column count of performance_schema.setup_actors is wrong. Expected 5, found 3. Created with MySQL 50627, now running 50717. Please use mysql_upgrade to fix this error.
db_1 | 2017-02-20T12:30:12.124780Z 0 [ERROR] Incorrect definition of table performance_schema.setup_objects: expected column 'OBJECT_TYPE' at position 0 to have type enum('EVENT','FUNCTION','PROCEDURE','TABLE','TRIGGER', found type enum('TABLE').
db_1 | 2017-02-20T12:30:12.125070Z 0 [ERROR] Column count of performance_schema.table_lock_waits_summary_by_table is wrong. Expected 68, found 73. Created with MySQL 50627, now running 50717. Please use mysql_upgrade to fix this error.
db_1 | 2017-02-20T12:30:12.125157Z 0 [ERROR] Column count of performance_schema.threads is wrong. Expected 17, found 14. Created with MySQL 50627, now running 50717. Please use mysql_upgrade to fix this error.
db_1 | 2017-02-20T12:30:12.125199Z 0 [ERROR] Column count of performance_schema.events_stages_current is wrong. Expected 12, found 10. Created with MySQL 50627, now running 50717. Please use mysql_upgrade to fix this error.
db_1 | 2017-02-20T12:30:12.125252Z 0 [ERROR] Column count of performance_schema.events_stages_history is wrong. Expected 12, found 10. Created with MySQL 50627, now running 50717. Please use mysql_upgrade to fix this error.
db_1 | 2017-02-20T12:30:12.125321Z 0 [ERROR] Column count of performance_schema.events_stages_history_long is wrong. Expected 12, found 10. Created with MySQL 50627, now running 50717. Please use mysql_upgrade to fix this error.
db_1 | 2017-02-20T12:30:12.125415Z 0 [ERROR] Incorrect definition of table performance_schema.events_stages_summary_by_account_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).
db_1 | 2017-02-20T12:30:12.125488Z 0 [ERROR] Incorrect definition of table performance_schema.events_stages_summary_by_user_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).
db_1 | 2017-02-20T12:30:12.125650Z 0 [ERROR] Column count of performance_schema.events_statements_current is wrong. Expected 41, found 40. Created with MySQL 50627, now running 50717. Please use mysql_upgrade to fix this error.
db_1 | 2017-02-20T12:30:12.125748Z 0 [ERROR] Column count of performance_schema.events_statements_history is wrong. Expected 41, found 40. Created with MySQL 50627, now running 50717. Please use mysql_upgrade to fix this error.
db_1 | 2017-02-20T12:30:12.125819Z 0 [ERROR] Column count of performance_schema.events_statements_history_long is wrong. Expected 41, found 40. Created with MySQL 50627, now running 50717. Please use mysql_upgrade to fix this error.
db_1 | 2017-02-20T12:30:12.125938Z 0 [ERROR] Incorrect definition of table performance_schema.events_statements_summary_by_account_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).
db_1 | 2017-02-20T12:30:12.126052Z 0 [ERROR] Incorrect definition of table performance_schema.events_statements_summary_by_user_by_event_name: expected column 'USER' at position 0 to have type char(32), found type char(16).
db_1 | 2017-02-20T12:30:12.126262Z 0 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_program' has the wrong structure
db_1 | 2017-02-20T12:30:12.126304Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_current' has the wrong structure
db_1 | 2017-02-20T12:30:12.126356Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history' has the wrong structure
db_1 | 2017-02-20T12:30:12.126376Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history_long' has the wrong structure
db_1 | 2017-02-20T12:30:12.126406Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_thread_by_event_name' has the wrong structure
db_1 | 2017-02-20T12:30:12.126426Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_account_by_event_name' has the wrong structure
db_1 | 2017-02-20T12:30:12.126446Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_user_by_event_name' has the wrong structure
db_1 | 2017-02-20T12:30:12.126489Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_host_by_event_name' has the wrong structure
db_1 | 2017-02-20T12:30:12.126512Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_global_by_event_name' has the wrong structure
db_1 | 2017-02-20T12:30:12.126585Z 0 [ERROR] Incorrect definition of table performance_schema.users: expected column 'USER' at position 0 to have type char(32), found type char(16).
db_1 | 2017-02-20T12:30:12.126642Z 0 [ERROR] Incorrect definition of table performance_schema.accounts: expected column 'USER' at position 0 to have type char(32), found type char(16).
db_1 | 2017-02-20T12:30:12.126897Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_global_by_event_name' has the wrong structure
db_1 | 2017-02-20T12:30:12.126948Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_account_by_event_name' has the wrong structure
db_1 | 2017-02-20T12:30:12.126972Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_host_by_event_name' has the wrong structure
db_1 | 2017-02-20T12:30:12.126992Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_thread_by_event_name' has the wrong structure
db_1 | 2017-02-20T12:30:12.127022Z 0 [ERROR] Native table 'performance_schema'.'memory_summary_by_user_by_event_name' has the wrong structure
db_1 | 2017-02-20T12:30:12.127044Z 0 [ERROR] Native table 'performance_schema'.'table_handles' has the wrong structure
db_1 | 2017-02-20T12:30:12.127062Z 0 [ERROR] Native table 'performance_schema'.'metadata_locks' has the wrong structure
db_1 | 2017-02-20T12:30:12.127091Z 0 [ERROR] Native table 'performance_schema'.'replication_connection_configuration' has the wrong structure
db_1 | 2017-02-20T12:30:12.127120Z 0 [ERROR] Native table 'performance_schema'.'replication_group_members' has the wrong structure
db_1 | 2017-02-20T12:30:12.127141Z 0 [ERROR] Native table 'performance_schema'.'replication_connection_status' has the wrong structure
db_1 | 2017-02-20T12:30:12.127176Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_configuration' has the wrong structure
db_1 | 2017-02-20T12:30:12.127199Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status' has the wrong structure
db_1 | 2017-02-20T12:30:12.127219Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_coordinator' has the wrong structure
db_1 | 2017-02-20T12:30:12.127247Z 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_worker' has the wrong structure
db_1 | 2017-02-20T12:30:12.127268Z 0 [ERROR] Native table 'performance_schema'.'replication_group_member_stats' has the wrong structure
db_1 | 2017-02-20T12:30:12.127287Z 0 [ERROR] Native table 'performance_schema'.'prepared_statements_instances' has the wrong structure
db_1 | 2017-02-20T12:30:12.127318Z 0 [ERROR] Native table 'performance_schema'.'user_variables_by_thread' has the wrong structure
db_1 | 2017-02-20T12:30:12.127346Z 0 [ERROR] Native table 'performance_schema'.'status_by_account' has the wrong structure
db_1 | 2017-02-20T12:30:12.127369Z 0 [ERROR] Native table 'performance_schema'.'status_by_host' has the wrong structure
db_1 | 2017-02-20T12:30:12.127398Z 0 [ERROR] Native table 'performance_schema'.'status_by_thread' has the wrong structure
db_1 | 2017-02-20T12:30:12.127418Z 0 [ERROR] Native table 'performance_schema'.'status_by_user' has the wrong structure
db_1 | 2017-02-20T12:30:12.127437Z 0 [ERROR] Native table 'performance_schema'.'global_status' has the wrong structure
db_1 | 2017-02-20T12:30:12.127472Z 0 [ERROR] Native table 'performance_schema'.'session_status' has the wrong structure
db_1 | 2017-02-20T12:30:12.127493Z 0 [ERROR] Native table 'performance_schema'.'variables_by_thread' has the wrong structure
db_1 | 2017-02-20T12:30:12.127512Z 0 [ERROR] Native table 'performance_schema'.'global_variables' has the wrong structure
db_1 | 2017-02-20T12:30:12.127546Z 0 [ERROR] Native table 'performance_schema'.'session_variables' has the wrong structure
db_1 | 2017-02-20T12:30:12.127657Z 0 [ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(16).
db_1 | 2017-02-20T12:30:12.127689Z 0 [ERROR] mysql.user has no `Event_priv` column at position 28
db_1 | 2017-02-20T12:30:12.127817Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
db_1 | 2017-02-20T12:30:12.127938Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 
db_1 | 2017-02-20T12:30:12.127953Z 0 [Note] Beginning of list of non-natively partitioned tables
db_1 | 2017-02-20T12:30:12.130987Z 0 [Note] End of list of non-natively partitioned tables
db_1 | 2017-02-20T12:30:12.131102Z 0 [Note] mysqld: ready for connections.
db_1 | Version: '5.7.17'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)

The output suggest to use mysql_upgrade to fix this error.

[almereyda]

Unfortunately

docker-compose run --rm db mysql_upgrade --protocol=tcp --host db -P 3306 --password

doesn't take the root password from the environment variable and expects it to be pasted to the prompt, but it works:

Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     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 the sys schema.
Checking databases.
known.config                                       OK
known.entities                                     OK
known.metadata                                     OK
known.reader                                       OK
known.session                                      OK
known.versions                                     OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

[almereyda]

Running

docker-compose up -d db

and

docker-compose run --rm db mysql_upgrade --protocol=tcp --host db -P 3306 --password

with manually pasting the password still helps upgrading the database.

[pierreozoux]

yes, this I know, but it is more a mysql issue than known.
I'll let you close, but anyway, it is good to keep this as reference.

[almereyda]

In that sense it's an issue of this Compose release, since it bundles MySQL without tagging a release. Would using tagged releases circumvent this? #20 (comment)

[pierreozoux]

yes, we should use a tagged release

[acepsaepudin]

thanks @almereyda