MySQL Bugs: #44665: 160x warnings on triggers after upgrading from 5.0 to 5.1 using mysql

Bug #44665	160x warnings on triggers after upgrading from 5.0 to 5.1 using mysql_upgrade
Submitted:	5 May 2009 11:16	Modified:	21 Apr 2011 18:31
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I looked through the whole documentation about upgrading from 5.0 to 5.1 and couldn't find a hint about loosing trigger informations.

I found informations here for upgrading from older to 5.0.10 but not from newer 5.0 versions to 5.1.

$ mysql_upgrade
...
Warning  : Triggers for table `blubb`.`t` have no creation context
Warning  : Triggers for table `blubb`.`t1` have no creation context
...

You really have to re-create the trigger manually to get them working again.

I think this should be documented.

How to repeat:
Setup a default replication system with actual MySQL 5.0 Master and MySQL 5.0 Slave.

Use my testfile and put it into the master:

$ mysql -h127.0.0.1 -P<your_master_port> < mysqlupgradebug.sql

Stop the slave

Install actual MySQL 5.1.

Start the slave with the deamon from MySQL 5.1:
./libexec/mysqld --defaults-file=<your_slave_my.cnf>

Now use mysql_upgrade to upgrade the slave:

./bin/mysql_upgrade --defaults-file=<your_slave_my.cnf>

You will see the warnings

When you know look into the slave you will figure out that the triggers weren't created.

Suggested fix:
Document that you have to re-create triggers

test file for getting some data before upgrading

Attachment: mysqlupgradebug.sql (text/x-sql), 1.49 KiB.

This is more serious because when triggers get lost during upgrade in replication data lost will be the follow.

Triggers should work on slave before slave will be restarted and before master will send new data.

"I looked through the whole documentation about upgrading from 5.0 to 5.1 and couldn't find
a hint about loosing trigger informations."

That doesn't make this a docs bug. This isn't documented, because it shouldn't happen. This is a server bug.

Recategorizing as Server:Stored Programs so it can be analyzed as such.

I agree here with Paul.

Fixing the problem is better as just documenting it.

Duplicate of bug#30962. 
Also see http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html

You imply from the last comment that this is documented and thus not an issue?

To be honest if I missed this in the documentation, and it took Susanne time to find this out this is going to hit other people too. The documentation needs to be improved.

This "action" (drop and recreate triggers) needs to be performed in 2 steps:
(1) drop on 5.0
(2) add again on 5.1

Not explicitly mentioned that way.

Hence any server being upgraded in a REPLICATION environment MUST be stopped and during this process replication MUST NOT take place. This is very easy to get wrong and hence lose data as the removed triggers may change the slave's content compared to the master.

I still maintain that the current upgrade documentation DOES NOT give a clear list of steps required in order to achieve the upgrade from 5.0 to 5.1. There's too much information scattered over 13 A4 pages of Section 2.12 "upgrading or downgrading MySQL" in a non-cronological order. In any case there is no mention of potential problems (such as impact in a replicated environment) and also the potential need to switch between old and new binaries in order to achieve the required upgrade.

So the comment in Bug#30962

" [4 Dec 2007 20:54] Konstantin Osipov

It was decided to document rather than fix this and 2 other bugs of the same nature."

seems rather unfortunate.

Please consider improving the documentation, amd more importantly making the life easier for the DBAs, by actually getting the MySQL software to do the task that can probably be reasonably easily automated.

With similar issues with upgrades to 5.4 on the horizon you're going to see more frustrated users.

Paul,

http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html

"When upgrading from a previous version of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers and re-create them ..."

But we try to upgrade here from > 5.0.10 which means this incompatibility should be fixed.

The tests showed that it is not fixed and the sentence above needs an update.

http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html says:

"Incompatible change: Several issues were identified for stored programs (stored procedures and functions, triggers, and events) and views containing non-ASCII symbols. These issues involved conversion errors due to incomplete character set information when translating these objects to and from stored format.

To address these problems, the representation for these objects was changed in MySQL 5.1.21. However, the fixes affect all stored programs and views. (For example, you will see warnings about “no creation context.”) To avoid warnings from the server about the use of old definitions from any release prior to 5.1.21, you should dump stored programs and views with mysqldump after upgrading to 5.1.21 or higher, and then reload them to recreate them with new definitions. Invoke mysqldump with a --default-character-set option that names the non-ASCII character set that was used for the definitions when the objects were originally defined."

Paul,

you are right. Sorry. After I read the page the hundreds time I finally found it too.

I will close this bug because all is fine here.

The URL is now http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html (not the upgrading-from-5-0.html URL above)

Tested further. General observations:

