MySQL Event Scheduler是MySQL提供的一个可以周期性地执行SQL语句或调用存储过程的功能。当使用Event Scheduler时,有可能会遇到“Failed to open table mysql.event”的错误,本文将详细讲解如何解决这个错误。
问题原因
MySQL Event Scheduler需要在mysql.event表中记录排定的事件的信息。当该错误出现时,通常是因为该表丢失或被删除或存在权限不足的问题等情况引起的。
解决方法
方法一:重建mysql.event表
步骤一:检查是否缺失mysql.event表
进入MySQL命令行,执行以下命令,检查是否缺失mysql.event表:
SHOW TABLES LIKE 'mysql.event';
如果查询结果为空,则表示缺失mysql.event表。
步骤二:重建mysql.event表
执行以下命令,重建mysql.event表:
USE mysql;
DROP TABLE IF EXISTS event;
CREATE TABLE event (
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
event_name VARCHAR(64) NOT NULL,
schema_name VARCHAR(64) DEFAULT NULL,
definer VARCHAR(77) NOT NULL,
time_zone VARCHAR(64) NOT NULL DEFAULT 'UTC',
event_status VARCHAR(18) NOT NULL,
on_completion VARCHAR(12) NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_altered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
last_executed TIMESTAMP DEFAULT NULL,
event_comment VARCHAR(64) NOT NULL,
originator INT UNSIGNED NOT NULL,
character_set_client VARCHAR(32) DEFAULT NULL,
collation_connection VARCHAR(32) DEFAULT NULL,
`database` VARCHAR(64) NOT NULL,
`sql_mode` SET('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION',
'NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS',
'MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES',
'STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO',
'TRADITIONAL','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'),
DEFAULT '',
`repeat_interval` VARCHAR(256) NOT NULL DEFAULT '',
`starts` DATETIME DEFAULT NULL,
`ends` DATETIME DEFAULT NULL,
`status` ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
`on_completion` ENUM('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_altered` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_executed` DATETIME DEFAULT NULL,
`event_comment` VARCHAR(64) NOT NULL,
`originator` INT(10) UNSIGNED NOT NULL,
`character_set_client` VARCHAR(32) DEFAULT NULL,
`collation_connection` VARCHAR(32) DEFAULT NULL,
`database` VARCHAR(64) NOT NULL,
PRIMARY KEY (`event_id`),
UNIQUE KEY `Name` (`event_name`,`schema_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Event Scheduler';
步骤三:重启MySQL服务
执行以下命令重启MySQL服务:
systemctl restart mysql
方法二:修改用户权限
使用类似以下的命令,修改MySQL用户的权限:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
其中,'root'是MySQL用户名,'%'表示任意主机都可以连接,'password'是该用户对应的密码。
示例说明
示例1:重建mysql.event表
执行以下命令,重建mysql.event表:
USE mysql;
DROP TABLE IF EXISTS event;
CREATE TABLE event (
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
event_name VARCHAR(64) NOT NULL,
schema_name VARCHAR(64) DEFAULT NULL,
definer VARCHAR(77) NOT NULL,
time_zone VARCHAR(64) NOT NULL DEFAULT 'UTC',
event_status VARCHAR(18) NOT NULL,
on_completion VARCHAR(12) NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_altered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
last_executed TIMESTAMP DEFAULT NULL,
event_comment VARCHAR(64) NOT NULL,
originator INT UNSIGNED NOT NULL,
character_set_client VARCHAR(32) DEFAULT NULL,
collation_connection VARCHAR(32) DEFAULT NULL,
`database` VARCHAR(64) NOT NULL,
`sql_mode` SET('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION',
'NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS',
'MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES',
'STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO',
'TRADITIONAL','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'),
DEFAULT '',
`repeat_interval` VARCHAR(256) NOT NULL DEFAULT '',
`starts` DATETIME DEFAULT NULL,
`ends` DATETIME DEFAULT NULL,
`status` ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
`on_completion` ENUM('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_altered` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_executed` DATETIME DEFAULT NULL,
`event_comment` VARCHAR(64) NOT NULL,
`originator` INT(10) UNSIGNED NOT NULL,
`character_set_client` VARCHAR(32) DEFAULT NULL,
`collation_connection` VARCHAR(32) DEFAULT NULL,
`database` VARCHAR(64) NOT NULL,
PRIMARY KEY (`event_id`),
UNIQUE KEY `Name` (`event_name`,`schema_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Event Scheduler';
示例2:修改用户权限
执行以下命令,修改MySQL用户的权限:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
其中,'root'是MySQL用户名,'%'表示任意主机都可以连接,'password'是该用户对应的密码。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:mysql Event Scheduler: Failed to open table mysql.event - Python技术站