Configuration
Last updated
Was this helpful?
Last updated
Was this helpful?
A Dolt SQL server can be configured at server start time using a , or by setting in the SQL session.
The simplest way to configure server behavior is to provide a config file with the--config flag, usually called config.yaml.
Here is a complete config.yaml file populated with all the default values for every key.
For the examples, I use a database named config_blog with a single table defined by:
log_levelThis configuration value is used to increase or decease the log level of your Dolt SQL server. Logs by default are printed to STDERR and STDOUT.
Level of logging provided. Options are: trace, debug, info, warning, error, and fatal.
Default: info
Values:
Possible values from most logging to least. Each log level logs everything below it plus the values at the listed level.
trace: Logs server messages including MySQL wire protocol messages. Useful for debugging client/server communication issues.
debug: Logs all queries, results, and latencies. Useful when trying to debug bad query behavior like what query is causing an error. Note, SQL queries often contain sensitive data so this log level is not recommended for production use.
info: Logs informational messages but not queries. This log level is recommended for production deployments.
warning: Logs warnings.
error: Logs all errors.
fatal: Logs fatal errors.
Example:
In this example, I set the log level to info and run a bad query. Then, I restart the server with debug log level an re-run the same bad query.
As you can see, I get the error but not the query that caused the error. Now, I stop the server using Ctrl-C and edit my config.yaml using emacs, raising the log level to debug. I restart the server and re-run the bad query in a connected client.
I now see the bad query being run is select * from t where bad_col=3.
log_formatThis configuration value is used to change the log format. Current supported options are text and json. Text is the default.
Default: text
Values:
text: Default log format emits logs as text strings
json: Emits logs in json format for easier parsing
Example:
In this example, I set the log format to json and start a server. As you can see the logs are emitted in json format.
behaviorThe behavior section of config.yaml defines configuration that determines the way the SQL engine works.
read_onlyThis configuration value is used to turn your SQL server into read only mode, preventing any write queries from succeeding and logging an error.
If true database modification is disabled. Defaults to false.
Default: false
Values: true, false
Example:
I start the Dolt SQL server with read_only set to false. The second read_only configuration value is remotesapi.read_only which is set to null.
I make an insert in a connected client and it succeeds.
Now, I stop the above server using Ctrl-C and modify the config.yaml by setting read_only to true. Then, I restart the server using the new config.yaml.
As expected, you can see the query failed with a "database server is set to read only mode". In the client, I also received the same error.
autocommitIf true every statement is committed automatically. Defaults to true. @@autocommit can also be specified in each session.
Default: true
Values: true, false
Example:
autocommit is visible under concurrency so for this example I need two connected clients. I start by starting the Dolt SQL server with autocommit on.
Now I connect both clients, viewing the state of the table. In client one I see:
In client two I see the same thing:
Back in client one, I insert a value:
And I am able to see that value in client two without issuing an explicit transaction commit.
Now, I kill the server with Ctrl-C and set autocommit to false in config.yaml.
Now I reconnect both clients. I should see this table in both clients:
In client one I make an insert:
But that insert is not visible in client two:
I must issue a commit in client one:
and a begin in client two. Now I see the insert in client two.
disable_client_multi_statementsBy default, the Dolt SQL server can accept and process multiple SQL queries in a single statement. The default delimiter is a semicolon (ie. ;). So, you can send multiple SQL queries in the same statement as long as they are separated by a semicolon and by default Dolt will process each individually and return the results. However, some clients are not able to handle multiple result sets from a single statement. So, Dolt offers a configuration value to fail statements that contain multiple SQL queries.
Default: false
Values: true, false
Example:
In order to get the standard MySQL client to send multi-statement queries to a server, I must change the delimiter to something other than ;. The client parses queries at the defined delimiter and sends them individually. So, I start by changing the delimiter on my client to ?.
Now, I issue a multi-statement query and it succeeds.
I stop the server using Ctrl-C. Now, I set the disable_client_multi_statement to true and restart the server:
I pass in the inverse query and it will fail:
dolt_transaction_commitIf true all SQL transaction commits will automatically create a Dolt commit, with a generated commit message. This is useful when a system working with Dolt wants to create versioned data, but doesn't want to directly use Dolt features such as dolt_commit()
Default: false
Values: true, false
Example:
Without dolt_transaction_commit enabled, I must issue a call to the dolt_commit() procedure to get a new entry in the log.
After I enable dolt_transaction_commit and restart the server:
Every write statement becomes a Dolt commit:
Note, I lose control of the commit message in this mode. The commit is made by the server user, in this case configblog, in contrast to manual commits which are made by the client user root.
event_schedulerDefault: "ON"
Values: "ON", "OFF"
Example:
I start the Dolt SQL server in debug mode so we can see event execution in the logs. I create an event to create a Dolt commit every minute. Notice the --allow-empty flag. This allows Dolt to commit without error even when nothing has changed in the database.
Now, we wait a minute and in the logs we see that the event has fired, as expected.
We can inspect the Dolt log and see indeed the commit succeeded.
Now stop the server and stop event execution using config.yaml.
We do not see any more commits in the debug log or in the Dolt log.
auto_gc_behaviorenable
Default: false
Values: false, true
Example:
When automatic GC is disabled, Dolt never performs a garbage collection of a database without being explicitly asked to. In this mode, the only way to run GC is to run the SQL query call dolt_gc(). When automatic GC is disabled, running garbage collection on the server forcefully terminates inflight connections and terminally invalidates the requesting connection:
When running with auto_gc_behavior: enable: true, the server will periodically run a garbage collection of a growing database in the background. The impact of running a garabage collection on the server will also be different. It will no longer terminate in flight connections and it will no longer leave the calling conection in an invalid state.
Here we see the impact of running GC is different:
We can cause an automatic GC by inserting some data.
We will see a log line from the server like:
and if we run a GC manually we can see that there is nothing to collect:
Automatic garbage collection in Dolt is currently experimental. Its scheduling and pacing of the GC process itself is not yet configurable, and enabling it may have material performance impact on the running server.
archive_level
Default: 0
Values: 0, 1
When automatic garbage collection is enabled, you can optionally specify the amount of compression performed on the storage files.
The default value of 0 will perform minimal compression, while level 1 will result in ~25% less space used.
Automated Garbage Collection, and Archive support are both considered experimental features.
Example:
listenerThe listener section of config.yaml is configuration for the SQL server transport layer.
hostThe host defines the address of the server that Dolt is running on.
The host address that the server will run on. This may be localhost or an IPv4 or IPv6 address
Default: localhost
Values: localhost or an IPv4 or IPv6 address
Example:
This is a bit of a hard one to show off as valid values for this field on my laptop are localhost or 127.0.0.1. I change the value to 127.0.0.1.
You notice the starting server message now says 127.0.0.1 instead of localhost.
portThe port on the server used to accept connections. The default is 3306. Be careful because that is also the MySQL and MariaDB default port so you either need to stop your MySQL server to run Dolt, or change the Dolt port to something else.
The port that the server should listen on
Default: 3306
Values: Any integer between 1024 to 49151.
Example:
max_connectionsThe maximum number of simultaneous connections the server will accept. Connections over the limit queue until
an existing connection is terminated. Setting this to 0 will allow for unlimited connections.
The number of simultaneous connections that the server will accept
Default: 1000
Values: Any integer between 0 and 100,000.
Example:
I configure a server with a single maximum connection.
I connect with a client one with no issue.
If I exit client one:
Client two connects.
back_logThis setting controls the number of client connections that can be blocked waiting. If the queue is full, new connection attempts will be refused until a slot becomes available. If
set to 0, new connections will be immediately rejected if max_connections has been reached. back_log is only meaningful if max_connections is set to a non-zero value.
The number of simultaneous connections that the server will allow to block waiting for a connection before new connections result in immediate rejection.
Default: 50
Values: Any integer between 0 and the max 32-bit integer (2,147,483,647).
Example:
I want to ensure that we take no more than 42 conncurrent active connections and allow 5 connections to wait (block) for one of those to close. Of those blocked connections, they will wait for a maximum of 7 seconds before being closed by the server:
max_connections_timeout_millisThis setting controls the maximum amount of time, in milliseconds, that a client connection attempt will block waiting for a connection. This configuration
is only meaningful if back_log is a non-zero value.
The maximum amount of time that a connection will block waiting for a connection before being rejected.
Default: 60000
Values: Any integer between 1 and the max 64-bit integer (9,223,372,036,854,775,807).
Examples:
read_timeout_millisThis setting controls when the server will time out a connection where no packets are sent. The value is defined in milliseconds. If the server does not read a packet from the connected client for the listed number of milliseconds a timeout error is returned and the connection is killed. The option is equivalent to net_read_timeout in MySQL. Most MySQL clients send keep alive packets to avoid this timeout. Use this to control bad client connections.
The number of milliseconds that the server will wait for a read operation
Default: 28800000
Values: Any integer between 1 and the max 64-bit integer (9,223,372,036,854,775,807).
Example:
I set the read timeout to 1 millisecond and start the server.
Now, I'll issue a select sleep(5) in a client which occupies the client so it does not send packets.
The query fails and the connection is killed.
write_timeout_millisThis setting controls when the server will time out a connection where it cannot send packets. The value is defined in milliseconds. If the server does not write a packet to the connected client for the listed number of milliseconds a timeout error is returned and the connection is killed. The option is equivalent to net_write_timeout in MySQL. Use this to control bad client connections.
The number of milliseconds that the server will wait for a write operation
Default: 28800000
Values: Any integer between 1 and the max 64-bit integer (9,223,372,036,854,775,807).
Example:
We were a bit confused how to trigger this timeout and could only do it within Dolt code. Practically, we think this type of timeout is triggered very rarely in the wild.
tls_keyThe path to the TLS key used for secure transport
Default: null
Values: A path on your filesystem to a .pem file.
tls_certThe path to the TLS certificate used for secure transport
Default: null
Values: A path on your filesystem to a .pem file.
require_secure_transportBoolean flag to turn on TLS/SSL transport
Default: null
Values: null or true
Example:
Dolt source code comes with a signed key and cert .pem file. Set the following variables in your config.yaml. I have my Dolt source code stored at ~/dolthub/git/dolt/.
Now I connect and run status and I can see I am on a SSL connection.
allow_cleartext_passwordsThis is a bit of an advanced option. allow_cleartext_passwords only affects the mysql_clear_password auth plugin, which is only used for JSON Web Token (JWT) authentication. Other auth plugins protect the password (e.g. mysql_native_password does a hash scramble, caching_sha2_password requires an encrypted connection), but mysql_clear_password sends the plaintext password over the wire. If you are using JWT authentication you must enable allow_cleartext_passwords or require_secure_transport.
Default: false
Values: true, false, or null
max_logged_query_len sets the maximum amount of characters Dolt will log in the server logs. We had an issue where very long queries, like seen in dumps would overflow buffers in some log monitoring utilities. This setting allows the user to truncate log lines at a maximum length to avoid such failure modes. This only effects queries so you must also set the log level to debug or above to see an effect.
If greater than zero, truncates query strings in logging to the number of characters given.
Default: 0
Values: non-negative integer
Example:
I set the log_level to debug and the max_logged_query_len to 10 and start theDolt SQL server.
Now, all queries are truncated to 10 characters in the logs:
data_dirThe data_dir, config_dir, privilege_file and branch_control_file work in conjunction to tell Dolt where to create and load various artifacts needed for the running of the database. data dir defaults to the current working directory. data_dir configures the root directory and is used by config_dir, privilege_file and branch_control_file.
A directory where the server will load dolt databases to serve, and create new ones. Defaults to the current directory.
Default: .
Values: Any filesystem path
config_dirconfig_dir is a directory where Dolt will load and store configuration used by the database. Configuration includes the privilege_file and branch_control_file used to store users/grants and branch permissions configuration respectively. This defaults to the $data_dir/doltcfg directory.
A directory where the server will load and store non-database configuration data, such as permission information. Defaults
$data_dir/.doltcfg
Default: .doltcfg
Values: Any filesystem path
privilege_fileThe privilege_file is a file used to store and load users/grants configuration.
Path to a file to load and store users and grants. Defaults to $doltcfg-dir/privileges.db. Will be created as needed.
Default: .doltcfg/privileges.db
Values: Any filesystem path
branch_control_fileThe branch_control_file is a file used to store and load users/grants configuration.
Path to a file to load and store branch control permissions. Defaults to $doltcfg-dir/branch_control.db. Will be created as needed.
Default: .doltcfg/branch_control.db
Values: Any filesystem path
Example:
data_dir, config_dir, privilege_file and branch_control_file can all be set to independent filesystem locations but we recommend only using data_dir to change the location of your database storage. It is common to have data stored on a different mounted drive than where the server binary or logs are stored.
I set the data_dir to /tmp.
This is a new directory so there are no databases in it.
After we create a database named tmp:
We can see the .dolt directory in /tmp/tmp:
The host defines the host Dolt will use to serve the metrics endpoint.
Default: null
Values: localhost or an IPv4 or IPv6 address
The port defines the port Dolt will use to expose the metrics endpoint.
Default: -1
Values: Any integer between 1024 to 49151
Example:
host and port must be defined together to enable a metrics endpoint. In this example, I define host as localhost and port as 11111 in config.yaml and start a server.
Then, I can access the metrics by making an HTTP request to http://localhost:11111/metrics
Labels can be added to any Dolt metrics emitted using this optional configuration setting. This is often used to differentiate metrics coming from multiple sources to a single Prometheus collector. The label map will be applied to every metric Dolt emits.
Default: {}
Values: A map of the form {"label": "value"}
Example:
I add the {"process": "dolt-sql-server"} label value in config.yaml and start a Dolt SQL Server.
Now all the metrics emitted are labeled with process="dolt-sql-server".
Default: null
Values: Any integer between 1024 to 49151
Example:
I now have a cloned copy of the database in the location I cloned to.
If a Dolt remote endpoint is enabled by setting a valid port, the endpoint can be made read only by setting read_only to true. The endpoint will accept clone, pull, and fetch requests but not push requests.
Default: null
Values: null, true, or false
Example:
I now set the read_only configuration value to true and start the Dolt SQL server.
If I make a change and attempt to push it will fail.
Default: {}
Values: A map of system variable to value.
Example:
Connecting a client to the server now has that variable set and exhibits the proper behavior:
If instead of setting system variables globally, you would rather set them for individual users, Dolt supports a user_session_vars list of maps in config.yaml.
Default: []
Values: A list of user to variable map
Example:
Let's again set the dolt_show_system_tables variable but this time only for user root. I modify my config.yaml as such.
And then in a connected client with user root, the server now has that variable set and exhibits the proper behavior:
Dolt defines system variables that you can set in your session via theSET syntax. Many of these can be persisted, so they remain set after
a server restart.
From the :
From the :
autocommit is a standard SQL database setting where every SQL statement triggers a transaction COMMIT. Without autocommit, the user is responsible for managing their own concurrency by issuing BEGIN statements at the start of transactions and COMMIT or ROLLBACK statements at the end of transactions. Most databases (ie. MySQL, Postgres) and clients (ie. ODBC, JDBC) have autocommit on by default with .
From the :
Dolt offers a setting where every transaction commit also becomes a Dolt commit. That setting can be controlled using dolt_transaction_commit in config.yaml. By default, Dolt commits are user controlled and triggered via the . In some cases, like when you have an existing application that is built against standard MySQL, you may want Dolt commits generated automatically. This setting enables that behavior.
From the :
Dolt supports . Events are scheduled jobs created using the CREATE EVENT SQL statement. Event scheduling is on by default but can be disabled using this configuration setting. Note, only events on the main branch will be executed by the event scheduler. Events can be used to schedule Dolt commits at intervals if you don't have access to the application code for your application, but also don't want a commit at every SQL transaction.
The auto_gc_behavior subsection of behavior controls the configuration of .
From the :
From the :
Astute readers may have noticed I've been running this example on port 3310 the whole time. I'm using port 3306 for . I have this port configured in muy config.yaml. The second and third port settings are for a Remote API and a metrics endpoint which are not covered in this article.
From the :
If I connect with another client, it will hang for 1 minute before giving up (see )
From the :
42 clients will immediately connect and be able to perform queries. The next 5 connections will block (assuming no connections drop). Additional connections will be immediately closed. Any blocked connection which waits for 7 seconds will be closed. To test, see the examples.
From the :
See
From the :
From the :
tls_key, tls_cert, and require_secure_transport as used together and are covered in . tls_key is the path to the key file to use for secure transport.
From the :
tls_key, tls_cert, and require_secure_transport as used together and are covered in . tls_cert is the path to the ket file to use for secure transport.
From the :
tls_key, tls_cert, and require_secure_transport as used together and are covered in . Setting require_secure_transport enables TLS using the listed tls_key and tls_cert files.
From the :
From the :
From the :
From the :
From the :
From the :
This set of configuration values configures a . Dolt emits metrics in format.
For more information on how to scrape the metrics from this endpoint consult .
A running Dolt SQL server can serve as a by enabling these configuration values. With a remote endpoint enabled, you can clone, push, pull, and fetch from a running Dolt SQL Server by connecting with a user with the appropriate permissions. Additional documentation on how to push can be found in where we announced push support.
In another shell, I can now clone the database by specifying the DOLT_REMOTE_PASSWORD environment variable and a --user. Note, cloning from or uses a different authentication method and thus, does not require a user or password.
Dolt features a number of and . These variables can be set for a running server using a map of system variable to value in this section of the configuration.
I will enable which changes the behavior of show tables to include Dolt system tables.
The jwks section of config.yaml is used to configure JSON web token (JWT) authentication. This configuration section is used to authenticate users of the Hosted Workbench to running Hosted Dolt servers. If your interested in this authentication method for your own Dolt use case, please come to and let us know.
This section of config.yaml is used to configure "Direct to Standby" or cluster replication. Refer to for this section of config.yaml. This configuration requires multiple Dolt instances configured so it is out of scope for this article.
A full list of available system variables can be found in the .