Serialization crashes on dump if there's no collections

[paoliniluis]

Describe the bug
When trying to create a dump without using "--user" flags, the process exits with an error

Logs
With PostgreSQL:

2021-07-07 21:28:08,395 INFO serialization.cmd :: BEGIN DUMP to /target via user null org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" Position: 36 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431) at clojure.java.jdbc$execute_query_with_params.invokeStatic(jdbc.clj:1090) at clojure.java.jdbc$execute_query_with_params.invoke(jdbc.clj:1084) at clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1113) at clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1093) at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1182) at clojure.java.jdbc$query.invoke(jdbc.clj:1144) at toucan.db$query.invokeStatic(db.clj:288) at toucan.db$query.doInvoke(db.clj:284) at clojure.lang.RestFn.invoke(RestFn.java:410) at toucan.db$simple_select.invokeStatic(db.clj:394) at toucan.db$simple_select.invoke(db.clj:383) at toucan.db$select.invokeStatic(db.clj:662) at toucan.db$select.doInvoke(db.clj:656) at clojure.lang.RestFn.invoke(RestFn.java:423) at metabase_enterprise.serialization.cmd$select_collections.invokeStatic(cmd.clj:107) at metabase_enterprise.serialization.cmd$select_collections.invoke(cmd.clj:92) at metabase_enterprise.serialization.cmd$dump.invokeStatic(cmd.clj:144) at metabase_enterprise.serialization.cmd$dump.invoke(cmd.clj:116) at metabase_enterprise.serialization.cmd$dump.invokeStatic(cmd.clj:119) at metabase_enterprise.serialization.cmd$dump.invoke(cmd.clj:116) at clojure.lang.Var.invoke(Var.java:388) at metabase.cmd$dump.invokeStatic(cmd.clj:161) at metabase.cmd$dump.doInvoke(cmd.clj:154) at clojure.lang.RestFn.invoke(RestFn.java:423) at metabase.cmd$dump.invokeStatic(cmd.clj:157) at metabase.cmd$dump.invoke(cmd.clj:154) at clojure.lang.AFn.applyToHelper(AFn.java:154) at clojure.lang.RestFn.applyTo(RestFn.java:132) at clojure.core$apply.invokeStatic(core.clj:665) at clojure.core$apply.invoke(core.clj:660) at metabase.cmd$run_cmd$fn__82234.invoke(cmd.clj:191) at metabase.cmd$run_cmd.invokeStatic(cmd.clj:191) at metabase.cmd$run_cmd.invoke(cmd.clj:187) at clojure.lang.Var.invoke(Var.java:388) at metabase.core$run_cmd.invokeStatic(core.clj:149) at metabase.core$run_cmd.invoke(core.clj:147) at metabase.core$_main.invokeStatic(core.clj:171) at metabase.core$_main.doInvoke(core.clj:166) at clojure.lang.RestFn.applyTo(RestFn.java:137) at metabase.core.main(Unknown Source) Command failed with exception: ERROR: syntax error at or near ")" Position: 36

With MySQL

