| 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: | |
| 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 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)
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