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
tls_key: null
tls_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
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:
create table t (
id int primary key,
words varchar(100)
)
log_level
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.
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.
$ grep log_level config.yaml
log_level: info
$ dolt sql-server --config=config.yaml
Starting server with Config HP="localhost:3310"|T="28800000"|R="false"|L="info"|S="/tmp/mysql.sock"
WARN[0000] unix socket set up failed: file already in use: /tmp/mysql.sock
INFO[0000] Server ready. Accepting connections.
WARN[0000] secure_file_priv is set to "", which is insecure.
WARN[0000] Any user with GRANT FILE privileges will be able to read any file which the sql-server process can read.
WARN[0000] Please consider restarting the server with secure_file_priv set to a safe (or non-existent) directory.
INFO[0009] NewConnection DisableClientMultiStatements=false connectionID=1
WARN[0009] error running query connectTime="2024-12-04 13:22:52.439832 -0800 PST m=+9.896056876" connectionDb=config_blog connectionID=1 error="column \"bad_col\" could not be found in any table in scope"
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.
$ emacs config.yaml
$ grep log_level config.yaml
log_level: debug
$ dolt sql-server --config=config.yaml
Starting server with Config HP="localhost:3310"|T="28800000"|R="false"|L="debug"|S="/tmp/mysql.sock"
DEBU[0000] Loading events
WARN[0000] unix socket set up failed: file already in use: /tmp/mysql.sock
INFO[0000] Server ready. Accepting connections.
WARN[0000] secure_file_priv is set to "", which is insecure.
WARN[0000] Any user with GRANT FILE privileges will be able to read any file which the sql-server process can read.
WARN[0000] Please consider restarting the server with secure_file_priv set to a safe (or non-existent) directory.
INFO[0006] NewConnection DisableClientMultiStatements=false connectionID=1
DEBU[0006] Starting query connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionID=1 query="select @@version_comment limit 1"
DEBU[0006] Query finished in 0 ms connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionID=1 query="select @@version_comment limit 1"
DEBU[0011] Starting query connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionID=1 query="SELECT DATABASE()"
DEBU[0011] Query finished in 0 ms connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionID=1 query="SELECT DATABASE()"
DEBU[0011] Starting query connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionDb=config_blog connectionID=1 query="show databases"
DEBU[0011] Query finished in 0 ms connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionDb=config_blog connectionID=1 query="show databases"
DEBU[0011] Starting query connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionDb=config_blog connectionID=1 query="show tables"
DEBU[0011] Query finished in 0 ms connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionDb=config_blog connectionID=1 query="show tables"
DEBU[0011] Starting query connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionDb=config_blog connectionID=1 query="SELECT * FROM `t` LIMIT 0;"
DEBU[0011] Query finished in 0 ms connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionDb=config_blog connectionID=1 query="SELECT * FROM `t` LIMIT 0;"
DEBU[0019] Starting query connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionDb=config_blog connectionID=1 query="select * from t where bad_col=3"
WARN[0019] error running query connectTime="2024-12-04 13:25:18.756126 -0800 PST m=+6.422378084" connectionDb=config_blog connectionID=1 error="column \"bad_col\" could not be found in any table in scope" query="select * from t where bad_col=3"
I now see the bad query being run is select * from t where bad_col=3
.
log_format
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:
text
: Default log format emits logs as text stringsjson
: 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.
$ grep log_format config.yaml
log_format: json
$ dolt sql-server --config=config.yaml
'Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="debug"|S="/tmp/mysql.sock"
{"level":"debug","msg":"Loading events","time":"2025-03-05T09:38:03-08:00"}
{"level":"debug","msg":"privileges.db already exists, not creating root superuser","time":"2025-03-05T09:38:03-08:00"}
{"level":"warning","msg":"unix socket set up failed: file already in use: /tmp/mysql.sock","time":"2025-03-05T09:38:03-08:00"}
{"level":"info","msg":"Server ready. Accepting connections.","time":"2025-03-05T09:38:03-08:00"}
{"level":"warning","msg":"secure_file_priv is set to \"\", which is insecure.","time":"2025-03-05T09:38:03-08:00"}
{"level":"warning","msg":"Any user with GRANT FILE privileges will be able to read any file which the sql-server process can read.","time":"2025-03-05T09:38:03-08:00"}
{"level":"warning","msg":"Please consider restarting the server with secure_file_priv set to a safe (or non-existent) directory.","time":"2025-03-05T09:38:03-08:00"}
behavior
behavior
The behavior
section of config.yaml
defines configuration that determines the way the SQL engine works.
read_only
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
.
$ grep read_only config.yaml
read_only: false
read_only: null
$ emacs config.yaml
$ dolt sql-server --config=config.yaml
Starting server with Config HP="localhost:3310"|T="28800000"|R="false"|L="info"|S="/tmp/mysql.sock"
WARN[0000] unix socket set up failed: file already in use: /tmp/mysql.sock
INFO[0000] Server ready. Accepting connections.
WARN[0000] secure_file_priv is set to "", which is insecure.
WARN[0000] Any user with GRANT FILE privileges will be able to read any file which the sql-server process can read.
WARN[0000] Please consider restarting the server with secure_file_priv set to a safe (or non-existent) directory.
I make an insert in a connected client and it succeeds.
MySQL [config_blog]> insert into t values (0, 'first');
Query OK, 1 row affected (0.006 sec)
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
.
$ emacs config.yaml
$ grep read_only config.yaml
read_only: true
read_only: null
$ dolt sql-server --config=config.yaml
Starting server with Config HP="localhost:3310"|T="28800000"|R="true"|L="info"|S="/tmp/mysql.sock"
WARN[0000] unix socket set up failed: file already in use: /tmp/mysql.sock
INFO[0000] Server ready. Accepting connections.
WARN[0000] secure_file_priv is set to "", which is insecure.
WARN[0000] Any user with GRANT FILE privileges will be able to read any file which the sql-server process can read.
WARN[0000] Please consider restarting the server with secure_file_priv set to a safe (or non-existent) directory.
INFO[0016] NewConnection DisableClientMultiStatements=false connectionID=1
WARN[0016] error running query connectTime="2024-12-04 14:38:05.684674 -0800 PST m=+16.751230334" connectionDb=config_blog connectionID=1 error="database server is set to read only mode"
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.
MySQL [config_blog]> insert into t values (1, 'second');
ERROR 1105 (HY000): database server is set to read only mode
autocommit
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.
$ grep autocommit config.yaml
autocommit: true
$ dolt sql-server --config=config.yaml
Starting server with Config HP="localhost:3310"|T="28800000"|R="false"|L="info"|S="/tmp/mysql.sock"
Now I connect both clients, viewing the state of the table. In client one I see:
MySQL [config_blog]> select * from t;
+----+-------+
| id | words |
+----+-------+
| 0 | first |
+----+-------+
1 row in set (0.001 sec)
In client two I see the same thing:
MySQL [config_blog]> select * from t;
+----+-------+
| id | words |
+----+-------+
| 0 | first |
+----+-------+
1 row in set (0.001 sec)
Back in client one, I insert a value:
MySQL [config_blog]> insert into t values (1, 'second');
Query OK, 1 row affected (0.007 sec)
And I am able to see that value in client two without issuing an explicit transaction commit.
MySQL [config_blog]> select * from t;
+----+--------+
| id | words |
+----+--------+
| 0 | first |
| 1 | second |
+----+--------+
2 rows in set (0.004 sec)
Now, I kill the server with Ctrl-C
and set autocommit
to false in config.yaml
.
$ emacs config.yaml
$ grep autocommit config.yaml
autocommit: false
$ dolt sql-server --config=config.yaml
Starting server with Config HP="localhost:3310"|T="28800000"|R="false"|L="info"|S="/tmp/mysql.sock"
Now I reconnect both clients. I should see this table in both clients:
MySQL [config_blog]> select * from t;
+----+--------+
| id | words |
+----+--------+
| 0 | first |
| 1 | second |
+----+--------+
2 rows in set (0.004 sec)
In client one I make an insert:
MySQL [config_blog]> insert into t values (2, 'third');
Query OK, 1 row affected (0.005 sec)
But that insert is not visible in client two:
MySQL [config_blog]> select * from t;
+----+--------+
| id | words |
+----+--------+
| 0 | first |
| 1 | second |
+----+--------+
2 rows in set (0.001 sec)
I must issue a commit
in client one:
MySQL [config_blog]> commit;
Query OK, 0 rows affected (0.007 sec)
and a begin
in client two. Now I see the insert in client two.
MySQL [config_blog]> begin;
Query OK, 0 rows affected (0.000 sec)
MySQL [config_blog]> select * from t;
+----+--------+
| id | words |
+----+--------+
| 0 | first |
| 1 | second |
| 2 | third |
+----+--------+
3 rows in set (0.001 sec)
disable_client_multi_statements
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 ?
.
MySQL [config_blog]> delimiter '?';
Now, I issue a multi-statement query and it succeeds.
MySQL [config_blog]> insert into t values (3, 'fourth'); update t set words='first modified' where id=0?
Query OK, 1 row affected (0.012 sec)
Query OK, 1 row affected (0.012 sec)
Rows matched: 1 Changed: 1 Warnings: 0
I stop the server using Ctrl-C
. Now, I set the disable_client_multi_statement
to true and restart the server:
$ emacs config.yaml
$ grep multi_statement config.yaml
disable_client_multi_statements: true
$ dolt sql-server --config=config.yaml
Starting server with Config HP="localhost:3310"|T="28800000"|R="false"|L="debug"|S="/tmp/mysql.sock"
I pass in the inverse query and it will fail:
MySQL [config_blog]> delete from t where id=3; update t set words='first' where id=0?
ERROR 1105 (HY000): syntax error at position 33 near 'update'
dolt_transaction_commit
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.
MySQL [config_blog]> call dolt_commit('-Am', 'Manual commit');
+----------------------------------+
| hash |
+----------------------------------+
| rgifn94i58hqov4mdv0efsjju0qpg964 |
+----------------------------------+
1 row in set (0.006 sec)
MySQL [config_blog]> select * from dolt_log;
+----------------------------------+-----------+-----------------+---------------------+---------------------------------+
| commit_hash | committer | email | date | message |
+----------------------------------+-----------+-----------------+---------------------+---------------------------------+
| rgifn94i58hqov4mdv0efsjju0qpg964 | root | root@% | 2024-12-05 00:48:50 | Manual commit |
| do1tvb8g442jvggv4e3nfqp3fmqt0u5a | timsehn | [email protected] | 2024-12-03 19:16:49 | Inіtialіze datа rеposіtory |
+----------------------------------+-----------+-----------------+---------------------+---------------------------------+
2 rows in set (0.001 sec)
After I enable dolt_transaction_commit
and restart the server:
$ emacs config.yaml
$ grep dolt_transaction_commit config.yaml
dolt_transaction_commit: true
$ dolt sql-server --config=config.yaml
Starting server with Config HP="localhost:3310"|T="28800000"|R="false"|L="debug"|S="/tmp/mysql.sock"
Every write statement becomes a Dolt commit:
MySQL [config_blog]> insert into t values (4, 'dolt commit');
Query OK, 1 row affected (0.009 sec)
MySQL [config_blog]> select * from dolt_log;
+----------------------------------+------------+-----------------+---------------------+---------------------------------+
| commit_hash | committer | email | date | message |
+----------------------------------+------------+-----------------+---------------------+---------------------------------+
| vmikac4f7s4395v0v43dtfcbhrmtmo41 | configblog | [email protected] | 2024-12-05 00:51:32 | Transaction commit |
| rgifn94i58hqov4mdv0efsjju0qpg964 | root | root@% | 2024-12-05 00:48:50 | Manual commit |
| do1tvb8g442jvggv4e3nfqp3fmqt0u5a | timsehn | [email protected] | 2024-12-03 19:16:49 | Inіtialіze datа rеposіtory |
+----------------------------------+------------+-----------------+---------------------+---------------------------------+
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
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.
[config_blog]> CREATE EVENT make_dolt_commits ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE DO CALL dolt_commit('-A', '--allow-empty', '-m', 'Commit created using an event');
Query OK, 0 rows affected (0.011 sec)
Now, we wait a minute and in the logs we see that the event has fired, as expected.
DEBU[0090] Executing event config_blog.make_dolt_commits, seconds until execution: -28.759227
DEBU[0090] executing event config_blog.make_dolt_commits query="CALL dolt_commit('-A', '--allow-empty', '-m', 'Commit created using an event')"
We can inspect the Dolt log and see indeed the commit succeeded.
MySQL [config_blog]> select * from dolt_log;
+----------------------------------+------------+-----------------+---------------------+---------------------------------+
| commit_hash | committer | email | date | message |
+----------------------------------+------------+-----------------+---------------------+---------------------------------+
| im7qq2ja3nfqnc75khtuli8krla3s3fm | root | root@% | 2024-12-05 20:19:04 | Commit created using an event |
| vmikac4f7s4395v0v43dtfcbhrmtmo41 | configblog | [email protected] | 2024-12-05 00:51:32 | Transaction commit |
| rgifn94i58hqov4mdv0efsjju0qpg964 | root | root@% | 2024-12-05 00:48:50 | Manual commit |
| do1tvb8g442jvggv4e3nfqp3fmqt0u5a | timsehn | [email protected] | 2024-12-03 19:16:49 | Inіtialіze datа rеposіtory |
+----------------------------------+------------+-----------------+---------------------+---------------------------------+
Now stop the server and stop event execution using config.yaml
.
$ emacs config.yaml
$ grep event_scheduler config.yaml
event_scheduler: "OFF"
$ dolt sql-server --config=config.yaml
Starting server with Config HP="localhost:3310"|T="28800000"|R="false"|L="debug"|S="/tmp/mysql.sock"
We do not see any more commits in the debug log or in the Dolt log.
auto_gc_behavior
auto_gc_behavior
The auto_gc_behavior
subsection of behavior
controls the configuration of automatic garbage collection.
enable
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:
MySQL [auto_gc_test]> call dolt_gc();
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0,04 sec)
MySQL [auto_gc_test]> select 1;
ERROR 1105 (HY000): this connection was established when this server performed an online garbage collection. this connection can no longer be used. please reconnect.
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.
% cat config.yaml
behavior: { auto_gc_behavior: { enable: true } }
% dolt sql-server --config=config.yaml >/dev/null &
Here we see the impact of running GC is different:
> call dolt_gc();
+--------+
| status |
+--------+
| 0 |
+--------+
1 row in set (0,04 sec)
> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0,01 sec)
We can cause an automatic GC by inserting some data.
mysql> CREATE TABLE vals (
-> i1 int, i2 int, i3 int, i4 int, i5 int, i6 int,
-> KEY(i1, i2, i3, i4, i5, i6),
-> KEY(i1, i3, i4, i5, i6, i2),
-> KEY(i1, i4, i5, i6, i2, i3),
-> KEY(i1, i5, i6, i2, i3, i4),
-> KEY(i1, i6, i2, i3, i4, i5)
-> );
Query OK, 0 rows affected, 4 warnings (0,01 sec)
mysql> DELIMITER //
mysql> CREATE PROCEDURE insertn(n INT)
-> BEGIN
-> SET @i = 0;
-> REPEAT
-> SET @i = @i + 1;
-> INSERT INTO vals VALUES (rand()*65536, rand()*65536, rand()*65536, rand()*65536, rand()*65536, rand()*65536);
-> UNTIL @i > n END REPEAT;
-> END //
Query OK, 0 rows affected (0,01 sec)
mysql> DELIMITER ;
mysql> CALL insertn(8192);
Query OK, 1 row affected (3,52 sec)
We will see a log line from the server like:
INFO[0257] sqle/auto_gc: Successfully completed auto GC of database auto_gc_test in 3.508769959s
and if we run a GC manually we can see that there is nothing to collect:
mysql> call dolt_gc();
ERROR 1105 (HY000): no changes since last gc
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:
% cat config.yaml
behavior: { auto_gc_behavior: { enable: true, archive_level: 1 } }
% dolt sql-server --config config.yaml
listener
listener
The listener section of config.yaml
is configuration for the SQL server transport layer.
host
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
.
$ emacs config.yaml
$ grep host: config.yaml
host: 127.0.0.1
host: null
$ dolt sql-server --config=config.yaml
Starting server with Config HP="127.0.0.1:3310"|T="28800000"|R="false"|L="debug"
You notice the starting server message now says 127.0.0.1
instead of localhost
.
port
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 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.
$ grep port config.yaml
port: 3310
require_secure_transport: null
port: -1
port: null
$ dolt sql-server --config=config.yaml
Starting server with Config HP="localhost:3310"|T="28800000"|R="false"|L="debug"|S="/tmp/mysql.sock"
max_connections
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.
$ grep max_connections config.yaml
max_connections: 1
$ dolt sql-server --config=config.yaml
Starting server with Config HP="127.0.0.1:3310"|T="28800000"|R="false"|L="debug"
I connect with a client one with no issue.
$ mysql -h 127.0.0.1 -P 3310 -u root
WARNING: option --ssl-verify-server-cert is disabled, because of an insecure passwordless login.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.33 Dolt
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
If I connect with another client, it will hang for 1 minute before giving up (see max_connections_timeout_millis
)
$ mysql -h 127.0.0.1 -P 3310 -u root
WARNING: option --ssl-verify-server-cert is disabled, because of an insecure passwordless login.
If I exit client one:
MySQL [(none)]> exit
Bye
Client two connects.
$ mysql -h 127.0.0.1 -P 3310 -u root
WARNING: option --ssl-verify-server-cert is disabled, because of an insecure passwordless login.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 8.0.33 Dolt
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
back_log
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:
$ cat config.yaml
listener:
max_connections: 42
back_log: 5
max_connections_timeout_millis: 7000
$ dolt sql-server --config=config.yaml
Starting server with Config HP="127.0.0.1:3310"|T="28800000"|R="false"|L="debug"
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
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
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.
$ emacs config.yaml
$ grep read_timeout config.yaml
read_timeout_millis: 1
$ dolt sql-server --config=config.yaml
Starting server with Config HP="127.0.0.1:3310"|T="1"|R="false"|L="debug"
Now, I'll issue a select sleep(5)
in a client which occupies the client so it does not send packets.
$ mysql -h 127.0.0.1 -P 3310 -u root
WARNING: option --ssl-verify-server-cert is disabled, because of an insecure passwordless login.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 8.0.33
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> select sleep(5);
ERROR 2006 (HY000): Server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: *** NONE ***
ERROR 1105 (HY000): row read wait bigger than connection timeout
The query fails and the connection is killed.
write_timeout_millis
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.
tls_key
tls_key
tls_key
, tls_cert
, and require_secure_transport
as 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_cert
tls_key
, tls_cert
, and require_secure_transport
as used together and are covered in this article. tls_cert
is the path to the ket 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
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/
.
tls_key: "/Users/timsehn/dolthub/git/dolt/go/libraries/doltcore/servercfg/tes\
tdata/chain_key.pem"
tls_cert: "/Users/timsehn/dolthub/git/dolt/go/libraries/doltcore/servercfg/te\
stdata/chain_cert.pem"
require_secure_transport: true
Now I connect and run status and I can see I am on a SSL connection.
$ mysql -h 127.0.0.1 -P 3310 -u root
WARNING: option --ssl-verify-server-cert is disabled, because of an insecure passwordless login.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.33 Dolt
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> source
ERROR: Usage: \. <filename> | source <filename>
MySQL [(none)]> status
--------------
mysql from 11.6.2-MariaDB, client 15.2 for osx10.20 (arm64) using EditLine wrapper
Connection id: 1
Current database:
Current user: root@%
SSL: Cipher in use is TLS_AES_128_GCM_SHA256, cert is UNKNOWN
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MySQL
Server version: 8.0.33 Dolt
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3310
--------------
allow_cleartext_passwords
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 theDolt SQL server.
$ grep log_level config.yaml
log_level: debug
$ grep max_log config.yaml
max_logged_query_len: 10
$ dolt sql-server --config=config.yaml
Starting server with Config HP="127.0.0.1:3310"|T="28800000"|R="false"|L="debug"
Now, all queries are truncated to 10 characters in the logs:
DEBU[0020] Starting query connectTime="2024-12-06 14:21:58.139943 -0800 PST m=+3.826578251" connectionDb=config_blog connectionID=1 query="select * f..."
data_dir
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
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
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
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
.
$ grep data_dir config.yaml
data_dir: /tmp
$ dolt sql-server --config=config.yaml
Starting server with Config HP="127.0.0.1:3310"|T="28800000"|R="false"|L="debug"
This is a new directory so there are no databases in it.
$ mysql -h 127.0.0.1 -P 3310 -u root
WARNING: option --ssl-verify-server-cert is disabled, because of an insecure passwordless login.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.33 Dolt
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.000 sec)
After we create a database named tmp
:
MySQL [(none)]> create database tmp;
Query OK, 1 row affected (0.135 sec)
We can see the .dolt
directory in /tmp/tmp
:
$ ls -al /tmp/tmp
total 0
drwxr-xr-x 3 timsehn wheel 96 Dec 6 14:26 .
drwxrwxrwt 7 root wheel 224 Dec 6 14:26 ..
drwxr-xr-x 7 timsehn wheel 224 Dec 6 14:26 .dolt
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.
$ grep -3 metrics config.yaml
cfg_dir: .doltcfg
metrics:
labels: {}
host: localhost
port: 11111
$ dolt sql-server --config config.yaml
Starting server with Config HP="127.0.0.1:3310"|T="28800000"|R="false"|L="debug"
INFO[0000] Server ready. Accepting connections.
Then, I can access the metrics by making an HTTP request to http://localhost:11111/metrics
$ curl http://localhost:11111/metrics | HEAD -n 10
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 6041 0 6041 0 0 4776k 0 --:--:-- --:--:-- --:--:-- 5899k
# HELP dss_concurrent_connections Number of clients concurrently connected to this instance of dolt sql server
# TYPE dss_concurrent_connections gauge
dss_concurrent_connections 0
# HELP dss_concurrent_queries Number of queries concurrently being run on this instance of dolt sql server
# TYPE dss_concurrent_queries gauge
dss_concurrent_queries 0
# HELP dss_connects Count of server connects
# TYPE dss_connects counter
dss_connects 0
# HELP dss_disconnects Count of server disconnects
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.
$ grep -3 metrics config.yaml
cfg_dir: .doltcfg
metrics:
host: localhost
port: 11111
labels: {"process": "dolt-sql-server"}
$ dolt sql-server --config config.yaml
Starting server with Config HP="127.0.0.1:3310"|T="28800000"|R="false"|L="debug"
INFO[0000] Server ready. Accepting connections.
Now all the metrics emitted are labeled with process="dolt-sql-server"
.
$ curl http://localhost:11111/metrics | HEAD -n 10
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 6425 0 6425 0 0 3115k 0 --:--:-- --:--:-- --:--:-- 3137k
# HELP dss_concurrent_connections Number of clients concurrently connected to this instance of dolt sql server
# TYPE dss_concurrent_connections gauge
dss_concurrent_connections{process="dolt-sql-server"} 0
# HELP dss_concurrent_queries Number of queries concurrently being run on this instance of dolt sql server
# TYPE dss_concurrent_queries gauge
dss_concurrent_queries{process="dolt-sql-server"} 0
# HELP dss_connects Count of server connects
# TYPE dss_connects counter
dss_connects{process="dolt-sql-server"} 0
# HELP dss_disconnects Count of server disconnects
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:
$ grep -3 remotesapi config.yaml
port: 11111
labels: {"process": "dolt-sql-server"}
remotesapi:
port: 22222
read_only: null
$ dolt sql-server --config config.yaml
Starting server with Config HP="127.0.0.1:3310"|T="28800000"|R="false"|L="debug"
INFO[0000] Server ready. Accepting connections.
INFO[0000] Starting http server on :22222
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.
$ DOLT_REMOTE_PASSWORD= dolt clone --user root http://localhost:22222/config_blog
cloning http://localhost:22222/config_blog
$ cd config_blog
$ dolt log
commit im7qq2ja3nfqnc75khtuli8krla3s3fm (HEAD -> main, remotes/origin/main)
Author: root <root@%>
Date: Thu Dec 05 12:19:04 -0800 2024
Commit created using an event
commit vmikac4f7s4395v0v43dtfcbhrmtmo41
Author: configblog <[email protected]>
Date: Wed Dec 04 16:51:32 -0800 2024
Transaction commit
commit rgifn94i58hqov4mdv0efsjju0qpg964
Author: root <root@%>
Date: Wed Dec 04 16:48:50 -0800 2024
Manual commit
commit do1tvb8g442jvggv4e3nfqp3fmqt0u5a
Author: timsehn <[email protected]>
Date: Tue Dec 03 11:16:49 -0800 2024
Inіtialіze datа rеposіtory
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.
$ grep -3 remotesapi config.yaml
port: 11111
labels: {"process": "dolt-sql-server"}
remotesapi:
port: 22222
read_only: true
$ dolt sql-server --config config.yaml
Starting server with Config HP="127.0.0.1:3310"|T="28800000"|R="false"|L="debug"
INFO[0000] Starting http server on :22222
If I make a change and attempt to push it will fail.
$ dolt sql -q "insert into t values (6, 'Can I push this');
dquote> "
Query OK, 1 row affected (0.00 sec)
$ dolt sql -q "select * from t;"
+----+-----------------+
| id | words |
+----+-----------------+
| 0 | first modified |
| 1 | second |
| 2 | third |
| 3 | fourth |
| 4 | dolt commit |
| 6 | Can I push this |
+----+-----------------+
$ dolt commit -am "Added row to push"
commit 0vkmfbrt3d1uljrh0ie0mdikoc9tcsss (HEAD -> main)
Author: timsehn <[email protected]>
Date: Wed Dec 11 14:07:35 -0800 2024
Added row to push
$ DOLT_REMOTE_PASSWORD= dolt push --user root origin main
- Uploading...unknown push error; rpc error: code = PermissionDenied desc = this server only provides read-only access
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.
$ grep system_variables config.yaml
system_variables: {"dolt_show_system_tables": 1}
Connecting a client to the server now has that variable set and exhibits the proper behavior:
MySQL [config_blog]> select @@dolt_show_system_tables;
+---------------------------+
| @@dolt_show_system_tables |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.000 sec)
MySQL [config_blog]> show tables;
+------------------------------+
| Tables_in_config_blog |
+------------------------------+
| dolt_branches |
| dolt_commit_ancestors |
| dolt_commit_diff_t |
| dolt_commits |
| dolt_conflicts |
| dolt_conflicts_t |
| dolt_constraint_violations |
| dolt_constraint_violations_t |
| dolt_diff_t |
| dolt_history_t |
| dolt_log |
| dolt_remote_branches |
| dolt_remotes |
| dolt_status |
| dolt_workspace_t |
| t |
+------------------------------+
16 rows in set (0.000 sec)
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.
$ grep -3 user_session config.yaml
system_variables: {}
user_session_vars:
- name: "root"
vars:
"dolt_show_system_tables": 1
And then in a connected client with user root
, the server now has that variable set and exhibits the proper behavior:
MySQL [config_blog]> select user();
+--------+
| user() |
+--------+
| root@% |
+--------+
1 row in set (0.000 sec)
MySQL [config_blog]> select @@dolt_show_system_tables;
+---------------------------+
| @@dolt_show_system_tables |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.000 sec)
MySQL [config_blog]> show tables;
+------------------------------+
| Tables_in_config_blog |
+------------------------------+
| dolt_branches |
| dolt_commit_ancestors |
| dolt_commit_diff_t |
| dolt_commits |
| dolt_conflicts |
| dolt_conflicts_t |
| dolt_constraint_violations |
| dolt_constraint_violations_t |
| dolt_diff_t |
| dolt_history_t |
| dolt_log |
| dolt_remote_branches |
| dolt_remotes |
| dolt_status |
| dolt_workspace_t |
| t |
+------------------------------+
16 rows in set (0.000 sec)
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 theSET
syntax. Many of these can be persisted, so they remain set after
a server restart.
set @@dolt_transaction_commit = 1;
A full list of available system variables can be found in the docs on system variables.
Last updated
Was this helpful?