Tokudb ROW_FORMAT 未被接受

我正在嘗試創建一些 tokudb 表來試驗不同的行格式選項來比較可用的壓縮。

https://www.percona.com/doc/percona-server/5.7/tokudb/using_tokudb.html

我已經嘗試了以下所有

TOKUDB_SNAPPY

TOKUDB_ZLIB

TOKUDB_DEFAULT

沒有效果。

如果我只是忽略它，則表是使用 row_fromat = fixed 創建的。

MariaDB [eventlog]> show VARIABLES like "%row_format%";
+--------------------------------+-------------+
| Variable_name                  | Value       |
+--------------------------------+-------------+
| tokudb_hide_default_row_format | ON          |
| tokudb_row_format              | tokudb_zlib |
+--------------------------------+-------------+



MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB, ROW_FORMAT=TOKUDB_DEFAULT;  
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TOKUDB_DEFAULT' at line 1    
MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ROW_FORMAT=tokudb_default;   
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tokudb_default' at line 1


MariaDB [eventlog]> CREATE TABLE stable1 ( column_a INT NOT NULL PRIMARY KEY, column_b INT NOT NULL) ENGINE=TokuDB;
Query OK, 0 rows affected (0.09 sec)


MariaDB [eventlog]> show table status from eventlog\G;
*************************** 1. row ***************************
          Name: stable1
        Engine: TokuDB
       Version: 10
    Row_format: Fixed
          Rows: 0
Avg_row_length: 0
   Data_length: 0
Max_data_length: 9223372036854775807
  Index_length: 0
     Data_free: 18446744073709551615
Auto_increment: NULL
   Create_time: 2017-02-20 12:26:18
   Update_time: 2017-02-20 12:26:18
    Check_time: NULL
     Collation: latin1_swedish_ci
      Checksum: NULL
Create_options: 
       Comment: 


MariaDB [eventlog]> ALTER TABLE stable1 ROW_FORMAT=TOKUDB_SNAPPY;  
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TOKUDB_SNAPPY' at line 1


version | 10.1.21-MariaDB  
tokudb_version | 5.6.34-79.1 

將 MariaDB 與 tokudb 外掛一起使用時，語法為

COMPRESSION=TOKUDB_LZMA並不是ROW_FORMAT=TOKUDB_LZMA

來自Mariadb 網站，

DDL 語法不同。雖然來自 Tokutek 的二進製文件具有修補的 SQL 解析器，但 MariaDB 中的 TokuDB 使用特殊的儲存引擎 API 擴展。因此，在 Tokutek 二進製文件中，您編寫 CLUSTERED KEY（列），例如，ROW_FORMAT=TOKUDB_LZMA。在 MariaDB 中，您編寫 KEY（列）CLUSTERING=YES 和 COMPRESSION=TOKUDB_LZMA。

該頁面還提供了有關實際檢查壓縮格式的更多資訊。

mysql -sNe 'SELECT dictionary_name, internal_file_name FROM information_schema.tokudb_file_map WHERE table_dictionary_name = "main" OR table_dictionary_name LIKE "key-%"' |perl -F'\t' -ane '@out=qx(tokuftdump --nodata --header --rootnode /var/lib/mysql/$F[1]); foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/) { print $F[0]."\t".$1."\n"; last } }'

上面的不同版本帶有表名，以便更容易檢查特定的表名。

mysql -sNe 'SELECT dictionary_name, internal_file_name, table_name, " " FROM information_schema.tokudb_file_map WHERE table_dictionary_name = "main" OR table_dictionary_name LIKE "key-%"' | perl -F'\t' -ane '@out=qx(tokuftdump --nodata --header --rootnode /srv/mysql/data/$F[1]); foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/) { print $F[2]."\t".$F[0]."\t".$1."\n"; last } }' > /var/tmp/tables.compressiontype.lst

會產生像

head /var/tmp/tables.compressiontype.lst 
TableName   ./database/TableName-key-entity_index   11
TableName   ./database/TableName-main   11

各種整數的含義可以在github repo中找到

   // compression methods
printf("typedef enum toku_compression_method {\n");
printf("    TOKU_NO_COMPRESSION = 0,\n");  // "identity" compression
printf("    TOKU_SNAPPY_METHOD  = 7,\n");  // google snappy
printf("    TOKU_ZLIB_METHOD    = 8,\n");  // RFC 1950 says use 8 for zlib.  It reserves 15 to allow more bytes.
printf("    TOKU_QUICKLZ_METHOD = 9,\n");  // We use 9 for QUICKLZ (the QLZ compression level is stored int he high-order nibble).  I couldn't find any standard for any other numbers, so I just use 9. -Bradley
printf("    TOKU_LZMA_METHOD    = 10,\n");  // We use 10 for LZMA.  (Note the compression level is stored in the high-order nibble).
printf("    TOKU_ZLIB_WITHOUT_CHECKSUM_METHOD = 11,\n"); // We wrap a zlib without checksumming compression technique in our own checksummed metadata.
printf("    TOKU_DEFAULT_COMPRESSION_METHOD = 1,\n");  // default is actually quicklz
printf("    TOKU_FAST_COMPRESSION_METHOD = 2,\n");  // friendlier names
printf("    TOKU_SMALL_COMPRESSION_METHOD = 3,\n");
printf("} TOKU_COMPRESSION_METHOD;\n");

引用自：https://dba.stackexchange.com/questions/164879