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

Submitted: 26 Oct 2005 17:23 Modified: 27 Oct 2005 19:34
Reporter: Wojciech Hlibowicki Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.15 OS:Linux (Linux (Fedora))
Assigned to: 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?

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.