Percona 5.6 mysql_upgrade calls mysqlcheck again after flush privileges

[Peiran Song]

To reproduce the bug:

- Install a Percona 5.6.16 sandbox
- Install a Percona 5.5.36 sandbox
- Stop both servers
- Copy 5.5.36 data dir to 5.6.16 data dir
- Start 5.6.16 with --skip-grant-tables

 sandboxes/msb_5_6_16$ ./start --skip-grant-tables

- Verify that grant tables are skipped by connecting to it as peiran.song which is not a mysql user, and run "show grants".

  sandboxes/msb_5_6_16$ ./use

  Welcome to the MySQL monitor. Commands end with ; or \g.
  Your MySQL connection id is 1
  Server version: 5.6.16-64.2-rel64.2-log Percona Server with XtraDB (GPL), Release rel64.2, Revision 569
  ...
  mysql [localhost] {peiran.song} ((none)) > show grants;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

- Run the mysql_upgrade from the 5.6.16 bin directory. Note that, in the cnf file, user/password are commented out.

  5.6.16/bin$ ./mysql_upgrade --defaults-file=/path/to/msb_5_6_16/my.sandbox.cnf --verbose

  At the end of the upgrade, it reported connection error for root@localhost without password:

  Running 'mysql_fix_privilege_tables'...
  Running 'mysqlcheck' with connection arguments: '--port=5616' '--socket=/tmp/mysql_sandbox5616.sock'
  ./mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
  FATAL ERROR: Upgrade failed

- Double check that grant tables are no longer skipped by connecting to it again as peiran.song. And indeed, connection is rejected this time.

  sandboxes/msb_5_6_16$ ./use
  ERROR 1045 (28000): Access denied for user 'peiran.song'@'localhost' (using password: NO)

[Peiran Song (peiran-song)]

mysql_upgrade is supposed to flush privileges. The culprit seems that the tested version calls mysqlcheck again after flush privileges, and so unable to connect to the server as root without password.

The output from running mysql_upgrade from Percona 5.6 package is as below where mysqlcheck is invoked again after running mysql_fix_privilege_tables.

Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=5616' '--socket=/tmp/mysql_sandbox5616.sock'
Running 'mysqlcheck' with connection arguments: '--port=5616' '--socket=/tmp/mysql_sandbox5616.sock'
mysql.columns_priv OK
... ...
mysql.time_zone_transition_type OK
mysql.user OK
Running 'mysql_fix_privilege_tables'...
Running 'mysqlcheck' with connection arguments: '--port=5616' '--socket=/tmp/mysql_sandbox5616.sock'
./mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
FATAL ERROR: Upgrade failed

The output from running mysql_upgrade from community v5.5.37 is as below, where running mysql_fix_privilege_tables is the last item.

Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=5537' '--socket=/tmp/mysql_sandbox5537.sock'
Running 'mysqlcheck' with connection arguments: '--port=5537' '--socket=/tmp/mysql_sandbox5537.sock'
mysql.columns_priv OK
... ...
mysql.user OK
test.a OK
test.company OK
Running 'mysql_fix_privilege_tables'...
OK

[Peiran Song (peiran-song)]

Also tested the mysql_upgrade program in Percona Server 5.1.68 and 5.5.24, both executed as expected without error. Thus only 5.6 is affected.

[Roel Van de Paar (roel11)]

Pieran, is this a legacy bug or recently introduced?

[Peiran Song (peiran-song)]

Roel,

The bug is verified on PS 5.6.16, but not on PS 5.5.24, PS 5.1.68. Also the customer, who contacted Support with the failure on 5.6 upgrade, had no issue upgrading to the latest PS 5.5. So I would say this is introduced in PS 5.6. Did that answer your question?

Thanks,
Peiran

[Laurynas Biveinis (laurynas-biveinis)]

Is this upstream bug?

[Peiran Song (peiran-song)]

Verified it in upstream mysql community version 5.6.17.

$ ./mysql_upgrade --defaults-file=/home/peiran.song/sandboxes/msb_cm5_6_17/my.sandbox.cnf --verbose
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=56171' '--socket=/tmp/mysql_sandbox56171.sock'
Running 'mysqlcheck' with connection arguments: '--port=56171' '--socket=/tmp/mysql_sandbox56171.sock'
mysql.columns_priv OK
...
mysql.time_zone_transition_type OK
mysql.user OK
Running 'mysql_fix_privilege_tables'...
Running 'mysqlcheck' with connection arguments: '--port=56171' '--socket=/tmp/mysql_sandbox56171.sock'
./mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
FATAL ERROR: Upgrade failed

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3130