Closed hieucd04 closed 11 months ago
All I know is that I upgraded from 1.5.0 to 1.7.0
Have you tried to upgrade to 1.6 then 1.7?
The default MySql collation has changed, we already have to reduce the size of some indexes, from memory for Alias
and Workflows
but not yet for OrchardCore.ContentFields.Indexing.SQL
.
https://github.com/OrchardCMS/OrchardCore/pull/13585#issuecomment-1553457322
https://github.com/OrchardCMS/OrchardCore/pull/13585#issuecomment-1553700024
See these comments
Just checking the TextFieldIndex
, yes it is already too long, we would need to decrease for example the MaxContentTypeSize
, MaxContentPartSize
and MaxContentFieldSize
.
Anyway would only work for fresh installations, so for now the column length of the related index tables need to be changed manually.
@sebastienros
Have you tried to upgrade to 1.6 then 1.7?
I haven't. However, I did tried something else:
Content Fields Indexing (SQL)
feature -> Boom! Exception thrown2023-09-29 16:56:41 fail: OrchardCore.Data.Migration.DataMigrationManager[0]
2023-09-29 16:56:41 Error while running migration version 0 for 'OrchardCore.ContentFields.Indexing.SQL'.
2023-09-29 16:56:41 System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
2023-09-29 16:56:41 ---> MySqlConnector.MySqlException (0x80004005): Specified key was too long; max key length is 3072 bytes
2023-09-29 16:56:41 at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 175
2023-09-29 16:56:41 at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 130
2023-09-29 16:56:41 at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 493
2023-09-29 16:56:41 at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 77
2023-09-29 16:56:41 at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 296
2023-09-29 16:56:41 at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 107
2023-09-29 16:56:41 at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) in /_/Dapper/SqlMapper.cs:line 2848
2023-09-29 16:56:41 at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 581
2023-09-29 16:56:41 at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 452
2023-09-29 16:56:41 at YesSql.Sql.SchemaBuilder.Execute(IEnumerable`1 statements)
2023-09-29 16:56:41 at YesSql.Sql.SchemaBuilder.AlterTable(String name, Action`1 table)
2023-09-29 16:56:41 at YesSql.Sql.SchemaBuilder.AlterIndexTable(Type indexType, Action`1 table, String collection)
2023-09-29 16:56:41 at YesSql.Sql.SchemaBuilderExtensions.AlterIndexTable[T](ISchemaBuilder builder, Action`1 table, String collection)
2023-09-29 16:56:41 at OrchardCore.ContentFields.Indexing.SQL.Migrations.Create()
2023-09-29 16:56:41 at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
2023-09-29 16:56:41 at System.Reflection.MethodInvoker.Invoke(Object obj, IntPtr* args, BindingFlags invokeAttr)
2023-09-29 16:56:41 --- End of inner exception stack trace ---
2023-09-29 16:56:41 at System.Reflection.MethodInvoker.Invoke(Object obj, IntPtr* args, BindingFlags invokeAttr)
2023-09-29 16:56:41 at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
2023-09-29 16:56:41 at OrchardCore.Data.Migration.DataMigrationManager.UpdateAsync(String featureId)
@jtkech
Anyway would only work for fresh installations
Shouldn't be a problem. I can export all my data to a zip file via Deployment Plan and then Re-import them after a fresh installation.
we would need to decrease for example the MaxContentTypeSize, MaxContentPartSize and MaxContentFieldSize
Just to confirm, is this something you guys will fix and release in upcoming versions of Orchard Core?
We spent some time trying to understand the issue during triage and we think we understand the issues completely.
An index entry in MySQL can't contain more than 3072 bytes. With the current default utf8nb4 collation this accounts for 768 chars. This means that all the fields which make up an index entry can't go over that, but only in MySQL.
MySQL has a custom syntax to define the prefix size of an index, meaning the amount of data to use for each field. For instance we can limit the Alias
field size in the index with CREATE INDEX ... (DocumentId, Alias(50), ...)
and then it would only take the first 50 chars even if the content is bigger. This way we can have limits on the content itself, MaxAliasSize
, but a distinct max size for the index. And we wouldn't need to constrain the field size to the index size for MySQL.
In YesSql the columns names are injected as-is in the SQL Query: https://github.com/sebastienros/yessql/blob/c0771eb555c67bf419859016cb3563a3540fe7c7/src/YesSql.Core/Sql/BaseComandInterpreter.cs#L233
So if we change the current migration to something like this, this would work:
SchemaBuilder.AlterIndexTable<AliasPartIndex>(table => table
.CreateIndex("IDX_AliasPartIndex_DocumentId",
"DocumentId",
"Alias(123)",
"ContentItemId",
"Published",
"Latest")
);
... as long as the sum of these constraints is less than 767 chars. (this doesn't work on binary formats, e.g. bools, numbers, ...)
But this only works for MySql, so if we don't want to change YesSql we need to do an if
on the db provider in the Schema generation (is that possible?).
The other option is to use this syntax in all cases (any db provider) and have YesSql custom command interpreters exclude this part when it's not supported.
@sebastienros @jtkech:
I'm just asking ... is this fixed in 1.7.1
release?
Because I was testing 1.7.1
and was still able to reproduce this issue with the steps I mentioned above:
- I factory reset my Docker -> So now, I should have a completely fresh start with no data left
- Build & deploy my project to Docker (which uses Orchard Core 1.7.1) -> Everything works fine, I can access the Dashboard without any exception thrown
- Create a test content type with a text field in it -> Still OK, no exception
- Enable Content Fields Indexing (SQL) feature -> Boom! Exception thrown
(New logs pasted from Docker)
2023-10-15 19:49:02 fail: OrchardCore.Data.Migration.DataMigrationManager[0]
2023-10-15 19:49:02 Error while running migration version 0 for 'OrchardCore.ContentFields.Indexing.SQL'.
2023-10-15 19:49:02 System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
2023-10-15 19:49:02 ---> MySqlConnector.MySqlException (0x80004005): Specified key was too long; max key length is 3072 bytes
2023-10-15 19:49:02 at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 175
2023-10-15 19:49:02 at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 133
2023-10-15 19:49:02 at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 493
2023-10-15 19:49:02 at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 77
2023-10-15 19:49:02 at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 296
2023-10-15 19:49:02 at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 107
2023-10-15 19:49:02 at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) in /_/Dapper/SqlMapper.cs:line 2848
2023-10-15 19:49:02 at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 581
2023-10-15 19:49:02 at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 452
2023-10-15 19:49:02 at YesSql.Sql.SchemaBuilder.Execute(IEnumerable`1 statements)
2023-10-15 19:49:02 at YesSql.Sql.SchemaBuilder.AlterTable(String name, Action`1 table)
2023-10-15 19:49:02 at YesSql.Sql.SchemaBuilder.AlterIndexTable(Type indexType, Action`1 table, String collection)
2023-10-15 19:49:02 at YesSql.Sql.SchemaBuilderExtensions.AlterIndexTable[T](ISchemaBuilder builder, Action`1 table, String collection)
2023-10-15 19:49:02 at OrchardCore.ContentFields.Indexing.SQL.Migrations.Create()
2023-10-15 19:49:02 at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
2023-10-15 19:49:02 at System.Reflection.MethodInvoker.Invoke(Object obj, IntPtr* args, BindingFlags invokeAttr)
2023-10-15 19:49:02 --- End of inner exception stack trace ---
2023-10-15 19:49:02 at System.Reflection.MethodInvoker.Invoke(Object obj, IntPtr* args, BindingFlags invokeAttr)
2023-10-15 19:49:02 at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
2023-10-15 19:49:02 at OrchardCore.Data.Migration.DataMigrationManager.UpdateAsync(String featureId)
After a quick look there is at least one missing index that has not been reduced
This one has been reduced
// The index in MySQL can accommodate up to 768 characters or 3072 bytes.
// DocumentId (2) + ContentType (254) + ContentPart (254) + ContentField (254)
// + Published (1) + Latest (1) = 766 (less than 768)
SchemaBuilder.AlterIndexTable<TextFieldIndex>(table => table
.CreateIndex("IDX_TextFieldIndex_DocumentId_ContentType",
"DocumentId",
"ContentType(254)",
"ContentPart(254)",
"ContentField(254)",
"Published",
"Latest")
);
But not this one, the size of Text
being of MaxTextSize
equal to 766 chars size.
So DocumentId (2) + Text (766) + even if both booleans only take 1 char size = 769 chars.
No luck, the max is 768 chars size => 768 * 4 = 3072 bytes ;)
SchemaBuilder.AlterIndexTable<TextFieldIndex>(table => table
.CreateIndex("IDX_TextFieldIndex_DocumentId_Text",
"DocumentId",
"Text", // <= 766 chars size
"Published",
"Latest")
);
Edited: Idem for this one
SchemaBuilder.AlterIndexTable<LinkFieldIndex>(table => table
.CreateIndex("IDX_LinkFieldIndex_DocumentId_Url",
"DocumentId",
"Url", // <= 766 chars size
"Published",
"Latest")
);
And this one
SchemaBuilder.AlterIndexTable<MultiTextFieldIndex>(table => table
.CreateIndex("IDX_MultiTextFieldIndex_DocumentId_Value",
"DocumentId",
"Value", // <= 766 chars size
"Published",
"Latest")
);
Is that mean we need a hot fix?
Yes I think, maybe 1.7.2 ;)
The feature needs to be enabled in functional tests as part of the fix.
IMHO no need to rush during the release to avoid such issues
@hieucd04 1.7.2 was released today. Please give that a try and let us know if you run into additional issues.
Thank you
@MikeAlhayek
Yes! I'm testing 1.7.2
with my data and I don't see any exception relating to the MySQL index issue in the log stream anymore.
At this point, I would say 1.7.2
did fixed the MySQL index issue on my side. Thank you all for your efforts. Appreciate it!
FYI, while testing 1.7.2
, I got some exceptions thrown in the log stream which I've never seen before while using Orchard Core.
I've opened a new issue to track it: https://github.com/OrchardCMS/OrchardCore/issues/14539
Yes I saw the issue, are you using the OpenId feature?
@jtkech Yes, I do!
I enabled the below OpenId features:
"OrchardCore.OpenId.Server",
"OrchardCore.OpenId.Validation",
Can't repro by just enabling these 2 features but there is nothing to prune, but I can see the query well executed in the background task (I set the period to 1 minute for testing).
I will copy paste this comment in the related issue
Multiple exceptions were thrown when I started a new MySQL based instance of Orchard Core CMS. Full log is at the end of this issue. Also, this looks like a regression bug to me as a similar issue happened in the past: https://github.com/OrchardCMS/OrchardCore/issues/4753
To Reproduce
Sorry, I don't really have a step by step instruction. All I know is that I upgraded from
1.5.0
to1.7.0
and this happens. Revert back to1.5.0
fixes the issue for me. Also, to make sure I'm not chasing phantom issue, I ran the following cmd between downgrades and upgrades:Environment
Expected behavior
Application starts successfully without any exception.
Full log