MYSQL 5.6 super_read_only和Event Scheduler冲突导致启动失败该怎么办

网友投稿 100 2023-12-26

MYSQL 5.6 super_read_only和Event Scheduler冲突导致启动失败该怎么办

本篇文章给大家分享的是有关MYSQL 5.6 super_read_only和Event Scheduler冲突导致启动失败该怎么办，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

关于MYSQL 5.6 super_read_only和Event Scheduler冲突导致启动失败

版本percona-server-5.6.22-72.0，这个版本肯定有这个问题

这个问题出现在线上我们将库设置为super_read_only后启动报错，整个MYSQLD CRASH掉

2017-04-23 01:15:46 22577 [ERROR] Event Scheduler: Failed to open table mysql.event

2017-04-23 01:15:46 22577 [ERROR] Event Scheduler: Error while loading from disk.

2017-04-23 01:15:46 22577 [Note] Event Scheduler: Purging the queue. 0 events

2017-04-23 01:15:46 22577 [ERROR] Aborting

在5.6.25以及其他5.7的版本上并没有这个问题。网上说可能是BUG但是没有说出具体原因，也有

帖子说和super_read_only 有关，确实关闭super_read_only就不会再出问题，

但是为了找到问题，我想挖一挖代码如下：

首先要找到报错源头，首先找到Event Scheduler: Error while loading from disk.的位置如下：

这段代码出现在

events.cc的Events::init(my_bool opt_noacl_or_bootstrap) 方法下

if (event_queue->init_queue(thd) || load_events_from_db(thd) ||

(opt_event_scheduler == EVENTS_ON && scheduler->start(&err_no)))

{

sql_print_error("Event Scheduler: Error while loading from disk."); --这里

res= TRUE; /* fatal error: request unireg_abort */

goto end;

}

这里3个条件都可能引起这个错误：

1、初始化队列失败，函数接口event_queue->init_queue

2、加载event数据失败，函数接口load_events_from_db

3、参数event_scheduler设置是否为ON，并且scheduler->start调用失败

而错误 Event Scheduler: Failed to open table mysql.event 正是load_events_from_db(thd)这个

方法报出来的，查看其部分代码

NOTE: even if we run in read-only mode, we should be able to lock the

mysql.event table for writing. In order to achieve this, we should call

mysql_lock_tables() under the super user.

Same goes for transaction access mode.

Temporarily reset it to read-write.

*/--这里源码也有不算清楚的解释

saved_master_access= thd->security_ctx->master_access;

thd->security_ctx->master_access |= SUPER_ACL;

bool save_tx_read_only= thd->tx_read_only;

thd->tx_read_only= false;

ret= db_repository->open_event_table(thd, TL_WRITE, &table); --这里的返回值进行判断

thd->tx_read_only= save_tx_read_only;

thd->security_ctx->master_access= saved_master_access;

if (ret)

{

sql_print_error("Event Scheduler: Failed to open table mysql.event"); ---这里

DBUG_RETURN(TRUE);

}

我们可以看到他的是通过调用db_repository->open_event_table(thd, TL_WRITE, &table)来

接收其返回值如果为true则报错。接下来看open_event_table

bool

Event_db_repository::open_event_table(THD *thd, enum thr_lock_type lock_type,

TABLE **table)

{

TABLE_LIST tables;

DBUG_ENTER("Event_db_repository::open_event_table");

tables.init_one_table("mysql", 5, "event", 5, "event", lock_type);

if (open_and_lock_tables(thd, &tables, FALSE, MYSQL_LOCK_IGNORE_TIMEOUT))

DBUG_RETURN(TRUE);

*table= tables.table;

tables.table->use_all_columns();

if (table_intact.check(*table, &event_table_def))

{

close_thread_tables(thd);

my_error(ER_EVENT_OPEN_TABLE_FAILED, MYF(0));

DBUG_RETURN(TRUE);

}

DBUG_RETURN(FALSE);

}

大概这个函数会判断

1、event表是否可以lock

2、event表是否损坏

最终会调用lock_tables-->mysql_lock_tables，如果mysql_lock_locks返回一个

NULL指针则报错如下：

DEBUG_SYNC(thd, "before_lock_tables_takes_lock");

if (! (thd->lock= mysql_lock_tables(thd, start, (uint) (ptr - start), flags))) --如果mysql_lock_tables返回一个NULL给thd->lock，产生异常

DBUG_RETURN(TRUE);

