Bug : Editing A Trigger Corrupts The Quotes !

[iubito]

Hi community!

I posted a new issue on https://code.google.com/p/sqlyog/issues/detail?id=2075&sort=-id

but as it doesn’t seems very active, I post here.

 

I wonder if when I copied a trigger from a database to another, it corrupted the same way.

 

 

What steps will reproduce the problem?

1. Create a trigger

CREATE

TRIGGER `test_trigger` BEFORE DELETE ON `test_table`

FOR EACH ROW BEGIN

    DECLARE a VARCHAR(25);

    SET a = CONCAT(‘I”m not happy’);

END;

$

2. close the trigger’s editor tab

3. refresh the trigger list in your database on the left

4. right click your trigger > Alter. Here is the corrupted line

SET a = CONCAT(‘I’m not happy’);

What is the expected output?

SET a = CONCAT(‘I”m not happy’);

What do you see instead?

SET a = CONCAT(‘I’m not happy’);

What version of the product are you using? On what operating system?

Community v12.04 64 bit on Windows Server 2012, MySQL 5.1.5

[Abhishek Kumar Pandey]

Hi,

 

Please try using SET a = CONCAT(‘I’,’m not happy’); instead of SET a = CONCAT(‘I”m not happy’);. If problem persist write back to us, we will look into it.

[peterlaursen]

I understand your problem different than what Abishek does.

 

I this “SET a = CONCAT(‘I”m not happy’);” you want the string “‘I’m not happy”, I think.  Please confirm.  And please also explain why you are using CONCAT as there is (as I understand it) only a single string and nothing to CONCAT. In my understanding CONCAT is redundant for the test case (and somewhat confusing, too) and you could as well – and more clearly – simply use SET a = ‘I”m not happy’;  to illustrate this.

 

Please explain if Abishkek’s or my understanding is the right one. If it is mine then using “doublequotes” around the string would be the most simple solution – like SET a = CONCAT(“I’m not happy”); or SET a = “I’m not happy”; .

 

If there is a bug it is a bug with SHOW TRIGGERS statement in MySQL.as I have reported it here http://bugs.mysql.com/bug.php?id=75685. 

 

BTW: You can always see what SQL-statements SQLyog uses in the HISTORY tab.

[peterlaursen]

As regards the question “I wonder if when I copied a trigger from a database to another, it corrupted the same way”, the answer is that it depends on whether we use SHOW TRIGGERS or SHOW CREATE TRIGGER in “copy database/table” and “schema sync” when fetching the TRIGGER definiton from the source server. I don’t know. Abishek wll reply to that. But most simple solution is still to use “doublequotes” around strings containing a ‘singlequote’ and vice versa.

 

If MySQL does not approve the bug report we may need to add the missing quote in our code. This will involve some painful parsing. And if they approve it, a fix will most likely only go into 5.7x or higher MySQL release. They will not consider it important enough to fix in releases that are currently deemed stable.

[iubito]

Sorry for the concat, it’s a remain of my original code.

I want to set, insert or wathever a string containing a quote.

For example I’m happy i write

Set a = ‘I”m happy’

[Abhishek Kumar Pandey]

We use SHOW TRIGGERS FROM `` WHERE `Trigger`=’‘ in schema sync and copy database. So I guess bug reported by peter applies to SQLyog.

[peterlaursen]

.. and then the TRIGGER  will actually fail to copy (I just tried: the table copies, but the trigger does not).  The CREATE TRIGGER syntax will contain an invalid expression and CREATE fails with a syntax error.

 

However, no error message appears in SQLyog and it should.

[Abhishek Kumar Pandey]

“However, no error message appears in SQLyog and it should.”…Not reproducible for me on first attempt. 

[Abhishek Kumar Pandey]

Also refer-https://mariadb.atlassian.net/browse/MDEV-7231

[iubito]

Hi,

I’ve tried with various SQLyog versions

 

show create trigger : Set a = ‘I”m happy’

right click, alter trigger : Set a = ‘I’m happy’ 

copy to another database : error

 

Can SQLyog replace the use of SHOW TRIGGERS by SHOW CREATE TRIGGER ?

[peterlaursen]

It looks like we have no other option.  I don’t think MySQL will fix this as priority, and even after a fix has been made available, people will continue to use affected server versions for years.  

 

We are checking if it raises new problems to replace the statement.

[peterlaursen]

Please check with version 12.08 released an hour ago: http://blog.webyog.com/2015/02/09/sqlyog-mysql-gui-12-08-released/ “Escaped strings in a TRIGGER definition could have the escapes removed when copying and when using Schema Sync.  This fix is actually a workaround for a bug in MySQL.”

 

 

(and we actually execute both statements against the sorurce now)

[iubito]

Hi !

Sorry for late reply, I didn’t had mail notification on this thread, or it didn’t work.

 

I checked with sqlyog community 12.0.9 : problem SOLVED !

I tested :

– alter a trigger

– alter a function

– alter a procedure

– copy a trigger to another server/database

 

all these are OK.

 

Many thanks !

[Author]

Posts