2021-07-07 22:53:45,766 INFO serialization.cmd :: BEGIN DUMP to /target via user null java.sql.SQLSyntaxErrorException: (conn=131) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND `archived` = FALSE)' at line 1 at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62) at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153) at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274) at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229) at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149) at org.mariadb.jdbc.ClientSidePreparedStatement.executeQuery(ClientSidePreparedStatement.java:163) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431) at clojure.java.jdbc$execute_query_with_params.invokeStatic(jdbc.clj:1090) at clojure.java.jdbc$execute_query_with_params.invoke(jdbc.clj:1084) at clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1113) at clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1093) at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1182) at clojure.java.jdbc$query.invoke(jdbc.clj:1144) at toucan.db$query.invokeStatic(db.clj:288) at toucan.db$query.doInvoke(db.clj:284) at clojure.lang.RestFn.invoke(RestFn.java:410) at toucan.db$simple_select.invokeStatic(db.clj:394) at toucan.db$simple_select.invoke(db.clj:383) at toucan.db$select.invokeStatic(db.clj:662) at toucan.db$select.doInvoke(db.clj:656) at clojure.lang.RestFn.invoke(RestFn.java:423) at metabase_enterprise.serialization.cmd$select_collections.invokeStatic(cmd.clj:107) at metabase_enterprise.serialization.cmd$select_collections.invoke(cmd.clj:92) at metabase_enterprise.serialization.cmd$dump.invokeStatic(cmd.clj:144) at metabase_enterprise.serialization.cmd$dump.invoke(cmd.clj:116) at metabase_enterprise.serialization.cmd$dump.invokeStatic(cmd.clj:119) at metabase_enterprise.serialization.cmd$dump.invoke(cmd.clj:116) at clojure.lang.Var.invoke(Var.java:388) at metabase.cmd$dump.invokeStatic(cmd.clj:161) at metabase.cmd$dump.doInvoke(cmd.clj:154) at clojure.lang.RestFn.invoke(RestFn.java:423) at metabase.cmd$dump.invokeStatic(cmd.clj:157) at metabase.cmd$dump.invoke(cmd.clj:154) at clojure.lang.AFn.applyToHelper(AFn.java:154) at clojure.lang.RestFn.applyTo(RestFn.java:132) at clojure.core$apply.invokeStatic(core.clj:665) at clojure.core$apply.invoke(core.clj:660) at metabase.cmd$run_cmd$fn__82234.invoke(cmd.clj:191) at metabase.cmd$run_cmd.invokeStatic(cmd.clj:191) at metabase.cmd$run_cmd.invoke(cmd.clj:187) at clojure.lang.Var.invoke(Var.java:388) at metabase.core$run_cmd.invokeStatic(core.clj:149) at metabase.core$run_cmd.invoke(core.clj:147) at metabase.core$_main.invokeStatic(core.clj:171) at metabase.core$_main.doInvoke(core.clj:166) at clojure.lang.RestFn.applyTo(RestFn.java:137) at metabase.core.main(Unknown Source) Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND `archived` = FALSE)' at line 1 at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:177) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321) at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220) ... 43 more Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND `archived` = FALSE)' at line 1 at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1688) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1550) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1513) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:318) ... 44 more Command failed with exception: (conn=131) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND `archived` = FALSE)' at line 1

To Reproduce
Steps to reproduce the behavior:

1. Create a Metabase instance
2. try to do a dump without any user flags
3. see the error

Expected behavior
The dump works or we give our users a warning, but we don't fail with a SQL error

Screenshots
Logs attached

Information about your Metabase Installation:

• Your browser and the version: Brave latest
• Your operating system: Ubuntu LTS latest
• Your databases: None
• Metabase version: .39.3
• Metabase hosting environment: Docker
• Metabase internal database: tried with both MySQL and Postgres

Severity
It breaks serialization if not using a --user, so users that are not using this flag have their serialization process broken

Additional context
Reported by a person on a trial
EDIT: just saw the DB logs and it says

postgres_origin         | 2021-07-08 13:44:02.751 UTC [50] ERROR:  syntax error at or near ")" at character 36
postgres_origin         | 2021-07-08 13:44:02.751 UTC [50] STATEMENT:  SELECT * FROM "collection" WHERE (() AND "archived" = FALSE)

Also, when running this script that we have in our documentation the process fails even with --user flag

