MEMORY table"is full" flag stuck when should not be

[Mihaly Arva-Toth]

I tried with latest 5.6.14-rel62.0-483 Percona build on Ubuntu Precise.

Steps to reproduce:
1. Set max_heap_table_size and tmp_table_size both to 16M.
2. Create a simple MEMORY type table with one column varchar().
3. Fill this table with lot of data (INSERT).
4. When you get back "is full", DELETE some rows for example 100.
5. At this time you should can to make new INSERT this table, because not should be full.
6. But you can not INSERT row because engine report "is full" still.

Workaround:
7. Execute ALTER TABLE <table_name> ENGINE=MEMORY; query.
8. And now you CAN insert new records.

I attached a simple Ruby test script, which does same things above.

[Jericho Rivera (jericho-rivera)]

I was able to reproduce on Percona Servers 5.5.35-33.0 and 5.6.15-63.0, can't reproduce on PS 5.1.73-rel14.11

Note: I did DROP TABLE IF EXISTS test before doing the procedure on 5.5 and 5.6.

mysql> select @@tmp_table_size;
+------------------+
| @@tmp_table_size |
+------------------+
| 16777216 |
+------------------+
1 row in set (0.00 sec)

mysql> select @@max_heap_table_size;
+-----------------------+
| @@max_heap_table_size |
+-----------------------+
| 16777216 |
+-----------------------+
1 row in set (0.00 sec)

See attachment for test results.

[Jericho Rivera (jericho-rivera)]

Was not able to reproduce on Oracle MySQL 5.6.15

root@percona-ubuntuvm:/opt/mysql/server-5.6/bin# ./mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table test (inmem varchar(8192)) ENGINE=MEMORY;
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE load_test() begin declare v_iterations int default 0; truncate table test; while v_iterations < 2100 do insert into test (inmem) values ("a"*8192); set v_iterations=v_iterations+1; end while; end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call load_test;
ERROR 1114 (HY000): The table 'test' is full

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2032 |
+----------+
1 row in set (0.00 sec)

mysql> DELETE FROM test LIMIT 100;
Query OK, 100 rows affected (0.01 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 1932 |
+----------+
1 row in set (0.00 sec)

mysql> INSERT INTO test VALUES ("a"*8192);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 1933 |
+----------+
1 row in set (0.00 sec)

[Mihaly Arva-Toth (mihaly-arva-toth+ulaunchpad)]

Have you got idea when will fix this issue?

[Valerii Kravchuk (valerii-kravchuk)]

You will see specific releases in the Milestone column where there is something definite to share.

[Jericho Rivera (jericho-rivera)]

Just to complete the tests, tested on latest PS 5.1 and upstream version but neither one affected.
max_heap_table_size and tmp_table_size both at 16M.

Server version: 5.1.73rel14.11 Percona Server with XtraDB (GPL), Release rel14.11, Revision 603

mysql> call load_test;
ERROR 1114 (HY000): The table 'test' is full
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2032 |
+----------+
1 row in set (0.00 sec)

mysql> delete from test limit 400;
Query OK, 400 rows affected (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 1632 |
+----------+
1 row in set (0.00 sec)

mysql> insert into test values ("a"*8192);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 1633 |
+----------+
1 row in set (0.00 sec)

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1469