8.1 Release Notes

Enhanced workload management so that a medium-size query can be put in the large-query queue if there are free resources for large queries but the resources for medium-size queries are fully used. This can improve throughput when there are many medium-size queries and few large ones. See the related discussion of the new variable  workload_management_queue_size_allow_upgrade.

Enhanced disk space manageability that allows controlled space sharing between column store blob cache and query spilling. Supports setting the maximum disk spilling space limit explicitly with a new engine variable spilling_maximum_disk_percent. Additional metrics in the SHOW STATUS EXTENDED output give details about the disk usage:

Added support to optimize table scans with an Order-By-Limit by (a) do the order and limit first, with only the minimum needed columns, and then (b) using a self-join to retrieve the additional necessary columns for only the qualifying rows. This optimization can be enabled/disabled via the optimizer_enable_orderby_limit_self_join session variable, which is ON by default.

Added a METADATA_ONLY argument for shard keys which will prevent an index from being created on shard keys thereby saving memory. It can cause queries that would have used that index to run slower.

Reduced the amount of memory used by autostats. This can be controlled via the autostats_flush_interval_secs engine variable.

Added two Information Schema views for tracking connection attributes: LMV_CONNECTION_ATTRIBUTES and MV_CONNECTION_ATTRIBUTES.

A new WORKLOAD_MANAGEMENT_QUEUE system table has been added which shows the list of queries for which the WM is not able to reserve resources and therefore has put them into the waiting queue.

Added the ability to add and drop FULLTEXT indexes via the ALTER TABLE and DROP INDEX commands. This allows simple addition or removal of full-text search capability without reloading data. A table can only have one FULLTEXT index. For example, with the following table:

CREATE TABLE articles (
    id INT UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SORT KEY (id));

Add a FULLTEXT index via:

ALTER TABLE articles ADD FULLTEXT (title, body);

To remove the index, use either of these commands:

ALTER TABLE articles DROP INDEX title;

DROP INDEX title ON articles;

You cannot add and drop an index in a single ALTER TABLE statement.

Added the ability to start only failed pipelines via the START FAILED PIPELINES command.

START FAILED PIPELINES;

Added the ability to load Avro formatted data from an AWS S3 bucket using LOAD DATA, which can be simpler than using pipelines.

LOAD DATA S3 '<bucket name>'
CONFIG '{"region" : "<region_name>"}' 
CREDENTIALS '{"aws_access_key_id" : "<key_id> ", 
             "aws_secret_access_key": "<access_key>"}'
INTO TABLE <table_name>
       (`<col_a>` <- %, 
 `<col_b>` <- % DEFAULT NULL , 
  ) FORMAT AVRO;

Added the ability to load Parquet formatted data from an AWS S3 bucket, which can be simpler than using pipelines.

LOAD DATA S3 '<bucket name>'
CONFIG '{"region" : "<region_name>"}' 
CREDENTIALS '{"aws_access_key_id" : "<key_id> ", 
             "aws_secret_access_key": "<access_key>"}' 
INTO TABLE <table_name>
       (`<col_a>` <- %, 
 `<col_b>` <- % DEFAULT NULL , 
  ) FORMAT PARQUET;

Added ability for users with SUPER or ALTER USER privileges to be able to see the hashed password.

SHOW GRANTS FOR <user>;

Added the WARM BLOB CACHE FOR INDEX clause to the OPTIMIZE TABLE command to ensure consistent query performance when querying through a hash index, irrespective of when the query was last accessed.

Improved the performance of a left join of a reference table with a sharded table by pushing the reference table clauses to the leaves. The engine variable disable_reference_table_leftjoin_where_pushdown must be set to "OFF" to enable this operation. (8.1.4)

auto_profile_type: Determines the mode of auto profiling. There are two values: LITE and FULL.

autostats_flush_interval_secs: Determines when autostats are flushed to disk if they are not used within the specified time. The default value is 600 seconds. If the engine variable is set to 0, autostats will always stay in memory.

compiled_images_eviction_memory_limit_percent: Sets the percentage of maximum_memory that is available for caching query object code images. This variable is set to 0 by default. For more information about this engine variable, see compiled_images_eviction_memory_limit_percent and compiled_images_eviction_memory_limit_mb.

