Triggers Ignored When Restoring From Sql Dump

[ashario]

Hi all

I selected Tools -> Backup database as SQL dump

When I select Tools -> Restore from SQL dump, the triggers contained in the script are ignored

I checked the SQL file that is generated and it sort of looks like they are commented out

For example, the statements generated for one of my triggers is below.

Does anyone know why SQLyog comments out CREATE TRIGGER statements? Is there some way that I can get my trigger SQL generated without it being commented out? Its fairly error prone for me to cut and paste the various CREATE TRIGGER statements manually.

Is there some setting i have screwed up?

[codebox]/*Trigger structure for table `bid` */

DELIMITER $$

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `trig_bid_before_insert` /*$$

/*!50003 CREATE TRIGGER `trig_bid_before_insert` BEFORE INSERT ON `bid` FOR EACH ROW begin

— if we are near the end time of the transfer then extend the end time by 2 minutes

declare _end_date datetime;

select transfer_date_end into _end_date from transfer where transfer_id = new.transfer_id;

if timestampdiff(minute, new.bid_date, _end_date) <= 2 then update transfer set transfer_date_end = timestampadd(minute, 2, new.bid_date) where transfer_id = new.transfer_id; end if; end */$$ [/codebox]

[peterlaursen]

They are not commented out!

This

/*!50003 … */

is a MySQL extension to standard SQL comment syntax. ” ! ” means “NOT” in C-type computer languages.

So it means 'IF NOT MySQL version is 5.0.3 or higher then treat as a comment, ELSE (if MySQL version is 5.0.3 or higher) execute'.

For your information the 'mysqldump' program does exactly the same! This syntax is implemented in the MySQL server itself!

What is the server version you import to? Before 5.03 MySQL does not support triggers. We use the 'version dependent conditional comment' like this to avoid that the job should abort as a result of the error occurring on lower server versions.

[ashario]

Thanks for the reply.

I'm using MySQL 5.1.14 and Webyog 6.0.4

When I run the script it is ignoring any trigger syntax, so it seems the 'IF NOT MySQL version is 5.0.3 or higher' part isnt working as we'd expect.

Currently I'm just cutting out the trigger syntax, removing the comments and running the script manually, which is a right pain in the bum.

[peterlaursen]

This is reproduced on 5.1.20

Can you tell positively that rewoving the pseudo-comments fixes this or is it another issue?

We will need to research. But it may very well be a server bug!

[peterlaursen]

test script/example:

/*

SQLyog Enterprise – MySQL GUI v6.1 Test Build

Host – 5.1.20-beta-community-nt-debug : Database – asdf

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

Server version : 5.1.20-beta-community-nt-debug

*/

/*!40101 SET NAMES utf8 */;

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

create database if not exists `asdf`;

USE `asdf`;

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

/*Table structure for table `tablename1` */

DROP TABLE IF EXISTS `tablename1`;

CREATE TABLE `tablename1` (

`ab` bigint(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `tablename1` */

/*Trigger structure for table `tablename1` */

DELIMITER $$

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `qwe` /*$$

/*!50003 CREATE TRIGGER `qwe` BEFORE DELETE ON `tablename1` FOR EACH ROW BEGIN

— nothing;

END */$$

DELIMITER ;

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

[peterlaursen]

It also fails on 5.0.45

Removing pseudocomments does not help.

BTW (but not the issue here):

I think it should be

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `tablename1`.`qwe` /* $$

and not only

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `qwe` /* $$

edit:

NO .. it is “DROP .. databasename.triggername”

[peterlaursen]

Sorry for the confuison!

We find a bug it the parser used when executing external files.

(this parser 'isolates' statements from the file)

Please:

1) upgrade to 6.06 latest beta.

2) copy the file into the editor.

.. that should work. but we will fix the bug very soon too as well!

It is not pseudo-comments that is a problem. They work fine!

You should notice however that 'nested comments' are only supported from version 6.06.

[peterlaursen]

And just in case I did not make it clear enough:

The error occurs when importing. Not when exporting!

[peterlaursen]

Our analysis tells that all that needs to be fixed is allready fixed in 6.06 beta2. However 6.06 RC will be released today .. but with no change in this respect.

The confusing thing was that with recent MySQL 5.1.x (tested with 5.1.14, 5.1.18 and 5.1.20) only full syntax like

Code:

drop trigger database_name.trigger_name;

works. Even

Code:

use database_name;
drop trigger_name;

.. does not work. This is a server bug we think and this we will report to bugs.mysql.com. Like this it is simply not possible to import a trigger to another database than where it was created!

There seems to be more server bugs too, introduced when MySQL introduced support for DROP TRIGGER IF EXISTS in 5.1.14.

For instance nested conditional comments like

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `zxcv` */;

does not work. On 5.0.45 it does!

[peterlaursen]

It may be necassary to delete the line with the DROP TRIGGER statement in the SQL and drop manually untill MySQL AB fixes this 'bug-complex'

[peterlaursen]

hmmm ….

The first issue in the above post I cannot reproduce right now ( 🙁 )

But the second is easily reproducable! I filed this bug report:

http://bugs.mysql.com/bug.php?id=30631

BTW: I think the easiest 'workaround' is to 'search and replace' in an editor. With MySQL >= 5.1.14 simply replace

Code:

/*!50114 IF EXISTS */

with

Code:

IF EXISTS

[peterlaursen]

nevertheless ..

after the discussion that has taken place in the bug report I posted to bugs.mysql.com we reached the conclusion that

we should not do this

/*!50003 DROP TRIGGER /*!50114 IF EXISTS */ `trigtest`.`tr1` */ ;

but instead

/*!50003 DROP TRIGGER */ /*!50032 IF EXISTS */ /*!50003 `trigtest`.`tr1` */ ;

That is two changes actually:

1) check for version >= 5.0.32 and not 5.1.14;

2) avoid 'nested comments';

You should notice that that will result in Syntax Error 1064 on MySQL versions 5.1.1 – 5.1.13. But better support stable version (5.0.32++) and recent betas (5.1.14++) than old betas!

We plan this change for version 6.06 FINAL.

[ashario]

Whoa. You've done quite a bit of investigating there 🙂

I'm happy to wait for 6.06 final.

For now I have separated my trigger creation/deletion code into a separate sql script and i dont select triggers when performing an import/export

[Author]

Posts