Join the MemSQL Community Today
Get expert advice, develop skills, and connect with others.

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

See the engine variables overview to learn how 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.

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
aggregator_failure_detection As of MemSQL 6.0, this is the timeout used when gathering workload management statistics from leaves. This variable can sync to all aggregators. ON
auto_attach 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
attach_rebalance_delay_seconds 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
background_statistics_collection_interval How often in seconds background statistics will check for out of date statistics (rowstore only). This variable can sync to all aggregators. 60
background_statistics_collection_threshold Threshold as a fraction of table row count triggering the collection of autostatistics. This variable can sync to all aggregators. 0.5
cardinality_estimation_level Specifies whether to use newer, more advanced histograms and algorithms to perform cardinality estimation (“6.5” and above), or use the previous histogram format (“6.0”). This variable can sync to all aggregators. “6.5” (for new installs), “6.0” (for upgrades from prior releases).
change_count_write_interval Maximum frequency in seconds that change count metadata will be written. Will never occur more frequently than background_statistics_collection_interval. This variable can sync to all aggregators. 1200
character_set_server The character set of the node. This is a session variable that can also be set globally. For more information on this variable, see the In-Depth Variable Definitions section below. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. utf8
cluster_name Specifies the cluster name used by the backup.
collation_connection Sets the collation that is used on the node. When you set this variable, collation_database and collation_server are automatically set to the same value. For more information on this variable, see the In-Depth Variable Definitions section below. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. utf8_general_ci
collation_database Sets the collation that is used on the node. When you set this variable, collation_connection and collation_server are automatically set to the same value. For more information on this variable, see the In-Depth Variable Definitions section below. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. utf8_general_ci
collation_server Sets the collation that is used on the node. When you set this variable, collation_connection and collation_database are automatically set to the same value. For more information on this variable, see the In-Depth Variable Definitions section below. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. utf8_general_ci
columnstore_disk_insert_threshold At this threshold (fraction of columnstore_flush_bytes), INSERT, LOAD DATA, and UPDATE queries to the columnstore will write straight to disk. For more information, see Advanced Columnstore Configuration Options. This variable can sync to all aggregators and all leaves. 0.5
columnstore_flush_bytes Controls the rowstore-backed segment size for columnstore tables. For more information, see Advanced Columnstore Configuration Options. This variable can sync to all aggregators and all leaves. 33554432
columnstore_segment_rows Controls the maximum row count for a columnstore segment. For more information, see Advanced Columnstore Configuration Options. This variable can sync to all aggregators and all leaves. 1024000
data_conversion_compatibility_level Specifies the level of data conversion behavior to use. Higher levels throws errors for integer under/overflow, illegal conversion of strings to integers, and string truncation issues. Values can be either 7.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. 6.0
default_autostats_columnstore_cardinality_mode Sets the default type of cardinality autostats for newly created columnstore tables. Values can be OFF, INCREMENTAL or PERIODIC. This variable can sync to all aggregators. INCREMENTAL
default_autostats_columnstore_sampling Sets the default state for autostats sampling for newly created columnstore tables. This variable can sync to all aggregators. ON
default_autostats_enabled Whether autostats is enabled by default on newly created columnstore tables. This variable can sync to all aggregators and all leaves. ON
default_autostats_histogram_mode Sets the default type of automatic histograms on newly created tables. This variable can sync to all aggregators. Values can be either OFF, CREATE, or UPDATE. CREATE
default_autostats_rowstore_cardinality_mode Sets the default type of cardinality autostats for newly created rowstore tables. Values can be OFF, INCREMENTAL or PERIODIC. This variable can sync to all aggregators. PERIODIC
default_distributed_ddl_timeout The time, in milliseconds, to wait for a distributed DDL transaction to commit. This value sets the timeout for both ALTER TABLE and BACKUP commands. If the timeout is reached, the transaction is rolled back. This variable can sync to all aggregators and all leaves. 180000
default_partitions_per_leaf The default number of partitions a newly created database will be created with. When you set this variable, its value is propagated to other aggregators only. 8
disk_plan_expiration_minutes The interval in which a query plan must be read from disk (plancache directory) before it is removed. This variable can sync to all aggregators and all leaves. 20160 (14 days)
distributed_commit_lock_timeout Deprecated in MemSQL 6.5. Replaced by default_distributed_ddl_timeout.
distributed_heartbeat_timeout 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
enable_background_plan_invalidation Controls whether the background statistics thread can invalidate plans automatically. This can improve plans if your data changes over time at the cost of spending time recompiling plans. This variable can sync to all aggregators. OFF
enable_background_statistics_collection If set to ON, background statistics are collected for all tables, unless disabled on the table level. If set to OFF, background statistics are not collected for any tables, and background statistics cannot be enabled for individual tables. This variable can sync to all aggregators. ON
enable_binary_protocol When this variable is set to ON, server-side prepared statements are enabled. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. ON
enable_disk_plan_expiration Enable removing of stale on disk plans from the plancache directory based on the value of disk_plan_expiration_minutes. This variable can sync to all aggregators and all leaves. true
enable_multipartition_queries When this variable is set to ON, queries are run per leaf instead of per partition on the leaf; the number of threads decreases and performance increases. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. ON
explain_expression_limit Specifies the maximum number of characters to be used by expressions when outputting the EXPLAIN for a query. This variable can sync to all aggregators and all leaves. 500
explicit_defaults_for_timestamp Specifies whether the server disables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns. See Timestamp Behavior for details on this variable. This variable can sync to all aggregators and all leaves. ON
enable_disk_plan_explain Retains human-readable query plan summaries for compiled query plans. Retained information is available through the SHOW PLAN command. This variable can sync to all aggregators. true
failover_initial_grace_interval_seconds 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 withing 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
geo_sphere_radius The radius of the sphere used for distance calculation, in meters. (Defaults to average Earth radius.) This variable can sync to all aggregators and all leaves. 6367444.657120
highlight_max_number_fragments Maximum number of fragments to return from highlight function. This variable can sync to all aggregators and all leaves. 0
highlight_fragment_size The size of a block of characters used to logically divide up string columns when using the full text search highlight functionality. Minimum value is 10, maximum value is 65535. This variable can sync to all aggregators and all leaves. 100
ignore_insert_into_computed_column When this variable is set to ON, an INSERT into a computed column succeeds and the INSERT ignores the computed column. When this variable is set to OFF, an INSERT into a computed column fails with an error. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. OFF
inlist_precision_limit Specifies the maximum number of list values to consider during histogram estimation for a query with an IN list; a smaller limit will result in a faster compilation time but may also give a less accurate estimate. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. 10000
internal_max_cte_depth Specifies the maximum number of nested common table expressions (CTEs). For more information, refer to the WITH topic. 128
json_extract_string_collation Controls the collation setting for JSON_EXTRACT_STRING function. This variable can sync to all aggregators and all leaves. For more details, see the In-Depth Variable Definitions section below. auto
leaf_failure_detection Whether or not the master aggregator should detect leaf failures and trigger failovers. WARNING: Turning this variable OFF disables failover. This variable can sync to all aggregators. On
leaf_failover_fanout Specifies the placement of replica partitions in a cluster. It can be set to the following modes: paired and load_balanced. For more information, see Managing High Availability. paired
load_data_internal_compression If this variable is set to ON and a network bottleneck is detected, LOAD DATA compresses data on the aggregator before forwarding it to the leaves. This variable can sync to all aggregators and all leaves. ON
load_data_max_buffer_size Maximum number of unparsed bytes read by LOAD DATA before throwing an error. This variable can sync to all aggregators and all leaves. 1073741823
load_data_read_size Number of bytes read at a time by LOAD DATA. This variable can sync to all aggregators and all leaves. 8192
load_data_write_size Number of bytes written at a time by LOAD DATA. This variable can sync to all aggregators and all leaves. 8192
lock_wait_timeout Time, in seconds, to wait for a row lock before returning an error. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. 60
log_file_size_partitions 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 log_file_size_partitions. By default, log_file_size_partitions is 256 MB. Changes in the log file size will not affect the sizes of the partitions that are used by existing databases. This variable can sync to all aggregators. 268435456
log_file_size_ref_dbs 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 log_file_size_ref_dbs. By default, log_file_size_ref_dbs is 64 MB. Changes in the log file size will not affect existing databases. This variable can sync to all aggregators. 67108864
max_connect_errors If the number of interrupted connections from a host exceeds the value of max_connect_errors this host will be blocked from further connections. This variable can sync to all aggregators and all leaves. 10
multi_insert_tuple_count Preferred number of tuples in multi-inserts that aggregators send to leaves. This variable can sync to all aggregators and all leaves. 20000
plan_expiration_minutes The interval in which a query plan must be reused at least once or it gets unloaded from the query plan cache. This variable can sync to all aggregators and all leaves. 720
preserve_original_colstore_json Set this variable to ON to preserve NULL values and empty arrays in a JSON object that is written to a columnstore table. It is a session variable that can be set globally. For more information on this variable, see the JSON Guide topic. AUTO (same as OFF)
redundancy_level If set to 1, there is no redundancy across leaves. If set to 2, turns on MemSQL’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
read_advanced_counters If ON, this variable enables collection of advanced statistics. For more information, see the Advanced Statistics section of the Management View Statistics Reference topic. This variable can sync to all aggregators and all leaves. OFF
regexp_format Specifies the regular expression format to be used by regular expression functions that you call. Possible values are 'extended' and 'advanced'. When you set this variable, its value is propagated to all nodes. For more information on this variable, see the Regular Expressions topic. This variable can sync to all aggregators and all leaves. ‘extended’
replication_sync This variable has been deprecated and is no longer operational in MemSQL.
resource_pool_statement_selector_function The function to execute at runtime that selects the resource pool to use when the user runs a query. The resource_pool variable must be set to system_auto for resource_pool_statement_selector_function to be used. For more information on this variable, see the Setting Resource Limits topic. This variable can sync to all aggregators and all leaves.
resource_usage_model Whether to train or enable the resource usage model for workload management. This variable can sync to all aggregators. OFF
snapshot_trigger_size The log size (in bytes) which, when reached, will trigger a new snapshot. This variable can sync to all aggregators and all leaves. 268435456
sp_query_dynamic_param Specifies whether different query plans are generated for CALL queries when arguments to the stored procedure are NULL or not NULL arguments. If enabled, queries that use variables inside stored procedures will use the same query plan. For more details, see the In-Depth Variable Definitions section below. ON
sql_select_limit The maximum number of rows returned by a SELECT query. If the LIMIT clause is specified in a SELECT query, the value in the LIMIT clause overrides sql_select_limit. This is a session variable that can also be set globally. This variable can sync to all aggregators and all leaves. 2^64-1
sync_permissions This aggregator will synchronize its permissions with other aggregators. This variable can sync to all aggregators. OFF
sync_slave_timeout 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. 20000

