List of Engine Variables

The following tables list the sync engine variables and non-sync engine variables. Some variables have an in-depth description that supplements the description found in the table.

Sync Variables Lists

See the engine variables overview to learn how sync variables work.

All of the sync variables listed in the following table are global variables, except when noted as a session variable that can also be set globally in the Description column.

You can set sync variables on the master aggregator, only. After you set a sync variable, the nodes affected by your update (all aggregators, all leaves, or both) depend on the variable’s type and the command you use to set the variable. Each variable’s type is indicated in the last sentence in the Description column in the following table.

Notice

Refer to the SingleStore DB Sync Variables List for a complete list of sync engine variables, including those that can be set with the assistance of SingleStore Support.

Name

Description

Default

cardinality_estimation_level

Possible values are '6.0', '6.5', '7.0', '7.1', and '7.3'. The behavior of each setting follows: '6.0': Use legacy histograms; '6.5': Use advanced histograms; '7.0': Same use of histograms as 6.5; '7.1': Use histograms for join estimation; '7.3': Use sampling and histograms together (when both are available), to improve selectivity estimation.

'7.1'

columnstore_flush_bytes

Controls the rowstore-backed segment size for columnstore tables. For more information, see Advanced Columnstore Configuration Options. This variable can sync to all aggregators and all leaves.

33554432

columnstore_segment_rows

Controls the maximum row count for a columnstore segment. For more information, see Advanced Columnstore Configuration Options. This variable can sync to all aggregators and all leaves.

1024000

compiled_images_eviction_memory_limit_mb

Sets a limit in megabytes for the memory amount used for query object code images.

4096

data_conversion_compatibility_level

Specifies the level of data conversion behavior to use. Higher levels throws errors for integer under/overflow, illegal conversion of strings to integers, and string truncation issues. Values can be either '7.5', '7.0', '6.5' or '6.0'. This variable can sync to all aggregators and all leaves. It’s recommended to set this to the highest available level for new application development. For more information, see Data Type Conversion.

'6.0'

default_autostats_enabled

Whether autostats is enabled by default on newly created columnstore tables. This variable can sync to all aggregators and all leaves.

ON

default_distributed_ddl_timeout

The time, in milliseconds, to wait for a distributed DDL transaction to commit. This value sets the timeout for both ALTER TABLE and BACKUP commands. If the timeout is reached, the transaction is rolled back. This variable can sync to all aggregators and all leaves.

180000

default_partitions_per_leaf

The default number of partitions a newly created database will be created with. When you set this variable, its value is propagated to other aggregators only.

8

default_table_type

The default table type to use when creating a new table. In SingleStore DB, value can be set to columnstore or rowstore. When the value is columnstore you can create a columnstore table using standard CREATE TABLE syntax. In SingleStore Managed Service (SMS), default_table_type is set to columnstore for newly created 7.3 clusters only. For existing SMS clusters, the setting remains at rowstore. This variable is read-only in SMS. This variable can sync to all aggregators.

columnstorein SingleStore DB. In SMS, default_table_type is set to columnstore for newly created 7.3 clusters only. For existing SMS clusters, the setting remains at rowstore.

disk_plan_expiration_minutes

The interval in which a query plan must be read from disk (plancache directory) before it is removed. This variable can sync to all aggregators and all leaves.

20160 (14 days)

distributed_commit_lock_timeout

Deprecated in MemSQL 6.5. Replaced by default_distributed_ddl_timeout.

enable_background_plan_invalidation

Controls whether the background statistics thread can invalidate plans automatically. This can improve plans if your data changes over time at the cost of spending time recompiling plans. This variable can sync to all aggregators.

OFF

enable_disk_plan_explain

Retains human-readable query plan summaries for compiled query plans. Retained information is available through the SHOW PLAN command. This variable can sync to all aggregators.

OFF

explain_expression_limit

Specifies the maximum number of characters to be used by expressions when outputting the EXPLAIN for a query. This variable can sync to all aggregators and all leaves.

500

explicit_defaults_for_timestamp

Specifies whether the server disables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns. See Timestamp Behavior for details on this variable. This variable can sync to all aggregators and all leaves.

ON

geo_sphere_radius

The radius of the sphere used for distance calculation, in meters. (Defaults to average Earth radius.) This variable can sync to all aggregators and all leaves.

