compare_server_default throws TypeError under a very specific condition in MySQL

[GoldstHa]

If the column doesn't have a default value and a server_default is added to the models.py file then autogenerate will throw a "TypeError: expected string or bytes-like object". I tried reversing it so the database has a default and the models.py file doesn't and I got no errors which suggests that the way alembic is pulling in the default value isn't being handled well in this this situation.

Versions:
MySQL 5.5
Alembic 1.0.9
SQLAlchemy 1.3.3
mysqlclient 1.3.14

This caused the error:

CREATE TABLE restaurants (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) DEFAULT NULL,
  browser_flag int(1) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;

class Restaurant(Base):
	__tablename__ = 'restaurants'

	id = Column(INTEGER(11), primary_key=True)
	name = Column(String(255))
	browser_flag = Column(INTEGER(1), nullable=False, server_default='0')

This did not cause an error:

CREATE TABLE restaurants (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) DEFAULT NULL,
  browser_flag int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;

class Restaurant(Base):
	__tablename__ = 'restaurants'

	id = Column(INTEGER(11), primary_key=True)
	name = Column(String(255))
	browser_flag = Column(INTEGER(1), nullable=False)

Full traceback:

Traceback (most recent call last):
  File "/home/alembic/miniconda3/bin/alembic", line 11, in <module>
    sys.exit(main())
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/config.py", line 527, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/config.py", line 521, in main
    self.run_cmd(cfg, options)
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/config.py", line 501, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/command.py", line 197, in revision
    script_directory.run_env()
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/script/base.py", line 475, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 90, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/util/compat.py", line 156, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 728, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 151, in <module>
    run_migrations_online()
  File "alembic/env.py", line 131, in run_migrations_online
    context.run_migrations(engine_name=name)
  File "<string>", line 8, in run_migrations
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/runtime/environment.py", line 839, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/runtime/migration.py", line 350, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/command.py", line 173, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 433, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 473, in _run_environment
    autogen_context, migration_script
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 25, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 51, in _produce_net_changes
    autogen_context, upgrade_ops, schemas
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/util/langhelpers.py", line 303, in go
    fn(*arg, **kw)
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 83, in _autogen_for_tables
    autogen_context,
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 216, in _compare_tables
    inspector,
  File "/home/alembic/miniconda3/lib/python3.7/contextlib.py", line 112, in __enter__
    return next(self.gen)
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 319, in _compare_columns
    metadata_col,
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/util/langhelpers.py", line 303, in go
    fn(*arg, **kw)
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 917, in _compare_server_default
    rendered_conn_default,
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/runtime/migration.py", line 494, in _compare_server_default
    rendered_column_default,
  File "/home/alembic/miniconda3/lib/python3.7/site-packages/alembic/ddl/mysql.py", line 130, in compare_server_default
    r"^'|'$", "", rendered_inspector_default
  File "/home/alembic/miniconda3/lib/python3.7/re.py", line 192, in sub
    return _compile(pattern, flags).sub(repl, string, count)
TypeError: expected string or bytes-like object

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Check for rendered integer default is None on MySQL https://gerrit.sqlalchemy.org/1203

[zzzeek]

thanks for the bug report and test case!

[GoldstHa]

Yeah, you're welcome! Thank you for getting to this so quickly!