Password Complexity Sync Variables

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

Name Description Default
password_min_length The minimum number of characters required (0 to 100). 0
password_min_uppercase_chars The minimum number of uppercase characters required (0 to 100). 0
password_min_lowercase_chars The minimum number of lowercase characters required (0 to 100). 0
password_min_numeric_chars The minimum number of numeric digit characters required (0 to 100). 0
password_min_special_chars The minimum number of special (non-alphanumeric) characters required (0 to 100). 0
password_max_consec_sequential_chars The maximum number of consecutive characters allowed (0 to 100). For example, if set to 3, passwords with a 4-letter sequence or longer (e.g ‘1234’ or ‘abcd’) are disallowed. 0
password_max_consec_repeat_chars The maximum number of consecutive repeated characters allowed (0 to 100). For example, if set to 3, passwords with 4 or more consecutive repeated characters (e.g., ‘aaaa’ or ‘1111’) are disallowed. 0
expire_root_password Specifies whether the root password can expire. If set to ON, the root password will expire after the password_expiration_seconds duration is reached. OFF
password_expiration_seconds The time in seconds before a password expires. The value 0 indicates that the password will never expire. 0
password_history_count The number of previous passwords per user that MemSQL will store and disallow from reuse. The value 0 indicates that any previous password can be reused. The maximum is 10. 0