6367444.657120

highlight_max_number_fragments

Maximum number of fragments to return from highlight function. This variable can sync to all aggregators and all leaves.

0

highlight_fragment_size

The size of a block of characters used to logically divide up string columns when using the full-text search highlight functionality. Minimum value is 10, maximum value is 65535. This variable can sync to all aggregators and all leaves.

100

json_extract_string_collation

Controls the collation setting for JSON_EXTRACT_STRING function. This variable can sync to all aggregators and all leaves. For more details, see the In-Depth Variable Definitions section below.

auto

load_data_errors_retention_minutes

The amount of time, in minutes, that a LOAD DATA error is stored on disk. This variable must be set to at least 0. These errors are surfaced in information_schema.LOAD_DATA_ERRORS as long as they are stored on disk. This variable can sync to all aggregators and all leaves.

1440

load_data_internal_compression

If this variable is set to ON and a network bottleneck is detected, LOAD DATA compresses data on the aggregator before forwarding it to the leaves. This variable can sync to all aggregators and all leaves.

ON

load_data_max_buffer_size

Maximum number of unparsed bytes read by LOAD DATA before throwing an error. This variable can sync to all aggregators and all leaves.

1073741823

load_data_read_size

Number of bytes read at a time by LOAD DATA. This variable can sync to all aggregators and all leaves.

8192

load_data_write_size

Number of bytes written at a time by LOAD DATA. This variable can sync to all aggregators and all leaves.

8192

lock_wait_timeout

Time, in seconds, to wait for a row lock before returning an error. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves.

60

max_async_compilation_concurrency

The number of compiles that can run asynchronously at a given time on each node, when interpreter_mode is set to interpret_first. Its effective value is capped at the number of cores on the host machine. If set to 0, it takes the default value.

Half the number of cores on the host machine.

max_compilation_memory_mb

The maximum amount of memory (in MBs) used to compile a query. An error is returned if the query reaches the specified memory limit.

4096

max_compilation_time_s

The maximum time allowed (in seconds) to compile a query. An error is returned if the query reaches the specified compilation time limit, and any subsequent attempts to compile the query fail.

600

plan_expiration_minutes

The interval in which a query plan must be reused at least once or it gets unloaded from the query plan cache. This variable can sync to all aggregators and all leaves.

720

privilege_transfer_mode

A global variable that controls whether privilege transferability is determined by GRANT OPTION or TRANSFERABLE keyword in the GRANT security management command. The allowed values for this variable are grant_option (default) or per_privilege. Note: This global variable can be set on a Managed Service cluster.

grant_option

regexp_format

Specifies the regular expression format to be used by regular expression functions that you call. Possible values are 'extended' and 'advanced'. When you set this variable, its value is propagated to all nodes. For more information on this variable, see the Regular Expression Commands topic. This variable can sync to all aggregators and all leaves.

extended

resource_governor_cpu_limit_mode

Determines whether the mode of control over the CPU for resource pools is HARD or SOFT. For more information on this variable, see CREATE RESOURCE POOL. This variable can sync to all aggregators and all leaves.

SOFT

resource_pool_statement_selector_function

The function to execute at runtime that selects the resource pool to use when the user runs a query. The resource_pool variable must be set to system_auto for resource_pool_statement_selector_function to be used. This variable can sync to all aggregators and all leaves.

resource_usage_model

Whether to train or enable the resource usage model for workload management. This variable can sync to all aggregators.

OFF

show_with_portability_comments

Adds conditional compatibility comments to outputs like SHOW CREATE TABLE to help with portability.

FALSE

sp_query_dynamic_param

Specifies whether different query plans are generated for CALL queries when arguments to the stored procedure are NULL or not NULL arguments. If enabled, queries that use variables inside stored procedures will use the same query plan. For more details, see the In-Depth Variable Definitions section below.

ON

sql_select_limit

The maximum number of rows returned by a SELECT query. If the LIMIT clause is specified in a SELECT query, the value in the LIMIT clause overrides sql_select_limit. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves.

2^64-1

subprocess_io_idle_timeout_ms

The maximum amount of time, in milliseconds, the engine waits for or retries a request before timing out and failing the backup when connecting to cloud providers. When you set this variable, its value is propagated to all nodes.

240000

Password Complexity Sync Variables

