OceanBase Database Enterprise Edition V3.2.2

V3.2.2

Version information

• Release date: January 10, 2022

• Version: V3.2.2

Overview

OceanBase Database V3.2.2 improves the stability and ease of use of the database kernel to meet the production requirements of customers. It also introduces JSON data types in MySQL mode. Core features:

• Support for JSON data types

  JSON data types are supported in MySQL mode, and can be used in DDL operations, index creation, SQL queries, data type conversion, all JSON functions of MySQL 5.7, and some JSON functions of MySQL 8.0.

• Kernel enhancements

  Table groups support non-template-based subpartitions. Standby clusters can be disconnected from the primary cluster.

• Compatibility

  MySQL tenants support the BIT_AND(), BIT_OR(), and BIT_XOR() aggregate functions.

  Oracle tenants allow the ArrayBinding function to return the number of effective rows.

• Ease of use

  The SQL_AUDIT view displays the values of bound variables. PL statements return error messages and line numbers in case of parsing or execution exceptions.

• Stability

  Logs can be compressed for transfer. TIMESTAMP partitioning precision is improved.

• Performance

  The performance of TRUNCATE operations on tables with constraints is improved. Resource usage statistics of the SQL_AUDIT view are optimized. The multi-level compaction algorithm and LIMIT operator push-down logic are optimized.

New features

Database improvements

• Supports JSON data types for MySQL tenants.

  • Supports four basic types (strings, numbers, Boolean values, and NULL values) and two structured types (objects and arrays).

  • Allows you to create JSON columns when creating tables or adding columns.

  • Allows you to create indexes on columns generated based on JSON columns.

  • Allows you to reference JSON objects by using the -> or ->> operator.

  • Supports JSON text in SQL statements such as SELECT, INSERT, UPDATE, and DELETE.

  • Supports all JSON functions of MySQL 5.7 and some JSON functions of MySQL 8.0 such as JSON_OVERLAPS() and JSON_VALUE().

• Supports non-template-based subpartitions for table groups.

  Allows you to specify non-template-based RANGE-RANGE or LIST-LIST subpartitions when creating or deleting a table group.

• Supports disconnecting of standby clusters from the primary cluster.

  Allows you to disconnect a standby cluster from the primary cluster so that the standby cluster functions as a new independent cluster. During the upgrade of a business system, you can disconnect a standby cluster as a new cluster to verify business logic in this cluster, without affecting the primary cluster.

Increased compatibility

• Compatibility with MySQL

  • Introduces the GLOBAL and LOCAL keywords in the statement for creating an index.

    You can specify whether to create a global index or a local index. If no keyword is specified, a local index is created by default. We recommend that you use local indexes for partitioned tables. In most scenarios, local indexes present higher performance than global indexes.

  • The BIT_AND(), BIT_OR(), and BIT_XOR() aggregate functions are supported. You can call these functions to return the bitwise AND, OR, and XOR operation results of expressions.

  • Allows you to use subquery statements as outer join conditions.

• Compatibility with Oracle

  • Allows the ArrayBinding function to return the number of effective rows. The ArrayBinding function can return the number of affected rows regardless of whether the execution succeeds or fails.

Enhanced ease of use

• Displays the values of bound variables in the SQL_AUDIT view.

  The SQL_AUDIT view can record SQL statements with bound variables, such as SELECT * FROM A WHERE A=:1. A storage field is extended in the __all_virtual_sql_audit system table to record the real value of the bound variable :1. This facilitates troubleshooting of the SQL statement and allows you to directly use the SQL statement for replay.

• Returns error messages and error line numbers if errors occur during parsing and execution of PL statements, and supports recursive output of error line numbers.

• When the observer process starts, the log level of screen output is changed from WARN to ERROR to reflect the node startup status. This helps you identify risks and exceptions during the process startup as soon as possible and allows you to conveniently determine the node startup status without querying the views or logs.

Enhanced stability

