MySQL Bugs: #44622: Using PARTITIONs with ARCHIVE engine reports 0 bytes in i

Bug #44622	Using PARTITIONs with ARCHIVE engine reports 0 bytes in i_s.TABLES
Submitted:	2 May 2009 19:29	Modified:	7 Mar 2010 12:47
Reporter:	Tim Soderstrom	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.34	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any
Tags:	archive, information_schema, partitions

Description:
Using PARITTIONs with the ARCHIVE engine causes inconsistent results when querying the information_schema.TABLES table. If the server has been restarted, or if the table has not been opened, the DATA_LENGTH will be reported as 0 bytes from i_s.TABLES on the table. Immediately running the same query produces the correct results.

This does not occur when using InnoDB or MyISAM with partitions and also does not occur when using ARCHIVE engine with no partitions.

How to repeat:
mysql> SHOW CREATE TABLE moocowproductions.Logger\G
*************************** 1. row ***************************
       Table: Logger
Create Table: CREATE TABLE `Logger` (
  `timestampOccurred` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dateOccurred` date NOT NULL,
  `session` char(32) DEFAULT NULL,
  `host` varchar(255) DEFAULT NULL,
  `sslMode` enum('enabled','disabled') DEFAULT 'enabled',
  `requestURI` varchar(255) DEFAULT NULL,
  `referer` varchar(255) DEFAULT NULL,
  `userAgent` varchar(255) DEFAULT NULL,
  `remoteHost` int(10) unsigned NOT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (TO_DAYS(dateOccurred))
(PARTITION p0801 VALUES LESS THAN (733438) ENGINE = ARCHIVE,
 PARTITION p0802 VALUES LESS THAN (733467) ENGINE = ARCHIVE,
 PARTITION p0803 VALUES LESS THAN (733498) ENGINE = ARCHIVE,
 PARTITION p0804 VALUES LESS THAN (733528) ENGINE = ARCHIVE,
 PARTITION p0805 VALUES LESS THAN (733559) ENGINE = ARCHIVE,
 PARTITION p0806 VALUES LESS THAN (733589) ENGINE = ARCHIVE,
 PARTITION p0807 VALUES LESS THAN (733620) ENGINE = ARCHIVE,
 PARTITION p0808 VALUES LESS THAN (733651) ENGINE = ARCHIVE,
 PARTITION p0809 VALUES LESS THAN (733681) ENGINE = ARCHIVE,
 PARTITION p0810 VALUES LESS THAN (733712) ENGINE = ARCHIVE,
 PARTITION p0811 VALUES LESS THAN (733742) ENGINE = ARCHIVE,
 PARTITION p0812 VALUES LESS THAN (733773) ENGINE = ARCHIVE) */
1 row in set (0.01 sec)

mysql> ALTER TABLE moocowproductions.Logger ENGINE='ARCHIVE';                                                                   Query OK, 945463 rows affected (13.86 sec)
Records: 945463  Duplicates: 0  Warnings: 0

mysql> SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='moocowproductions' AND TABLE_NAME='Logger';
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|           0 |            0 | 
+-------------+--------------+
1 row in set (0.00 sec)

mysql> SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='moocowproductions' AND TABLE_NAME='Logger';
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|    28680102 |            0 | 
+-------------+--------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE moocowproductions.Logger ENGINE='MyISAM';Query OK, 945463 rows affected (7.81 sec)
Records: 945463  Duplicates: 0  Warnings: 0

mysql> SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='moocowproductions' AND TABLE_NAME='Logger';
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|   195406416 |        12288 | 
+-------------+--------------+
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE test.Logger\G
*************************** 1. row ***************************
       Table: Logger
Create Table: CREATE TABLE `Logger` (
  `timestampOccurred` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dateOccurred` date NOT NULL,
  `session` char(32) DEFAULT NULL,
  `remoteHost` int(10) unsigned NOT NULL,
  `sslMode` enum('enabled','disabled') DEFAULT 'enabled',
  `host` varchar(255) DEFAULT NULL,
  `requestURI` varchar(255) DEFAULT NULL,
  `referer` varchar(255) DEFAULT NULL,
  `userAgent` varchar(255) DEFAULT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=150
1 row in set (0.00 sec)

mysql> ALTER TABLE test.Logger ENGINE='ARCHIVE';
Query OK, 945463 rows affected (13.70 sec)
Records: 945463  Duplicates: 0  Warnings: 0

mysql> SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='Logger'; 
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|    28100448 |            0 | 
+-------------+--------------+
1 row in set (0.01 sec)

Suggested fix:
DATA_LENGTH should always report the proper size for ARCHIVED and PARTITIONED tables, no matter whether or not MySQL has been recently restarted, or the table altered, etc.

Thank you for the bug report. Duplicate of bug http://bugs.mysql.com/bug.php?id=43030.

I don't how this could be a duplicate of 43030? That bug is in reference to creating partitions with the archive engine. I am referring to the fact that the DATA_LENGTH field information_schema.TABLES is 0 if the ARCHIVE/partitioned table in question was not previously opened. Further, bug 43030 is only patching the test-suite. This issue has nothing to do the test-suite but with the ARCHIVE engine and MySQL itself.

Please read the note in the patch done for that bug:

" This is not a bug. Since the archive engine does not support 
      indexes, it cannot be used for partitioning."

Please try the test case provided and you will see that the above commands *DO* work in current versions of MySQL 5.1. I have successfully created, and use, partitioned tables using the archive engine. Regardless, that is not the issue. The issue is, again, that the DATA_LENGTH field in the inforamtion-schema.TABLES is reporting the wrong size initially.

Adjusting the test case will NOT FIX this bug!

Further proof partitions + ARCHIVE works:

http://datacharmer.blogspot.com/2006/03/mysql-51-improving-archive-performance.html
http://dev.mysql.com/tech-resources/articles/testing-partitions-large-db.html

Tim, Miguel;

Verified as described.

Partitioned tables in ARCHIVE engine report incorrect DATA_LENGH in I_S.TABLES when not open in table cache. I'm not sure which it needs to be in, table_open_cache or table_definition_cache.

Concise test:

mysql> USE test;

mysql> CREATE TABLE t1 (f1 DATE NOT NULL) 
ENGINE = ARCHIVE PARTITION BY RANGE (TO_DAYS(f1)) 
(partition p1 values less than (733751), 
 partition p2 values less than MAXVALUE); 

mysql> INSERT INTO t1 VALUES(CURRENT_DATE); 

mysql> SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|           0 |            0 | 
+-------------+--------------+
1 row in set (0.01 sec)

mysql> SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|         190 |            0 | 
+-------------+--------------+
1 row in set (0.00 sec)

mysql> flush tables; 
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|           0 |            0 | 
+-------------+--------------+
1 row in set (0.00 sec)

mysql> SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
+-------------+--------------+
| DATA_LENGTH | INDEX_LENGTH |
+-------------+--------------+
|         200 |            0 | 
+-------------+--------------+
1 row in set (0.00 sec)

Reports from other bugs fixed in MySQL 5.1 mention ARCHIVE engine + PARTITIONING.  This bug should be handled were those.

SELECT COUNT(*) is not correct for some partitioned tables. (Bug#32247) Fixed in 5.1.28

SHOW CREATE TABLE misreported the value of AUTO_INCREMENT for partitioned tables using either of the InnoDB or ARCHIVE storage engines. (Bug#32247) Fixed in 5.1.23

A partitioned table that used the DATA DIRECTORY option, where the data directory was the same as the directory in which the table definition file resided, became corrupted following ALTER TABLE ENGINE=ARCHIVE. This was actually due to an issue with the ARCHIVE storage engine, and not with partitioned tables in general. (Bug#22634) Fixed in 5.1.15

Seems like ha_archive::info does not support HA_STATUS_CONST, but instead does those things in HA_STATUS_TIME...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/80574

3063 Mattias Jonsson	2009-08-11
      Bug#44622: Using PARTITIONs with ARCHIVE engine reports 0 bytes in i_s.TABLES
      
      Problem was that ha_archive::info did not use the flag argument
      correctly
      
      fixed so that it updated the correct values depending on the flag.
     @ mysql-test/r/partition_archive.result
        Bug#44622: Using PARTITIONs with ARCHIVE engine reports 0 bytes in i_s.TABLES
        
        Updated testresult
     @ mysql-test/t/partition_archive.test
        Bug#44622: Using PARTITIONs with ARCHIVE engine reports 0 bytes in i_s.TABLES
        
        Added testcase
     @ storage/archive/ha_archive.cc
        Bug#44622: Using PARTITIONs with ARCHIVE engine reports 0 bytes in i_s.TABLES
        
        Fixed ha_archive::info() so that it updated the correct values
        depending on the flag. (did it all only if HA_STATUS_TIME before...)

Pushed to mysql-next-mr-bugfixing

pushed to mysql-6.0-codebase-bugfixing as well.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)

Documented bug fix in the 5.6.0 and 6.0.14 changelogs as follows:

        The first time that a query against the
        INFORMATION_SCHEMA.TABLES table for partitioned tables using the
        ARCHIVE engine was run, it returned invalid data. If the server
        had been restarted since such a table had been created, or if
        the table had never actually been opened, its DATA_LENGTH was
        reported as 0 bytes. (The second and subsequent attempts to
        issue the same query returned the expected result.)

Closed.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.