DoltHubBlogDiscordGitHub
Edit

Configuration

A Dolt SQL server can be configured at server start time using a configuration file, or by setting system variables in the SQL session.

config.yaml

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.

log_level: info
log_format: text

behavior:
  read_only: false
  autocommit: true
  disable_client_multi_statements: false
  dolt_transaction_commit: false
  event_scheduler: "ON"
  auto_gc_behavior:
    enable: false
    archive_level: 0

listener:
  host: localhost
  port: 3306
  max_connections: 1000
  back_log: 50
  max_connections_timeout_millis: 60000
  read_timeout_millis: 28800000
  write_timeout_millis: 28800000
  ca_cert: null
  tls_key: null
  tls_cert: null
  require_client_cert: null
  require_secure_transport: null
  allow_cleartext_passwords: null

max_logged_query_len: 0

data_dir: .
cfg_dir: .doltcfg
privilege_file: .doltcfg/privileges.db
branch_control_file: .doltcfg/branch_control.db

# Advanced Configuration
metrics:
  labels: {}
  host: null
  port: -1

remotesapi:
  port: null
  read_only: null

mcp_server:
  port: 7007
  user: root
  password: ""
  database: ""

system_variables: {}

user_session_vars: []

jwks: []

# Cluster configuration has required defaults.
# cluster: {}

For the examples, I use a database named config_blog with a single table defined by:

log_level

This 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.

From the dolt sql-server help documentation:

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.

  1. trace: Logs server messages including MySQL wire protocol messages. Useful for debugging client/server communication issues.

  2. 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.

  3. info: Logs informational messages but not queries. This log level is recommended for production deployments.

  4. warning: Logs warnings.

  5. error: Logs all errors.

  6. 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 and 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_format

This configuration value is used to change the log format. Current supported options are text and json. Text is the default.

Default: text

Values:

  1. text: Default log format emits logs as text strings

  2. 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.

behavior

The behavior section of config.yaml defines configuration that determines the way the SQL engine works.

read_only

This configuration value is used to turn your SQL server into read only mode, preventing any write queries from succeeding and logging an error.

From the dolt sql-server help documentation:

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.

autocommit

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 the notable exception of the Python client.

From the dolt sql-server help documentation:

If 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_statements

By 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_commit

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 dolt_commit() procedure. 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 sql-server help documentation:

If 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_scheduler

Dolt supports MySQL events. 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.

Default: "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_behavior

The auto_gc_behavior subsection of behavior controls the configuration of automatic garbage collection.

enable

Default: true

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 garbage collection on the server will also be different. It will no longer terminate in flight connections and it will no longer leave the calling connection 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:

archive_level

Default: 1

Values: 0, 1

When automatic garbage collection is enabled, you can optionally specify the amount of compression performed on the storage files.

The default level 1 will result in ~30% less space used, but may force older clients to perform more computational work if they are not upgraded. Setting this value to 0 should only be done if there are a considerable number of contributors to the database who can not upgrade.

Example:

branch_activity_tracking

Default: false

Values: true, false, null

This flag is used to enable the dolt_branch_activity system table. This feature can impact performance on high-throughput servers, so it is disabled by default.

Example:

listener

The listener section of config.yaml is configuration for the SQL server transport layer.

host

The host defines the address of the server that Dolt is running on.

From the dolt sql-server help documentation:

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.

port

The 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.

From the dolt sql-server help documentation:

The port that the server should listen on

Default: 3306

Values: Any integer between 1024 to 49151.

Example:

Astute readers may have noticed I've been running this example on port 3310 the whole time. I'm using port 3306 for my long-running Wikipedia import. I have this port configured in my config.yaml. The second and third port settings are for a Remote API and a metrics endpoint which are not covered in this article.

max_connections

The 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.

From the dolt sql-server help documentation:

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 connect with another client, it will hang for 1 minute before giving up (see max_connections_timeout_millis)

If I exit client one:

Client two connects.

back_log

This 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.

From the dolt sql-server help documentation:

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:

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 max_connections examples.

max_connections_timeout_millis

This 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.

From the dolt sql-server help documentation:

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:

See back_log

read_timeout_millis

This 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.

From the dolt sql-server help documentation:

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_millis

This 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.

From the dolt sql-server help documentation:

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.

ca_cert

ca_cert allows you to specify a CA (Certificate Authority) certificate that will be used to validate client certificates. You can configure user accounts to require a valid client certificate when creating the user account by specifying the REQUIRE X509 clause, for example: CREATE USER user1@'%' REQUIRE X509.

