MySQL Bugs: #13587: Server crash when SP is created without database selected

Bug #13587	Server crash when SP is created without database selected
Submitted:	28 Sep 2005 21:40	Modified:	20 Oct 2005 6:59
Reporter:	Paul Zentner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.13-rc-standard-log, 5.0.13-rc-nt	OS:	Linux (Linux, Solaris 9 64 bit, Windows)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Trying to upgrade 4.1.9 to 5.0.13 after bringing down the 4.1.9 server modified environment pointing to 5.0.13 code, started the database under 5.0.13 log follows:

more $ALERT
050928 14:23:17  mysqld started
050928 14:23:19  InnoDB: Started; log sequence number 0 219046
050928 14:23:19 [Note] /usr/local/mysql-standard-5.0.13-rc-solaris9-sparc-64bit/bin/mysqld: ready for connections.
Version: '5.0.13-rc-standard-log'  socket: '/tmp/mysql-3305.sock'  port: 3305  MySQL Community Edition - Standard (GPL)

cd /usr/local/mysql50/bin
./mysql_fix_privilege_tables --verbose --socket=/tmp/mysql-3305.sock --password=########

Log Follows:This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, stored procedures, or
more secure passwords in 4.1

You can safely ignore all 'Duplicate column' and 'Unknown column' errors
because these just mean that your tables are already up to date.
This script is safe to run even if your tables are already up to date!

ERROR 1060 (42S21) at line 44: Duplicate column name 'File_priv'
ERROR 1060 (42S21) at line 57: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 58: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 59: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 70: Duplicate column name 'ssl_type'
ERROR 1054 (42S22) at line 109: Unknown column 'Type' in 'columns_priv'
ERROR 1060 (42S21) at line 115: Duplicate column name 'type'
ERROR 1060 (42S21) at line 125: Duplicate column name 'Show_db_priv'
ERROR 1060 (42S21) at line 142: Duplicate column name 'max_questions'
ERROR 1060 (42S21) at line 152: Duplicate column name 'Create_tmp_table_priv'
ERROR 1060 (42S21) at line 155: Duplicate column name 'Create_tmp_table_priv'
ERROR 1061 (42000) at line 163: Duplicate key name 'Grantor'
done

Cycled the server:

Tried to open a database on server 3305 under EMS Manager 3.4.0.4 got message "Table mysql.proc doesn't exist"

Then when trying to create a procedure from mysql client on server:
mysql> delimiter //
mysql> CREATE PROCEDURE `new_proc`(OUT param1 INTEGER(11))
    -> NOT DETERMINISTIC
    -> SQL SECURITY DEFINER
    -> COMMENT ''
    -> BEGIN
    -> SELECT COUNT(*) into param1 from mysql.user;
    -> END 
    -> //
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 050928 15:36:52  mysqld restarted

With No database was selected:

If database is selected we get:

mysql> use mysql;
Database changed
mysql> delimiter //
mysql> CREATE PROCEDURE `new_proc`(OUT param1 INTEGER(11))
    ->     NOT DETERMINISTIC
    ->     SQL SECURITY DEFINER
    ->     COMMENT ''
    -> BEGIN
    ->  SELECT COUNT(*) into param1 from mysql.user;
    -> END
    -> //
ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist
mysql> exit

I cab provide the mysql err log if required.

How to repeat:
Upgrade a 4.1.9 Server to 5.0.13

Are you verified if actually the mysql.proc table not exist on your
mysql database and after to run the script you stopped and re-started
the server ?

Yes I cycled the server following is a show tables;
mysql> select version();
+------------------------+
| version()              |
+------------------------+
| 5.0.13-rc-standard-log |
+------------------------+
1 row in set (0.00 sec)

mysql> show tables\G
*************************** 1. row ***************************
Tables_in_mysql: columns_priv
*************************** 2. row ***************************
Tables_in_mysql: db
*************************** 3. row ***************************
Tables_in_mysql: func
*************************** 4. row ***************************
Tables_in_mysql: help_category
*************************** 5. row ***************************
Tables_in_mysql: help_keyword
*************************** 6. row ***************************
Tables_in_mysql: help_relation
*************************** 7. row ***************************
Tables_in_mysql: help_topic
*************************** 8. row ***************************
Tables_in_mysql: host
*************************** 9. row ***************************
Tables_in_mysql: tables_priv
*************************** 10. row ***************************
Tables_in_mysql: time_zone
*************************** 11. row ***************************
Tables_in_mysql: time_zone_leap_second
*************************** 12. row ***************************
Tables_in_mysql: time_zone_name
*************************** 13. row ***************************
Tables_in_mysql: time_zone_transition
*************************** 14. row ***************************
Tables_in_mysql: time_zone_transition_type
*************************** 15. row ***************************
Tables_in_mysql: user
15 rows in set (0.01 sec)

I think the problem is related to the upgrade procedure from 4.1.9 to 5.0.13 only... Looks like ./mysql_fix_privilege_tables is broken (it was once...).

Please, try to perform a fresh install of 5.0.13 (in a new data directory, according to the manual) and try to create your procedure, both with database selected and without. Please, inform us about the results. It will help to isolate the bugs more precisely, if both upgrading procedure is faulty and new version has problem with stored procedures.

