[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

[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

[iceman]

Sorry,

When writing a query, right click on any table in the database explorer panel. You will see the context menu shown in the attachment. The top item allows you to insert a choice of statements into a query you are working on. That’s where I’d also like an option to get the same choice of statements, but just put onto the clipboard (preferably even if I’m not writing a query).

 

Interestingly, in the query designer window, right clicking on the generated sql gives options to both copy to clipboard and copy to new query tab, that’s the sort of behaviour I’m, looking for but available when right clicking on a table in the explorer.

[iceman]

Ah, Thank you.

At least I know where the issue lies now.

I frequently dump the db from the desktop, copy the sql to the laptop and run it so that I can synchronise the two databases. I was looking for a simpler way by doing it directly in SQLyog.

Looks like I shall have to carry on as before.

[iceman]

Yes, I was using a 32 bit version on another machine to to get round the problem.

I didn’t realise that I could have both 32 and 64 installed on the same machine using the same licence.

Problem solved now, thanks

[iceman]

Thank you very much for the information Peter. That really makes it clear.

 

> And it is at least the 50th time I reply along these lines here (but no problem).

 

Yes, I could tell that by the tone of your answer. However it’s the first time I have asked this – and I did look for answers before I asked, although it is true I haven’t read the MySQL documentation from cover to cover and so had no reason to think /*!50001 was a special comment extension for MySQL rather than simply !50001 in a block comment.

 

If this is a question that is asked so often then perhaps the SQLyog Help file page, ‘Backup Database as SQL Dump’ could include a few lines about the use made of these comments in the sql that is generated. Then, when users look at the SQLyog help file page to see how this feature operates (as I did) it will alert them to the fact that they will not be able to directly use the sql to create the objects in other types of database, eg SQLite, as they will treat it as being commented out.

[iceman]

Its ver 11.42 (64 bit), upgraded a short time ago, its possible I was still using the earlier version when I was having the trouble, in which case my appologies.

 

Its also possible that I misunderstood the operation. In ver 11.42 you have to check ‘ignore definer’ before doing the compare. I was checking it after doing the compare but before clicking ‘execute’ in the belief that this would cause the execute to ignore the definer. That was my fault, I can make the synch work now, thank you.

 

However trying to duplicate object in the remote db using ‘backup to sql’ still seems to behave oddly.

 

As a test I backed up just one view, called ‘test’, to sql but the sql it produced was nearly all commented out, including the statement to create the view. It also contained contained much addtional stuff, including sql to create a table as well as a view

 

The only line that would be executed if I ran the resulting sql was one to drop a table with the same name as the view I was trying to create – line shown in bold below.

The backup was done with none of the options checked.

 

Is this what I should expect from a backup statement? If so how would I restore the database without extensive editing of the sql?

 

Output from the backup of VIEW ‘test’ is below.

 

/*

SQLyog Enterprise v11.42 (64 bit)

MySQL – 5.6.12-log : Database – sitkacon_mam

*********************************************************************

*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=”*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

/*Table structure for table `test` */

DROP TABLE IF EXISTS `test`;

/*!50001 CREATE TABLE  `test`(

 `member_id` int(11) ,

 `member_title` varchar(10) ,

 `member_fn` varchar(30) ,

 `member_sn` varchar(30) ,

 `member_address_id` int(11) ,

 `member_email_id` int(11) ,

 `member_home_phone` varchar(15)

)*/;

/*View structure for view test */

/*!50001 DROP TABLE IF EXISTS `test` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`sitkacon_mamuser`@`localhost` SQL SECURITY DEFINER VIEW `test` AS (select `member`.`member_id` AS `member_id`,`member`.`member_title` AS `member_title`,`member`.`member_fn` AS `member_fn`,`member`.`member_sn` AS `member_sn`,`member`.`member_address_id` AS `member_address_id`,`member`.`member_email_id` AS `member_email_id`,`member`.`member_home_phone` AS `member_home_phone` from `member`) */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

[Author]

Posts