DEBUG_SYNC(thd, "after_lock_tables_takes_lock");

接下来mysql_lock_tables 又调用lock_tables_check函数进行table

lock的检查，如果lock_tables_check函数返回一个大于0的值则异常

那么调用流程清晰了

Events::init-->Events::load_events_from_db-->open_event_table

-->open_and_lock_tables -->lock_tables-->mysql_lock_tables-->

lock_tables_check

最终我们分析掉这个版本的BUG由于lock_tables_check函数检查返回异常

而导致这个错误。

函数调用栈如下:

#0 mysql_lock_tables (thd=0x1c0b5e0, tables=0x1b62ca0, count=1, flags=2048) at /home/percona-server-5.6.22-72.0/sql/lock.cc:296

#1 0x00000000007910c9 in lock_tables (thd=0x1c0b5e0, tables=0x7fffffffdae0, count=1, flags=2048) at /home/percona-server-5.6.22-72.0/sql/sql_base.cc:6125

#2 0x000000000079086f in open_and_lock_tables (thd=0x1c0b5e0, tables=0x7fffffffdae0, derived=false, flags=2048, prelocking_strategy=0x7fffffffda90)

at /home/percona-server-5.6.22-72.0/sql/sql_base.cc:5889

#3 0x0000000000781ed2 in open_and_lock_tables (thd=0x1c0b5e0, tables=0x7fffffffdae0, derived=false, flags=2048)

at /home/percona-server-5.6.22-72.0/sql/sql_base.h:477

#4 0x0000000000a26d20 in Event_db_repository::check_system_tables (thd=0x1c0b5e0) at /home/percona-server-5.6.22-72.0/sql/event_db_repository.cc:1202

#5 0x00000000008ff5fb in Events::init (opt_noacl_or_bootstrap=0 \000) at /home/percona-server-5.6.22-72.0/sql/events.cc:858

#6 0x000000000063e21d in mysqld_main (argc=83, argv=0x18f4c58) at /home/percona-server-5.6.22-72.0/sql/mysqld.cc:5784

#7 0x0000000000632634 in main (argc=11, argv=0x7fffffffe398) at /home/percona-server-5.6.22-72.0/sql/main.cc:25

跟踪lock_tables_check函数发现如下

198 if (!(flags & MYSQL_LOCK_IGNORE_GLOBAL_READ_ONLY) && !t->s->tmp_table)

(gdb) n

200 if (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE &&

(gdb) n

204 opt_super_readonly ? "--read-only (super)" : "--read-only");

(gdb) n

205 DBUG_RETURN(1);

上面由于这段代码：

200 if (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE &&

201 enforce_ro && opt_readonly && !thd->slave_thread)

202 {

203 my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0),

204 opt_super_readonly ? "--read-only (super)" : "--read-only");

205 DBUG_RETURN(1);

206 }

if (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE && enforce_ro && opt_readonly && !thd->slave_thread)

条件满足返回了 DBUG_RETURN(1); 造成整个报错

而这里

enforce_ro 起到了关键作用，也是问题的根源，相信和这里有关

if (!opt_super_readonly)

enforce_ro= !(thd->security_ctx->master_access & SUPER_ACL);

(gdb) p t->alias

$12 = 0x1c5b4d0 "event"

(gdb) list

200 if (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE &&

201 enforce_ro && opt_readonly && !thd->slave_thread)

202 {

203 my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0),

204 opt_super_readonly ? "--read-only (super)" : "--read-only");

205 DBUG_RETURN(1);

206 }

207 }

208 }

209

(gdb) p enforce_ro

$13 = true

(gdb) p t->alias

$15 = 0x1c5b4d0 "event"

(gdb) p thd->security_ctx->master_access

$16 = 32768

我们研究下

lock_tables_check(THD *thd, TABLE **tables, uint count, uint flags)

{

uint system_count= 0, i= 0;

bool enforce_ro= true;

Identifies if the executed sql command can updated either a log

or rpl info table.

bool log_table_write_query= false;

DBUG_ENTER("lock_tables_check");

if (!opt_super_readonly)

enforce_ro= !(thd->security_ctx->master_access & SUPER_ACL);

后面省略

这句问题的根源，默认enforce_ro= true;

如果opt_super_readonly开启则

enforce_ro= !(thd->security_ctx->master_access & SUPER_ACL);不运行

那么enforce_ro= true

如果opt_super_readonly不开启则

enforce_ro= !(thd->security_ctx->master_access & SUPER_ACL);运行

SUPER_ACL是一个宏#define SUPER_ACL (1L << 15)

当做4字节INT的话,及

1000 0000 0000 0000

而thd->security_ctx->master_access是32768及

1000 0000 0000 0000

显然他们按位与得到是1 及ture，然后!true

所以enforce_ro= false;

如果为flase则

200 if (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE &&

201 enforce_ro && opt_readonly && !thd->slave_thread)

202 {

203 my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0),

204 opt_super_readonly ? "--read-only (super)" : "--read-only");