Pipelines Sync Variables

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

Name Description Default
advanced_hdfs_pipelines Specifies whether to enable security features for HDFS pipelines. This variable can sync to all aggregators and all leaves. OFF
java_pipelines_heap_size Heap size in megabytes for HDFS pipelines. This variable can sync to all aggregators and all leaves. 8
pipelines_deskew_batch_partitions_threshold For keyless sharded destination tables, if less than this fraction of batch partitions are active, reshuffle to avoid skew. When you set this variable, its value is propogated to all nodes. This variable can sync to all aggregators and all leaves. 0.75
pipelines_extractor_get_offsets_timeout_ms The maximum time in milliseconds to wait for offset data to be returned from the data source before returning an error. Increase this value if you experience timeout errors, such as ERROR 1970 (HY000): Subprocess timed out. Use the value 0 to indicate no timeout. This variable can sync to all aggregators and all leaves. 20000
pipelines_extractor_idle_timeout_ms The maximum time (in milliseconds) that a pipeline will wait for more data from an external source. Use the value 0 to indicate no timeout. This variable can sync to all aggregators and all leaves. 120000
pipelines_max_offsets_per_batch_partition The maximum number of data source partition offsets to extract in a single batch transaction. If the data source’s partition contains fewer than the specified number of offsets, all of the partition’s offsets will be batched into the destination table. This variable can sync to all aggregators. 1000000
pipelines_max_retries_per_batch_partition The number of retry attempts for writing batch partition data to the destination table.
If pipelines_stop_on_error is set to OFF and the specified retry number is reached without success, the batch partition will be skipped and will not appear in the destination table. If a batch partition is skipped, data loss can occur.
If pipelines_stop_on_error is set to ON and the specified retry number is reached without success, the pipeline will stop. No batch partition data will be skipped.
This configuration variable applies to the entire batch transaction, which includes extraction from a data source, optional transformation, and loading of the data into the destination table. If the batch transaction fails at any point during extraction, transformation, or loading, it will be retried up to the specified number.
This variable can sync to all aggregators.
4
pipelines_stop_on_error Specifies whether or not each pipeline in the cluster should stop when an error occurs.
If set to OFF, batches will be retried up to the number specified in the pipelines_max_retries_per_batch_partition variable. After all retries have failed, the batch will be skipped. When a batch is skipped, data loss can occur.
If set to ON, the batch transaction that caused the error will be retried up to the number specified in the pipelines_max_retries_per_batch_partition variable. After all retries have failed, the pipeline will enter a Stopped state and must be manually started. This variable can sync to all aggregators.
ON
pipelines_stored_proc_exactly_once If set to ON, run stored procedures from pipelines in a transaction. This variable can sync to all aggregators. ON

Workload Management Sync Variables

Name Description Default
workload_management Specifies whether to enable workload management for the cluster. If this variable is set to ON, the other workload management engine variables will affect the way a query is executed. If set to OFF, the feature is disabled and no queueing or system resource optimization will occur. This variable can sync to all aggregators. ON
workload_management_expected_aggregators This variable has been deprecated and is no longer operational in MemSQL.
workload_management_max_connections_per_leaf The maximum number of connections to use per leaf node in the cluster. This variable can sync to all aggregators. 10000
workload_management_max_queue_depth The maximum depth of the query queue, which is the maximum number of queries that can be queued. If this number is reached, additional queries will not execute, and a ER_TOO_MANY_QUEUED_QUERIES error will appear. This variable can sync to all aggregators. 100
workload_management_max_threads_per_leaf The maximum number of threads to use per leaf. This number correlates with the max_connection_threads engine variable, and they should generally be set to the same value. This variable can sync to all aggregators. 8192
workload_management_memory_queuing Whether to turn on queueing based on memory usage of queries. This variable can sync to all aggregators. ON
workload_management_memory_queue_threshold Percentage of memory a query can use before it will get queued. If an individual query is projected to use more than workload_management_memory_queue_threshold * (leaf_maximum_memory - leaf_current_table_memory) / workload_management_expected_aggregators, then it will be queued. This variable can sync to all aggregators. 0.01
workload_management_queue_time_warning_ratio Specifies when a warning will appear based on the ratio of time spent by a query in the queue versus the actual execution time of the query. For example, if a query waits in the queue for one second and it takes four seconds to execute, the ratio is 1:4, or 0.25. Once the specified ratio is reached for a query, a ER_QUERY_QUEUED_WARNING warning will appear. This variable can sync to all aggregators. 0.5
workload_management_queue_timeout The time duration in seconds after which a query times out and is removed from the queue without being executed. This variable can sync to all aggregators. 3600

Non-Sync Variables

See the engine variables overview to learn how non-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.

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
bind_address If the address is 0.0.0.0, memsql accepts connections on all network interfaces, otherwise it only accepts connections for the given IP address. 0.0.0.0 You cannot set this variable to take effect while the node is running.
div_precision_increment 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 MemSQL. MemSQL always returns the results to the fourth precision value.
flush_before_replicate If ON, data is written and flushed to disk locally before being replicated. This will increase replication latency but will guarantee that replicas will not flush data to disk before the master does. OFF You cannot set this variable to take effect while the node is running.
master_aggregator Appending master_aggregator (with no arguments) to the end of a node’s memsql.cnf specifies that node as the cluster’s master aggregator. For more information on this variable, see the Master Aggregator topic. You cannot set this variable to take effect while the node is running.
maximum_memory Maximum memory MemSQL 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
maximum_table_memory Maximum memory MemSQL will use for table storage, in MB. WARNING: Do not set to more than the default. If maximum_memory >= 10 GB, then 90% of maximum_memory. Else if maximum_memory >= 1.25 GB, then maximum_memory - 1 GB
port MemSQL port number. 3306 You cannot set this variable to take effect while the node is running.
reported_hostname The hostname or IP of the machine in use.
snapshots_to_keep Number of snapshots and log files to keep for backup and replication. 2 You cannot set this variable to take effect while the node is running.
datadir Directory path for the data directory. This directory contains snapshots, logs, and columnstore segments. You cannot set this variable to take effect while the node is running.
plancachedir Directory path for the plancache directory. This directory contains compiled plans used for codegen. ./plancache You cannot set this variable to take effect while the node is running.
tracelogsdir Directory path for the tracelogs directory. This directory contains log files, including memsql.log and the query log. ./tracelogs You cannot set this variable to take effect while the node is running.

Connection Management Variables

Name Description Default Setting Exceptions to When Variable can be Set
connect_timeout The number of seconds the node’s MemSQL process is waiting for a connection. 10
max_allowed_packet Maximum allowed protocol packet size. This is a session variable that can also be set globally. 104857600
max_async_compilation_concurrency The number of compiles that can run asynchronously at a given time on each node, when interpreter_mode is set to interpret_first. Its effective value is capped at the number of cores on the host machine. If set to 0, it takes the default value. Half the number of cores on the host machine.
max_connections The number of simultaneous clients allowed. For more, see the In-Depth Variable Definitions section below. 100000
max_connection_threads The maximum number of kernel threads for processing queries. For more, see the In-Depth Variable Definitions section below. 192
max_dedicated_admin_connections Specifies the number of connections reserved for admin users (users granted the SUPER permission). This number must be lower than max_connections. When the number of active connections is equal to or greater than max_connections minus max_dedicated_admin_connections, only admin users will be able to connect. Other users’ connections will be refused until the number of active connections falls below the limit. For example, if there are 95 current connections and max_connections is set to 100 and max_dedicated_admin_connections is set to 5, the limit is reached and only admin users will be able to connect. This is to prevent administrative users from being locked out of the system during heavy traffic. 5
max_pooled_connections The maximum number of stashed connections per leaf. For more information on this variable, see the In-Depth Variable Definitions section below. 1024 You cannot set this variable to take effect while the node is running.
skip_name_resolve Controls whether to perform name resolution. By default, AUTO will only perform a reverse Domain Name System (DNS) lookup if there are any host-based security rules. Options include AUTO, OFF, or ON. For more information on this variable, see the In-Depth Variable Definitions section below. AUTO You cannot set this variable to take effect while the node is running.

Database Optimization Variables

Name Description Default Setting Exceptions to When Variable can be Set
columnar_segment_rows Deprecated alias to columnstore_segment_rows. 1024000
columnstore_window_size Controls how much columnstore data is kept on disk for replicas to fetch. 2147483648
max_prepared_stmt_count The maximum number of simultaneous prepared statements. 16382
net_read_timeout 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
net_write_timeout 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
node_degree_of_parallelism Controls the number of threads per leaf node for parallel columnstore scans. 0
optimize_columnar_tables This variable has been deprecated and is no longer operational in MemSQL.
query_parallelism Maximum number of simultaneous running queries. This is a session variable that can also be set globally. 0
transaction_buffer This variable has been deprecated and is no longer operational in MemSQL.

Logging Variables

Name Description Default Setting Exceptions to When Variable can be Set
core_file Turning core_file on or off determines whether or not full core dumps are produced upon a crash. ON
critical_diagnostics Sends usage and critical error diagnostics to MemSQL. ON
general_log If ON, every query will be logged to a table or log file. If PARTIAL, will log only when load is light. OFF turns off logging. OFF
general_log_file Log connections and queries to given file. /var/lib/memsql/tracelogs/query.log
warn_level Defines how MemSQL behaves when it encounters unsupported functionality. For more information, visit the Unsupported Feature List section of the MySQL Features Unsupported in MemSQL 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
java_pipelines_java_home Specifies the value that MemSQL sets the node’s operating system environment variable JAVA_HOME to. If java_pipelines_java_home is not set, MemSQL does not set JAVA_HOME. You cannot set this variable to take effect while the node is running.
java_pipelines_java_path Specifies the path that the HDFS extractor uses to run Java.
pipelines_batches_metadata_to_keep The number of batch metadata entries to persist before they are overwritten by incoming batches.
As data is extracted from a source, it’s written in batches to a destination table on a leaf node. Metadata about these batches is temporarily persisted in the master aggregator’s information_schema.PIPELINES_BATCHES table. As new batches are loaded into the database, the oldest batch metadata entries will be removed from the information_schema.PIPELINES_BATCHES table. See the information_schema.PIPELINES_BATCHES Table section for more information about this metadata.
1000
pipelines_extractor_debug_logging Specifies whether to enable extractor debugging for Kafka or HDFS pipelines. This variable currently does not apply to S3 pipelines. OFF
pipelines_kafka_version The Kafka version used for the Kafka extractor. While the default version is 0.8.2.2, newer versions can also be specified. 0.8.2.2
pipelines_max_concurrent The maximum number of pipelines running concurrently. 50
pipelines_max_concurrent_batch_partitions The maximum number of pipeline batch partitions running concurrently. 0
pipelines_max_errors_per_partition Deprecated in MemSQL 6.7. The maximum number of error event rows per leaf node partition to persist before they are deleted.
Once the specified number of rows in the information_schema.PIPELINES_ERRORS table is reached, the database will eventually remove the oldest rows from the table. The removal mechanism for older error data is based on heuristics. Old errors are guaranteed to exist up to the specified number, but they may not immediately be removed.
1000
pipelines_stderr_bufsize The buffer size for standard error output in bytes. Error messages that exceed this size will be truncated when written to the information_schema.PIPELINES_ERRORS table. However, the complete standard error text can be viewed by using the BATCH_ID and querying the information_schema.PIPELINES_BATCHES table. 65535

Security Variables

