BUGFIX: Guaranty order of sequencenumber

[saschanowak]

When not setting the order by the sorting of the query is not always the primary key depending on the order in the index. For me as simple category stream query retuned the following order and thats why the projector not getting all events in the correct order:	sequencenumber
83
63
1018
209
161
1
71
623
59
67
922
178
225

[bwaidelich]

@nlx-sascha wow, that would be a hell of a bug – and the fix is so simple. But I'm really curious about the root cause since I can't seem to create a query that would lead to the sequencenumber not being the primary key.. Can you share the exact kind of query and database you use?

[saschanowak]

@bwaidelich the Query is the following: SELECT * FROM eventstore WHERE stream LIKE 'Verein-%'

Database:

mysql --version
mysql  Ver 15.1 Distrib 10.2.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

MariaDB on Ubuntu 18.04.4 LTS

[bwaidelich]

That's crazy, with that query I get the right order on

mysql Ver 15.1 Distrib 10.5.9-MariaDB, for osx10.15 (x86_64) using readline 5.1

o.O

[saschanowak]

The explain said it would use the stream_version_uniq index. explain SELECT * FROM eventstore WHERE stream LIKE 'Verein-%'	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	eventstore	range	stream_version_uniq	stream_version_uniq	1022	NULL	13	Using index condition

explain SELECT * FROM eventstore WHERE stream LIKE 'Verein-%' order by sequencenumber ASC;	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	eventstore	range	stream_version_uniq	stream_version_uniq	1022	NULL	13	Using index condition; Using filesort

[bwaidelich]

Weird.. for EXPLAIN SELECT * FROM neos_contentrepository_events WHERE stream LIKE "Neos.ContentRepository:ContentStream:%" I get:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	neos_contentrepository_events	range	PRIMARY,stream_version_uniq	PRIMARY	4	NULL	43	Using where

(the same with and without the ORDER BY part).

Is your table schema correct?

CREATE TABLE `neos_contentrepository_events` (
  `sequencenumber` int(11) NOT NULL AUTO_INCREMENT,
  `stream` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `version` bigint(20) unsigned NOT NULL,
  `payload` longtext COLLATE utf8_unicode_ci NOT NULL,
  `metadata` longtext COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `causationidentifier` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `correlationidentifier` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `recordedat` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',
  PRIMARY KEY (`sequencenumber`),
  UNIQUE KEY `id_uniq` (`id`),
  UNIQUE KEY `stream_version_uniq` (`stream`,`version`),
  KEY `IDX_67A26AC9EE6C504` (`correlationidentifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

[saschanowak]

Create table statement copied from DataGrip:

create table eventstore
(
    sequencenumber        int auto_increment
        primary key,
    stream                varchar(255)    not null,
    version               bigint unsigned not null,
    type                  varchar(255)    not null,
    payload               longtext        not null,
    metadata              longtext        not null,
    id                    varchar(255)    not null,
    correlationidentifier varchar(255)    null,
    causationidentifier   varchar(255)    null,
    recordedat            datetime        not null comment '(DC2Type:datetime_immutable)',
    constraint id_uniq
        unique (id),
    constraint stream_version_uniq
        unique (stream, version)
);

create index IDX_5AB7C7FD9EE6C504
    on eventstore (correlationidentifier);

I would say it's the same. Same primary key, same index. How do your streams look like? Ours have the format PREFIX-UUIDV4.

[bwaidelich]

How do your streams look like?

In the ES CR package they follow the default pattern of <package-key>:<module>:<id>, for example Neos.ContentRepository:ContentStream:5df91015-6bdf-4090-b5c0-cdcbfc31738c.

But I just tried

CREATE TABLE eventstore
(
    sequencenumber        INT AUTO_INCREMENT
        PRIMARY KEY,
    stream                VARCHAR(255)    NOT NULL,
    version               BIGINT UNSIGNED NOT NULL,
    TYPE                  VARCHAR(255)    NOT NULL,
    payload               LONGTEXT        NOT NULL,
    metadata              LONGTEXT        NOT NULL,
    id                    VARCHAR(255)    NOT NULL,
    correlationidentifier VARCHAR(255)    NULL,
    causationidentifier   VARCHAR(255)    NULL,
    recordedat            DATETIME        NOT NULL COMMENT '(DC2Type:datetime_immutable)',
    CONSTRAINT id_uniq
        UNIQUE (id),
    CONSTRAINT stream_version_uniq
        UNIQUE (stream, version)
);

CREATE INDEX IDX_5AB7C7FD9EE6C504
    ON eventstore (correlationidentifier);

INSERT INTO `eventstore` (`sequencenumber`, `stream`, `version`, `type`, `payload`, `metadata`, `id`, `correlationidentifier`, `causationidentifier`, `recordedat`)
VALUES
    (2, 'Verein-5df91015-6bdf-4090-b5c0-cdcbfc31738c', 1, 'someType', '{}', '{}', UUID(), NULL, NULL, '2021-04-29 10:48:48'),
    (3, 'Verein-5df91015-6bdf-4090-b5c0-cdcbfc31738c', 2, 'someType', '{}', '{}', UUID(), NULL, NULL, '2021-04-29 10:48:48'),
    (1, 'Verein-5df91015-6bdf-4090-b5c0-cdcbfc31738c', 0, 'someType', '{}', '{}', UUID(), NULL, NULL, '2021-04-29 10:48:48');

SELECT * FROM eventstore WHERE stream LIKE 'Verein-%';

and that seems to work just fine for me..

Anyways, the explicit "order by" makes sense of course