Invalid Cycle option Syntax for Create Sequence

[RLashofRegas]

Running EnsureCreated in EFCore against MariaDB get the following error:

Failed executing DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE SEQUENCE `listing_hilo` START WITH 1 INCREMENT BY 10 NO MINVALUE NO MAXVALUE NO CYCLE;
MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CYCLE' at line 1 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CYCLE' at line 1
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 43
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 81
   at MySql.Data.MySqlClient.MySqlDataReader.ReadFirstResultSetAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 307
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(MySqlCommand command, CommandBehavior behavior, ResultSetProtocol resultSetProtocol, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 292
   at MySqlConnector.Core.TextCommandExecutor.ExecuteReaderAsync(String commandText, MySqlParameterCollection parameterCollection, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 37
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 276
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 77
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)

According to the MariaDB documentation for CREATE SEQUENCE, the correct syntax for the cycle option is "NOCYCLE" rather than "NO CYCLE".

This differs from the syntax for ALTER SEQUENCE where "NO CYCLE" seems to be the correct syntax.

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/da58f640f6aa7488644c2d292d41e02e0114386f/src/EFCore.MySql/Migrations/MySqlMigrationsSqlGenerator.cs#L700

[RLashofRegas]

Note: Just noticed that this is caused by the following line in my IEntityTypeConfiguration<Listing> implementation:

builder.Property(l => l.Id) .ForSqlServerUseSequenceHiLo("listing_hilo") .IsRequired();

[mguinness]

This code was added in PR https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/pull/568 where code was copied from upstream and not tailored for MySQL/MariaDB. Seeing method ForSqlServerUseSequenceHiLo seems to indicate as much.

It would need someone to adjust this code to make it work with MariaDB and get the corresponding tests working. Also see issue https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/477 for discussion on HiLo key generation.