| Bug #20689 | Events: need to select before I can drop | ||
|---|---|---|---|
| Submitted: | 25 Jun 2006 20:32 | Modified: | 28 Jun 2006 15:29 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.1.12-beta-debug | OS: | Linux (SUSE 10.0 / 64-bit) |
| Assigned to: | Andrey Hristov | CPU Architecture: | Any |
[25 Jun 2006 20:54]
MySQL Verification Team
Thank you for the bug report. I was unable to repeat on FC 5 with server built with the below changeset: [miguel@hegel mysql-5.1]$ bk changes | head ChangeSet@1.2214, 2006-06-24 13:14:24+02:00, knielsen@rt.int.sifira.dk Merge mysql.com:/usr/local/mysql/tmp-5.0 into mysql.com:/usr/local/mysql/tmp-5.1 ChangeSet@1.1810.1349.156, 2006-06-24 13:11:09+02:00, knielsen@mysql.com Fix race condition in test case wait_timeout. Is your changeset never than the above? [miguel@hegel 5.1]$ bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.1.12-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database db88; Query OK, 1 row affected (0.02 sec) mysql> use db88; Database changed mysql> create event e1 on schedule every 1 second do call pf4(1000,100); Query OK, 1 row affected (0.01 sec) mysql> quit Bye [miguel@hegel 5.1]$ bin/mysqladmin shutdown -uroot skipped 9 bytes from file: socket (3) [miguel@hegel 5.1]$ bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.1.12-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> drop event db88.e1; Query OK, 1 row affected (0.00 sec) mysql> select * from mysql.event where db='db88' and name='e1'\G Empty set (0.01 sec) mysql> Thanks in advance.
[25 Jun 2006 23:45]
Peter Gulutzan
My changeset is slightly newer.
pgulutzan@linux:~/mysql-5.1> bk changes | head
ChangeSet@1.2215, 2006-06-25 10:25:00+02:00, kent@mysql.com
.del-make_win_src_distribution.sh~f80d8fca44e4e5f1:
Delete: scripts/make_win_src_distribution.sh
Makefile.am:
Unix and Windows now shares source package
ChangeSet@1.2214, 2006-06-24 13:14:24+02:00, knielsen@rt.int.sifira.dk
Merge mysql.com:/usr/local/mysql/tmp-5.0
into mysql.com:/usr/local/mysql/tmp-5.1
[26 Jun 2006 12:28]
MySQL Verification Team
Thank you for the feedback. Still with latest BK source I was unable to repeat. I will ask someone for to test too. miguel@hegel:~/dbs/5.1> bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.1.12-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database db88; Query OK, 1 row affected (0.03 sec) mysql> use db88; Database changed mysql> create event e1 on schedule every 1 second do call pf4(1000,100); Query OK, 1 row affected (0.01 sec) mysql> quit; Bye miguel@hegel:~/dbs/5.1> bin/mysqladmin shutdown -uroot skipped 9 bytes from file: socket (3) miguel@hegel:~/dbs/5.1> bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.1.12-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> drop event db88.e1; Query OK, 1 row affected (0.01 sec) mysql> select * from mysql.event where db='db88' and name='e1'\G Empty set (0.01 sec) mysql>
[26 Jun 2006 12:47]
Tonci Grgin
Hi Peter, Miguel. Verified as described on Suse 10.0 munja:/home/Tonci/bkwork/mysql-5.1 # bk changes | head ChangeSet@1.2217, 2006-06-25 16:04:11-04:00, elliot@mysql.com Post merge fix Client console: munja:/home/scratch # /home/Tonci/bkwork/copyto/mysql-5-1/bin/mysql -uroot --port=3307 --socket=/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.1.12-beta Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database db88; Query OK, 1 row affected (0.05 sec) mysql> use db88; Database changed mysql> create event e1 on schedule every 1 second do call pf4(1000,100); Query OK, 1 row affected (0.00 sec) mysql> quit Bye munja:/home/scratch # /home/Tonci/bkwork/copyto/mysql-5-1/bin/mysqladmin -uroot --port=3307 --socket=/tmp/mysql.sock shutdown munja:/home/scratch # /home/Tonci/bkwork/copyto/mysql-5-1/bin/mysql -uroot --port=3307 --socket=/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.1.12-beta Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use db88; Database changed mysql> drop event db88.e1; ERROR 1526 (HY000): Unknown event 'e1' mysql> select version(); +-------------+ | version() | +-------------+ | 5.1.12-beta | +-------------+ 1 row in set (0.00 sec) Server console: munja:/home/Tonci/bkwork/copyto/mysql-5-1 # bin/mysqld_safe --no-defaults --port=3307 Starting mysqld daemon with databases from /home/Tonci/bkwork/copyto/mysql-5-1/var STOPPING server from pid file /home/Tonci/bkwork/copyto/mysql-5-1/var/munja.pid 060626 14:37:49 mysqld ended munja:/home/Tonci/bkwork/copyto/mysql-5-1 # bin/mysqld_safe --no-defaults --port=3307 Starting mysqld daemon with databases from /home/Tonci/bkwork/copyto/mysql-5-1/var
[26 Jun 2006 14:51]
Andrey Hristov
Tonci, did you reproduce it on 32 or 64 bit? I can't, my CSet is (which is the same as yours) andrey@lmy004:/work/mysql-5.1-clean/sql> bk changes | head ChangeSet@1.2217, 2006-06-25 16:04:11-04:00, elliot@mysql.com Post merge fix ChangeSet@1.2216, 2006-06-25 09:59:34-04:00, elliot@mysql.com Merge mysql.com:/home/emurphy/src/bk-clean/tmp_merge into mysql.com:/home/emurphy/src/bk-clean/mysql-5.1
[26 Jun 2006 17:16]
Andrey Hristov
Hi Tonci, please try to reproduce it. Start the server with --debug so you can get a trace log, actually 2. Please pack them and attach to the report, thanks!
[28 Jun 2006 14:01]
Tonci Grgin
Andrey, I repeated the problem with new changeset. Build script used is compile-pentium-debug. What's puzzling me is mysql> drop event e1; ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' Cleanup after last test: munja:/home/Tonci/bkwork/copyto/mysql-5-1 # bin/mysql -uroot --port=3307 --socket=/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 5.1.12-beta Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | Bug20084 | | cluster | | db88 | | mysql | | test | +--------------------+ 6 rows in set (0.00 sec) mysql> drop database db88; Query OK, 0 rows affected (0.00 sec) mysql> use information_schema; Database changed mysql> select * from events; Empty set (0.02 sec) mysql> quit Bye New test: munja:/home/Tonci/bkwork/copyto/mysql-5-1 # bin/mysql -uroot --port=3307 --socket=/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.1.12-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select version(); +-------------------+ | version() | +-------------------+ | 5.1.12-beta-debug | +-------------------+ 1 row in set (0.00 sec) mysql> create database db88; Query OK, 1 row affected (0.00 sec) mysql> use db88; Database changed mysql> create event e1 on schedule every 1 second do call pf4(1000,100); Query OK, 1 row affected (0.02 sec) mysql> quit Bye munja:/home/Tonci/bkwork/copyto/mysql-5-1 # bin/mysqladmin -uroot --port=3307 --socket=/tmp/mysql.sock shutdown munja:/home/Tonci/bkwork/copyto/mysql-5-1 # bin/mysql -uroot --port=3307 --socket=/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.1.12-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> drop event db88.e1; ERROR 1526 (HY000): Unknown event 'e1' mysql> use db88; Database changed mysql> drop event db88.e1; ERROR 1526 (HY000): Unknown event 'e1' mysql> drop event e1; ERROR 1526 (HY000): Unknown event 'e1' mysql> use information_schema Database changed mysql> drop event e1; << WEIRD! ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' mysql> use mysql Database changed mysql> select * from user\G *************************** 1. row *************************** Host: localhost User: root Password: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 *************************** 2. row *************************** Host: % User: root Password: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 2 rows in set (0.01 sec) mysql> select * from EVENTS\G ERROR 1146 (42S02): Table 'mysql.EVENTS' doesn't exist mysql> use information_schema Database changed mysql> select * from EVENTS\G *************************** 1. row *************************** EVENT_CATALOG: NULL EVENT_SCHEMA: db88 EVENT_NAME: e1 DEFINER: root@localhost EVENT_BODY: SQL EVENT_DEFINITION: call pf4(1000,100) EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 1 INTERVAL_FIELD: SECOND SQL_MODE: STARTS: 2006-06-28 13:46:47 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2006-06-28 15:46:47 LAST_ALTERED: 2006-06-28 15:46:47 LAST_EXECUTED: NULL EVENT_COMMENT: 1 row in set (0.03 sec) mysql> drop database db88; Query OK, 0 rows affected (0.00 sec) mysql> select * from EVENTS\G Empty set (0.03 sec) mysql> quit Bye munja:/home/Tonci/bkwork/copyto/mysql-5-1 # bin/mysqladmin -uroot --port=3307 --socket=/tmp/mysql.sock shutdown skipped 9 bytes from file: socket (3) munja:/home/Tonci/bkwork/copyto/mysql-5-1 #
[28 Jun 2006 14:51]
Tonci Grgin
Problem was in old structure of mysql.event table. Running mysql_fix_privilege_tables script solves it.
[28 Jun 2006 15:29]
Andrey Hristov
If the mysql.event was old, that is it has Primary Key (definer, db, name) there was a problem. The right PK now is (db, name)
Description: Create a new database. Create an event in the new database. Shut down mysql server. Start again. Try to drop the event -- this fails. Select from mysql.event. Try to drop the event --this succeeds. How to repeat: mysql> create database db88; Query OK, 1 row affected (0.00 sec) mysql> use db88; Database changed mysql> create event e1 on schedule every 1 second do call pf4(1000,100); Query OK, 1 row affected (0.00 sec) mysql> quit; Bye ... I used mysqladmin to stop mysqld. Then I started mysqld again. ... Then I started the client again. mysql> drop event db88.e1; ERROR 1526 (HY000): Unknown event 'e1' mysql> select * from mysql.event where db='db88' and name='e1'\G *************************** 1. row *************************** db: db88 name: e1 body: call pf4(1000,100) definer: root@localhost execute_at: NULL interval_value: 1 interval_field: SECOND created: 2006-06-25 11:13:37 modified: 2006-06-25 11:13:37 last_executed: NULL starts: 2006-06-25 17:13:37 ends: NULL status: ENABLED on_completion: DROP sql_mode: comment: 1 row in set (0.00 sec) mysql> drop event db88.e1; Query OK, 1 row affected (0.00 sec)