Sep 8, 2010

Mysql Server error.

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


THis is commonly occures when you are trying to execute query on a big data. For example when you are trying select/delete a million of record try chunk it by 5%-10% of the record.

Also you may try to higher the the buffer memory, as default it only 8MB which is too short for that kind of large data. To do it, modify the /etc/my.cnf

set-variable = innodb_buffer_pool_size=100M
set-variable = innodb_additional_mem_pool_size=10M
innodb_flush_log_at_trx_commit=1


Replication can often break


When a query cannot process properly on a slave (due to duplicate key or another MySQL error), replication will stop running until you manually resume it.

To avoid replication breaking due to duplicate keys, make sure your /etc/my.cnf configuration file includes these two lines:

slave_exec_mode = IDEMPOTENT
slave-skip-error = 1062

No comments: