Description:
In 5.1
----------
mysql> CREATE USER 'trig_user'@'localhost' IDENTIFIED BY 'trig_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT INSERT, SELECT, UPDATE, DELETE on trigdb.* to 'trig_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'trig_user'@'localhost';
+------------------------------------------------------------------------------------------------------------------+
| Grants for trig_user@localhost |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'trig_user'@'localhost' IDENTIFIED BY PASSWORD '*23815845E5E220C48E55A82C239022AF42F58660' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `trigdb`.* TO 'trig_user'@'localhost' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
After upgrade to 5.5.3-m3
---------------------------
mysql> create database trigdb;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE trigdb.t7(i int);
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT TRIGGER ON trigdb.t7 to 'trig_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'trig_user'@'localhost';
+------------------------------------------------------------------------------------------------------------------+
| Grants for trig_user@localhost |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'trig_user'@'localhost' IDENTIFIED BY PASSWORD '*23815845E5E220C48E55A82C239022AF42F58660' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `trigdb`.* TO 'trig_user'@'localhost' |
| GRANT TRIGGER ON `trigdb`.`t7` TO 'trig_user'@'localhost' |
+------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Run mysql_upgrade now.
mysql> show grants for 'trig_user'@'localhost';
+------------------------------------------------------------------------------------------------------------------+
| Grants for trig_user@localhost |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'trig_user'@'localhost' IDENTIFIED BY PASSWORD '*23815845E5E220C48E55A82C239022AF42F58660' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `trigdb`.* TO 'trig_user'@'localhost' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
=>Trigger privilege is lost.
Or After downgrade to 5.1
----------------------------
mysql> show grants for 'trig_user'@'localhost';
+------------------------------------------------------------------------------------------------------------------+
| Grants for trig_user@localhost |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'trig_user'@'localhost' IDENTIFIED BY PASSWORD '*23815845E5E220C48E55A82C239022AF42F58660' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `trigdb`.* TO 'trig_user'@'localhost' |
| GRANT TRIGGER ON `trigdb`.`t7` TO 'trig_user'@'localhost' |
+------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Run mysql_upgrade (Not required after downgrade but let say you run it due to bug#50183)
mysql> show grants for 'trig_user'@'localhost';
+------------------------------------------------------------------------------------------------------------------+
| Grants for trig_user@localhost |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'trig_user'@'localhost' IDENTIFIED BY PASSWORD '*23815845E5E220C48E55A82C239022AF42F58660' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `trigdb`.* TO 'trig_user'@'localhost' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Trigger privilege is lost.
Same happens by just running mysql_upgrade on 5.5.3-m3 too before downgrading or on 5.1 after downgrading.
How to repeat:
As mentioned in how to repeat section.
