tortoise.exceptions.OperationalError: near "INDEX": syntax error

[PythonCoderAS]

When I tried to upgrade the migrations made by aerich migrate, I got the error tortoise.exceptions.OperationalError: near "INDEX": syntax error. I suspect the line ALTER TABLE "option" ADD INDEX "idx_option_name_abe08f" ("name"); is causing it, because my IDE marks it as invalid. Here is the full migration script that was generated:

-- upgrade --
CREATE TABLE IF NOT EXISTS "argument" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "name" VARCHAR(2000) NOT NULL,
    "description" TEXT NOT NULL,
    "command_id" INT NOT NULL REFERENCES "command" ("id") ON DELETE CASCADE
);;
CREATE TABLE IF NOT EXISTS "command" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "name" VARCHAR(2000) NOT NULL UNIQUE,
    "description" TEXT
);
CREATE INDEX IF NOT EXISTS "idx_command_name_a5f5d3" ON "command" ("name");;
CREATE TABLE IF NOT EXISTS "example" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "text" TEXT NOT NULL,
    "argument_id" INT NOT NULL REFERENCES "argument" ("id") ON DELETE CASCADE
);;
ALTER TABLE "option" ADD INDEX "idx_option_name_abe08f" ("name");
CREATE TABLE IF NOT EXISTS "prefix" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "guild_id" BIGINT NOT NULL,
    "value" VARCHAR(20) NOT NULL
);
CREATE INDEX IF NOT EXISTS "idx_prefix_guild_i_22997b" ON "prefix" ("guild_id");
CREATE INDEX IF NOT EXISTS "idx_prefix_value_3efe4f" ON "prefix" ("value");;
-- downgrade --
ALTER TABLE "option" DROP INDEX "idx_option_name_abe08f";
DROP TABLE IF EXISTS "argument";
DROP TABLE IF EXISTS "command";
DROP TABLE IF EXISTS "example";
DROP TABLE IF EXISTS "prefix";

[PythonCoderAS]

I fixed this by manually modifying the SQL to:

-- upgrade --
CREATE TABLE IF NOT EXISTS "argument" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "name" VARCHAR(2000) NOT NULL,
    "description" TEXT NOT NULL,
    "command_id" INT NOT NULL REFERENCES "command" ("id") ON DELETE CASCADE
);;
CREATE TABLE IF NOT EXISTS "command" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "name" VARCHAR(2000) NOT NULL UNIQUE,
    "description" TEXT
);
CREATE INDEX IF NOT EXISTS "idx_command_name_a5f5d3" ON "command" ("name");;
CREATE TABLE IF NOT EXISTS "example" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "text" TEXT NOT NULL,
    "argument_id" INT NOT NULL REFERENCES "argument" ("id") ON DELETE CASCADE
);;
CREATE INDEX IF NOT EXISTS "idx_option_name_abe08f" ON "option" ("name");
CREATE TABLE IF NOT EXISTS "prefix" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "guild_id" BIGINT NOT NULL,
    "value" VARCHAR(20) NOT NULL
);
CREATE INDEX IF NOT EXISTS "idx_prefix_guild_i_22997b" ON "prefix" ("guild_id");
CREATE INDEX IF NOT EXISTS "idx_prefix_value_3efe4f" ON "prefix" ("value");;
-- downgrade --
DROP INDEX IF EXISTS "idx_option_name_abe08f";
DROP TABLE IF EXISTS "argument";
DROP TABLE IF EXISTS "command";
DROP TABLE IF EXISTS "example";
DROP TABLE IF EXISTS "prefix";

However, it should not require intervention.

[long2ice]

So which db?

[PythonCoderAS]

So which db?

This is for an SQLite database.

[spacemanspiff2007]

I can confirm that the created ALTER TABLE command does not work with SQLite and this it's not possible to create migrations for it.

If I drop a column "old_cfg" and create a new one "config" I get

tortoise.exceptions.OperationalError: near "DROP": syntax error

If I try it again I get

tortoise.exceptions.OperationalError: duplicate column name: config

because the column was created but the migration was not complete.

I can't do init-db because then it will not create the column on my server and the app will crash.

@long2ice Could you provide me with hints how I can make the migrations happen? Thanks for your help!

[PythonCoderAS]

@spacemanspiff2007 one option you have is editing the generated SQL migration manually. aerich upgrade does not do any integrity checks, so it'll read the migration code verbatim. I use this feature to fix any errors so that aerich upgrade works as expected.

[spacemanspiff2007]

@PythonCoderAS Thank you for your hint! Unfortunately it's not clear to me how I can drop columns manually through the sql statement since everything looks correct. Could you provide me with a hint?

[PythonCoderAS]

According to sqlite documentation, you would want something like ALTER TABLE <TABLENAME> DROP COLUMN <COLUMNNAME>.

[spacemanspiff2007]

This is what is generated by aerich:

-- upgrade --
ALTER TABLE "mymodel" DROP COLUMN "myfield";
-- downgrade --
ALTER TABLE "mymodel" ADD "myfield" JSON;

The generation is only successful on the second aerich migrate call. The first one throws the following error:

    table = change[0][1].get("through")
AttributeError: 'str' object has no attribute 'get'

The migration statements actually look allright, but I get the following exception when doing the aerich upgrade:

    raise OperationalError(exc)
tortoise.exceptions.OperationalError: near "DROP": syntax error

[georges-g]

I had the same issue and it seems to be a database version problem.

The syntax ALTER TABLE DROP COLUMN was only introduced in sqlite 3.35.0. I have debian stable installed and it is still sqlite 3.34.1-3 (you can see this with apt show sqlite3).

You might want to clarify supported versions of sqlite3.

PS: Thanks for the open source work!