Sign in Register
Go to solution
Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Losh 5 posts 35 karma points
    Sep 14, 2015 @ 12:17
    Losh
    0

    Problem upgrading db from v6 to v7 (timeout)

    Hi, We're experiencing a problem, trying to upgrade our database from v6 to v7 via umbracos installer. We already got our codebase upgraded to v7, but our site has been running on v6 for a while after - so we need to do a new db upgrade with a copy of the live db (to get new members, and content).

    This is the process I have tried:

    1. Copy v6 database

    2. Set all "uComponent" datatypes to "textbox" (As uComponents dosent support v7, and gave warnings on the installer).

    3. Install a fresh umbraco 7 via nuget

    4. Build it, point it to the new database, and let it run (seems to be running smoothly, with a nice picture of the umbraco team in the bacground, and the text "Upgrading database").

    After about 10-15 minuntes, an exception occurs in Visual Studio - with the details "Sql statement timed out".

    Here is the umbraco tracelog:

    2015-09-14 03:40:08,416 [10] INFO       Umbraco.Web.Install.Controllers.InstallApiController - [P7688/T1/D2] Executing    installation step: DatabaseInstall
2015-09-14 03:40:08,418 [10] INFO  Umbraco.Core.DatabaseContext - [P7688/T1/D2] Database configuration status: Started
2015-09-14 03:40:08,419 [10] INFO  Umbraco.Core.DatabaseContext - [P7688/T1/D2] Database requires upgrade
2015-09-14 03:40:08,419 [10] INFO  Umbraco.Web.Install.Controllers.InstallApiController - [P7688/T1/D2] Step completed (took 2ms)
2015-09-14 03:40:08,428 [10] INFO  Umbraco.Web.Install.Controllers.InstallApiController - [P7688/T1/D2] Executing installation step: DatabaseUpgrade
2015-09-14 03:40:08,429 [10] INFO  Umbraco.Web.Install.InstallSteps.DatabaseUpgradeStep - [P7688/T1/D2] Running 'Upgrade' service
2015-09-14 03:40:08,429 [10] INFO  Umbraco.Core.DatabaseContext - [P7688/T1/D2] Database upgrade started
2015-09-14 03:40:08,431 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Initializing database migrations
2015-09-14 03:40:08,431 [10] INFO  Umbraco.Core.PluginManager - [P7688/T1/D2] Starting resolution types of Umbraco.Core.Persistence.Migrations.IMigration
2015-09-14 03:40:08,434 [10] INFO  Umbraco.Core.PluginManager - [P7688/T1/D2] Completed resolution of types of Umbraco.Core.Persistence.Migrations.IMigration, found 37 (took 3ms)
2015-09-14 03:40:08,443 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AssignMissingPrimaryForMySqlKeys' to context
2015-09-14 03:40:08,443 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AssignMissingPrimaryForMySqlKeys2' to context
2015-09-14 03:40:08,553 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AdditionalIndexesAndKeys' to context
2015-09-14 03:40:08,555 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'ChangePasswordColumn' to context
2015-09-14 03:40:08,555 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AddChangeDocumentTypePermission' to context
2015-09-14 03:40:08,555 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'UpdateToNewMemberPropertyAliases' to context
2015-09-14 03:40:08,673 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AssignMissingKeysAndIndexes' to context
2015-09-14 03:40:08,673 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AddPropertyEditorAliasColumn' to context
2015-09-14 03:40:08,773 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'UpdateControlIdToPropertyEditorAlias' to context
2015-09-14 03:40:08,774 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'DropControlIdColumn' to context
2015-09-14 03:40:08,774 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AlterUserTable' to context
2015-09-14 03:40:08,831 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AddIndexToCmsMacroTable' to context
2015-09-14 03:40:08,886 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AddIndexToCmsMacroPropertyTable' to context
2015-09-14 03:40:09,238 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AlterCmsMacroPropertyTable' to context
2015-09-14 03:40:09,238 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'RemoveCmsMacroPropertyTypeTable' to context
2015-09-14 03:40:09,449 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AlterTagRelationsTable' to context
2015-09-14 03:40:09,507 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AlterTagsTable' to context
2015-09-14 03:40:09,507 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'UpdateRelatedLinksData' to context
2015-09-14 03:40:09,574 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AlterDataTypePreValueTable' to context
2015-09-14 03:40:09,782 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AddMissingForeignKeyForContentType' to context
2015-09-14 03:40:09,849 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'RemoveCmsDocumentAliasColumn' to context
2015-09-14 03:40:09,907 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Added UPGRADE migration 'AddIndexToUmbracoNodeTable' to context
2015-09-14 03:40:09,935 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Executing sql statement 1: CREATE NONCLUSTERED INDEX [IX_umbracoNodeTrashed] ON [umbracoNode] ([trashed])
2015-09-14 03:40:10,135 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Executing sql statement 2: CREATE NONCLUSTERED INDEX [IX_cmsContentVersion_ContentId] ON [cmsContentVersion] ([ContentId])
2015-09-14 03:40:10,289 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Executing sql statement 3: CREATE NONCLUSTERED INDEX [IX_cmsDocument_published] ON [cmsDocument] ([published])
2015-09-14 03:40:10,361 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Executing sql statement 4: CREATE NONCLUSTERED INDEX [IX_cmsDocument_newest] ON [cmsDocument] ([newest])
2015-09-14 03:40:10,433 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Executing sql statement 5: DROP INDEX [umbracoUserLogins_Index] ON [umbracoUserLogins]
2015-09-14 03:40:10,481 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Executing sql statement 6: CREATE CLUSTERED INDEX [IX_umbracoUserLogins_Index] ON [umbracoUserLogins] ([contextID])
2015-09-14 03:40:10,587 [10] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - [P7688/T1/D2] Executing sql statement 7: ALTER TABLE [umbracoUser] ALTER COLUMN [userPassword] NVARCHAR(500) NOT NULL
2015-09-14 03:46:19,531 [10] INFO  Umbraco.Core.Persistence.UmbracoDatabase - [P7688/T1/D2]    ved System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   ved System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   ved System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   ved System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   ved System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   ved System.Data.SqlClient.SqlDataReader.get_MetaData()
   ved System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   ved System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   ved System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   ved System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   ved System.Data.SqlClient.SqlCommand.ExecuteScalar()
   ved StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteScalar() i c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:linje 311
   ved Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClassa.<ExecuteScalarWithRetry>b__9()
   ved Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   ved Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
   ved Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command, RetryPolicy retryPolicy)
   ved Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteScalarWithRetry(IDbCommand command)
   ved Umbraco.Core.Persistence.Database.ExecuteScalar[T](String sql, Object[] args)

    I have tried multiple times, and having the db both on local, and remote servers. It always fails on the same place.

    The error occurs after the line:

    Umbraco.Core.Persistence.Migrations.MigrationRunner -    [P7688/T1/D2] Executing sql statement 7: ALTER TABLE [umbracoUser] ALTER COLUMN [userPassword]

    I have tried running that line directly in SQL, and it took < 1 second before completing successfully, so I'm guessing it's whatever next step thats causing the problem. It's also worth noting that we have over 140 000 members on our site, and the last line was regarding [umbracoUser].

    Is there a way to find the Database upgrade Sql commands from umbracos github?

    It's possible that the size of the database is causing it to time out, so anyone have any idea on how to extend the command timeout?

    Would really appreciate any thoughts on this :-)

    Copy Link

  • Comment author was deleted

    Sep 15, 2015 @ 12:08

    Yeah I would image that that amount of members is causing the timeout, what you'll need to do is increase the time out by updating the following on your sql server instance http://stackoverflow.com/questions/1137190/changing-the-commandtimeout-in-sql-management-studio

    Copy Link
Please Sign in or register to post replies

Write your reply to:

Draft