Bug #14337 (proc/func table != myisam) => unreliable results

• Submitted: 26 Oct 2005 17:23
• Modified: 27 Oct 2005 19:34
• Reporter: Wojciech Hlibowicki
• Status: Unsupported
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.15
• OS: Linux (Linux (Fedora))
• CPU Architecture: Any

[26 Oct 2005 17:23] Wojciech Hlibowicki

Description:
If you convert the tables: proc, procs_priv to InnoDB, then MySQL will not see the stored functions/procedures when you connect to the server (at first) or may execute the newest function instead of the correct one.

I changed those tables to InnoDB because I have an issue with my system and MyISAM tables, for some reason MyISAM tables lock randomly, and will not release the lock, and a proper shutdown of MySQL is not possible then. (have to resort to kill -9). 

Due to this, my server has crashed when creating stored procedures (not 100% sure if that was the reason, since the tables get randomly locked, and I really do not want to test it on a production server)

How to repeat:
use mysql;
ALTER TABLE proc ENGINE=INNODB;
ALTER TABLE procs_priv ENGINE=INNODB;

use testdb;
DELIMITER //
CREATE FUNCTION foo() RETURNS TINYINT(4) 
BEGIN
 RETURN 1;
END //

CREATE FUNCTION foo2() RETURNS TINYINT(4) 
BEGIN
 RETURN 0;
END //

DELIMITER ;

select foo();
select foo();

exit;
(reconnect)
use testdb;
select foo();
select foo2();

(note, when you have only one stored procedure, it tends to say the procedure does not exist (ie. only create foo()))

[26 Oct 2005 17:24] Wojciech Hlibowicki

Here are the results I get for the last two selects:

Database changed
mysql> select foo2();
+--------+
| foo2() |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

mysql> select foo();
+-------+
| foo() |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

[26 Oct 2005 23:12] Jorge del Conde

I was able to repeat this using 5.0.16bk:

mysql> select foo();
+-------+
| foo() |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> select foo2();
+--------+
| foo2() |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

[27 Oct 2005 19:34] Sergei Golubchik

Having system tables in anything but MyISAM is not supported.

[27 Oct 2005 19:45] Wojciech Hlibowicki

Then perhaps disable the ability to convert system tables to anything other than MyISAM?