Bug #80454 mysql_upgrade - Error occurred: Cannot setup server variables
Submitted: 20 Feb 2016 15:39 Modified: 23 Feb 2016 9:08
Reporter: Pierre Ozoux Email Updates:
Status: Open Impact on me:
None 
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
Description:
I run MySQL 5.7.11 inside the official docker image.

When I want to upgrade I get the following error:

mysql_upgrade -uroot -p
password:
Error occurred: Cannot setup server variables.

I searched around, didn't find any hint. I tried to run with --verbose, but no luck.
I asked on http://dba.stackexchange.com/questions/129525/mysql-mysql-upgrade-error-occurred-cannot-se...

They suggested me to file a bug here.
I used Severity S1 as I can't dump the database for backups.

How to repeat:
N/A
[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
```