Open romtur opened 1 month ago
@iancooper @preardon Is there any chance that this could be included in the next pre-release version?
@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.
@romtur Possibly could be schema issues between V9 and V10; we will investigate but it may be because you need to migrate
@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
CreatedIDBETWEEN ((?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
Ah yes! Thanks that will be what is wrong. //cc @preardon
@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
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: https://github.com/BrighterCommand/Brighter/issues/3075
Exceptions (if any)
MySqlConnector.MySqlException (0x80004005): Unknown column 'NUMBER' in 'where clause'
Further technical details