| Bug #41907 | SHOW EVENTS shows events in other schema | ||
|---|---|---|---|
| Submitted: | 7 Jan 2009 0:28 | Modified: | 21 Mar 2011 19:26 |
| Reporter: | Serdar S. Kacar | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.1.30/6.0 | OS: | Any |
| Assigned to: | Christopher Powers | CPU Architecture: | Any |
| Tags: | events, mysqldump | ||
[8 Jan 2009 22:52]
MySQL Verification Team
Thank you for the bug report. c:\dbs>5.1\bin\mysqldump -uroot -p --port=3510 --databases --events k ka Enter password: -- MySQL dump 10.13 Distrib 5.1.31, for Win32 (ia32) -- -- Host: localhost Database: k -- ------------------------------------------------------ -- Server version 5.1.31-nt-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Current Database: `k` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `k` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `k`; -- -- Dumping events for database 'k' -- /*!50106 SET @save_time_zone= @@TIME_ZONE */ ; mysqldump: Couldn't execute 'SHOW CREATE EVENT `PerDayEvt`': Unknown event 'PerDayEvt' (1539) c:\dbs>5.1\bin\mysqldump -uroot -p --port=3510 --databases --events ka Enter password: -- MySQL dump 10.13 Distrib 5.1.31, for Win32 (ia32) -- -- Host: localhost Database: ka -- ------------------------------------------------------ -- Server version 5.1.31-nt-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Current Database: `ka` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `ka` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `ka`; -- -- Dumping events for database 'ka' -- /*!50106 SET @save_time_zone= @@TIME_ZONE */ ; /*!50106 DROP EVENT IF EXISTS `PerDayEvt` */; DELIMITER ;; /*!50003 SET @saved_cs_client = @@character_set_client */ ;; /*!50003 SET @saved_cs_results = @@character_set_results */ ;; /*!50003 SET @saved_col_connection = @@collation_connection */ ;; /*!50003 SET character_set_client = latin1 */ ;; /*!50003 SET character_set_results = latin1 */ ;; /*!50003 SET collation_connection = latin1_swedish_ci */ ;; /*!50003 SET @saved_sql_mode = @@sql_mode */ ;; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES' */ ;; /*!50003 SET @saved_time_zone = @@time_zone */ ;; /*!50003 SET time_zone = 'SYSTEM' */ ;; /*!50106 CREATE EVENT `PerDayEvt` ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN CALL `ka`.`PerDay`(); END */ ;; /*!50003 SET time_zone = @saved_time_zone */ ;; /*!50003 SET sql_mode = @saved_sql_mode */ ;; /*!50003 SET character_set_client = @saved_cs_client */ ;; /*!50003 SET character_set_results = @saved_cs_results */ ;; /*!50003 SET collation_connection = @saved_col_connection */ ;; DELIMITER ; /*!50106 SET TIME_ZONE= @save_time_zone */ ; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2009-01-08 23:40:29 c:\dbs>
[20 May 2009 0:56]
Jim Winstead
This happens because SHOW EVENTS actually treats the current database (and anything passed to it using "SHOW EVENTS FROM '...'") as just a prefix.
jimw@garuda:~/my/mysql-5.1 $ mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 399
Server version: 5.1.34-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show events\G
*************************** 1. row ***************************
Db: test_2
Name: once_per_day
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: DAY
Starts: 2009-05-19 17:49:21
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
[22 Jan 2011 7:12]
Sveta Smirnova
Workaround: create dummy event in k database.
[21 Mar 2011 19:26]
Paul DuBois
Noted in 5.1.57, 5.5.12, 5.6.3 changelogs. SHOW EVENTS did not always show events from the correct database. CHANGESET - http://lists.mysql.com/commits/133432
[27 Jun 2013 16:35]
x x
I'm a little unclear on the impact and resolution for this bug. First, is the message harmless or is my backup invalid. Next, I see the status is closed, but it's not clear for what versions it has been fixed. I'm experiencing it on 5.1.54. It's a hosted system, so I don't have a say on which version of MySQL to run, but I might be able to get the admins to install the latest patches. As written, though, I can't tell if there is a fix for 5.1, and if so at which level. Thank you for your work and your assistance.
[2 Jul 2013 10:14]
Georgi Kodinov
Depends on how you define your backup as valid. It does contain all of the data you've requested for. Plus some (and that's the error at hand). The version list this was fixed in is clearly stated by Paul in a comment right before yours :)
[2 Jul 2013 14:39]
x x
Thank you for your response on both points. Regarding "when is it fixed", I did not understand "Noted in" meant "Fixed in".
Description: I think is something with database names. - Have two databases named x and xy. - Create an event in xy. - mysqldump --events --databases x xy. How to repeat: mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 33 Server version: 5.1.30-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE DATABASE IF NOT EXISTS k; Query OK, 1 row affected (0.00 sec) mysql> mysql> CREATE DATABASE IF NOT EXISTS ka; Query OK, 1 row affected (0.00 sec) mysql> mysql> DELIMITER $$ mysql> mysql> DROP EVENT IF EXISTS `ka`.`PerDayEvt` $$ Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE EVENT `ka`.`PerDayEvt` -> ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01' -> DO -> BEGIN -> CALL `ka`.`PerDay`(); -> END $$ Query OK, 0 rows affected (0.00 sec) mysql> mysql> DELIMITER ; mysql> mysqldump -u root -p --events --databases k ka > kall.sql mysqldump: Couldn't execute 'SHOW CREATE EVENT `PerDayEvt`': Unknown event 'PerD ayEvt' (1539)