Replication just stopped in one Slave server with error: The table xtable is full which means no more records are permitted to insert in this table by MySQL and hence this has broken the replication.
I checked that xtable is having storage engine as Memory. In such tables, the max. no. of records you can insert is controlled by variable max_heap_table_size. When checking the size of this variable, I found that this is having default value:
mysql> show variables like ‘max_heap_table_size’;
+———————+———-+
| Variable_name | Value |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+
1 row in set (0.00 sec)
So we need to increase the value of this variable and then issue Alter Table command to make it effective. Also do not forget to add variable with new value in your my.cnf.
Change the variable value:
mysql> set max_heap_table_size=268435456;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘max_heap_table_size’;
+———————+———–+
| Variable_name | Value |
+———————+———–+
| max_heap_table_size | 268435456 |
+———————+———–+
1 row in set (0.00 sec)
Now letÃs truncate the table and issue Alter table on it to make the value effective for this table:
mysql> truncate table xtable;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table xtable ENGINE=MEMORY;
Query OK, 88 rows affected (0.06 sec)
Records: 88 Duplicates: 0 Warnings: 0
After this fix, the issue has been resolved. Make sure that you do not run the truncate command on table which have important data. I have issued truncate because it contains temporary/transactional data so we are fine with removing all records here.
