Bug #58514 mysql_upgrade fails on dump upgrade between 5.1.53 -> 5.5.8
Submitted: 26 Nov 2010 10:58 Modified: 8 Dec 2010 19:49
Reporter: Nidhi Shrotriya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Installing Severity:S3 (Non-critical)
Version:5.5.8 OS:Any
Assigned to: CPU Architecture:Any

[26 Nov 2010 10:58] Nidhi Shrotriya
Description:
"Might be a consequence of fixing bug#57551 ?"

Running mysql_upgrade on 5.5.8 after importing the dump from 5.1.53 fails as below.

Running 'mysql_fix_privilege_tables'...

ERROR 1548 (HY000) at line 153: Cannot load from mysql.proc. The table is probably corrupted

ERROR 1007 (HY000) at line 160: Can't create database 'performance_schema'; database exists

ERROR 1050 (42S01) at line 183: Table 'cond_instances' already exists

ERROR 1050 (42S01) at line 213: Table 'events_waits_current' already exists

ERROR 1050 (42S01) at line 227: Table 'events_waits_history' already exists

ERROR 1050 (42S01) at line 241: Table 'events_waits_history_long' already exists

ERROR 1050 (42S01) at line 262: Table 'events_waits_summary_by_instance' already exists

ERROR 1050 (42S01) at line 283: Table 'events_waits_summary_by_thread_by_event_name' already exists

ERROR 1050 (42S01) at line 303: Table 'events_waits_summary_global_by_event_name' already exists

ERROR 1050 (42S01) at line 320: Table 'file_instances' already exists

ERROR 1050 (42S01) at line 339: Table 'file_summary_by_event_name' already exists

ERROR 1050 (42S01) at line 359: Table 'file_summary_by_instance' already exists

ERROR 1050 (42S01) at line 376: Table 'mutex_instances' already exists

ERROR 1050 (42S01) at line 394: Table 'performance_timers' already exists

ERROR 1050 (42S01) at line 412: Table 'rwlock_instances' already exists

ERROR 1050 (42S01) at line 428: Table 'setup_consumers' already exists

ERROR 1050 (42S01) at line 445: Table 'setup_instruments' already exists

ERROR 1050 (42S01) at line 461: Table 'setup_timers' already exists

ERROR 1050 (42S01) at line 478: Table 'threads' already exists

FATAL ERROR: Upgrade failed

Note: 
Seems to be happening for dump upgrade only between major releases 5.1 & 5.5.
Live upgrade between 5.1.53 -> 5.5.8 works fine. 
Dump/Live upgrade between 5.5.7-rc -> 5.5.8 works fine.

This issue was also seen earlier as bug#52444, but wasn't seen for a long time.

How to repeat:
5.5.8 Binaries used:
http://trollheim.norway.sun.com/archive/2587153.mysql-advanced-5.5.8-linux2.6-x86_64.tar.g...
http://trollheim.norway.sun.com/archive/2586529.mysql-advanced-5.5.8-solaris10-sparc-64bit... 

Steps:
on 5.1.53:
./scripts/mysql_install_db --no-defaults
./bin/mysqld_safe --no-defaults &
./bin/mysqldump --user=root --socket=/tmp/mysql.sock --all-databases > mydump_51
./bin/mysqladmin --user=root --socket=/tmp/mysql.sock shut

On 5.5.8
./scripts/mysql_install_db --no-defaults
./bin/mysqld_safe --no-defaults &
./bin/mysql --user=root --socket=/tmp/mysql.sock < ../mysql_adv_5153/mydump_51
./bin/mysql_upgrade --user=root --socket=/tmp/mysql.sock

fails as mentioned in the description.
[26 Nov 2010 11:15] Nidhi Shrotriya
The impact looks limited to the mysql_upgrade utility throwing errors and 'FATAL ERROR: Upgrade Failed'. Also this seems to happen the first time 'mysql_upgrade' is run.
Running the mysql_upgrade utility second time makes the errors disappear, and it passes as
Running 'mysql_fix_privilege_tables'...
OK
[29 Nov 2010 17:06] Marc ALFF
I think this upgrade process is wrong:

Steps:
on 5.1.53:
./scripts/mysql_install_db --no-defaults
./bin/mysqld_safe --no-defaults &
./bin/mysqldump --user=root --socket=/tmp/mysql.sock --all-databases > mydump_51
./bin/mysqladmin --user=root --socket=/tmp/mysql.sock shut

On 5.5.8
(a) ./scripts/mysql_install_db --no-defaults
./bin/mysqld_safe --no-defaults &
./bin/mysql --user=root --socket=/tmp/mysql.sock < ../mysql_adv_5153/mydump_51
(b) ./bin/mysql_upgrade --user=root --socket=/tmp/mysql.sock

