Installation with MySQL fails.

[Raceeend]

A fresh installation of oqtane 3.1.1 (from May 5, 2022) with mysql database fails.

Error on the screen: An Error Occurred Executing Upgrade Logic. Unknown column 's.CaptureBrokenUrls' in 'field list'

Contents of error.log
[2022-05-06 10:03:43+00:00] [Error] [Oqtane.Infrastructure.DatabaseManager] An Error Occurred Migrating A Tenant Database. This Is Usually Related To A Tenant Database Not Being In A Supported State. Cannot drop index 'IX_Folder': needed in a foreign key constraint
[2022-05-06 10:03:43+00:00] [Error] [Oqtane.Infrastructure.DatabaseManager] An Error Occurred Executing Upgrade Logic. Unknown column 's.CaptureBrokenUrls' in 'field list'

Prior to the installation, I created an empty database in MySQL. This database is filled with tables during the installation process, but not as much as with a successful installation in SqlServer.
It seems that the installation fails when processing migration 2.01.01 (removing IX_Folder index).

Database used:
MySQL version 8.0.15 and 8.0.29 in a docker container.
The installation with SQLite and SqlServer does work.

Question is, what am I doing wrong.

[sbwalker]

I will have to investigate further. Providing multiple database support for an application is definitely way much more challenging than most people imagine - even though EF Core supports it in theory, there are many exception conditions which need to be handled by the developer.

[sbwalker]

As I expected, it appears that MySQL has some odd behavior https://stackoverflow.com/questions/8482346/mysql-cannot-drop-index-needed-in-a-foreign-key-constraint

[Raceeend]

If I look at the source code. If in IDatabase interface a 'DropIndex' added. In DatabaseBase defaults to the MigrationBuilder.DropIndex. And in the Oqtane.Database.MySQL the function DropIndex is provided with the correct SQL statements. Assum that the correct foreignkey name can be retrieved.
Is this a solution, or am I missing something?

[sbwalker]

Yes, that may be a possible solution. Another solution may be to disable EF Core's default behavior of creating indexes for all foreign keys - and require Oqtane to explicitly create named indexes where required

ie. in the DBContext

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            ...
            modelBuilder.Conventions.Remove<ForeignKeyIndexConvention>();
            base.OnModelCreating(modelBuilder);
        }

I am not sure if this may cause challenges for existing installations that were created with the previous behavior.

EDIT: this feature is not available in EF Core - it was part of EF6

[sbwalker]

As expected... this turns out to be a lot more complicated than it should be...

SQL Server requires you to drop an index if you want to modify a column that is part of an index. So this is why the migration logic includes code such as:

            // Drop the index is needed because the Name is already associated with IX_File
            fileEntityBuilder.DropIndex("IX_File");
            fileEntityBuilder.AlterStringColumn("Name", 256);
            fileEntityBuilder.AddIndex("IX_File", new[] { "FolderId", "Name" }, true);

However, SQLite does not even support changing the size of a column - so the AlterStringColumn() method above actually does nothing within the SQLite provider. SQLite does support dropping and adding indexes without any issues - so this logic works for SQLite - although it actually accomplishes nothing.

MySQL supports the changing of a column size - however it has extra restrictions in terms of dropping indexes that are part of foreign keys - which is why you are experiencing an error. But it turns out that MySQL actually allows you to change a column size without dropping the index - so the drop index logic is not required.

But you can see how this gets complicated... as even though you can try to abstract operations into a generic interface, the different behavior supported by each database makes this challenging.

Rather than including the logic to drop/add indexes in the general migration logic (which is not applicable to all databases), it would probably be better to modify the AlterStringColumn() abstract method to also include a parameter where any indexes affected by the operation could be specified. This would reduce the logic to:

           fileEntityBuilder.AlterStringColumn("Name", 256, false, true, "IX_File:FolderId,Name:true");

And in the SQL Server provider it would perform the DropIndex, AlterStringColumn, AddIndex using the new parameter info. Whereas in the SQLite provider it would do nothing. And the MySQL provider would simply do an AlterStringColumn. And I would still need to research what would be needed in the PostgreSQL provider. But this approach would provide a better abstraction for this specific operation.

[sbwalker]

I ran into a number of other challenges as I worked through this issue. The solution of moving the drop/add index logic into the provider resolved the "Cannot drop index 'IX_Folder': needed in a foreign key constraint" issue. However, the next issue was that the UrlMapping table was trying to specify a column size of 2048 for the Url field (which is the max length for a Url according to the spec) however MySQL only supports indexes of 3072 bytes - and since the default character set is UTF8(unicode) it meant that that the 2048 character Url column plus the SiteID would be 2048 X 4 + 4 = 8196 bytes - which far exceeds the MySQL index limitation. So I had to reduce the size of the Url column to 750 characters and add logic to truncate the value if it exceeds the limit. Then I ran into an issue where the AspNetUserLogins table has a LoginProvider and ProviderKey defined as 450 characters. Again, the MySQL limitation for indexes was an issue as 450 X 2 X 4 = 3600 bytes. So I had to reduce the size of the LoginProvider column.