MySql Outbox - Some query syntax is not supported in MySql version 5.7

[honkuan86]

Describe the bug

The query syntax to query the number of outstanding messages that using ROW_NUMBER() is not supported in MySql version below 8.0

Exceptions (if any)

MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY Timestamp ASC) As ROWNUMBER FROM message_outbox WHERE DISPATCHED IS N' at line 1
at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in //src/MySqlConnector/Core/ResultSet.cs:line 44
at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in //src/MySqlConnector/MySqlDataReader.cs:line 129
at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 451 at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/Core/CommandExecutor.cs:line 56
at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/MySqlCommand.cs:line 312
at MySqlConnector.MySqlCommand.ExecuteReader() in /_/src/MySqlConnector/MySqlCommand.cs:line 98
at Paramore.Brighter.Outbox.MySql.MySqlOutboxSync.OutstandingMessages(Double millSecondsSinceSent, Int32 pageSize, Int32 pageNumber, Dictionary`2 args) in C:\repos\Ideagen-Brighter\Brighter\src\Paramore.Brighter.Outbox.MySql\MySqlOutboxSync.cs:line 353
at Paramore.Brighter.ExternalBusServices.OutstandingMessagesCheck() in C:\repos\Ideagen-Brighter\Brighter\src\Paramore.Brighter\ExternalBusServices.cs:line 271

Solution

To use supported query for both < 8.0 and > 8.0 versions

[holytshirt]

@honkuan86 you should have a chat with @samrumley88 as he submitted the patch for that.

I assume it needs to support versions 5.7 and 8.*

[samrumley88]

@holytshirt the patch I submitted was for the PostgreSql Outbox #1820 (comment).

@honkuan86 happy to have a discussion about this, given that we need both PostgreSql and MySql support 🙂

[holytshirt]

@samrumley88 doh, sorry