Bug #79873 | DROP TRIGGER after RENAME TABLE results in Error Code 1146: table doesn't exist. | ||
---|---|---|---|
Submitted: | 7 Jan 2016 14:11 | Modified: | 3 Oct 2016 18:11 |
Reporter: | Michael Vaughan | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.7.10 | OS: | MacOS (10.11.2) |
Assigned to: | CPU Architecture: | Any |
[7 Jan 2016 14:11]
Michael Vaughan
[7 Jan 2016 17:41]
Michael Vaughan
This bug appears to be specific to OSX. The bug has been verified on the following environments: - OSX 10.11.2/MySQL 5.7.10 - OSX 10.11.1/MySQL 5.6.19 The test script has worked as desired in the following environments: - Linux Mint 17.2 Cinnamon 64 bit/MySQL 5.5.46-0ubuntu0.14.04.2.
[7 Jan 2016 18:39]
MySQL Verification Team
On Windows: C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.11 Source distribution PULL: 2015-DEC-27 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > CREATE SCHEMA testschema DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; Query OK, 1 row affected (0.00 sec) mysql 5.7 > mysql 5.7 > USE testschema; Database changed mysql 5.7 > mysql 5.7 > CREATE TABLE Test( -> id int not null primary key auto_increment, -> name varchar(255) -> ); Query OK, 0 rows affected (0.03 sec) mysql 5.7 > mysql 5.7 > delimiter // mysql 5.7 > CREATE TRIGGER TEST_TRIGGER -> BEFORE INSERT ON Test -> FOR EACH ROW -> BEGIN -> SET NEW.name = CONCAT(NEW.name, '_X'); -> END// Query OK, 0 rows affected (0.02 sec) mysql 5.7 > delimiter ; mysql 5.7 > mysql 5.7 > RENAME TABLE Test TO TestRenamed; Query OK, 0 rows affected (0.03 sec) mysql 5.7 > DROP TRIGGER TEST_TRIGGER; Query OK, 0 rows affected (0.02 sec)
[7 Jan 2016 18:50]
MySQL Verification Team
Repeatable with 5.7.9. I will test 5.7.10 too: Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.9 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE SCHEMA testschema DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; Query OK, 1 row affected (0.00 sec) mysql> mysql> USE testschema; Database changed mysql> mysql> CREATE TABLE Test( -> id int not null primary key auto_increment, -> name varchar(255) -> ); Query OK, 0 rows affected (0.06 sec) mysql> mysql> delimiter // mysql> CREATE TRIGGER TEST_TRIGGER -> BEFORE INSERT ON Test -> FOR EACH ROW -> BEGIN -> SET NEW.name = CONCAT(NEW.name, '_X'); -> END// Query OK, 0 rows affected (0.34 sec) mysql> delimiter ; mysql> mysql> RENAME TABLE Test TO TestRenamed; Query OK, 0 rows affected (0.29 sec) mysql> mysql> DROP TRIGGER TEST_TRIGGER; ERROR 1146 (42S02): Table 'testschema.TestRenamed' doesn't exist mysql> show variables like "%versio%"; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.7.9 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.7.9 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | osx10.9 | +-------------------------+------------------------------+ 7 rows in set (0.00 sec)
[7 Jan 2016 19:05]
MySQL Verification Team
Thank you for the bug report. miguel:~ miguel$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.7.10 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE SCHEMA testschema DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; Query OK, 1 row affected (0.00 sec) mysql> mysql> USE testschema; Database changed mysql> mysql> CREATE TABLE Test( -> id int not null primary key auto_increment, -> name varchar(255) -> ); Query OK, 0 rows affected (0.08 sec) mysql> mysql> delimiter // mysql> CREATE TRIGGER TEST_TRIGGER -> BEFORE INSERT ON Test -> FOR EACH ROW -> BEGIN -> SET NEW.name = CONCAT(NEW.name, '_X'); -> END// Query OK, 0 rows affected (0.38 sec) mysql> delimiter ; mysql> mysql> RENAME TABLE Test TO TestRenamed; Query OK, 0 rows affected (0.32 sec) mysql> mysql> DROP TRIGGER TEST_TRIGGER; ERROR 1146 (42S02): Table 'testschema.TestRenamed' doesn't exist mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.7.10 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.10 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | osx10.9 | +-------------------------+------------------------------+ 8 rows in set (0.03 sec)
[3 Oct 2016 18:11]
Paul DuBois
Posted by developer: Noted in 5.7.17, 8.0.1 changelogs. On macOS, if a table with an associated trigger was renamed to a new name containing both lowercase and uppercase uppercase characters, DROP TRIGGER for the trigger resulted in an ER_NO_SUCH_TABLE error for the table.