メタデータの末尾にスキップ
メタデータの先頭に移動

このトピックでは、MySQLの元の実装と異なる様々な機能をClustrixがどのようにサポートしているかを説明します。

MySQLとの一般的な違い

制限事項

テーブルの数の制限

Clustrix は最大 約 10,000テーブル をサポートします。正確な制限は、インデックスの数と種類によります。

行サイズの制限

Clustrixでは、最大の行サイズは製品版で60MBで、Clustrixの非製品版(AWSを含む)では 4MBです。この最大行サイズは、ストレージと、クエリの結果返る行の両方で、当てはまります。 

"パーティションの最大数" も見てください。

順序付け

By default, Clustrix imposes no order on result sets, whereas MySQL results are often ordered as a side effect of implementation (for example, when a query reads an index or includes a GROUP BY clause). To ensure that query results are returned in the desired sequence, specify the ORDER BY clause. Alternatively, set the global variable consistent_order to true. This will have an additional compile time overhead that is mitigated by the query cache.

エンジンのタイプ

Engine types are parsed and ignored by Clustrix. Clustrix most closely emulates the interface of the InnoDB storage engine. All tables in Clustrix are fully transactional and ACID-compliant and use the MVCC model for concurrency control.

インライン変数の評価

Clustrix has limited support for inline variable evaluation. MySQL semantics depend very heavily on serial execution of queries, whereas Clustrix has a parallel evaluation model, because Clustrix performance would be adversely affected by inline variables.

SQLSTATEコードのためのハンドラの複製

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

予約語 

Clustrix reserves the keyword "legacy" where MySQL does not. 

Clustrix does not permit columns to be named "rowid". 

照合順序

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

mysql> 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 |
| 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 |
+--------------------------+---------+------+---------+----------+---------+
15 rows in set (0.00 sec)

UTF8 コードポイント

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

制御コード

Control codes, for example, space and empty strings, collate differently in Clustrix than MySQL. Both MySQL and Clustrix trim spaces at the end of strings, but in Clustrix, 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.

LOCKの挙動

LOCK TABLE

Clustrix ignores LOCK TABLE syntax. No error is returned, because dump files generated by mysqldump include LOCK TABLE commands and Clustrix must be able to process such files. However, Clustrix 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 updates, deletes, and selects for update will be blocked.

Clustrix also provides a global variable, lock_table_alias_for_update that can be configured to alias a MySQL LOCK TABLE to LOCK TABLE FOR UPDATE.

EXPLAIN

EXPLAIN statement output in Clustrix differs significantly from MySQL. In Clustrix, the output is a highly detailed, tree-based representation of a distributed query plan. In MySQL, only minimal information on index usage is displayed.

PERMISSIONS / PRIVILEGES

The GRANT and REVOKE commands differ from their MySQL equivalents as follows:

  • column_list and object_type are ignored.

See also section on ClustrixDB versus MySQL Privileges

MySQL 関数

MySQL関数サポートのための注意事項

  • SYSDATE(): Does not reflect time zone settings; always returns UTC.
  • RAND(): Does not accept an integer argument as seed.
  • GROUP_CONCAT(): Only one permitted with ORDER BY in a query.
  • In Clustrix, LOCATE() returns NULL when position is provided as NULL. MySQL returns 0 in such instances.

日付 

月の0日目

Clustrix represents the zeroth day for a month as an invalid date. MySQL considers it to be a valid date value. For example, a date type field set to "2010-03-00" returns "0000-00-00" in Clustrix, whereas MySQL returns it as "2010-03-00."

ストアド ルーチン

サポートされないストアド ルーチンの機能

  • User-defined functions
  • Triggers
  • Altering a routine
  • Creating a view which calls a stored routine
  • Creating a prepared statement that calls a stored routine
  • SQL 2003 Compliant Case Statement
  • DECLARE ... CONDITION is not supported. Although Clustrix will create the stored procedure, the stored procedure will produce errors when invoked. 
  • Label support for BEGIN ... END
  • GIS functions
  • RESIGNAL

ストアド ルーチンの機能の注意事項

  • Clustrix will not report an error when encountering duplicate label names
  • Signals do not accept variables
  • When creating a table with a trailing select statement, (create ... select), the following data types do not translate properly:
    • bit type will always be bit(64)
    • decimal type will become vdecimal
    • timestamp type will be datetime.
    • enum and set type will always be varchar(256)
  • Storing a timestamp value in a session variable yields 0000-00-00 00:00:00.
  • Clustrix allows you to create a stored procedure with the same name as system built-in procedures. MySQL does not allow that. 
    Example: create procedure pi(); begin; end; does not error in Clustrix.

パーティション

パーティションでサポートされていないもの

  • LIST, HASH, KEY, LINEAR KEY partitions
  • SUBPARTITIONS
  • ABS()
  • DAYOFMONTH()
  • DAYOFWEEK()
  • DAYOFYEAR()
  • DATEDIFF()
  • Function extract()
  • MICROSECOND()
  • MINUTE()
  • MOD()
  • MONTH()
  • HOUR()
  • SECOND()
  • QUARTER()
  • TIME_TO_SEC()
  • WEEKDAY()
  • COALESCE PARTITION number
  • ANALYZE PARTITION {partition_names | ALL }
  • CHECK PARTITION {partition_names | ALL }
  • OPTIMIZE PARTITION {partition_names | ALL }
  • REBUILD PARTITION {partition_names | ALL }
  • REPAIR PARTITION {partition_names | ALL }
  • EXPLAIN PARTITION
  • Explicit selection of partitions

パーティショニング キー、プライマリキー および ユニークキー

The partitioning expression must contain the primary key and all columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have. This restriction does not apply if the table has no unique key constraint or a primary key. Any column can participate as partition key in that case. In the case of replicating between a MySQL Master and a Clustrix Slave, the MySQL Master allows a partition table to be created without a Primary Key. However, the Clustrix Slave will be disconnected with the error "Invalid partitioning scheme: Partitioned tables must have a primary key that includes the partition expression".

パーティション刈り込みの注意事項

If the partition key does not happen to be the leading column for a composite primary key, partition pruning does not get exercised. To work around this behavior, it is recommended to create an unique index on the partition key which will enable partition pruning.

パーティションの最大数

The maximum number of total partitions supported is 1024.

DDL

複数テーブルのDROPステートメント

A Clustrix slave errors out or stops on a multi-table DROP statement being replicated where only a subset of tables exist, whereas a MySQL slave does not.

異なるセッションからの DROP TABLE

Clustrix allows you to drop a table created and rows inserted from another session with autocommit set to 0 explicitly. MySQL hangs on a DROP TABLE command from a different session until the session that created the table, inserted rows, and set autocommit to 0 issues an explicit COMMIT.

SHOW CREATE TABLE 出力の中の Autoincrement 値

In Clustrix, the autoincrement value shows up improperly in SHOW CREATE TABLE output following an unconstrained delete. Clustrix resets it properly after one successful insert into the table.

ENUM欄のデフォルトの整数値

Clustrix accepts a default integer value for a column with ENUM values which is positional. MySQL errors out in such a situation.

CREATE TABLE と、デフォルトの整数値としてのクォートされた文字列

In Clustrix, CREATE TABLE does not raise an error when the default value for an INT type field is set to a quoted string; subsequent inserts with default value will however set the field value to 0. MySQL errors out in the CREATE TABLE statement itself.

CREATE TABLE foo (id int DEFAULT 'abc', name varchar(30));

The above will not error in Clustrix, however a subsequent insert statement will insert 0 for the id field.

CREATE TABLE と、デフォルトのNOT NULL フィールド値としての NULL

In Clustrix, CREATE TABLE does not raise error when the default value for a NOT NULL column is set to NULL; subsequent inserts with no default value specified fails. MySQL errors out in CREATE TABLE statement itself.

create table foo (id int not null default null);

The above will not fail in Clustrix however, insert into foo values(); will fail.

ユニーク外部キーの機能の違い

MySQL (InnoDB) allows foreign keys to reference non-unique parent keys, but Clustrix has opted to disallow foreign keys with non-unique parents with referential actions CASCADE or SET NULL. Non-unique parents are permitted as long as CASCADE and SET NULL are not also in use (RESTRICT and NO ACTION are permitted).

This change was made as of v5.0-8987 

Prior to those releases, the behavior for foreign keys to non-unique parents could result in errors for "Container key not found" in the case of concurrent actions to the multiple parent rows. 

To enforce this requirement, when a schema with a foreign key referencing a non-unique parent is created or altered and uses CASCADE or SET NULL, Clustrix will provide an error message:

Invalid foreign key specification encountered in DDL statement: FOREIGN KEY <name> requires UNIQUE index on
<name> if using the CASCADE or SET NULL referential actions

Clustrix Support can assist with identifying whether your schema contains constraints that are not permitted.

ALTER DATABASE

Clustrix has added the new-option described below to the available ALTER DATABASE syntax:

ALTER DATABASE [name] <mysql-option>
ALTER DATABASE [name] SET <mysql-option | new-option> [ , ... ]

where 

<mysql-option> ::= [DEFAULT] COLLATE [=] <collation-name>
            | [DEFAULT] CHARACTER SET [=] <charset-name>
 
<new-option> ::= <mysql-option>
    | HIDDEN [=] <TRUE | FALSE>

new-option (HIDDEN) is only for Clustrix-specific/non-MySQL arguments. For MySQL arguments (charset/collate), use the mysql-option so it works over replication.

Temporary Tables

For the Clustrix 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 Clustrix:
CREATE TEMPORARY table temp5860_foo (a int, b int);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);INSERT into temp5860_foo select * from temp5860_foo;

ログ

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

リプリケーション

Clustrix does not support table-level replication. 

その他

  • The ssl options cipher, issuer, and subject are not supported and generate syntax errors.
  • MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR and MAX_USER_CONNECTIONS are not supported and generate syntax errors.

 

TOP
inserted by FC2 system