'ignore Definer' Is Ignored When Copying Procedure To Another

[iceman]

Firstly, when creating a procedure using

CREATE  PROCEDURE age_frequency_count(IN bin_size INT)

The resulting procedure gets the definer placed in it whether I like it or not, resulting in

CREATE DEFINER=@ PROCEDURE `age_frequency_count`(IN bin_size INT)

This seems to cause problems when copying the procedure to another database for backup purposes using Database | Copy Database to a different Host.Database  (The host in my case is the same but it’s a different connection to a database with a different name and with a different username.

 

The four scenarios are below.

All have the procedure manually deleted in the backup database before attempting the copy.

 

I would expect he error in scenarios 1 and 3 as the user name in the definer would be wrong for the backup database but surely scenario 4 should work as the definer should be ignored (as it is in scenario 2). Additionally it should work whether I manually delete the procedure in the target first or not as I am asking for it to be dropped first anyway.

 

Qn

How can I make the Copy Database… work correctly without having to manually delete all the procedures and functions in the backup database first?

 

Qn

Is there a way to stop the definer being inserted into the procedure in the first place, or is that being done by the database and not by SQLYog?

 

1)

‘drop if exists in target’ is not ticked

‘ignore definer’ is not ticked

Error 1227 Access denied; you need (at least one of) the SUPER privilege(s) for this operation

 

2)

‘drop if exists in target’ not ticked

‘ignore definer’ is ticked
Success The procedure is created in the backup database

 

3)

‘drop if exists in target’ is ticked

‘ignore definer’ is not ticked

Error 1227 Access denied; you need (at least one of) the SUPER privilege(s) for this operation

4)

‘drop if exists in target’ is ticked

‘ignore definer’ is ticked

Error 1227 Access denied; you need (at least one of) the SUPER privilege(s) for this operation          ??????????

[Sibin A S]

Hi,

 

“The host in my case is the same but it’s a different connection to a database with a different name and with a different username.”

 

Based on your inputs, consider the case having two connections i.e, ‘Connection A’ and ‘Connection B’ to the same host, when an object is created in ‘Connection A’, it also gets reflected in ‘Connection B’. So while copying the object, it would return that the object already exists.

 

Can you please clarify this and also give us the test case where we can reproduce it and please elaborate more. Also please tell us the Privileges for both the connections.

[iceman]

I think maybe I wasn’t very clear. I have lots of different ‘connections’ set up within SQLYog, each going to a different database. Some of these  are hosted with the same ISP. The two in question are a development database and a database that I use as a backup (hence the need to copy things over to it).

 

My development database is hosted by the Siteground ISP on their server. I log onto it over port 3306 using the database name, my user name and my user password. Call this database A.

 

I also have a second database hosted at Siteground, on the same server. Call that one B. That one is also reached over port 3306 but whilst it uses the same host address, it has a different database name, a different user name and a different password. The schema of database B is the same, more or less, as database A.

 

If I make changes to the development database, (database A) I will often open the second database (database B ) in a different connection tab within SQLYog, then I copy any new objects across from database A to database B in order to maintain a backup.

 

The steps to create the error

I made a new procedure in database A using one of my connection tabs. I then used File|New connection and selected my second database from the saved ones and opened it in another tab

 

I used Database | Copy Database to a different Host/Database, unticked every object except the new procedure and selected my second database as the target. I unselected ‘drop if exists in target’ (as it didn’t exist in there yet) but selected ‘ignore definer (because otherwise the username from database A would not be recognised in database B. This worked OK and created the procedure in database B with the definer created by MySQL correctly showing the username for database B.

 

I then altered the procedure in database A and tried to copy the new version over to database B. This time I selected both ‘drop if exists in target’ (as the old version of the procedure now did exist in database B ) and also ‘ignore definer’ for the same reason as before.

 

However, although it did drop the procedure in database B, the DEFINER from the procedure in database A was not ignored, in spite of the ‘ignore definer’ box being selected. Hence SQLYog tried to create a procedure in database B with the wrong username and error 1227 Access denied was raised. The message box in SQLYog showed that the SQL it was trying to run did still include the definer from database A. So, as it contained the user name for for database A this was denied access in database B.

 

Since it had already dropped the procedure before attempting the copy it should have performed exactly the same as the first time when the procedure was not there anyway.

 

In summary, if the procedure does not already exist in database B, then the copy process works with  ‘drop if exists in target’ not selected and ‘ignore definer’ selected but fails with access denied if ‘drop if exists in target’ is selected,(and ‘ignore definer’ still selected) even though the object doesn’t exist in target.

 

The privileges for both databases are as high as I am allowed by Siteground. SHOW GRANTS reports ‘GRANT ALL PERMISSIONS’ on both databases.

 

I don’t know how I can give you a test case without revealing my user names and passwords

[Sibin A S]

Hi,

 

We appreciate the time you have taken in elaborating the test-case. We did try the test-case that you mentioned and were successful in reproducing it our end. We will surely look into this issue and update you with the progress. Thanks in advance for your patience.

[Sibin A S]

Hi,

 

We have been successful in finding where the issue lies and it seems to be a bug in the code. We will be fixing this in the next release as soon as possible and will update you on this.

[iceman]

OK, that’s  great.

Its nice to see the forum posts are monitored after all!

Look forward to the next version with teh bug fixed.

Regards

[Sibin A S]

Hi,

 

We have fixed this issue in SQLyog 12.2.3. 

 

You can refer the release notes, here >> http://blog.webyog.com/2016/05/26/sqlyog-mysql-gui-12-2-3-released/

[Author]

Posts