Updates for MariaDB sequences

MariaDB should not run a Sequence if it has optional=True.
Additionally, rework the rules in crud.py to accommodate the
new combination MariaDB brings us, which is a dialect
that supports both cursor.lastrowid, explicit sequences,
*and* no support for returning.

Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com>
Fixes: #5528
Change-Id: I9a8ea69a34983affa95dfd22186e2908fdf0d58c

2 files changed, 10 insertions, 8 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 1d032b600..46529636d 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1782,15 +1782,10 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
         if not column.nullable:
             colspec.append("NOT NULL")
 
-        # see: http://docs.sqlalchemy.org/en/latest/dialects/
-        #   mysql.html#mysql_timestamp_null
+        # see: http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#mysql_timestamp_null  # noqa
         elif column.nullable and is_timestamp:
             colspec.append("NULL")
 
-        default = self.get_column_default_string(column)
-        if default is not None:
-            colspec.append("DEFAULT " + default)
-
         comment = column.comment
         if comment is not None:
             literal = self.sql_compiler.render_literal_value(
@@ -1802,9 +1797,17 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
             column.table is not None
             and column is column.table._autoincrement_column
             and column.server_default is None
+            and not (
+                self.dialect.supports_sequences
+                and isinstance(column.default, sa_schema.Sequence)
+                and not column.default.optional
+            )
         ):
             colspec.append("AUTO_INCREMENT")
-
+        else:
+            default = self.get_column_default_string(column)
+            if default is not None:
+                colspec.append("DEFAULT " + default)
         return " ".join(colspec)
 
     def post_create_table(self, table):
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 53c54ab65..07405e6d1 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2526,7 +2526,6 @@ class PGExecutionContext(default.DefaultExecutionContext):
             elif column.default is None or (
                 column.default.is_sequence and column.default.optional
             ):
-
                 # execute the sequence associated with a SERIAL primary
                 # key column. for non-primary-key SERIAL, the ID just
                 # generates server side.