Bug #68163 ordering of mysql_upgrade tasks is not optimal (makes upgrades noisy)

• Submitted: 24 Jan 2013 12:54
• Modified: 13 Mar 2013 13:24
• Reporter: Simon Mudd (OCA)
• Status: Closed
• Category: MySQL Server: Installing
• Severity: S3 (Non-critical)
• Version: 5.6.9rc
• OS: Any
• CPU Architecture: Any

[24 Jan 2013 12:54] Simon Mudd

Description:
When upgrading a MySQL 5.5.23 server to MySQL 5.6.9rc, and running mysql_upgrade I noticed that I had a lot of messages like:

2013-01-24 13:04:51 57261940 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-01-24 13:04:51 57261940 InnoDB: Error: Fetch of persistent statistics requested for table "mydatabase"."table1" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2013-01-24 13:04:51 57261940 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-01-24 13:04:51 57261940 InnoDB: Error: Fetch of persistent statistics requested for table "mydatabase"."table2" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2013-01-24 13:04:51 57261940 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-01-24 13:04:51 57261940 InnoDB: Error: Fetch of persistent statistics requested for table "mydatabase"."table3" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

This is silly: you should have first upgraded the mysql database, then the I_S and P_S databases (if necessary) and then finally look at non-system databases. Then none of these errors would appear.

They may be mainly harmless but they're unnecessary, and adjusting the ordering of the mysql_upgrade process should I think fix this.

How to repeat:
run mysql_upgrade on a server that was running 5.5.23 after installing 5.6.9rc binaries.
make sure you have innodb tables etc configured and then look for these messages.

Suggested fix:
See above adjust the ordering of the steps so this noise will go away, and the missing mysql tables are already there.

[24 Jan 2013 13:03] Mark Leith

Verified with code inspection, and should be relatively easy to fix (not counting tests that may be done on this already), we currently do:

  /*
    Run "mysqlcheck" and "mysql_fix_privilege_tables.sql"
  */
  if ((!opt_systables_only &&
       (run_mysqlcheck_fixnames() || run_mysqlcheck_upgrade())) ||
      run_sql_fix_privilege_tables())
  {
    /*
      The upgrade failed to complete in some way or another,
      significant error message should have been printed to the screen
    */
    die("Upgrade failed" );
  }

We should just change the if logic around a little to upgrade the system tables first:

  if (run_sql_fix_privilege_tables() || (!opt_systables_only &&
       (run_mysqlcheck_fixnames() || run_mysqlcheck_upgrade())))

[13 Mar 2013 13:24] Erlend Dahl

Noted in 5.6.11, 5.7.1 changelogs.

mysqlcheck has a new --skip-database option. The option value is the name of a database (case sensitive) for which checks should be skipped.

mysql_upgrade uses this option to upgrade the system tables in the mysql database before tables in other databases: It upgrades the mysql database, then all databases except the mysql database. This avoids problems that can occur if user tables are upgraded before the system tables.

[17 Feb 2016 19:42] Paul DuBois

Revision to changelog entry (to avoid the impression that mysql_upgrade itself accepts the --skip-database option):

mysql_upgrade adds this option to mysqlcheck commands that it
generates to upgrade the system tables in the mysql database before
tables in other databases: It upgrades the mysql database, then all
databases except the mysql database. This avoids problems that can
occur if user tables are upgraded before the system tables.