Description
When using liquibase with mysql, we generally use the “CreateView” tag to add views in the XML. Normally this works fine and you can put just the query in the body of the tag.
One of the nice things I like to set up with views in liquibase is to create a file for each view with the name of the view. Then I can use the runOnChange=“true” attribute to allow me to edit the file later and have the changes applied to my db. I feels a little cleaner than adding each minor change as a full update in the squential part of my changelogs. It generally looks something like this:
<databaseChangelog>
<changeSet id="1" author="mrankin" runOnChange="true">
<createView viewName="my_view" replaceIfExists="true">
<![CDATA[
SELECT
field_1 as `Field 1`,
field_2 as `Field 2`
FROM my_table;
]]>
</createView>
</changeSet>
</databaseChangelog>
This generates and applys
CREATE OR REPLACE my_view AS
SELECT
field_1 as `Field 1`,
field_2 as `Field 2`
FROM my_table;
So far, so good. If, however, I make use of the replace function in the body of my view,
<databaseChangelog>
<changeSet id="1" author="mrankin" runOnChange="true">
<createView viewName="my_view" replaceIfExists="true">
<![CDATA[
SELECT
replace(field_1,'-','_') as `Field 1`, -- replace dashes with underscores
field_2 as `Field 2`
FROM my_table;
]]>
</createView>
</changeSet>
</databaseChangelog>
Liquibase will generate this:
CREATE my_view AS
SELECT
replace(field_1,'-','_') as `Field 1` -- replace dashes with underscores,
field_2 as `Field 2`
FROM my_table;
I’m guessing there is some sort of logic that is getting wrecked by the presence of the “replace” keyword in the body of the view.
This is fine the first run, and can make it unlikely that you will catch the problem quickly. On subsequent passes, the engine squawks stating that the “table” already exists (bit of a misleading error message there as well).
Easy Workaround
While a fix would be nice, the workaround is trivial. Whenever you use the replace function in a view, use the raw SQL tag instead of the CreateView tag like this:
<databaseChangelog>
<changeSet id="1" author="mrankin" runOnChange="true">
<sql>
<![CDATA[
CREATE OR REPLACE VIEW my_view AS
SELECT
replace(field_1,'-','_') as `Field 1`, -- replace dashes with underscores
field_2 as `Field 2`
FROM my_table;
]]>
</sql>
</changeSet>
</databaseChangelog>
Liquibase will parse and execute that just fine.
comments powered by Disqus