[Bug] MySQL Outbox - PagedDispatchedCommand fails

[romtur]

Describe the bug

The PagedDispatchedCommand query fails in MySQL, causing the Archiver to stop working due to unknown column 'NUMBER' error.
Noticed in 10.0.0-preview.3 but persists in the latest master version

To Reproduce

Just setting up the archiver. Managed to make the archiver work in 10.0.0-preview.3 by using a workaround to register IAmAnOutbox, as described here: #3075

Exceptions (if any)

MySqlConnector.MySqlException (0x80004005): Unknown column 'NUMBER' in 'where clause'

Further technical details

• Brighter version: 10.0.0-preview.3
• The OS: Windows

[romtur]

@iancooper @preardon Is there any chance that this could be included in the next pre-release version?

[iancooper]

@romtur Yes, hopefully we will be at a point to do a release in the next few days. I want to get one more PR in.

[iancooper]

@romtur Possibly could be schema issues between V9 and V10; we will investigate but it may be because you need to migrate

[romtur]

@iancooper It looks like this query never worked for MySql
Same query for MsSql:
public string PagedDispatchedCommand { get; } = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Timestamp DESC) AS NUMBER, * FROM {0}) AS TBL WHERE DISPATCHED IS NOT NULL AND DISPATCHED < DATEADD(millisecond, @OutStandingSince, getutcdate()) AND NUMBER BETWEEN ((@PageNumber-1)*@PageSize+1) AND (@PageNumber*@PageSize) ORDER BY Timestamp DESC";

we are selecting NUMBER and then using it. For MySql we are not selecting it but trying to use:
public string PagedDispatchedCommand { get; } = "SELECT * FROM {0} AS TBL WHERE CreatedID BETWEEN ((?PageNumber-1)*?PageSize+1) AND (?PageNumber*?PageSize) AND DISPATCHED IS NOT NULL AND DISPATCHED < DATE_ADD(UTC_TIMESTAMP(), INTERVAL ?OutstandingSince MICROSECOND) AND NUMBER BETWEEN ((?PageNumber-1)*?PageSize+1) AND (?PageNumber*?PageSize) ORDER BY Timestamp DESC";

Maybe we can use the same approach as for OutstandingCommand: LIMIT and OFFSET

[iancooper]

Ah yes! Thanks that will be what is wrong. //cc @preardon

[romtur]

@iancooper @preardon Another thing to mention (not directly related to this issue), but I noticed that in the latest master code, archiveBatchSize is no longer configurable and is always set to 100