Bug #19204 ARCHIVE data size increases when table is flushed
Submitted: 19 Apr 2006 19:59 Modified: 1 May 2006 16:48
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.0.21 bk/5.1BK OS:Linux (Linux)
Assigned to: MC Brown CPU Architecture:Any

[19 Apr 2006 19:59] Dean Ellis
Description:
An unmodified ARCHIVE table's data size grows when the table is flushed.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a LONGBLOB ) ENGINE=ARCHIVE;
INSERT INTO t1 VALUES ('archive');
SELECT SQL_NO_CACHE a FROM t1;
SHOW TABLE STATUS LIKE 't1';
FLUSH TABLES;
SELECT SQL_NO_CACHE a FROM t1;
SHOW TABLE STATUS LIKE 't1';
FLUSH TABLES;
SELECT SQL_NO_CACHE a FROM t1;
SHOW TABLE STATUS LIKE 't1';

Suggested fix:
n/a
[19 Apr 2006 20:08] MySQL Verification Team
I was able to repeat,
[19 Apr 2006 20:08] Mark Leith
mysql> SELECT SQL_NO_CACHE a FROM t1;
+---------+
| a       |
+---------+
| archive |
+---------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 't1';
+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine  | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t1   | ARCHIVE | 10      | Compressed | 1    | 4109           | 59          | 4109            | 0            | 0         | NULL           | 2006-04-19 21:01:16 | 2006-04-19 21:01:16 |            | latin1_swedish_ci | NULL     |                |         |
+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT SQL_NO_CACHE a FROM t1;
+---------+
| a       |
+---------+
| archive |
+---------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 't1';
+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine  | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t1   | ARCHIVE | 10      | Compressed | 1    | 4109           | 79          | 4109            | 0            | 0         | NULL           | 2006-04-19 21:01:16 | 2006-04-19 21:01:16 |            | latin1_swedish_ci | NULL     |                |         |
+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SQL_NO_CACHE a FROM t1;
+---------+
| a       |
+---------+
| archive |
+---------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 't1';
+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine  | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t1   | ARCHIVE | 10      | Compressed | 1    | 4109           | 99          | 4109            | 0            | 0         | NULL           | 2006-04-19 21:01:16 | 2006-04-19 21:01:16 |            | latin1_swedish_ci | NULL     |                |         |
+------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.11 sec)

mysql> SELECT SQL_NO_CACHE a FROM t1;
+---------+
| a       |
+---------+
| archive |
+---------+
1 row in set (0.00 sec)

mysql> SELECT VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 5.1.7-beta-debug-log |
+----------------------+
1 row in set (0.02 sec)
[19 Apr 2006 20:09] James Day
Verified on Windows 5.0.18, grows by 20 bytes.
[20 Apr 2006 22:43] Brian Aker
Please reread the manual and then ask yourself if this is a bug (what you are seeing is the effect of the compression buffer being flushed).
[26 Apr 2006 13:48] MC Brown
Changed assignee.
[1 May 2006 16:48] Paul DuBois
Noted in 5.0.22 changelog.

Flushing the compression buffer (via <literal>FLUSH
TABLE</literal>) no longer increases the size of an unmodified
<literal>ARCHIVE</literal> table. (Bug #19204)