Mysql 事件不工作

Mysql Event Not Working

提问by Rana

I have added the following simple test event on my mysql database via phpmyadmin:

我已经通过 phpmyadmin 在我的 mysql 数据库上添加了以下简单的测试事件：

CREATE DEFINER=`root`@`localhost` EVENT `my_event` 
ON SCHEDULE EVERY 1 MINUTE STARTS '2013-05-27 00:00:00' 
ON COMPLETION NOT PRESERVE ENABLE DO 
BEGIN
    UPDATE `test` SET `name`="z";
END

My environment is mac + MAMP Pro. I am expecting to change all rows on my 'test' table with name 'z' within a minute. But not happening so.

我的环境是mac+MAMP Pro。我希望在一分钟内更改名为“z”的“test”表上的所有行。但事实并非如此。

Do I have to something additional to get my events start working?

我是否需要一些额外的东西才能让我的活动开始工作？

Output of "SHOW PROCESSLIST":

“SHOW PROCESSLIST”的输出：

Thanks.

谢谢。

回答by Jacopofar

Events are run by the scheduler, which is not started by default. Using SHOW PROCESSLISTis possible to check whether it is started. If not, run the command

事件由调度程序运行，默认情况下不启动。使用SHOW PROCESSLIST可以检查是否启动。如果没有，运行命令

 SET GLOBAL event_scheduler = ON;

to run it.

运行它。

回答by techdude

For those wondering how to enable it by default at startup, add the following to your config file (my.ini, my.cnf):

对于那些想知道如何在启动时默认启用它的人，请将以下内容添加到您的配置文件（my.ini、my.cnf）中：

#Event scheduler can be set to 1 (On), 0 (Off), or Disabled
event_scheduler=1

Restart of the service is required in this case, so if you want minimal disruption, add this to the config file, and then run the SQL:

在这种情况下需要重新启动服务，因此如果您希望将中断降至最低，请将其添加到配置文件中，然后运行 ​​SQL：

SET GLOBAL event_scheduler = ON;

That way, it will run for the current process, and if the server is restarted it will still work.

这样，它将为当前进程运行，如果服务器重新启动，它仍然可以工作。

Note that this doesn't work if the event_scheduler was set to disabled. In that case the only option is to restart the service.

请注意，如果 event_scheduler 设置为禁用，这将不起作用。在这种情况下，唯一的选择是重新启动服务。

回答by Nabeel Ahmed

Verify if the event_scheduler is On - execute the following command:

验证 event_scheduler 是否打开 - 执行以下命令：

SHOW PROCESSLIST;

It'll output a table/entries, you must look for an entry with Userevent_scheduler, and CommandDaemon:

它将输出一个表/条目，您必须使用Userevent_scheduler和Command查找条目Daemon：

Id           User         Host      db  Command Time    State            Info
22870   event_scheduler localhost   \N   Daemon  23    Waiting for next activation  \N

OR, you can also verify using the following command:

或者，您还可以使用以下命令进行验证：

SELECT @@global.event_scheduler;

The result should be ON, otherwise set it off (will get 0for the command), as stated in the next section.

结果应该是ON，否则将其设置为 off（将获取0命令），如下一节所述。

---

---

If you don't have any such entry (as above), you may start the event scheduler using the following command:

如果您没有任何此类条目（如上所述），您可以使用以下命令启动事件调度程序：

 SET GLOBAL event_scheduler = ON;

Once done, you can verify if it has been executed properly using the SHOW PROCESSLISTcommand, as mentioned above.

完成后，您可以使用SHOW PROCESSLIST上面提到的命令验证它是否已正确执行。

回答by tony gil

If you want your event_schedulerto startup automatically every time mysql server restarts, anywhere under the [mysqld]section of the my.inior my.cnffile that you find in /etc/mysqlyou should place

如果您希望event_scheduler每次 mysql 服务器重新启动时自动启动，则应放置[mysqld]在my.ini或my.cnf文件部分下的任何/etc/mysql位置

[mysqld]

# turning on event_scheduler  
event_scheduler=ON

restart mysql to check if it is running (in command line terminal!)

