Weird error while adding index for MEMORY table with row_format=Dynamic

[Valerii Kravchuk]

Recent PS 5.5.x versions produce weird error message when index is added for MEMORY table with Dynamic row format. Like this:

mysql> select version();
+-----------------+
| version() |
+-----------------+
| 5.5.31-30.3-log |
+-----------------+
1 row in set (0.02 sec)

mysql> create table tm (c1 int, c2 varchar(2000), c3 date, c4 time) engine=memory row_format=dynamic;
Query OK, 0 rows affected (0.07 sec)

mysql> show table status like 'tm'\G
*************************** 1. row ***************************
           Name: tm
         Engine: MEMORY
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 256
    Data_length: 0
Max_data_length: 16777216
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2013-05-28 20:21:40
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment:
1 row in set (0.00 sec)

mysql> create index i1 on tm(c1);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index i2 on tm(c2);
ERROR 1234 (42000): Incorrect usage/placement of 'key_block_size'
mysql> create index i2 on tm(c3);
ERROR 1234 (42000): Incorrect usage/placement of 'key_block_size'
mysql> create index i2 on tm(c4);
ERROR 1234 (42000): Incorrect usage/placement of 'key_block_size'
mysql> show table status like 'tm'\G
*************************** 1. row ***************************
           Name: tm
         Engine: MEMORY
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 272
    Data_length: 0
Max_data_length: 15845088
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2013-05-28 20:21:59
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC KEY_BLOCK_SIZE=256
        Comment:
1 row in set (0.00 sec)

[Valerii Kravchuk (valerii-kravchuk)]

I see no way to create MEMORY tables with Dynamic row format in PS 5.1.68 or 5.6.10, so I assume the bug does not apply to these versions.

[Laurynas Biveinis (laurynas-biveinis)]

This is obviously stemming for the VARCHAR/BLOB support in MEMORY. It imposes restrictions on column order and indexing.

Please review http://www.percona.com/doc/percona-server/5.5/flexibility/improved_memory_engine.html, especially the Caveats section and see if this bug report should be adjusted somehow, i.e. the feature or the docs could be improved somehow.

[Valerii Kravchuk (valerii-kravchuk)]

This error message or use case (I had never set key_block_size explicitly, so I may expect that server sets it as needed, no?) is NOT explained or documented at http://www.percona.com/doc/percona-server/5.5/flexibility/improved_memory_engine.html for sure.

[Laurynas Biveinis (laurynas-biveinis)]

Please check whether the CREATE TABLE statements that attempt to create schema that would result from successful ALTER TABLEs work? If it does, we can log a feature request for a better error message at least, if it does not, then it's a bug. In both cases we have a doc bug here as well.

The issue here is that CREATE TABLE auto-sizes the fixed-size block using the non-VARCHAR/non-BLOB columns. And the fixed-sizes block is the one that may be indexes. Thus we end up with fixed-size block with c1, and c2/c3/c4 in a dynamic block due to c2 being VARCHAR and no indexes created there. Then index create operation inside a dynamic block fails with a non-intuitive error message.

Workarounds: re-order columns so that c2 comes in the end, replace ALTER TABLE with CREATE, INSERT ... SELECT, DROP, RENAME.

[Alexey Kopytov (akopytov)]

It is a documentation bug. What we need to clarify in the Restrictions section of the docs is that:

the size of the fixed-format portion of the record is chosen automatically on CREATE TABLE and cannot be changed later. This, in particular, means that no indexes can be created later with CREATE INDEX or ALTER TABLE when the dynamic row format is used.

[Shahriyar Rzayev (rzayev-sehriyar)]

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