magento mysql主从_Magento Mysql 死锁-1205 Lock wait timeout exceeded

追紧刷magento的时候报 这个错误

[root@ajiang ~]# php -f /home/wwwroot/weicotshop/shell/indexer.php reindexall

Product Attributes index process unknown error:

exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction' in /home/wwwroot/weicotshop/lib/Zend/Db/Statement/Pdo.php:248

Stack trace:

#0 /home/wwwroot/weicotshop/lib/Zend/Db/Statement/Pdo.php(248): PDOStatement->execute(Array)

#1 /home/wwwroot/weicotshop/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)

#2 /home/wwwroot/weicotshop/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)

#3 /home/wwwroot/weicotshop/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)

#4 /home/wwwroot/weicotshop/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('DELETE FROM `ca...', Array)

#5 /home/wwwroot/weicotshop/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('DELETE FROM `ca...', Array)

#6 /home/wwwroot/weicotshop/lib/Zend/Db/Adapter/Abstract.php(661): Varien_Db_Adapter_Pdo_Mysql->query('DELETE FROM `ca...')

#7 /home/wwwroot/weicotshop/app/code/core/Mage/Index/Model/Resource/Abstract.php(235): Zend_Db_Adapter_Abstract->delete('cataloginventor...')

#8 /home/wwwroot/weicotshop/app/code/core/Mage/CatalogInventory/Model/Resource/Indexer/Stock.php(245): Mage_Index_Model_Resource_Abstract->clearTemporaryIndexTable()

#9 /home/wwwroot/weicotshop/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_CatalogInventory_Model_Resource_Indexer_Stock->reindexAll()

#10 /home/wwwroot/weicotshop/app/code/core/Mage/Index/Model/Process.php(210): Mage_Index_Model_Indexer_Abstract->reindexAll()

#11 /home/wwwroot/weicotshop/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()

#12 /home/wwwroot/weicotshop/app/code/core/Mage/Index/Model/Process.php(252): Mage_Index_Model_Process->reindexEverything()

#13 /home/wwwroot/weicotshop/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything()

#14 /home/wwwroot/weicotshop/shell/indexer.php(212): Mage_Shell_Compiler->weicotshopn()

#15 {main}

用 show full processlist \G 查看状态

mysql> show full processlist \G

*************************** 1. row ***************************

Id: 13036

User: jiang

Host: localhost:xxoo

db: m_w

Command: Query

Time: 524198

State: Sending data

Info: SELECT `main_table`.`entity_id`, `url_rewrite`.`request_path` FROM `catalog_product_entity` AS `main_table`

INNER JOIN `catalog_product_website` AS `w` ON main_table.entity_id = w.product_id

LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.product_id = main_table.entity_id AND url_rewrite.is_system = 1 AND url_rewrite.category_id IS NULL AND url_rewrite.store_id = 1 AND url_rewrite.id_path LIKE 'product/%'

INNER JOIN `catalog_product_entity_int` AS `t1_visibility` ON main_table.entity_id=t1_visibility.entity_id AND t1_visibility.store_id=0

LEFT JOIN `catalog_product_entity_int` AS `t2_visibility` ON t1_visibility.entity_id = t2_visibility.entity_id AND t1_visibility.attribute_id = t2_visibility.attribute_id AND t2_visibility.store_id = 1

INNER JOIN `catalog_product_entity_int` AS `t1_status` ON main_table.entity_id=t1_status.entity_id AND t1_status.store_id=0

LEFT JOIN `catalog_product_entity_int` AS `t2_status` ON t1_status.entity_id = t2_status.entity_id AND t1_status.attribute_id = t2_status.attribute_id AND t2_status.store_id = 1 WHERE (w.website_id='1') AND (t1_visibility.attribute_id='102') AND ((IF(t2_visibility.value_id > 0, t2_visibility.value, t1_visibility.value)) IN(3, 2, 4)) AND (t1_status.attribute_id='96') AND ((IF(t2_status.value_id > 0, t2_status.value, t1_status.value)) IN(1))

