Structure Sync Stored Procecdures

[dbuckle]

I sync stored procs then do a “Compare Now” again and the same stored procs are listed as being different – even after applying the generated sync sql to the other database.

Basically its not generating EXACTLY the same code.

Please advise what to do as its caused so many problems and wasted so much time. I've tried using the latest beta build and it still does it.

I'm running MySQL 5.0.37

[navyashree.r]

Hi,

Can you please provide structure sync dump or simple test case?

Regards,

Navya

[peterlaursen]

Please ALWAYS tell the SQLyog program version you are using!

If it is not recent, then first try an upgrade.

I think I recall such issue was fixed 3-6 months ago.

And if it is reproducable with a recent version we need a test case to reproduce with!

[dbuckle]

I created a stored proc on db1:

DELIMITER $$

DROP PROCEDURE IF EXISTS `db1`.`test1`$$

CREATE PROCEDURE `db1`.`test1`()

/*LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'*/

BEGIN

select ID, TestData from test_table;

END$$

DELIMITER ;

How to reproduce:

1. used structure sync to copy it to db2 by copying the result to clipboard and pasting in query window and running it:

/* Create Procedure in Second database */

USE `db2`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `db2`.`test_1`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_1`()

BEGIN

select * from test_table;

END$$

DELIMITER ;

2. modified that stored proc on db1 again.

3. used structure sync again in the same way.

/* Alter Procedure in Second database */

USE `db2`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `db2`.`test1`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test1`()

BEGIN

select * from test_table;

END$$

DELIMITER ;

4. Start structure sync tool again. It STILL shows test1 stored proc as being different even though I just applied the changes.

Table scructure follows:

/*

SQLyog Enterprise Trial – MySQL GUI v6.1 Beta4

MySQL – 5.0.37-community-nt : Database – db1

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

*/

/*!40101 SET NAMES utf8 */;

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

create database if not exists `db1`;

USE `db1`;

/*!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_table` */

DROP TABLE IF EXISTS `test_table`;

CREATE TABLE `test_table` (

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

`TestData` varchar(100) NOT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `test_table` */

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

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

---

/*

SQLyog Enterprise Trial – MySQL GUI v6.1 Beta4

MySQL – 5.0.37-community-nt : Database – db2

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

*/

/*!40101 SET NAMES utf8 */;

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

create database if not exists `db2`;

USE `db2`;

/*!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_table` */

DROP TABLE IF EXISTS `test_table`;

CREATE TABLE `test_table` (

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

`TestData` varchar(100) NOT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

/*Data for the table `test_table` */

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

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

[navyashree.r]

Issue confirmed!

We will fix in the next release . Thanks for reporting.

Regards,

Navya

[dbuckle]

navyashree.r wrote on Oct 15 2007, 01:35 PM:

Issue confirmed!

We will fix in the next release . Thanks for reporting.

Regards,

Navya

Is there a workround I can use in the meantime?

[peterlaursen]

We will try to fix this VERY soon and give you a private build with the fix for this.

VERY SOON may mean today itself or tomorrow.

[peterlaursen]

I am sorry, we have fixed in the trunk but did not finish testing.

A 'workaround' for this is to execute the SQL script from Structure Sync in a 'advanced' query tab. The 'advanced' query tab is the one that opens the templates for generating Stored Procedures etc.

From the database menu .. create store procedure ..give a dummy name .. delete the template .. and paste the STRUCT SYNC SQL instead .. execute all.

[Mahesh]

HI,

6.1 RC has been released with this fix now ,Please check

http://www.webyog.com/forums//index.php?showtopic=3562

Thanks

[chris graner]

I'm still having the same problem using 6.11

Moreover, the DROP statements don't include the procedure names.

[peterlaursen]

@chris

1)

You are telling that structure sync of 6.11 still reports difference immediately after it executed?

please provide more details and an example!

2)

missing names is confirmed!

example:

create a dummy table in two empty databases and a dummy SP 'qwe' in one, sync from the one without the SP, check 'drop SP' from struct sync gui and run struct sync – script will be like (empty SP name):

Code:

/* Drop Procedure in Second database */

DROP PROCEDURE `test`.“;

[peterlaursen]

We fixed this issue with empty names!

Please confirm the fix with this build:

http://www.webyog.com/downloads/betas/not_…QLyog612Ent.exe

… and still please provide details for the other issue you reported!

[chris graner]

Hey Peter,

I confirmed the DROP statement generation has been fixed.

peterlaursen wrote on Nov 5 2007, 05:40 AM:

1)

You are telling that structure sync of 6.11 still reports difference immediately after it executed?

please provide more details and an example!

Yes, the sync still reports an immediate difference after execution. Here's a simple function which causes the behavior.

Code:

DELIMITER $$

DROP FUNCTION IF EXISTS `stats`.`gn_fn_result_count`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `gn_fn_result_count`($raceId INT) RETURNS int(11)
BEGIN
RETURN
(
SELECT
COUNT(*)
FROM tbl_race_result
WHERE race_id = $raceId
);
END$$

DELIMITER;

[peterlaursen]

We cannot reproduce this. I create in one database, run struct sync, copy script to clipboard, close struct sync, select another database, paste the script into the editor and execute. Next run struct sync again. No differences found.

Forgive me if this is a silly question: do you understand the structure sync will generate a script that you will have to execute later? Running structure sync does not ITSELF exectue the sync script generated. You will have to do!

Also please tell if this stored procedure was created with an earlier (5.x) version? If so there might be some issue with how are represented. Can you copy the function definition from ALTER PROCEDURE from both databases, save both as text files, zip and attach here. It is important that it is attached as a binary (zip) file and not text files to avoid browser formatting issues!

[peterlaursen]

version 6.12 RC was released!

http://www.webyog.com/forums//index.php?showtopic=3592

Everyone who has installed the non-public release linked to above, should replace as the non-public build could easily crash immediately after establishing HTTP-connection.

[chris graner]

peterlaursen wrote on Nov 6 2007, 04:31 AM:

Forgive me if this is a silly question: do you understand the structure sync will generate a script that you will have to execute later? Running structure sync does not ITSELF exectue the sync script generated. You will have to do!

Yes, I'm executing the generated script.

peterlaursen wrote on Nov 6 2007, 04:31 AM:

Also please tell if this stored procedure was created with an earlier (5.x) version?

It's possible…but I thought some of the procedures were created more recently. Regardless, I can't confirm.

peterlaursen wrote on Nov 6 2007, 04:31 AM:

Can you copy the function definition from ALTER PROCEDURE from both databases, save both as text files, zip and attach here. It is important that it is attached as a binary (zip) file and not text files to avoid browser formatting issues!

Files attached. I generated from sqlyog, saved to disk and then created the .zip.

[Manoj]

Hi,

I imported the dump attached to two databases and structure sync tool reports both are same!

SQLyog: v6.12 RC

MySQL : v5.0.28-enterprise-nt-log

Screen shot attached

[peterlaursen]

We simply are running out of ideas of how to do progress here. Do you have any?

[Author]

Posts