Problem with Preconditions in Version 3.3

Liquibase Development
1

today I tried to migrate one of our mysql databases. In my changelog there is 1 changeset with precondition columnExists. After all migration took 40 minutes to complete its work. After I monitored mysqls processlist it seems that columnExists precondition is executed for every table in the current schema.

Processlist displayed for example following info:

SHOW FULL COLUMNS FROM TABLE_XYZ FROM appdb01 LIKE ‘%’

my condition is like that…

<columnExists </div>

  schemaName="APPDB01"  </div>

  tableName="BASE__WEB_USERS" </div>

  columnName="EMAIL"/></div>

For a small database containing a few tables this might not be a problem, but we have 26.000 tables in our schema, so this is a serious problem.

While processing the changelog liquibase calls the table list from the database Before I gave up (after 20 Minutes) threre were 5 messages in the processlist, that liquibase fetched a full table list

SHOW FULL TABLES FROM appdb01 LIKE ‘%’

Is there a way to tell liquibase it should use all the given information to shorten execution time?

Thanks and Regards Rainer

2

I am starting to look at ways to improve the general snapshot process with 4.0. For the upcoming 3.3.1, I did make a couple changes that may help. I’m only seeing one SHOW FULL TABLES now and it doesn’t expand to LIKE ‘%’, it keeps it isolated to the table it is interested in.

You are just using one precondition and it is a columnExits? Or do you have others?

Nathan

3

I have the same issue even without preconditions defined. When upgrading
from version 3.2.2 to 3.3.2, Liquibase suddenly runs a “SHOW FULL
COLUMNS…” query for every table in the database.



To verify the difference, I created a database (MySQL) with 3 tables and
ran liquibase with “updateSQL” on both versions. Below is the
output of the general query log for each run.



With Liquibase 3.2.2:

Connect
root@localhost on test
Query SHOW SESSION
VARIABLES
Query SHOW COLLATION
Query SET
character_set_results = NULL
Query SET
autocommit=1
Query SET
sql_mode='STRICT_TRANS_TABLES’
Query SELECT
USER()
Query SET autocommit=0
Query SELECT
USER()
Query rollback
Query SHOW FULL TABLES FROM
test LIKE 'databasechangeloglock’
Query select
count() from test.DATABASECHANGELOGLOCK
Query
commit
Query rollback
Query SHOW FULL TABLES FROM
test LIKE 'databasechangelog’
Query SHOW FULL
TABLES FROM test LIKE 'databasechangelog’
Query
SHOW FULL COLUMNS FROM databasechangelog FROM test LIKE
'%'
Query SELECT MD5SUM FROM test.DATABASECHANGELOG
WHERE MD5SUM IS NOT NULL
Query SHOW FULL TABLES FROM test
LIKE 'databasechangelog’
Query SELECT
FILENAME,AUTHOR,ID,MD5SUM,DATEEXECUTED,ORDEREXECUTED,EXECTYPE,DESCRIPTION,COMMENTS,TAG,LIQUIBASE
FROM test.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED
ASC
Query commit
Query SHOW FULL TABLES FROM test
LIKE 'databasechangeloglock’
Query select count()
from test.DATABASECHANGELOGLOCK
Query rollback
Query
SHOW FULL TABLES FROM test LIKE
'databasechangeloglock’
Query rollback
Query
commit
Query rollback
Query SHOW FULL TABLES FROM
test LIKE 'databasechangeloglock’
Query
rollback
Query commit
Query rollback
Query
rollback
Query SET autocommit=1
Quit

Liquibase 3.3.2:

Connect root@localhost on test
Query SHOW SESSION VARIABLES
Query SHOW COLLATION
Query SET character_set_results = NULL
Query SET autocommit=1
Query SET sql_mode='STRICT_TRANS_TABLES'
Query SELECT USER()
Query SET autocommit=0
Query SELECT USER()
Query rollback
Query SHOW FULL TABLES FROM `test` LIKE 'databasechangeloglock'
Query select count(*) from test.DATABASECHANGELOGLOCK
Query commit
Query rollback
Query SHOW FULL TABLES FROM `test` LIKE 'databasechangelog'
Query SHOW FULL TABLES FROM `test` LIKE '%'
Query SHOW FULL COLUMNS FROM `databasechangelog` FROM `test` LIKE '%'
Query SHOW FULL COLUMNS FROM `databasechangeloglock` FROM `test` LIKE '%'
Query SHOW FULL COLUMNS FROM `table1` FROM `test` LIKE '%'
Query SHOW FULL COLUMNS FROM `table2` FROM `test` LIKE '%'
Query SHOW FULL COLUMNS FROM `table3` FROM `test` LIKE '%'
Query SELECT MD5SUM FROM test.DATABASECHANGELOG WHERE MD5SUM IS NOT NULL
Query SHOW FULL TABLES FROM `test` LIKE 'databasechangelog'
Query SELECT FILENAME,AUTHOR,ID,MD5SUM,DATEEXECUTED,ORDEREXECUTED,EXECTYPE,DESCRIPTION,COMMENTS,TAG,LIQUIBASE FROM test.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
Query commit
Query rollback
Query rollback
Query commit
Query rollback
Query rollback
Query SET autocommit=1
Quit

As you see, in the latter example it runs queries like "SHOW FULL COLUMNS FROM `table1` FROM `test` LIKE '%'" for each of the tables in the database. And in rainer.spruenkens case, this will lead to the execution of 26.000 queries...