For information about how to configure a password complexity policy, see Configuring a Password Policy.

Name

Description

Default

password_min_length

The minimum number of characters required (0 to 100).

0

password_min_uppercase_chars

The minimum number of uppercase characters required (0 to 100).

0

password_min_lowercase_chars

The minimum number of lowercase characters required (0 to 100).

0

password_min_numeric_chars

The minimum number of numeric digit characters required (0 to 100).

0

password_min_special_chars

The minimum number of special (non-alphanumeric) characters required (0 to 100).

0

password_max_consec_sequential_chars

The maximum number of consecutive characters allowed (0 to 100). For example, if set to 3, passwords with a 4-letter sequence or longer (e.g. 1234 or abcd) are disallowed.

0

password_max_consec_repeat_chars

The maximum number of consecutive repeated characters allowed (0 to 100). For example, if set to 3, passwords with 4 or more consecutive repeated characters (e.g., aaaa or 1111) are disallowed.

0

password_expiration_seconds

The time in seconds before a password expires. The value 0 indicates that the password will never expire.

0

password_history_count

The number of previous passwords per user that SingleStore DB will store and disallow from reuse. The value 0 indicates that any previous password can be reused. The maximum is 10.

0

Pipelines Sync Variables

You cannot set a variable for a specific pipeline – each variable setting applies to all pipelines in the cluster.

Name

Description

Default

pipelines_batches_metadata_to_keep

The number of batch metadata entries to persist before they are overwritten by incoming batches. As data is extracted from a source, it’s written in batches to a destination table on a leaf node. Metadata about these batches is temporarily persisted in the master aggregator’s information_schema.PIPELINES_BATCHES table. As new batches are loaded into the database, the oldest batch metadata entries will be removed from the information_schema.PIPELINES_BATCHES table. See the PIPELINES_BATCHES section for more information about this metadata.

1000

pipelines_deskew_batch_partitions_threshold

For keyless sharded destination tables, if less than this fraction of batch partitions are active, reshuffle to avoid skew. When you set this variable, its value is propagated to all nodes. This variable can sync to all aggregators and all leaves.

0.75

pipelines_errors_retention_minutes

The amount of time, in minutes, that a pipeline error is stored on disk. These errors are surfaced in PIPELINES_ERRORS as long as they are stored on disk. This variable must be set to at least 0. This variable can sync to all aggregators and all leaves.

1440

pipelines_extractor_debug_logging

Specifies whether to enable extractor debugging for Kafka or HDFS pipelines. This variable currently does not apply to S3 pipelines.

OFF

pipelines_extractor_get_offsets_timeout_ms

The maximum time in milliseconds to wait for offset data to be returned from the data source before returning an error. Increase this value if you experience timeout errors, such as ERROR 1970 (HY000): Subprocess timed out. Use the value 0 to indicate no timeout. This variable can sync to all aggregators and all leaves.

35000

pipelines_extractor_idle_timeout_ms

The maximum time (in milliseconds) that a pipeline will wait for more data from an external source. Use the value 0 to indicate no timeout. This variable can sync to all aggregators and all leaves.

120000

pipelines_kafka_version

The Kafka version used for the Kafka extractor. While the default version is 0.8.2.2, newer versions can also be specified.

0.8.2.2

pipelines_max_concurrent

The maximum number of pipelines running concurrently.

50

pipelines_max_concurrent_batch_partitions

The maximum number of pipeline batch partitions running concurrently.

0

pipelines_max_errors_per_partition

Deprecated in MemSQL 6.7. The maximum number of error event rows per leaf node partition to persist before they are deleted. Once the specified number of rows in the information_schema.PIPELINES_ERRORS table is reached, the database will eventually remove the oldest rows from the table. The removal mechanism for older error data is based on heuristics. Old errors are guaranteed to exist up to the specified number, but they may not immediately be removed.

1000

pipelines_max_offsets_per_batch_partition

The maximum number of data source partition offsets to extract in a single batch transaction. If the data source’s partition contains fewer than the specified number of offsets, all of the partition’s offsets will be batched into the destination table. This variable can sync to all aggregators. This variable applies only to Kafka pipelines.

1000000

pipelines_max_retries_per_batch_partition

