5.10.1 Lakehouse Limitations for all File Formats

• See Section 2.18.9, “Other Limitations” for all HeatWave MySQL query related limitations.

• Prior to MYSQL 9.1.2, you can modify loaded table data only using Lakehouse Section 5.4.1, “Lakehouse Incremental Load”. From MYSQL 9.1.2 onwards, you can also update loaded table data using ALTER TABLE command and editing the engine_attribute, or using Auto Parallel Load interface. For more information, see Editing Loaded Data.

• Do not create Lakehouse tables on the source DB in a replicated MySQL DB System if any of the replicas are outside HeatWave on OCI or HeatWave on AWS. This will cause replication errors.

• Before MySQL 8.4.0-u2, a replication channel might fail if a HeatWave Cluster is added to a replica of a MySQL DB System, and later manually stopped.

• It is not possible to dump external tables using the MySQL Shell export utilities, such as dumpInstance(). External tables are not replicated to InnoDB storage and cannot be exported. To export InnoDB data from a Lakehouse enabled database, exclude the external tables with an excludeTables option.

• It is not possible to restore a backup from a Lakehouse enabled MySQL DB System to a standalone MySQL DB System.

• A Lakehouse enabled MySQL DB System can support a maximum of 512 nodes.

• Before MySQL 8.4.0, Lakehouse does not enforce any specified constraints on primary key, unique key, foreign key and CHECK constraints. MySQL 8.4.0 removes this limitation for primary key and unique key constraints.

• Before MySQL 9.0.1-u1, the limit for the Lakehouse error message count is 100. As of MySQL 9.0.1-u1, Lakehouse supports max_error_count.

• The lakehouse_filter_warning_codes_list session variable has a limit of 50 codes and 250 characters.

• HeatWave Lakehouse does not support the following:

  • DML statements:

    • INSERT

    • UPDATE

    • DELETE

    • REPLACE

  • The CREATE TABLESPACE statement.

  • The following options for the CREATE TABLE statement:

    • AUTOEXTEND_SIZE

    • AVG_ROW_LENGTH

    • CHECKSUM

    • COMPRESS

    • CONNECTION

    • DATADIR

    • DELAY_KEY_WRITE

    • ENCRYPT

    • INDEXDIR

    • INSERT_METHOD

    • KEY_BLOCK_SIZE

    • MAX_ROWS

    • MIN_ROWS

    • PACK_KEYS

    • PASSWORD

    • ROW_FORMAT

    • STATS_AUTO_RECALC

    • STATS_PERSISTENT

    • STATS_SAMPLE_PAGES

    • UNION

  • The default expression for a column definition for the CREATE TABLE statement.

  • Creating temporary tables.

  • Creating AUTO_INCREMENT columns.

    Therefore, Lakehouse is not compatible with REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE.

  • Creating triggers.

  • Running ANALYZE TABLE.

  • Running ALTER TABLE statements that construct indexes, ADD or DROP columns, or add enforced check constraints.

  • SELECT statements without RAPID as the secondary engine.

  • Hidden columns.

  • Index construction.

  • Keys with column prefixes.

  • Running CREATE TABLE or ALTER TABLE statement containing a STORAGE clause in the column attributes of a Lakehouse table.

  • The use of CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP or NOW() as a default value for a timestamp column. Also an UPDATE statement with CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP or NOW(). Enable explicit_defaults_for_timestamp to use ALTER TABLE and CREATE TABLE statements with Lakehouse tables that have a timestamp column.

  • A zone map on a JSON column.