Piranha.core: Setting up a Blog template on Piranha CMS using MySQL Db/Identity

[aneff-official]

Hello @tidyui

I think that I might've found (and possibly resolved) a new? issue.

I have tried setting up a new project. I've tried following the instructions from this page:
Blog Template

Changing the Startup.cs file to be using <IdentityMySQLDb> led to this problem (before it was run initially).

When I ran the command dotnet run it exited with an error, trying to execute the following command:

CREATE UNIQUE INDEX IX_Piranha_Aliases_SiteId_AliasUrl ON Piranha_Aliases (SiteId, AliasUrl);

I did some googling and found out that the error that I was getting
#1071 - Specified key was too long; max key length is 767 bytes

And I thought that the solution was this:
You have to change the VARCHAR to 255 (from 256) to make it work. Yeah, it worked for creating the index, but then it had more problems. So, what I found next was changing the collation.

Using the collation "latin1_bin" solves the problem.

I think it is because it uses 1 byte per character, while utf8_bin (which I tried first) uses 3 bytes per character.

I did not find where the SQL execution commands are stored, but maybe changing them globally to use VARCHAR(255) fields or just another collation will solve it for future problems.

I can still add some information if the provided is not sufficient

P.S. - Running it on localhost with:
10.1.37-MariaDB UTF-8 Unicode Apache/2.4.37 PHP: 7.2.12 on XAMPP
P.S.2 - Can you please my other issue with the posts access, please? I have reopened it.

Best Regards,
Chris

[aneff-official]

@tidyui

Tried it with another instance of database - Percona (MySQL)

Still getting the same error:

This might actually be a problem for the people who decided to use the CMS. Can you let me know, where are the queries located or is it possible to override them?

Thanks,
Chris

[aneff-official]

Title to be changed

From:

SQLite -> MySQL Database (DB) Migration Issue

To:

Setting up a Blog template on Piranha CMS using MySQL Db/Identity

[tidyui]

As we limit our support in terms of testing for SQLite, SQL Server and SQL Azure I have not tested it on MySql myself:

http://piranhacms.org/docs/architecture/databases

Regarding collation and MySQL settings, maybe @aatmmr can help out here, he has created the AspNetCore.Identity package for MySQL and I know he is using Piranha on MySQL.

[tidyui]

And I would gladly change VARCHAR column to a maximum of 255 but since SQLite doesn't support ALTER COLUMN changes like this will break backwards compatibility for this database which is an issue. I'll have to figure out a way to get it done without breaking stuff.

[aneff-official]

Hi

I see, I know it is a lot more complicated than it sounds.

I will be on holiday for a month, maybe after that we can find some other solution than the one I am using at the moment (latin1_bin).

Many thanks,
Chris

[tidyui]

I will make a test and see what happens if I change all of the already existing migrations and db setup to use a maximum of 255 chars instead of 256. Hopefully EF Core will not consider the migrations changed meaning that all new databases will be created with the new max length, but existing databases will be left alone.

[aneff-official]

Hi!

Sorry for not replying long time, but I was away. Any news?

Thanks,
Chris

[aneff-official]

Should I close the issue then?

[tidyui]

This hasn’t been tested or released yet so let’s keep it open. We’ll try to get it done for the next release

[tidyui]

I have done some digging regarding this issue. I can't really change the lengths of the columns as the DbContext for Identity (which is provided by MS) also uses 256 NVARCHAR columns. However reading around the limit you are referring to (767 bytes) is a limit that is present in version 5.6 of MySQL which was released in 2013. This limit was replaced with a much higher limit of 3072 bytes in version 5.7 of MySQL which was released in 2015. In other words, using a newer database should fix the problems.

References:

• https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes
• https://en.wikipedia.org/wiki/MySQL#Release_history

Regards