Because of (a), the new instance is already 5.5.8, and this is different from the old 5.1 instance.
Then what is the purpose of the mysql_upgrade in (b) ? It is attempting to upgrade from 5.5.8 to 5.5.8, and fails on mysql.proc.

Please clarify where these instructions come from, and why such sequence of operations is considered valid.
[29 Nov 2010 17:47] Elena Stepanova
Additional check with the previous versions (with Nidhi's algorithm):
5.1.53 / 5.5.6 -- no error messages
5.1.53 / 5.5.7 -- no error messages
5.1.53 / 5.5.8 -- error messages as described
[29 Nov 2010 17:56] Elena Stepanova
Hi Marc,

I don't see what steps can be skipped in the algorithm Nidhi described.

- dump is the recommended way of upgrade (see http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-5-1.html);
- (a) is needed because otherwise the server won't start and we won't be able to load the dump;
- (b) is needed because in a real-life scenario the dump will contain user tables, not only system tables, and mysql_upgrade is supposed to check those;  besides, after loading the full dump the system tables will be back to 5.1 version.
[30 Nov 2010 10:56] Marc ALFF
Root cause analysis
===================

A statement fails with:

ERROR 1548 (HY000) at line 153: Cannot load from mysql.proc. The table is probably
corrupted

The statement that fails however, is *not*

SET @broken_routines = (select count(*) from mysql.proc where db='performance_schema');

this statement is executed normally, and returns 0.

What fail is:

SET @cmd= "DROP DATABASE IF EXISTS performance_schema";

SET @str = IF(@broken_pfs = 0, @cmd, 'SET @dummy = 0');
PREPARE stmt FROM @str;
EXECUTE stmt;
DROP PREPARE stmt;

The failure itself is not related to prepared statements, but just to the DROP DATABASE statement alone.

The 'DROP DATABASE IF EXISTS performance_schema' fails, because the implementation of mysql_rm_db has changed between 5.5.7 and 5.5.8.

In particular, the code attempts to name lock all stored routines in the db to be dropped.
To do this, the mysql.proc table is opened, and this fails since the table has the wrong structure (which has just been damaged by the dump import).

The error handler which is *intended* to catch such errors,
Lock_db_routines_error_handler::handle_condition(),
is not trapping error 1548 ER_CANNOT_LOAD_FROM_TABLE,
causing the DROP DATABASE to fail, despite comments in the code that indicates that the intent was to have this statement succeed with damaged mysql.proc tables.

The other errors:

ERROR 1007 (HY000) at line 160: Can't create database 'performance_schema'; database
exists

ERROR 1050 (42S01) at line 183: Table 'cond_instances' already exists

are the logical consequence of a failed statement DROP DATABASE IF EXISTS,
since the performance_schema database was created by the 5.5.8 install and is still here.
[30 Nov 2010 10:58] Marc ALFF
The following patch fixes the problem:

MarcBook:mysql-5.5.8-release malff$ bzr diff
=== modified file 'sql/sp.cc'
--- sql/sp.cc	2010-11-24 15:08:27 +0000
+++ sql/sp.cc	2010-11-30 10:33:51 +0000
@@ -1372,7 +1372,8 @@
                         MYSQL_ERROR ** cond_hdl)
   {
     if (sql_errno == ER_NO_SUCH_TABLE ||
-        sql_errno == ER_COL_COUNT_DOESNT_MATCH_CORRUPTED)
+        sql_errno == ER_COL_COUNT_DOESNT_MATCH_CORRUPTED ||
+        sql_errno == ER_CANNOT_LOAD_FROM_TABLE)
       return true;
     return false;
   }

This patch however is only a work around, a better solution could be to reorder the statements executed during install/upgrade.
[30 Nov 2010 10:59] Davi Arnaut
Nice analysis Marc, thanks!
[30 Nov 2010 11:22] Marc ALFF
See related:

Bug #57663 Concurrent statement using stored function and DROP DATABASE
           breaks SBR
[30 Nov 2010 12:15] Ståle Deraas
Thanks you for the analysis Marc. Great work! Reassigning bug.
[30 Nov 2010 17:49] Nirbhay Choubey
The latest patch solves the problem mentioned in the
bug report.

Verified with 5.1.53 ---> 5.5.8
[30 Nov 2010 17:55] Jon Olav Hauglid
Patch pushed to the mysql-5.5.8-release tree.
[8 Dec 2010 19:49] Paul DuBois
Noted in 5.5.8 changelog.

mysql_upgrade failed after an upgrade from MySQL 5.1.