Search code examples
mysqlsqitch

Verify table does NOT exist with mysql query in sqitch

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?

Solution

  • 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';