205 DBUG_RETURN(1);

206 }

不会执行，则不会再次报错

所以解决这个问题或者说BUG，就是设置如果opt_super_readonly不开启，

就是不要设置super_read_only权限。

在5.7.14版本中，我粗略查看lock_tables_check代码，有改动。5.7.14没遇到这个问题

if (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE &&

check_readonly(thd, true))

DBUG_RETURN(1);

可以看到 enforce_ro已经不再作为判断的标准

而5.6.22这个版本确实有这个问题，但是这个问题不是每个版本都有。如果遇到可以参考。

附带5.6.22，5.7.14代码：

5.6.22

点击(此处)折叠或打开

static int

lock_tables_check(THD *thd, TABLE **tables, uint count, uint flags)

{

uint system_count= 0, i= 0;

bool enforce_ro= true;

Identifies if the executed sql command can updated either a log

or rpl info table.

bool log_table_write_query= false;

DBUG_ENTER("lock_tables_check");

if (!opt_super_readonly)

enforce_ro= !(thd->security_ctx->master_access & SUPER_ACL);

log_table_write_query=

is_log_table_write_query(thd->lex->sql_command);

for (i=0 ; i

{

TABLE *t= tables[i];

/* Protect against fake partially initialized TABLE_SHARE */

DBUG_ASSERT(t->s->table_category != TABLE_UNKNOWN_CATEGORY);

Table I/O to performance schema tables is performed

only internally by the server implementation.

When a user is requesting a lock, the following

constraints are enforced:

if (t->s->table_category == TABLE_CATEGORY_LOG &&

(flags & MYSQL_LOCK_LOG_TABLE) == 0 &&

!log_table_write_query)

{

A user should not be able to prevent writes,

or hold any type of lock in a session,

since this would be a DOS attack.

if (t->reginfo.lock_type >= TL_READ_NO_INSERT ||

thd->lex->sql_command == SQLCOM_LOCK_TABLES)

{

my_error(ER_CANT_LOCK_LOG_TABLE, MYF(0));

DBUG_RETURN(1);

}

if (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE)

{

if (t->s->table_category == TABLE_CATEGORY_SYSTEM)

system_count++;

if (t->db_stat & HA_READ_ONLY)

{

my_error(ER_OPEN_AS_READONLY, MYF(0), t->alias);

DBUG_RETURN(1);

}

If we are going to lock a non-temporary table we must own metadata

lock of appropriate type on it (I.e. for table to be locked for

write we must own metadata lock of MDL_SHARED_WRITE or stronger

type. For table to be locked for read we must own metadata lock

of MDL_SHARED_READ or stronger type).

The only exception are HANDLER statements which are allowed to

lock table for read while having only MDL_SHARED lock on it.

DBUG_ASSERT(t->s->tmp_table ||

thd->mdl_context.is_lock_owner(MDL_key::TABLE,

t->s->db.str, t->s->table_name.str,

t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE ?

MDL_SHARED_WRITE : MDL_SHARED_READ) ||

(t->open_by_handler &&

thd->mdl_context.is_lock_owner(MDL_key::TABLE,

t->s->db.str, t->s->table_name.str,

MDL_SHARED)));

Prevent modifications to base tables if READ_ONLY is activated.

In any case, read only does not apply to temporary tables.

if (!(flags & MYSQL_LOCK_IGNORE_GLOBAL_READ_ONLY) && !t->s->tmp_table)

{

if (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE &&

enforce_ro && opt_readonly && !thd->slave_thread)

{

my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0),

opt_super_readonly ? "--read-only (super)" : "--read-only");

DBUG_RETURN(1);

}

Locking of system tables is restricted:

locking a mix of system and non-system tables in the same lock

is prohibited, to prevent contention.

if ((system_count > 0) && (system_count < count))

{

my_error(ER_WRONG_LOCK_OF_SYSTEM_TABLE, MYF(0));

DBUG_RETURN(1);

}

DBUG_RETURN(0);

}

5.7.14

点击(此处)折叠或打开

static int

lock_tables_check(THD *thd, TABLE **tables, size_t count, uint flags)

{

uint system_count= 0, i= 0;

Identifies if the executed sql command can updated either a log

or rpl info table.

bool log_table_write_query= false;

DBUG_ENTER("lock_tables_check");

log_table_write_query=

is_log_table_write_query(thd->lex->sql_command);

for (i=0 ; i

{

TABLE *t= tables[i];

/* Protect against fake partially initialized TABLE_SHARE */

DBUG_ASSERT(t->s->table_category != TABLE_UNKNOWN_CATEGORY);

Table I/O to performance schema tables is performed

only internally by the server implementation.

When a user is requesting a lock, the following

constraints are enforced:

if (t->s->table_category == TABLE_CATEGORY_LOG &&

(flags & MYSQL_LOCK_LOG_TABLE) == 0 &&

!log_table_write_query)

{

A user should not be able to prevent writes,

or hold any type of lock in a session,

since this would be a DOS attack.

if (t->reginfo.lock_type >= TL_READ_NO_INSERT ||

thd->lex->sql_command == SQLCOM_LOCK_TABLES)

{

my_error(ER_CANT_LOCK_LOG_TABLE, MYF(0));

DBUG_RETURN(1);

}

if (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE)

{

if (t->s->table_category == TABLE_CATEGORY_SYSTEM)

system_count++;

if (t->db_stat & HA_READ_ONLY)

{

my_error(ER_OPEN_AS_READONLY, MYF(0), t->alias);

DBUG_RETURN(1);

}

If we are going to lock a non-temporary table we must own metadata

lock of appropriate type on it (I.e. for table to be locked for

write we must own metadata lock of MDL_SHARED_WRITE or stronger

type. For table to be locked for read we must own metadata lock

of MDL_SHARED_READ or stronger type).

DBUG_ASSERT(t->s->tmp_table ||

thd->mdl_context.owns_equal_or_stronger_lock(MDL_key::TABLE,

t->s->db.str, t->s->table_name.str,

t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE ?

MDL_SHARED_WRITE : MDL_SHARED_READ));

Prevent modifications to base tables if READ_ONLY is activated.

In any case, read only does not apply to temporary tables.

if (!(flags & MYSQL_LOCK_IGNORE_GLOBAL_READ_ONLY) && !t->s->tmp_table)

{

if (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE &&

check_readonly(thd, true))

DBUG_RETURN(1);

}

Locking of system tables is restricted:

locking a mix of system and non-system tables in the same lock

is prohibited, to prevent contention.

if ((system_count > 0) && (system_count < count))

{

my_error(ER_WRONG_LOCK_OF_SYSTEM_TABLE, MYF(0));

DBUG_RETURN(1);

}

DBUG_RETURN(0);

}

以上就是MYSQL 5.6 super_read_only和Event Scheduler冲突导致启动失败该怎么办，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。

标签：流程代码字节版本数据

暂时没有评论，来抢沙发吧~

MYSQL 5.6 super_read_only和Event Scheduler冲突导致启动失败该怎么办

MYSQL 5.6 super_read_only和Event Scheduler冲突导致启动失败该怎么办

放榜查成绩新骗局，360防骗高手小程序一辩真伪

支持BAT三大平台，小程序开发工具应用上线

支付宝：小程序“续命”开放平台布局，不和微信比做生态之难

最近发表

更多内容

小程序SDK

Finclip技术文档

小程序开发

小程序容器

小程序框架

Finclip小程序平台

Finclip用户投稿

车联网

推荐文章

小程序SDK是什么意思？小程序sdk和插件有什么区别？

小程序支付功能怎么实现？

企业app开发流程是什么？

app运营模式有哪些？

小程序多端引流怎么做？

小程序生态分析的机会和威胁

Flutter入门这一篇效率文章就够了

原生与跨平台解决方案分析,跨平台软件开发技术方案

热更新技术：让软件更新变得更加轻松快速

解决方案

银行解决方案

证券解决方案

互联网解决方案

政企OA解决方案

科技解决方案

loT解决方案

信任解决方案

热评文章

6个用于弹出USB可移动设备的免费工具

商店App排名，十大App各具特色

全向智能移动平台——改变未来交通方式的革命性创新

uni-app做微信小程序的分包处理方法

工业互联网服务平台：全面连接未来工业的智慧引擎

Java解压和压缩带密码的zip文件过程详解