Help with prepared statements

Benjamin Hall

I'm doing a table change to a MySQL which involves adding a column with a nullable foreign key. That's easy.

The sticky part is creating the rollback entry--we use liquibase as our database schema update platform. Which internally munges foreign key names, adding underscores every time it has to update that same table later (because it tries to create a new table, do all the changes, then move it over the top of the old one once it's done. But that breaks foreign keys, since those have to have unique names per database. As a result, the foreign keys could have the form foreign_key_name, _foreign_key_name, or __foreign_key_name (or more if the table has changed a lot). And this depends on environment--our staging/qa/testing environments get set up from scratch, so they're predictable. The production database wasn't always liquibase managed, so it's got some differences. So simply doing ALTER TABLE DROP FOREIGN KEY foreign_key_name won't work reliably.

All of this is preamble to the real question. We have a hacky, ugly way of doing rollbacks for foreign keys:

SET @q = (SELECT CONCAT('ALTER TABLE table_name DROP FOREIGN KEY ', CONSTRAINT_NAME) AS query FROM information_schema.table_constraints WHERE CONSTRAINT_NAME LIKE '%foreign_key_name'); 
PREPARE drop_key FROM @q; 
EXECUTE drop_key; 
SET @q = NULL; 
DEALLOCATE PREPARE drop_key; 
ALTER TABLE table_name DROP COLUMN column_name;

This has worked in the past. But now when I try to apply this pattern to a new combination of table/column/foreign_key_name, I get "Syntax Error, check near 'PREPARE drop_key FROM @q'".

I've checked the following:

Manually run the SQL it says it's using in the database. No issues.
Check that table/column/fk name are all matching the correct values
Check that the SQL it thinks its running is the same as expected.
Check that the database and liquibase versions are what they should be.

Anyone see any glaring errors I'm missing? Ideas on what else could be going wrong?

izzion

@Benjamin-Hall said in Help with prepared statements:

I'm doing a table change to a MySQL which involves adding a column with a nullable foreign key. That's easy.

The sticky part is creating the rollback entry--we use liquibase as our database schema update platform. Which internally munges foreign key names, adding underscores every time it has to update that same table later (because it tries to create a new table, do all the changes, then move it over the top of the old one once it's done. But that breaks foreign keys, since those have to have unique names per database. As a result, the foreign keys could have the form foreign_key_name, _foreign_key_name, or __foreign_key_name (or more if the table has changed a lot). And this depends on environment--our staging/qa/testing environments get set up from scratch, so they're predictable. The production database wasn't always liquibase managed, so it's got some differences. So simply doing ALTER TABLE DROP FOREIGN KEY foreign_key_name won't work reliably.

All of this is preamble to the real question. We have a hacky, ugly way of doing rollbacks for foreign keys:
SET @q = (SELECT CONCAT('ALTER TABLE table_name DROP FOREIGN KEY ', CONSTRAINT_NAME) AS query FROM information_schema.table_constraints WHERE CONSTRAINT_NAME LIKE '%foreign_key_name'); 
PREPARE drop_key FROM @q; 
EXECUTE drop_key; 
SET @q = NULL; 
DEALLOCATE PREPARE drop_key; 
ALTER TABLE table_name DROP COLUMN column_name;
This has worked in the past. But now when I try to apply this pattern to a new combination of table/column/foreign_key_name, I get "Syntax Error, check near 'PREPARE drop_key FROM @q'".

I've checked the following:

Manually run the SQL it says it's using in the database. No issues.

Check that table/column/fk name are all matching the correct values

Check that the SQL it thinks its running is the same as expected.

Check that the database and liquibase versions are what they should be.

Anyone see any glaring errors I'm missing? Ideas on what else could be going wrong?

Is the SELECT CONCAT guaranteed to be unique? Could you be getting row mis-ordering hijinx on different runs? At a glance, that doesn't look like a very specific WHERE clause.

Benjamin Hall

@izzion said in Help with prepared statements:

@Benjamin-Hall said in Help with prepared statements:
I'm doing a table change to a MySQL which involves adding a column with a nullable foreign key. That's easy.

The sticky part is creating the rollback entry--we use liquibase as our database schema update platform. Which internally munges foreign key names, adding underscores every time it has to update that same table later (because it tries to create a new table, do all the changes, then move it over the top of the old one once it's done. But that breaks foreign keys, since those have to have unique names per database. As a result, the foreign keys could have the form foreign_key_name, _foreign_key_name, or __foreign_key_name (or more if the table has changed a lot). And this depends on environment--our staging/qa/testing environments get set up from scratch, so they're predictable. The production database wasn't always liquibase managed, so it's got some differences. So simply doing ALTER TABLE DROP FOREIGN KEY foreign_key_name won't work reliably.

All of this is preamble to the real question. We have a hacky, ugly way of doing rollbacks for foreign keys:
SET @q = (SELECT CONCAT('ALTER TABLE table_name DROP FOREIGN KEY ', CONSTRAINT_NAME) AS query FROM information_schema.table_constraints WHERE CONSTRAINT_NAME LIKE '%foreign_key_name'); 
PREPARE drop_key FROM @q; 
EXECUTE drop_key; 
SET @q = NULL; 
DEALLOCATE PREPARE drop_key; 
ALTER TABLE table_name DROP COLUMN column_name;
This has worked in the past. But now when I try to apply this pattern to a new combination of table/column/foreign_key_name, I get "Syntax Error, check near 'PREPARE drop_key FROM @q'".

I've checked the following:

Manually run the SQL it says it's using in the database. No issues.

Check that table/column/fk name are all matching the correct values

Check that the SQL it thinks its running is the same as expected.

Check that the database and liquibase versions are what they should be.

Anyone see any glaring errors I'm missing? Ideas on what else could be going wrong?
Is the SELECT CONCAT guaranteed to be unique? Could you be getting row mis-ordering hijinx on different runs? At a glance, that doesn't look like a very specific WHERE clause.

There is guaranteed to be exactly 1 constraint with the name like %foreign_key_name at a time. The key base names are unique (always of the form fk_..., and the renaming is atomic--on the first update to that table (the one that introduces the foreign key), it will have 0 underscores. Every schema update to that table afterward will introduce 1 more underscore.

And I'm really not seeing how that would cause a syntax error, since parsing happens before it actually queries any tables. And what I'm seeing is a syntax error, pointing to the "PREPARE drop_key FROM @q" statement.

dkf

@Benjamin-Hall said in Help with prepared statements:

And what I'm seeing is a syntax error, pointing to the "PREPARE drop_key FROM @q" statement.

If a foreign key is something that needs to be quoted for some reason, that SQL will blow up in preparation. If the SELECT doesn't yield any rows, that will also make the preparation detonate (as I think empty statements are going to be not allowed). I've no idea what happens if multiple rows are produced.

In any case, that's three cases you need to check for.

loopback0

@dkf said in Help with prepared statements:

If a foreign key is something that needs to be quoted for some reason, that SQL will blow up in preparation.

It doesn't need to be, unless the name starting with underscore is buggering it up. Putting ` around it might be worth trying.

@dkf said in Help with prepared statements:

If the SELECT doesn't yield any rows, that will also make the preparation detonate (as I think empty statements are going to be not allowed)

It should just not execute. At least that's the case on recent versions of MySQL/MariaDB.
edit: Oh, no, it'll error but it'll complain that 'NULL' isn't a valid statement

@dkf said in Help with prepared statements:

I've no idea what happens if multiple rows are produced.

It executes multiple times.

dkf

@loopback0 said in Help with prepared statements:

Putting ` around it might be worth trying.

I've seen `…`, "…" and […] all used as quoting for “naughty“ identifiers, and '…' is often also accepted (it's formally wrong, but if unambiguous then might get worked with anyway). I can't remember which ones MySQL uses as I've not done much with it…

loopback0

@dkf said in Help with prepared statements:

I can't remember which ones MySQL uses as I've not done much with it…

It's ` unless the SQL_MODE is set to something other than default.

loopback0

@loopback0 said in Help with prepared statements:

It doesn't need to be, unless the name starting with underscore is buggering it up. Putting ` around it might be worth trying.

Nope. Underscore is permitted in unquoted identifiers. At least in 5.7 onwards.

MySQL :: MySQL 5.7 Reference Manual :: 9.2 Schema Object Names

Benjamin Hall

@loopback0 that might be it. We're on 5.5. yes, I know it's EOL a long time ago.

loopback0

@Benjamin-Hall said in Help with prepared statements:

@loopback0 that might be it. We're on 5.5. yes, I know it's EOL a long time ago.

I think it's still safe to use underscore unquoted back to 5.1, it's just the documentation wasn't as easy to find.

Benjamin Hall

@loopback0 said in Help with prepared statements:

@Benjamin-Hall said in Help with prepared statements:

@loopback0 that might be it. We're on 5.5. yes, I know it's EOL a long time ago.

I think it's still safe to use underscore unquoted back to 5.1, it's just the documentation wasn't as easy to find.

Yeah, that's why I initially leaned against it. All our fk names have underscores and none of them are quoted. Which is why I'm puzzled that this one in particular goes boom.

Jaloopa

Is there anything else unusual about this foreign key's name?

Benjamin Hall

@Jaloopa said in Help with prepared statements:

Is there anything else unusual about this foreign key's name?

Nope. The two at fault are:
fk_overrides_station and
fk_overrides_status.

And quoting didn't make a difference.

boomzilla

I admit that I haven't looked super closely here. Is the problem that _ is treated as a single character wildcard in LIKE?

Benjamin Hall

@boomzilla said in Help with prepared statements:

I admit that I haven't looked super closely here. Is the problem that _ is treated as a single character wildcard in LIKE?

When I ran the select part of the query as SELECT CONSTRAINT_NAME FROM information_schema.table_constraints WHERE CONSTRAINT_NAME LIKE '%fk_overrides_status' I got back the __fk_overrides_status result I expected.