Databases
StackOverflow2013
Postsdbo
POStatement parsing bug in flyway?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStatement parsing bug in flyway?
    primarykey
    data
    text
    <p>I've encountered a very strange error using flyway. First, here is a version of my update script that works. (This is in a file named V57.3__foo.sql): </p> <pre class="lang-sql prettyprint-override"><code>use database go set nocount on create table userz ( username char(30), firstname char(30), lastname char(30), activeind char(1) ) insert into userz (username, firstname, lastname, activeind) Values('jsmith', 'John', 'Smith','a') declare @username varchar(30), @firstname varchar(255), @lastname varchar(255), @activeind char(1) DECLARE the_cursor CURSOR FAST_FORWARD LOCAL FOR SELECT username, firstname, lastname, activeind FROM userz OPEN the_cursor FETCH NEXT FROM the_cursor INTO @username, @firstname, @lastname, @activeind WHILE (@@FETCH_STATUS &lt;&gt; -1) BEGIN declare @email varchar(255) SELECT @email=replace(coalesce(@firstname, 'xxxxx')+'.'+coalesce(@lastname, 'xxxxx'),' ','')+'@domain.com' SELECT @email=lower(@email) -- SELECT @email=lower(replace(coalesce(@firstname, 'xxxxx')+'.'+coalesce(@lastname, 'xxxxx'),' ','')+'@domain.com') declare @deleted bit SELECT @deleted= case when @activeind='a' then 0 else 1 end FETCH NEXT FROM the_cursor INTO @username, @firstname, @lastname, @activeind END CLOSE the_cursor DEALLOCATE the_cursor drop table userz set nocount off go </code></pre> <p>When I turn on the application, I get this output: </p> <pre class="lang-sql prettyprint-override"><code>[2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.dbsupport.DbSupportFactory - Database: Microsoft SQL Server 10.50 [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.Flyway - DDL Transactions Supported: true [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.Flyway - Schema: dbo [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.command.DbSchemas - Schema [dbo] already exists. Skipping schema creation. [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.metadatatable.MetaDataTableTo20FormatUpgrader - No upgrade to the Flyway 2.0 format necessary for metadata table [dbo].[schema_version] [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.metadatatable.MetaDataTableTo202FormatUpgrader - No metadata table upgrade to the Flyway 2.0.2 format necessary [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.metadatatable.MetaDataTableTo21FormatUpgrader - No metadata table upgrade to the Flyway 2.1 format necessary [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.dbsupport.Table - Locking table [dbo].[schema_version]... [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.dbsupport.Table - Lock acquired for table [dbo].[schema_version] [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.util.FeatureDetector - Spring Jdbc available: true [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.util.scanner.classpath.ClassPathScanner - Scanning for classpath resources at 'db/migration' (Prefix: 'V', Suffix: '.sql') [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.util.scanner.classpath.ClassPathScanner - Scanning URL: file:/home/mcurwen/projects/core/target/classes/db/migration [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.util.FeatureDetector - JBoss VFS v2 available: false ---- very large snip of scanning/filtering ---- [2013-07-08 14:37:31] [] INFO localhost-startStop-1 com.googlecode.flyway.core.command.DbMigrate - Current version of schema [dbo]: 57.2 [2013-07-08 14:37:31] [] WARN localhost-startStop-1 com.googlecode.flyway.core.command.DbMigrate - outOfOrder mode is active. Migration of schema [dbo] may not be reproducible. [2013-07-08 14:37:31] [] INFO localhost-startStop-1 com.googlecode.flyway.core.command.DbMigrate - Migrating schema [dbo] to version 57.3 [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.dbsupport.SqlScript - Found statement at line 1: use database [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.dbsupport.SqlScript - Found statement at line 3: set nocount on create table userz ( username char(30), firstname char(30), lastname char(30), activeind char(1) ) insert into userz (username, firstname, lastname, activeind) Values('jsmith', 'John', 'Smith','a') declare @username varchar(30), @firstname varchar(255), @lastname varchar(255), @activeind char(1) DECLARE the_cursor CURSOR FAST_FORWARD LOCAL FOR SELECT username, firstname, lastname, activeind FROM userz OPEN the_cursor FETCH NEXT FROM the_cursor INTO @username, @firstname, @lastname, @activeind WHILE (@@FETCH_STATUS &lt;&gt; -1) BEGIN declare @email varchar(255) SELECT @email=replace(coalesce(@firstname, 'xxxxx')+'.'+coalesce(@lastname, 'xxxxx'),' ','')+'@domain.com' SELECT @email=lower(@email) -- SELECT @email=lower(replace(coalesce(@firstname, 'xxxxx')+'.'+coalesce(@lastname, 'xxxxx'),' ','')+'@domain.com') declare @deleted bit SELECT @deleted= case when @activeind='a' then 0 else 1 end FETCH NEXT FROM the_cursor INTO @username, @firstname, @lastname, @activeind END CLOSE the_cursor DEALLOCATE the_cursor drop table userz set nocount off [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.dbsupport.SqlScript - Executing SQL: use database [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.dbsupport.SqlScript - Executing SQL: set nocount on create table userz ( username char(30), firstname char(30), lastname char(30), activeind char(1) ) insert into userz (username, firstname, lastname, activeind) Values('jsmith', 'John', 'Smith','a') declare @username varchar(30), @firstname varchar(255), @lastname varchar(255), @activeind char(1) DECLARE the_cursor CURSOR FAST_FORWARD LOCAL FOR SELECT username, firstname, lastname, activeind FROM userz OPEN the_cursor FETCH NEXT FROM the_cursor INTO @username, @firstname, @lastname, @activeind WHILE (@@FETCH_STATUS &lt;&gt; -1) BEGIN declare @email varchar(255) SELECT @email=replace(coalesce(@firstname, 'xxxxx')+'.'+coalesce(@lastname, 'xxxxx'),' ','')+'@domain.com' SELECT @email=lower(@email) -- SELECT @email=lower(replace(coalesce(@firstname, 'xxxxx')+'.'+coalesce(@lastname, 'xxxxx'),' ','')+'@domain.com') declare @deleted bit SELECT @deleted= case when @activeind='a' then 0 else 1 end FETCH NEXT FROM the_cursor INTO @username, @firstname, @lastname, @activeind END CLOSE the_cursor DEALLOCATE the_cursor drop table userz set nocount off [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.command.DbMigrate - Successfully completed and committed migration of schema [dbo] to version 57.3 [2013-07-08 14:37:31] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.command.DbMigrate - Finished migrating schema [dbo] to version 57.3 (execution time 00:00.060s) [2013-07-08 14:37:32] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.metadatatable.MetaDataTableImpl - MetaData table [dbo].[schema_version] successfully updated to reflect changes </code></pre> <p>As you see, it correctly identifies and then executes both statements. </p> <p>But, if I comment out the two SELECT @email statements, and uncomment the third one, I get this output, and a failed migration. It only identifies one statement (according to its own logging) but then runs two statements anyway - the second one didn't trim the 'go' from the bottom. again, the <em>only</em> change between the two is the SELECT statements being used in the cursor loop. </p> <pre class="lang-sql prettyprint-override"><code>[2013-07-08 14:41:54] [] INFO localhost-startStop-1 com.googlecode.flyway.core.command.DbMigrate - Migrating schema [dbo] to version 57.3 [2013-07-08 14:41:54] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.dbsupport.SqlScript - Found statement at line 1: use database [2013-07-08 14:41:54] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.dbsupport.SqlScript - Executing SQL: use database [2013-07-08 14:41:54] [] DEBUG localhost-startStop-1 com.googlecode.flyway.core.dbsupport.SqlScript - Executing SQL: set nocount on create table userz ( username char(30), firstname char(30), lastname char(30), activeind char(1) ) insert into userz (username, firstname, lastname, activeind) Values('jsmith', 'John', 'Smith','a') declare @username varchar(30), @firstname varchar(255), @lastname varchar(255), @activeind char(1) DECLARE the_cursor CURSOR FAST_FORWARD LOCAL FOR SELECT username, firstname, lastname, activeind FROM userz OPEN the_cursor FETCH NEXT FROM the_cursor INTO @username, @firstname, @lastname, @activeind WHILE (@@FETCH_STATUS &lt;&gt; -1) BEGIN declare @email varchar(255) -- SELECT @email=replace(coalesce(@firstname, 'xxxxx')+'.'+coalesce(@lastname, 'xxxxx'),' ','')+'@domain.com' -- SELECT @email=lower(@email) SELECT @email=lower(replace(coalesce(@firstname, 'xxxxx')+'.'+coalesce(@lastname, 'xxxxx'),' ','')+'@domain.com') declare @deleted bit SELECT @deleted= case when @activeind='a' then 0 else 1 end FETCH NEXT FROM the_cursor INTO @username, @firstname, @lastname, @activeind END CLOSE the_cursor DEALLOCATE the_cursor drop table userz set nocount off go [2013-07-08 14:41:54] [] ERROR localhost-startStop-1 com.googlecode.flyway.core.command.DbMigrate - com.googlecode.flyway.core.api.FlywayException: Error executing statement at line 3: set nocount on create table userz ( username char(30), firstname char(30), lastname char(30), activeind char(1) ) insert into userz (username, firstname, lastname, activeind) Values('jsmith', 'John', 'Smith','a') declare @username varchar(30), @firstname varchar(255), @lastname varchar(255), @activeind char(1) DECLARE the_cursor CURSOR FAST_FORWARD LOCAL FOR SELECT username, firstname, lastname, activeind FROM userz OPEN the_cursor FETCH NEXT FROM the_cursor INTO @username, @firstname, @lastname, @activeind WHILE (@@FETCH_STATUS &lt;&gt; -1) BEGIN declare @email varchar(255) -- SELECT @email=replace(coalesce(@firstname, 'xxxxx')+'.'+coalesce(@lastname, 'xxxxx'),' ','')+'@domain.com' -- SELECT @email=lower(@email) SELECT @email=lower(replace(coalesce(@firstname, 'xxxxx')+'.'+coalesce(@lastname, 'xxxxx'),' ','')+'@domain.com') declare @deleted bit SELECT @deleted= case when @activeind='a' then 0 else 1 end FETCH NEXT FROM the_cursor INTO @username, @firstname, @lastname, @activeind END CLOSE the_cursor DEALLOCATE the_cursor drop table userz set nocount off go [2013-07-08 14:41:54] [] ERROR localhost-startStop-1 com.googlecode.flyway.core.command.DbMigrate - Caused by java.sql.SQLException: Incorrect syntax near 'go'. [2013-07-08 14:41:54] [] ERROR localhost-startStop-1 org.springframework.web.context.ContextLoader - Context initialization failed </code></pre> <p>And yes, my real migration does actually do something inside the loop - it calls a stored procedure to insert the new user. This is demo code I made in the hopes that anyone could run and reproduce. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAnna
    1. USMikeCWpg
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI've just noticed the pre tags ate the "not equals" <> in the above SQL statement while loops. They're actually there.
      singulars
      1. POStatement parsing bug in flyway?
      1. USMikeCWpg
    2. COUnfortunately I can't answer your question but indent the code by 4 spaces (or highlight it and click the `{}` button) instead of using `<pre>` or `<code>` tags to avoid code such as `<>` being hidden.
      singulars
      1. POStatement parsing bug in flyway?
      1. USAnna
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload