Bug #91938 Incorrect usage of spatial/fulltext/hash index and explicit index order on migra

• Submitted: 8 Aug 2018 22:16
• Modified: 10 Jan 2020 23:21
• Reporter: HENOC SALINAS
• Status: No Feedback
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 8.0.12
• OS: Windows (10)
• CPU Architecture: Any
• Tags: fulltext, hash, incorrect, INDEX, spatial, usage

[8 Aug 2018 22:16] HENOC SALINAS

Description:
When runing update-database of migration witn index cration, showed exception :

MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect usage of spatial/fulltext/hash index and explicit index order

How to repeat:
the entity:

namespace libProduccionDataBase.Tablas
{
	[Table ( "Try_Master" )]
	public class Master
	{
		[Key, DatabaseGenerated ( DatabaseGeneratedOption.Identity )]
		public int MasterId { get; set; }
		[Index ( IsUnique = true ), MaxLength ( 250 )]
		public string Nombre { get; set; }
		public string OtherProperty { get; set; }
		public virtual ObservableListSource<Child> Childs { get; set; } = new ObservableListSource<Child> ( );
	}
	[Table ( "Try_Child" )]
	public class Child
	{
		[Key, Column ( Order = 1 )]
		public int ChildId { get; set; }
		[Key, Column ( Order = 0 )]
		public int MasterId { get; set; }
		public string Desc { get; set; }
		public double cant { get; set; }

		[Index ( "myIndex", Order = 0 )]
		public int Region { get; set; }
		[Index ( "myIndex", Order = 1 )]
		public int Pais { get; set; }
	}
}

the context:

        [DbConfigurationType ( typeof ( MySqlEFConfiguration ) )]
	public class DataBaseContexto : IdentityDbContext<ApplicationUser, ApplicationRole, int, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>
	{
		
		public DbSet<Master> Master { get; set; }
		public DbSet<Child> Childs { get; set; }

		public DataBaseContexto () : base ( "ProduccionConexionDebug" )
		{
			
			this.Configuration.LazyLoadingEnabled = true;
		}

		public static DataBaseContexto Create () => new DataBaseContexto ( );
    }

the migration Configuration:

	internal sealed class Configuration : DbMigrationsConfiguration<libProduccionDataBase.Contexto.DataBaseContexto>
	{
		public Configuration ()
		{			
			AutomaticMigrationsEnabled = false;			
			SetSqlGenerator ( "MySql.Data.MySqlClient", new MySqlMigrationSqlGenerator ( ) );
		}

		protected override void Seed ( libProduccionDataBase.Contexto.DataBaseContexto context )
		{}
        }

Suggested fix:
Changing the MySqlMigrationSqlGenerator.Generate( CreateIndexOperation op ) for reemplace "USING HASH"  TO "USING BTREE"

[9 Aug 2018 14:08] HENOC SALINAS

for fix i create a class inherits of MySqlMigrationSqlGenerator:

	public class myMigrationSQLGenerator : MySqlMigrationSqlGenerator
	{
		private string TrimSchemaPrefix ( string table )
		{
			if ( table.StartsWith ( "dbo." ) )
				return table.Replace ( "dbo.", "" );
			return table;
		}

		protected override MigrationStatement Generate ( CreateIndexOperation op )
		{
			StringBuilder sb = new StringBuilder ( );

			sb = sb.Append ( "CREATE " );

			if ( op.IsUnique )
			{
				sb.Append ( "UNIQUE " );
			}

			//index_col_name specification can end with ASC or DESC.
			// sort order are permitted for future extensions for specifying ascending or descending index value storage
			//Currently, they are parsed but ignored; index values are always stored in ascending order.

			object sort;
			op.AnonymousArguments.TryGetValue ( "Sort", out sort );
			var sortOrder = sort != null && sort.ToString ( ) == "Ascending" ?
							"ASC" : "DESC";

			sb.AppendFormat ( "index  `{0}` on `{1}` (", op.Name, TrimSchemaPrefix ( op.Table ) );
			sb.Append ( string.Join ( ",", op.Columns.Select ( c => "`" + c + "` " + sortOrder ) ) + ") " );

			object indexTypeDefinition;
			op.AnonymousArguments.TryGetValue ( "Type", out indexTypeDefinition );

			var indexType = indexTypeDefinition != null && string.Compare ( indexTypeDefinition.ToString ( ), "Hash", StringComparison.InvariantCultureIgnoreCase ) > 0 ?
							"HASH": "BTREE" ;

			sb.Append ( "using " + indexType );

			return new MigrationStatement ( ) { Sql = sb.ToString ( ) };
		}
	}

and on Migration configuration (Configuration.cs) set my custom class:

		public Configuration ()
		{			
			AutomaticMigrationsEnabled = false;
			SetSqlGenerator ( "MySql.Data.MySqlClient", new myMigrationSQLGenerator ( ) );
		}

[10 Dec 2019 23:21] MySQL Verification Team

Please check with latest release. Thanks.

[11 Jan 2020 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".