OceanBase Database Enterprise Edition V3.2.2

V3.2.2 BP2

Version information

• Release date: January 19, 2022

• Version: V3.2.2 BP2

• RPM version: oceanbase-3.2.2-20220119123901

Enhanced features

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

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

Bug fixes

• Fixed the issue where when an RPC communication failed, the CHECKSUM() function returned inconsistent results for the data in a table and the index column.

• Fixed the issue where the column type of NULL columns in views was different from that in Oracle Database.

• Fixed the issue where an error was returned during file mounting.

V3.2.2 BP1

Version information

• Release date: December 15, 2021.

• Version: V3.2.2 BP1

• RPM version: oceanbase-3.2.2-20211215001504

Enhanced features

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

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

Bug fixes

• Fixed the issue where the version of the maximum transaction was 0.

• Fixed the issue where the MERGE INTO statement returned inaccurate values for the affected_rows parameter.

• Fixed the issue where the success and failure results of XA transactions in PL scripts were different from those in Oracle Database.

• Fixed the issue where the MERGE SORT algorithm consumed a large amount of memory under the parallel execution framework.

• Fixed the issue where ODP failed to connect to a cluster after the cluster was restarted.

V3.2.2

Version information

• Release date: January 10, 2022

• Version: V3.2.2

• RPM version: oceanbase-3.2.2-20211130225726

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-compatible mode. Core features include:

• Support for JSON data types

  JSON data types are supported in MySQL-compatible 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-compatible tenants support the BIT_AND(), BIT_OR(), and BIT_XOR() aggregate functions.

  Oracle-compatible 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-compatible 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.

Enhanced 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-compatible 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 node to calculate the partitioning result for precise routing. This avoids secondary routing of the SQL statement between OBServer nodes 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-compatible 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-compatible mode, the synonym feature is removed.

Upgrade notes

• 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.

Bug fixes

• Fixed an issue where 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.

• Fixed the issue where disk space usage of temporary files continuously increases due to low-probability release exceptions of cursor resources.