From: Dmitry Shulga Date: May 27 2011 8:17am Subject: bzr commit into mysql-5.1 branch (Dmitry.Shulga:3632) Bug#12546938 List-Archive: http://lists.mysql.com/commits/138286 X-Bug: 12546938 Message-Id: <201105270818.p4R8I8bs011851@acsmt356.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============6211253128155828555==" --===============6211253128155828555== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///Users/shulga/projects/mysql/mysql-5.1-bug12546938/ based on revid:dmitry.lenev@stripped 3632 Dmitry Shulga 2011-05-27 Fixed bug#12546938 (formerly known as 61005) - CREATE IF NOT EXIST EVENT will create multiple running events. A CREATE IF NOT EXIST on an event that existed and was enabled caused multiple instances of the event to run. Disabling the event didn't help. If the event was dropped, the event stopped running, but when created again, multiple instances of the event were still running. The only way to get out of this situation was to restart the server. The problem was that Event_db_repository::create_event() didn't return enough information to discriminate between situation when event didn't exist and was created and when event did exist and was not created (but a warning was emitted). As result in the latter case event was added to in-memory queue of events second time. And this led to unwarranted multiple executions of the same event. The solution is to add out-parameter to Event_db_repository::create_event() method which will signal that event was not created because it already exists and so it should not be added to the in-memory queue. @ mysql-test/r/events_bugs.result Added results for test for Bug#12546938. @ mysql-test/t/events_bugs.test Added test for Bug#12546938. @ sql/event_db_repository.cc Event_db_repository::create_event was modified: set newly added out-parameter event_already_exists to true value if event wasn't created because event already existed and IF NOT EXIST clause was present. @ sql/event_db_repository.h Added out-parameter 'event_already_exists' to create_event() method. @ sql/events.cc Events::create_event was modified: insert new element into event queue only if event was actually created. modified: mysql-test/r/events_bugs.result mysql-test/t/events_bugs.test sql/event_db_repository.cc sql/event_db_repository.h sql/events.cc === modified file 'mysql-test/r/events_bugs.result' --- a/mysql-test/r/events_bugs.result 2011-03-28 15:31:37 +0000 +++ b/mysql-test/r/events_bugs.result 2011-05-27 08:17:56 +0000 @@ -756,6 +756,45 @@ SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation DROP DATABASE event_test1; DROP DATABASE event_test12; +# +# Bug#12546938 (formerly known as bug#61005): +# CREATE IF NOT EXIST EVENT WILL CREATE MULTIPLE RUNNING EVENTS +# +USE events_test; +SET GLOBAL event_scheduler = ON; +DROP TABLE IF EXISTS table_bug12546938; +DROP EVENT IF EXISTS event_Bug12546938; +CREATE TABLE table_bug12546938 (i INT); +# Create an event which will be executed with a small delay +# and won't be automatically dropped after that. +CREATE EVENT event_Bug12546938 +ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE +ENABLE DO +BEGIN +INSERT INTO table_bug12546938 VALUES(1); +END +| +# Now try to create the same event using CREATE EVENT IF NOT EXISTS. +# A warning should be emitted. A new event should not be created nor +# the old event should be re-executed. +CREATE EVENT IF NOT EXISTS event_bug12546938 +ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE +ENABLE DO +BEGIN +INSERT INTO table_bug12546938 VALUES (1); +END +| +Warnings: +Note 1537 Event 'event_bug12546938' already exists +# Wait until at least one instance of event is executed. +# Check that only one instance of our event was executed. +SELECT COUNT(*) FROM table_bug12546938; +COUNT(*) +1 +# Clean-up. +DROP EVENT IF EXISTS event_Bug12546938; +DROP TABLE table_bug12546938; +SET GLOBAL EVENT_SCHEDULER = OFF; DROP DATABASE events_test; SET GLOBAL event_scheduler= 'ON'; SET @@global.concurrent_insert= @concurrent_insert; === modified file 'mysql-test/t/events_bugs.test' --- a/mysql-test/t/events_bugs.test 2011-03-28 15:31:37 +0000 +++ b/mysql-test/t/events_bugs.test 2011-05-27 08:17:56 +0000 @@ -1235,6 +1235,55 @@ SHOW EVENTS; DROP DATABASE event_test1; DROP DATABASE event_test12; +--echo # +--echo # Bug#12546938 (formerly known as bug#61005): +--echo # CREATE IF NOT EXIST EVENT WILL CREATE MULTIPLE RUNNING EVENTS +--echo # +USE events_test; +SET GLOBAL event_scheduler = ON; + +--disable_warnings +DROP TABLE IF EXISTS table_bug12546938; +DROP EVENT IF EXISTS event_Bug12546938; +--enable_warnings +CREATE TABLE table_bug12546938 (i INT); + +delimiter |; + +--echo # Create an event which will be executed with a small delay +--echo # and won't be automatically dropped after that. +CREATE EVENT event_Bug12546938 +ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE +ENABLE DO +BEGIN + INSERT INTO table_bug12546938 VALUES(1); +END +| + +--echo # Now try to create the same event using CREATE EVENT IF NOT EXISTS. +--echo # A warning should be emitted. A new event should not be created nor +--echo # the old event should be re-executed. +CREATE EVENT IF NOT EXISTS event_bug12546938 +ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE +ENABLE DO +BEGIN + INSERT INTO table_bug12546938 VALUES (1); +END +| + +delimiter ;| + +--echo # Wait until at least one instance of event is executed. +let $wait_condition= SELECT COUNT(*) FROM table_bug12546938; +--source include/wait_condition.inc + +--echo # Check that only one instance of our event was executed. +SELECT COUNT(*) FROM table_bug12546938; + +--echo # Clean-up. +DROP EVENT IF EXISTS event_Bug12546938; +DROP TABLE table_bug12546938; +SET GLOBAL EVENT_SCHEDULER = OFF; ########################################################################### # === modified file 'sql/event_db_repository.cc' --- a/sql/event_db_repository.cc 2011-05-04 12:59:24 +0000 +++ b/sql/event_db_repository.cc 2011-05-27 08:17:56 +0000 @@ -604,18 +604,21 @@ Event_db_repository::open_event_table(TH only creates a record on disk. @pre The thread handle has no open tables. - @param[in,out] thd THD - @param[in] parse_data Parsed event definition - @param[in] create_if_not TRUE if IF NOT EXISTS clause was provided - to CREATE EVENT statement - + @param[in,out] thd THD + @param[in] parse_data Parsed event definition + @param[in] create_if_not TRUE if IF NOT EXISTS clause was provided + to CREATE EVENT statement + @param[out] event_already_exists When method is completed successfully + set to true if event already exists else + set to false @retval FALSE success @retval TRUE error */ bool Event_db_repository::create_event(THD *thd, Event_parse_data *parse_data, - my_bool create_if_not) + bool create_if_not, + bool *event_already_exists) { int ret= 1; TABLE *table= NULL; @@ -641,6 +644,7 @@ Event_db_repository::create_event(THD *t { if (create_if_not) { + *event_already_exists= true; push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, ER_EVENT_ALREADY_EXISTS, ER(ER_EVENT_ALREADY_EXISTS), parse_data->name.str); @@ -648,8 +652,10 @@ Event_db_repository::create_event(THD *t } else my_error(ER_EVENT_ALREADY_EXISTS, MYF(0), parse_data->name.str); + goto end; - } + } else + *event_already_exists= false; DBUG_PRINT("info", ("non-existent, go forward")); === modified file 'sql/event_db_repository.h' --- a/sql/event_db_repository.h 2007-08-15 15:08:44 +0000 +++ b/sql/event_db_repository.h 2011-05-27 08:17:56 +0000 @@ -73,7 +73,8 @@ public: Event_db_repository(){} bool - create_event(THD *thd, Event_parse_data *parse_data, my_bool create_if_not); + create_event(THD *thd, Event_parse_data *parse_data, bool create_if_not, + bool *event_already_exists); bool update_event(THD *thd, Event_parse_data *parse_data, LEX_STRING *new_dbname, === modified file 'sql/events.cc' --- a/sql/events.cc 2011-05-16 20:04:01 +0000 +++ b/sql/events.cc 2011-05-27 08:17:56 +0000 @@ -370,6 +370,7 @@ create_query_string(THD *thd, String *bu return 0; } + /** Create a new event. @@ -390,8 +391,8 @@ bool Events::create_event(THD *thd, Event_parse_data *parse_data, bool if_not_exists) { - int ret; - bool save_binlog_row_based; + bool ret; + bool save_binlog_row_based, event_already_exists; DBUG_ENTER("Events::create_event"); /* @@ -440,7 +441,8 @@ Events::create_event(THD *thd, Event_par pthread_mutex_lock(&LOCK_event_metadata); /* On error conditions my_error() is called so no need to handle here */ - if (!(ret= db_repository->create_event(thd, parse_data, if_not_exists))) + if (!(ret= db_repository->create_event(thd, parse_data, if_not_exists, + &event_already_exists))) { Event_queue_element *new_element; bool dropped= 0; @@ -460,7 +462,7 @@ Events::create_event(THD *thd, Event_par { /* TODO: do not ignore the out parameter and a possible OOM error! */ bool created; - if (event_queue) + if (event_queue && !event_already_exists) event_queue->create_event(thd, new_element, &created); } /* @@ -475,13 +477,14 @@ Events::create_event(THD *thd, Event_par { sql_print_error("Event Error: An error occurred while creating query string, " "before writing it into binary log."); - /* Restore the state of binlog format */ - thd->current_stmt_binlog_row_based= save_binlog_row_based; - DBUG_RETURN(TRUE); + ret= true; } - /* If the definer is not set or set to CURRENT_USER, the value of CURRENT_USER - will be written into the binary log as the definer for the SQL thread. */ - ret= write_bin_log(thd, TRUE, log_query.c_ptr(), log_query.length()); + else + /* + If the definer is not set or set to CURRENT_USER, the value of CURRENT_USER + will be written into the binary log as the definer for the SQL thread. + */ + ret= write_bin_log(thd, TRUE, log_query.c_ptr(), log_query.length()); } } pthread_mutex_unlock(&LOCK_event_metadata); --===============6211253128155828555== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/dmitry.shulga@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: dmitry.shulga@stripped\ # khjrlxqee12a1b4c # target_branch: file:///Users/shulga/projects/mysql/mysql-5.1-\ # bug12546938/ # testament_sha1: 3c713609b795682e9139da46cd0b9762ef60c5c6 # timestamp: 2011-05-27 15:18:02 +0700 # base_revision_id: dmitry.lenev@stripped\ # 32ch5c5hm224hhlh # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWcMUgCsAB1X/gHd4gAF///// f+f/yr////ZgEL19fO+7e+Hrc8e+99ZgA0nNvvTpPe098c3VoK0b7cBKqiqVdg+2p6ebhJTUZTKb RqeSnqfkozRqaZqeUNAGmmmaekR6h6gA0HqAEkmgjRoI0mEptT9Sepo0PUIDEGAg0GgwRgZBKEDS Sn+lPSnkwpptQDQNAAZGgAAAAABIkQim9JtU8oGR6ZNEPSGmCD1AAPU0ZA000000aBFJCaZNNTIp +ApmQ9IBPU0TQeU00aAADEADQSJBCniGg1MjQDSaaoeKepoMmEeoGI0AAeo9QrPg0J6uEybrOYxL HR2cka5V0HiGSrlKbmM4eC4cna+9FFM77s+u6mJsuPPlO1D+e+Ofo8urmp1sDl4tFPb4G6VqvVTq vYpptabCiLbbzvx0d8Kzj8lHOvL6Qu0A9bIIhe4GGedMu4GHhOXiqquMQEOpvVPrvzVcRXsv2I9z BJPFFCPqs0kTyvFf1b9FuPF9msNChn0khEwfkwtMHpiyBoAgeKGKJSjvsBoaIu1Teu4jOmbsLd/7 gzd6bk4kgiIhJtJjMf5QYp3XXvg41WY6SoWPTqnRSVVRqO03OcEjLjZXlWUax2VksViqXQa5aOwY mk+jX2xjYC85JkBhiM3A3V0GLmgaKyAYoPFbC2/aCIGHLSSEhbwMYy1g2samBaRrO5IL51TMEgoM 4RYbp6AZmFBh0BLz9e4qMyz6gxMDV/c3ePPhGOZT6X6ZkZRgzVJLu9goTRMcYZhzSw94/V8/CV2a ugVxlAwM+EB5jLxXzL/x89S8Zj0c7DuOTHiwR0MYPEbQ0DVjqT39ChYGtpixrtJESef76U3lbotn hWkTl7kBR3zgYvGl6hIs9SfZEvjT9CLvINJE1ITJ2MzFxBhVyizS1ULq0V6LAtF9M3ElOUmRZtrU u4EETnp5S+/NqPvPdD/q6DkbaCMbX8iR+1t0+G7hBR2dZrJ310S4wTvv5zsvpXjEOuJ1HWTDQcTp lzO7W4Buk/cZ7J0wRhTDOqsu772NTc2zawYPPRdPAWPZmzvSRXPSrrvCKWJGWwY22xtjbbG31EoR le/fwyMAnUqmrKHYe5z5mS39N1+/MZruzfiTD3sYdqA98/fB3LIHPrmfnYU5LMY6GbvXcEZxtLUk sKtirFJkQjbWbImHZcawWLMJY0lhLlDm5VaWJZRLdtFRvQlVE9yCwHK8FXltKqjH6RjBYKNmfVt2 E2s+SplJaVMLIzWFJFkBi3FCTCwnRt2JTRfTI1VmtIn4dHB4MAmTQNoJMXTuUUCSYB1j9VsIRNr3 m8UCmYciY5XCtPNFTZMkF8AWhAVwqYzgTkSHZJXYQVlYlVklpLS4sqegnXSapQuTDS0OdHIOkNs7 T3VvmnRLT3NBl46bWtEwtF7oKHIw0HKHcwsEA5pqtFOYqs3lY9O/JlSdVehayhoYDox2GS9zgMf5 fYcSutz7C/x5yHML8oRjmjdwICWqKGxccopK4sQxFfHVmKKNlb2ACTIRJRFlOcxcUeWagKi9ZQFX DzEjsPTwLVVpV1iLYvBLi3SeqwHYUBLvz4GvEv00oc8OcwJjCW/AxKrHvPy0pXMXvCpDS/B21tDa m7SXY2pOMB1u0Don79xcQpAiPoMzlilrhLFMJRm6YxzKxWC4li4jMzPErLx9VJk5QwyvfR8GkjLs gRcz3XyV+28jgSIbyCq1MpFjcqHCwJW8eVVPAZOJmYhmms3QixEWBTXVOhcCxkPEpFbvMmXrrxrP yKpTbK7k0d8d82l2Z4LlK54dIKUyLsSok6A5cnJL0FzcEszsT35HRZQmJZRNTjXOMiZe1Sdy3Q6r y5dyqSmDrqu0dU9krbFZw159VdbEoqKxi6jZBLyHY8k9Rr55TOHC4WyhCUQFu/Q9fZSCM8tiAJeK ubbb34OQFqxkXFDcVw9BQXReiu1QXnTrR9xA04Wynq6mceMKdxsLM3wEsTngUJyNzLlhyXcrZ1Gb 9cG9JQubhFdlZFs0O3drrvnVLYZ7S4kYnQuldm2uARVykh263EghyaJmd5KgoiTMeqgmcy54FX0G KhiqVHew9dNr9nhX2155qIhmLVTVGwus7I2nB8d4iJO+mMeNiexKWIclMbLhi8nrgVneamZkR7fb O2ZatUR9lXOzRy1ctbiGynKdUStiLWoExWlKzSoiJuBRqHS2xzwAuo/0ve3buheZKMLpFUyAZIjS NkTDJO22y2iwJoyzJNIS7306d2ugv1bmdWwMMBiDuH+w5QgJBMMqSwqC9aBjabHmBh6RFNAf4XjF ASCiExjGkyFALkuA2EBMCYo53HcDMBnpMMJhip+Lg3wpLSIRVhag6sBeIKCeY0WwYWwmHxCQUBcE B5s+E+UAxnRJqd8vhILhyigA/pIOAKSd5tiJi5gDjnohYMPNwDiL6RgzTjhvMgcoG5RCAQDIcICV AGCBmTJCHCQk4QEQEYxPqDQMgc0JkBgcKAxAIgXCDBoDfAUGCYfjTYLElcLIN60gahVDUcxAEDGg YyA6yZAaM0DQJg4RzQ4MkQ/nKy3hmtwi4JAaAybG0rRQFUlIP9/XoC0NxQPfdBckMCNJeVDWLMka A0iqYEwtBhgIICbgA9Ic3OQuUCBPVtJEDELuEkSCEYXvWUMkhEhgBrAizZxjMJSWRxoURIMRXUxw kAThLkJDxyxHEcJbOQtHruJLj0WE6FeuOdLYWLTpEh9+dhgZJVFvzXqyBhkFKBTwGwUeTTtnqmJp nHXEUHwmQZrFEFyotiAncD5u8fhxBG9GzQD3L8IlWGGBN8ivN3WWRw8iuGCHyBEqyEYWRIOJ80F1 12k5FhlmLaTzc50VdB9dxqecpFxMsztdrnzG3WTCsyZ4aF4FxpN4KwaxtPJdC+O+rxAP1v/vw+FY Zr3LlZmpDbCB3zcu88jtxnWA5DbMMmE1EGU43kJzKHqO039FD1GV47CCiKjM7S8mvPUFMDCRHaWT 3KGhiChWBBlxWZESKYLISOcqcjgOogx/z4QIxvLULpwhFwNDNEdjUkznZRjGmE4IHUbfii2EFOc6 L9x3G88tCZguk8DKDkQZJZELpyOZmlKvEkVSNhwD288IVSUz6Ezqs4C4K5GBhZtNJ5WWB4Cg0Ltn cdJa+xfsPsE0RqXjaovzNQk/ZzBt6u6YxxAxkyF6xjkdIqk9msJlk7azGYcgxQnBHqTJPQndMpO+ CQufaeBWwl6b97eUyHHeSobLnO7ospyI03HgQbeCNaMdTWbIj4tqFYEyyEhwASMxlxwLMEKmE5jI b5SYTWDUalj8SDhpJdxCJ6Z8ZqqrEUZCpOEFrK9RI294lr5Sryq/b+9Yi7/Oy3e39uKniy+P5vbL XEl2E1N2b6VDKzsAbBLre9pAQhgJoRzKo8hhPE6Jbdb6pujObHS0xPcCg8GStQ6snB5Ch3XhmtUH PaF2xoaHfTFwz/XFxcbpYydQtjYb3KAlJzURCiRpAxDKSoMMGGiIgRtSNeZ62MY19URD086vESdj 1kjTtKg4i0yePtDtwlY1niJi0wDa8rI9SYMqQlqXnyLpJhnlwthczxa0GWEuWUiSp4fhd1DsAK+h hOSELfG1hS53s1uaZnbsRSJBwcLQ6uUxbH3ZpQkPldrjm0g0LMwFLCPnICIzGH0GConyx4n4tjim aziFzvkdLgBrbzLfXVNsdbU62T9NIsXKHKX3DBdc1TE7LBs4OTsAMUzNcVaPLAUsXgMfna2ZpL6J zaQmiG3OVnP7JE8GR49tOsDA4bZ6zJYsVeaiDh5+HNkjmbbE0VXBt7v1PoA8hBMzDPcc5rtMBKYk UcHkL7IRu+XF4XO3PALzHLiaGfmYxkuFndjR4t97qBbgLGpzsT+9Nbd/sNZmEDG2+ifOg3BNcVaB FJtnyaLyRS8JkCwh7Hj1YkhioUhLkYEg3FhKIs5w5jldtkAcVzalaXVmDGeSutJq/oZ2XTSTToLO ZpJjnPa47LhaeSHBMw3WX0SOBlWHgKWYjpdBRbWjsLiqoKlFs3OWKa9B2IC5eGxaA9quKF+Aqeta FZ2q3Syxai2zTGawWCcmThaRmyJEmUOEjNMU03YSwRJVSbPnrApAOAIKqgQkow4HJawQ0XyL4dDY 9eA4oGGyh5N5wNVa3kRMGAkSTYCai3f1HEE0jaxSkslfC612zJ+RrEbE3Nw74zmGsUwkdTGOiA8B AkzMGjqkE+IdIXPpsNhYz3CRUCEtMbBgECW6xBuf17PLkcU6VSxM2Hs9+u8dgcTpyuQi7YG1cpIa Xu7/mnNFF8coZMjvOkKA629yC0hjobiwsie3gcJf1UPTuEujNI46l3GCxV5khjvGeiRA2Y/HZI+V ni++wVvrdptLKcJj1i9pjRK0d4Wqk0JB1fwS1bpCtZNfh7Wx4CUlsl3LfqLwW7pjDBND21MVWatA zCUSmyBgOUBChiUA+I5obPBwyGOHAOhjI9lKSHAiojSTXtUpoDlUrU7hYBp0lcblxDZc1HUF0onX hlUP8m1aKB5M0Iql1iTwNIGYlIoj25LewmamX/Mjk8YiIiIiIiCS81CZsSxGWjSNgndMiNDsAmJR NKW9VPodkUlaZVhkKUFRVgEyJngkDPWsyJAk7sVUHkMsBkRcxffBFB062jJNUYNZHb1WDjKdbi5I MBkKTSqz0iIqGSFCCkhTrAKY1VltpuYmPlODyKQ/UO74HMOw1KjxEQmd1URA8PUIAi9PqVNbM9Zl XgYbLFBGgK3W29bA8WpNWFUErWxDa8e6VQmxiCgc04SYNMRiznTSoGLtssNBQrpCauLIo0FaUlXp 6yu512CCHVBt64reYGDGgbGxjMNCgR08FNLEoAbTSLwB4Bmb+wnq81et0kWGmbaN4G50Ml98zVpO D6mIAgIdTASgHFVTXOBqe1zON1dbeaXn9mJ5jFJzwLlhD1zSfQ11sxngzPwZNV++Jk62nCziGUvi wkPRMT0ZBJRANjBsEECJFKwLCtRYkXlNl0W2iP0prZXnLj52XI/MXckU4UJDDFIArA== --===============6211253128155828555==--