Mysql error & quot; ERROR1206 (HY000): Thetotalnumberofl_MySQL

Mysql reports the following error: amp; quot; ERROR1206 (HY000): Thetotalnumberoflocksexceedsthelocktablesizeamp; quot; bitsCN.com

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

1. Background

InnoDB is the default storage engine of the new MySQL version (v5.5 and later). The default engine of the previous version is MyISAM. Therefore, the mysql configuration file is earlier than 5.5. my. in cnf, The InnoDB configuration is commented out by default. In actual use, it is found that many people just copy the mysql configuration file to the required path, and then start mysqld, while the engine = innodb is specified during table creation. Normally, this engine can be used even if innodb engine parameters are not explicitly configured (because MySQL uses the default innodb engine parameters to manage corresponding tables, everyone is happy, because everything is normal.

However, as the data volume in the table increases (for example, the number of millions of records in a single table), the problem arises: when you execute some fuzzy query SQL statements, an error is reported because the default engine parameter is too small. a typical error type is as follows:

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

For example, execute an SQL command like this in a single table with more than 200 million records: delete from table_xxx where col_1 like '% bytes.

Check the information (for example, here). This type of error is caused by the improper default configuration parameters of InnoDB. Obviously, the solution to this exception is to modify the configuration and restart mysqld.

2. modify the default InnoDB configuration in. my. cnf.

The typical configuration of InnoDB in the configuration file is as follows:

[Plain]

# Uncomment the following if you are using InnoDB tables

# Innodb_data_home_dir =/home/root/tool/mysql-5.0.80/var/

# Innodb_data_file_path = ibdata1: 10 M: autoextend

# Innodb_log_group_home_dir =/home/root/tool/mysql-5.0.80/var/

# Innodb_log_arch_dir =/home/root/tool/mysql-5.0.80/var/

# You can set .. _ buffer_pool_size up to 50-80%

# Of RAM but beware of setting memory usage too high

# Innodb_buffer_pool_size = 16 M

# Innodb_additional_mem_pool_size = 2 M

# Set .. _ log_file_size to 25% of buffer pool size

# Innodb_log_file_size = 5 M

# Innodb_log_buffer_size = 8 M

# Innodb_flush_log_at_trx_commit = 1

# Innodb_lock_wait_timeout = 50

It can be seen that all InnoDB-Related configurations are commented out by default, and the new values are enabled and specified as follows (note: the specific configuration values should be determined based on the physical configuration of the deployed machine ):

[Plain]

Innodb_buffer_pool_size = 512 M

Innodb_additional_mem_pool_size = 256 M

Innodb_log_file_size = 128 M # note that this is different from the default value!

Innodb_log_buffer_size = 8 M

Innodb_flush_log_at_trx_commit = 0 # Avoid frequent flush

Innodb_lock_wait_timeout = 50

After the modification, run "./bin/mysqld_safe &" in the mysql installation path to restart the mysql server. Run ps aux | grep "mysqld" on the shell terminal to check that the process is started.

It seems that this is a success,... Slow!

After logging on to mysql through the command line, the process of using the InnoDB data table will be sadly reminded that an error will be reported when executing the SQL command:

Error 'unknown table engine 'innodb''' on query.

Mysql command line input show engines/G found that the list of Engines does not contain InnoDB.

What's going on? The mysql server process is normal. why is an error in the InnoDB engine?

View the mysql server error log (./var/xxx. err) in the mysql installation path. the output is as follows:

[Plain]

130701 16:15:20 mysqld started

InnoDB: Error: log file/home/root/tools/mysql/var/ib_logfile0 is of different size 0 5242880 bytes

InnoDB: than specified in the. cnf file 0 134217728 bytes!

130701 16:15:21 [Note]/home/root/tools/mysql/libexec/mysqld: ready for connections.

Version: '5. 0.80-log' socket: '/home/root/tools/mysql/var/mysql. sock' port: 3306 Source distribution

130701 16:15:37 [ERROR]/home/root/tools/mysql/libexec/mysqld: Incorrect information in file: './data/TV _key_predeal.frm'

130701 16:15:37 [ERROR]/home/root/tools/mysql/libexec/mysqld: Incorrect information in file: './data/TV _key_predeal.frm'

# Several lines are omitted here, which are Error logs for loading the frm file of the data table.

The log shows that an error occurs when the mysql server instance is restarted. if the log file is not correct, the InnoDB engine cannot be loaded.

How to solve it?

3. Final Solution

According to the analysis above, we now encounter two errors:

1) the mysql command line throws: Error 'unknown table engine 'innodb' on query.

2) mysql error log output: InnoDB: Error: log file/home/root/tools/mysql/var/ib_logfile0 is of different size 0 5242880 bytes

From the perspective of causal relationship, the latter is the root cause of an error. Therefore, you only need to solve this error.

According to the solution provided in this post on stackoverflow, perform the following operations:

1) delete ib_logfile0 and ib_logfile1 in the mysql data folder (it is safer to back up their music videos to other paths)

2) restart mysql server

At this time, there is no ERROR in viewing mysql startup logs. at the same time, the "Support" column corresponding to innodb is displayed as "YES" in mysql command line show engines, indicating that mysql server has successfully loaded the engine, finally, no error is reported when you execute the SQL query command.

So far, the problem is completely solved.

BitsCN.com