The number of retry attempts for writing batch partition data to the destination table. If pipelines_stop_on_error is set to OFF and the specified retry number is reached without success, the batch partition will be skipped and will not appear in the destination table. If a batch partition is skipped, data loss can occur. If pipelines_stop_on_error is set to ON and the specified retry number is reached without success, the pipeline will stop. No batch partition data will be skipped. This configuration variable applies to the entire batch transaction, which includes extraction from a data source, optional transformation, and loading of the data into the destination table. If the batch transaction fails at any point during extraction, transformation, or loading, it will be retried up to the specified number. This variable can sync to all aggregators.

4

pipelines_stderr_bufsize

The buffer size for standard error output in bytes. Error messages that exceed this size will be truncated when written to the information_schema.PIPELINES_ERRORS table. However, the complete standard error text can be viewed by using the BATCH_ID and querying the information_schema.PIPELINES_BATCHES table.

65535

pipelines_stop_on_error

Specifies whether or not each pipeline in the cluster should stop when an error occurs. If set to OFF, batches will be retried up to the number specified in the pipelines_max_retries_per_batch_partition variable. After all retries have failed, the batch will be skipped. When a batch is skipped, data loss can occur. If set to ON, the batch transaction that caused the error will be retried up to the number specified in the pipelines_max_retries_per_batch_partition variable. After all retries have failed, the pipeline will enter a Stopped state and must be manually started. This variable can sync to all aggregators.

ON

pipelines_stored_proc_exactly_once

If set to ON, run stored procedures from pipelines in a transaction. This variable can sync to all aggregators.

ON

Workload Management Sync Variables

Name

Description

Default

workload_management

Specifies whether to enable workload management for the cluster. If this variable is set to ON, the other workload management engine variables will affect the way a query is executed. If set to OFF, the feature is disabled and no queueing or system resource optimization will occur. This variable can sync to all aggregators.

ON

workload_management_enable_static_partitioning

When set to TRUE, this variable enables static partitioning for deterministic behavior by the Workload Manager, as it was prior to version 7.0. When set to FALSE, partitioning is dynamic, meaning that the Workload Manager allows aggregators to share leaf resources based on each aggregators need. This is an improvement over static partitioning, which could result in wasted resources due to uniform allocation across aggregators. This variable can sync to all aggregators.

FALSE

workload_management_expected_aggregators

