Unwanted Code Produced By Create View Breaks Synchonisation

[iceman]

I love SQLyog, especially its helpful developer tools. However…

I develop dbs using a Wamp server on my localhost and have the live version of the database on a remote, shared access server.

However when creating veiws, udfs etc, SQLyog is a bit over enthusiastic about inserting unwanted code and this stops synchronisation from working. For example

 

When I create a view (on localhost) using syntax like

 

CREATE  VIEW `sitkacon_mam`.`test`

    AS

(SELECT * FROM table1);

 

The DLL of the created view appears with extra DEFINER code as below

 

CREATE ALGORITHM=UNDEFINED DEFINER=`my_db`@`localhost` SQL SECURITY DEFINER VIEW `test`

  AS

(SELECT * FROM table1);

 

This is in spite of the ‘Create view’ pop up menu showing the definer code commented out.

 

If I then use the synchronisation tool to replicate this view to the remote server (or simply dump the dll and try to run it) the DLL will fail as access to localhost is denied on the remote server for security reasons.

 

Using just the DLL I can manually delete the ‘ALGORITHM=UNDEFINED DEFINER=`my_db`@`localhost` SQL SECURITY DEFINER’ part,  manually copy the DLL to the remote server and run it. But using the synchronisation tool I cannot edit the code that the tool produces so cannot use that at all.

 

Is it possible to either have an option to stop the automatic production of the DEFINER=`my_db`@`localhost code (prefereably), or at least allow modification of the synchronisation code before it is run? 

 

 

[sathish]

Hello,

 

Could you please let us know what version of SQLyog that you are using? We have introduced an option to ignore DEFINER-clause in V11.4. So if you are using version less than 11.4 then please upgrade to the latest version. Also quoting from release notes:

 

In Copy database, Export as SQL-Dump, Scheduled Backup and Schema Sync there is now an option to ignore/omit DEFINER-clause for database objects where it applies. Note that the target server will then create DEFINER as current SQLyog user when creating the object.

 

 

 

Also please refer: http://faq.webyog.com/content/33/7/en/sqlyog-version-history.html

 

Regards,

Sathish

[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 */;

 

[peterlaursen]

This 

 

/*!50001

 

.. is NOT a comment.  Please read MySQL documentation at http://dev.mysql.com/doc/refman/5.5/en/comments.html.

[peterlaursen]

It means “treat as a commented if server version in less than 5.01. If server version is 5.01 or higher don’t treat as a comment”.

[peterlaursen]

And one more thing you should be aware of is that when dumping and importing a dumps with VIEWs, is that first a temporary table will be created for each VIEW, next the VIEW is created and finally the table is dropped.  This is necessary to handle ‘VIEWS built on VIEWS’ as there is no way to guarantee in what order VIEWS are dumped. 

 

 

 

All this is *defacto standards* with all MySQL tools – including the official ‘mysqldump’ utility. And it is at least the 50th time I reply along these lines here (but no problem). 

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

[peterlaursen]

“could include a few lines about the use made of these comments in the sql that is generated”.

 

What we do here is the de-facto standard of what any MySQL tool dumping or copying SQL database objects do  – including the official ‘mysqldump’ tool.  It is simply something every MySQL user should know.  MySQL has lots of ‘oddities’ compared to other RDBMS and as provider of a client it is not our job to rewrite the MySQL manual.

[Author]

Posts