U4-2294 - 6.1.0: umbracoServer table can't be created on MySQL <5.6.5

Created by Brian Powell 30 May 2013, 13:25:21 Updated by Shannon Deminick 18 Dec 2013, 04:31:35

Umbraco 6.1.0 creates a new table called "umbracoServer" to be used with the ServerRegistrationDto object. This table is defined as having two fields, registeredDate and lastNotifiedDate, with default values set to be the current timestamp.

Versions of MySQL before 5.6.5 only allow one field per table to have this configuration (per http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html). People using MySQL with Umbraco will need to have a recent version to do this upgrade. Not sure this is a bug per se, but it should be documented.

Here's the applicable part from the trace from when I tried doing the upgrade on 5.5.24. 2013-05-30 08:34:51,969 [10] INFO Umbraco.Core.DatabaseContext - [Thread 6] Database configuration failed with the following error and stack trace: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.b__0() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy retryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command) at Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args) at Umbraco.Core.Persistence.Database.Execute(Sql sql) at Umbraco.Core.Persistence.PetaPocoExtensions.CreateTable(Database db, Boolean overwrite, Type modelType) at Umbraco.Core.Persistence.PetaPocoExtensions.CreateTable[T](Database db) at Umbraco.Core.Persistence.Migrations.Upgrades.TargetVersionSixOneZero.CreateServerRegistryTable.Up() at Umbraco.Core.Persistence.Migrations.MigrationBase.GetUpExpressions(IMigrationContext context) at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, DatabaseProviders databaseProvider, Boolean isUpgrade) at Umbraco.Core.DatabaseContext.CreateDatabaseSchemaAndDataOrUpgrade()


Brian Powell 30 May 2013, 16:12:48

I upgraded to the current MySQL Community 5.6.11. After I did this, I was able to successfully upgrade to Umbraco 6.1.0.

Simon Justesen 31 May 2013, 12:50:35

Still, would be nice to have this working on pre-5.6 versions as well, because many shared hosts do not run the latest MySQL-versions. Should be easy to solve either in code or with a trigger on create/updates?

Brian Powell 31 May 2013, 12:59:52

I'd rather see this handled in code than creating triggers.

Simon Justesen 31 May 2013, 13:00:49

I agree :)

Sebastiaan Janssen 01 Jun 2013, 14:23:33

Sorry about that, someone is marking a bunch of issues as fixed (they're now banned).

Priority: Normal

Type: Bug

State: Fixed

Assignee: Shannon Deminick

Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions: 6.1.0

Due in version: 6.2.0


Story Points: