Dolt backup restore drops user root, prevents access to other databases.

[coffeegoddd]

Using Dolt v1.2.2. Attempting to restore one database of a Dolt SQL server results in the root user being dropped, which prevents clients from interacting with other databases.

Steps to repro:

# start server from parent directory
mkdir test
cd test
dolt sql-server -l debug &

# connect with client from other directory
cd ..
mkdir alt
cd alt
mysql --host 0.0.0.0 --port 3306 -uroot

# create two dbs
mysql> create database db1;
mysql> create database db2;

# add table, rows, and user to db1
mysql> create table t1 (pk int, c1 varchar(55), primary key (pk));
mysql> insert into t1 (pk, c1) values (1, 'foo');
mysql> insert into t1 (pk, c1) values (2, 'bar');
mysql> insert into t1 (pk, c1) values (3, 'baz');
mysql> create user tester;
mysql> grant all privileges on db1.* to tester;

# disconnect client
mkdir -p backups/test

# reconnect as root
mysql --host 0.0.0.0 --port 3306 -uroot

# add backup
mysql> use db1;
mysql> call dolt_backup('add', 'local-backup', 'file:///Users/dustin/doltdbs/alt/backup/test');

# make changes to the working set
mysql> insert into t1 (pk, c1) values (4, 'aaa');
mysql> insert into t1 (pk, c1) values (5, 'bbb');
mysql> insert into t1 (pk, c1) values (6, 'ccc');

# sync the backup
mysql> call dolt_backup('sync', 'local-backup');

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
+--------------------+
4 rows in set (0.01 sec)

# disconnect client
# stop the sql server

cd ../test
ls -a
.  ..  .doltcfg  db1  db2

# attempt to restore db1 from backup
dolt backup restore file:///Users/dustin/doltdbs/alt/backup/test db1 
data repository already exists: db1

# remove db1
rm -rf db1

# attempt restore again
dolt backup restore file:///Users/dustin/doltdbs/alt/backup/test db1
ls -a
.  ..  .doltcfg  db1  db2

# start server
dolt sql-server --host 0.0.0.0 -l debug &

cd ../alt

# attempt to reconnect client as root
mysql --host 0.0.0.0 --port 3306 -uroot
ERROR 1045 (28000): User not found 'root'

# connect as tester
mysql --host 0.0.0.0 --port 3306 -utester

# show data
mysql> use db1;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+------+
| pk   | c1   |
+------+------+
|    1 | foo  |
|    2 | bar  |
|    3 | baz  |
|    4 | aaa  |
|    5 | bbb  |
|    6 | ccc  |
+------+------+
6 rows in set (0.00 sec)

# show databases
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
+--------------------+
2 rows in set (0.01 sec)

[timsehn]

So i think if that second restart does dolt sql-server --host 0.0.0.0 -l debug -u root & instead, this will work.

Related bug:

#5759

This disappearing root user is dumb. @Hydrocharged why did we do it in the first place?

[coffeegoddd]

@timsehn you're right, restarting the server with that -u root fixes this, thanks.

[timsehn]

Going to resolve in favor of the other issue.

[Hydrocharged]

Late to the party, but we chose that method as the default root user has no password. So someone that doesn't manually specify their own admin account would expose their database to everyone. The root user isn't quite dropped, as they don't really "exist" in the first place. If we don't have any users to read from, then we accept any connections that specify a root username.