Sequences with increment=1 are incompatible with multi-master MariaDB Galera clusters

[pedrorosadodias]

Description:

Flask-AppBuilder model uses sqlalchemy that create sequences with a default increment=1. This works correctly if we use a single-node MariaDB Database, but it fails on multi-master MariaDB clusters using Galera.

Flask-AppBuilder models define sequences like:

Sequence("ab_permission_id_seq", start=1, increment=1, minvalue=1, cycle=False)

According to SQLAlchemy documentation, the increment parameter [1][2] is the "value used when the CREATE SEQUENCE command is emitted to the database as the value of the “INCREMENT BY” clause. If None, the clause is omitted, which on most platforms indicates an increment of 1."

Galera relies on an auto-increment–based mechanism to guarantee unique, non-conflicting identifiers across nodes. In a multi-master setup, MariaDB uses the auto_increment_increment, typically set to the number of nodes in the cluster, and auto_increment_offset system variables to ensure that each node generates distinct values for AUTO_INCREMENT columns.

In MariaDB, for sequences, this mechanism is only respected when the sequence is created with increment=0. In that case, the sequence automatically uses the value of the auto_increment_increment system variable at creation time, which is always a positive number and is consistent across the cluster.

Proposed Change

Remove the explicit increment=1 argument from sequence definitions in Flask-AppBuilder (target file: Flask-AppBuilder/blob/master/flask_appbuilder/security/sqla/models.py), allowing each database backend to determine the appropriate increment behavior.

This would allow MariaDB Galera to apply its cluster-safe auto-increment logic while preserving correct behavior on other platforms.

Example of patch

In my case I was installing the 6.0.0 version of Apache Superset, that depended on Flask-AppBuilder and sqlalchemy. My database backend was a multi-master MariaDB clusters using Galera and I had to perform the following patch in the file Flask-AppBuilder/blob/master/flask_appbuilder/security/sqla/models.py that just worked for me.

Below is the patch applied locally, replacing increment=1 with increment=0, which allows MariaDB Galera to derive the increment from auto_increment_increment:

     id: Mapped[int] = mapped_column(
         Integer,
-        Sequence("ab_permission_id_seq", start=1, increment=1, minvalue=1, cycle=False),
+        Sequence("ab_permission_id_seq", start=1, increment=0, minvalue=1, cycle=False),
         primary_key=True,
     )
     name: Mapped[str] = mapped_column(String(100), unique=True, nullable=False)
@@ -51,7 +51,7 @@
 
     id: Mapped[int] = mapped_column(
         Integer,
-        Sequence("ab_view_menu_id_seq", start=1, increment=1, minvalue=1, cycle=False),
+        Sequence("ab_view_menu_id_seq", start=1, increment=0, minvalue=1, cycle=False),
         primary_key=True,
     )
     name: Mapped[str] = mapped_column(String(250), unique=True, nullable=False)
@@ -75,9 +75,9 @@
         Sequence(
             "ab_permission_view_role_id_seq",
             start=1,
-            increment=1,
+            increment=0,
             minvalue=1,
             cycle=False,
         ),
         primary_key=True,
     ),
@@ -99,7 +99,7 @@
     Column(
         "id",
         Integer,
-        Sequence("ab_user_role_id_seq", start=1, increment=1, minvalue=1, cycle=False),
+        Sequence("ab_user_role_id_seq", start=1, increment=0, minvalue=1, cycle=False),
         primary_key=True,
     ),
     Column("user_id", Integer, ForeignKey("ab_user.id", ondelete="CASCADE")),
@@ -113,7 +113,7 @@
 
     id: Mapped[int] = mapped_column(
         Integer,
-        Sequence("ab_role_id_seq", start=1, increment=1, minvalue=1, cycle=False),
+        Sequence("ab_role_id_seq", start=1, increment=0, minvalue=1, cycle=False),
         primary_key=True,
     )
     name: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
@@ -141,7 +141,7 @@
     id: Mapped[int] = mapped_column(
         Integer,
         Sequence(
-            "ab_permission_view_id_seq", start=1, increment=1, minvalue=1, cycle=False
+            "ab_permission_view_id_seq", start=1, increment=0, minvalue=1, cycle=False
         ),
         primary_key=True,
     )
@@ -158,7 +158,7 @@
     __tablename__ = "ab_user"
     id: Mapped[int] = mapped_column(
         Integer,
-        Sequence("ab_user_id_seq", start=1, increment=1, minvalue=1, cycle=False),
+        Sequence("ab_user_id_seq", start=1, increment=0, minvalue=1, cycle=False),
         primary_key=True,
     )
     first_name: Mapped[str] = mapped_column(String(64), nullable=False)
@@ -241,7 +241,7 @@
     Column(
         "id",
         Integer,
-        Sequence("ab_user_group_id_seq", start=1, increment=1, minvalue=1, cycle=False),
+        Sequence("ab_user_group_id_seq", start=1, increment=0, minvalue=1, cycle=False),
         primary_key=True,
     ),
     Column("user_id", Integer, ForeignKey("ab_user.id", ondelete="CASCADE")),
@@ -258,7 +258,7 @@
     Column(
         "id",
         Integer,
-        Sequence("ab_group_role_id_seq", start=1, increment=1, minvalue=1, cycle=False),
+        Sequence("ab_group_role_id_seq", start=1, increment=0, minvalue=1, cycle=False),
         primary_key=True,
     ),
     Column("group_id", Integer, ForeignKey("ab_group.id", ondelete="CASCADE")),
@@ -273,7 +273,7 @@
     __tablename__ = "ab_group"
     id: Mapped[int] = mapped_column(
         Integer,
-        Sequence("ab_group_id_seq", start=1, increment=1, minvalue=1, cycle=False),
+        Sequence("ab_group_id_seq", start=1, increment=0, minvalue=1, cycle=False),
         primary_key=True,
     )
     name: Mapped[str] = Column(String(100), unique=True, nullable=False)
@@ -295,7 +295,7 @@
     id = mapped_column(
         Integer,
         Sequence(
-            "ab_register_user_id_seq", start=1, increment=1, minvalue=1, cycle=False
+            "ab_register_user_id_seq", start=1, increment=0, minvalue=1, cycle=False
         ),
         primary_key=True,
     )

References:
[1] https://docs.sqlalchemy.org/en/14/core/defaults.html#sqlalchemy.schema.Sequence
[2] https://docs.sqlalchemy.org/en/20/core/defaults.html#sqlalchemy.schema.Sequence