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.
Name | Description | Default |
|---|---|---|
| Deprecated in MemSQL 6.0, this is the timeout used when gathering workload management statistics from leaves. This variable can sync to all aggregators. | ON |
| Specifies if the aggregator will try to attach a node automatically after it has been marked offline, but starts responding to heartbeats again. This variable can sync to all aggregators. | ON |
| Determines the mode of auto profiling. There are two values: |
|
| Number of seconds to wait after a new node has attached to the cluster before running a rebalance. This mechanism is used to batch up rebalancing if many nodes fail and then come back online within a short period of time. This variable can sync to all aggregators. | 120 |
| You can use this variable to control the supported key size. It takes a value in aes-keylen-mode format, where keylen is the key length in bits and mode is the encryption mode. The value is not case-sensitive. Permitted keylen values are 128 and 256. Permitted mode values are ECB, GCM and CBC. List of allowed values are: Optionally an encryption mode string in the same format can be provided to the AES_ENCRYPT and AES_DECRYPT functions. If not provided, the default value from this variable will be used. In addition to the key, the initialization vector can be provided to the encryption/decryption function in GCM and CBC encryption modes. | aes-128-ecb |
| How often in seconds background statistics will check for out of date statistics (rowstore only). This variable can sync to all aggregators. | 60 |
| Threshold as a fraction of table row count triggering the collection of autostatistics. This variable can sync to all aggregators. | 0.5 |
| Helps tune backup performance. This variable's config depends on the When When With an increased number of go routines the backup performance improves but it comes with an increased cost of CPU, memory and network throughput. | |
| Possible values are |
|
| Maximum frequency in seconds that change count metadata will be written. Will never occur more frequently than | 1200 |
| The character set of the node. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. For more information on this variable, see the In-Depth Variable Definitions section below. | utf8 |
| Specifies whether to use a heuristic to more accurately estimate date predicates that are larger than the histograms range. OFF/ON/AUTO AUTO is ON. | AUTO |
| Specifies the cluster name used by the backup. | |
| Sets the collation that is used on the node. When you set this variable, | utf8_general_ci |
| Sets the collation that is used on the node. When you set this variable, | utf8_general_ci |
| Sets the collation that is used on the node. When you set this variable, | utf8_general_ci |
| At this threshold (fraction of | 0.5 |
| 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 |
| Amount of time (in seconds) the background thread waits before trying to flush on a table with data less than | 125 |
| Sets the threshold at which multiple inserts to a columnstore table with unique keys will switch from row value lock to table lock. The value's metric is the number of rows for each partition in a database. This variable can sync to all aggregators and leaves. | 1000000 rows per partition |
| 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 |
| Maximum file size (in bytes) that two or more column blobs can be combined into. | 5242880 |
| Verifies the checksum of a blob before merging it. For more information, see CHECK BLOB CHECKSUM and Columnstore. | OFF |
| Sets a limit in megabytes for the memory amount used for query object code images. | 4096 |
| If | OFF |
| 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 '8.0', '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. | '8.0' |
| wait_die | |
| Sets the default type of cardinality autostats for newly created columnstore tables. Values can be | INCREMENTAL |
| Sets the default state for autostats sampling for newly created columnstore tables. This variable can sync to all aggregators. | ON |
| Whether autostats is enabled by default on newly created columnstore tables. This variable can sync to all aggregators. | ON |
| Sets the default type of automatic histograms on newly created tables. This variable can sync to all aggregators. Values can be either | CREATE |
| Sets the default type of cardinality autostats for newly created rowstore tables. Values can be | PERIODIC |
| Sets a threshold for the number of rows that are locked before a table lock is acquired when updating or deleting rows in a columnstore table. When set to 0, the threshold of 5000 rows is used. This variable can sync to all aggregators and all leaves. It is a session variable that can be set globally. | 0 |
| The time, in milliseconds, to wait for a distributed DDL transaction to commit. This value sets the timeout for both | 180000 |
| 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 |
| A global variable used for MySQL compatibility. | |
| The default table type to use when creating a new table. The value can be set to |
When you upgrade a cluster, the setting of the variable does not change. |
| Controls push down logic of pushing down reference table clauses to the leaves. When set to | ON |
| Prevents the removal of | AUTO |
| Whether merging subqueries are disabled if they contain | AUTO |
| OFF | |
| 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) |
| Deprecated in MemSQL 6.5. Replaced by | |
| As of MemSQL 6.0, this is the timeout used when gathering workload management statistics from leaves. Before MemSQL 6.0 this was the timeout of the heartbeat query used to trigger failovers. This variable can sync to all aggregators. | 10 seconds |
| This variable is depreciated, but still exists for backward compatibility purposes. As of version 7.3 of SingleStore, it is replaced by two new variables that assist in leveraging significantly increased speed for optimization at large join sizes: | 22 |
| The minimum number of tables that must be present in a join for initial heuristics to be run before join optimization. This means a small cost is incurred for the initial use of these heuristics in exchange for faster join optimization. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. | 16 |
| Enabling this variable will result in a return to pre 7.3 behavior through use of heuristics to complete a faster, restricted search for a join plan, once the number of tables in a given join are greater than the threshold set for this with | FALSE |
| The maximum number of tables present in a join that will result in an unrestricted search being performed to find the optimal query plan. A query with a greater number of tables will use heuristics to complete search for a query plan. This restricted search is faster than previous versions of Singlestore. However, this may be at the cost of a less optimal query plan compared to an unrestricted search. | 22 |
| If set If set to Please note, a profiled query will have a different query plan than a non-profiled query. When a query is first profiled or | OFF |
| 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 |
| If set to | ON |
| When this variable is set to | ON |
| When this variable is set to | TRUE |
| Determines whether to use LRU (least recently used) eviction of query object code images. | ON |
| Enable removing of stale on disk plans from the plancache directory based on the value of | TRUE |
| 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 |
| See the Enabling External Functions section in Enabling External Functions. | OFF |
| Enables the ability for histograms to estimate predicates using stored procedure parameters or implicit/explicit typecasted literals. This variable needs to be manually enabled by setting it to ON or 1. | OFF |
| Allows the on-disk IR cache to be enabled or disabled. | ON |
| When this variable is set to | ON |
| Enables the forwarding of DDL queries from a child aggregator to the master. | ON |
| Enables spilling onto disk for HashGroupBy, hash join, order by, and window function operations during query processing. When
The sync variables This variable can sync to all aggregators and all leaves. | OFF (in versions prior to 7.8) ON (starting in version 7.8) |
| Enables tracing in backup subprocesses. | OFF |
| Uses heuristic or histogram to estimate missing sampling data instead of returning zero rows. False enables new estimating behavior. | False |
| Specifies how SingleStoreDB estimates the equality predicate for uncorrelated scalar subselects. When set to "true", the engine estimates scalar subselects as non-selective. When set to false, the engine estimates the equality predicate for uncorrelated scalar subselects as 1/cardinality, and use heuristics for all other scalar subselect predicates. | false |
| Specifies the maximum number of characters to be used by expressions when outputting the | 500 |
| Specifies whether the server disables certain nonstandard behaviors for default values and NULL-value handling in | ON |
| Provides the path to the ssl certificate. | |
| Used by the monitoring process when a user selects what entity to trust for the custom private key. | |
| Used when the | |
| Adds ability to use HTTPS. Default is FALSE, set to TRUE to enable. This variable is used in conjunction with engine variables | FALSE |
| See the Enabling External Functions section in Enabling External Functions. | |
| Assists with controlling the batch size while making external function calls. | 512 rows |
| If a node moves offline a second time after just failing and then coming back online failover won’t be triggered again if it happens within grace_interval_seconds. This is avoid a cycle of a problem node failing and coming back online. This variable can sync to all aggregators. | 300 |
| Controls whether the cluster fails over if disk use falls below | True |
| 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 |
| Maximum number of fragments to return from highlight function. This variable can sync to all aggregators and all leaves. | 0 |
| 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 |
| This variable controls the assumed correlation between columns when using histograms for selectivity estimation. | 0.5 |
| Allow foreign keys that have no referential actions or RESTRICT or NO ACTION referential actions to be ignored in CREATE TABLE commands. | False |
| When this variable is set to | OFF |
| Specifies the maximum number of list values to consider during histogram estimation for a query with an | 10000 |
| Specifies the maximum number of nested common table expressions (CTEs). For more information, refer to the WITH topic. | 128 |
| Controls whether the | ON |
| Controls the collation setting for | auto/SERVER_V2 |
| Whether or not the master aggregator should detect leaf failures and trigger failovers. WARNING: Turning this variable | On |
| Specifies the placement of replica partitions in a cluster. It can be set to the following modes: |
|
| Hides license-related variables from | True |
| The amount of time, in minutes, that a | 1440 |
| If this variable is set to | ON |
| Maximum number of unparsed bytes read by | 1073741823 |
| Number of bytes read at a time by | 8192 |
| Number of bytes written at a time by | 8192 |
| Restricts access to the local filesystem. | False |
| 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 |
| Specifies the log file size (in bytes) for partitions. Can be set to a minimum of 1 MB (in bytes) to maximum of 1 TB (in bytes), in multiples of 1 MB. A MB contains 1048576 bytes. Each partition is pre-allocated two log files. Each of these log files has size | 268435456 |
| Specifies the log file size (in bytes) for reference databases. Can be set to a minimum of 1 MB (in bytes) to maximum of 1 TB (in bytes), in multiples of 1 MB. A MB contains 1048576 bytes. Each reference database is pre-allocated two log files. Each of these log files has size | 67108864 |
| Specifies the amount of time online failover will wait to drain open transactions on a master partition (whether running or idle) before killing them. | 120 |
| Sets the maximum amount of space that can be used by the blob cache. It is a percent of the disk space and its value should be between 0 and 1. For example: It is set on the MA unlike | 0 |
| Maximum allowed protocol packet size. This is a session variable that can also be set globally. Maximum value is 1GB. | 104857600 bytes |
| The number of compiles that can run asynchronously at a given time on each node, when | Half the number of cores on the host machine. |
| 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 |
| 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 |
| If the number of interrupted connections from a host exceeds the value of | 10 |
| The maximum number of stashed connections per leaf. For more information on this variable, see In-Depth Variable Definitions. | 1024 |
| Maximum number of threads to use for prefetching per scan. | 1 |
| Preferred number of tuples in multi-inserts that aggregators send to leaves. This variable can sync to all aggregators and all leaves. | 20000 |
| The time, in seconds, that is allowed for a multi-statement transaction to remain idle while holding locks. This is a session variable that can also be set globally. This variable can sync to all aggregators. | 300 |
| Controls the number of background merger threads to start for each node. The default value of 2 implies two threads run on each node and the work is bound to 2 CPUs. On large nodes, with many CPUs, it is easier for nodes to outpace two merger threads. When there is high-throughput ingest workload, the merger cannot keep up and query processing gets slower as the data is not well merged. Use the The allowed values range between 2 and 64. | 2 |
| A statement count threshold for a procedure or function. When the threshold is exceeded, the procedure or function compiles faster, but the highest level of code optimizations is not applied; however, SQL query optimizations are still in effect. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. | 50 |
| Controls the beam search width. The default value is | 10 |
| Increased the default value to reduce the chance of Cartesian Joins being included when there are incorrect estimations. | |
| Setting this to Disabling this rewrite may cause queries to fail if they require the rewrite to run. | OFF |
| Sets a threshold for the number of empty tables that must be present in a query before the optimizer falls back to being rule based, instead of cost based. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. | 0 |
| Applies to a computed column when matching | true |
| Allows the optimizer to rewrite leaf order by limit queries using a self join. Defaults to true (enabled). | true |
| The maximum number of scalar constants a single query can contain. Any query with more than | 1048576 |
| 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 |
| A global variable that preserves | AUTO (same as ON) |
| A global variable that controls whether privilege transferability is determined by |
|
| The maximum number of bytes available to the | 2048 |
| The fraction of available cores that will be used on each leaf for a single query. The available values are any decimal from 0 to 1. If this value is set a 0, flexible parallelism is turned off. If this value is set at 1, all of the available core's processing power will work on a single query. | 1.0 |
| If set to 1, there is no redundancy across leaves. If set to 2, turns on SingleStoreDB’s High Availability mode. When you set this variable, its value is propagated to other aggregators only. For more information on this variable, see the Managing High Availability topic. This variable can sync to all aggregators and all leaves. | 1 |
| If | OFF |
| Specifies the heap allocation limit of the regexp compiler. Can be manually adjusted by the user. | 2MB |
| Specifies the regular expression format to be used by regular expression functions that you call. Possible values are | “extended” |
| Determines whether the mode of control over the |
|
| The function to execute at runtime that selects the resource pool to use when the user runs a query. The For more information on this variable, see the Setting Resource Limits topic. | |
| Whether to train or enable the resource usage model for workload management. This variable can sync to all aggregators. | OFF |
| A JSON array of strings with engine variable names. Each engine variable listed indicates which variable(s) the user can set if the user has | " " |
| If | ON |
| Adds conditional compatibility comments to outputs like | FALSE |
| The maximum number of tables present in a join which will utilize the cost-based singlebox join optimizer. For joins with more than this many tables, singlebox join optimization will use an alternate, rule-based algorithm which is faster but less effective. The singlebox join optimizer is responsible for making decisions about the local aspects of how to efficiently execute a join, such as choosing an index. This variable must be set between 0 and 30 tables. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. | 18 |
| Controls when segment elimination will not use an IN list that is too large. | 1000 elements |
| The aggregate size of transaction logs (in bytes) which, when reached, will trigger a new snapshot. This variable can sync to all aggregators and all leaves. | 2147483648 |
| Specifies how much time in seconds to wait between snapshots before taking a snapshot for blob garbage collection from previous snapshots. | 180 |
| Number of snapshot and log files to keep for backup and replication. This variable may be set live. | 2 |
| Specifies whether different query plans are generated for | ON |
| Memory threshold (ratio of total node memory, 0 - 1) to start spilling. | .75 |
| Memory threshold (bytes) for a query operator to be considered for spilling. | 100000000 |
| Specifies the maximum percentage of disk space to be used by spilling during query execution. The value must be between 0 and 1. The default is recommended in general. Under default setting, the amount of blob cache space that spilling can steal is 30% of This variable can sync to all aggregators and all leaves. For more information refer Advanced Disk Spilling Configuration | -1 |
| Specifies the SQL mode or modes that affect the SQL syntax SingleStoreDB supports and the query validation checks it performs. This is a session variable that can also be set globally. See the sql_mode section below for more information. | STRICT_ALL_TABLES |
| If | ON |
| The maximum number of rows returned by a | 2^64-1 |
| The number of sub-partitions per physical partition that a new database will be created with. It must be set at 0 or a power of 2 value of the physical partition count. The available values are 2, 4, 8, 16, 32, and 64. | 4 |
| This variable is the setting for the MaxRetries parameter for S3 and GCS. Connectivity issues that are retry-able will automatically try to connect the default number of times. | 10 |
| The maximum amount of time, in milliseconds, the engine waits for or retries a request before timing out to return metadata used to verify the cluster is on ec2 from which implicit credentials can be obtained. | 1000 |
| 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 |
| This session variable fine-tunes the behavior of the rewrite If the variable is set to ON: always merge the sub-select with outer join(s). If the variable is set to HEURISTIC: check to see if merging the sub-select with outer join(s) will cause negative performance for the overall query. If the variable is set to OFF: don’t merge sub-selects with outer join(s). If the variable is set to AUTO (this is the default): For SingleStore 7.5 and previous versions, AUTO will behave like ON. For SingleStore versions after 7.5, AUTO will behave like HEURISTIC. | AUTO |
| This aggregator will synchronize its permissions with other aggregators. When ON, this variable also enables DDL forwarding. See Synchronizing Permissions Across Your Cluster for more information about utilizing this variable. This variable can sync to all aggregators. | ON |
| (Deprecated) Maximum amount of time in milliseconds for the master to wait for acknowledgement from the synchronous replica. This variable can sync to all aggregators and all leaves. | 10000 |
| Used to enable seekable encoding for JSON. See Columnstore Seekablity for more information about utilizing this variable. | ON |
| For a column defined as type CHAR of length len, store the column as a VARCHAR of length len if len greater than or equal to the value of this variable. If the value of this variable is 0, the column is not stored as a VARCHAR. | 0 |
| Limits the application of join predicate transitivity to predicates that may be estimated. To use, set this variable to ON. | OFF |
| 250 milliseconds |
Password Complexity Sync Variables
For information about how to configure a password complexity policy, see Configuring a Password Policy.
Name | Description | Default |
|---|---|---|
| The minimum number of characters required ( |
|
| The minimum number of uppercase characters required ( |
|
| The minimum number of lowercase characters required ( |
|
| The minimum number of numeric digit characters required ( |
|
| The minimum number of special (non-alphanumeric) characters required ( |
|
| The maximum number of consecutive characters allowed ( |
|
| The maximum number of consecutive repeated characters allowed ( |
|
| Specifies whether the root password can expire. If set to | OFF |
| The time in seconds before a password expires. The value | 0 |
| The number of previous passwords per user that SingleStoreDB will store and disallow from reuse. The value | 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 |
|---|---|---|
| Specifies whether to enable security features for HDFS pipelines. This variable can sync to all aggregators and all leaves. |
|
| The maximum amount of disk space, in MB, that is used to log errors for pipelines. |
|
| Heap size in megabytes for HDFS pipelines. This variable can sync to all aggregators and all leaves. |
|
| 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 |
|
| 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. |
|
| 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 |
|
| Specifies whether to enable extractor debugging for Kafka or HDFS pipelines. This variable currently does not apply to S3 pipelines. |
|
| 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 |
|
| The maximum time (in milliseconds) that a pipeline will wait for more data from an external source. Use the value |
|
| The Kafka version used for the Kafka extractor. While the default version is |
|
| The maximum number of pipelines running concurrently. See Load Data with Pipelines page for more information. |
|
| The maximum number of pipeline batch partitions running concurrently. |
|
| 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 |
|
| 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. |
|
| The number of retry attempts for writing batch partition data to the destination table. If |
|
| The buffer size for standard error output in bytes. Error messages that exceed this size will be truncated when written to the |
|
| Specifies whether or not each pipeline in the cluster should stop when an error occurs. If set to |
|
| If set to |
|
Unlimited Storage Database (Bottomless) and Point-in-Time Restore Variables
Name | Description | Default |
|---|---|---|
| Specifies the amount of time, in minutes, a restore point for a remote storage database is retained after it is created. This determines how far back in time you can do a PITR. For example, to configure retention for 90 days, you should set the value to 90*24*60 = 129600 minutes. | 1440 |
| Determines how often snapshot files are uploaded to the object store. A higher value will cause snapshot files to be uploaded less frequently. Snapshot files are used during recovery. For example, copying bucket files in an object store to a new location and then running | 300 |
| A higher value can increase the concurrency of uploading data updates made on the cluster to the object store. A higher value will increase the CPU and memory usage, and potentially the network usage on the cluster. | 8 |
Workload Management Sync Variables
Name | Description | Default |
|---|---|---|
| Whether to train or enable the resource usage model for workload management. When set to | OFF |
| Specifies whether to enable workload management for the cluster. If this variable is set to | ON |
| 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 ` | 0 |
| The maximum number of connections to use per leaf node in the cluster. This variable can sync to all aggregators. | 10000 |
| 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 | 100 |
| The maximum number of threads to use per leaf. This number correlates with the | 8192 |
| Whether to turn on queueing based on memory usage of queries. This variable can sync to all aggregators. | ON |
| Percentage of memory a query can use before it will get queued. If an individual query is projected to use more than | 0.01 |
| 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 | 0.5 |
| 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 |
| Enables/disables the dynamic reuse of WM queues feature | off |
| Specifies how many large queries can be waiting for execution in the Large Queue when we want to move to that queue a medium query from the Medium Queue. | 1 |
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.
Basic Variables
Name | Description | Default Setting | Exceptions to When Variable can be Set |
|---|---|---|---|
| Whether to enable IPv6 support. When set to false, the SingleStoreDB engine can only use an IPv4 network. When set to true, SingleStoreDB can use either an IPv4 or IPv6 network. The actual network interface to use is determined by the | false | 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. Note: If |
| Defines the IP address to use to listen for incoming connections. Used in conjunction with the When When If the address is 0.0.0.0(IPv4) or :: (IPv6), SingleStoreDB accepts connections on all network interfaces, otherwise it only accepts connections for the given IP address. | 0.0.0.0 | 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. |
| The number of digits by which to increase the scale of division results performed with the division (/) operator. | 4 | You can set this variable but it is currently not supported in SingleStoreDB. SingleStoreDB always returns the results to the fourth precision value. |
| If | OFF | 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. |
| Appending | 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. | |
| Maximum memory SingleStoreDB will use, in MB. WARNING: Do not set to more than the default. | 90% of System RAM or System RAM minus 10 GB, whichever is greater | You are required to restart a node only when the variable is being reduced. |
| Maximum memory SingleStoreDB will use for table storage, in MB. WARNING: Do not set to more than the default. | If | You are required to restart a node only when the variable is being reduced. |
| Specifies the maximum blob cache size per leaf node. The cache stores recently accessed columnstore data objects and is located on the cluster's local storage. The cache exists in
| 80% of disk space in versions prior to 8.0. In versions 8.0 and higher, it is based on disk size: 75% for disks smaller than 666GB. For larger disks, it is: | |
| SingleStoreDB port number. | 3306 | 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. |
| The hostname or IP of the machine in use. | ||
| Directory path for the data directory. This directory contains snapshots, logs, and columnstore segments. | 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. | |
| Directory path for the plancache directory. This directory contains compiled plans used for codegen. |
| 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. |
| Directory path for the tracelogs directory. This directory contains log files, including memsql.log and the query log. |
| 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. |
Connection Management Variables
Name | Description | Default Setting | Exceptions to When Variable can be Set |
|---|---|---|---|
| The number of seconds the node’s SingleStoreDB waits for a connection packet before sending back a | 10 | |
| The number of simultaneous clients allowed. For more, see In-Depth Variable Definitions. | 100000 | |
| The maximum number of kernel threads for processing queries. For more, see In-Depth Variable Definitions. | 192 | |
| Specifies the number of connections reserved for admin users (users granted the SUPER permission). This is the number of connections over and above the You connect via a socket file, for example: Refer Dedicated Admin Connections for how to create dedicated admin connections. | 5 | |
| Controls whether to perform name resolution. By default, | AUTO | 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 |
|---|---|---|---|
| Deprecated alias to | 1024000 | |
| This variable has been deprecated and is no longer operational in SingleStoreDB. | ||
| Timeout, in seconds, to queue a columnstore ingest query before returning an error. | 360 | |
| Whether to merge the newly loaded data segments (for | On | |
| The maximum number of simultaneous prepared statements. | 16382 | |
| Number of seconds an online failover will wait to drain open idle write transactions before issuing a kill to those transactions. After the idle write transactions are killed, an application-level retry is required. | 30 | |
| Number of seconds to wait for more data from a connection before aborting the read. This is a session variable that can also be set globally. | 3600 | |
| Number of seconds to wait for a block to be written to a connection before aborting the write. This is a session variable that can also be set globally. | 3600 | |
| Controls the number of threads per leaf node for parallel columnstore scans. | 0 | |
| This variable has been deprecated and is no longer operational in SingleStoreDB. | ||
| 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 SingleStoreDB. | 0 | |
| Degree of parallelism for applicable query operators, specified as a ratio of the leaf core count. If set to 0, the number of database partitions is chosen as the parallelism. This is a session variable that can be set on Aggregators only. | 0 | |
| This variable has been deprecated and is no longer operational in SingleStoreDB. |
Logging Variables
Name | Description | Default Setting | Exceptions to When Variable can be Set |
|---|---|---|---|
| Turning | ON | |
| Sends usage and critical error diagnostics to SingleStoreDB. | ON | |
| If | OFF | |
| Log connections and queries to given file. |
| |
| Defines how SingleStoreDB 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 |
|---|---|---|---|
| Specifies the value that SingleStoreDB sets the node’s operating system environment variable | 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. | |
| Specifies the path that the HDFS extractor uses to run Java. | ||
| 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 |
| |
| Specifies whether to enable extractor debugging for Kafka or HDFS pipelines. This variable currently does not apply to S3 pipelines. |
| |
| The Kafka version used for the Kafka extractor. While the default version is |
| |
| The maximum number of pipelines running concurrently. |
| |
| The maximum number of pipeline batch partitions running concurrently. |
| |
| 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 |
| |
| The buffer size for standard error output in bytes. Error messages that exceed this size will be truncated when written to the |
|
Security Variables
Name | Description | Default Setting | Exceptions to When Variable can be Set |
|---|---|---|---|
| CA file to be used for SSL connections. | 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. | |
| CA directory to be used for SSL connections. | 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. | |
| Certificate file to be used for SSL connections. | 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. | |
| Cipher to be used for SSL/TLS connections. You must manually enumerate all desired cipher suites. For example: ssl_cipher = DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384 This will enable only the enumerated cipher suites in both the SingleStoreDB engine and websocket proxy. | 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. | |
| When this variable is set to | OFF | 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. |
| Public-private key pair file to be used for SSL connections. | 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. | |
| Passphrase for encrypted | 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. | |
| Contains the sequence number of reload attempts. | ||
| Contains the date/time of the last successful reload. | ||
| When this variable is set to | OFF | 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. |
| Controls the default value for If it is enabled and Once it is turned on, it impacts only the newly created users. It will not enforce SSL for existing users. | OFF |
Other Variables
Name | Description | Default Setting | Exceptions to When Variable can be Set |
|---|---|---|---|
| Enables support for filesystems without | false | |
|
| 1 second | |
| Specifies the local or network directory to write log files. For more information on this variable, see Configure Audit Logging. |
| 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. |
| Specifies if every audit log record is synchronously written and persisted to the disk. By default, it delays the audit log writes to the disk. For more information on this variable, see Configure Audit Logging. | OFF | 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. |
|
| OFF | 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. |
| Indicates the retention period (in days) for audit log files. | 0 (store files indefinitely) |
|
| Specifies the maximum size per log file in bytes. Required if logging is enabled. For more information on this variable, see Configure Audit Logging. | 134217728 | 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. |
| Specifies the maximum time duration to write to a single log file in seconds. For more information on this variable, see Configure Audit Logging. | 3600 | 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. |
| If | ON | |
| Specifies the maximum number of concurrent threads, per leaf, used to run a backup on each leaf. By default, a backup of a leaf uses one thread per partition and there is no restriction on the number of threads created (aside from system limitations). The number of threads, if set, is shared among active backups, with the first backup using as many as it needs. The remainder, if any, goes to the next backup, etc. | 0 | |
| Specifies the path where the installation directory can be found. | 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. | |
| Specifies the directory where character sets are stored. |
| 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. |
| This variable exists for backwards compatibility with MySQL. It specifies the MySQL compatibility version. | ||
| Sets the percentage of | ||
| Sets the memory limit (in MBs) that is available for caching query object code images. Refer to In-Depth Variable Definitions for more information. | ||
| When this variable is set as | TRUE | 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. |
| Specifies type of core dump to generate if SingleStoreDB terminates abnormally. Options include | PARTIAL | 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. |
| This is a configuration variable that is used to set the time zone on a host by specifying an offset from UTC time. Refer to Setting the Time Zone for details on this variable. | ||
| When set to ON, forwards the Data Manipulation Language (DML) queries from a child aggregator to the master aggregator. Currently, this setting is used for write requests to reference tables, which cannot be executed on child aggregators. | ON | |
| This variable has been deprecated and is no longer operational in SingleStoreDB version 5.0 or newer. | ||
| This variable is the maximum length string | 16777216 | 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. |
| The local path to the keytab file created on the KDC. For more information on this variable, see Kerberos Authentication. | 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. | |
| The SPN for SingleStoreDB that was created on the KDC. For more information on this variable, see Kerberos Authentication. | 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. | |
| The server host name specified by the server at startup. This variable is read-only. | ||
| Enables SingleStore's Data API endpoints on the built-in HTTP server when used in conjunction with the | OFF | |
| Controls how long a pooled connection can remain idle (or unused) before it will be closed. The default of zero means the connect will remain open. Please contact SingleStore Customer Support if you need to change this value. | 86400 | |
| Controls how long a pooled connection can remain open. The default of zero means the connect will remain open. Please contact SingleStore Customer Support if you need to change this value. | 0 | |
| Specifies the maximum number of connections each pool can have open at any point in time when using the SingleStore's Data API. In other words, this variable controls the number of parallel queries that can be run by a SingleStore user. | 2048 | |
| Specifies the maximum number of idle connections to retain in each connection pool when using the SingleStore's Data API. Each connection pool is associated with a single SingleStore user. | 1024 | |
| Specifies the port on which the WebSocket proxy should run. This WebSocket allows Studio to connect with the SingleStoreDB server over HTTP using TLS (Learn more). The Note: This variable is mutually exclusive with the | ||
| Specifies the port on which WebSocket proxy should run. This WebSocket allows Studio to connect with the SingleStoreDB server over HTTPS using TLS. It requires SSL to be enabled (Learn more). The Note: This variable is mutually exclusive with the | ||
| Idle threads terminate after the specified number of seconds. | 3600 | |
| Contains the value of | You cannot set this variable while the node is offline. | |
| If set to | OFF | |
| If set to | OFF | |
| Controls whether SingleStore compiles or interprets query plans. When compiling, this variable also controls how the query plans are compiled. Allowed settings include: | interpret_first | |
| Controls the maximum length of JSON_AGG output. It can be set to a minimum of 1 MB (in bytes). This is a session variable that can also be set globally. | 16777216 Bytes | |
| This variable has been deprecated and is no longer operational in SingleStoreDB. | ||
| Specifies the directory where error messages are stored. |
| 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. |
| This variable has been deprecated and is no longer operational in SingleStoreDB version 5.0 or newer. | ||
| Specifies the setting to materialize the common table expressions (CTEs). If set to For more information, see Materializing Common Table Expressions. | AUTO | |
| The ID of the SingleStoreDB node whose config you would like to update. This variable is read-only. | ||
| SingleStoreDB version number. This variable is read-only. | ||
| The build date of the SingleStoreDB version currently running. This variable is read-only. | — | |
| SingleStoreDB version hash. This variable is read-only. | ||
| Sets the minimal available disk space allowed (in MBs). If the available disk space falls below | 100 MB | |
| Specifies the size of the connection buffer and the result buffer with which each client thread starts. This is a session variable that can also be set globally. | 102400 bytes | |
| The path name of the process ID file. |
| 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. |
| This variable enables collection of additional data with | OFF | |
| Specifies the version of the client/server protocol. This variable is read-only. | 10 | |
| The number of threads pooled for replication management of reference databases. Must be between 1 and 100. | 8 | |
| The number of threads pooled for replication management of partition databases. Must be between 1 and 100. | 8 | |
| Specifies the resource pool to be used. This is a session variable that can also be set globally. It can be set while a node is running, but cannot be set on node startup. For more information on this variable, see Set Resource Limits. | default_pool | |
| This variable determines verbose debug tracing for rewrites. OFF: no output. ON: query before and after each rewrite; some extra tracing inside some rewrites. | OFF | |
| Specifies a single audience restriction for the SAML assertion. For more information on this variable, see Configuring SAML Global Variables. | 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. | |
| Specifies the intended recipient for a wrapped encryption key. For more information on this variable, see Configuring SAML Global Variables. | 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. | |
| Specifies the file path for the private key used to decrypt an encrypted assertion. For more information on this variable, see Configuring SAML Global Variables. | 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. | |
| Specifies if authentication should fail when both the SAML response and SAML assertion are unencrypted. For more information on this variable, see Configuring SAML Global Variables. | OFF | 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. |
| Specifies if authentication should fail when both the SAML response an SAML assertion are unsigned. For more information on this variable, see Configuring SAML Global Variables. | OFF | 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. |
| Specifies whether a username value should be extracted from the | OFF | 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. |
| Specifies the username attribute in a SAML assertion that should be used to determine if a user exists in the database. For more information on this variable, see Configuring SAML Global Variables. | 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. | |
| Specifies the file path for the identity provider’s public x509 signing certificate. For more information on this variable, see Configuring SAML Global Variables. | 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. | |
| Specifies the directory to which any import or export operations should be limited, or | 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. | |
| Specifies the Unix socket file to use for local connections. | memsql.sock | 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. |
| If the node's disk space (in MBs) is less than | 500 | |
| System time zone. This variable is read-only. | PST | |
| Sets the case-sensitivity for database object names. If set to | ON | |
| This variable has been deprecated and is no longer operational in SingleStoreDB version 5.0 or newer. | ||
| Specifies how many inactive connection threads should be cached. | 0 | |
| Determines how the server handles connection threads. A value of | one-thread-per-connection | 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. |
| Specifies the stack size for each thread. | 1048576 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. |
| The current time zone. By default, it is set to the same value as that of | SYSTEM | |
| Used to capture the original timestamp of the client. This is a session variable that can also be set globally. It can be set while a node is running, but cannot be set on node startup. | 1391112305 seconds | |
| Indicates the TLS version with which to configure the node. For more information, see Specifying the TLS Version | 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. | |
| MemSQL Ops writes temporary data to | 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. | |
| Transaction isolation level. This is a session variable that can also be set globally. | READ-COMMITTED | |
| This variable has been deprecated and is no longer operational in SingleStoreDB version 5.0 or newer. | ||
| The type of the server binary. This variable is read-only. | x86_64 | |
| The operating system on which SingleStoreDB was built. This variable is read-only. | Linux |
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.
compiled_images_eviction_memory_limit_percent and compiled_images_eviction_memory_limit_mb
These variables are used to specify the memory limit available for caching query object code images. If the memory limit is not set (or compiled_images_eviction_memory_limit_percent and compiled_images_eviction_memory_limit_mb are set to 0), SingleStoreDB allocates the compiled_images_eviction_memory_limit_percent value by default in respect to the maximum_memory as follows:
| Percentage of ( |
|---|---|
0-1 | Prohibited ( |
1-64 | 10% of |
64+ | 5% of |
The module cache limit is derived at runtime from the values of the compiled_images_eviction_memory_limit_mb and compiled_images_eviction_memory_limit_percent variables as follows (where _mb denotes compiled_images_eviction_memory_limit_mb and _percent denotes compiled_images_eviction_memory_limit_percent, respectively):
_percent | _mb | Effective Module Cache Limit |
|---|---|---|
0.0 | 0 | The value is derived in accordance with the default allocation rule specified in the table above. |
0.0 | > 0 | If _mb exceeds the |
> 0.0 | Any value | The value of _percent is used. |
If the compiled_images_eviction_memory_limit_percent variable is set to a value other than 0.0, the value of compiled_images_eviction_memory_limit_mb is ignored.
Note
Changes to maximum_memory may result in changes in the module cache limit, in accordance to the rules specified above.
max_connection_threads
max_connection_threads is the maximum number of kernel-level threads the SingleStoreDB 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 SingleStoreDB 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 12192. The default for aggregators is 192, and the default for leaves is 8192.
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.
max_connections
max_connections is the maximum number of connections that can be open to a SingleStoreDB node at one time. The default is 100,000, which is the maximum allowed. There is no reason to change the value of max_connections as lowering its value would not impact resource allocation.
node_degree_of_parallelism
Sets the per-node degree of parallelism for applicable query operators. If the variable is set to 0 the parallelism is chosen automatically (i.e. no change from default behavior).
When node_degree_of_parallelism is set to a value other than 0, say n, then n processor threads are started on each leaf node to handle all the partitions on that leaf.
interpreter_mode
Controls both whether and how SingleStoreDB compiles or interprets query plans. Allowed settings include the following modes:
llvmorcompile: Queries are compiled to machine code.compileis an alias forllvm.mbcorinterpret: Queries are interpreted and not compiled.interpretis an alias formbc.interpret_first: Queries start out as interpreted and dynamically switch to compiled during the first query execution. This mode can help improve ad-hoc query performance. Theinterpret_firstmode is turned on by default and can be used in production deployments.
This variable can also be set as a query option by adding OPTION (interpreter_mode = { interpret | compile | interpret_first}) at the end of the query. interpreter_mode is non-operational on the leaf node since it is forwarded from the aggregator to the leaf.
For more information on interpreter_mode and query compilation behavior, see Code Generation.
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
NULLor notNULL. 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
NULLor notNULL. 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.
Note
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 ofJSON_EXACT_STRINGwill use the collation of the JSON argument. If the collation of the argument is not set, the default (utf8_bin) is used.Since the old
AUTO/SERVERbehavior is error-prone, existing customers should set this engine variable to SERVER_V2 immediately after upgrading. See the support bulletin: Using the JSON utf8mb4 Column...server/auto: Specifies that the collation ofJSON_EXTRACT_STRINGresult will be the same as collation of the server defined by thecollation_servervariable. Generally, this value isutf8_general_ci.SERVER_V1: Functions the same as server/auto, in previous version older than 7.8.21 but without the parser lockdown.SERVER_V2: When thecollation_serverisutf8and the JSON object isutf8mb4, the output will useutf8mb4. This is the default beginning with version 7.8.21
skip_name_resolve
skip_name_resolve controls whether the server performs name resolution via DNS lookup upon login. The possible values for this variable are:
AUTO: By default,AUTOwill only perform a reverse DNS lookup if there are any host-based security rules.OFF: Always uses name resolution. This is not recommended without a reliable existing DNS.ON: Disables DNS and never uses name resolution. The server matches only IP addresses (not host names) to the list of grants. This can improve performance for users with a slow DNS and many hosts.
sql_mode
sql_mode specifies the current SQL mode (or modes) that the current session runs in. Currently, SingleStoreDB supports the following SQL modes:
ANSI_QUOTES: Changes"to be treated as the identifier quote character (like the ` quote character) and not as a string quote character. You can still use ` as an identifier quote character with this mode enabled. WithANSI_QUOTESenabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers.ANSI: Setssql_modetoSTRICT_ALL_TABLES,ONLY_FULL_GROUP_BY,PIPES_AS_CONCAT, andANSI_QUOTEStogether.NO_AUTO_CREATE_USER: Throws an error when using GRANT on non-existent users. Default behavior is to create a user instead.ONLY_FULL_GROUP_BY: Throws an error when fields in theSELECTlist,HAVINGcondition, orORDER BYlist are not in aGROUP BYclause, or are outside of an aggregate function. This is because the result set could include multiple possible values for the non-grouped, non-aggregated field.PIPES_AS_CONCAT: Changes||to be treated as string concatenation operation and not as an OR operation. You can still use built-in functionCONCATas a string concatenation with this mode enabled.STRICT_ALL_TABLES: Always enabled, regardless of other modes being enabled or not. If set to an empty value, behavior is the same as ifSTRICT_ALL_TABLESis set.
Example
The following examples show how to set sql_mode with one more or more values.
SELECT @@sql_mode; +-------------------+ | @@sql_mode | +-------------------+ | STRICT_ALL_TABLES | +-------------------+ 1 row in set (0.00 sec) -- Set sql_mode to include ONLY_FULL_GROUP_BY SET sql_mode = 'ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) SELECT @@sql_mode; +--------------------------------------+ | @@sql_mode | +--------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES | +--------------------------------------+ 1 row in set (0.01 sec) -- Set sql_mode to ANSI_QUOTES, which replaces ONLY_FULL_GROUP_BY SET sql_mode = 'ANSI_QUOTES'; Query OK, 0 rows affected (0.00 sec) SELECT @@sql_mode; +-------------------------------+ | @@sql_mode | +-------------------------------+ | ANSI_QUOTES,STRICT_ALL_TABLES | +-------------------------------+ 1 row in set (0.00 sec) -- Set ONLY_FULL_GROUP_BY and ANSI_QUOTES together SET sql_mode = 'ONLY_FULL_GROUP_BY,ANSI_QUOTES'; Query OK, 0 rows affected (0.01 sec) SELECT @@sql_mode; +--------------------------------------------------+ | @@sql_mode | +--------------------------------------------------+ | ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES | +--------------------------------------------------+ 1 row in set (0.01 sec)
ssl_key_passphrase
The passphrase specified with ssl_key_passphrase is used to decrypt the encrypted Privacy Enhanced Mail (PEM) or Password Protected (PASS) file. It can be added for encrypted SSL either at runtime or in memsql.cnf file and can used for both intra-cluster and inter-cluster configuration. The PEM or PASS file is decrypted once at boot time.