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: | |
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
[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.