Discussion:
[Liquibase-user] dropNotNullConstraint creates bad SQL
Zach Cox
2007-12-05 05:35:10 UTC
Permalink
I have a column that is NOT NULL and I want to remove that constraint.
I tried using this change set:

<changeSet id="16" author="zcox">
<comment>Make barcode column nullable</comment>
<dropNotNullConstraint tableName="barcode_searches"
columnName="barcode"/>
</changeSet>

But LiquiBase 1.4.1 tries to run this SQL on the MySQL database:

ALTER TABLE barcode_searches MODIFY barcode null NULL

Which obviously causes this error:

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'null NULL' at line 1

Am I not using dropNotNullConstraint properly or is it broken?

Thanks,
Zach
Nathan Voxland
2007-12-05 15:58:52 UTC
Permalink
The trouble is that MySQL requires you to use the columnDataType
attribute on dropNotNullConstraint because of the way their SQL works.
That is what the first null is coming from.

I added a validation check so you will get a better error messages with
the next release.

Let me know if you still have troubles.

Nathan


-----Original Message-----
From: liquibase-user-***@lists.sourceforge.net
[mailto:liquibase-user-***@lists.sourceforge.net] On Behalf Of Zach
Cox
Sent: Tuesday, December 04, 2007 11:35 PM
To: liquibase-***@lists.sourceforge.net
Subject: [Liquibase-user] dropNotNullConstraint creates bad SQL

I have a column that is NOT NULL and I want to remove that constraint.
I tried using this change set:

<changeSet id="16" author="zcox">
<comment>Make barcode column nullable</comment>
<dropNotNullConstraint tableName="barcode_searches"
columnName="barcode"/>
</changeSet>

But LiquiBase 1.4.1 tries to run this SQL on the MySQL database:

ALTER TABLE barcode_searches MODIFY barcode null NULL

Which obviously causes this error:

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'null NULL' at line 1

Am I not using dropNotNullConstraint properly or is it broken?

Thanks,
Zach

------------------------------------------------------------------------
-
SF.Net email is sponsored by: The Future of Linux Business White Paper
from Novell.
Zach Cox
2007-12-05 16:05:03 UTC
Permalink
That worked perfectly - thanks! And now I see the [required for MySQL
& MS-SQL] note on columnDataType in the docs - woops! :)

Thanks,
Zach
Post by Nathan Voxland
The trouble is that MySQL requires you to use the columnDataType
attribute on dropNotNullConstraint because of the way their SQL works.
That is what the first null is coming from.
I added a validation check so you will get a better error messages with
the next release.
Let me know if you still have troubles.
Nathan
-----Original Message-----
Cox
Sent: Tuesday, December 04, 2007 11:35 PM
Subject: [Liquibase-user] dropNotNullConstraint creates bad SQL
I have a column that is NOT NULL and I want to remove that constraint.
<changeSet id="16" author="zcox">
<comment>Make barcode column nullable</comment>
<dropNotNullConstraint tableName="barcode_searches"
columnName="barcode"/>
</changeSet>
ALTER TABLE barcode_searches MODIFY barcode null NULL
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'null NULL' at line 1
Am I not using dropNotNullConstraint properly or is it broken?
Thanks,
Zach
------------------------------------------------------------------------
-
SF.Net email is sponsored by: The Future of Linux Business White Paper
from Novell.
Nathan Voxland
2007-12-05 16:10:58 UTC
Permalink
No problem. I should update the docs to also say it's required for HSQL
too.

Nathan

-----Original Message-----
From: liquibase-user-***@lists.sourceforge.net
[mailto:liquibase-user-***@lists.sourceforge.net] On Behalf Of Zach
Cox
Sent: Wednesday, December 05, 2007 10:05 AM
To: liquibase-***@lists.sourceforge.net
Subject: Re: [Liquibase-user] dropNotNullConstraint creates bad SQL

That worked perfectly - thanks! And now I see the [required for MySQL
& MS-SQL] note on columnDataType in the docs - woops! :)

Thanks,
Zach
Post by Nathan Voxland
The trouble is that MySQL requires you to use the columnDataType
attribute on dropNotNullConstraint because of the way their SQL works.
That is what the first null is coming from.
I added a validation check so you will get a better error messages with
the next release.
Let me know if you still have troubles.
Nathan
-----Original Message-----
Cox
Sent: Tuesday, December 04, 2007 11:35 PM
Subject: [Liquibase-user] dropNotNullConstraint creates bad SQL
I have a column that is NOT NULL and I want to remove that constraint.
<changeSet id="16" author="zcox">
<comment>Make barcode column nullable</comment>
<dropNotNullConstraint tableName="barcode_searches"
columnName="barcode"/>
</changeSet>
ALTER TABLE barcode_searches MODIFY barcode null NULL
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'null NULL' at line 1
Am I not using dropNotNullConstraint properly or is it broken?
Thanks,
Zach
------------------------------------------------------------------------
Post by Nathan Voxland
-
SF.Net email is sponsored by: The Future of Linux Business White Paper
from Novell.
Loading...