MySQL: The table is full Error - solved | ...::: Guatewireless.org ::::..

MySQL: The table is full error - resolved

Posted by fher98 on 12/18/08 • under Databases

Apart from my regular duties, I am also the DBA (database administrator) and honorary lately I've been correguir troubleshoot errors and MySQL. Everything started with the problem described below:

Among all our databases, there is one in particular that handles an average of 40k transactions per day. This database runs on MySQL which has a table which already exceeds the 21 million records, which make it have the 4 GB of information.

For its size, this error showed MyISAM table The table is full, which at first we thought we might be limitations 2.4.x kernel ext2 file system (if it is a Red Hat Linux 9).

MySQL's 4GB Limit

The MySQL error, the table is full is a message that is displayed when a table reaches 4GB in size, really underestimated the impact, as they are very few applications that may exceed this amount of data. But when we have consolidated our services and our database server MySQL stops working, is a major problem.

It is advisable to make a good analysis and design of our database so that this does not happen. However, sometimes it's hard to imagine that a table can contain 21 million records in two years.

InnoDB tables do not have this limitation because its storage model is completely different. In a MyISAM table with dynamic or variable-length rows, the index file of the table (nombre_tabla.MYI) rows stored using 32-bit pointer in the data file (nombre_tabla.MYI). This has a limit of 4 GB of space.

Remember that most computers are 32bit since technologies like 64-bit AMD Opteron processors also have support for 32bit.

Solving the problem of the table is full

The solution to the table is full is not very dicil, just enough to run the ALTER TABLE command, but it can take considerable time. Consider the example of the creature table Sleeve Length:

  mysql> describe creature;

  +-----------------+-----------------------+------+ -----+---------+----------------+

  | Field Â Â Â Â Â Â Â Â Â | Type Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | Null | Key | Default | Extra Â Â Â Â Â Â Â Â |

  +-----------------+-----------------------+------+ -----+---------+----------------+

  | Guide Â Â Â Â Â Â Â Â Â Â | int (10) Â Â Â Â unsignedÂ | NoA | PRI | NULL Â | auto_increment |

  | Ida Â Â Â Â Â Â Â Â Â Â Â Â | mediumint (8) unsigned | NoA | MUL | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | Map Â Â Â Â Â Â Â Â Â Â Â | smallint (5) unsignedÂ | NoA | MUL | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | SpawnMaskÂ Â Â Â Â Â | tinyint (3) unsignedÂ Â | NoA | Â Â Â Â | Â Â 1Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | Models Â Â Â Â Â Â Â | mediumint (8) unsigned | NoA | Â Â Â Â | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | Equipment_idÂ Â Â | mediumint (9) Â Â Â Â Â Â Â Â Â | NoA | Â Â Â Â | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | Position_xÂ Â Â Â Â | float Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | NoA | Â Â Â Â | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | Position_yÂ Â Â Â Â | float Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | NoA | Â Â Â Â | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | Position_zÂ Â Â Â Â | float Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | NoA | Â Â Â Â | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | Orientation Â Â Â | float Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | NoA | Â Â Â Â | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | SpawntimesecsÂ Â | int (10) Â Â Â Â unsignedÂ | NoA | Â Â Â Â | 120A Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | SpawndistÂ Â Â Â Â Â | float Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â | NoA | Â Â Â Â | Â 5Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | Currentwaypoint | mediumint (8) unsigned | NoA | Â Â Â Â | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | CurhealthÂ Â Â Â Â Â | int (10) Â Â Â Â unsignedÂ | NoA | Â Â Â Â | Â Â 1Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | CurmanaÂ Â Â Â Â Â Â Â | int (10) Â Â Â Â unsignedÂ | NoA | Â Â Â Â | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | DeathStateÂ Â Â Â Â | tinyint (3) unsignedÂ Â | NoA | Â Â Â Â | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  | MovementTypeÂ Â Â | tinyint (3) unsignedÂ Â | NoA | Â Â Â Â | 0A Â Â Â Â Â | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â |

  +-----------------+-----------------------+------+ -----+---------+----------------+

  17 rows in set (0.02 sec)

To find the limit of the size of the table, use the SHOW TABLE STATUS. And note that Max_data_length is 4 GB.

  mysql> SHOW TABLE STATUS LIKE 'creature' \ G;
 *************************** 1.  row ***************************
 Â Â Â Â Â Â Â Â Â Â Name: creature
 Â Â Â Â Â Â Â Â Engine: MyISAM
 Â Â Â Â Â Â Â Version: 10
 Â Â Â Â Row_format: Dynamic
 Â Â Â Â Â Â Â Â Â Â Rows: 95,094
 Â Avg_row_length: 46
 Â Â Â Data_length: 4382696
 Max_data_length: 281474976710655
 Â Â Index_length: 2628608
 Â Â Â Â Â Data_free: 0
 Â Auto_increment: 106,039
 Â Â Â Create_time: 2008-09-19 09:54:05
 Â Â Â Update_time: 2008-09-19 10:31:08
 Â Â Â Â Check_time: 2008-09-19 10:31:09
 Â Â Â Â Â Collation: utf8_general_ci
 Â Â Â Â Â Â Checksum: NULL
 Â Create_options: row_format = DYNAMIC
 Â Â Â Â Â Â Â Comment: Summon System
 1 row in set (0.00 sec)

 ERROR:
 No query specified

 mysql>

Let's fix this by typing:

  mysql> ALTER TABLE creature AVG_ROW_LENGTH max_rows = 200000000000 = 50;
 Query OK, 0 rows affected (0.03 seconds)

Now if we run the SHOW TABLE STATUS LIKE 'creature' \ G, we can see idea that the new value Max_data_length: 1099511627775. If so, now we can store more information in our MySQL table.

Note: This command in my hardware Intel (R) Xeon (TM) CPU 2.80GHz, with 2GB of RAM it takes 45 minutes.

The good thing about this whole experience is that we are more ready to migrate our computer center to support high availability for MySQL.

...::: Guatewireless.org ::::..

MySQL: The table is full error - resolved

MySQL's 4GB Limit

Solving the problem of the table is full

Please enter a comment

Search terms

More on 'Databases'

Recommended Sites

Translator

Recent Comments

Target