Shulou(Shulou.com)05/31 Report--

This article is to share with you what to do about the startup failure caused by the conflict between MYSQL 5.6super_read_only and Event Scheduler. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

About MYSQL 5.6conflict between super_read_only and Event Scheduler causing startup failure

Version percona-server-5.6.22-72.0, this version must have this problem.

This problem occurs online. After we set the library to super_read_only, we start to report an error, and the whole MYSQLD CRASH is dropped.

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

There is no problem with 5.6.25 and other versions of 5.7. The Internet said it may be BUG, but did not give a specific reason, there are

The post says that it has something to do with super_read_only. If you do shut down super_read_only, there will be no more problems.

But in order to find the problem, I want to dig the code as follows:

First of all, to find the source of the error, first find Event Scheduler: Error while loading from disk. The location is as follows:

This code appears in the

Under the Events::init (my_bool opt_noacl_or_bootstrap) method of events.cc

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.");-here

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

Goto end

}

All three conditions can cause this error:

1. Failed to initialize the queue. Function API event_queue- > init_queue

2. Failed to load event data. Function API load_events_from_db

3. Whether the parameter event_scheduler is set to ON and the call to scheduler- > start fails.

And error Event Scheduler: Failed to open table mysql.event is exactly what load_events_from_db (thd) is.

Method, check part of its code

/ *

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.

* /-- there is also an unclear explanation of the source code here.

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);-- the returned value here to determine

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");-here

DBUG_RETURN (TRUE)

}

We can see him by calling db_repository- > open_event_table (thd, TL_WRITE, & table).

Receive an error if the return value is true. Let's take a look at 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)

}

Maybe this function will judge

1. Whether the event table can be lock

2. Whether the event table is damaged

Eventually lock_tables-- > mysql_lock_tables will be called, if mysql_lock_locks returns a

The NULL pointer reported an error as follows:

DEBUG_SYNC (thd, "before_lock_tables_takes_lock")

If (! (thd- > lock= mysql_lock_tables (thd, start, (uint) (ptr-start), flags))-if mysql_lock_tables returns a NULL to thd- > lock, an exception is generated

DBUG_RETURN (TRUE)

DEBUG_SYNC (thd, "after_lock_tables_takes_lock")

Next, mysql_lock_tables calls the lock_tables_check function to table

Check for lock, exception if the lock_tables_check function returns a value greater than 0

Then the calling process is clear.

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

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

Lock_tables_check

Finally, we analyze this version of BUG because the lock_tables_check function checks and returns an exception.

Which led to this mistake.

The function call stack is as follows:

# 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

The trace lock_tables_check function is found as follows

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)

Because of this code:

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) was returned when the condition was met; caused the entire error

And here.

Enforce_ro plays a key role and is the root of the problem, which is believed to have something to do with it.

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}

two hundred and nine

(gdb) p enforce_ro

$13 = true

(gdb) p t-> alias

$15 = 0x1c5b4d0 "event"

(gdb) p thd- > security_ctx- > master_access

$16 = 32768

Let's study it.

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)

Followed by omission

The root cause of this problem, default enforce_ro= true

If opt_super_readonly is turned on

Enforce_ro=! (thd- > security_ctx- > master_access & SUPER_ACL); do not run

So, enforce_ro= true,

If opt_super_readonly is not enabled, then

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

SUPER_ACL is a macro # define SUPER_ACL (1L security_ctx- > master_access is 32768 and

1000 0000 0000 0000

Obviously they bit by bit and get 1 and ture, and then! true

So enforce_ro= false

If flase, then

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 it is not executed, it will not report an error again.

So to solve this problem, or BUG, is to set if opt_super_readonly is not enabled.

Just do not set super_read_only permissions.

In version 5.7.14, I glanced at the lock_tables_check code and saw some changes. 5.7.14 did not encounter this problem

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

Check_readonly (thd, true))

DBUG_RETURN (1)

You can see that enforce_ro is no longer the standard of judgment.

The 5.6.22 version does have this problem, but not every version. If you encounter, you can refer to it.

With 5.6.22Pol 5.7.14 code:

5.6.22

Click (here) to collapse or open

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 (iTun0; is- > 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 (iTun0; is- > 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)

}

The above is what to do if the conflict between MYSQL 5.6super_read_only and Event Scheduler leads to startup failure. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.