Default: null

Values: A path on your filesystem to a .pem file.

tls_key

tls_key, tls_cert, and require_secure_transport are used together and are covered in this article. tls_key is the path to the key file to use for secure transport.

From the dolt sql-server help documentation:

The path to the TLS key used for secure transport

Default: null

Values: A path on your filesystem to a .pem file.

tls_cert

tls_key, tls_cert, and require_secure_transport are used together and are covered in this article. tls_cert is the path to the cert file to use for secure transport.

From the dolt sql-server help documentation:

The path to the TLS certificate used for secure transport

Default: null

Values: A path on your filesystem to a .pem file.

require_secure_transport

tls_key, tls_cert, and require_secure_transport as used together and are covered in this article. Setting require_secure_transport enables TLS using the listed tls_key and tls_cert files.

From the dolt sql-server help documentation:

Boolean 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.

require_client_cert

Setting this field requires all client connections to present a client certificate. If the server also defines ca_cert, then the client certificates will also be verified against the server's CA cert. Using this option requires also setting tls_key and tls_cert since use of certificates require a TLS connection.

Default: null

Values: null, false, or true

Example:

The following snippet from a config.yaml file shows how to set require_client_cert, along with tls_key, and tls_cert.

When running with this configuration, all clients must present a certificate in order to connect to the sever. If ca_cert is also configured, then the client's certificate will be verified against the server's CA cert.

allow_cleartext_passwords

This 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

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.

From the dolt sql-server help documentation:

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 the Dolt SQL server.

Now, all queries are truncated to 10 characters in the logs:

data_dir

The 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.

From the dolt sql-server help documentation:

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_dir

config_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.

From the dolt sql-server help documentation:

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_file

The privilege_file is a file used to store and load users/grants configuration.

From the dolt sql-server help documentation:

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_file

The branch_control_file is a file used to store and load users/grants configuration.

From the dolt sql-server help documentation:

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:

metrics

This set of configuration values configures a Dolt metrics HTTP endpoint. Dolt emits metrics in Prometheus format.

host

The host defines the host Dolt will use to serve the metrics endpoint.

Default: null

Values: localhost or an IPv4 or IPv6 address

port

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

For more information on how to scrape the metrics from this endpoint consult our metrics documentation.

labels

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".

remotesapi

A running Dolt SQL server can serve as a Dolt remote 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 this blog article where we announced push support.

port

Default: null

Values: Any integer between 1024 to 49151

Example:

In another shell, I can now clone the database by specifying the DOLT_REMOTE_PASSWORD environment variable and a --user. Note, cloning from DoltHub or DoltLab uses a different authentication method and thus, does not require a user or password.

I now have a cloned copy of the database in the location I cloned to.

read_only

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.

mcp_server

Starts a Dolt MCP HTTP server that will be connected to the started Dolt SQL server when enabling these configuration values. When the MCP server is enabled, your AI agents can call tools against your Dolt database. Additional information is provided in this blog article. Note: MCP server logs respect the log level of the dolt sql-server command, and will be written to the Dolt server log output.

port

Default: 7007

Required

Values: Any integer between 1024 to 49151

user

Default: root

Required

Values: The SQL user the MCP server will use.

password

Optional

Values: The password of the SQL user used by the MCP server.

database

Optional

Values: The specific database the MCP server is connected to.

Example:

In another shell, I can register the MCP server with an agent an it can start using it immediately.

I now have a Dolt SQL server on port 3306 and a Dolt MCP server on port 7007.

system_variables

Dolt features a number of custom system variables and supports many of MySQL's system variables. These variables can be set for a running server using a map of system variable to value in this section of the configuration.

Default: {}

Values: A map of system variable to value.

Example:

I will enable the dolt_show_system_tables system variable which changes the behavior of show tables to include Dolt system tables.

Connecting a client to the server now has that variable set and exhibits the proper behavior:

user_session_vars

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:

jwks

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 our Discord and let us know.

cluster

This section of config.yaml is used to configure "Direct to Standby" or cluster replication. Refer to the documentation for replication for this section of config.yaml. This configuration requires multiple Dolt instances configured so it is out of scope for this article.

System Variables

Dolt defines system variables that you can set in your session via the SET syntax. Many of these can be persisted, so they remain set after a server restart.

A full list of available system variables can be found in the docs on system variables.

PreviousRunning the ServerNextAccess Management

Last updated

Was this helpful?