read_only mode in sql-server not working properly

[wonderix]

When running a dolt database in read_only mode, it's not possible to insert new data using the mysql command line client. The following error is reported: ERROR 1105 (HY000): database server is set to read only mode.
When using the mysql client from the crystal language no error is reported and the row is inserted. Afterwards the changes are visible to the mysql command line client.

It should never be possible to insert new data into a read only dolt database, even with an exotic client.

I' using dolt version 0.41.6 on a Mac M1.

[timsehn]

We agree this should't be possible.

Do you mind including a complete repro? How you started the server and the logs from --loglevel=debug? We are unfamiliar with the mysql client in crystal. This will really help us debug.

Otherwise, we'll play around and see if we can get a repro.

[bpf120]

Hi @wonderix, we'd love to learn more about your use case too. Feel free to swing by our Discord or shoot me an email if you want to share.

[wonderix]

Steps to reproduce the issue

• Setup project (for help have a look at this page)

  brew install crystal # or sudo apt-get install crystal
  mkdir test
  cd test
  shards init
  

• Add this to shards.yml

  dependencies:
  mysql:
      github: crystal-lang/crystal-mysql
  

• Install required libraries

  shards install
  

• Create test.cr with the following content

  require "mysql"
  
  url = "mysql://#{URI.encode_path_segment("b/KXkLAJo3EFS/wj+cVbcQ==")}:#{URI.encode_path_segment("tc2cEcZFuZ2Fjj+Nxd7hog==")}@localhost:3335/default"
  DB.open url do |db|
    db.exec "create table contacts (name varchar(30), age int)"
  end

• Run program

  crystal run test.cr
  

dolt server config

databases:
- path: .
  name: default
behavior:
  read_only: true
user:
  name: b/KXkLAJo3EFS/wj+cVbcQ==
  password: tc2cEcZFuZ2Fjj+Nxd7hog==
listener:
  host: 0.0.0.0
  port: 3335
  tls_key: ...
  tls_cert: ...

[timsehn]

Thanks for the repro. We'll dig into this today.

[timsehn]

So something a little more nuanced is going on:

Shell 1:

$ dolt sql-server --config=config.yaml
'Starting server with Config HP="0.0.0.0:3335"|T="28800000"|R="true"|L="info"
2022-10-03T13:35:08-07:00 INFO [conn 1] NewConnection {DisableClientMultiStatements=false}
2022-10-03T13:35:08-07:00 INFO [conn 1] ConnectionClosed {}
^[[A^[[A^[[B2022-10-03T13:35:25-07:00 INFO [conn 2] NewConnection {DisableClientMultiStatements=false}
2022-10-03T13:35:57-07:00 WARN [conn 2] error running query {connectTime=2022-10-03T13:35:25-07:00, connectionDb=default, error=database server is set to read only mode, query=create table t(c1 int)}

Shell 2:

$ mysql -h 127.0.0.1 --port=3335 -u"b/KXkLAJo3EFS/wj+cVbcQ==" -p"tc2cEcZFuZ2Fjj+Nxd7hog==" 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9-Vitess 

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| default            |
| information_schema |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

mysql> use default
Database changed
mysql> create table t(c1 int);
ERROR 1105 (HY000): database server is set to read only mode

So, it's respecting read-only mode through a standard mysql client. Trying the crystal client now.

[timsehn]

I can repro.

Step 1:

brew install crystal
mkdir test
cd test
shards init

Step 2 (slightly modified shard.yml):

$ cat shard.yml
name: shards
version: 0.1.0

dependencies:
  mysql:
    github: crystal-lang/crystal-mysql
$ shards install 
Resolving dependencies
Fetching https://github.com/crystal-lang/crystal-mysql.git
Fetching https://github.com/crystal-lang/crystal-db.git
Installing db (0.11.0)
Installing mysql (0.14.0)
Writing shard.lock

Step 3:

$ cat test.cr 
require "mysql"

url = "mysql://#{URI.encode_path_segment("b/KXkLAJo3EFS/wj+cVbcQ==")}:#{URI.encode_path_segment("tc2cEcZFuZ2Fjj+Nxd7hog==")}@localhost:3335/default"
DB.open url do |db|
  db.exec "create table contacts (name varchar(30), age int)"
end
$ crystal run test.cr
$

Step 4:

$ mysql -h 127.0.0.1 --port=3335 -u"b/KXkLAJo3EFS/wj+cVbcQ==" -p"tc2cEcZFuZ2Fjj+Nxd7hog=="
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.9-Vitess 

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| default            |
| information_schema |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

mysql> use default
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_default |
+-------------------+
| contacts          |
+-------------------+
1 row in set (0.00 sec)

mysql> exit

[timsehn]

Here's the query in log level debug:

$ dolt sql-server --config=config.yaml --loglevel=debug 
Starting server with Config HP="0.0.0.0:3335"|T="28800000"|R="true"|L="debug"
2022-10-03T14:00:05-07:00 INFO [conn 1] NewConnection {DisableClientMultiStatements=false}
2022-10-03T14:00:05-07:00 DEBUG [conn 1] Starting query {connectTime=2022-10-03T14:00:05-07:00, connectionDb=default, query=create table contacts (name varchar(30), age int)}
2022-10-03T14:00:05-07:00 DEBUG [conn 1] Query finished in 6 ms {connectTime=2022-10-03T14:00:05-07:00, connectionDb=default, query=create table contacts (name varchar(30), age int)}
2022-10-03T14:00:05-07:00 INFO [no conn] kill query: pid 2 {}
2022-10-03T14:00:05-07:00 INFO [conn 1] ConnectionClosed {}

[timsehn]

Also, we agree on the severity. This will be patched immediately once we figure out what is going on.

[timsehn]

Our current working hypothesis is the "prepared statement" path does not check for read-only mode.

[timsehn]

Looks like crystal is always sending Prepared statements even when there are no bind variables. We had a hole in our read-only coverage there so it's good it does :-) We're getting a fix out now and putting it in the next release.

Nice find!

[timsehn]

This is patched and release in 0.50.1. Thanks for the bug report.