Bug #41726 upgrade from 5.0 to 5.1.30 crashes if you didn't run mysql_upgrade
Submitted: 24 Dec 2008 6:56 Modified: 12 Mar 2010 17:45
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.1.30 OS:Any
Assigned to: Davi Arnaut
Triage: Triaged: D1 (Critical) / R2 (Low) / E3 (Medium)

[24 Dec 2008 6:56] Shane Bester
Description:
after upgrading to 5.1.30 and forgetting to run mysql_upgrade, simple queries issued against I_S or certain SHOW command - by MEM, MySQL Administrator, and users can lead to serious crashes.

14021CA3E    mysqld.exe!get_field()[table.cc:2578]
1401EED63    mysqld.exe!store_schema_proc()[sql_show.cc:4044]
1401EF49C    mysqld.exe!fill_schema_proc()[sql_show.cc:4115]
1401F770C    mysqld.exe!get_schema_tables_result()[sql_show.cc:5953]
1401E2326    mysqld.exe!JOIN::exec()[sql_select.cc:1713]
1401E3A2A    mysqld.exe!mysql_select()[sql_select.cc:2363]
1401E3F76    mysqld.exe!handle_select()[sql_select.cc:269]
14019C1B8    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4889]
14019D83D    mysqld.exe!mysql_execute_command()[sql_parse.cc:2136]
1401A2F06    mysqld.exe!mysql_parse()[sql_parse.cc:5791]
1401A3C1A    mysqld.exe!dispatch_command()[sql_parse.cc:1202]
1401A4CD7    mysqld.exe!do_command()[sql_parse.cc:857]
140246327    mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
1402B82C5    mysqld.exe!pthread_start()[my_winthread.c:85]
1403CAC37    mysqld.exe!_callthreadstart()[thread.c:295]
1403CAD05    mysqld.exe!_threadstart()[thread.c:275]
077D6B69A    kernel32.dll!BaseThreadStart()
to get some variables.
inters may be invalid and cause the dump to abort...
ery at 00000000042E3850=show procedure status

It's well documented that mysql_upgrade must be run after an upgrade.
However, we *should* try to not crash the server, and keep it more resilient.

How to repeat:
in 5.0.70, create the following procedure:

delimiter //
drop procedure if exists `p1` //
create procedure `p1`()
begin
drop temporary table if exists `t1`;
create temporary table `t1` as select now() as `a`;
update `t1` set `a`=null;
end //
delimiter ;

now shutdown 5.0.70 and copy the `mysql` database to 5.1.30.
start 5.1.30 and run "show procedure status"
[24 Dec 2008 6:57] Shane Bester
another query, from MEM that will cause this:

