Sqlyog Ent: Powertool Structure Synch Produces Blank Update Scripts

[Anonymous]

Hi Guys,

Was using 6.02 and noticed that my synchronisation scripts had BLANK scripts for scripts that needed updates. eg.

/* Alter Procedure in Second database */

USE `tournaments`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `tournaments`.“$$

$$

DELIMITER ;

/* Alter Procedure in Second database */

USE `tournaments`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `tournaments`.“$$

$$

DELIMITER ;

/* Alter Procedure in Second database */

USE `tournaments`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `tournaments`.“$$

$$

DELIMITER ;

/* Alter Procedure in Second database */

USE `tournaments`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `tournaments`.“$$

$$

DELIMITER ;

/* Alter Function in Second database */

USE `tournaments`;

DELIMITER $$

DROP FUNCTION IF EXISTS `tournaments`.“$$

$$

DELIMITER ;

…I upgraded to 6.03 and it still appears to be there. I've downgraded to 5.32 and that works….

…please have a look at this….as this is a major feature I use (and pay for).

D.

[peterlaursen]

Please …

1) tell the exact MySQL versions involved

2) provide two complete structure-only dumps to reproduce with.  Create a ticket if you do not want to expose it here

[Anonymous]

peterlaursen wrote on Jun 28 2007, 11:17 AM:

Please …

1) tell the exact MySQL versions involved

2) provide two complete structure-only dumps to reproduce with. Create a ticket if you do not want to expose it here

Sheesh Pete……now I'm doing all YOUR work 😉

1. 5.0.41-community-nt-log (both source and destination)

2. Easy enough….

Source database….

[codebox]/*

SQLyog Enterprise – MySQL GUI v6.03

Host – 5.0.41-community-nt-log : Database – test

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

Server version : 5.0.41-community-nt-log

*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

create database if not exists `test`;

USE `test`;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

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

/*Table structure for table `test` */

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (

`ID` int(10) unsigned NOT NULL auto_increment,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `test` */

/* Procedure structure for procedure `sp_test` */

/*!50003 DROP PROCEDURE IF EXISTS `sp_test` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test`()

MODIFIES SQL DATA

DETERMINISTIC

SQL SECURITY INVOKER

BEGIN

— // Source Database

END */$$

DELIMITER ;

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

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

[/codebox]

…Destination

[codebox]/*

SQLyog Enterprise – MySQL GUI v6.03

Host – 5.0.41-community-nt-log : Database – test

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

Server version : 5.0.41-community-nt-log

*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

create database if not exists `test`;

USE `test`;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

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

/*Table structure for table `test` */

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (

`ID` int(10) unsigned NOT NULL auto_increment,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `test` */

/* Procedure structure for procedure `sp_test` */

/*!50003 DROP PROCEDURE IF EXISTS `sp_test` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`daniel`@`192.168.%` PROCEDURE `sp_test`()

MODIFIES SQL DATA

DETERMINISTIC

SQL SECURITY INVOKER

BEGIN

— // Target Database

END */$$

DELIMITER ;

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

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

[/codebox]

….try and synch that…and it should produce bad script.

D.

[peterlaursen]

First conclusion is that we think that is related to the SQL SECURITY clause!

We get various results depending on in what direction we sync.  Also it looks like this occurs because the SP names are identical.

This might also involve bugs with MySQL itself!

But you claim an isse with 6.03 and not 5.x ??

With what user privileges are you running STRUCTURE SYNC?

as 'root', 'daniel' or a third one?

“SQL SECURITY …. ” tells who has access to the SQL inside the SP.  You will never be allowed to acces the contents 

of a SP defined with SQL SECURITY 

Please execute 'SHOW GRANTS FOR 'user@'host' and paste the results.

BTW: Are you sure that you have updated the privilege tables every time you have upgraded MySQL?

[peterlaursen]

OK .. confirmed.

5.32 and 6.03 behave differently here!

and what I wrote about SQL SECURITY was not correct.

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

We are analyzing now!

[peterlaursen]

Please check:

http://www.webyog.com/downloads/betas/not_released/SQLyog604Ent.exe

[Anonymous]

peterlaursen wrote on Jun 28 2007, 04:51 PM:

Please check:

http://www.webyog.com/downloads/betas/not_released/SQLyog604Ent.exe

…like a charm!

Thanks!

D.

[Author]

Posts