Bug #26119 Stored Procedures Not Visible
Submitted: 6 Feb 2007 16:40 Modified: 22 Feb 2007 15:17
Reporter: Rolando Edwards Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version:1.2.9 OS:Windows (Windows x64)
Assigned to: CPU Architecture:Any

[6 Feb 2007 16:40] Rolando Edwards
Description:
Query Browser 1.2.9 rc and MySQL Administrator 1.2.9 cannot view stored procedures against MySQL 5.0.27-community-max-nt running on Windows x64.

I used 'mysqldump --triggers --routines' against a database on MySQL 5.0.27-max-log running on Linux and redirected it to MySQL 5.0.27-community-max-nt running Windows x64. Stored Procedures Could Not Be Seen.

You could create stored procedures by hand in QB and MySQLAdmin and those would be visible. All the stored proecdures imported via mysqldump are there if you goto mysql client and run 'SHOW FUNCTION STATUS' or 'SHOW PROCEDURE STATUS'.

I uninstalled MySQL 5.0.27-community-max-nt and installed MySQL 5.0.26-community-max-nt. Stored Procedures were totally visible.

How to repeat:
Install these at a Windows x64 or NT-basaed machine
MySQL 5.0.27-community-max-nt 
Query Browser 1.2.9 rc
MySQL Administrator 1.2.9

Load data into it via mysqldump

Try to find catalog of stored proecdures

Ihave repeated this three times before going
back to MySQL 5.0.26-community-max-nt 

Suggested fix:
Look through code of mysqldump for any anomalies related to reading mysqldumps from MySQL 5.0.26 and prior into MySQL 5.0.27 

Look through code of Query Browser 1.2.9 rc and MySQL Administrator 1.2.9 to see if any 5.0.27 features were overlooked as well
[7 Feb 2007 13:42] Valeriy Kravchuk
Thank you for a problem report. What user are you connecting as with QB? Have your tried to connect as root? Do you use 32-bit version of MySQL 5.0.27 on your 64-bit Windows?
[7 Feb 2007 14:16] Rolando Edwards
I logged as a user with everything a root user should has, i.e. 'Y' to every '_priv' field in mysql.user. I ran 'flush privileges' to make sure.

I installed from the Windows downloads (x86) section of your download page.
[7 Feb 2007 16:40] Valeriy Kravchuk
And what happens when you log in as root into QB? Can you see the procedures? What user name was used when you loaded these procedures from mysqldump? Can you upload some procedures from your mysqldump to check?
[7 Feb 2007 18:19] Rolando Edwards
This is how I moveed just the stored procedures

mysqldump -h... -urolando -p... --no-create-info --no-data NDW > SP.sql
mysql -h... -urolando -p... -A -DNDW < SP.sql

root had all privileges (Y on every _priv) and host='%'
so did rolando
[7 Feb 2007 18:21] Rolando Edwards
Correct on the last entry

I also had --triggers --routines on the mysqldump
[8 Feb 2007 14:46] Rolando Edwards
I am going to try again today to reinstall MySQL 5.0.27 onto my Windows x64 machine. I am starting suspect that mysqld is not the problem. I think the problem lies with mysqldumps from older versions.

Yesterday, I performed a mysqldump (software with 5.0.26-community-nt) with --no-create-info --no-data --routines against a database (5.0.24-standard)just to see if the stored procedures had a problem. I saw something strange. I noticed that the DELIMITER command had ;; not $$. I do not think ;; a valid DELIMITER string. I will investigate and get back with a response.
[15 Feb 2007 14:12] Valeriy Kravchuk
Any results from your further tests?
[15 Feb 2007 14:55] Rolando Edwards
I reinstalled 5.0.27 on the x64, used 5.0.27's mysqldump program against a 5.0.24 database to import the data into 5.0.27. The mysqldump (along with triggers and stored procedures) proved successful.

The original problem stemmed from using a 5.0.26 mysqldump to import 5.0.24 data into 5.0.27. That made the stored procedures seem to vanish because the code for inserting stored procedures simply did not work in this scenario. However, it was successful in the aforementioned scenario.

mysqldump apparently DOES NOT HAVE A PROBLEM.
It was the heterogenous way I was using mysqldump.
[20 Feb 2007 9:12] Valeriy Kravchuk
So now, after restoring your procedures properly on the server level, can you see them in Query Browser?
[20 Feb 2007 14:16] Rolando Edwards
YES
[22 Feb 2007 15:17] Rolando Edwards
I found the true underlying problem. It was not mysqldump at all.

Stored Procedures appeared me to 'disappear' when using 'mysqldump --routines' because the server I was loading the data onto had binary logging turned on and the majority of my stored functions did not have the DETERMINISTIC clause in the header of each stored function.

The quick and dirty solution was simple. I added this to my.ini
log_bin_trust_function_creators=1

All stores procedures have been loading cleanly ever since.

This bug can be closed forever more.