Upgrade failing for MySQL with syntax error using v1.7.0

[twerthi]

Describe the bug
grate-internal/01_version_add_status_column.sql: 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 'IF NOT EXISTS status varchar(50) NULL' at line 2

An error occurred: Migration failed due to the following errors:

Update ("up"):

I switched to version 1.6.2 and it works fine.

To Reproduce
Use the grate Step Template in Octopus Deploy with the Download Grate option selected and not specify a version.

Expected behavior

Screenshots

Desktop (please complete the following information):

• OS: Linux
• Version: 22.04

Additional context

[erikbra]

Blargh! Sorry. This is probably the internal grate tables' migrations I have introduced that have errors. But, all my tests work fine. Which version of MySql are you running against?

Do you see any errors in the script that is registered in the Grate_Internal.ScriptRunErrors (or Grate.ScriptRunErrors?)

It's the script here (with the tokens replaced) that is run:

https://github.com/erikbra/grate/blob/main/src/grate.mariadb/Bootstrapping/Sql/GrateStructure/up/01_version_add_status_column.sql

[twerthi]

Greetings Erik! I'm running against the latest MySQL container, mysql/mysql-server. I'm running grate using an Octopus Deploy step template, this is the command executed

Executing C:\Octopus\Work\Ta5sL6HFhUGfxOyhNbMb5g\grate\tools\net8.0\any\grate.dll with --connectionstring="Server=192.168.1.64;Port=30000;Allow User Variables=true; Uid=root;Pwd=****; Database=sakila_Development;" --databasetype=mariadb --silent --transaction=false --commandtimeout=60 --verbosity=information --version=1.0.24045.165919 

Did I miss something?

In regards to your question, where would I find that?

[twerthi]

I just saw there was a new version of the template available which added the environment and schema inputs. I ran it again, but encountered the same error

Executing C:\Octopus\Work\Duh4YIXn10C1StO6tHnw\grate\tools\net8.0\any\grate.dll with --connectionstring="Server=192.168.1.64;Port=30000;Allow User Variables=true; Uid=root;Pwd=****; Database=sakila_Development;" --databasetype=mariadb --silent --transaction=false --commandtimeout=60 --verbosity=information --version=1.0.24045.165919 --environment=Development --schema=grate

I also tested

• MariaDB
• Postgres
• SQL Server

All of those work fine.

[erikbra]

A quick follow-up question, @twerthi - could you issue be related to #512 ? Are you using custom "Create database" scripts?

[twerthi]

I think it could be related, the database is created without the use of grate.

[erikbra]

If it is not created using grate, I don't think it's related.

It looks like it might be a difference in supported syntax in MariaDb vs MySQL:

MariaDb seems to support ADD COLUMN IF NOT EXISTS: https://mariadb.com/kb/en/alter-table/
MySQL doesn't seem to support it: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

I'll have to think a bit on how to solve this... we might rephrase the script that adds the status column to just add it, and ignore any errors if the column exists already.

I'm sceptical to remove the script, as any existing RoundhousE installations will have the version table, but the status column won't be there, so, we'd have to add a manual migration step, then.

[twerthi]

Closing the loop, confirmed the new version is working fine :)

[erikbra]

Great, thanks for confirming, @twerthi !