3

I'm using sqitch to track database changes, and one of the ways it works is to verify changes with a sql query. If the query returns an error then the change is considered unsuccessful.

I have a change where I am permanently dropping a table from my mysql database. To verify the deployment my verification needs to return an error if the table exists. While it's straightforward to return an error if a table doesn't exist, how do I get mysql to return an error only when a specific table does exist?

Slotheroo
  • 925
  • 2
  • 9
  • 17
  • 2
    You can try creating a table or a view with the same name, and if the table exists, it'll throw an error. Interestingly, it does _not_ throw an error if you create a temporary table of the same name. – Bill Karwin Feb 01 '19 at 19:02
  • 1
    But I'd choose to check for the table in `INFORMATION_SCHEMA.TABLES` instead of causing an error. – Bill Karwin Feb 01 '19 at 19:03
  • Trying to create a table with the same name seems like a clever solution. I'm worried about using information_schema.tables since I may not know in the context of my script what the current database is or what databases that user would have access to, since the target database for the script is either in a config or specified on the command line. – Slotheroo Feb 01 '19 at 19:07
  • 1
    The `DATABASE()` function returns the default database of the current session. – Bill Karwin Feb 01 '19 at 19:17
  • Looking into information_schema feels more elegant, but even there I can't figure how to thrown an error if a result is found. That said, I've implemented my verify with a CREATE + a DROP (to clean up) and it seems to be working as required. – Slotheroo Feb 01 '19 at 19:44
  • I wasn't suggesting to make the SQL statement throw an error. More like you run the SQL query and if it returns more than 0 results (indicating the table does exist), then you produce an error in your application code. – Bill Karwin Feb 01 '19 at 21:44

1 Answers1

4

Use the checkit function that comes with Sqitch, as described in the MySQL tutorial:

SELECT checkit(COUNT(*) = 0, 'Table "foo" exists but should not')
  FROM information_schema.tables
 WHERE table_schema = 'your_db'
   AND table_name = 'dropped_table';
theory
  • 9,178
  • 10
  • 59
  • 129
  • Somehow didn't realize checkit() would work for this type of query as well but that makes sense. Always good to get information from the source. Thanks. – Slotheroo May 02 '19 at 17:46
  • How come that sqitch database has checkit() function ? I don't have it, I guess because I created sqitch database myself and loaded it with DB dump. – Matko Feb 02 '21 at 12:17
  • I added `checkit()` specifically to address the need for straightforward verification checks. You can see it's definition as part of the registry schema (database) [here](https://github.com/sqitchers/sqitch/blob/a2c920545cdba64367c88b77a6268b0fc503f0d3/lib/App/Sqitch/Engine/mysql.sql#L149-L165). – theory Feb 03 '21 at 16:30