Timeout when migrating database after updating to 1.6.0.278

I tried updating our Continua installation to 1.6.0.278 but when starting the service afterward I get a database timeout. Continua’s debug log looks like this:

Medium: 09:40:58 {T4} [Debug] [Logging] Cleaning up existing logs which are older then 7 days.
Medium: 09:40:58 {T4} [Startup] Starting Continua version 1.6.0.278.
Medium: 09:45:00 {T4} [Database] Could not initialise database:

System.Exception: An error occurred executing the following sql:
update builds_unittest set assemblyname = LEFT(assemblyname, 256)
The error was FEHLER: 57014: storniere Anfrage wegen Zeitüberschreitung der Anfrage
 —> Npgsql.NpgsqlException: FEHLER: 57014: storniere Anfrage wegen Zeitüberschreitung der Anfrage
   at Npgsql.NpgsqlState.d__0.MoveNext()
   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
   at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
   at Npgsql.ForwardsOnlyDataReader…ctor(IEnumerable1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)<br>&nbsp;&nbsp; at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)<br>&nbsp;&nbsp; at Npgsql.NpgsqlCommand.ExecuteNonQuery()<br>&nbsp;&nbsp; at FluentMigrator.Runner.Processors.Postgres.PostgresProcessor.Process(String sql)<br>&nbsp;&nbsp; --- End of inner exception stack trace ---<br>&nbsp;&nbsp; at FluentMigrator.Runner.Processors.Postgres.PostgresProcessor.Process(String sql)<br>&nbsp;&nbsp; at FluentMigrator.Runner.StopWatch.Time(Action action)<br>&nbsp;&nbsp; at FluentMigrator.Runner.MigrationRunner.ExecuteExpressions(ICollection1 expressions)
   at FluentMigrator.Runner.MigrationRunner.ApplyMigrationUp(IMigrationInfo migrationInfo, Boolean useTransaction)
   at FluentMigrator.Runner.MigrationRunner.MigrateUp(Boolean useAutomaticTransactionManagement)
   at FluentMigrator.Runner.Initialization.TaskExecutor.Execute()
   at Continua.Migrations.Runner.Execute(String database, String connectionString, String fileName)
   at Continua.Server.Service.Migrator.Begin()
Exiting…
Medium: 09:45:00 {T4} [Startup] Application is Errored


Hope this gets resolved quickly.

Regards,
Christoph

Hi Christoph,

How big is the builds_unittest table? Can you run  

SELECT COUNT(*) FROM builds_unittest

on your database?


Can you also try running

update builds_unittest set assemblyname = LEFT(assemblyname, 256)

directly on the the database. You can do this using C:\Program Files\VSoft Technologies\ContinuaCI\Server\PostgreSQL\bin\pgAdmin3.exe. Use the connection details in C:\Program Files\VSoft Technologies\ContinuaCI\Server\Continua.Server.Service.exe.config under hibernate-configuration\session-factory\property name=“connection.connection_string”


Ouch!
SELECT COUNT(*) FROM builds_unittest returns 40,852,35!. I aborted the update after 15 minutes.
I have now changed our unit tests clean up to also delete database entries. After that the count was reduced to about 108k. The update was done in about 62 seconds on the reduced data set.

Hi Christoph,

Great that it works now. I’m just in the process creating a new build with a longer (10 minute) time out - but I guess that would not have been enough either!

After reducing the number of entries I was now also able to update to buid 278 successfully.

I’ve uploaded build 279 with a longer time out in case anyone else has similar problems. You can safety ignore it.

we cannot upgrade, too. we still have the same problem with version 279.
we have about 53k rows using [code]SELECT COUNT(*) FROM builds_unittest[/code].

Hi Christian,

Please try this version with improved migration code:

Continua CI (64-bit) v1.6.0.283.
Continua CI Agent Installer (64-bit) v1.6.0.283

Continua CI (32-bit) v1.6.0.283.
Continua CI Agent Installer (32-bit) v1.6.0.283

If the problem persists, can you post the new error message.

well, its still migrating since 45 minutes. very long time, but still doing something.

Hi Christian,

That 's really unusual. Can you switch on debug logging, restart and send the log to support@finalbuilder.com?

i’ll do this. for now, this is what is shown during startup:

08:22 : Starting
08:22 : Migrating Database
09:11 : Could not initialise database: An error occurred executing the following sql: ALTER TABLE public.builds_unittest ALTER assemblyname TYPE varchar(256), ALTER assemblyname SET NOT NULL The error was FEHLER: 57014: storniere Anfrage wegen Zeitüberschreitung der Anfrage Check the eventlog for more info.

simply a database timeout (the german part at the end).


Can you try running that command:

ALTER TABLE builds_unittest ALTER assemblyname TYPE varchar(256)

directly on the database?

It shouldn’t be taking that long. Maybe the database needs some maintenance. Can you try running the following:

VACUUM ANALYSE builds_unittest

if  this doesn’t improve it then run:

REINDEX TABLE builds_unittest


some stats about this table from pgAdmin:
active tupel: 17552056 (my previous number 53k was just the last part of the real number forgot to resize the colum in pgAdmin ;-))
table size: 4GB
index size: 16GB

in general, the size of index in the whole database is very big :frowning: well, i should check the clean up rules for old builds. maybe we can tweak something here. is it possible to remove the test results itself but keep the stats or does delete database entries everything?

can i clean up the table builds_unittest by hand so the update can proceed? currently i have no working installation. not tried to restore yet.

ok, i just truncated the unit test table then it worked instantly. now i’ve changed our clean up policies to also delete database entries so i think this will avoid the problem in future even if we lose statistics then (as the documentation says).

Hi Christian,

Great that it’s up and working now. One effect of truncating the unit test table is that the results are not displayed on the unit tests tab for the build (the totals will still be displayed). Another effect will be that all failures in the next build will be displayed as new failures.

We plan to allow more fine tuned database clean-up in the future to allow the unit test results to be cleaned up separately. We also plan to ensure that builds can be cleaned up without effecting statistics.