PolarDB:Usage notes

Limits

• Limits on features

  The following table describes the feature limits on X-Engine.

  Category	Feature	Description
  SQL features	Foreign key	Not supported
  	Temporary table	Not supported
  	Generated Column	Not supported
  	Handler API	Not supported
  Column properties	Maximum column size (LONGBLOB/LONGTEXT/JSON)	32 MB
  	GIS data type	X-Engine does not support GIS data types, including GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEMOMETRYCOLLECTION.
  Indexing	Hash index	Not supported
  	Spatial index	Not supported. X-Engine does not support the creation or use of full-text indexes.
  Transactions	Transaction isolation levels	Supports the following isolation levels: Read Committed (RC) Repeatable Read (RR)
  	Maximum account of data supported by a transaction	32 MB
  	Savepoint	Not supported
  	eXtended Architecture (XA) transaction	Supports internal XA transactions.
  Lock	Lock granularity	Supports table-level and row-level locks. Does not support gap locks.
  	Skip Locked	Not supported
  	Lock Nowait	Not supported
  Character set	Character sets supported by non-indexed columns	All
  	Character sets supported by indexed columns	Latin1 (latin1_bin) GBK (gbk_chinese_ci and gbk_bin) UTF-8 (utf8_general_ci and utf8_bin) UTF-8MB4 (utf8mb4_0900_ai_ci, utf8mb4_general_ci, and utf8mb4_bin)
  Primary/secondary replication	Binary log formats	Supports the following formats: stmt row mixed Note By default, binary logs use the row format. The stmt and mixed log formats may cause data security issues in specific concurrency scenarios.

  Note

  By default, the features of X-Engine that are not described in the preceding table are the same as the features of InnoDB.

• Limits on large transactions

  X-Engine does not support large transactions. If the number of rows modified in a transaction is equal to or greater than 10,000, X-Engine enables the commit in middle feature. This way, X-Engine can internally commit the transaction and start a sub-transaction to continue to perform the transaction. However, the commit in middle feature may not ensure the atomicity of transactions. Take note of the following items when you use the commit in middle feature:

  • Assume that you want to start a transaction to insert a large amount of data. During the insertion, part of the data is committed because of the commit in middle feature. The inserted data can be queried by other requests.

  • Assume that you want to start a transaction to modify a large amount of data. The data that has been committed by the commit in middle feature cannot be rolled back.

    DROP TABLE t1;
    CREATE TABLE t1(c1 int primary key , c2 int)ENGINE=xengine;
    BEGIN;
    call insert_data(12000);// 12,000 rows are inserted and a commit in middle operation is triggered. As a result, the first 10,000 rows of data are committed. 
    rollback;// You can roll back only the last 2,000 rows. 
    SELECT COUNT(*) FROM t1;// The committed 10,000 rows of data can be queried. 
    +----------+
    | COUNT(*) |
    +----------+
    |    10000 |
    +----------+
    1 row in set (0.00 sec)

  • Assume that you want to modify and delete a large amount of data in a transaction. The DELETE operation cannot read the committed rows in this transaction because of the commit in middle feature. As a result, you cannot delete the newly committed data.

    DROP TABLE t1;
    CREATE TABLE t1(c1 int primary key , c2 int)ENGINE=xengine;
    call insert_data(10000);
    BEGIN;
    INSERT INTO t1 VALUES(10001,10001), (10002,10002);
    DELETE FROM t1 WHERE c1 >= 0;// The deletion triggers a commit in middle operation, and the two rows of data inserted by the current transaction are not deleted. 
    commit;
    SELECT * FROM t1;
    +-------+-------+
    | c1    | c2    |
    +-------+-------+
    | 10001 | 10001 |
    | 10002 | 10002 |
    +-------+-------+
    2 rows in set (0.00 sec)

Change the storage engine of a table from InnoDB to X-Engine

1. Connect to the database that you want to manage by using Data Management (DMS), a client, or a CLI. For more information, see Connect to a cluster.

2. Execute the following statement to change the storage engine of a table from InnoDB to X-Engine:

   ALTER TABLE <database name>.<table name> ENGINE xengine;

   Note

   When you use the ALTER statement to change the storage engine of a table from InnoDB to X-Engine, DML and DDL write operations to the table are blocked.

Create a table by using X-Engine

To create a table by using X-Engine in a cluster that uses X-Engine and InnoDB, specify X-Engine as the storage engine when you create the table. Sample statement:

CREATE TABLE t1(c1 int primary key , c2 int) ENGINE=xengine;

Parameters