MySQL Bugs: #41726: upgrade from 5.0 to 5.1.30 crashes if you didn't run mysql

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	CPU Architecture:	Any

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"

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 *%'

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

bug #32828 was marked as a duplicate of this

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.

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.

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.

Queued to 5.1-bugteam

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)

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+.

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)

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)

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)

Noted in 5.5.1, 6.0.14 changelogs.

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)

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)

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)

bug #48862 is a duplicate of this.