The expected number of aggregators that will be used to run a high volume of client queries which require fully distributed execution. The default value is 0, which is equivalent to setting it to the total number of aggregators in the cluster. For version 7.0 and up, this variable should only be set to its default of 0, unless `workload_management_enable_static_partitioning is set to TRUE. This variable can sync to all aggregators.

0

workload_management_max_connections_per_leaf

The maximum number of connections to use per leaf node in the cluster. This variable can sync to all aggregators.

10000

workload_management_max_queue_depth

The maximum depth of the query queue, which is the maximum number of queries that can be queued. If this number is reached, additional queries will not execute, and a ER_TOO_MANY_QUEUED_QUERIES error will appear. This variable can sync to all aggregators.

100

workload_management_max_threads_per_leaf

The maximum number of threads to use per leaf. This number correlates with the max_connection_threads engine variable, and they should generally be set to the same value. This variable can sync to all aggregators.

8192

workload_management_memory_queuing

Whether to turn on queueing based on memory usage of queries. This variable can sync to all aggregators.

ON

workload_management_memory_queue_threshold

Percentage of memory a query can use before it will get queued. If an individual query is projected to use more than workload_management_memory_queue_threshold * (leaf_maximum_memory - leaf_current_table_memory) / workload_management_expected_aggregators, then it will be queued. This variable can sync to all aggregators.

0.01

workload_management_queue_time_warning_ratio

Specifies when a warning will appear based on the ratio of time spent by a query in the queue versus the actual execution time of the query. For example, if a query waits in the queue for one second and it takes four seconds to execute, the ratio is 1:4, or 0.25. Once the specified ratio is reached for a query, a ER_QUERY_QUEUED_WARNING warning will appear. This variable can sync to all aggregators.

0.5

workload_management_queue_timeout

The time duration in seconds after which a query times out and is removed from the queue without being executed. This variable can sync to all aggregators.

3600

Non-Sync Variables List

See the engine variables overview to learn how non-sync variables work.

All of the non-sync variables listed in the following table are global variables, except when noted as a session variable that can also be set globally in the Description column.

The variables listed in the following tables can be set to take effect on node startup and can be set to take effect while the node is running. Exceptions are noted in the fourth column.

Notice

Refer to the SingleStore DB Non-Sync Variables List for a complete list of non-sync engine variables, including those that can be set with the assistance of SingleStore Support.

Connection Management Variables

Name

Description

Default Setting

Exceptions to When Variable can be Set

connect_timeout

The number of seconds the node’s SingleStore DB process is waiting for a connection.

10

max_allowed_packet

Maximum allowed protocol packet size. This is a session variable that can also be set globally. Maximum value is 1GB.

104857600 bytes

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

max_connection_threads

The maximum number of kernel threads for processing queries. For more, see In-Depth Variable Definitions.

192

max_pooled_connections

The maximum number of stashed connections per leaf. For more information on this variable, see In-Depth Variable Definitions.

1024

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

Database Optimization Variables

Name

Description

Default Setting

Exceptions to When Variable can be Set

columnstore_ingest_management_queue_timeout

Timeout, in seconds, to queue a columnstore ingest query before returning an error.

360

enable_sort_on_load

Whether to merge the newly loaded data segments (for LOAD DATA, INSERT SELECT, and PIPELINE statements) into a single sorted run.

On

max_prepared_stmt_count

The maximum number of simultaneous prepared statements.

16382

query_parallelism

Maximum number of simultaneous running queries. This is a session variable that can also be set globally.

This variable has been deprecated and is no longer operational in SingleStore DB.

0

Logging Variables

Name

Description

Default Setting

Exceptions to When Variable can be Set

warn_level

Defines how SingleStore DB behaves when it encounters unsupported functionality. For more information, visit the Unsupported Feature List section of the Unsupported MySQL Features topic.

WARNINGS

Pipelines Variables

You cannot set a variable for a specific pipeline – each variable setting applies to all pipelines in the cluster.

Name

Description

Default Setting

Exceptions to When Variable can be Set

pipelines_stderr_bufsize

The buffer size for standard error output in bytes. Error messages that exceed this size will be truncated when written to the information_schema.PIPELINES_ERRORS table. However, the complete standard error text can be viewed by using the BATCH_ID and querying the information_schema.PIPELINES_BATCHES table.

65535

Other Variables

Name

Description

Default Setting

Exceptions to When Variable can be Set

autocommit

If ON, individual SQL statements are run in a single transaction which commits when the statement finishes. If OFF, individual SQL statements run in a multi-statement transaction by default, which must be ended explicitly with COMMIT or ROLLBACK.

ON

character_set_client

This variable exists for backwards compatibility with MySQL and is non-operational in SingleStore DB.

character_set_connection

This variable exists for backwards compatibility with MySQL and is non-operational in SingleStore DB.

character_set_database

This variable exists for backwards compatibility with MySQL and is non-operational in SingleStore DB.

character_set_filesystem

This variable exists for backwards compatibility with MySQL and is non-operational in SingleStore DB.

character_set_results

This variable exists for backwards compatibility with MySQL and is non-operational in SingleStore DB.

compile_only

If ON, SingleStore DB will compile, but not run, each query it receives. This is a session variable that can also be set globally.

OFF

You cannot set this variable while the node is offline.

identity

Contains the value of last_insert_id. This is a session variable that can also be set globally.

You cannot set this variable while the node is offline.

lc_messages

This variable exists for backwards compatibility with MySQL and is non-operational in SingleStore DB.

max_user_connections

This variable exists for backwards compatibility with MySQL and is non-operational in SingleStore DB.

show_query_parameters

If ON, query parameters will be visible in the output of SHOW PROCESSLIST and in the output of SELECT from INFORMATION_SCHEMA.PROCESSLIST. If it is OFF, parameters will be hidden.

ON

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

sql_quote_show_create

If ON, identifiers are quoted by the server for SHOW CREATE commands. This is a session variable that can also be set globally.

ON

table_name_case_sensitivity

Sets the case-sensitivity for database object names. If set to OFF, tables, views, table aliases, and user-defined table-valued functions (TVFs) are not case-sensitive. For details on case-sensitivity, refer to Database Object Case-Sensitivity. This variable can sync to all aggregators and all leaves.

ON

tx_isolation

Transaction isolation level. This is a session variable that can also be set globally.

READ-COMMITTED

In-Depth Variable Definitions

This section contains supplemental information about engine variables that require more understanding to configure properly. Ensure that you understand these details before modifying any engine variables listed in this section.

max_connection_threads

max_connection_threads is the maximum number of kernel-level threads the SingleStore DB node will use to handle connections (i.e. running queries - not including background threads). Each query takes exactly one thread on the aggregator, so the max_connection_threads setting on an aggregator is essentially a limit on the number of queries - including internal SingleStore DB queries - the aggregator will run simultaneously. When the limit is reached, further queries are queued until a thread becomes available.

The maximum value of max_connection_threads is 8192. The default for aggregators is 192, and the default for leaves is 8192. Since leaves are defaulted to the highest setting, there is typically no reason to change this variable for leaves.

If the max_connection_threads limit is reached on an aggregator, queries are queued until a thread becomes available, which can potentially cause unresponsiveness, latency spikes, and failures. On the other hand, in rarer cases, too many queries running simultaneously on some workloads (such as high volume concurrent writes on larger clusters) can exhaust cluster resources. Typically, if the max_connection_threads limit is reached on an aggregator, increasing the limit should solve the problem. If raising the limit causes further problems on your workload, you may need to explore other avenues to resolve the root cause.

max_pooled_connections

max_pooled_connections is the maximum number of connections cached between nodes. Every connection that is opened to run a query between nodes will be left open and reused until the limit set by max_pooled_connections is reached. If more connections are needed to run a workload, the connections will be opened/closed as needed as the query runs. This is why running SHOW PROCESSLIST on a leaf that has been running a workload will show both open and idle connections.

The default value is 1024, which is typically sufficient. Some heavy distributed join workloads may need more internode connections, in which case this variable can be changed.

sp_query_dynamic_param

Syntax

SET GLOBAL sp_query_dynamic_param = {ON | OFF};

Remarks

When set to ON, sp_query_dynamic_param changes the plan generation behavior as follows:

  • Calls to a stored procedure use the same plan, regardless of whether the arguments to the procedure are NULL or not NULL. For example, the following procedure calls use the same plan:

    CALL proc_test(1, NULL, 7);
    CALL proc_test("Adam", NULL, 4);
    CALL proc_test(NULL, "Sam", "Smith");
    

    The following procedure calls use different query plans, because of different number of arguments:

    CALL proc_test(NULL);
    CALL proc_test(NULL, 7);
    
  • All occurrences of a query, inside a stored procedure, that uses variables will use the same plan, regardless of whether the variable values are NULL or not NULL. This applies when such a query appears in the stored procedure multiple times or when the query runs repeatedly via multiple calls to the stored procedure.

When sp_query_dynamic_param is enabled, compilation time and plancache size are reduced.

Notice

The OFF setting of sp_query_dynamic_param disables the plan generation behavior described above. This setting will no longer be supported around May 2021. You should remove any dependencies on the setting by this time.

Example: Changes in Type Conversion Behavior

A query returns different results for cases where an invalid conversion is being done, depending on the value of sp_query_dynamic_param. For example, in the query below, 9006060 is an invalid value for TIME datatype.

If sp_query_dynamic_param is disabled,

DELIMITER //

CREATE OR REPLACE PROCEDURE sp_ex (t TIME) AS
BEGIN
  ECHO SELECT t AS "t";
END; //

DELIMITER ;

CALL sp_ex(9006060);
****
+-----------+
| t         |
+-----------+
| 838:59:59 |
+-----------+

If sp_query_dynamic_param is enabled,

DELIMITER //

CREATE OR REPLACE PROCEDURE sp_ex (t TIME) AS
BEGIN
  ECHO SELECT t AS "t";
END; //

DELIMITER ;

CALL sp_ex(9006060);
****
+----------+
| t        |
+----------+
| 00:00:00 |
+----------+

json_extract_string_collation

json_extract_string_collation allows you to control the collation of JSON_EXTRACT_STRING output. It takes the following values:

  • json: Specifies that the result of JSON_EXTRACT_STRING will use the standard collation used for JSON (binary), which is utf8_bin.

  • server: Specifies that the collation of JSON_EXTRACT_STRING result will be the same as collation of the server defined by the collation_server variable. Generally, this value is utf8_general_ci.

  • auto: Interpreted as the server setting for json_extract_string_collation.