Error when removing server_default on MySQL

[sqlalchemy-bot]

Migrated issue, originally created by Robert Buchholz (@rbuchholz)

The following statement produces an exception when run against MySQL:

    op.alter_column('table', 'column', server_default=None, existing_type=sa.Boolean(), existing_nullable=False)

A unit test to extend tests/test_mysql.py follows:

def test_alter_column_drop_default():
    context = op_fixture('mysql')
    op.alter_column("t", "c", existing_type=Integer, server_default=None)
    context.assert_(
        'ALTER TABLE t ALTER COLUMN c DROP DEFAULT $MYSQL_MAGIC_YOU_KNOW_BETTER_THAN_ME'
    )

[sqlalchemy-bot]

Changes by Robert Buchholz (@rbuchholz):

• edited description

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

the whole of mysql.py needs to be expanded out with individual ALTER, CHANGE, and DEFAULT directives. part of a patch:

diff -r cc6a13c4f4cf9e635aedfe3c377e5b6789f3e8f8 alembic/ddl/mysql.py
--- a/alembic/ddl/mysql.py	Mon Feb 04 13:44:48 2013 -0500
+++ b/alembic/ddl/mysql.py	Tue Feb 05 11:00:56 2013 -0500
@@ -22,23 +22,57 @@
                         existing_nullable=None,
                         existing_autoincrement=None
                     ):
-        self._exec(
-            MySQLAlterColumn(
-                table_name, column_name,
-                schema=schema,
-                newname=name if name is not None else column_name,
-                nullable=nullable if nullable is not None else
-                                existing_nullable
-                                if existing_nullable is not None
-                                else True,
-                type_=type_ if type_ is not None else existing_type,
-                default=server_default if server_default is not False
-                                            else existing_server_default,
-                autoincrement=autoincrement if autoincrement is not None
-                                            else existing_autoincrement
+        if server_default is not False:
+            self._exec(
+                MySQLAlterDefault(
+                    table_name, column_name,
+                    schema=schema,
+                )
+            )
+        if name is not None:
+            self._exec(
+                MySQLChangeColumn(
+                    table_name, column_name,
+                    schema=schema,
+                    newname=name,
+                    nullable=nullable if nullable is not None else
+                                    existing_nullable
+                                    if existing_nullable is not None
+                                    else True,
+                    type_=type_ if type_ is not None else existing_type,
+                    default=server_default if server_default is not False
+                                                else existing_server_default,
+                    autoincrement=autoincrement if autoincrement is not None
+                                                else existing_autoincrement
+                )
+            )
+        elif nullable is not None or \
+            type_ is not None or \
+            autoincrement is not None:
+            self._exec(
+                MySQLAlterColumn(
+                    table_name, column_name,
+                    schema=schema,
+                    newname=name if name is not None else column_name,
+                    nullable=nullable if nullable is not None else
+                                    existing_nullable
+                                    if existing_nullable is not None
+                                    else True,
+                    type_=type_ if type_ is not None else existing_type,
+                    default=server_default if server_default is not False
+                                                else existing_server_default,
+                    autoincrement=autoincrement if autoincrement is not None
+                                                else existing_autoincrement
             )
         )
 
+class MySQLAlterDefault(AlterColumn):
+    def __init__(self, name, column_name, default, schema=None):
+        super(AlterColumn, self).__init__(name, schema=schema)
+        self.column_name = column_name
+        self.default = default
+
+
 class MySQLAlterColumn(AlterColumn):
     def __init__(self, name, column_name, schema=None,
                         newname=None,
@@ -60,6 +94,10 @@
 
         self.type_ = sqltypes.to_instance(type_)
 
+class MySQLChangeColumn(MySQLAlterColumn):
+    pass
+
+
 @compiles(ColumnNullable, 'mysql')
 @compiles(ColumnName, 'mysql')
 @compiles(ColumnDefault, 'mysql')

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: op directives

[sqlalchemy-bot]

Jeff Dairiki (@dairiki) wrote:

Possible fix in pull request #21

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• Added/fixed support for MySQL "SET DEFAULT" / "DROP DEFAULT" phrases,
  which will now be rendered if only the server default is changing
  or being dropped (e.g. specify None to alter_column() to indicate
  "DROP DEFAULT"). Also added support for rendering MODIFY rather than
  CHANGE when the column name isn't changing.
  fixes Error when removing server_default on MySQL #103

→ b61eccc

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• add an additional fix to the MySQL fixes for re: Error when removing server_default on MySQL #103.

→ b297807