[2.1-scala] Can't apply evolutions to MSSQL 2008 Database

[Tim Whitbeck]

I'm not sure if I have a configuration problem, or if this is a bug with Play.

I'm using Microsoft SQL Server 2008 R2.

Steps to reproduce:

1. Create a new play application with play new
2. Add the microsoft sql server driver sqljdbc4.jar to the "lib" folder
3. Adjust application.conf to point to a local, running instance of sql server and a freshly created, empty database
4. Add 1.sql to conf/evolutions/default/ with these contents:

# --- !Ups

create table "smi" ("id" INTEGER NOT NULL PRIMARY KEY,"ro_number" VARCHAR(254) NOT NULL);

# --- !Downs

drop table "smi";

When I navigate to the application, I get the expected error page 'Database 'default' needs evolution!'

However, when I click 'Apply this script now!' the page simply refreshes and I still see the error.

The play_evolutions table is successfully created, but my table was not created. These lines are in the log file:

2013-03-07 12:57:06,040 - [DEBUG] - from com.jolbox.bonecp.PreparedStatementHandle in New I/O  worker #1 

insert into play_evolutions values(1, 'c46eefa343788f2604ef159f0e0faf0978f19a7c', 2013-03-07, 'create table "smi" ("id" INTEGER NOT NULL PRIMARY KEY,"ro_number" VARCHAR(254) NOT NULL);', 'drop table "smi";', 'applying_up', '')

2013-03-07 12:57:06,060 - [DEBUG] - from com.jolbox.bonecp.PreparedStatementHandle in New I/O  worker #1 

update play_evolutions set last_problem = 'Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. [ERROR:273, SQLSTATE:S0001]' where id = 1

The problem appears to be attempting to set a column of type "timestamp" with a value of 2013-03-7.

Here's the documentation on Microsoft SQL Server's Timestamp datatype: http://msdn.microsoft.com/en-us/library/ms182776(v=sql.105).aspx

[Alejandro Luján]

Hi Tim,

Despite my efforts, I was not able to get Evolutions to work with MSSQL either.

I had to resort to managing evolutions manually.

[Tim Whitbeck]

If this is confirmed, I'll post a bug report on lighthouse. Does anyone else have any insight?

[Trung Ly]

Crap, google groups is so confusing.  I think I hit the Reply to Author button and the message only went to Tim.  So now I need to retype my message...

Basically, what I said was, the workaround for this problem is simply to connect to your db, drop the play_evolutions table, and recreate it with the datetime data type instead of timestamp for the field called 'applied_at':

                    create table play_evolutions (

                        id int not null primary key, hash varchar(255) not null, 

                        applied_at datetime not null, 

                        apply_script text, 

                        revert_script text, 

                        state varchar(255), 

                        last_problem text

                    )

Tim, feel free to paste my reply to you here.

Thanks.

[Marcus Heese]

Although this post is pretty old, I can still confirm that this is a good workaround for evolutions in play 2.2.1 with MS SQL Server 2012 (and probably other versions as well).