Database error on first run

[DWRoelands]

Installed completes with no issues and I am able to successfully test the database connection during the installation process.

When I try to browse to the local ContinuaCI site after installation, I get the error message below. I have confirmed that the services are running and are running under a user with full admin privileges.

•12:09:13 PM : Starting service Continua version 1.9.0.374. 82ms
•12:09:13 PM : Initialising service. 16ms
•12:09:13 PM : Migrating database. 217ms
•12:09:13 PM : Creating full database structure. 599ms
•12:09:14 PM : Error: Exception: Exception Message: An error occured executing the following sql:create view builds_buildsummaryhistory_view asselect builds.id, builds.started, builds.finished, builds.workspaceid, builds.statusid,builds.userid,builds.triggerid, builds.onlynotifytriggeringusers, builds.quietperiodlength, builds.quietperiodexpiry,builds.buildnumber, builds.version, builds.summarystate, builds.timeonqueue, config.name as configurationname,config.id as configurationid, x.total as artifactcount, users.username, users.firstname, users.lastname,trigs.name as triggername, buildchangeset.total as changesetcount, bstatus.state as buildstate, btag.totaltags as totaltags,btagname.firsttagname as firsttagname, builds.pinned as pinned, builds.pinuserid as pinuserid, builds.pincomment as pincommentfrom builds_build AS builds left outer join(select buildid, sum(1) as totalfrom builds_artifactgroup by buildid) as x on builds.id = x.buildid left outer joincore_user as users on builds.userid = users.id left outer joinbuilds_trigger AS trigs on builds.triggerid = trigs.id left outer joinbuilds_buildstatus AS bStatus on builds.statusid = bstatus.id left outer join(select buildid, sum(1) AS totalfrom builds_buildchangesetgroup by buildid) AS buildchangeset on builds.id = buildchangeset.buildid inner joinbuilds_configuration as config on builds.configurationid = config.id left outer join(select buildid, count(*) AS totaltagsfrom builds_buildtaggroup by buildid) as btag on builds.id = btag.buildid left outer join(select buildid, min(name) AS firsttagnamefrom builds_buildtaggroup by buildid) as btagname on builds.id = btagname.buildidwhere (builds.summarystate = 2) The error was The multi-part identifier "bstatus.id" could not be bound. The multi-part identifier "bstatus.state" could not be bound. Stack Trace: at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.ExecuteNonQuery(String sql)at FluentMigrator.Runner.StopWatch.Time(Action action)at FluentMigrator.Runner.MigrationRunner.ExecuteExpressions(ICollection`1 expressions) Exception: SqlException Message: The multi-part identifier "bstatus.id" could not be bound. The multi-part identifier "bstatus.state" could not be bound. Stack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.ExecuteNonQuery(String sql). 100ms
•12:09:14 PM : Could not initialise database: An error occured executing the following sql:create view builds_buildsummaryhistory_view asselect builds.id, builds.started, builds.finished, builds.workspaceid, builds.statusid,builds.userid,builds.triggerid, builds.onlynotifytriggeringusers, builds.quietperiodlength, builds.quietperiodexpiry,builds.buildnumber, builds.version, builds.summarystate, builds.timeonqueue, config.name as configurationname,config.id as configurationid, x.total as artifactcount, users.username, users.firstname, users.lastname,trigs.name as triggername, buildchangeset.total as changesetcount, bstatus.state as buildstate, btag.totaltags as totaltags,btagname.firsttagname as firsttagname, builds.pinned as pinned, builds.pinuserid as pinuserid, builds.pincomment as pincommentfrom builds_build AS builds left outer join(select buildid, sum(1) as totalfrom builds_artifactgroup by buildid) as x on builds.id = x.buildid left outer joincore_user as users on builds.userid = users.id left outer joinbuilds_trigger AS trigs on builds.triggerid = trigs.id left outer joinbuilds_buildstatus AS bStatus on builds.statusid = bstatus.id left outer join(select buildid, sum(1) AS totalfrom builds_buildchangesetgroup by buildid) AS buildchangeset on builds.id = buildchangeset.buildid inner joinbuilds_configuration as config on builds.configurationid = config.id left outer join(select buildid, count(*) AS totaltagsfrom builds_buildtaggroup by buildid) as btag on builds.id = btag.buildid left outer join(select buildid, min(name) AS firsttagnamefrom builds_buildtaggroup by buildid) as btagname on builds.id = btagname.buildidwhere (builds.summarystate = 2) The error was The multi-part identifier "bstatus.id" could not be bound. The multi-part identifier "bstatus.state" could not be bound. Check the eventlog for more info.

[Sparky (Dave Sparks)]

Hi,

Can you let us know which version of SQL Server you are running and which database version you chose when running the installer? Was this a first install or upgrade?

[Sparky (Dave Sparks)]

Hi,

We have reproduced this issue with a SQL server database that is setup with a case-sensitive collation. To check the collation of your database, run the following SQL query:

SELECT collation_name FROM sys.databases WHERE name = '{your_database_name}'

To fix the issue, either recreate the database with the collation set to SQL_Latin1_General_CP1_CI_AS or run the following SQL statements (one at a time):

ALTER DATABASE {your_database_name} SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE {your_database_name} COLLATE SQL_Latin1_General_CP1_CI_AS

ALTER DATABASE {your_database_name} SET MULTI_USER

Then restart the Continua CI Server service.

We are currently working on changes to the database configuration code to automatically set the collation for new databases on start up. This should be ready for the next version.

[DWRoelands]

Thanks for the update!

[DWRoelands]

I am still unable to set up Continua on SQL Server.

I created a new empty database with SQL_Latin1_General_CP1_CI_AS collation.

I ran the installer and configured the services to run as me, as I have full admin access to my machine.

The first time I run the application it now fails with this:

Application is errored: Exception: SqlException Message: Must declare the scalar variable "@securableid". 
Stack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) 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 Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1053 at Dapper.SqlMapper.<MultiMapImpl>d__153`8.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.cs:line 1423 at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at Dapper.SqlMapper.MultiMap[TFirst,TSecond,TThird,TFourth,TFifth,TSixth,TSeventh,TReturn](IDbConnection cnn, String sql, Delegate map, Object param, IDbTransaction transaction, Boolean buffered, String splitOn, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1404 at Continua.Membership.Cache.AclCache.UpdateAcl(Guid securableId) at Continua.Membership.Cache.AclCache.AclChanged(Guid securableId, ChangeType changeType) at Continua.Membership.AclManager.CreateOrUpdateACLEntry(SecurityEntity entity, ISecurable securable, IEnumerable`1 permissions, IEnumerable`1 roles, Boolean enforcePermisisons) at Continua.Membership.MembershipDefaultsManager.CreateGuestUserAndRole(Boolean isFirstRun) at Continua.Membership.MembershipDefaultsManager.CreateDefaults(Boolean isFirstRun) at Continua.ServerApplication.CreateDefaults(ISession context) at Continua.ServerApplication.OnApplicationStart(IContainer container) at Continua.Service.Common.ApplicationServiceBase.OnStart(String[] args)

Any suggestions would be welcome.

[Sparky (Dave Sparks)]

Hi,

Apologies for this poor first installation experience. Unfortunately, we have tried to reproduce this latest error today but cannot get it to occur with a new database regardless of the collation.

Can you provide a little more information so we can ensure that we are testing with the exact same scenario? Which version of SQL Server you are using? Did you run the new version (1.9.0.426) when the latest error occurred or are you still running 1.9.0.374?

Can you also create a full database creation script containing all the default database properties? You can do this by right-clicking on the database in SQL Server Management Studio and selecting Script Database as > CREATE To > File. Send us the file and we use it to create a test database so we can hopefully get to the bottom of this issue.

[DWRoelands]

I was using SQL Server 2008 R2, and I believe I was running Continua v1.9.0.413.

I’ve scripted out the Continua Database, but there’s not much there.ContinuaCI.txt (2.3 KB)

[Sparky (Dave Sparks)]

We’ve done some testing with the same clean database on SQL Server 2008 R2, but unfortunately we have been able to reproduce this error.

Can you check that the version of SQL is correct in the server configuration file:%ProgramFiles%\VSoft Technologies\ContinuaCI\Server\Continua.Server.Service.exe.config? It should have MsSql2008Dialect for the dialect property under hibernate-configuration -> session-factory e.g.

<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
<property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
<property name="connection.connection_string">Server=ServerName\Instance;Initial Catalog=ContinuaCI;Trusted_Connection=True;</property>

Otherwise, can you try uninstalling Continua CI, removing any left over configuration files from %ProgramFiles%\VSoft Technologies, before dropping and recreating a new database, then reinstalling Continua CI.

If you still see the error, can you enable debug logging in the server configuration file, restart the service, then send the debug log file to me as a direct message (or by email to support at finalbuilder.com)

[DWRoelands]

So, I uninstalled from Control Panel, and the uninstaller left three services on my system. I don’t know why there’s a PostgreSQL service as I’ve never done a PostgreSQL install.

So, I’ve uninstalled and rebooted my computer. I download the latest version of the installer from the VSoft website and run it. Now I get this:

Obviously I don’t want to keep my existing settings because the existing settings don’t work. So I choose to “Update my existing settings”.

I configure the database settings and successfully test the connection::

I proceed to finish the wizard and get this:

So I am now in a position where I can’t uninstall Continua because it doesn’t show up in Add/Remove Programs. I also cannot INSTALL it because the installer will not complete. My system is now in an unknown state where I don’t know which VSoft components are installed and which ones are not.

I would very much like to remove every trace of Continua and its components from this computer. Please advise.

[DWRoelands]

I’m documenting this here for other users who may find themselves in similar circumstances.

I was able to delete the services that the uninstaller left behind by executing the following from an elevated command prompt:

I then had to open Internet Information Services and manually delete the Continua site.

Even after doing these things, the Continua Installer still thinks that Continua is installed. I’m not sure what the conditions need to be for the installer to not behave this way.

[DWRoelands]

I believe that I have finally gotten Continua off of my computer.

I ran the installer, and chose the option to “Keep My Existing Settings”. It reaches a point of failure where it is complaining about not being able to update PostgreSQL (again, I’ve never installed Continua with PostgreSQL). No matter.

At this point, I aborted the installation. I went to Control Panel and uninstalled the Contnua Server and the Continua Agent. Then I manually removed the site in IIS. Then I deleted the c:\users\Continua and c:\ProgramData\VSoft\Continua folders.

At this point, I think my system is clean.

I also think that my evaluation of Continua has provided me with the information that I need. I’m grateful to the VSoft folks for their help.

[Sparky (Dave Sparks)]

@DWRoelands

So, I uninstalled from Control Panel, and the uninstaller left three services on my system. I don’t know why there’s a PostgreSQL service as I’ve never done a PostgreSQL install.

The uninstaller is coded to remove all services. This works in our tests. If it doesn’t then there should be an error message shown or logged to the Windows Event log.

The PostgreSQL service is only installed if this is option is selected on the Database page during the installation. We have tested this several times today and in the past.

@DWRoelands

I proceed to finish the wizard and get this:

The installer attempts to back up any existing PostgreSQL database only if the PostgreSQL service exists and the file {commonappdata}\VSoft\ContinuCI\PostgreSQLDB\PG_VERSION exists. This service and file will only exist if a PostgreSQL installation option was selected in a previous installation. If an error occurs then this message will appear allowing you to answer No to continue the installation. Again, it will not install the PostgresSQL database unless the option has been selected.

We don’t really need to backup the existing database in this scenario, and can see that this is confusing, so we’re looking into changing the installer so that the existing database backup is not performed during an upgrade when the PostgreSQL option has not been selected.

Note that the installer generates a log file which matches the pattern “Setup Log YYYY-MM-DD #XXX.txt” under %LocalAppData%\Temp. This can be used to work out what has happened (or gone wrong) during the installation.

@DWRoelands

I also think that my evaluation of Continua has provided me with the information that I need.

As you have not managed to get Continua CI up and running, it’s unfortunate that you have not had the opportunity to fully evaluate it. If you had, we’re confident that you would have found a lot to like.

@DWRoelands

I’m grateful to the VSoft folks for their help.

We are always ready to help and, although we don’t generally hear about issues with the installer, are usually able to get people up and running swiftly if we are given enough information.