MySQL Bugs: #12820: Unable to use UDFs and Stored Procs in 5.0.11 (after upgrading from 5.0.9)

Bug #12820	Unable to use UDFs and Stored Procs in 5.0.11 (after upgrading from 5.0.9)
Submitted:	26 Aug 2005 1:44	Modified:	2 Sep 2005 16:34
Reporter:	Yuri Sprague	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S1 (Critical)
Version:	5.0.11	OS:	Windows (Windows Server 2003, XP, XP SP2)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
I'm unable to see or use any of the functions and stored procedures I've defined.   They don't display in Query Browser and when I try to call them from a C# app I'm developing, I get an error message that they don't exist.  I uninstalled 5.0.11 and reinstalled 5.0.9 and everything reappeared in Query Browser and the app ran.  Unfortunately, I wanted to upgrade to 5.0.11 because of the LOCK TABLES error that occurs when I try to use an SP from 5.0.9 (see bug #10497), so I'm now back to square one and unable to proceed until this is resolved.  According to the comment made by Dmitri Lenev [8 Aug 17:05] in the bug #10497 notes, the LOCK TABLES error was fixed in 5.0.11, but how can I test this if 5.0.11 doesn't recognize the SPs?  

How to repeat:
Install server version 5.0.11, check Query Browser for any UDFs or SPs that are installed within a schema.  Try calling them from a SQL command line.

Looks OK for me...

Attachment: 12820.png (image/png, text), 51.97 KiB.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

I tried to create one of the SPs you mentioned on the newly installed 5.0.11, in an empty test schema, using Query Browser 1.1.13 on XP. It was created successfully and is visible in Query Browser. See the screenshot I made for you in the Files tab.

Instead of XP, try using Win 2003.  It may be endemic to the OS.  I'm using Query Browser 1.13 as well, and, as I already stated, the SP's already existed when I installed 5.0.11, so perhaps you need to test by creating an SP in a previous server version, say 5.0.9, and then installing 5.0.11.  I reinstalled 5.0.11 and tried creating a very simple SP and it caused the server to crash.  I repeated it several times with the same result each time.  Perhaps it's the OS, Win 2003, that's causing the problem.  Please test this.  Since my PC (which has XP Pro) is out of order right now, I can't test there, but I will test this on my laptop, which is running XP Home and report back to you.