Name Description Default Setting Exceptions to When Variable can be Set
ssl_ca CA file to be used for SSL connections. You cannot set this variable to take effect while the node is running.
ssl_capath CA directory to be used for SSL connections. You cannot set this variable to take effect while the node is running.
ssl_cert Certificate file to be used for SSL connections. You cannot set this variable to take effect while the node is running.
ssl_cipher Cipher to be used for SSL connections. You cannot set this variable to take effect while the node is running.
ssl_key Public-private key pair file to be used for SSL connections. You cannot set this variable to take effect while the node is running.
ssl_key_passphrase Passphrase for encrypted ssl_key. You cannot set this variable to take effect while the node is running.
node_replication_ssl_only When this variable is set to ON (assuming that SSL is enabled), SSL is used for cross-cluster replication, but not for intra-cluster communication. When the variable is OFF, SSL is used for both cross-cluster as well as intra-cluster replication. OFF You cannot set this variable to take effect while the node is running.

Other Variables

Name Description Default Setting Exceptions to When Variable can be Set
activities_delta_sleep_s activities_delta_sleep_s specifies an interval of time, which is used by the mv_activities_cumulative variable to determine recent resource usage. For more information, see the Management View Reference topic. This is a session variable that can also be set globally. 1
auditlogsdir Specifies the local or network directory to write log files. For more information on this variable, visit the Configuring Audit Logging topic. //auditlogs You cannot set this variable to take effect while the node is running.
auditlog_disk_sync 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, visit the Configuring Audit Logging topic. OFF You cannot set this variable to take effect while the node is running.
auditlog_level auditlog_level is used to specify the level of logging in a node. There are 11 logging levels. For more information on this variable, visit the Audit Logging Levels topic. OFF You cannot set this variable to take effect while the node is running.
auditlog_rotation_size Specifies the maximum size per log file in bytes. Required if logging is enabled. For more information on this variable, visit the Configuring Audit Logging topic. 134217728 You cannot set this variable to take effect while the node is running.
auditlog_rotation_time Specifies the maximum time duration to write to a single log file in seconds. For more information on this variable, visit the Configuring Audit Logging topic. 3600 You cannot set this variable to take effect while the node is running.
auto_replicate This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
autocommit If ON, transactions will take effect immediately. This is a session variable that can also be set globally. ON
basedir Specifies the path where the installation directory can be found. You cannot set this variable to take effect while the node is running.
character_set_client This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
character_set_connection This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
character_set_database This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
character_set_filesystem This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
character_set_results This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
character_set_server The character set of the node. This is a session variable that can also be set globally. For more information on this variable, see the In-Depth Variable Definitions section below. utf8
character_set_system The character set used to store identifiers. The value is read-only and always has the value utf8. utf8
character_sets_dir Specifies the directory where character sets are stored. /var/lib/memsql/share/charsets/ You cannot set this variable to take effect while the node is running.
collation_connection Sets the collation that is used on the node. When you set this variable, collation_database and collation_server are automatically set to the same value. This is a session variable that can also be set globally. For more information on this variable, see the In-Depth Variable Definitions section below. utf8_general_ci
compile_only If ON, MemSQL will compile, but not run, each query it receives. This is a session variable that can also be set globally. OFF You cannot set this variable to take effect when the node starts.
core_file_mode Specifies type of core dump to generate if MemSQL terminates abnormally. Options include NONE, PARTIAL, or FULL. PARTIAL omits most user data to keep the dump small, while a FULL core dump uses the equivalent amount of disk space as the amount of memory used by MemSQL. PARTIAL You cannot set this variable to take effect while the node is running.
date_format This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. %Y-%m-%d
datetime_format This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. %Y-%m-%d %H:%i:%s
default_time_zone 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 the Setting the Time zone topic for details on this variable.
error_count This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. 0
expire_root_password Specifies whether the root password can expire. OFF
external_user This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
flat_plancache This variable has been deprecated and is no longer operational in MemSQL version 5.0 or newer.
group_concat_max_len This variable is the maximum length string GROUP_CONCAT() can return in bytes. It is a session variable which can be set globally and can be set to any value smaller than or equal to max_allowed_packet. For more information on this variable see max_allowed_packet in the previous section and the GROUP_CONCAT() SQL reference. 8192
gssapi_keytab_path The local path to the keytab file created on the KDC. For more information on this variable, see the Kerberos Authentication topic. You cannot set this variable to take effect while the node is running.
gssapi_principal_name The SPN for MemSQL that was created on the KDC. For more information on this variable, see the Kerberos Authentication topic. You cannot set this variable to take effect while the node is running.
hostname The server host name specified by the server at startup. This variable is read-only.
identity Contains the value of last_insert_id. This is a session variable that can also be set globally. You cannot set this variable to take effect when the node starts.
interactive_timeout This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. 28800
interpreter_mode Controls whether and how MemSQL compiles or interprets query plans. Allowed settings include: llvm, mbc, interpret_first. See the interpreter_mode section below for more details. This is a session variable that can also be set globally. interpret_first
kerberos_server_keytab This variable has been deprecated and is no longer operational in MemSQL.
lc_messages This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
lc_messages_dir Specifies the directory where error messages are stored. /var/lib/memsql/share/ You cannot set this variable to take effect while the node is running.
lc_time_names This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. en_US
load_data_batch_size This variable has been deprecated and is no longer operational in MemSQL version 5.0 or newer.
local_infile This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. ON
locked_in_memory This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. OFF
materialize_ctes Specifies the setting to materialize the common table expressions (CTEs). If set to OFF, it inlines the CTEs similar to view definitions. If set to ALL, then each CTE appearing more than once in a query is materialized into an internal result table. If set to AUTO, optimizations are enabled. Currently, only the predicate pushdown optimization is available. This engine variable is experimental and should not be enabled in production. For more information, see the Materialize Common Table Expressions topic. OFF
max_user_connections This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
memsql_id The ID of the MemSQL node whose config you would like to update. This variable is read-only.
memsql_version MemSQL version number. This variable is read-only.
memsql_version_date The build date of the MemSQL version currently running. This variable is read-only.
memsql_version_hash MemSQL version hash. This variable is read-only.
minimal_disk_space Sets the minimal available disk space allowed, under which MemSQL will halt new write queries. 100
net_buffer_length 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
net_retry_count This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. 10
password_expiration_seconds The time in seconds before a password expires. A value of 0 indicates the password will never expire. 0
password_history_count Restricts the reuse of previous user passwords. This variable is the number of previous passwords per user that MemSQL will store and disallow from reuse. Maximum value is 10. 0
pid_file The path name of the process ID file. /var/lib/memsql/memsql.pid You cannot set this variable to take effect while the node is running.
protocol_version Specifies the version of the client/server protocol. This variable is read-only. 10
proxy_user This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
resource_pool 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 the Setting Resource Limits topic. default_pool
saml_assertion_audience Specifies a single audience restriction for the SAML assertion. For more information on this variable, see the Configuring SAML Global Variables topic. You cannot set this variable to take effect while the node is running.
saml_message_recipient Specifies the intended recipient for a wrapped encryption key. For more information on this variable, see the Configuring SAML Global Variables topic. You cannot set this variable to take effect while the node is running.
saml_private_decryption_key Specifies the file path for the private key used to decrypt an encrypted assertion. For more information on this variable, see the Configuring SAML Global Variables topic. You cannot set this variable to take effect while the node is running.
saml_require_encryption Specifies if authentication should fail when both the SAML response and SAML assertion are unencrypted. For more information on this variable, see the Configuring SAML Global Variables topic. OFF You cannot set this variable to take effect while the node is running.
saml_require_signature_validation Specifies if authentication should fail when both the SAML response an SAML assertion are unsigned. For more information on this variable, see the Configuring SAML Global Variables topic. OFF You cannot set this variable to take effect while the node is running.
saml_use_NameID Specifies whether a username value should be extracted from the <saml:NameID> element in a SAML assertion. For more information on this variable, see the Configuring SAML Global Variables topic. OFF You cannot set this variable to take effect while the node is running.
saml_user_name_attribute 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 the Configuring SAML Global Variables topic.