updateSQL does not like tableExists pre-condition IF the MySQL database contains a View

Description

Executing updateSQL from the command line with a changeset that has a tableExists pre-condition fails if the MySQL database contains a View.

Steps to Reproduce

  1. Create a new MySQL database that contains at least one Table and one View

  2. Create a change log file that contains a preCondition that checks for a Tables existence. Also add a database change to this file (e.g. add a column to a table). Save the file to test.xml.

  3. From the command line execute liquibase --driver=com.mysql.jdbc.Driver --username root --url=jdbc:mysql://localhost/database_name --changeLogFile=test.xml updateSQL (assuming that you have correct classpath and a blank root password).

What I expected to happen

I expected that the program would output a series of SQL commands to perform the database migration(s) contaied in the change log file.

What happened instead

Instead I got an error message.

Error getting jdbc:mysql://localhost/liquibase_test view with select view_definition from information_schema.views where table_name='view_bar' AND table_schema='null'
Migration Failed: Validation Failed:
1 preconditions generated an error
test.xml : Empty result set, expected one row

If I either:

  1. remove the view the updateSQL command succeeds

  2. remove the tableExists pre-condition check the command succeeds

Additional Notes

I have attached a database dump (liquibase.sql) and change log file (test.xml) that will reproduce the issue.

Environment

Windows XP SP2, MySQL 5.0.45

Attachments

2

Activity

Show:

Nathan Voxland October 4, 2009 at 10:14 PM

Redid the database execute/logging logic in 2.0 which solves this issue. Was not able to duplcate it with trunk.

Thanks for the reproduction data and code pointers!

TimothyC September 29, 2009 at 7:33 PM

I can confirm that this bug exists in LiquiBase 1.9.5 with Oracle.

I stepped though the code. The fatal exception is throw in JdbcTemplate.java at

As described above, the query method always returns null in a JdbcOutputTemplate instance.

This also prevents the getting a database change lock, because an exception is thrown (also in JdbcTemplate.java) at

This is called as part of waitForLock in LockHandler.java, which ignores the exception with this code

Nathan Voxland September 27, 2009 at 2:49 PM

reopening to clear resoltion field

Nathan Voxland September 27, 2009 at 2:47 PM

Fixing "resolution". have to resolve and reopen

Andrew Davey September 14, 2009 at 4:10 AM

I believe that the problem described above is not just limited to mysql.

The problem stems from 'updateSQL' replaces the default JdbcTemplate implementation with JdbcOutputTemplate. This makes sense since 'updateSQL' is supposed to show what sql will be output against the database (but not acutally perform any actions against the database).

However, the JdbcOutputTemplate stops the validator from determining what the definition of a view is when creating a database snapshot (see line 199 of SqlDatabaseSnapshot.java). When creating a database snapshot, if a view is encountered the database object is asked for the view definition. The database object creates the sql it needs to get the view, but passes execution to 'JdbcOutputTemplate' which just outputs the sql without executing it. Hence the view definition is not returned and the validator raises and exception.

Fixed

Details

Reporter

Fix versions

Affects versions

Priority

Created September 2, 2009 at 12:54 PM
Updated October 4, 2009 at 10:14 PM
Resolved October 4, 2009 at 10:14 PM