Bug #11365 Stored Procedure: Crash on Procedure operation
Submitted: 15 Jun 2005 22:48 Modified: 12 Jul 2005 19:27
Reporter: Shuichi Tamagawa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.8-beta-debug (bk Jun 20) OS:Linux (SuSE linux 9.2)
Assigned to: Bugs System

[15 Jun 2005 22:48] Shuichi Tamagawa
Description:
Server crashes on any procedure operation after creating stored procedure.

e.g) call, show create procedure, drop procedure

How to repeat:
1.Create a procedure like below:

---

delimiter //

begin
 declare tmp1,tmp2,tmp3,tmp4,tmp5,tmp6,tmp7,tmp8 varchar(8);
 set @x = 0;
 repeat
  select sql_no_cache c1,c2,c3,c4,c5,c6,c7,c8
  into tmp1,tmp2,tmp3,tmp4,tmp5,tmp6,tmp7,tmp8
  from t1 where c1 = cast(concat('AAA',right(concat('0000',@x),5)) as char character set binary);
  set @x = @x + 1;
  until @x >= i
 end repeat;
end
//

---

2.Call the procedure:

call sp_select(100000)
[15 Jun 2005 22:50] Shuichi Tamagawa
Error Log

Attachment: linux.err (application/octet-stream, text), 5.03 KiB.

[15 Jun 2005 22:51] Shuichi Tamagawa
Result of stack trace

Attachment: stacktrace.txt (text/plain), 89 bytes.

[15 Jun 2005 22:53] Shuichi Tamagawa
configuration option (mysqlbug)

Attachment: mysqlbug (application/octet-stream, text), 10.74 KiB.

[15 Jun 2005 22:55] Shuichi Tamagawa
I've attached
 - Error log
 - Resolved stack trace
 - mysqlbug
[16 Jun 2005 19:21] Shuichi Tamagawa
The procedure to reproduce this can be very simple one.

mysql> create procedure foo42()
    ->   insert into test.t1 values ("foo", 42);
Query OK, 0 rows affected (0.07 sec)

mysql> show create procedure foo42;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
050616 12:13:16  mysqld restarted
[16 Jun 2005 22:10] Shuichi Tamagawa
OK, I found out that this happens when the character set of mysql database is multi-byte characterset.

Here is simple way to repeat this.

1. Add the line below in [mysqld] section of my.cnf file

default-character-set = ujis

2. Run mysql_install_db

3. start mysql server, login, and execute the following statement

mysql>show create procedure sp1;
[21 Jun 2005 13:38] Hartmut Holzgraefe
I can't reproduce this using a bk Jun 18 build?
[21 Jun 2005 18:47] Shuichi Tamagawa
Hi Hartmut,

Thanks for taking look at this.

Actually, I can reproduce using bk Jun 20 build. How did you try to reproduce this? The 'How to repeat'  in my first post is not correct, and my post on Jun 17 describes the right procedure to reproduce this. This crash doesn't happen if the character set of 'mysql' database is single byte like latin1.

Regards,
[21 Jun 2005 18:53] Tetsuro Ikeda
I reproduced this crush by following step.

1. Download the MySQL 5.0.7beta binary distribution for Linux x86 max

2. Extract the archive and copy it to the /usr/local/mysql

3. Write a new my.cnf file that have the below and put it in the data directory
    [mysqld]
    default-character-set = ujis

4. Run the install script

5. Start the server

6. Login by using mysql client

7. execute the command "show create procedure sp1;" although procedure is not created yet.

And then, server crushed with error which is almost same one of uploaded file which shuichi did.

When I skipped the step3, server didn't crush and returned the correct message which means "sp1" is not created yet.

Thanks!!
[25 Jun 2005 16:01] Jorge del Conde
I was unable to reproduce this bug using 5.0.7

Testing w/5.0.8 from bk ...
[2 Jul 2005 0:50] Shuichi Tamagawa
This is reproducible when the character set of mysql.proc table is multi-byte. Try "SHOW CREATE TABLE mysql.proc;" and check the character set on your environment.

If the character set is 'latin1', which is default, or another single-byte character set, try "ALTER TABLE CONVERT TO CHARACTER SET ****;", where *** is multi-byte character set like utf8,sjis,ujis,big5, etc. And try "call sp1()". again. It should crash the server.

Note:
I looked at bin/mysql_create_system_tables, and found that the "CREATE TABLE proc" statement doesn't specify the character set. So the table's character set is affected by the option in my.cnf. I think this is why this problem is not reproducible depending on the environment.

You could just add a "CHARACTER SET latin1" to the statement to avoide this problem, but seems like a kludge...
[8 Jul 2005 13:05] Andrey Hristov
Related to #11669 (if not the same).
[11 Jul 2005 5:52] 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/internals/26832
[11 Jul 2005 10:57] Alexander Barkov
I think the patch is ok to push.
[11 Jul 2005 17:48] Per-Erik Martin
Pushed to 5.0.10.

This changes the character sets for the mysql.proc table. For this to take effect
in existing databases, the table must be updated either manually, or using the
mysql_fix_privilege_tables script (or mysql_fix_privilege_tables.sql).
[12 Jul 2005 19:27] Paul Dubois
Noted in 5.0.10 changelog.