I installed 5.0.11 on my XP Home laptop.   I could create a new SP without problem, but I still could NOT see or use any of the SPs and UDFs created in a previous server version (5.0.9 or earlier; I've never used 5.0.10).   If I tried using a view, e.g., vw_Loans, that called any of the functions created in an earlier server message, I received error 1356, "View 'iq.vw_Loans' references invalid table(s) or column(s) or function(s)."  I then uninstalled 5.0.11 and reinstalled 5.0.9.  When I reopened Query Browser all the SPs and UDFs were there and worked properly.  Thus, the problem seems to lie with SPs and UDFs created in a previous server version to 5.0.11.  As I mentioned previously, I also received an error when I tried to create an SP in 5.0.11 running on a Win 2003 server and this may be a (separate) bug as well; it certainly seems to warrant further investigation.

I've got your point. You can not see SPs created in 5.0.9 after upgrading it to 5.0.11. Have you read the manual part on upgrading? I mean:

http://dev.mysql.com/doc/mysql/en/upgrading-from-5-0.html

Please, run mysql_fix_privilege_tables.sql script from mysql.exe after upgrading, as described here:

http://dev.mysql.com/doc/mysql/en/upgrading-grant-tables.html

and check the availability of your old stored procedures again.

I do not think this is an OS version specific issue.

I ran the update as described in the manual.  It only allows you to create NEW procedures.  I still can't see the SPs created in a previous server version.  BTW, I found and fixed a syntax error in mysql_fix_privilege_tables.sql.  I guess I should report that...

hi,
i've got the same problem. I'm using win xp with service pack 2 .it is not only in the query browser but also from mysql client. i can not also create a new stored procedure. it gives me an error message when creating an SP and then the server crashes. it can not reconnect to the server 'cause the server can not restart itself.

the error message i get is :
"mysqld-nt.exe application error: 
The instruction at "0x0052e646" referenced memory at "0x20202020". the memory could not be read."

Does it happen in 5.0.10 also? 
code of SP i want to create:
"create procedure setest2 () 
begin 
select * from test ; 
end 
" 

when i use "select name from mysql.proc" it gives me a result set of strange characters ( "☺☺☻☻-" which the  ☺s are smilies)
but the number of rows of result set is the number of my SPs(i have restored my DB from an older version.)
and when i use "select specific_name from information_schema.routines" it gives me the same number of rows which contain nothing(not null).

What is this error for? i've upgraded to this version 'cause i wanted to use triggers. what can i do now?

Regards
Chagh

Hi Yury !

Could you please write here your changes of mysql_fix_privilege_tables.sql ?

hi,
it seems this happens for 5.0.10 also. would yoou please test it also?

Chagh,

Have you tried running mysql_fix_privilege_tables.sql?  (See Vasiliy's previous notation.)  That should enable you to create NEW SP's in 5.0.11.

Vasiliy,

The change is minor and comes near the end of the script.  It's at the end of an ALTER TABLE command:

"# Correct the name fields to not binary, and expand sql_data_access
ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,...".

The last statements are,
"                           ) DEFAULT 0 NOT NULL
                     DEFAULT CHARACTER SET utf8;".

I'm sure this is generated from a template, depnding on the char set selected.  Anyway, there should be a comma immediately after "NOT NULL" , preceding the "DEFAULT CHARACTER SET" statement.  That's it.  Very easy to fix.

hi Yuri,
yes i did but it gave me syntax errors.
i installed 5.0.10 then used: "source mysql_fix_privilege_tables" but it gave me syntax error.(i don't have the syntax error). 
what is the memory error for?
and would you please tell me when this bug is gone be removed? it is critical to my work to know when the next release, which has not this problem, comes.

Thanks 
Chagh

Chagh,

You can safely ignore the table, view, etc. already exists messages.  See my note to Valeriy about the syntax error I found.  It might be the same thing you experienced.

Chagh,

I hope this bug is resolved quickly as well, but since I'm not a MySQL engineer - I'm just the guy who reported the problem - I can't tell you when this will be fixed.  Very soon, I hope.  It's a big thorn in my side.

I was finally able to repeat exactly what you had written about. On XP and XP SP2, on 2 different machines.

Use the following scenario:

1. Istall 5.0.9, then connect as root and create a siplest procedure:

mysql> use test
Database changed
mysql> delimiter //
mysql> create procedure p1()
    -> begin
    ->   set @a = 1;
    -> end;
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> select @a;
+------+
| @a   |
+------+
| NULL |
+------+
1 row in set (0.01 sec)

mysql> call p1();
Query OK, 0 rows affected (0.01 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> select routine_name from information_schema.routines;
+--------------+
| routine_name |
+--------------+
| p1           |
+--------------+
1 row in set (0.05 sec)

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.9-beta-nt |
+---------------+
1 row in set (0.01 sec)

So, it works, everything is OK.

2. Install 5.0.11 into the same directory

(Reconfigure Instance when prompted). Then, connect as root and:

mysql> use test
Database changed
mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.11-beta-nt |
+----------------+
1 row in set (0.00 sec)

mysql> call p1();
ERROR 1305 (42000): PROCEDURE test.p1 does not exist

mysql> use mysql
Database changed
mysql> select routine_name from information_schema.routines;
+--------------+
| routine_name |
+--------------+
|          |
+--------------+
1 row in set (0.07 sec)

So, p1 is no longer available (and I saw the smiles you talked about). Oh, but I forgot the upgrading procedure described in http://dev.mysql.com/doc/mysql/en/upgrading-grant-tables.html. Let me use it:

mysql> source c:/Program Files/MySQL/MySQL Server 5.0/scripts/mysql_fix_privilege_tables.sql
Query OK, 0 rows affected (0.00 sec)

--- skipped ---

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8' at line 42
Query OK, 1 row affected, 1 warning (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 1

Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

The bug (#1, in the script, you had written about). Let me make the change you proposed. I changed line 512 of the script (added comma) and rerun the script. It worked better. But let us see the results:

mysql> select routine_name from information_schema.routines;
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
ERROR:
Can't connect to the server

So, server crashed! (bug #2). Let me restart it:

net start MySQL5

Then:

mysql> use test
Database changed
mysql> call p1();
ERROR 1305 (42000): PROCEDURE test.p1 does not exist
mysql> select routine_name from information_schema.routines;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Server crashed!

Nothing special in the .err log (server starts only), by the way.

You have to make a dump in 5.0.9, delete a data directory completely, and install 5.0.11 "fresh", then you'll be able to recreate you databases, including procedures. That is the only workaround I can propose for now.

Thank you for your bug report and your patience.

Thanks!  I dropped the UDFs and SPs, installed 5.0.11 and then reloaded all the UDFs and SPs.  A bit tedious, but it worked.

The bug was triggered by the following sequence of events:
1. mysql 5.0.9 (or earlier) is installed: 
1.1 Installer puts proc.frm, proc.MYI, proc.MYD into $mysqldir/data/mysql)

2. user creates some stored procedures. This cause proc.MYI and proc.MYD to change but proc.frm is not changed (this is expected behavior)

3. 5.0.11 version version is installed. The installer does this:
3.1 Don't replace proc.MYI and proc.MYD files as they have been modified.
3.2 Do replace proc.frm file from 5.0.6 with proc.frm file from 5.0.11 (proc.frm file is different in 5.0.11 as definition of mysql.proc table has changed)

At this point we have proc.frm file that doesn't match proc.MYI and proc.MYD files. This causes the server to crash/produce garbage output.

Mike Zinner has made appropriate fixes in the windows installer. The fix will available in  5.0.13.

If anyone has already lost access to his stored procedures because of this, here is how to get them back:
 * Stop the server.
 * Go to <MySQL install dir>\data\mysql, and replace proc.frm file with corresponding from the version of MySQL you were upgrading from.
 * Start the server
 * Start command-line client (with full database privileges) and run 
    source <MySQL install dir>/scripts/mysql_fix_privileges.sql
After this all stored procedures functionality should work.

Noted in 5.0.13 changelog.