#1 The trigger still works, even if it you see 1600/1603 warning(s):

------
mysql> SET @var1=1; SELECT @var1;
Query OK, 0 rows affected (0.00 sec)
+-------+
| @var1 |
+-------+
| 1 |
+-------+

mysql> INSERT INTO tb1 VALUES (10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @var1;
+-------+
| @var1 |
+-------+
| 2 |
+-------+

mysql> CREATE TRIGGER tg2 AFTER INSERT ON tb1 FOR EACH ROW SET @var1=2*@var1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------+
| Warning | 1603 | Triggers for table `test`.`tb1` have no creation context |
+---------+------+----------------------------------------------------------+
------

#2 Manually dropping the trigger and re-creating it:

------
mysql> drop trigger tg1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Warning | 1603 | Triggers for table `test`.`tb1` have no creation context |
+---------+------+----------------------------------------------------------+

mysql> CREATE DEFINER=`root`@`localhost` trigger tg1 BEFORE INSERT on tb1 for each row set @var1=@var1+1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER tg2 AFTER INSERT ON tb1 FOR EACH ROW SET @var1=2*@var1;
Query OK, 0 rows affected (0.01 sec)
------

Works fine.

#3 Making another trigger and dropping it on the same table *once* also seems to resolve the warning in this testcase:

------
mysql> CREATE TRIGGER tg2 AFTER INSERT ON tb1 FOR EACH ROW SET @var1=2*@var1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------+
| Warning | 1603 | Triggers for table `test`.`tb1` have no creation context |
+---------+------+----------------------------------------------------------+

mysql> DROP TRIGGER tg2;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER tg2 AFTER INSERT ON tb1 FOR EACH ROW SET @var1=2*@var1; /* no warning anymore */
Query OK, 0 rows affected (0.00 sec)
------

This is because the trigger format is being re-written (ref bug #52025)

However, this is not a viable workaround.

The manual states:

'To avoid warnings from the server about the use of old definitions from any release prior to 5.1.21, you should dump stored programs and views with mysqldump after upgrading to 5.1.21 or higher, and then reload them to recreate them with new definitions'
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html

Testing the same does not work *unless* all faulty triggers are dropped and a complex (and potentially risky) procedure is used: 

(The procedure can be done *after an upgrade to 5.1*, and no action on 5.0 is required.)

-------
./mysql_upgrade --datadir=/tmp/var3/mysqld.1/data/ --basedir=/mysql/mysql-advanced-gpl-5.1.41-linux-i686-icc-glibc23/ --force --socket=/tmp/var3/tmp/mysqld.1.sock --user=root

[...]
test.tb1
Warning : Triggers for table `test`.`tb1` have no creation context
status : OK
[...]

./mysqldump --socket=/tmp/var3/tmp/mysqld.1.sock -uroot --triggers --no-data --no-create-db --no-create-info --all-databases > out.sql

mysql> DROP TRIGGER tg1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------+
| Warning | 1603 | Triggers for table `test`.`tb1` have no creation context |
+---------+------+----------------------------------------------------------+

./mysql --socket=/tmp/var3/tmp/mysqld.1.sock -uroot --force < out.sql

Got rid of the 1603 warning:

mysql> DROP TRIGGER tg1;
Query OK, 0 rows affected (0.00 sec)
-------

There are several complexities/issues here:

1. When dumping the triggers, a complex mysqldump command needs to be used, *exactly* as per the above.

A mistake is easily made: if once specifies --no-data, --all-databases and --triggers but none of the other options, and reloads this dump, *the entire server data will be wiped with only the schema re-created*.

Besides the complexity or risk, this requirement is not described in the manual.

2. There is a need to DROP all existing faulty triggers before reloading the dump, and additionally --force needs to be used if there are other triggers on the server. Both these requirements are not described in the manual:

'you should dump stored programs and views with mysqldump after upgrading to 5.1.21 or higher, and then reload them to recreate them with new definitions'

This reload/recreate is not possible without dropping the existing triggers, or editing the dump file. 

One of the problems is a limitation with mysqldump options; there is no option like --add-drop-trigger, which would be handy here.

And, when trying to re-load triggers (without dropping them first), it will not re-create the triggers (including when the the --force option is used):

ERROR 1235 (42000) at line 32: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

Summary: though it is possible to follow the procedure above, the procedure is:

A. Dangerous if not done correctly
B. Complex
C. Not documented

Keeping the above in mind - and given that we are talking about a simple migration of triggers from 5.0 to 5.1 - I think this bug should be resolved.

Kostja, Davi,

Why not just broaden the scope of mysql_upgrade, actually of "CHECK FOR UPGRADE" and have mysql_upgrade (or CHECK) automatically convert or 5.0 .TRG files into 5.1 format.

With this approach we would not have problems with locking, that Davi has described so thoroughly.

Your opinion is very much required on this idea.

> Why not just broaden the scope of mysql_upgrade, actually of "CHECK FOR 
> UPGRADE" and have mysql_upgrade (or CHECK) automatically convert or 
> 5.0 .TRG files into 5.1 format.

I agree, as per my note in bug #52025:

> IMO, mysql_upgrade should fix this. See comments on bug #44665

Bug #52025 should be closed as a duplicate of this one, *if* it is decided that
mysql_upgrade should handle this.

Side note: there is no need to drop the triggers in 5.0, it can be done after the upgrade to 5.1. However, that said, one difference may be that mysql_upgrade would not give a bunch of errors if the triggers are dropped in 5.0, after the dump, before mysql_upgrade.

See bug #45235

A sidenote for those using mysqlcheck after an upgrade using mysql_upgrade: the issue can only be seen on second run of mysqlcheck -r:

--------------
roel@roel-ubuntu-vm:/mysql/mysql-5.1.32-linux-i686-icc-glibc23$ ./bin/mysqlcheck -h127.0.0.1 -P5132 -uroot -r roelt
roelt.atst                                         OK
roelt.btst                                         OK
roelt.ctst                                         OK
roelt.dtst                                         OK
roelt.ftst                                         OK
roelt.gtst                                         OK
roel@roel-ubuntu-vm:/mysql/mysql-5.1.32-linux-i686-icc-glibc23$ ./bin/mysqlcheck -h127.0.0.1 -P5132 -uroot -r roelt
roelt.atst
Warning  : Triggers for table `roelt`.`atst` have no creation context
status   : OK
roelt.btst                                         OK
roelt.ctst
Warning  : Triggers for table `roelt`.`ctst` have no creation context
status   : OK
roelt.dtst                                         OK
roelt.ftst
Warning  : Triggers for table `roelt`.`ftst` have no creation context
status   : OK
roelt.gtst                                         OK
--------------

Subsequent runs give same errors. Running with -c, -g or -e (even multiple times) does not produce the errors, except - oddly enough - the first time -g is used:

--------------
roel@roel-ubuntu-vm:/mysql/mysql-5.1.32-linux-i686-icc-glibc23$ ./bin/mysqlcheck -h127.0.0.1 -P5132 -uroot -g roelt
roelt.atst
Warning  : Triggers for table `roelt`.`atst` have no creation context
status   : OK
roelt.btst                                         OK
roelt.ctst
Warning  : Triggers for table `roelt`.`ctst` have no creation context
status   : OK
roelt.dtst                                         OK
roelt.ftst
Warning  : Triggers for table `roelt`.`ftst` have no creation context
status   : OK
roelt.gtst                                         OK
roel@roel-ubuntu-vm:/mysql/mysql-5.1.32-linux-i686-icc-glibc23$ ./bin/mysqlcheck -h127.0.0.1 -P5132 -uroot -g roelt
roelt.atst                                         OK
roelt.btst                                         OK
roelt.ctst                                         OK
roelt.dtst                                         OK
roelt.ftst                                         OK
roelt.gtst                                         OK
roel@roel-ubuntu-vm:/mysql/mysql-5.1.32-linux-i686-icc-glibc23$ ./bin/mysqlcheck -h127.0.0.1 -P5132 -uroot -g roelt
roelt.atst                                         OK
roelt.btst                                         OK
roelt.ctst                                         OK
roelt.dtst                                         OK
roelt.ftst                                         OK
roelt.gtst                                         OK
roel@roel-ubuntu-vm:/mysql/mysql-5.1.32-linux-i686-icc-glibc23$ ./bin/mysqlcheck -h127.0.0.1 -P5132 -uroot -r roelt
roelt.atst                                         OK
roelt.btst                                         OK
roelt.ctst                                         OK
roelt.dtst                                         OK
roelt.ftst                                         OK
roelt.gtst                                         OK
roel@roel-ubuntu-vm:/mysql/mysql-5.1.32-linux-i686-icc-glibc23$ ./bin/mysqlcheck -h127.0.0.1 -P5132 -uroot -r roelt
roelt.atst
Warning  : Triggers for table `roelt`.`atst` have no creation context
status   : OK
roelt.btst                                         OK
roelt.ctst
Warning  : Triggers for table `roelt`.`ctst` have no creation context
status   : OK
roelt.dtst                                         OK
roelt.ftst
Warning  : Triggers for table `roelt`.`ftst` have no creation context
status   : OK
roelt.gtst                                         OK
roel@roel-ubuntu-vm:/mysql/mysql-5.1.32-linux-i686-icc-glibc23$ ./bin/mysqlcheck -h127.0.0.1 -P5132 -uroot -r roelt
roelt.atst
Warning  : Triggers for table `roelt`.`atst` have no creation context
status   : OK
roelt.btst                                         OK
roelt.ctst
Warning  : Triggers for table `roelt`.`ctst` have no creation context
status   : OK
roelt.dtst                                         OK
roelt.ftst
Warning  : Triggers for table `roelt`.`ftst` have no creation context
status   : OK
roelt.gtst                                         OK
--------------

See bug#30731.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated upgrade note:

   * Incompatible change: Several issues were identified for
     stored programs (stored procedures and functions,
     triggers, and events) and views containing non-ASCII
     symbols. These issues involved conversion errors due to
     incomplete character set information when translating
     these objects to and from stored format.

     To address these problems, the representation for these
     objects was changed in MySQL 5.1.21. However, the fixes
     affect all stored programs and views. (For example, you
     will see warnings about "no creation context.") To avoid
     warnings from the server about the use of old definitions
     from any release prior to 5.1.21, you should dump stored
     programs and views with mysqldump after upgrading to
     5.1.21 or higher, and then reload them to recreate them
     with new definitions. Invoke mysqldump with a
     --default-character-set
     (http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#op
     tion_mysqldump_default-character-set) option that names
     the non-ASCII character set that was used for the
     definitions when the objects were originally created.

     Upgrading for triggers in particular must be handled
     carefully, for two reasons:

        + The output from mysqldump does not contain a DROP
          TRIGGER
          (http://dev.mysql.com/doc/refman/5.1/en/drop-trigger
          .html) statement preceding each CREATE TRIGGER
          (http://dev.mysql.com/doc/refman/5.1/en/create-trigg
          er.html) statement, so reloading the dump file will
          fail to re-create the triggers unless you manually
          drop them after generating the dump file and before
          reloading it.

        + If you are upgrading from a very old version of
          MySQL 5.0 (before 5.0.10), the trigger upgrade
          procedure is different because triggers for those
          versions were created using a different namespace
          (trigger names had to be unique per table, rather
          than per schema as is true now). For instructions on
          upgrading triggers from old 5.0 versions, see
          Upgrading from MySQL 4.1 to 5.0
          (http://dev.mysql.com/doc/refman/5.0/en/upgrading-fr
          om-previous-series.html).

     Assuming that you are upgrading from MySQL 5.0.10 to
     5.1.20 to MySQL 5.1.21 or later, use the following
     procedure to upgrade your triggers:

        + Use mysqldump to generate a dump file that contains
          the trigger definitions:

mysqldump --triggers--no-create-db --no-data
        --no-create-info --all-databases > triggers.sql

          You might need to add options to specify connection
          parameters, such as --user
          (http://dev.mysql.com/doc/refman/5.1/en/mysqldump.ht
          ml#option_mysqldump_user) or --password
          (http://dev.mysql.com/doc/refman/5.1/en/mysqldump.ht
          ml#option_mysqldump_password). Also, if you are
          updating from a version of MySQL 5.1 older than
          5.1.21, you may need to include a
          --default-character-set option that specifies the
          non-ASCII character set that was used for the
          definitions when the triggers were originally
          created.

          Otherwise, invoke mysqldump with exactly the
          preceding options to avoid generating a dump file
          that will not have the intended effect when
          reloaded. For example, if you omit the --no-create-db
          option, your databases will be removed and recreated
          with no contents when you reload the dump file.

        + Drop existing triggers. To see which triggers exist,
          use this statement:

SELECT TRIGGER_SCHEMA, EVENT_OBJECT_TABLE, TRIGGER_NAME
FROM INFORMATION_SCHEMA.TRIGGERS;

          To generate DROP TRIGGERS statements for the
          triggers, use this statement:

SELECT CONCAT('DROP TRIGGER ', TRIGGER_SCHEMA, '.', TRIGGER_NAME, ';')
FROM INFORMATION_SCHEMA.TRIGGERS
INTO OUTFILE '/tmp/drop_triggers.sql';

          The statement uses INTO OUTFILE, so you must have
          the FILE
          (http://dev.mysql.com/doc/refman/5.1/en/privileges-p
          rovided.html#priv_file) privilege. The file will be
          created on the server host. Use a different file
          name if you like. To be 100% safe, inspect the
          trigger definitions in the drop_triggers.sql file,
          and perhaps make a backup of the file. Then execute
          the statements in the file:

mysql --force < /tmp/drop_triggers.sql

        + Recreate the triggers by reloading the dump file
          created earlier:

mysql --force < triggers.sql