*************************** 2. row ***************************

Id: 226052

User: jiang

Host: localhost:xxoo

db: m_w

Command: Query

Time: 5767

State: Sending data

Info: SELECT `main_table`.`entity_id`, `url_rewrite`.`request_path` FROM `catalog_product_entity` AS `main_table`

INNER JOIN `catalog_product_website` AS `w` ON main_table.entity_id = w.product_id

LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.product_id = main_table.entity_id AND url_rewrite.is_system = 1 AND url_rewrite.category_id IS NULL AND url_rewrite.store_id = 1 AND url_rewrite.id_path LIKE 'product/%'

INNER JOIN `catalog_product_entity_int` AS `t1_visibility` ON main_table.entity_id=t1_visibility.entity_id AND t1_visibility.store_id=0

LEFT JOIN `catalog_product_entity_int` AS `t2_visibility` ON t1_visibility.entity_id = t2_visibility.entity_id AND t1_visibility.attribute_id = t2_visibility.attribute_id AND t2_visibility.store_id = 1

INNER JOIN `catalog_product_entity_int` AS `t1_status` ON main_table.entity_id=t1_status.entity_id AND t1_status.store_id=0

LEFT JOIN `catalog_product_entity_int` AS `t2_status` ON t1_status.entity_id = t2_status.entity_id AND t1_status.attribute_id = t2_status.attribute_id AND t2_status.store_id = 1 WHERE (w.website_id='1') AND (t1_visibility.attribute_id='102') AND ((IF(t2_visibility.value_id > 0, t2_visibility.value, t1_visibility.value)) IN(3, 2, 4)) AND (t1_status.attribute_id='96') AND ((IF(t2_status.value_id > 0, t2_status.value, t1_status.value)) IN(1))

*************************** 3. row ***************************

Id: 227290

User: jiang

Host: localhost:xxoo

db: m_w

Command: Query

Time: 1

State: Sending data

Info: SELECT `core_url_rewrite`.* FROM `core_url_rewrite` LIMIT 320 OFFSET 770880

*************************** 4. row ***************************

Id: 227834

User: jiang

Host: localhost:xxoo

db: NULL

Command: Query

Time: 0

State: NULL

Info: show full processlist

*************************** 5. row ***************************

Id: 227838

User: jiang

Host: localhost:xxoo

db: m_w

Command: Query

Time: 30

State: updating

Info: DELETE FROM `catalogsearch_result` WHERE query_id = 1245

*************************** 6. row ***************************

Id: 227839

User: jiang

Host: localhost:xxoo

db: m_w

Command: Query

Time: 22

State: Updating

Info: UPDATE `catalogsearch_query` SET `query_text` = 'bulls', `num_results` = '395', `popularity` = '28122', `redirect` = NULL, `synonym_for` = NULL, `store_id` = '1', `display_in_terms` = '1', `is_active` = '1', `is_processed` = '1', `updated_at` = '2015-11-09 09:46:31' WHERE (query_id='466')

6 rows in set (0.00 sec)

mysql>show processlist;

mysql>show engine innodb status ,

//查看

mysql>kill thread_id;

//kill 掉所有锁表的进程

mysql>SELECT * FROM information_schema.INNODB_TRX\G;

//再查看事物表 注意trx_mysql_thread_id 的值

相关脚本

#!/bin/bash

mysql -u root -e "show processlist" | grep -i "Locked" >> locked_log.txt

for line in `cat locked_log.txt | awk '{print $1}'`

do

echo "kill $line;" >> kill_thread_id.sql

done

#现在kill_thread_id.sql的内容像这个样子

kill 66402982;

kill 66402983;

kill 66402986;

kill 66402991;

.....

mysql shell 中执行

mysql>source kill_thread_id.sql

#一行版

for id in `mysqladmin processlist | grep -i locked | awk '{print $1}'`

do

mysqladmin kill ${id}

done

未完待续