Resolve MySQL Memory table "table is full" error 101209 13:13:32 [ERROR]/usr/local/mysql/bin/mysqld:the table ' Test_1291870945841162′is Fu ll101209 13:13:32 [ERROR]/usr/local/mysql/bin/mysqld:the table ' test_1291870945841162′is full101209 13:13:32 [ERROR]/ Usr/local/mysql/bin/mysqld:the table ' Test_1291870945841162′is full101209 13:13:32 [ERROR]/usr/local/mysql/bin/ Mysqld:the table ' Test_1291870945841162′is full It is important to have this phrase: You are using the MEMORY (HEAP) storage engine; Need to increase the value of the max_heap_table_size system variable. See section 5.1.3, "Server System Variables". So just modify the MySQL configuration file/etc/my.cnf, add/Modify two lines under [mysqld]:tmp_table_size = 256Mmax_heap_table_size = 256M system default is 16M, do not forget to restart MySQL, you can also dynamically modify the parameters online, after these days of observation, the world is a lot quieter. By modifying memory table Max_Rows can also. The-------------------------------------------(memory) heap is the fastest MySQL table to access data, using a hash index that is stored in memory. However, if MySQL or the server restarts, the data in the table will be lost. Usage: such as the online statistics of the forum, such table data should be irrelevant, in a few simple fields, the data is not much, the number of records will not exceed 1000, but the operation is the most frequent ( The basic user each action to update this table) . Note: When the data in the memory table is larger than the max_heap_table_size setting, MySQL will convert the excess data to disk, so this performance is greatly compromised. So we also need to adjust the max_heap_table_size according to our actual situation, for example, in the. cnf file [mysqld] in the following: Max_heap_table_size = 2048M In addition, the table statement can also be used Max_ Rows to control the number of records for the table. The memory table uses a hash index to keep the data in memory, so it has a very fast speed and is suitable for caching small to medium sized databases, but there are some restrictions on usage. 1. The heap connection to all users is visible, which makes it ideal for caching. 2, only suitable for use in the occasion. The heap does not allow xxxtext and xxxblob data types; only the = and <=> operators are allowed to search for records (<, >, <=, or >= are not allowed) ; Auto_increment is not supported; only non-null data columns are allowed to be indexed (not NULL). Note: the operator "<=>" description: Null-safe equal. This operator performs the same comparison operation as the "=" operator, but when the two opcode is null, the resulting value is 1 and not null, and when an opcode is null, The resulting value is 0 and not null. 3. Once the server restarts, all heap table data is lost, but the heap table structure still exists because the heap table structure is stored under the actual database path and is not automatically deleted. After the reboot, the heap is emptied, and the query results for the heap are empty. 4. If the heap is a replicated data table, all primary keys, indexes, self-increment, and so on after copyingThe format will no longer exist and you will need to re-add the primary key and index, if necessary. memory Restart Note: 5, for the data loss caused by the restart, there are the following solutions: A, before any query, execute a simple query, determine whether the heap table exists data, if not, then write the data again, or drop table to re-copy a table. This requires more than one query. However, it is convenient to write an include file and call it at any time when the page needs to use the heap table. b, for the page that needs the heap table, the dataset results are judged on the first and only the first time the table is queried, and if the result is empty, the data needs to be re-written. This saves you one query at a time. C, a better approach is to automatically write data to the heap each time MySQL restarts, but the need to configure the server, the process is more complex, the universality is limited. 6. Some expected SQL statements//If the table exists, delete the drop table if EXISTS ' abc ';//Copy entire table xyz to heap table ABC (contains all data) CREATE table ' abc ' TYPE=HEAP SELECT * From ' XYZ ';//Add primary key Idalter table ' abc ' Add PRIMARY key (' id ');//Add Index usernamealter table ' ABC ' Add index ' abc ' (' username '); nbsp; this digest from http://blog.csdn.net/lmocm/article/details/76146166
MySQL Memory engine table is full problem processing
