Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-3981

MyRocks alter table with string indexed column to non-binary collation works

    XMLWordPrintable

    Details

      Description

      I have reported this upstream and will link the ticket here, but I think it's worth to have it here also.
      MyRocks doesn't seem to support indexes on char fields without using binary collations and it prevents that on create table, but I can still create table with binary collation and alter it to non-binary collation or create table with different SE and alter it to RocksDB.

      1. Create table with InnoDB non-binary collation and alter it to rocksdb storage engine

      master [localhost] {msandbox} (test) > create table t1 (a int primary key, b char(3), key(b)) engine=rocksdb default charset=latin1 collate=latin1_general_ci;
      ERROR 12009 (HY000): Unsupported collation on string indexed column test.t1.b Use binary collation (binary, latin1_bin, utf8_bin).
      
      master [localhost] {msandbox} (test) > create table t1 (a int primary key, b char(3), key(b)) engine=innodb default charset=latin1 collate=latin1_general_ci;
      Query OK, 0 rows affected (0.09 sec)
      
      master [localhost] {msandbox} (test) > insert into t1 values (1, "  a");
      Query OK, 1 row affected (0.01 sec)
      
      master [localhost] {msandbox} (test) > show create table t1;
      +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                            |
      +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` int(11) NOT NULL,
        `b` char(3) COLLATE latin1_general_ci DEFAULT NULL,
        PRIMARY KEY (`a`),
        KEY `b` (`b`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
      +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      
      master [localhost] {msandbox} (test) > alter table t1 engine=rocksdb;
      Query OK, 1 row affected (0.07 sec)
      Records: 1  Duplicates: 0  Warnings: 0
      
      master [localhost] {msandbox} (test) > show create table t1;
      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                             |
      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` int(11) NOT NULL,
        `b` char(3) COLLATE latin1_general_ci DEFAULT NULL,
        PRIMARY KEY (`a`),
        KEY `b` (`b`)
      ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      
      master [localhost] {msandbox} (test) > insert into t1 values (4, "  a");
      Query OK, 1 row affected (0.02 sec)
      
      master [localhost] {msandbox} (test) > 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   | ROCKSDB |      10 | Fixed      |    1 |             24 |          24 |               0 |            0 |         0 |           NULL | NULL        | NULL        | NULL       | latin1_general_ci |     NULL |                |         |
      +------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
      1 row in set (0.00 sec)
      

      2. Alter rocksdb table with binary collation to non-binary collation

      master [localhost] {msandbox} (test) > create table t1 (a int primary key, b char(3), key(b)) engine=rocksdb default charset=latin1 collate=latin1_bin;
      Query OK, 0 rows affected (0.05 sec)
      
      master [localhost] {msandbox} (test) > insert into t1 values (1, "  a");
      Query OK, 1 row affected (0.03 sec)
      
      master [localhost] {msandbox} (test) > alter table t1 collate=latin1_general_ci;
      Query OK, 1 row affected (0.06 sec)
      Records: 1  Duplicates: 0  Warnings: 0
      
      master [localhost] {msandbox} (test) > insert into t1 values (2, "  a");
      Query OK, 1 row affected (0.02 sec)
      
      master [localhost] {msandbox} (test) > show create table t1;
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                           |
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` int(11) NOT NULL,
        `b` char(3) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
        PRIMARY KEY (`a`),
        KEY `b` (`b`)
      ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      
      master [localhost] {msandbox} (test) > 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   | ROCKSDB |      10 | Fixed      |    2 |             15 |          30 |               0 |           12 |         0 |           NULL | NULL        | NULL        | NULL       | latin1_general_ci |     NULL |                |         |
      +------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
      1 row in set (0.00 sec)
      

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              george.lorch George Lorch
              Reporter:
              tomislav.plavcic@percona.com Tomislav Plavcic
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: