SQL Azure Issue? "Tables without a clustered index are not supported in this version of SQL"

[drewid]

Hello,

I got the following error when I changed the ConnectionString to use SQL Azure. It was working fine with LocalDB.

Error refers to BrockAllen.MembershipReboot.Ef line 54: db.SaveChanges();

Error: "Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again."

Any idea how this can be tweaked to work? Really need to use SQL Azure.

Thanks

[drewid]

Sorry; the error was itself enough to solve things. Thought it might be something more complex and related to injection and/or dbcontexts and/or repositories.

With SQL Azure you have to add a clustered index no matter what. For instance, For UserClaims:

DROP TABLE [dbo].[UserClaims];

GO CREATE TABLE [dbo].[UserClaims]([UserAccountID] UNIQUEIDENTIFIER NOT NULL, [Type] NVARCHAR %28150%29 NOT NULL, [Value] NVARCHAR %28150%29 NOT NULL, CONSTRAINT [PK_UserClaims] PRIMARY KEY CLUSTERED %28 [UserAccountID], [Type] %29);

Once you have your tables all have the clustered primary key it is very easy to swap back and forth between LocalDB and Sql Azure as the underlying plumbing in the project and web.config is the same.

[brockallen]

Is this something than can/should be done in the MR EF code, you think? I'll reopen until I have time to investigate. Thanks for the info.

[brockallen]

@drewid -- i added back migrations to the ef project (mainly because msft doesn't support sql compact anymore, so there's only one flavor to support). mind looking at them and seeing if they have the indexes you/azure needs?

[drewid]

Sorry - just saw this, will take a look at it, and yes, is nice to only need to support the one version (as long as you use the least common denominator of what works with SQL Azure - and for relatively straight forward sql needs shouldn't bee too much of an issue)

[brockallen]

Ok, I think all the rework I've done recently addresses this. If not, please re-open.

[ultragizmoboard]

I'm actually still seeing this issue out of the box. I also can't get it running in Azure right now as it seems like everything correctly has a clustered index. I'm not sure what the error message is referring to at this point.

Is anyone else seeing this issue? It's possible I just have some weird config in my world somewhere.

E.g. when I get SSMS to create a script for PK_dbo.UserAccounts it gives me:

/\ Object: Index [PK_dbo.UserAccounts] Script Date: 12/14/2013 19:12:05 **/ ALTER TABLE [dbo].[UserAccounts] ADD CONSTRAINT [PK_dbo.UserAccounts] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO

Which looks correct... All the others are similar. However I still get:

"Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again."

[brockallen]

Have you tested with the latest source from github?

[ultragizmoboard]

Ya, I'm sitting on: cbdf5da65973182824d999f02e816a5bb4f8123a

There was a bug in a beta version of EF6 that caused __MigrationHistory to have a non clustered index but that was fixed in the version I'm on (6.0.2). And in this case I don't see any table without a clustered index.

I do a git clean -fdx, rebuild, delete all the tables that MR creates plus __MigrationHistory, deploy locally to the emulator, invoke the relevant code, notice the correct tables are created in Azure and then get the exception.

DbContextRepository->Create where T is a UserAccount.

System.Data.SqlClient.SqlException occurred HResult=-2146232060 Message=Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again. The statement has been terminated. Source=.Net SqlClient Data Provider ErrorCode=-2146232060 Class=16 LineNumber=1 Number=40054 Procedure="" Server=.database.windows.net State=1 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TInterceptionContext,TResult](Func1 operation, TInterceptionContext interceptionContext, Action1 executing, Action1 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass32.<ExecuteStatements>b__2e() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable1 operations, IEnumerable1 systemOperations, Boolean downgrading, Boolean auto) at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration) at System.Data.Entity.Migrations.DbMigrator.<>cDisplayClassc.bb() at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update() at System.Data.Entity.MigrateDatabaseToLatestVersion2.InitializeDatabase(TContext context) at System.Data.Entity.Internal.InternalContext.<>c__DisplayClasse1.bd() at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() at System.Data.Entity.Internal.LazyInternalContext.b4(InternalContext c) at System.Data.Entity.Internal.RetryAction1.PerformAction(TInput input) at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action1 action) at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase() at System.Data.Entity.Internal.InternalContext.Initialize() at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) at System.Data.Entity.Internal.Linq.InternalSet1.Initialize() at System.Data.Entity.Internal.Linq.InternalSet1.get_InternalContext() at System.Data.Entity.Internal.Linq.InternalSet1.Create() at System.Data.Entity.DbSet1.Create() at BrockAllen.MembershipReboot.Ef.DbContextRepository`1.BrockAllen.MembershipReboot.IRepository.Create() InnerException:

[dandresini]

For SQL AZURE need fix some part of migration code for working fine: when I execute IniziatCreate I have a Warnig "The maximum key length is 900 bytes. The index 'PK_dbo.LinkedAccountClaims' has maximum length of 916 bytes. For some combination of large values, the insert/update operation will fail.", in this moment not is important because the index is recreated in second step of migration. In second step if you changed the name of index column, the code go on error; also the operation can't be performed if there isn't a primary key. To solve the bug I create first a key index, and then a new column, updated value from older to new column and then drop the older column

        AddPrimaryKey("dbo.UserCertificates", "Thumbprint");
        AddColumn("dbo.UserCertificates", "UserAccount_ID", c => c.Guid(nullable: false));
        CreateIndex("dbo.UserCertificates", "UserAccount_ID");
        AddForeignKey("dbo.UserCertificates", "UserAccount_ID", "dbo.UserAccounts", "ID", cascadeDelete: true);
        this.Sql(@"UPDATE dbo.UserCertificates SET UserAccount_ID=UserAccountID");
        DropColumn("dbo.UserCertificates", "UserAccountID");

        AddPrimaryKey("dbo.UserClaims", new[] { "Type", "Value" });
        AddColumn("dbo.UserClaims", "UserAccount_ID", c => c.Guid(nullable: false));
        this.Sql(@"UPDATE dbo.UserClaims SET UserAccount_ID=UserAccountID");
        CreateIndex("dbo.UserClaims", "UserAccount_ID");
        AddForeignKey("dbo.UserClaims", "UserAccount_ID", "dbo.UserAccounts", "ID", cascadeDelete: true);
        DropColumn("dbo.UserClaims", "UserAccountID");

for LinkedAccounts table we have that the providername column is a part of primary key, so i create a temporany primary key with unique value, I modified the column and then I recreate the key:

       AddPrimaryKey("dbo.LinkedAccounts", new[] { "ProviderAccountID","LastLogin" });
        AlterColumn("dbo.LinkedAccounts", "ProviderName", c => c.String(maxLength: 30,nullable:false));
        DropPrimaryKey("dbo.LinkedAccounts");
        AddPrimaryKey("dbo.LinkedAccounts", new[] { "ProviderName", "ProviderAccountID" });
        AddColumn("dbo.LinkedAccounts", "UserAccount_ID", c => c.Guid(nullable: false));
        this.Sql(@"UPDATE dbo.LinkedAccounts SET UserAccount_ID=UserAccountID");
        CreateIndex("dbo.LinkedAccounts", "UserAccount_ID");
        DropColumn("dbo.LinkedAccounts", "UserAccountID");
        AddForeignKey("dbo.LinkedAccounts", "UserAccount_ID", "dbo.UserAccounts", "ID", cascadeDelete: true);

        AddPrimaryKey("dbo.LinkedAccountClaims", new[] { "Type", "Value" });
        AddColumn("dbo.LinkedAccountClaims", "LinkedAccount_ProviderName", c => c.String(nullable: false, maxLength: 30));
        AddColumn("dbo.LinkedAccountClaims", "LinkedAccount_ProviderAccountID", c => c.String(nullable: false, maxLength: 100));
        CreateIndex("dbo.LinkedAccountClaims", new[] { "LinkedAccount_ProviderName", "LinkedAccount_ProviderAccountID" });
        this.Sql(@"UPDATE dbo.LinkedAccountClaims SET  LinkedAccount_ProviderName=CONVERT(nvarchar(30),ProviderName), LinkedAccount_ProviderAccountID=UserAccountID");
        AddForeignKey("dbo.LinkedAccountClaims", new[] { "LinkedAccount_ProviderName", "LinkedAccount_ProviderAccountID" }, "dbo.LinkedAccounts", new[] { "ProviderName", "ProviderAccountID" }, cascadeDelete: true);
        DropColumn("dbo.LinkedAccountClaims", "ProviderAccountID");
        DropColumn("dbo.LinkedAccountClaims", "ProviderName");

Now the code work fine. Now I solve downgrade migration.

[ultragizmoboard]

@dandresini running your code during migration throws the following:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in EntityFramework.dll

Additional information: The constraint 'PK_dbo.LinkedAccounts' is being referenced by table 'LinkedAccountClaims', foreign key constraint 'FK_dbo.LinkedAccountClaims_dbo.LinkedAccounts_LinkedAccount_ProviderName_LinkedAccount_ProviderAccountID'.

Could not drop constraint. See previous errors.

[dandresini]

Ok, Now for me worked fine. I prefer to use a new configuration for SqlAzure. In Ef project I create a new class SqlAzureMembershioRebootDatabase:

  namespace BrockAllen.MembershipReboot.Ef
  {
    public class SqlAzureMembershipRebootDatabase : DefaultMembershipRebootDatabase
    {
        public SqlAzureMembershipRebootDatabase(): base("name=MembershipReboot"){}

        public SqlAzureMembershipRebootDatabase(string nameOrConnectionString): base(nameOrConnectionString){}

        public DbSet<UserAccount> Users { get; set; }
        public DbSet<Group> Groups { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
           modelBuilder.ConfigureMembershipRebootUserAccounts<UserAccount>();
           modelBuilder.ConfigureMembershipRebootGroups<Group>();
        }

   }

Then in Configuration.cs I had created a new sealed class ConfigurationAzure:

  public sealed class ConfigurationAzure : DbMigrationsConfiguration<BrockAllen.MembershipReboot.Ef.SqlAzureMembershipRebootDatabase>
   {
      public ConfigurationAzure()
      {
        AutomaticMigrationsEnabled = false;
        ContextKey = "BrockAllen.MembershipReboot.Ef.SqlAzureMembershipRebootDatabase";
        MigrationsDirectory = @"Migrations\SqlAzure";
        MigrationsNamespace = "BrockAllen.MembershipReboot.Ef.Migrations.SqlAzure";
      }

      protected override void Seed(BrockAllen.MembershipReboot.Ef.SqlAzureMembershipRebootDatabase context){}

     }

Before to create file for migration I modify the value of Type in BrockAllen.MembershipReboot.LinkedAccountClaims to StringLength of 100 (so index not exeded of length great then 900 byte)and then I execute in PMC the command for create a initial migration for SqlAzure:

 Add-Migration -ConfigurationTypeName ConfigurationAzure InitialCreate

For use this in SingleTenantWebApp example in file class NinjectWebCommon I added

   private static void RegisterEntityFrameworkSqlAzure(IKernel kernel)
    {
        System.Data.Entity.Database.SetInitializer(new System.Data.Entity.MigrateDatabaseToLatestVersion<SqlAzureMembershipRebootDatabase, BrockAllen.MembershipReboot.Ef.Migrations.ConfigurationAzure>());
        kernel.Bind<IUserAccountRepository>().ToMethod(ctx => new DefaultUserAccountRepository()).InRequestScope();
    }

and I call this function in RegisterServices. I don't know if this is the best solution but now I solved my problem in SQL AZURE database and can work in my project.

[brockallen]

Ok, this thread has gotten past me and I'd like to sort out the problem. I've been making many changes recently to the codebase -- can you @drewid let me know if the latest changes work for you? I changed some of the columns to be a bit shorter given this feedback and I think it'll fit within those azure constraints. Thx.

[ultragizmoboard]

@brockallen 31882fd723ae1f1d2644079f7d32b022e8486b43 works for me now. Thanks for your hard work!

[drewid]

@brockallen - I'll check too; to be honest, when I ran into the issues I pulled out the sql needs and created sql scripts to handle things for the database. Is not nicely built into the project as the Migrations code, but SQL Azure has been working solid with MR the past month.

[brockallen]

Ok, @drewid , I'll close this for now then. Like I said, with the latest code I reduced the width of some of those columns and I think it's within the constraints. If you ever upgrade to the latest code and run into the issue again, open a new issue. Thanks.