Weird syntax error only for sqlx

[lynn2910]

Bug Description

For a project, I want to run a query at the beginning that creates all the tables that may not exist, but, when I run it with the MariaDB shell or another tool, the code runs correctly, but it differs for sqlx that found a syntax error

Minimal Reproduction

My file src/assets/sql/init.sql:

CREATE TABLE IF NOT EXISTS `guilds` (
    `id` VARCHAR(32) NOT NULL,
    `lang` VARCHAR(10) NOT NULL DEFAULT "fr-FR",
    `prefix` VARCHAR(5) NOT NULL DEFAULT "=",
    `tos_accepted` boolean NOT NULL DEFAULT 0,
    `xp` boolean DEFAULT false,
    `agnus` boolean DEFAULT false,
    `ghostping` boolean NOT NULL DEFAULT false,
    `sapphire` boolean NOT NULL DEFAULT false COMMENT 'premium system',
    PRIMARY KEY (`id`)
    );

CREATE TABLE IF NOT EXISTS `guild_functionnalities` (
    `id` VARCHAR(32) NOT NULL,
    `logs` boolean NOT NULL DEFAULT false,
    `report` boolean NOT NULL DEFAULT false,
    `suggestions` boolean NOT NULL DEFAULT false,
    `members_join` boolean NOT NULL DEFAULT false,
    `members_leave` boolean NOT NULL DEFAULT false,
    PRIMARY KEY (`id`)
    );
ALTER TABLE `guild_functionnalities` ADD FOREIGN KEY (`id`) REFERENCES `guilds` (`id`);

CREATE TABLE IF NOT EXISTS `guild_logs` (
    `guild` VARCHAR(32) NOT NULL,
    `message_update` VARCHAR(32) DEFAULT null,
    `message_delete` VARCHAR(32) DEFAULT null,
    `guild_member_add` VARCHAR(32) DEFAULT null,
    `guild_member_remove` VARCHAR(32) DEFAULT null,
    `config_edit` VARCHAR(32) DEFAULT null,
    PRIMARY KEY (`guild`)
    );
ALTER TABLE `guilds` ADD FOREIGN KEY (`id`) REFERENCES `guild_logs` (`guild`);

CREATE TABLE IF NOT EXISTS `guilds_perms` (
    `guild` VARCHAR(32) NOT NULL,
    `user` VARCHAR(32) NOT NULL,
    `permissions` int NOT NULL DEFAULT 0,
    PRIMARY KEY (`guild`, `user`)
    );
ALTER TABLE `guilds_perms` ADD FOREIGN KEY (`guild`) REFERENCES `guilds` (`id`);

CREATE TABLE IF NOT EXISTS `guilds_xp` (
    `guild` VARCHAR(32) NOT NULL,
    `user` VARCHAR(32) NOT NULL,
    `lvl` integer NOT NULL DEFAULT 0,
    `xp` integer NOT NULL DEFAULT 0,
    PRIMARY KEY (`guild`, `user`)
    );
ALTER TABLE `guilds_xp` ADD FOREIGN KEY (`guild`) REFERENCES `guilds` (`id`);

CREATE TABLE IF NOT EXISTS `users` (
    `id` VARCHAR(32),
    `creation` DATETIME(6) NOT NULL DEFAULT NOW(6),
    `allow_mp` boolean NOT NULL DEFAULT true,
    `bio` VARCHAR(512) DEFAULT null,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `users_logs` (
    `user` VARCHAR(32),
    `details` VARCHAR(512),
    `author` VARCHAR(32) NOT NULL,
    `time` DATETIME(6) NOT NULL DEFAULT NOW(6),
    `logs_ref` CHAR(128) NOT NULL,
    `data_expiration` INT NOT NULL DEFAULT 1209600000 COMMENT 'Default: 2 weeks before deletion; NULL for no deletion',
    PRIMARY KEY (`user`)
);
ALTER TABLE `users_logs` ADD FOREIGN KEY (`user`) REFERENCES `users` (`id`);

CREATE TABLE IF NOT EXISTS `users_scoreboard` (
    `user` VARCHAR(32),
    `score` INT NOT NULL DEFAULT 0,
    `lvl` INT NOT NULL DEFAULT 0,
    `xp` INT NOT NULL DEFAULT 0,
    `reputation` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`user`)
);
ALTER TABLE `users_scoreboard` ADD FOREIGN KEY (`user`) REFERENCES `users` (`id`);

CREATE TABLE IF NOT EXISTS `marriage` (
    `u1` VARCHAR(32) NOT NULL,
    `u2` VARCHAR(32) NOT NULL,
    `time` DATETIME(6) NOT NULL DEFAULT NOW(6),
    PRIMARY KEY (`u1`, `u2`)
);
ALTER TABLE `marriage` ADD FOREIGN KEY (`u1`) REFERENCES `users` (`id`);
ALTER TABLE `marriage` ADD FOREIGN KEY (`u2`) REFERENCES `users` (`id`);

CREATE TABLE IF NOT EXISTS `blacklist` (
    `user` VARCHAR(32) NOT NULL,
    `reason` VARCHAR(512),
    `author` VARCHAR(32) NOT NULL,
    `time` DATETIME(6) NOT NULL DEFAULT NOW(6),
    PRIMARY KEY (`user`)
    );
ALTER TABLE `blacklist` ADD FOREIGN KEY (`author`) REFERENCES `operators` (`user`);

CREATE TABLE IF NOT EXISTS `operators` (
    `user` VARCHAR(32),
    `permissions` INT NOT NULL DEFAULT 0,
    `id` CHAR(60) NOT NULL,
    PRIMARY KEY (`user`)
);

CREATE TABLE IF NOT EXISTS `op_logs` (
    `op` CHAR(60) NOT NULL,
    `unique_ref` CHAR(128) NOT NULL,
    `action` VARCHAR(256),
    `details` VARCHAR(512),
    `date` DATETIME(6) NOT NULL DEFAULT NOW(6),
    PRIMARY KEY (`op`, `unique_ref`)
);
ALTER TABLE `op_logs` ADD FOREIGN KEY (`op`) REFERENCES `operators` (`user`);

The code executed:

static INIT_DATABASE: &str = include_str!("../assets/sql/init.sql");

...

let res = sqlx::query(&INIT_DATABASE)
    .execute(&mut conn)
    .await;

dbg!(&res);

Error: (not at compile time, always at runtime)

[src/libs/database.rs:196] &res = Err(
    Database(
        MySqlDatabaseError {
            code: Some(
                "42000",
            ),
            number: 1064,
            message: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS `guild_functionnalities` (\n    `id` VARCHAR(32) NO...' at line 13",
        },
    ),
)

Info

• SQLx version: 0.6.2
• SQLx features enabled: mysql, macros, sqlite, runtime-tokio-rustls, decimal, bigdecimal, chrono
• Database server and version: mariadb Ver 15.1 Distrib 10.11.1-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
• Operating system: Debian 12 (x86_64)
• rustc --version: rustc 1.67.0 (fc594f156 2023-01-24)

[CosmicHorrorDev]

It sounds like you want to set that up as a migration (look at the sqlx-cli crate)

Also sqlx::query is for a single query while that file contains many queries