OK looks like the mysql_install_db works well:

# ./scripts/mysql_install_db
Installing all prepared tables
Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h odin password 'new-password'
See the manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com

mysql> use mysql;
Database changed
mysql> show tables\G
*************************** 1. row ***************************
Tables_in_mysql: columns_priv
*************************** 2. row ***************************
Tables_in_mysql: db
*************************** 3. row ***************************
Tables_in_mysql: func
*************************** 4. row ***************************
Tables_in_mysql: help_category
*************************** 5. row ***************************
Tables_in_mysql: help_keyword
*************************** 6. row ***************************
Tables_in_mysql: help_relation
*************************** 7. row ***************************
Tables_in_mysql: help_topic
*************************** 8. row ***************************
Tables_in_mysql: host
*************************** 9. row ***************************
Tables_in_mysql: proc
*************************** 10. row ***************************
Tables_in_mysql: procs_priv
*************************** 11. row ***************************
Tables_in_mysql: tables_priv
*************************** 12. row ***************************
Tables_in_mysql: time_zone
*************************** 13. row ***************************
Tables_in_mysql: time_zone_leap_second
*************************** 14. row ***************************
Tables_in_mysql: time_zone_name
*************************** 15. row ***************************
Tables_in_mysql: time_zone_transition
*************************** 16. row ***************************
Tables_in_mysql: time_zone_transition_type
*************************** 17. row ***************************
Tables_in_mysql: user
17 rows in set (0.01 sec)

Create procedure without database selected:

mysql> DELIMITER $
mysql> CREATE PROCEDURE PAUL (OUT TBLROWS INTEGER(11))
    ->     DETERMINISTIC
    ->     SQL SECURITY INVOKER
    ->     COMMENT ''
    -> BEGIN
    ->      select count(*) into TBLROWS from petstore.pet;
    -> END
    -> $
ERROR 2013 (HY000): Lost connection to MySQL server during query

create procedure with database selected:

mysql> use petstore;
Database changed
mysql> use mysql;
Database changed
mysql> DELIMITER $
mysql> CREATE PROCEDURE PAUL (OUT TBLROWS INTEGER(11))
    ->     DETERMINISTIC
    ->     SQL SECURITY INVOKER
    ->     COMMENT ''
    -> BEGIN
    ->      select count(*) into TBLROWS from petstore.pet;
    -> END
    -> $
Query OK, 0 rows affected (0.00 sec)

I change bug description to the more appropriate one.

Verified on Windows:

Your MySQL connection id is 5 to server version: 5.0.13-rc-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER $
mysql> CREATE PROCEDURE PAUL (OUT TBLROWS INTEGER(11))
    ->      DETERMINISTIC
    ->      SQL SECURITY INVOKER
    ->      COMMENT ''
    -> BEGIN
    ->    select count(*) into TBLROWS from petstore.pet;
    -> END
    -> $
ERROR 2013 (HY000): Lost connection to MySQL server during query

And on Linux (debx86):

Your MySQL connection id is 1 to server version: 5.0.13-rc

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER $
mysql> CREATE PROCEDURE PAUL (OUT TBLROWS INTEGER(11))
    ->      DETERMINISTIC
    ->      SQL SECURITY INVOKER
    ->      COMMENT ''
    -> BEGIN
    ->    select count(*) into TBLROWS from petstore.pet;
    -> END
    -> $
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
050930 19:54:06  mysqld restarted

mysql> exit
Bye
vkravchuk@debx86:~/dbs/5.0-debx86$ cat var/*.err
...
050930 19:53:41  mysqld started
050930 19:53:42  InnoDB: Started; log sequence number 0 43655
050930 19:53:42 [Note] /users/vkravchuk/dbs/5.0-debx86/libexec/mysqld: ready for  connections.
Version: '5.0.13-rc'  socket: '/tmp/mysql.sock'  port: 0  Source distribution
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8979d90
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbe5fe8d8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x815c06f
0x40056825
0x83c10df
0x8174d16
0x8178160
0x816e363
0x816dead
0x816d262
0x40050e51
0x402a092a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x89a4b98 = CREATE PROCEDURE PAUL (OUT TBLROWS INTEGER(11))
     DETERMINISTIC
     SQL SECURITY INVOKER
     COMMENT ''
BEGIN
   select count(*) into TBLROWS from petstore.pet;
END
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
050930 19:54:06  mysqld restarted
050930 19:54:06  InnoDB: Started; log sequence number 0 43655
050930 19:54:06 [Note] /users/vkravchuk/dbs/5.0-debx86/libexec/mysqld: ready for  connections.
Version: '5.0.13-rc'  socket: '/tmp/mysql.sock'  port: 0  Source distribution
vkravchuk@debx86:~/dbs/5.0-debx86$

Any incorrect SQL should not crash server, so it is a bug.

Given that the mysql_fix_privilege_tablesseems to be broke how would one upgrade from 4.1 to 5.0 ?

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/31251

Fixed in 5.0 tree, currently tagged 5.0.16

Note for documentation: see Bug#13514 for a shorter synopsis and explanation.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix in 5.0.16 changelog. Closed.