mysql從庫出錯

標籤：mysql從庫報錯

今天收到郵件，mysql3308,同步出錯，日誌如下：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/58/C7/wKioL1S8l_KypZBfAAFoPmRUszU909.jpg" title="無標題.png" alt="wKioL1S8l_KypZBfAAFoPmRUszU909.jpg" />

修改設定檔  my.cnf ,添加：

innodb_buffer_pool_size = 16M

重啟mysql,解決，

---------

參考：

 Mysql解決The total number of locks exceeds the lock table size錯誤 

在操作mysql資料庫表時出現以下錯誤。

650) this.width=650;" src="http://blog.chinaunix.net/attachment/201207/31/25266990_1343698912z4O9.jpg" border="0" height="246" width="530" style="border:0px;" alt="25266990_1343698912z4O9.jpg" />

網上google搜尋相關問題，發現一位外國牛人這麼解釋：

1. If you‘re running an operation on a large number of rows within a table that uses the InnoDB storage engine, you might see this error:

2. 
   

3. ERROR 1206 (HY000): The total number of locks exceeds the lock table size

4. 
   

5. MySQL is trying to tell you that it doesn‘t have enough room to store all of the row locks that it would need to execute your query. The only way to fix it for sure is to adjust innodb_buffer_pool_size and restart MySQL. By default, this is set to only 8MB, which is too small for anyone who is using InnoDB to do anything.

6. 
   

7. If you need a temporary workaround, reduce the amount of rows you‘re manipulating in one query. For example, if you need to delete a million rows from a table, try to delete the records in chunks of 50,000 or 100,000 rows. If you‘re inserting many rows, try to insert portions of the data at a single time.

原來是InnoDB表執行大批量資料的更新，插入，刪除操作時會出現這個問題，需要調整InnoDB全域的innodb_buffer_pool_size的值來解決這個問題，並且重啟mysql服務。

查看當前資料庫儲存引擎，在建立時使用 ENGINE=InnoDB類型。

預設的innodb_buffer_pool_size=8M

650) this.width=650;" src="http://blog.chinaunix.net/attachment/201207/31/25266990_1343698926Z3h3.png" border="0" height="292" width="389" style="border:0px;" alt="25266990_1343698926Z3h3.png" />

修改 innodb_buffer_pool_size的值：

點擊(此處)摺疊或開啟

1. innodb_buffer_pool_size=64M

再一次重啟mysql伺服器，執行表操作，成功執行完畢。

本文出自 “石頭記” 部落格，請務必保留此出處http://dodowolf.blog.51cto.com/793581/1605647

mysql從庫出錯