Shulou(Shulou.com)06/01 Report--

[problem description]: in the master-slave environment of Mysql5.6, the fields added to the master library can never be added, and no value has been returned.

[investigation and analysis]:

1. There is no problem with execution in the test environment, and it will be returned soon. Because the amount of data is not large, it is only about 10w (online is also about 10w)

2. When executing the alter statement, a waiting TML lock is found at another terminal through show processlist.

3. Check the thread ID through the following sql

SELECT distinct b.trx_id blocking_trx_id,b.trx_mysql_thread_id Source Lock thread_id,SUBSTRING (p. HOST, 1, INSTR (p. HOST,':')-1) blocking_host,SUBSTRING (p. HOST, INSTR (p. HOST,':) + 1) blocking_port,IF (p.COMMAND = 'Sleep', p.TIME, 0) idel_in_trx,b.trx_query blocking_query,r.trx_id waiting_trx_id R.trx_mysql_thread_id waiting_thread,TIMESTAMPDIFF (SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) wait_time,r.trx_query waiting_query L.lock_table waiting_table_lockFROM information_schema.INNODB_LOCKS lLEFT JOIN information_schema.INNODB_LOCK_WAITS wON w.requested_lock_id = l.lock_idLEFT JOIN information_schema.INNODB_TRX bON b.trx_id = w.blocking_trx_idLEFT JOIN information_schema.INNODB_TRX rON r.trx_id = w.requesting_trx_idLEFT JOIN information_schema. PROCESSLIST pON p.ID = b.trx_mysql_thread_idORDER BY wait_time DESC

If there is no return value, use the following sql to check:

Select i.trx_mysql_thread_idfrom information_schema.innodb_trx I, (select id, time from information_schema.processlist where time = (select max (time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring (info, 1) 5) in ('alter',' optim', 'repai',' lock', 'drop', 'creat') p where timestampdiff (second, i.trx_started, now ()) > p.time and i.trx_mysql_thread_id not in (connection_id (), p.id)

Continue to check the relevant session based on the returned results:

Select * from information_schema.processlist where id=104018

The session can be handled as appropriate.

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.