Page tree
Skip to end of metadata
Go to start of metadata

This topic describes how ClustrixDB differs from MySQL. 

Limitations

Number of Tables

ClustrixDB supports a maximum of approximately 2,000 tables. The precise limit for your installation depends on the number and type of indexes.

Row Size

  • In ClustrixDB, the maximum row size for persistent tables (stored on disk) is 64MB. 
  • The maximum row size when using RBR replication is 32MB.
  • In-Memory tables have a maximum row size of 32K, enforced at insert and update.

Schema Object Names

Identifier Case Sensitivity

In MySQL, the lower_case_table_names system variable also affects how the server handles identifier case sensitivity. ClustrixDB does not match any of the MySQL supported modes for this variable. On ClustrixDB, name comparisons are NOT case sensitive and table names are stored in the case specified in the CREATE statement. The lower_case_table_names global variable has a value of 1 and cannot be changed.

Reserved Keywords

The follow are reserved keywords in ClustrixDB but not MySQL:

  • complement
  • full
  • legacy
  • signed

Inline Variable Evaluation

ClustrixDB has limited support for inline variable evaluation. MySQL semantics depend very heavily on serial execution of queries, which makes it straightforward to enforce strict ordering throughout the evaluation of a query. ClustrixDB leverages a parallel evaluation model to provide distributed scale, so such serialization would have an adverse impact on performance.

Here is an example of how ClustrixDB provides only partial support for inline variables:

sql> create table foo (a int, b int);
sql> insert into foo values (2, 0), (4, 0), (6, 0);
 
sql>  set @rc :=1;
sql>  create table foo (a int, b int);
sql>  insert into foo values (2, 0), (4, 0), (6, 0);

sql> set @rc := 1;
sql> insert into foo select @rc := @rc + 1, a from foo limit 1;

Here, depending on when the ClustrixDB planner applies the LIMIT, the value for rc may be incremented multiple times. The results show that indeed, the inline variable handling incremented rc twice.

sql> select @rc;
+------+
| @rc  |
+------+
| 2    |
+------+
sql> select * from foo order by a, b;
+------+------+
| a    | b    |
+------+------+
|    2 |    0 |
|    2 |    2 |
|    4 |    0 |
|    6 |    0 |
+------+------+
4 rows in set (0.00 sec)

Duplicate Handlers for SQLSTATE Code

ClustrixDB allows duplicate handlers for the same SQLSTATE code within a stored procedure block. MySQL errors out during compilation.

Character Sets

ClustrixDB supports the following subset of the MySQL character set. To list the supported character sets, issue the following query:

sql> select * from mysql_character_sets;
+---------+-----------------------+--------------------+--------+
| Charset | Description           | Default collation  | Maxlen |
+---------+-----------------------+--------------------+--------+
| binary  | Binary pseudo charset | binary             |      1 |
| latin1  | CP1252 West European  | latin1_swedish_ci  |      1 |
| utf8    | UTF-8 Unicode         | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode         | utf8mb4_general_ci |      4 |
| koi8r   | KOI8-R Relcom Russian | koi8r_general_ci   |      1 |
+---------+-----------------------+--------------------+--------+
5 rows in set (0.00 sec)          

Collations

The default collation is utf8_general_ci. To list supported collations, issue the following query:

sql> show collation;  
+--------------------------+---------+------+---------+----------+---------+
| Collation                | Charset | Id   | Default | Compiled | Sortlen |
+--------------------------+---------+------+---------+----------+---------+
| binary                   | binary  |   63 | Yes     | Yes      |       1 |
| latin1_swedish_ci        | latin1  |    8 | Yes     | Yes      |       1 |
| latin1_bin               | latin1  |   47 | No      | Yes      |       1 |
| latin1_general_ci        | latin1  |   48 | No      | Yes      |       1 |
| latin1_general_cs        | latin1  |   49 | No      | Yes      |       1 |
| utf8_general_ci          | utf8    |   33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |   83 | No      | Yes      |       1 |
| utf8_unicode_ci          | utf8    |  192 | No      | Yes      |       1 |
| utf8mb4_general_ci       | utf8mb4 |   45 | Yes     | Yes      |       1 |
| utf8mb4_bin              | utf8mb4 |   46 | No      | Yes      |       1 |
| utf8mb4_unicode_ci       | utf8mb4 |  224 | No      | Yes      |       1 |
| koi8r_general_ci         | koi8r   |    7 | Yes     | Yes      |       1 |
| koi8r_bin                | koi8r   |   74 | No      | Yes      |       1 |
| latin1_swedish_ci_legacy | latin1  |  264 | No      | Yes      |       1 |
| latin1_general_ci_legacy | latin1  |  304 | No      | Yes      |       1 |
| latin1_general_cs_legacy | latin1  |  305 | No      | Yes      |       1 |
| utf8_general_ci_legacy   | utf8    |  289 | No      | Yes      |       1 |
+--------------------------+---------+------+---------+----------+---------+
17 rows in set (0.00 sec)

