Error when performing DB migration 22

[pogo-excalibur]

Expected Behavior

DB migrations should run without errors on all supported DBs.

Current Behavior

Running migration 22 (#2416) on a MySQL database produces an error:

ProgrammingError: (1064, u"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 'CONSTRAINT CONSTRAINT_2' at line 1")

Note: the concern here is the error message - the migration gets applied 'correctly'.

Possible Solution

MySQL doesn't support CHECK constraints, so the migration is not actually required for MySQL DBs. This is why the migration gets applied 'correctly' - in this case 'correctly' means not applying it at all.

The error is raised because MySQL also doesn't used the DROP CONSTRAINT syntax use in the PR.

It may therefore be necessary to detect the DB type and only apply the migration to MariaDB databases.

Alternatively, as this doesn't cause any particular issues we could just ignore it - most users have probably updated by now, and new users don't have to run DB migrations.

Steps to Reproduce (for bugs)

1. Have a version of the database prior to v22 (I used v21 for testing)
2. Update to a version which contains Update to fix CONSTRAINT_4 error for spawnpoint table #2416
3. Run the DB migration

Context

It's a small issue - particularly as our docs recommend using MariaDB - but I've seen it come up a couple of times in #help so there are at least some users using MySQL.

Your Environment

MySQL DB, rather than MariaDB.

[sebastienvercammen]

Confirmed, but the problem isn't that MySQL doesn't support CHECK constraints. MySQL parses but ignores them.

The problem is that MySQL doesn't know DROP CONSTRAINT, causing a syntax error.

Since the constraints aren't applied in MySQL this won't cause any issues with databases, but we should handle it better.

[dongemus]

FYI, I got this error migrating to 22 using MariaDB. I have 4 machines running mariadb so I can't remember which it was, but they all have 10.1xx or 10.2xx

[sebastienvercammen]

@dongemus I've just tested on MariaDB and it worked properly. Could you reproduce the problem on MariaDB and paste the logs?

[dongemus]

InfiniteChaos had it happen on MariaDB as well, he posted about it in Devkat #help

I had a machine that I hadn't used for RM in a while and so I did a pull and got the error on schema upgrade. It worked fine after restarting.
https://hastebin.com/fapoduwuwi.md