• Supports log compression for transfer.

  When network bandwidth is insufficient and CPU resources are abundant, you can enable log compression algorithms to reduce bandwidth usage. The lz4_1.0, snappy_1.0, zlib_1.0, and zstd_1.0 compression algorithms are supported. You can use the ALTER SYSTEM SET DEFAULT_TRANSPORT_COMPRESS_FUNC = "zstd_1.0" statement to specify the compression algorithm.

• Optimizes TIMESTAMP partitioning precision.

  When the partitioning key type of a table under an Oracle tenant is TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE, the ODP queries the __all_virtual_proxy_partition_info table to obtain the precision of the TIMESTAMP partitioning key and uses the same algorithm of the OBServer to calculate the partitioning result for precise routing. This avoids secondary routing of the SQL statement between OBServers during TIMESTAMP partition routing.

Improved performance

• Optimizes performance of the TRUNCATE TABLE operation

  In the current data organization mechanism of OceanBase Database, the TRUNCATE TABLE operation combines the DROP and CREATE TABLE operations. It takes a long time when the table contains many CHECK constraints. This version is optimized for this scenario. It improves the performance of the TRUNCATE operation by approximately 50% when the table contains approximately 200 CHECK constraints.

• Optimizes resource usage statistics of the SQL_AUDIT view.

  This version optimizes the tenant-level lock mechanism to avoid repeated locking of tenant space, allocates fixed buffers to record SQL statements, and optimizes the waiting mechanism for event recording. It improves the performance by 5% in BMSQL scenarios.

• Optimizes the multi-level compaction algorithm.

  OceanBase Database uses the loser tree algorithm for major compaction of multiple rows from different tables. However, this algorithm results in high computing costs when the number of tables does not exceed 3. In this case, the loser tree algorithm is replaced with ordered array computation of three elements to optimize the data structure of the algorithm and reduce memory usage. When the number of tables does not exceed 3, the performance of this algorithm is 50% higher than that of the loser tree algorithm.

• Optimizes the push-down logic of the LIMIT operator.

  In paged queries with the ORDER BY clause, when the execution plan path is set to table access by global index primary key, the LIMIT operator can be pushed down to the TABLE LOOKUP operator to reduce the amount of generated data and improve the query performance.

Compatibility changes

Parameters

Behavioral changes

• In MySQL mode, if you do not specify the index type when creating an index, a local index (a global index in earlier versions) is created by default.

• In MySQL mode, the synonym feature is removed.

Upgrade path

• Upgrade from V3.2.1 to V3.2.2 is supported.

• Upgrade from V3.1.2 to V3.2.2 is supported. You must download the software packages of V3.2.0 BP1 and V3.2.1 and upload them to OCP.

• Upgrade from V2.2.77 is supported. You need to upgrade it to V3.1.2 first and then to V3.2.2.

Supported components

The following table describes the recommended versions of components used with OceanBase Database V3.2.2.

Fixed issues

• An indexed generated column is filled with incorrect values. When you create an index for a generated column, if the generated column contains a newly created column that has default values,

  nulls are populated. This causes data inconsistency between the table and the index, resulting in unexpected query results.

• Disk space usage of temporary files continuously increases due to low-probability release exceptions of cursor resources.

V3.2.2 BP1

Version information

• Release date: December 15, 2021.

• Version: V3.2.2 BP1

Enhanced features

• Using the DECODE() function in TRIGGER is supported in Oracle mode.

• Different time ranges are supported in RANGE, HASH, and LIST partition pruning.

Fixed issues

• The version of the maximum transaction is 0.

• The MERGE INTO statement returns inaccurate values for the affected_rows parameter.

• The success and failure results of XA transactions in PL scripts are different from those in Oracle Database.

• The MERGE SORT algorithm consumes a large amount of memory under the parallel execution framework.

• ODP fails to connect to a cluster after the cluster is restarted.

V3.2.2 BP2

Version information

• Release date: January 19, 2022

• Version: V3.2.2 BP2

Enhanced features

• The REVERSE() function is supported in Oracle mode.

• The performance of batch execution of the UPDATE statements is optimized.

Fixed issues

• When an RPC communication fails, the CHECKSUM() function returns inconsistent results for the data in a table and the index column.

• The column type of NULL columns in views is different from that in Oracle Database.

• An error is returned during file mounting.