UTF8 Codepoints

UTF8 codepoints in ClustrixDB are not same as that of MySQL due to the fact that the internal implementation of UTF8 codepoint validity in ClustrixDB varies from that of MySQL.

Control Codes

Control codes, for example, space and empty strings, collate differently in ClustrixDB than MySQL. Both MySQL and ClustrixDB trim spaces at the end of strings, but in ClustrixDB, it is assumed that shorter strings always collate before longer strings. MySQL, however assumes a shorter string MAY collate after a longer string if the characters of the longer string contain pre-space characters.

Locking Behavior

LOCK TABLE

ClustrixDB ignores LOCK TABLE syntax. No error is returned, because dump files generated by mysqldump include LOCK TABLE commands and ClustrixDB must be able to process such files.

However, ClustrixDB does support LOCK TABLES ... FOR UPDATE which accepts a list of relations to issue an exclusive table lock on the listed relations. With this lock, inserts by other transactions are still allowed, but  UPDATE, DELETE, or SELECT FOR UPDATE will be blocked.  Note that for LOCK TABLES ... FOR UPDATE to be effective, it must be issued within the context of an explicit transaction, as the table lock is held until that transaction is committed or rolled back (which will be instantly if in autocommit mode). 

It is also possible to have LOCK TABLES ... FOR UPDATE also block inserts.  There is a performance penalty associated with having inserts check for this lock, so this behavior is not enabled by default.  This behavior is enabled on a per-table basis with ALTER TABLE table_name ENABLE INSERT_LOCK, and can be disabled with ALTER TABLE table_name DISABLE INSERT_LOCK.  A table with this behavior enabled will include INSERT_LOCK at the end of SHOW CREATE TABLE output:

sql> show create table foo\G  
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` int(11)
) CHARACTER SET utf8 /*$ REPLICAS=2 SLICES=6 INSERT_LOCK */

Here is an example of the behavior, where the s1> and s2> prompts indicate two separate sessions of mysql client:

s1> alter table foo enable insert_lock;
Query OK, 0 rows affected (0.22 sec)

s1> begin;
Query OK, 0 rows affected (0.00 sec)

s1> lock tables foo for update;
Query OK, 0 rows affected (0.03 sec)

s2> insert into foo values (666);
[session blocks]

s1> commit;
Query OK, 0 rows affected (0.00 sec)

[session s2 resumes]
Query OK, 1 row affected (2 min 14.29 sec)  

Global Variables to Control Locking

ClustrixDB provides the following global variables to control locking behavior:

  • lock_dest_on_insert_select - locks the destination table when executing statements that INSERT INTO... SELECT FROM
    • Note that this will only block UPDATE, DELETE, and SELECT FOR UPDATE statements.
    • To block inserts to the destination table, you must enable this global variable, then issue an ALTER TABLE ENABLE INSERT LOCK.
  • lock_on_insert_select - locks the source table when executing statements that INSERT INTO... SELECT FROM

These are also documented in the list of Global Variables

EXPLAIN statement

EXPLAIN statement output in ClustrixDB differs significantly from MySQL. In ClustrixDB, the output is a highly detailed, tree-based representation of a distributed query plan. In MySQL, only minimal information on index usage is displayed. Please see Understanding the ClustrixDB Explain Output for an explanation of ClustrixDB EXPLAIN output.  

Permissions / Privileges

The GRANT and REVOKE commands differ from their MySQL equivalents in that the column_list and object_type are ignored.

See also section on ClustrixDB versus MySQL Privileges.

Datatypes

DATETIME

Boolean values (True, False, Null) are accepted in a DATETIME column. This matches the behavior for MySQL v5.5. MySQL v5.6 provides an error message. 

See also Data TypesJSONFractional Seconds

Date Formats

Month, Day, or Year of Zero

Within ClustrixDB, dates containing all zeroes in the month, day, or year are considered to be invalid dates. MySQL considers date columns containing all zeroes to be valid date values. For example, a date type field set to 2010-03-00 returns 0000-00-00 in ClustrixDB and 2010-03-00 in MySQL.

Temporary Tables

For the ClustrixDB implementation of temporary tables, a self-INSERT from a temporary table is supported, whereas it is not supported in MySQL. For example, the following SQL statements will result in "Can't reopen table" with MySQL, but works for ClustrixDB:

sql> CREATE TEMPORARY table temp5860_foo (a int, b int);
sql> INSERT into temp5860_foo values (1,1), (2,1), (3,1), (4,1), (5,1), (6,1), (7,1), (8,1), (9,1), (0,1);
sql> INSERT into temp5860_foo select * from temp5860_foo; 

Logging

When ClustrixDB logs queries to query.log, the semicolon is stripped off. This means that any comments that are included with the statement are not logged.

SQL Limitations

Passed arguments must be less than 25K bytes.

Other

The ssl options cipher, issuer, and subject are not supported and generate syntax errors.

See also the list of Unsupported Features.

 

  • No labels