Issue 516 in flyway: Flyway appears to hang during migration on sqlserver 2008

[fly...@googlecode.com]

Status: New
Owner: ----
Labels: Type-Defect Priority-Medium Database-SqlServer

New issue 516 by pierc...@gmail.com: Flyway appears to hang during
migration on sqlserver 2008
http://code.google.com/p/flyway/issues/detail?id=516

I'm attempting to run an initial migration on sqlserver 2008 using command
line tools. Flyway appears to hang indefinitely during execution.

---------->
> flyway -configFile=conf\sqlserver.properties migrate
Flyway (Command-lin Tool) v.2.2.1

Creating metadata table: [dbo].[schema_version]
Current version of schema [dbo]: << Empty Schema >>
Migrating schema [dbo] to version 1.0
<----------

If I run with -X, I see the last line of debug output which is consistent
with my last line of DDL in my SQL.

---------->
... other DDL ...
DEBUG: Executing SQL: INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID)
VALUES ('user_role', 200 )

DEBUG: Executing SQL: if exists ( SELECT * FROM sysobjects WHERE name
= 'auto_pk_for_table') BEGIN DROP PROCEDURE auto_pk_for_table END
<----------

That is the last line of output and the command never returns. I've left
the command to run for over ten minutes without success. Comparing this to
running flyway successfully in my development environment against postgres
(same DDL), it runs successfully nearly instantaneously.

The same SQL runs successfully against the database when executed directly
in Microsoft SQL Server Management Studio (indicating the SQL itself is
valid).

OS: Windows Server 2008.
JDBC: Microsoft JDBC driver 4.

Any ideas would be greatly appreciated.

--
You received this message because this project is configured to send all
issue notifications to this address.
You may adjust your notification preferences at:
https://code.google.com/hosting/settings

[fly...@googlecode.com]

Comment #1 on issue 516 by pierc...@gmail.com: Flyway appears to hang

during migration on sqlserver 2008
http://code.google.com/p/flyway/issues/detail?id=516

I'm brand new to SQLServer (again, my dev environment was postgres). But I
did find and activity monitor within the SQL Management Studio. It breaks
down the processes accessing the database and I believe it shows the
problem. There are two session IDs open against my database currently:

Session X running the query:
SELECT
[version_rank],[installed_rank],[version],[description],[type],[script],[checksum],[installed_on],[installed_by],[execution_time],[success]
FROM [dbo].[schema_version] ORDER BY [version_rank]

Session Y running:
if exists (SELECT * FROM sysobjects WHERE name = 'auto_pk_for_table') BEGIN
DROP PROCEDURE auto_pk_for_table END

Session Y is blocked by session X. It is not clear what the holdup for
session X is.

[fly...@googlecode.com]

Comment #2 on issue 516 by pierc...@gmail.com: Flyway appears to hang

during migration on sqlserver 2008
http://code.google.com/p/flyway/issues/detail?id=516

- The problem is consistent (every attempted execution).

- Ctrl-C to cancel flyway ends the program, but no tables have been created
in the database

- Session Y reports the following about the block; Wait Type=LCK_M_S, Wait
Resource: keylock

- The DDL SQL was generated by Apache Cayenne (SQLServer Adapter). The hang
occurs on the conditional where Cayenne is trying to establish an automatic
PK generation procedure. The following are the last lines of my DDL. At the
time I run the migration, the "if" statement should be returning false
since auto_pk_for_table does not yet exist:

if exists (SELECT * FROM sysobjects WHERE name = 'auto_pk_for_table') BEGIN
DROP PROCEDURE auto_pk_for_table END

go
CREATE PROCEDURE auto_pk_for_table @tname VARCHAR(32), @pkbatchsize INT AS
BEGIN BEGIN TRANSACTION UPDATE AUTO_PK_SUPPORT set NEXT_ID = NEXT_ID +
@pkbatchsize WHERE TABLE_NAME = @tname SELECT NEXT_ID FROM AUTO_PK_SUPPORT
WHERE TABLE_NAME = @tname COMMIT END
go

- I've found that if I delete the "if exists...." statement, the migration
will run fine. This is a perfectly fine workaround for the time being, but
it is unfortunate that Cayenne and flyway would have this conflict.

[fly...@googlecode.com]

Updates:
Status: Migrated

Comment #3 on issue 516 by axel.fontai...@gmail.com: Flyway appears

to hang during migration on sqlserver 2008
http://code.google.com/p/flyway/issues/detail?id=516

Please repost on GitHub. This issue tracker has been obsolete for about a
year now.