List of Engine Variables
On this page
The following tables list the sync engine variables and non-sync engine variables.
When reading or setting engine variables keep in mind that the values ON and 1 are synonymous, as are the values OFF and 0.
Sync Variables Lists
See the engine variables overview to learn how sync variables work.
Note
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.Description
column in the following table.
|
Name |
Description |
Default |
|---|---|---|
|
|
Shows a JSON array of users whose query runs are not considered active to measure the cluster's idle time. |
|
|
|
You can use this variable to control the supported key size. List of allowed values are: Optionally an encryption mode string in the same format can be provided to the AES_ This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Deprecated in MemSQL 6. |
|
|
|
Allows the query optimizer to materialize and reuse common table expressions without recomputing them when the query contains |
|
|
|
Allows modification of global variables even in the presence of computed shard keys. The behavior changing sysvars include:
|
|
|
|
Determines whether to allow errors while consuming '/proc/cpuinfo' to append CPU flags to the code generation's hash. |
|
|
|
controls behavior where SingleStore will do extra work to avoid issues caused by UDFs that return different values when repeatedly invoked (e. |
|
|
|
Specifies if every audit log record is synchronously written and persisted to the disk. |
|
|
|
Indicates the retention period (in days) for audit log files. |
|
|
|
Indicates the retention size (in megabytes) for audit log files. |
|
|
|
Specifies if the aggregator will try to attach a node automatically after it has been marked offline, but starts responding to heartbeats again. |
|
|
|
Determines the mode of auto profiling. |
|
|
|
Number of seconds a table's incremental autostats can go unused before being flushed to disk. |
|
|
|
Number of seconds to wait after a new node has attached to the cluster before running a rebalance. |
|
|
|
How often (in seconds) background statistics will check for out-of-date statistics (rowstore only). |
|
|
|
Threshold as a fraction of table row count triggering the collection of autostats. |
|
|
|
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. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Determines if external functions are batched. |
|
|
|
Deprecated. |
|
|
|
Possible values are This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
Maximum frequency (in seconds) that change count metadata will be written. |
|
|
|
The character set of the node. For more information, see Specifying Character Set and Collation for Clusters. |
|
|
|
Specifies whether to use a heuristic to more accurately estimate date predicates that are larger than the histograms range.
|
|
|
|
Sets the collation that is used on the node. For more information, see Specifying Character Set and Collation for Clusters. |
|
|
|
Sets the collation that is used on the node. For more information, see Specifying Character Set and Collation for Clusters. |
|
|
|
Sets the collation that is used on the node. For more information, see Specifying Character Set and Collation for Clusters. |
|
|
|
Sends |
|
|
|
At this threshold (fraction of |
|
|
|
Controls the rowstore-backed segment size for columnstore tables. |
|
|
|
Timeout (in seconds) to queue a columnstore ingest query before returning an error. |
|
|
|
Sets the threshold at which multiple inserts to a columnstore table with unique keys will switch from row value lock to table lock. |
|
|
|
Controls the maximum row count for a columnstore segment. |
|
|
|
Maximum file size (in bytes) that two or more column blobs can be combined into. |
|
|
|
Verifies the checksum of a blob before merging it. |
|
|
|
Sets a limit (in megabytes) for the memory amount used for query object code images. |
|
|
|
Sets the percentage of |
|
|
|
If |
|
|
|
Determines whether consensus can be configured or not. |
|
|
|
If set to |
|
|
|
Generates a core if a file IO API encounters |
|
|
|
Generates a core instead of throwing an error if a stack overflow occurs during query execution. |
|
|
|
Sets the maximum number of tables used in a non-critical query optimization estimate. |
|
|
|
Limits the size of some critical diagnostics sent to SingleStore. |
|
|
|
Specifies the level of data conversion behavior to use. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
See Row Locking. |
|
|
|
Sets the default type of cardinality autostats for newly created columnstore tables. |
|
|
|
Sets the default state for autostats sampling for newly created columnstore tables. |
l |
|
|
Whether autostats is enabled by default on newly created tables. |
|
|
|
Sets the default type of automatic histograms on newly created tables. |
|
|
|
Sets the default type of cardinality autostats for newly created rowstore tables. |
|
|
|
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. |
|
|
|
The time (in milliseconds) to wait for a distributed DDL transaction to commit. |
|
|
|
The default number of partitions a newly created database will be created with. |
|
|
|
A global variable used for MySQL compatibility. |
|
|
|
The default table type to use when creating a new table. |
When you upgrade a cluster, the setting of the variable does not change. |
|
|
Controls the default value for If it is enabled and Once it is turned on, it impacts only the newly created users. This variable can sync to all aggregators. |
|
|
|
Controls pushdown logic of pushing down reference table clauses to the leaves. |
|
|
|
Prevents the removal of |
|
|
|
Whether merging subqueries are disabled if they contain This is a session variable that can also be set globally. |
|
|
|
The interval in which a query plan must be read from disk (plancache directory) before it is removed. |
|
|
|
Controls how long (in seconds) an aggregator caches a distributed transaction outcome. |
|
|
|
Deprecated in MemSQL 6. |
|
|
|
As of MemSQL 6. |
|
|
|
At a high level, this variable makes the optimizer more (lower) or less (higher) likely to consider a broadcast when planning distributed joins. This is a session variable that can also be set globally. |
|
|
|
This variable is deprecated but still exists for backward compatibility purposes. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The minimum number of tables that must be present in a join for initial heuristics to be run before join optimization. This is a session variable that can also be set globally. |
|
|
|
Enabling this variable will result in a return to pre-7. This is a session variable that can also be set globally. |
|
|
|
The maximum number of tables present in a join that will result in an unrestricted search being performed to find the optimal query plan. This is a session variable that can also be set globally. |
|
|
|
Allows the user to set the minimum amount of time to wait for Disaster Recovery (DR) replication in milliseconds (ms). This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Trims leading spaces for column name aliases. |
|
|
|
If set If set to Please note, a profiled query will have a different query plan than a non-profiled query. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Controls whether the background statistics thread can invalidate plans automatically. |
|
|
|
If set to |
|
|
|
When this variable is set to |
|
|
|
When this variable is set to |
|
|
|
Enables queuing on columnstore ingest queries when the background flusher is behind. |
|
|
|
Determines whether to use LRU (least recently used) eviction of query object code images. |
|
|
|
Enable removal of stale on-disk plans from the plancache directory based on the value of |
|
|
|
Retains human-readable query plan summaries for compiled query plans. |
|
|
|
When set to |
|
|
|
See the Enabling External Functions section in CREATE [OR REPLACE] EXTERNAL FUNCTION. |
|
|
|
Enables the automatic creation of histograms based on |
|
|
|
Enables the ability for histograms to estimate predicates using stored procedure parameters or implicit/explicit typecasted literals. |
|
|
|
Enables Iceberg ingest. |
|
|
|
Used to reduce table memory overhead for idle tables on a cluster. To completely remove any overhead, use the non-sync variable |
|
|
|
Allows the on-disk IR cache to be enabled or disabled. |
|
|
|
When this variable is set to |
|
|
|
Enables query plan pinning. |
|
|
|
Enables the forwarding of DDL queries from a child aggregator to the master. |
|
|
|
Enables spilling onto disk for HashGroupBy, hash join, order by, and window function operations during query processing.
The sync variables This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Enables tracing in backup subprocesses. |
|
|
|
Allows the query optimizer to use stale statistics instead of querying for statistics during optimization. |
|
|
|
Enables compressing identical strings on |
|
|
|
Uses heuristic or histogram to estimate missing sampling data instead of returning zero rows. |
|
|
|
Specifies how SingleStore estimates the equality predicate for uncorrelated scalar subselects. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Number of cpu cores expected on each leaf. |
|
|
|
Specifies the maximum number of characters to be used by expressions when outputting the |
|
|
|
Specifies whether the server disables certain nonstandard behaviors for default values and NULL-value handling in |
|
|
|
Port number to use for prometheus exporter http endpoint. |
|
|
|
Path to user CA file to use for prometheus exporter connection. |
|
|
|
Path to user directory with CA files to use for prometheus exporter connection. |
|
|
|
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 the ability to use HTTPS. |
|
|
|
See the Enabling External Functions section in CREATE [OR REPLACE] EXTERNAL FUNCTION. |
|
|
|
Assists with controlling the batch size while making external function calls. |
|
|
|
Sets the maximum size (in mb) of the memory-mapped region used to communicate between the engine and collocated services. |
|
|
|
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 |
|
|
|
Controls whether the cluster fails over if disk use falls below |
|
|
|
Maximum number of tables in a join which SingleStore will still cost bushy joins. |
|
|
|
The radius of the sphere is used for distance calculation, in meters. |
|
|
|
This variable is the maximum length string This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Controls whether |
|
|
|
The size of a block of characters used to logically divide up string columns when using the full-text search highlight functionality. |
|
|
|
Maximum number of fragments to return from highlight function. |
|
|
|
This variable controls the assumed correlation between columns when using histograms for selectivity estimation. |
|
|
|
Allows the most commonly used |
|
|
|
When this variable is set to |
|
|
|
Specifies the maximum number of list values to consider during histogram estimation for a query with an This is a session variable that can also be set globally. |
|
|
|
Amount of time (in seconds) the background thread waits before trying to flush on a table with data less than |
|
|
|
Controls the approximate maximum uncompressed size of a columnstore blob in bytes. If a merge or batch write operation estimates that it would produce a segment with blobs larger than this size, the operation would lower the segment size to limit memory use for that operation. Caution It may take approximately up to 3 times the amount of memory specified in this variable to create a blob, for example during loading or columnstore merge process. Increasing this variable can allow segments with large sizes to be created, which can benefit full-text or vector search workloads, with the drawback of using more memory during merge and batch write operations. SingleStore recommends that in a memory-constrained environment, the variable should not be set to a large value. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Specifies the maximum number of nested common table expressions (CTEs). |
|
|
|
The endpoint to get JWKS updates from. |
|
|
|
When set, specifies the value that must be present in JWT |
|
|
|
The CA certificate file to be used for SSL JWKS endpoint host verification when HTTPS protocol is used. |
|
|
|
The JWKS update interval in seconds. |
|
|
|
When set, specifies which JWT field contains username overriding other rules. |
|
|
|
Whether or not the master aggregator should detect leaf failures and trigger failovers. |
|
|
|
Specifies the placement of replica partitions in a cluster. |
|
|
|
Hides license-related variables from |
|
|
|
The amount of time, in minutes, that a |
|
|
|
If this variable is set to |
|
|
|
Maximum number of unparsed bytes read by |
|
|
|
Number of bytes read at a time by |
|
|
|
Number of bytes written at a time by |
|
|
|
Restricts access to the local file system. |
|
|
|
Time (in seconds) to wait for a row lock before returning an error. |
|
|
|
Specifies the log file size (in bytes) for partitions. |
|
|
|
Specifies the log file size (in bytes) for reference databases. |
|
|
|
Specifies the amount of time online failover will wait to drain open transactions on a master partition (whether running or idle) before killing them. |
|
|
|
Maximum allowed protocol packet size (in bytes). |
|
|
|
The number of compiles that can run asynchronously at a given time on each node, when This variable can sync to all nodes, including aggregator and leaf nodes. |
Half the number of cores on the host machine. |
|
|
Sets the maximum number of merger threads that the autostats updater can run on. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The maximum amount of memory (in megabytes) used to compile a query. |
|
|
|
The maximum time allowed (in seconds) to compile a query. |
|
|
|
If the number of interrupted connections from a host exceeds the value of |
|
|
|
The maximum number of expressions within a query item. |
Maximum unsigned |
|
|
The maximum number of expressions within an entire query. |
Maximum unsigned |
|
|
The maximum number of stashed connections per leaf. |
|
|
|
Sets the maximum number of threads to use for pre-fetching per scan. |
|
|
|
Sets the maximum number of simultaneous prepared statements. |
|
|
|
Sets the maximum amount of memory (in bytes) required when creating or attaching a database. |
|
|
|
Sets the maximum amount of space that can be used by the blob cache. For example:
It is set on the MA unlike This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Displays the version of SingleStore initially installed. |
|
|
|
Indicates the size (in bytes) of the SingleStore database log upon which a new snapshot will be kicked off. |
|
|
|
The period in minutes to keep historical monitoring data. |
|
|
|
Preferred number of tuples in multi-inserts that aggregators send to leaves. |
|
|
|
Time (in seconds) that is allowed for a multi-statement transaction to remain idle while holding locks. |
|
|
|
Controls the number of threads per leaf node for parallel columnstore scans. |
|
|
|
Variable to write default |
|
|
|
Controls the number of background merger threads to start for each node. The allowed values range between 2 and 64. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Specifies the write timeout, in seconds, for the socket used by an This variable can sync to all aggregators. |
|
|
|
Specifies the write timeout, in seconds, for the socket used by an |
|
|
|
Enable or disable optimization for JSON computed columns. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
A statement count threshold for a procedure or function. |
|
|
|
Controls the beam search width. |
|
|
|
Increased the default value to reduce the chance of cartesian joins being included when there are incorrect estimations. |
|
|
|
Setting this to |
|
|
|
Disables predicate transitivity on query rewrites is set to |
|
|
|
Setting this variable to |
|
|
|
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. |
|
|
|
Applies to a computed column when matching |
|
|
|
Allows the optimizer to rewrite leaf |
|
|
|
This variable specifies the maximum row count for a derived table to be pushable to each branch of another union all table in the join list. |
|
|
|
The minimum total number of rows estimates in joined reference tables for gathering non-reference tables to be considered. |
|
|
|
The minimum number of reference tables joined in a query that will be considered before gathering non-reference tables. |
|
|
|
Enable or disable the not null filter derivation rewrite in the optimizer. This variable is settable with an Currently, This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Estimate row size in distributed join optimization based off sum of sizes in projection fields ( If this variable is set to This is a session variable that can also be set globally. |
|
|
|
The maximum number of scalar constants a single query can contain. |
|
|
|
The interval in which a query plan must be reused at least once or it gets unloaded from the query plan cache. |
|
|
|
A global variable that preserves This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
This variable can be used to temporarily disable updating privilege caches while performing security operations such as: granting/revoking permissions to/from users, granting/revoking permissions to/from roles, adding/deleting roles to/from groups, adding/deleting users to/from groups, role deletion, group deletion. IT IS IMPORTANT that the privilege cache updates be reenabled (set back to This variable can sync to all aggregators. To use this variable:
|
|
|
|
A global variable that controls whether privilege transferability is determined by This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The maximum number of bytes available to the |
|
|
|
Default timeout (in milliseconds) to wait for a |
|
|
|
This variable is deprecated. Maximum number of simultaneous running queries. |
|
|
|
The fraction of available cores that will be used on each leaf for a single query. |
|
|
|
Serializes query shape metadata to disk. |
|
|
|
Maximum size (in bytes) for each query shape in a serialization file. |
|
|
|
Stores actual parameter values to on-disk query shape metadata serialization. |
|
|
|
If |
|
|
|
If set to 1, there is no redundancy across leaves. |
|
|
|
Specifies the heap allocation limit of the regexp compiler (in megabytes). Can be manually adjusted by the user. |
|
|
|
Specifies the regular expression format to be used by regular expression functions that you call. |
|
|
|
This variable controls the output behavior of Possible values are This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Determines whether the mode of control over the |
|
|
|
The function that is executed at runtime that selects the resource pool to use when the user runs a query. For more information on this variable, see the Setting Resource Limits topic. |
|
|
|
Sets the lifetime (in seconds) of a result table error before garbage collection. |
|
|
|
Specifies the method used for sampling. This variable can sync to all nodes, including aggregator and leaf nodes. Reoptimize the most recently profiled query based on previous executions of it. |
|
|
|
Enables sending usage telemetry data (about how the system is configured and used and how it is performing). To enable, set the variable to one of the following (the type of cluster):
You will also need to allow outbound traffic to |
OFF |
|
|
Displays the service edition: |
|
|
|
A JSON array of strings, where each string is the name of an engine variable. |
|
|
|
If |
|
|
|
Adds conditional compatibility comments to outputs like |
|
|
|
Maximum number of tables present in a join which will utilize the cost-based singlebox join optimizer. This is a session variable that can also be set globally. |
|
|
|
Controls when segment elimination will not use an |
|
|
|
The aggregate size of transaction logs (in bytes) which, when reached, will trigger a new snapshot. |
|
|
|
Specifies how much time (in seconds) to wait between snapshots before taking a snapshot for blob garbage collection from previous snapshots. |
|
|
|
Number of snapshot and log files to keep for backup and replication. |
|
|
|
Specifies whether different query plans are generated for This variable can sync to all aggregators. |
|
|
|
Specifies the maximum percentage of disk space to be used by spilling during query execution. Under the default setting, spilling will first use available free disk space. The maximum amount of blob cache space that spilling can negotiate to use is 30% of This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Memory threshold (ratio of total node memory, 0 - 1) to start spilling. |
|
|
|
Memory threshold (bytes) for a query operator to be considered for spilling. |
|
|
|
Specifies the SQL mode or modes that affect the SQL syntax SingleStore supports and the query validation checks it performs. |
|
|
|
If |
|
|
|
The maximum number of rows returned by a |
|
|
|
The number of sub-partitions per physical partition that a new database will be created with. |
|
|
|
Makes subprocess errors easier to read. |
|
|
|
This engine variable is deprecated. |
|
|
|
The maximum number of retries for |
|
|
|
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. |
|
|
|
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. |
|
|
|
This variable is the setting for the |
|
|
|
This session variable fine-tunes the behavior of the rewrite If the variable is set to This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
This aggregator will synchronize its permissions with other aggregators. |
|
|
|
This engine variable is deprecated. |
|
|
|
Sets the case-sensitivity for database object names. |
|
|
|
Controls how far the system allows a child aggregator to lag behind replicating the transaction log of it's master aggregator before throttling is applied to the master aggregator. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The maximum size, in bytes, of the trace events ring buffer,
This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
Setting this value to Leaving this value set to |
|
|
Indicates the retention period (in days) for trace log files. |
|
|
|
Controls the retention, by total size in megabytes, of the rotated files of each component log separately. For example, the repl log component of the engine will write traces to the memsql_ |
|
|
|
Transaction isolation level. This engine variable is configurable on SingleStore Helios. |
|
|
|
Turns the Two-Phase Transaction Commit feature ON or OFF. |
|
|
|
Transaction isolation level. This engine variable has been deprecated. |
|
|
|
Determines whether |
|
|
|
Used to enable seekable encoding for JSON. See Columnstore Seekability using JSON for more information about utilizing this variable. |
|
|
|
Controls what will be displayed via the This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
This variable has been deprecated and is no longer operational in SingleStore version 5. |
|
|
|
For a column defined as type This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Enables logging to heartbeat thread in order to better diagnose process stalls. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Controls the format in which VECTOR-type columns and expressions are projected. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Limits the application of join predicate transitivity to predicates that may be estimated. |
|
|
|
Controls how much time (in milliseconds) a transaction waits on a rowlock before giving up when the decision is "die". This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Specifies the cluster name used by the backup. |
Full-Text Search Sync Variables
|
Name |
Description |
Default |
|---|---|---|
|
|
Collocated service control endpoint for full-text search version 2. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Collocated service request endpoint for full-text search version 2. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The amount of heap memory with which to start the Java process for the full-text search version 2 collocated service. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The maximum number of parallel requests that the full-text search version 2 collocated service can accept before queuing them. The default value is This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Sets the maximum size of the memory-mapped region used to communicate with the FTS service (in MB). This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The maximum amount of heap memory that the Java process for the full-text search version 2 collocated service may use. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
This is the number of seconds between monitor polls (0 disables it). This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Sets the positionIncrementGap for use when indexing multi-valued fields. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Sets the maximum number of seconds that a query will wait for a response from the full-text search version 2 service before returning a timeout error. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Sets the service's socket timeout (in milliseconds). This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
When the full-text search version 2 collocated service is shut down in a controlled manner, this sets the number of seconds to wait before it must be forcibly killed. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
JSON Sync Variables
|
Name |
Description |
Default |
|---|---|---|
|
|
Enables JSON statistics support if set to |
|
|
|
Controls the collation of JSON columns. Refer to Using JSON for information. The This variable can sync to all nodes, including aggregator and leaf nodes. |
When upgrading from SingleStoreversions 8. If |
|
|
Controls whether the |
|
|
|
The maximum number of children allowed during schema inference. |
|
|
|
Limits the number of JSON key paths inferred within a segment. |
|
|
|
The minimum average frequency to infer the schema for a json sub-object. |
|
|
|
The number of children after which the sparse condition is checked. |
|
|
|
The minimum absolute frequency to infer the schema for a json sub-object. |
|
|
|
The soft limit to how much data fits in a single internal page while compressing a json column. |
|
|
|
Controls the collation setting for the This is a session variable that can also be set globally. |
|
|
|
Used for parametrizing the JSON key path arguments for This variable can sync to all nodes, including aggregator and leaf nodes. For more details refer |
|
Password Complexity Sync Variables
For information about how to configure a password complexity policy, see Configuring a Password Policy.
|
Name |
Description |
Default |
|---|---|---|
|
|
The maximum number of consecutive repeated characters allowed ( |
|
|
|
The maximum number of consecutive characters allowed ( |
|
|
|
The minimum number of 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 minimum number of uppercase characters required ( |
|
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. |
|
|
|
Allows custom java pipelines. |
|
|
|
The maximum amount of disk space (in megabytes) that is used to log errors for pipelines. |
|
|
|
Implements an extractor interface used for experimental Java pipelines. |
|
|
|
Heap size (in megabytes) for HDFS and Iceberg pipelines. |
|
|
|
The amount of time in milliseconds that a pipeline waits before checking for new data from its data source. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The number of batch metadata entries to persist per partition, per pipeline before they are overwritten by incoming batches. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Specifies a forced delay in row emission while migrating/replicating your tables (or collections) to your SingleStore databases. Refer to Replicate Data from MongoDB® for more information. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Specifies the JVM heap size limit (in MBs) for CDC-in pipelines. Refer to Replicate Data from MongoDB® for more information. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Specifies the maximum number of CDC-in extractor instances that can run concurrently. |
|
|
|
Specifies the minimum duration (in seconds) that the extractor allocates to a single pipeline for ingesting data and listening to CDC events. |
|
|
|
For keyless sharded destination tables, if less than this fraction of batch partitions are active, reshuffle to avoid skew. |
|
|
|
The amount of time in minutes that a pipeline error is stored on disk. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Generates core dumps on pipeline extractor operations that timeout. |
|
|
|
Specifies whether to enable extractor debugging for Kafka or HDFS pipelines. |
|
|
|
The maximum time (in milliseconds) to wait for offset data to be returned from the data source before returning an error. This variable can sync to all aggregators. |
|
|
|
The maximum time (in milliseconds) that a pipeline will wait for more data from an external source. |
|
|
|
Maximum memory of a pipeline extractor (in megabytes). |
|
|
|
Max number of files that will be garbage collected in one iteration. |
|
|
|
The Kafka version used for the Kafka extractor. This variable is not applicable to current (>= 0. For legacy Kafka, |
|
|
|
The maximum number of pipelines running concurrently. |
|
|
|
The maximum number of pipeline batch partitions running concurrently. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Deprecated in MemSQL 6. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The maximum number of data source partition offsets to extract in a single batch transaction. This variable applies only to Kafka pipelines. This variable can sync to all aggregators. |
|
|
|
Max pooled extractors for Kafka pipelines. |
|
|
|
The number of retry attempts for writing batch partition data to the destination table. This variable can sync to all aggregators. |
|
|
|
Amount of time in minutes allowed for pipeline metadata timestamp skew. |
|
|
|
If the number of errors per batch partition exceeds the value, the pipeline will stop or skip the current batch and move to the next batch based on if the Set the variable with an integer value. |
0 |
|
|
Max number of batches for a pooled extractor for Kafka pipelines. |
|
|
|
The buffer size for standard error output (in bytes). This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Specifies whether or not each pipeline in the cluster should stop when an error occurs. This variable can sync to all aggregators. |
|
|
|
If set to This variable can sync to all aggregators. |
|
Unlimited Storage Database (Bottomless) and Point-in-Time Restore Variables
|
Name |
Description |
Default |
|---|---|---|
|
|
Controls the maximum number of threads available for downloading blobs from unlimited storage. |
|
|
|
Sets the timeout, in milliseconds, for CURL HTTP requests made by the unlimited storage cloud API (AWS/Azure). |
|
|
|
Turns on full debug tracing in an unlimited storage cloud API (AWS/Azure). |
|
|
|
When this is set to |
|
|
|
Specifies the amount of time, in minutes, a restore point for a remote storage database is retained after it is created. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Number of milliseconds elapsed between unlimited storage uploads. |
|
|
|
Maximum size (in bytes) an unlimited storage download's buffer can grow to. |
|
|
|
Skips API checks when creating a new bottomless database. |
|
|
|
Determines how often snapshot files are uploaded to the object store. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
A higher value can increase the concurrency of uploading data updates made on the cluster to the object store. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The usability (free space + evictable space) of blob cache below which all columnstore ingest is throttled. |
|
|
|
Amount of lag (in seconds) after which all columnstore ingest is throttled, subject to This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Minimum disk space when an unlimited storage columnstore ingest kicks in. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The usability (free space + evictable space) of blob cache below which some columnstore ingest is throttled. |
|
|
|
Amount of lag (in seconds) after which some columnstore ingest may be throttled, subject to This variable can sync to all nodes, including aggregator and leaf nodes. |
|
Wasm Sync Variables
|
Name |
Description |
Default |
|---|---|---|
|
|
Specifies if a user can create or call Wasm UDFs/TVFs. Note
|
|
|
|
Specifies the maximum memory (in bytes) that can be allocated to a Wasm module directly from the operating system in case of emergencies. |
|
|
|
Specifies the maximum number of times SingleStore retries memory allocation if an out-of-memory error occurs in the Wasm runtime. |
|
|
|
Specifies the time (in milliseconds) before SingleStore retries memory allocation if an out-of-memory error occurs in the Wasm runtime. |
|
|
|
Enables creation of Wasm functions from a remote URL. |
|
|
|
Specifies the maximum size (in bytes) that a compiled Wasm module can use. |
|
|
|
Specifies the maximum size (in bytes) that is available for caching Wasm query object code images. |
|
|
|
Specifies the maximum linear memory (in bytes) that an individual Wasm module can use. |
|
|
|
Specifies the maximum size (in bytes) of Wasm modules that may be loaded. |
|
Workload Management Sync Variables
|
Name |
Description |
Default |
|---|---|---|
|
|
Whether to train or enable the resource usage model for workload management. This variable can sync to all aggregators. |
|
|
|
Specifies whether to enable workload management for the cluster. This variable can sync to all aggregators. |
|
|
|
Sets the ratio of the available leaf memory for workload_ |
|
|
|
Enables/disables the dynamic reuse of WM queues feature. |
|
|
|
When enabled (set to When set to This variable can sync to all aggregators. |
|
|
|
Enables static resource partitioning for deterministic behavior by workload management. |
|
|
|
The expected number of aggregators that will be used to run a high volume of client queries which require fully distributed execution. This variable can sync to all aggregators. |
|
|
|
The maximum number of connections to use per leaf node in the cluster. |
|
|
|
The maximum depth of the query queue, which is the maximum number of queries that can be queued. This variable can sync to all aggregators. |
|
|
|
The maximum number of threads to use per leaf. |
|
|
|
Percentage of memory a query can use before it will get queued. This variable can sync to all aggregators. |
|
|
|
Whether to turn on queuing based on memory usage of queries. |
|
|
|
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. |
|
|
|
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. This variable can sync to all aggregators. |
|
|
|
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. |
|
Non-Sync Variables List
See the engine variables overview to learn how non-sync variables work.
Note
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.
Basic Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Whether to enable IPv6 support. When set to false, the SingleStore engine can only use an IPv4 network. When set to true, SingleStore can use either an IPv4 or IPv6 network. The actual network interface to use is determined by the |
|
You can only set this variable while the node is offline. 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. |
|
You can only set this variable while the node is offline. |
|
|
Directory path for the data directory. |
You can only set this variable while the node is offline. |
|
|
|
The number of digits by which to increase the scale of division results performed with the division (/) operator. |
|
You can set this variable but it is currently not supported in SingleStore. |
|
|
If |
|
You can only set this variable while the node is offline. |
|
|
Appending |
You can only set this variable while the node is offline. |
|
|
|
This variable is unused and setting this variable has no effect. |
|
|
|
|
Specifies the maximum blob cache size per leaf node.
|
Version <=7. For disk range 0-40GB, cache value is 100% of disk. For disk range 40GB+, cache value is 80% of disk.
Version =>8. For disk range 0-40GB, cache value is 100% of disk. For disk range 40-140GB, cache value is 40GB. For disk range 140-666GB, cache value is 75% of disk. For disk range 667GB+, cache value is 90% of disk - 100GB. |
|
|
|
Maximum memory SingleStore will use, in MB. |
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 SingleStore will use for table storage, in MB. |
If |
You are required to restart a node only when the variable is being reduced. |
|
|
SingleStore port number. |
|
You can only set this variable while the node is offline. |
|
|
The hostname or IP of the machine in use. |
||
|
|
Directory path for the plancache directory. |
|
You can only set this variable while the node is offline. |
|
|
Directory path for the tracelogs directory. |
|
You can only set this variable while the node is offline. |
Connection Management Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
The number of seconds the node’s SingleStore waits for a connection packet before sending back a |
|
|
|
|
The number of simultaneous clients allowed. |
|
|
|
|
The maximum number of kernel threads for processing queries. |
|
|
|
|
Specifies the number of connections reserved for admin users (users granted the You connect via a socket file, for example: Refer to the Dedicated Admin Connections page for how to create dedicated admin connections. |
|
|
|
|
This variable exists only for backward compatibility with MySQL. |
||
|
|
Sets the percentage of |
|
|
|
|
Controls whether to perform name resolution. |
|
You can only set this variable while the node is offline. |
Database Optimization Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Deprecated alias to |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStore. |
||
|
|
|
|
|
|
|
Whether to merge the newly loaded data segments (for |
|
|
|
|
Number of seconds an online failover will wait to drain open idle write transactions before issuing a kill to those transactions. |
|
|
|
|
Number of seconds to wait for more data from a connection before aborting the read. |
|
|
|
|
Number of seconds to wait for a block to be written to a connection before aborting the write. |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStore. |
||
|
|
This variable has been deprecated and is no longer operational in SingleStore. |
Full-Text Search Non-Sync Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
If set, this provides the JAVA_ |
None |
|
|
|
If set, this provides the path for the Java executable used by full-text search version 2. |
None |
Logging Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Turning |
|
|
|
|
Sends usage and critical error diagnostics to SingleStore. |
|
|
|
|
If set to |
|
|
|
|
Log connections and queries to a given file. |
|
|
|
|
Indicates the size threshold (in bytes) for tracelog file rotation. |
65536> |
|
|
|
Indicates the time threshold (in seconds) for tracelog file rotation. |
60> |
|
|
|
Defines how SingleStore behaves when it encounters unsupported functionality. |
|
Password Complexity Non-Sync Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Specifies whether the root password can expire. |
|
|
|
|
The time (in seconds) before a password expires. |
|
|
|
|
The number of previous passwords per user that SingleStore will store and disallow from reuse. |
|
Pipelines Non-Sync 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 SingleStore sets the node’s operating system environment variable |
You can only set this variable while the node is offline. |
|
|
|
Specifies the path that the HDFS extractor uses to run Java. |
||
|
|
Specifies the path of the (JRE 11+) For HDFS pipelines, use the |
Query Plan Pinning Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Writes a query plan pins when an un-planned pin is first optimized. |
|
|
|
|
This is used to enforce a maximum latency penalty that can be incurred when the server looks for a plan pin before proceeding with compiling a new plan. |
|
|
|
|
This is used to enforce a maximum latency penalty that can be incurred when the server writes a plan pin during compilation. |
|
|
|
|
Loads query plan pins if one is found. |
|
Security Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
When this variable is set to |
|
You can only set this variable while the node is offline. |
|
|
CA file to be used for SSL connections. |
You can only set this variable while the node is offline. |
|
|
|
CA directory to be used for SSL connections. |
You can only set this variable while the node is offline. |
|
|
|
Certificate file to be used for SSL connections. |
You can only set this variable while the node is offline. |
|
|
|
Cipher to be used for SSL/TLS connections. You must manually enumerate all desired cipher suites. For example: ssl_ This will enable only the enumerated cipher suites in both the SingleStore engine and websocket proxy. |
You can only set this variable while the node is offline. |
|
|
|
When this variable is set to |
|
You can only set this variable while the node is offline. |
|
|
Public-private key pair file to be used for SSL connections. |
You can only set this variable while the node is offline. |
|
|
|
Passphrase for encrypted |
You can only set this variable while the node is offline. |
|
|
|
Contains the sequence number of reload attempts. |
||
|
|
Contains the date/time of the last successful reload. |
Other Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Enables support for filesystems without |
|
|
|
|
|
|
|
|
|
Specifies the local or network directory to write log files. |
|
You can only set this variable while the node is offline. |
|
|
|
|
You can only set this variable while the node is offline. |
|
|
Specifies the maximum size per log file (in bytes). |
|
You can only set this variable while the node is offline. |
|
|
Specifies the maximum time duration to write to a single log file (in seconds). |
|
You can only set this variable while the node is offline. |
|
|
If Note: If |
|
|
|
|
Specifies the maximum number of concurrent threads, per leaf, used to run a backup on each leaf. The number of threads, if set, is shared among active backups, with the first backup using as many as it needs. |
|
|
|
|
Specifies the path where the installation directory can be found. |
You can only set this variable while the node is offline. |
|
|
|
This variable exists for backwards compatibility with MySQL. |
||
|
|
When this variable is set as |
|
You can only set this variable while the node is offline. |
|
|
Specifies the type of core dump to generate if SingleStore terminates abnormally. |
|
You can only set this variable while the node is offline. |
|
|
Port number to use for Dedicated Admin Connections (DAC). |
|
You can only set this variable while the node is offline. |
|
|
This is a configuration variable that is used to set the time zone on a host by specifying an offset from UTC time. |
||
|
|
If set then allows Dedicated Admin Connections (DAC) to be established at the port specified in |
|
You can only set this variable while the node is offline. |
|
|
Enables the handling of extended data types that are not defined in MySQL |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStore version 5. |
||
|
|
The local path to the keytab file created on the KDC. |
You can only set this variable while the node is offline. |
|
|
|
The SPN for SingleStore that was created on the KDC. |
You can only set this variable while the node is offline. |
|
|
|
Speficies the server hostname at startup. |
||
|
|
Enables SingleStore's Data API endpoints on the built-in HTTP server when used in conjunction with the |
|
|
|
|
Controls how long a pooled connection can remain idle (or unused) before it will be closed. Please contact SingleStore Customer Support if you need to change this value. |
|
|
|
|
Controls how long a pooled connection can remain open. Please contact SingleStore Customer Support if you need to change this value. |
|
|
|
|
Specifies the maximum number of connections each pool can have open at any point in time when using the SingleStore's Data API. |
|
|
|
|
Specifies the maximum number of idle connections to retain in each connection pool when using the SingleStore's Data API. |
|
|
|
|
Specifies the port on which the WebSocket proxy should run. The Note: This variable is mutually exclusive with the |
||
|
|
Specifies the port on which WebSocket proxy should run. The Note: This variable is mutually exclusive with the |
||
|
|
Idle threads terminate after the specified number of seconds. |
|
|
|
|
Contains the value of |
You cannot set this variable while the node is offline. |
|
|
|
If set to |
|
|
|
|
If set to |
|
|
|
|
Controls whether SingleStore compiles or interprets query plans. |
|
|
|
|
Controls the maximum length (in bytes) of JSON_ |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStore. |
||
|
|
Specifies the directory where error messages are stored. |
|
You can only set this variable while the node is offline. |
|
|
This variable has been deprecated and is no longer operational in SingleStore version 5. |
||
|
|
Specifies the setting to materialize the common table expressions (CTEs). For more information, see WITH (Common Table Expressions). |
|
|
|
|
The ID of the SingleStore node whose config you would like to update. |
||
|
|
SingleStore version number. |
||
|
|
The build date of the SingleStore version currently running. |
||
|
|
SingleStore version hash. |
||
|
|
Sets the minimal available disk space allowed (in megabytes). |
|
|
|
|
Specifies the size (in bytes) of the connection buffer and the result buffer with which each client thread starts. |
|
|
|
|
The path name of the process ID file. |
|
You can only set this variable while the node is offline. |
|
|
This variable enables the collection of additional data with |
|
|
|
|
Specifies the version of the client/server protocol. |
|
|
|
|
The number of threads pooled for replication management of reference databases. |
|
|
|
|
The number of threads pooled for replication management of partition databases. |
|
|
|
|
Specifies the resource pool to be used. |
|
|
|
|
This variable determines verbose debug tracing for rewrites. |
|
|
|
|
Number of threads running for rowstore garbage collection. |
|
|
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
You can only set this variable while the node is offline. |
|
|
Specifies if authentication should fail when both the SAML response and SAML assertion are unsigned. For more information on this variable, see Configuring SAML Global Variables. |
|
You can only set this variable while the node is offline. |
|
|
Specifies whether a username value should be extracted from the For more information on this variable, see Configuring SAML Global Variables. |
|
You can only set this variable while the node is offline. |
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
Specifies the Unix socket file to use for local connections. |
|
You can only set this variable while the node is offline. |
|
|
If the node's disk space (in MBs) is less than |
|
|
|
|
If |
|
|
|
|
System time zone. |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStore version 5. |
||
|
|
Specifies how many inactive connection threads should be cached. |
|
|
|
|
Determines how the server handles connection threads. |
|
You can only set this variable while the node is offline. |
|
|
Specifies the stack size (in bytes) for each thread. The value must be between 131072 and 134217728, and also evenly divisible by 1024. |
|
You can only set this variable while the node is offline. |
|
|
The current time zone. |
|
|
|
|
Used to capture the original timestamp of the client. |
|
|
|
|
Indicates the TLS version with which to configure the node. |
You can only set this variable while the node is offline. |
|
|
|
MemSQL Ops writes temporary data to |
You can only set this variable while the node is offline. |
|
|
|
The type of the server binary. |
|
|
|
|
The operating system on which SingleStore was built. |
|
|
|
|
Sends configuration to websocket_ |
|
|
In-Depth Variable Definitions
This section contains supplemental information about engine variables that require more understanding to configure properly.
max_
max_ is the maximum number of kernel-level threads the SingleStore node will use to handle connections (i.max_ setting on an aggregator is essentially a limit on the number of queries - including internal SingleStore queries - the aggregator will run simultaneously.
The maximum value of max_ is 12192.
If the max_ limit is reached on an aggregator, queries are queued until a thread becomes available, which can potentially cause unresponsiveness, latency spikes, and failures.max_ limit is reached on an aggregator, increasing the limit should solve the problem.
max_
max_ is the maximum number of connections cached between nodes.max_ is reached.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.
max_
max_ is the maximum number of connections that can be open to a SingleStore node at one time.max_ as lowering its value would not impact resource allocation.
node_
Sets the per-node degree of parallelism for applicable query operators.0 the parallelism is chosen automatically (i.
When node_ 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_
Controls both whether and how SingleStore compiles or interprets query plans.
-
llvmorcompile: Queries are compiled to machine code.compileis an alias forllvm. -
mbcorinterpret: Queries are interpreted and not compiled.interpretis an alias formbc. -
interpret_: Queries start out as interpreted and dynamically switch to compiled during the first query execution.first This mode can help improve ad-hoc query performance. The interpret_mode is turned on by default and can be used in production deployments.first
This variable can also be set as a query option by adding OPTION (interpreter_ at the end of the query.interpreter_ is non-operational on the leaf node since it is forwarded from the aggregator to the leaf.
For more information on interpreter_ and query compilation behavior, see Code Generation.
sp_
Syntax
SET GLOBAL sp_query_dynamic_param = {ON | OFF};
Remarks
When set to ON, sp_ 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_ is enabled, compilation time and plancache size are reduced.
Note
The OFF setting of sp_ disables the plan generation behavior described above.
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_.TIME data type.
If sp_ is disabled,
DELIMITER //CREATE OR REPLACE PROCEDURE sp_ex (t TIME) ASBEGINECHO SELECT t AS "t";END; //DELIMITER ;CALL sp_ex(9006060);
+-----------+
| t |
+-----------+
| 838:59:59 |
+-----------+If sp_ is enabled,
DELIMITER //CREATE OR REPLACE PROCEDURE sp_ex (t TIME) ASBEGINECHO SELECT t AS "t";END; //DELIMITER ;CALL sp_ex(9006060);
+----------+
| t |
+----------+
| 00:00:00 |
+----------+json_
json_ allows you to control the collation of JSON_ and JSON_ output.
-
json: Specifies that the result will use the collation of the JSON argument.If the collation of the argument is not set, the default ( utf8_) is used.bin -
Since the old
AUTO/SERVERbehavior is error-prone, existing customers should set this engine variable toSERVER_immediately after upgrading.V2 See the support bulletin: Using the JSON utf8mb4 Column. . . -
server/auto: Specifies that the collation result will be the same as collation of the server defined by thecollation_variable.server Generally, this value is utf8_.general_ ci -
SERVER_: Functions the same as server/auto, in previous version older than 7.V1 8. 21 but without the parser lockdown. -
SERVER_: When theV2 collation_isserver utf8and the JSON object isutf8mb4, the output will useutf8mb4.This is the default beginning with version 7. 8. 21
skip_
skip_ controls whether the server performs name resolution via DNS lookup upon login.
-
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_
sql_ specifies the current SQL mode (or modes) that the current session runs in.
-
ANSI_: ChangesQUOTES "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. With ANSI_enabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers.QUOTES -
ANSI: Setssql_tomode STRICT_,ALL_ TABLES ONLY_,FULL_ GROUP_ BY PIPES_, andAS_ CONCAT ANSI_together.QUOTES -
NO_: This feature is deprecated, It is now enabled by default and generates an error when privileges are granted to a non-existent user.AUTO_ CREATE_ USER -
ONLY_: Throws an error when fields in theFULL_ GROUP_ BY SELECTlist,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_: ChangesAS_ CONCAT ||to be treated as string concatenation operation and not as an OR operation.You can still use built-in function CONCATas a string concatenation with this mode enabled. -
STRICT_: Always enabled, regardless of other modes being enabled or not.ALL_ TABLES If set to an empty value, behavior is the same as if STRICT_is set.ALL_ TABLES
Example
The following examples show how to set sql_ 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_BYSET sql_mode = 'ONLY_FULL_GROUP_BY';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_BYSET sql_mode = 'ANSI_QUOTES';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 togetherSET sql_mode = 'ONLY_FULL_GROUP_BY,ANSI_QUOTES';SELECT @@sql_mode;
+--------------------------------------------------+
| @@sql_mode |
+--------------------------------------------------+
| ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES |
+--------------------------------------------------+
1 row in set (0.01 sec)sql_
The following examples show how to set sql_ with PIPES_.
Create a table and add values:
CREATE TABLE concat_exp(col_1 TEXT, col_2 TEXT);INSERT INTO concat_exp VALUES('base', 'ball');INSERT INTO concat_exp VALUES('rail', 'road');INSERT INTO concat_exp VALUES('sun', 'flower');INSERT INTO concat_exp VALUES('moon', 'light');
Set the sql_:
SET sql_mode = 'PIPES_AS_CONCAT';
Create a function:
DELIMITER //CREATE or REPLACE FUNCTION func_1(base TEXT, ball TEXT)RETURNS TEXT AS BEGIN RETURN base || ball;END //
Set the sql_:
delimiter ;set sql_mode = '';
Call the function previously created:
SELECT func_1(col_1, col_2) pipe_1 FROM concat_exp;
+-----------+
| pipe_1 |
+-----------+
| baseball |
| sunflower |
| railroad |
| moonlight |
+-----------+Select the columns from the table:
SELECT col_1 || col_2 as pipe_2 FROM concat_exp;
+--------+
| pipe_2 |
+--------+
| 0 |
| 0 |
| 0 |
| 0 |
+--------+ssl_
The passphrase specified with ssl_ is used to decrypt the encrypted Privacy Enhanced Mail (PEM) or Password Protected (PASS) file.memsql. file and can used for both intra-cluster and inter-cluster configuration.
Last modified: August 30, 2024