SELECT GROUP_CONCAT('\\\\\n* ', routine_schema, '.', routine_name, '()' ORDER BY routine_schema, routine_name) AS routine FROM information_schema.routines WHERE UPPER(routine_definition) LIKE '%SELECT *%'
[24 Dec 2008 7:24] Shane Bester
everybody upgrading from 5.0 to 5.1 must carefully read:
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html
[24 Dec 2008 7:32] Shane Bester
bug #32828 was marked as a duplicate of this
[6 Nov 2009 18:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89667

3201 Davi Arnaut	2009-11-06
      Bug#41726: upgrade from 5.0 to 5.1.30 crashes if you didn't run mysql_upgrade
      
      The problem is that the server could crash when attempting
      to access a non-conformant proc system table. One such case
      was a crash when invoking stored procedure related statements
      on a 5.1 server with a proc system table in the 5.0 format.
      
      The solution is to validate the proc system table format
      before attempts to access it are made. If the table is not
      in the format that the server expects, a message is written
      to the error log and the statement that caused the table to
      be accessed fails.
     @ mysql-test/r/sp-destruct.result
        Add test case result for Bug#41726
     @ mysql-test/t/sp-destruct.test
        Add test case for Bug#41726
     @ sql/event_db_repository.cc
        Update code to use new structures.
     @ sql/sp.cc
        Describe the proc table format and use it to validate when
        opening a instance of the table.
     @ sql/sql_acl.cc
        Remove unused variable and use new structure.
     @ sql/sql_acl.h
        Export field definition.
     @ sql/table.cc
        Accept the field count and definition in a single structure.
     @ sql/table.h
        Combine the field count and definition in a single structure.
        Add a pointer cache to TABLE_SHARE.
[20 Nov 2009 21:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91171

3212 Davi Arnaut	2009-11-20
      Bug#41726: upgrade from 5.0 to 5.1.30 crashes if you didn't run mysql_upgrade
      
      The problem is that the server could crash when attempting
      to access a non-conformant proc system table. One such case
      was a crash when invoking stored procedure related statements
      on a 5.1 server with a proc system table in the 5.0 format.
      
      The solution is to validate the proc system table format
      before attempts to access it are made. If the table is not
      in the format that the server expects, a message is written
      to the error log and the statement that caused the table to
      be accessed fails.
     @ mysql-test/r/sp-destruct.result
        Add test case result for Bug#41726
     @ mysql-test/t/sp-destruct.test
        Add test case for Bug#41726
     @ sql/event_db_repository.cc
        Update code to use new structures.
     @ sql/sp.cc
        Describe the proc table format and use it to validate when
        opening a instance of the table.
     @ sql/sql_acl.cc
        Remove unused variable and use new structure.
     @ sql/sql_acl.h
        Export field definition.
     @ sql/table.cc
        Accept the field count and definition in a single structure.
     @ sql/table.h
        Combine the field count and definition in a single structure.
        Transform function into a class in order to support different
        ways of reporting a error.
        Add a pointer cache to TABLE_SHARE.
[21 Nov 2009 11:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91205

3211 Davi Arnaut	2009-11-21
      Bug#41726: upgrade from 5.0 to 5.1.30 crashes if you didn't run mysql_upgrade
      
      The problem is that the server could crash when attempting
      to access a non-conformant proc system table. One such case
      was a crash when invoking stored procedure related statements
      on a 5.1 server with a proc system table in the 5.0 format.
      
      The solution is to validate the proc system table format
      before attempts to access it are made. If the table is not
      in the format that the server expects, a message is written
      to the error log and the statement that caused the table to
      be accessed fails.
     @ mysql-test/r/sp-destruct.result
        Add test case result for Bug#41726
     @ mysql-test/t/sp-destruct.test
        Add test case for Bug#41726
     @ sql/event_db_repository.cc
        Update code to use new structures.
     @ sql/sp.cc
        Describe the proc table format and use it to validate when
        opening a instance of the table.
        Add a check to insure that a error message is written to
        the error log only once.
     @ sql/sql_acl.cc
        Remove unused variable and use new structure.
     @ sql/sql_acl.h
        Export field definition.
     @ sql/table.cc
        Accept the field count and definition in a single structure.
     @ sql/table.h
        Combine the field count and definition in a single structure.
        Transform function into a class in order to support different
        ways of reporting a error.
        Add a pointer cache to TABLE_SHARE.
[21 Nov 2009 11:29] Davi Arnaut
Queued to 5.1-bugteam
[2 Dec 2009 8:04] Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)
[3 Dec 2009 1:09] Paul Dubois
Noted in 5.1.42 changelog.

The server could crash when attempting to access a non-conformant
mysql.proc system table. For example, the server could crash when
invoking stored procedure-related statements after an upgrade from
MySQL 5.0 to 5.1 without running mysql_upgrade. 

Setting report to NDI pending push to 5.6.x+.
[16 Dec 2009 8:38] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:45] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:51] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[16 Dec 2009 15:45] Paul Dubois
Noted in 5.5.1, 6.0.14 changelogs.
[12 Mar 2010 14:11] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:26] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:41] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[19 Mar 2010 12:58] Shane Bester
bug #48862 is a duplicate of this.