重新启动 mysql 以检查它是否正在运行（在命令行终端中！）

sudo service mysql restart

then check your processlist

然后检查你的进程列表

SHOW PROCESSLIST

you can check if your events are running by checking the last time they ran

您可以通过检查它们上次运行的时间来检查您的事件是否正在运行

SELECT * FROM INFORMATION_SCHEMA.events

回答by Madacol

Temporal

颞

SET GLOBAL event_scheduler = ON;

Will not work if event_scheduler is explicitly DISABLED, see the method below

如果 event_scheduler 被明确禁用将不起作用，请参阅下面的方法

Permanent (needs restart)

永久（需要重启）

In your config file (In Ubuntu it's /etc/mysql/mysql.cnf):

在您的配置文件中（在 Ubuntu 中是/etc/mysql/mysql.cnf）：

[mysqld]
event_scheduler = ON

Notes:

笔记：

The event_schedulervariable can have this possible states:

该event_scheduler变量可以有这个可能的状态：

• OFF(or 0) (default)
• ON(or 1)
• DISABLED: you cannot use the temporal enabling, you can only change state through the config file and restarting the server

• 关（或0）（默认）
• 开（或1）
• 禁用：您不能使用临时启用，您只能通过配置文件更改状态并重新启动服务器

WARNING:Keywords ON / OFFare preferred over their numerical equivalents. And in fact Mysql Workbenchdoesn't recognize the configuration event_scheduler=1, it shows as OFFin the Options Filesection. Tested in Ubuntu with Mysql Workbench 8.0.17 and Mysql Server 5.7.27

警告：关键字ON / OFF优先于它们的数字等效项。事实上，Mysql Workbench无法识别配置event_scheduler=1，它在Options File部分显示为OFF。 在 Ubuntu 中使用 Mysql Workbench 8.0.17 和 Mysql Server 5.7.27 进行测试

Although ONand OFFhave numeric equivalents, the value displayed for event_scheduler by SELECT or SHOW VARIABLES is always one of OFF, ON, or DISABLED. DISABLEDhas no numeric equivalent. For this reason, ONand OFFare usually preferred over 1and 0when setting this variable.

尽管ON和OFF具有数字等效项，但 SELECT 或 SHOW VARIABLES 为 event_scheduler 显示的值始终是OFF、ON或DISABLED 之一。DISABLED没有数字等效项。因此，在设置此变量时，ON和OFF通常优先于1和0。

Source: https://dev.mysql.com/doc/refman/5.7/en/events-configuration.html

来源：https: //dev.mysql.com/doc/refman/5.7/en/events-configuration.html

回答by Lownc

Remember to add in 'Commit', after 'DO BEGIN' or 'DO'. Works for me after that.

记得在' DO BEGIN'或' DO'之后添加' Commit'。在那之后为我工作。

回答by GoodbyeKitty

I just figured out that on MariaDB, after adding an event (in my case, it was the first one), you have to restart the event-scheduler

我刚刚发现在 MariaDB 上，在添加一个事件（在我的情况下，它是第一个）之后，您必须重新启动事件调度程序

 SET GLOBAL event_scheduler = OFF;

and then

进而

 SET GLOBAL event_scheduler = ON;

to make it actually bring the scheduler into "waiting for activation"-state.

使其实际上使调度程序进入“等待激活”状态。

回答by Daniel James Canil

I would just like to add to this thread. I dumped my database to another server and as a result the definer of my event had no such grant defined for the user. I updated my definer with

我只想添加到这个线程。我将我的数据库转储到另一台服务器，结果我的事件的定义者没有为用户定义这样的授权。我更新了我的定义器

ALTER DEFINER='root'@'localhost' EVENT event.name COMMENT '';

Make sure your definer has the correct PRIVILEGES.

确保您的定义者具有正确的特权。

回答by Dhaval Hingrajia

Try

尝试

SET GLOBAL event_scheduler = ON;
DELIMITER $$
CREATE DEFINER=`root`@`db01` EVENT `PRICEALERT_STATUS` 
ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE) 
DO BEGIN
// Your Query
END $$
DELIMITER ;