Howdy, Monty. First, to congrat you and the Ali colleagues on this nice piece of functionality! Doubtlessly the idea is generic and we shall see creative followups at some time. Now having MDEV-15782 we can identify some immediate issues, as well as ideas how to cope with them. It was a surprise to see a DML operating on a sequence, like in the MDEV: REPLACE INTO seq VALUES (1,1,9223372036854775806,1,1,1000,0,0); And the query was binlogged in ROW format only to meet an assert at row event slave applying (details omitted here). Soon I understood that REPLACE or INSERT are mere shortcuts for "official" ALTER. The actual motivation must be given here: [https://mariadb.com/kb/en/library/sequence-overview/] If one tries to insert into a sequence table, the single row will be updated. This allows mysqldump to work ... However these two syntactically DML operations actually have the ALTER semantics, which is conventionally the DDL semantics. And then we arrive at the following questions. If REPLACE (INSERT) is essentially syntax sugar of ALTER should it also be regarded as DDL and therefore to cause implicit commit (ALTER-sequence does) and be binlogged solely in the STATEMENT format? If the answer to the DDL semantics is positive, unexpected row-events of the replaced (actually altered) sequence MDEV-15782 could be fixed e.g with converting REPLACE into ALTER at binlogging time. To the implicit commit concern, I would also prefer uniform implicit commit by any ALTER (even one that is written as REPLACE), so a better idea seems to re-write the REPLACE into ALTER already at parsing time. Cheers, Andrei

Hi!

For those that are trying to port applications to MariaDB from other databases or need more features than the current AUTO_INCREMENT provides, the following may be of interest:

First version of sequence is pushed to bb-10.2-sequence. It should be merged to bb-10.2-compatilbity and then to 10.3 shortly (1-2 weeks).

What is working for the moment:

CREATE OR REPLACE [TEMPORARY] SEQUENCE [IF NOT EXISTS] name [ INCREMENT [ BY | = ] increment ] [ MINVALUE [=] minvalue | NO MINVALUE ] [ MAXVALUE [=] maxvalue | NO MAXVALUE ] [ START [ WITH | = ] start ] [ CACHE [=] cache ] [ [ NO ] CYCLE ] ENGINE=xxx COMMENT=".." SELECT NEXT VALUE FOR sequence_name; SELECT NEXTVAL(sequence_name); SELECT PREVIOUS VALUE FOR sequence_name; SELECT LASTVAL(sequence_name);

SHOW CREATE SEQUENCE sequence_name; SHOW CREATE TABLE sequence_name; CREATE TABLE sequence-structure ... SEQUENCE=1 ALTER TABLE sequence RENAME TO sequence2; RENAME TABLE sequence RENAME TO sequence2; DROP [TEMPORARY] SEQUENCE sequence_name [IF EXISTS]

See https://jira.mariadb.org/browse/MDEV-10139 for progress. See commit messages in bb-10.2-sequence for what is still to be done.

Some documentation can be found at: https://mariadb.com/kb/en/mariadb/create-sequence/ This will be improved as the work progress.

One of the goals with the SEQUENCE implementation is that all old tools, like mysqldump, should work unchanged, while still keeping normal usage of sequence standard compatibly.

The make this possible, the sequence is currently implemented as a table with a few exclusive properties.

The main disadvantage of having sequence as a table is that it uses the same name space as tables. In other words, you can't have a table and a sequence of the same name. The benefit is that sequences shows up in 'show tables', one can create a sequence also with 'create table' and drop it with 'drop table'. One can select from it as from any other table. This ensures that all old tools that works with tables should work with sequences.

The special properties for sequence tables are: - A sequence table has always one row. - When one creates a sequence, either with CREATE TABLE or CREATE SEQUENCE, one row will be inserted. - Normal table options works for CREATE SEQUENCE. One can use ENGINE=xxx, COMMENT=xxx etc. - If one tries to insert into a sequence table, the single row will be updated. This allows mysqldump to work but also gives the additional benefit that one can change all properties of a sequence with a single insert. New applications can of course also use ALTER SEQUENCE. - Updates to the sequence table will change the single row. - Doing a select on the sequence shows the current state of the sequence, except the values that are reserved in the cache. The column 'next_value' shows the next value not reserved by the cache. - Truncate on a sequence table will give an error. - Alter table and rename works on sequences. - If one creates a sequence with INCREMENT 0, then the sequence will use auto_increment_increment and auto_increment_offset for the sequence, just like AUTO_INCREMENT. This allows sequences to work reliable in a master-master environment and with Galera.

Internally sequence tables are created as a normal table without rollback (InnoDB, Aria and MySAM supports this) with is wrapped by a sequence engine. This allowed me to create sequences with almost no performance impact for normal tables. (The cost is one 'if' per insert if binary log is enabled).

MariaDB 10.3 will support both the ANSI SQL and Oracle syntax for creating and accessing sequences. As ANSI SQL doesn't have an easy access to the last generated value, 103 also supports 'PREVIOUS VALUE FOR sequence_name', like IBM DB2, and LASTVAL(sequence_name) as PostgreSQL.

I want to thank Jianwe Zhao from Aliyun for his work on SEQUENCE in AliSQL which gave me ideas and inspiration for this work. I also want to thank Marko Mäkelä for his help in the InnoDB part of the code.

There is still a lot of work to fix edge cases, but in the current implementation most major things seams to work...

Comments, suggestions or questions?

Regards, Monty