compiled_images_eviction_memory_limit_mb: Sets the memory limit (in MBs) that is available for caching query object code images. For more information about this engine variable, see compiled_images_eviction_memory_limit_percent and compiled_images_eviction_memory_limit_mb.

enable_compiled_images_eviction is now enabled by default.

sql_mode is now a Sync variable and is settable on SingleStoreDB Cloud.

spilling_maximum_disk_percent: Sets the maximum percentage of disk space to be used by disk spilling. The value must be between 0 and 1 or left to the default (-1).

Two new Sync variables control the dynamic reuse of WM queues: workload_management_dynamic_resource_allocation  and  workload_management_queue_size_allow_upgrade. The default value for workload_management_queue_size_allow_upgrade is 1. This means we can upgrade queries from the medium queue to the large queue until the large queue length becomes equal to 1.

backup_multipart_upload_concurrency : Improves backup performance.

Now allowing child aggregator root users to be dropped even if the sync_permissionengine variable is enabled. (8.1.4)

Removed the restrictions on the names of JWT users. Customers may now create users with names that look like UUIDs and emails. (8.1.4)

Added more information about blob files that need to be downloaded from unlimited storage to the blob cache when running PROFILE. (8.1.4)

Changed the system information built-ins to use the utf8_general_ci collation for the collation_server value. (8.1.3)

Added capability to create Wasm functions in SingleStoreDB clusters with heterogeneous hardware, e.g., with different levels of SIMD support.

Wasm modules are now serialized to the plancache. Refer to Code Engine - Powered by Wasm for more information.

SingleStoreDB now supports creation of Wasm-based user-defined aggregate functions (UDAFs). Refer to CREATE AGGREGATE for more information.

The CREATE FUNCTION statement now returns an error if the USING EXPORT clause is omitted and the Wasm module has functions with the same name but different case. Refer to Wasm UDF/TVF Naming Convention for more information.

Added a new metric to the output of the SHOW STATUS EXTENDED command, which displays the memory allocated (in MBs) used to cache query object code images, as derived from compiled_images_eviction_memory_limit_percent and compiled_images_eviction_memory_limit_mb.

Adjusted how CREATE_TIME, CREATE_USER, ALTER_TIME, and ALTER_USER columns in information_schema.TABLES are set so that they are given proper values for the relevant CREATE and ALTER commands.

Enhanced support for recursive common table expressions (CTEs) by expanding the range of query shapes allowed, improving column type checking and resolution between the base and recursive branches, and adding cross database support. Also, resolved the issue that caused the "ER_UNSUPPORTED_RECURSIVE_CTE_SHAPE" error with the accompanying message about dependency on a streaming result table outside the cycle.

Renamed spill_outputted_rows to non_aggregated_output_rows for the shuffle groupby operator in the profiling output to avoid confusion with disk spilling.

[...]
"non_aggregated_output_rows":{ "value":1048576, "avg":349525.333333, 
[...]

Improved JSON encoding speed for sparse schemas (JSON schemas with a very large number of distinct property names across the full set of documents, with most properties missing in each document). This applies only to universal storage.

Incremental backups are supported on SingleStoreDB Self-Managed for unlimited storage space. Note: incremental backups are not supported as part of the managed backup solution.

Updated OpenSSL to version 3.0, which is now used to establish secure connections to SingleStoreDB. As a consequence, a client certificate that uses SHA or MD5 hash functions in its signature must be replaced with a certificate that uses SHA256 at a minimum, or a secure connection to SingleStoreDB cannot be established. While SingleStoreDB supports TLS v1, TLS v1.1, and TLS v1.2, using TLS v1.2 is recommended. When FIPS is enabled, only TLS v1.2 is supported. Refer to Troubleshoot OpenSSL 3.0 Connections for more information.

The ORDER BY ALL [DESC|ASC] (or ORDER BY *) syntax is now supported.

The GROUP BY ALL [DESC|ASC] (or GROUP BY *) syntax is now supported.

Added the following JSON functions: