Mysql ERROR & quot; ERROR 1206 (HY000): The total number of loc

Mysql ERROR "ERROR 1206 (HY000): The total number of locks exceeds the lock table size" solution 1. problem 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 similar to this in a single table with 200 million + records: delete from table_xxx where col_1 like '% http://www.youku.com/% ', While there are many records that meet the fuzzy conditions, the InnoDB engine will throw the above error because there are too many rows to lock. 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. my. the typical configuration of InnoDB in the default configuration file of InnoDB in cnf is as follows: [plain] # Uncomment the following if you are using InnoDB tables # innodb_data_home_dir =/home/root/tools/mysql-5.0.80/var/# innodb_data_file_path = ibdata1: 10 M: autoextend # innodb_log_group_home_dir =/home/root/tools/mysql-5.0.80/var/# innodb_log_arch_dir =/home/root/tools/mysql-5.0.80/var/# You can set .. _ buffer_pool_size up to 50-80% # of RAM 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 # bytes = 1 # innodb_lock_wait_timeout = 50 visible. InnoDB-related configurations are commented out by default, enable and specify the following new values (Note: The specific configuration values should be determined based on the physical configuration of the deployment machine): [plain] innodb_buffer_pool_size = 512 M innodb_additional_me M_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 bytes = 0 # Avoid frequent flush innodb_lock_wait_timeout = 50. After the modification is completed, 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 the Error 'unknown table engine 'innodb' on query will be reported when the SQL command is executed. 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 error log (. /var/xxx. err), 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. 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, all of which are Error logs that fail to load the frm file of the data table. From the log, an Error occurs when the mysql server instance is restarted, if the log file is not matched, the InnoDB engine fails to be loaded. How to solve it? 3. the final solution is as follows: 1) the mysql command line throws the 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 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 the mysql server and check that there is no ERROR in the mysql startup log, on the mysql command line show engines, you can see that the "Support" column corresponding to innodb is in the "YES" status, indicating that the mysql server has successfully loaded the engine. Finally, no error is reported when executing the SQL query command. So far, the problem is completely solved.