Bug #80454 | mysql_upgrade - Error occurred: Cannot setup server variables | ||
---|---|---|---|
Submitted: | 20 Feb 2016 15:39 | Modified: | 27 Nov 2019 18:43 |
Reporter: | Pierre Ozoux | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.7.11 | OS: | Debian (Latest official docker image) |
Assigned to: | Lars Tangvald | CPU Architecture: | Any |
Tags: | upgrade |
[20 Feb 2016 15:39]
Pierre Ozoux
[20 Feb 2016 18:53]
Terje Røsten
Hi! Thanks for your report! From where (docker, host OS or remote hosts) are you running mysql_upgrade? MySQL server running in docker container and mysql_upgrade command must be of identical version.
[20 Feb 2016 19:38]
Pierre Ozoux
I'm running the command from inside the MySQL containers. I assume they are at the same version: https://github.com/docker-library/mysql/blob/master/5.7/Dockerfile#L31
[20 Feb 2016 20:44]
Terje Røsten
Good, are you able to connect with mysql -uroot -p from inside the same container?
[21 Feb 2016 8:46]
Pierre Ozoux
Yes, I can start the REPL.
[21 Feb 2016 18:51]
Terje Røsten
Hi again, problem seems not to be related to docker image usage. Error you get seems to be generated from: if (!this->m_write_binlog) { if (mysql_query(this->m_mysql_connection, "SET SQL_LOG_BIN=0") != 0) { return this->print_error(1, "Cannot setup server variables."); } } Ref: https://github.com/mysql/mysql-server/blob/d04d7a8f844caf78f03c007243681a1b5471d0a3/client... The error message don't include information why that happen, permission issue?
[22 Feb 2016 14:50]
Lars Tangvald
Hi, I'm unable to reproduce this with the default Docker image setup. Are you using your own server config? If so, can you please post it here? How is the container started? How are you connecting to the container to run mysql_upgrade?
[22 Feb 2016 15:47]
Pierre Ozoux
I checked for permission issue: ``` /var/lib/mysql# chown mysql:mysql -R ./* ``` Same error, would you like me to provide more info on a specific folder? I actually manage various dbs (around 30), and this is the only one this happened, I think it will be difficult to reproduce. I really use vanilla docker image without specific conf.
[22 Feb 2016 15:55]
Lars Tangvald
Are you able to set up a non-Docker server with the same datadir and see if you can reproduce the issue without Docker?
[22 Feb 2016 16:02]
Pierre Ozoux
I think it would be complicated. But it would boil down to shutdown MySQL, copying the folder to another server, and starting a MySQL against that folder right? I'll see this week if I find 2hours to do this test. What if I tell you that it is not docker, what would be the next step? Do you have any command for me to perform to help you identify the source of the issue? The main issue here I guess is that the error message is not really verbose.
[22 Feb 2016 20:07]
Lars Tangvald
That should be all you need, yes, though maybe you'd also need to copy the config files. If it turns out not to be a Docker issue, we'll pass it on to the server team to help debug it further.
[22 Feb 2016 21:29]
Lars Tangvald
Could you also try connecting to the database and running the "SET SQL_LOG_BIN=0" query?
[23 Feb 2016 9:08]
Pierre Ozoux
The query gave me this: ``` ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation ``` So I guess, I miss some privileges. I found a workaround though (I changed severity). I started MySQL with `--skip-grant-tables`. and I managed to do the upgrade: ``` mysql_upgrade Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. 2016-02-23T08:59:49.475493Z 2 [Warning] System table 'servers' is expected to be transactional. 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.host 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 error : Table upgrade required. Please do "REPAIR TABLE `proxies_priv`" or dump/reload to fix it! 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 Repairing tables mysql.proxies_priv Note : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format. status : OK Upgrading the sys schema. 2016-02-23T08:59:49.723551Z 2 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode. 2016-02-23T08:59:49.723574Z 2 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode. 2016-02-23T08:59:49.723623Z 2 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode. Checking databases. sys.sys_config OK wordpress.wp_ap_appointments OK wordpress.wp_ap_events OK wordpress.wp_ap_service_category OK wordpress.wp_ap_services OK wordpress.wp_appointgen_scappointments OK wordpress.wp_appointgen_scappointments_paymentmethods OK wordpress.wp_appointgen_schedules OK wordpress.wp_appointgen_services OK wordpress.wp_appointgen_timeslot OK wordpress.wp_appointgen_venues OK wordpress.wp_commentmeta OK wordpress.wp_comments error : Table rebuild required. Please do "ALTER TABLE `wp_comments` FORCE" or dump/reload to fix it! wordpress.wp_links error : Table rebuild required. Please do "ALTER TABLE `wp_links` FORCE" or dump/reload to fix it! wordpress.wp_ngg_album OK wordpress.wp_ngg_gallery OK wordpress.wp_ngg_pictures error : Table rebuild required. Please do "ALTER TABLE `wp_ngg_pictures` FORCE" or dump/reload to fix it! wordpress.wp_options OK wordpress.wp_postmeta OK wordpress.wp_posts error : Table rebuild required. Please do "ALTER TABLE `wp_posts` FORCE" or dump/reload to fix it! wordpress.wp_sc_currency_list OK wordpress.wp_statistics_exclusions OK wordpress.wp_statistics_historical OK wordpress.wp_statistics_pages OK wordpress.wp_statistics_search OK wordpress.wp_statistics_useronline OK wordpress.wp_statistics_visit OK wordpress.wp_statistics_visitor OK wordpress.wp_term_relationships OK wordpress.wp_term_taxonomy OK wordpress.wp_terms OK wordpress.wp_usermeta OK wordpress.wp_users error : Table rebuild required. Please do "ALTER TABLE `wp_users` FORCE" or dump/reload to fix it! Repairing tables mysql.proxies_priv OK `wordpress`.`wp_comments` Running : ALTER TABLE `wordpress`.`wp_comments` FORCE status : OK `wordpress`.`wp_links` Running : ALTER TABLE `wordpress`.`wp_links` FORCE status : OK `wordpress`.`wp_ngg_pictures` Running : ALTER TABLE `wordpress`.`wp_ngg_pictures` FORCE status : OK `wordpress`.`wp_posts` Running : ALTER TABLE `wordpress`.`wp_posts` FORCE status : OK `wordpress`.`wp_users` Running : ALTER TABLE `wordpress`.`wp_users` FORCE status : OK Upgrade process completed successfully. Checking if update is needed. ``` Next time there is an upgrade, I'll try with: GRANT SUPER ON *.* TO user@'localhost' IDENTIFIED BY 'password'; I guess, to close the issue, we should change the error message: `Cannot setup server variables` to ``` ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation ```
[27 Oct 2019 18:43]
MySQL Verification Team
This bug report could be considered solver so could be closed?. Thanks.
[28 Nov 2019 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".