Running alter database scripts

[ggmueller]

In Roundhouse 0.7 you cannot run all ALTER DATABASE scripts from within the up folder.
It would be great to introduce an additional folder that allows running these scripts.
Therefore it would be also great to have a way to inject the database name into the script, to be able to change the database name during the build.

Example:

On SQL Server running:
ALTER DATABASE DbName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

results in an exception:

2011-05-24 12:15:32,610 [ERROR] - roundhouse.databases.sqlserver.SqlServerDatabase with provider System.Data.SqlClient does not provide a facility for recording scripts run at this time. could not insert: [roundhouse.model.ScriptsRun][SQL: INSERT INTO RoundhousE.ScriptsRun (version_id, script_name, text_of_script, text_hash, one_time_script, entry_date, modified_date, entered_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?); select SCOPE_IDENTITY()] 2011-05-24 12:15:32,624 [ERROR] - RoundhousE encountered an error. NHibernate.Exceptions.GenericADOException: could not insert: [roundhouse.model.ScriptsRun][SQL: INSERT INTO RoundhousE.ScriptsRun (version_id, script_name, text_of_script, text_hash, one_time_script, entry_date, modified_date, entered_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?); select SCOPE_IDENTITY()] ---> System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObje ct stateObj) at System.Data.SqlClient.TdsParserStateObject.WriteSni() at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode) at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush() at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) at NHibernate.Id.IdentityGenerator.InsertSelectDelegate.ExecuteAndExtract(IDbC ommand insert, ISessionImplementor session) at NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder) --- End of inner exception stack trace --- at NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder) at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlCommandInfo sql, Object obj, ISessionImplementor session) at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Object obj, ISessionImplementor session) at NHibernate.Action.EntityIdentityInsertAction.Execute() at NHibernate.Engine.ActionQueue.Execute(IExecutable executable) at NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(O bject entity, EntityKey key, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) at NHibernate.Event.Default.AbstractSaveEventListener.PerformSave(Object entity, Object id, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) at NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Obje ct entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGenerated OrRequestedId(SaveOrUpdateEvent event) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient (SaveOrUpdateEvent event) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.PerformSaveOrUpda te(SaveOrUpdateEvent event) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(Sa veOrUpdateEvent event) at NHibernate.Impl.SessionImpl.FireSaveOrUpdate(SaveOrUpdateEvent event) at NHibernate.Impl.SessionImpl.SaveOrUpdate(Object obj) at roundhouse.infrastructure.persistence.Repository.save_or_update[T](T item) at roundhouse.databases.DefaultDatabase1.insert_script_run(String
script_name, String sql_to_run, String sql_to_run_hash, Boolean
run_this_script_once, Int64 version_id)
at
roundhouse.migrators.DefaultDatabaseMigrator.record_script_in_scripts_run_t able(String
script_name, String sql_to_run, Boolean run_this_script_once, Int64
version_id)
at roundhouse.migrators.DefaultDatabaseMigrator.run_sql(String
sql_to_run, String script_name, Boolean run_this_script_once, Boolean
run_this_script_every_time, Int64 version_id, Environment environment,
String repository_version, String repository_path)
at
roundhouse.runners.RoundhouseMigrationRunner.traverse_files_and_run_sql(Str ing
directory, Int64 version_id, MigrationsFolder migration_folder,
Environment migrating_environment, String repository_version)
at roundhouse.runners.RoundhouseMigrationRunner.run()`

[BiggerNoise]

Is this perhaps specific to ENABLE_BROKER? I'm not familiar with the option, but at http://msdn.microsoft.com/en-us/library/bb522682.aspx it states:

"This example uses the termination option WITH ROLLBACK IMMEDIATE in the first ALTER DATABASE statement. All incomplete transactions will be rolled back and any other connections to the AdventureWorks2008R2 sample database will be immediately disconnected."

That sounds like that might be what is happening.

[ferventcoder]

From the thread on the chucknorrisframework group (http://groups.google.com/group/chucknorrisframework/browse_thread/thread/42bf5de06a51f07b), I asked for this to be logged as an issue. It appears the connection wasn't allowed to record the alter statement. This happens due to the usage of NHibernate to record scripts, which is done on a second connection. If that above implicitly sets the database into single user, it would cause this to happen.

[BiggerNoise]

I wasn't aware that the script updates were going in on a separate connection; given how the MS docs say the command behaves, I suppose there's no real mystery as to what is happening.

This one should be a hoot to sort out :).

[ferventcoder]

It is an estimated guess that it is on a second connection. It might be on the same one. We tried some alter scripts recently and watched it time out.

[ferventcoder]

If I remember correctly, their is a connection to run the script and a connection to record the script (since the recording is done with NHibernate).

[BiggerNoise]

Did you have the ROLLBACK_IMMEDIATE on your alter scripts? Reading through that MS doc page, it appears that if you don't, you can end up with a timeout.

Wish I was better with NHibernate, but I think that the session factory has to be specially wired up so that it can join an existing transaction. That might be there, but I don't see it. If that's not too hard to do then it might not be that bad to address this issue.

[ggmueller]

Yes, ROLLBACK IMMEDIATE was defined, but it looks like this resets your connection

[ferventcoder]

This is fixed in release 330, but wait for the next version. There is now a folder known as alterdatabase to put your alter database scripts in - it comes with a commandtimeoutadmin option as well. The default timeout for administration scripts is 300 seconds.
This is a dark feature, and will be documented when we are no longer seeing kinks.

[ggmueller]

Great stuff, looking forward for the next version.

And I'm really loving the dark side ;-)

On Tue, Jun 28, 2011 at 5:07 PM, ferventcoder <
[email protected]>wrote:

This is fixed in release 330, but wait for the next version. There is now
a folder known as alterdatabase to put your alter database scripts in - it
comes with a commandtimeoutadmin option as well. The default timeout for
administration scripts is 300 seconds.
This is a dark feature, and will be documented when we are no longer seeing
kinks.

Reply to this email directly or view it on GitHub:
#6 (comment)

[ferventcoder]

It seems right now something is a little messed up if you are using the database refresh. The exe works fine.

[ferventcoder]

Fixed in a1d064a

[jwynveen]

I just got this exact same error with a script I have in the AlterDatabase directory. It's a script to backup and restore the database before running any other scripts. It happens within a minute of Roundhouse starting, so I don't think it's the timeout thing. I would assume it has to do with the 'set single_user'. What can I do to fix this?

FYI, the connection string for this build points to the medela_stage database, so based on the timing of the error in my log files, it looks like the error probably happens after the backup, as soon as the alter is executed.

BACKUP DATABASE [medela] TO  DISK = N'C:\Share\medela_snapshot.bak' WITH NOFORMAT, INIT,  NAME = N'medela-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

use [master]
ALTER DATABASE  [medela_stage] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [medela_stage] 
FROM  DISK = N'C:\Share\medela_snapshot.bak' 
WITH  FILE = 1,  
MOVE N'medela_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MEDELA\MSSQL\DATA\medela_stage.mdf',  
MOVE N'ftrow_siteSearch' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MEDELA\MSSQL\DATA\medela_stage.ndf',  
MOVE N'medela_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MEDELA\MSSQL\DATA\medela_stage.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

I am running 0.8.5 which I just pulled from Nuget yesterday.

[jwynveen]

I just realized that I can use the Restore option to accomplish what I'm trying to do with that script. Even though I can't do a backup immediately beforehand, I can at least restore from the most recent backup I have stored on my DB server, which should be sufficient for what I'm doing.

Although I am still curious if there was some way to fix the script to make it work in the AlterDatabase folder, since this task makes it sound like it should be possible.

[ferventcoder]

Single user is likely the issue. RH runs two concurrent connections, one for the database itself and one for the master (basically the admin connection). Usually it has the db connection closed while it is performing the work in the alterdatabase folder, so it could be a bug.

If you run /debug you will get worlds more information into what the problem might be. But I may have just fixed the /debug and it might be coming out in 0.8.6. You may want to grab the latest build off of TeamCity (http://teamcity.codebetter.com/ under the ChuckNorris project) and see if your issue is still happening.

[kgress1]

I have a script to turn on the "contained database" feature for SQL Server 2012. It needs to put the db in single user mode to get an exclusive lock.

ALTER DATABASE {{DatabaseName}} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- Set db to be "contained"
ALTER DATABASE {{DatabaseName}} SET CONTAINMENT = PARTIAL WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE {{DatabaseName}} SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

The script runs fine, but the first thing Roundhouse does after it finishes is try to run a dynamic statement in the db being upgrade with sp_executesql that does an INSERT INTO RoundhousE.ScriptsRun followed by SELECT SCOPE_IDENTITY(). I get the following error:

could not insert: [roundhouse.model.ScriptsRun][SQL: INSERT INTO RoundhousE.ScriptsRun (version_id, script_name, text_of_script, text_hash, one_time_script, entry_date, modified_date, entered_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?); select SCOPE_IDENTITY()]

The process of putting the db in single user mode is closing all open connections and rolling back any open transactions. When I look a SQL trace, the INSERT statement isn't being sent to the db server. I'm guessing Roundhouse is expecting its db connection to be open, but it's not.

Any ideas?

[BiggerNoise]

What folder is your script in? Is it in up or alter database?

[kgress1]

It's in the AlterDatabase folder. I have four scripts in this folder. The first one also does an ALTER DATABASE, but it doesn't have the WITH ROLLBACK IMMEDIATE clause. It's the third one (see script above) that causes the error.

[kgress1]

Have you been able to reproduce this problem? Do you need any additional information from me? Thanks.

[BiggerNoise]

Sorry, I have not attempted to reproduce the issue. I was hoping that there would be some low hanging fruit there (if the scripts were in up). I'm probably not going to be the guy that looks into this.

I thought that alter could handle single user scripts, but I may be wrong. I've actually never used the alter folder for my work.

[ferventcoder]

@kgress1 Please open a new issue for what you are experiencing. Thanks.