create_users()
{
    ADMIN_EMAIL=${MB_ADMIN_EMAIL:-admin@metabase.local}
    ADMIN_PASSWORD=${MB_ADMIN_PASSWORD:-Metapass123}

    METABASE_HOST=${1}
    METABASE_PORT=${2}

    echo "⌚︎ Waiting for Metabase to start"
    while (! curl -s -m 5 http://${METABASE_HOST}:${METABASE_PORT}/api/session/properties -o /dev/null); do sleep 5; done

    echo "😎 Creating admin user"

    SETUP_TOKEN=$(curl -s -m 5 -X GET \
        -H "Content-Type: application/json" \
        http://${METABASE_HOST}:${METABASE_PORT}/api/session/properties \
        | jq -r '.["setup-token"]'
    )

    MB_TOKEN=$(curl -s -X POST \
        -H "Content-type: application/json" \
        http://${METABASE_HOST}:${METABASE_PORT}/api/setup \
        -d '{
        "token": "'${SETUP_TOKEN}'",
        "user": {
            "email": "'${ADMIN_EMAIL}'",
            "first_name": "Metabase",
            "last_name": "Admin",
            "password": "'${ADMIN_PASSWORD}'"
        },
        "prefs": {
            "allow_tracking": false,
            "site_name": "Metawhat"
        }
    }' | jq -r '.id')


    echo -e "\n👥 Creating some basic users: "
    curl -s "http://${METABASE_HOST}:${METABASE_PORT}/api/user" \
        -H 'Content-Type: application/json' \
        -H "X-Metabase-Session: ${MB_TOKEN}" \
        -d '{"first_name":"Basic","last_name":"User","email":"basic@somewhere.com","login_attributes":{"region_filter":"WA"},"password":"'${ADMIN_PASSWORD}'"}'

    curl -s "http://${METABASE_HOST}:${METABASE_PORT}/api/user" \
        -H 'Content-Type: application/json' \
        -H "X-Metabase-Session: ${MB_TOKEN}" \
        -d '{"first_name":"Basic 2","last_name":"User","email":"basic2@somewhere.com","login_attributes":{"region_filter":"CA"},"password":"'${ADMIN_PASSWORD}'"}'

    echo -e "\n👥 Basic users created!"
}

# remove any existing containers
docker rm --force metabase-origin postgres-origin metabase-target postgres-target
docker volume prune --force

# export METABASE_VERSION=v1.36.7         # version from example - works ok
export METABASE_VERSION=v1.39.3       # version in use - errors on dump
# export METABASE_VERSION=v1.39.4       # latest version - errors on dump

echo "Testing serialisation with metabase-enterprise:${METABASE_VERSION}"
echo "======================================================"

docker network create metanet

docker run --rm -d --name postgres-origin \
    -p 5433:5432 \
    -e POSTGRES_USER=metabase \
    -e POSTGRES_PASSWORD=knockknock \
    --network metanet \
    postgres:13.3-alpine

docker run --rm -d --name metabase-origin \
    -p 5001:3000 \
    -e MB_DB_TYPE=postgres \
    -e MB_DB_DBNAME=metabase \
    -e MB_DB_PORT=5432 \
    -e MB_DB_USER=metabase \
    -e MB_DB_PASS=knockknock \
    -e MB_DB_HOST=postgres-origin \
    --network metanet \
    metabase/metabase-enterprise:${METABASE_VERSION}

create_users localhost 5001

# target instance

docker run --rm -d --name postgres-target \
    -p 5434:5432 \
    -e POSTGRES_USER=metabase \
    -e POSTGRES_PASSWORD=knockknock \
    --network metanet \
    postgres:13.3-alpine

docker run --rm -d --name metabase-target \
    -p 5002:3000 \
    -e MB_DB_TYPE=postgres \
    -e MB_DB_DBNAME=metabase \
    -e MB_DB_PORT=5432 \
    -e MB_DB_USER=metabase \
    -e MB_DB_PASS=knockknock \
    -e MB_DB_HOST=postgres-target \
    --network metanet \
    metabase/metabase-enterprise:${METABASE_VERSION}

create_users localhost 5002

# try an export
METABASE_DUMP_DIR=metabase-serialisation-test

rm -r ${METABASE_DUMP_DIR}
mkdir ${METABASE_DUMP_DIR}          # Metabase creates it with root ownership if we don't create it ourselves
chmod 777 ${METABASE_DUMP_DIR}

docker run --rm --name metabase-dump --network metanet \
    -e MB_DB_CONNECTION_URI="postgres://postgres-origin:5432/metabase?user=metabase&password=knockknock" \
    -e MUID=$(id -u) \
    -e MGID=$(id -g) \
    -v "$PWD/${METABASE_DUMP_DIR}:/target" metabase/metabase-enterprise:${METABASE_VERSION} "dump /target --user admin@metabase.local"

but when doing the process raising the following docker-compose file and then doing the steps manually it works with the --user flag:

version: '3.7'
services:
  metabase_origin:
    image: metabase/metabase-enterprise:v1.39.3
    container_name: metabase_origin
    volumes: 
      - $PWD/metabase_dump:/target
    ports:
      - 3001:3000
    environment: 
      - "MB_DB_DBNAME=metabase"
      - "MB_DB_USER=metabase"
      - "MB_DB_PASS=mysecretpassword"
      - "MB_DB_PORT=5432"
      - "MB_DB_HOST=postgres"
      - "MB_DB_TYPE=postgres"
    networks: 
      - metanet1
    depends_on: 
      - postgres_origin
  postgres_origin:
    image: postgres:13.3-alpine
    container_name: postgres_origin
    hostname: postgres
    environment:
      - "POSTGRES_USER=metabase"
      - "POSTGRES_DB=metabase"
      - "POSTGRES_PASSWORD=mysecretpassword"
    volumes:
      - $PWD/postgres_origin:/var/lib/postgresql/data
    networks: 
      - metanet1
  metabase_destination:
    image: metabase/metabase-enterprise:v1.39.3
    container_name: metabase_destination
    volumes: 
      - $PWD/metabase_dump:/target
    ports:
      - 3002:3000
    environment: 
      - "MB_DB_DBNAME=metabase"
      - "MB_DB_USER=metabase"
      - "MB_DB_PASS=mysecretpassword"
      - "MB_DB_PORT=5432"
      - "MB_DB_TYPE=postgres"
      - "MB_DB_HOST=postgres"
    networks: 
      - metanet2
    depends_on: 
      - postgres_destination
  postgres_destination:
    image: postgres:13.3-alpine
    container_name: postgres_destination
    hostname: postgres
    environment:
      - "POSTGRES_USER=metabase"
      - "POSTGRES_DB=metabase"
      - "POSTGRES_PASSWORD=mysecretpassword"
    volumes:
      - $PWD/postgres_destination:/var/lib/postgresql/data
    networks: 
      - metanet2
networks: 
  metanet1:
    driver: bridge
  metanet2:
    driver: bridge

[jeff-bruemmer]

Regarding the learn article @paoliniluis mentioned: I've updated the serialization article, followed the tutorial, and verified that it works, or "it works on my machine".

[flamber]

This is caused by lack of non-PC collections. If just a single collection is created, then it works.
Reproduce:

1. Setup a source: java -DMB_DB_FILE=source.db -jar metabase.jar
2. Only complete the setup - don't do anything else
3. Serialization dump: java -DMB_DB_FILE=source.db -jar metabase.jar dump source - fails with:

...
2021-07-27 13:33:07,922 INFO serialization.cmd :: BEGIN DUMP to source via user null
org.h2.jdbc.JdbcSQLException: Data conversion error converting "()"; SQL statement:
SELECT * FROM "COLLECTION" WHERE (() AND "ARCHIVED" = FALSE) [22018-197]
...

4. Run command in step 1 again and now create a collection in root
5. Run command in step 3 again and now it completes the dump correctly:

...
2021-07-27 13:35:30,062 INFO serialization.cmd :: BEGIN DUMP to source via user null
2021-07-27 13:35:30,474 INFO serialization.cmd :: END DUMP to source via user null

[jeff303]

Should be fixed by commit 1 under #17388