Description:
Why is it not possible to turn on or off event-scheduling for a single database? Why is this a global, server-setting?
In this case, we're using the MySQL event-scheduling on some of our database we use on our development-server. During development, we regulary make backups of the database on the SQL-server. This way, we can easily switch between databases.
So for example, I've got a database named 'database_01'. I'm going to do some big changes in code or truncate some tables to start over again. First, I copy it to the database 'database_01_yyyymmdd' (where yyyymmdd is the current data). By doing this, I've now got 2 databases. The backup-database can be easily renamed back to 'database_01' when I need to get my old tables / structures / data back. For us, this works fine.
Today, I found out the MySQL-server had some heavy loads on the system, so had to figure out where this came from. I noticed we had about 12 backup-databases, all from various dates. Because some of them might come handy during the development of our software, these database's can't be removed yet. However, I found out that all the database had some events running. So I used phpMyAdmin to disable the event scheduler in some of the older databases in order to free some resources.
Unfortunately, the event-scheduling on the database named 'database_01' was also off. First, I thought I made a mistake, turning off the events at the wrong database, but when turning it on, I saw this took effect on ALL databases on the MySQL-server. (SET GLOBAL event_scheduler = 1). So now, all the databases has the event-scheduler turning on again.
It seams to be impossible to disable the event-scheduler for table A, but enable it for table B. The only way to remove the events from the processlist is to remove / disable them all one by one. It's not able to enable / disable them all at once for a single database?
How to repeat:
Create a database, add 100 scheduled events, copy the database... Now, try to turn off the 100 scheduled events in the copied database, without turning off the scheduled events in the original one, without having to disable all the 100 events one by one.
Suggested fix:
Would like to see some query / statement available like: 'SET event_scheduler = 1 {FROM | IN} database_01;
Description: Why is it not possible to turn on or off event-scheduling for a single database? Why is this a global, server-setting? In this case, we're using the MySQL event-scheduling on some of our database we use on our development-server. During development, we regulary make backups of the database on the SQL-server. This way, we can easily switch between databases. So for example, I've got a database named 'database_01'. I'm going to do some big changes in code or truncate some tables to start over again. First, I copy it to the database 'database_01_yyyymmdd' (where yyyymmdd is the current data). By doing this, I've now got 2 databases. The backup-database can be easily renamed back to 'database_01' when I need to get my old tables / structures / data back. For us, this works fine. Today, I found out the MySQL-server had some heavy loads on the system, so had to figure out where this came from. I noticed we had about 12 backup-databases, all from various dates. Because some of them might come handy during the development of our software, these database's can't be removed yet. However, I found out that all the database had some events running. So I used phpMyAdmin to disable the event scheduler in some of the older databases in order to free some resources. Unfortunately, the event-scheduling on the database named 'database_01' was also off. First, I thought I made a mistake, turning off the events at the wrong database, but when turning it on, I saw this took effect on ALL databases on the MySQL-server. (SET GLOBAL event_scheduler = 1). So now, all the databases has the event-scheduler turning on again. It seams to be impossible to disable the event-scheduler for table A, but enable it for table B. The only way to remove the events from the processlist is to remove / disable them all one by one. It's not able to enable / disable them all at once for a single database? How to repeat: Create a database, add 100 scheduled events, copy the database... Now, try to turn off the 100 scheduled events in the copied database, without turning off the scheduled events in the original one, without having to disable all the 100 events one by one. Suggested fix: Would like to see some query / statement available like: 'SET event_scheduler = 1 {FROM | IN} database_01;