Toad World® Forums

Bug report with TRIGGER script created with schema compare


#1

Comparing a source and target DB, and generated a script for target DB gave me this.

CREATE TRIGGER `core_currency_BEFORE_INSERT`
   BEFORE INSERT
   ON core_currency
   FOR EACH ROW
BEGIN
   SET NEW.currency_sort =
          COALESCE(NEW.currency_sort,
                   (SELECT MAX(currency_sort) + 1 FROM core_currency),
                   0);
END;

When I try to import using phpmyadmin, it gave me an error as follows:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 13

I google’d a bit on the problem, and the reason is the delimiter.

I fixed it by manually doing the following:

DELIMITER $$
CREATE TRIGGER `core_currency_BEFORE_INSERT`
   BEFORE INSERT
   ON core_currency
   FOR EACH ROW
BEGIN
   SET NEW.currency_sort =
          COALESCE(NEW.currency_sort,
                   (SELECT MAX(currency_sort) + 1 FROM core_currency),
                   0);
END;
$$
DELIMITER ;

I’m on evaluation of the tool. So far it’s looking good, except for this error here.

I was wondering if a proper fix can be applied any time soon?

My company is considering a tool for comparing MySQL databases.

Thank you!


#2

Hi, thank you for your feedback, script generated by Toad Edge is correct, is it possible to try it with mysql console client? i have created an item in our tracking system, we will keep you informed about the progress.
Anyway, is there a problem to use Toad Edge to deploy scripts to database? we are curious why you need to use phpmyadmin to deploy scripts to db.
thanks
br.
Miroslav Stanik