Issues when using batch_alter_table in parallel using recreate (name of the temp table is not unique)

[sqlalchemy-bot]

Migrated issue, originally created by Bastien Gérard (@bagerard)

Hi,
I recently had the following error when using the batch_alter_table in parallel on different tables of a database. It is due to the name of the temporary table that is not unique and collides.

I'd suggest to append a unique identifier (uuid) to the name of the temp table (_alembic_batch_temp) but perhaps you'll have a better idea. As minor as it is, I'm willing to work on this :)

I'm using MySQL5.5 & alembic 0.9.3

...
  File "/home/instance/env27/lib/python2.7/site-packages/alembic/operations/base.py", line 299, in batch_alter_table
    impl.flush()
  File "/home/instance/env27/lib/python2.7/site-packages/alembic/operations/batch.py", line 82, in flush
    batch_impl._create(self.impl)
  File "/home/instance/env27/lib/python2.7/site-packages/alembic/operations/batch.py", line 246, in _create
    op_impl.create_table(self.new_table)
  File "/home/instance/env27/lib/python2.7/site-packages/alembic/ddl/impl.py", line 194, in create_table
    self._exec(schema.CreateTable(table))
  File "/home/instance/env27/lib/python2.7/site-packages/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/home/instance/env27/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/home/instance/env27/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/home/instance/env27/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1002, in _execute_ddl
    compiled
  File "/home/instance/env27/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/home/instance/env27/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
    exc_info
  File "/home/instance/env27/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/instance/env27/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/instance/env27/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
  File "/home/instance/env27/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/instance/env27/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (_mysql_exceptions.OperationalError) (1050, "Table '_alembic_batch_temp' already exists") [SQL: u'\nCREATE TABLE _alembic_batch_temp (\n\t`_CPWP_PK_ID_` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, \n\t`STUDYID` VARCHAR(8), \n\t`DOMAIN` VARCHAR(2), \n\t`USUBJID` VARCHAR(18), \n\t`VSSEQ` DOUBLE, \n\t`VSGRPID` VARCHAR(9), \n\t`VSTESTCD` VARCHAR(8), \n\t`VSTEST` VARCHAR(40), \n\t`VSPOS` VARCHAR(7), \n\t`VSSTRESN` DOUBLE, \n\t`VSSTRESU` VARCHAR(9), \n\t`VSSTAT` VARCHAR(8), \n\t`VSLOC` VARCHAR(11), \n\t`VSLAT` VARCHAR(5), \n\t`VSBLFL` VARCHAR(1), \n\t`VSDRVFL` VARCHAR(1), \n\t`VISITNUM` VARCHAR(255), \n\t`VISIT` VARCHAR(9), \n\t`VSDTC` VARCHAR(19), \n\t`VSDTC_DT` DOUBLE, \n\t`CP_PATIENT` VARCHAR(255), \n\t`CP_VISIT_ID` INTEGER(11), \n\t`VSDTC_DATETIME` DATETIME, \n\t`CP_CENTER` VARCHAR(255), \n\t`CP_REGION` VARCHAR(255), \n\t`CP_VISIT_NAME` VARCHAR(255), \n\t`CP_UNSCHEDULED` BIGINT, \n\tPRIMARY KEY (`_CPWP_PK_ID_`)\n)ENGINE=MyISAM DEFAULT CHARSET=utf8\n\n']

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

why uuid and not the original name of the table itself?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

uuids are long and cumbersome and make testing more difficult, both for Alembic's own suite as well as for the purpose of validating that a particular batch script remains constant.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

https://gerrit.sqlalchemy.org/#/c/572/

[sqlalchemy-bot]

Bastien Gérard (@bagerard) wrote:

I'm altering an existing table with the 'recreate' flag thus from what I understand it is creating a temporary table with the name '_alembic_batch_temp' and when it completes, deletes the original table and rename the temporary one. Another possibility is to let me inject the name of the temporary table into the context

[sqlalchemy-bot]

Bastien Gérard (@bagerard) wrote:

OK I got what you mean. I wanted to work on it but I see that you already implemented it :) Thx!

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Append table name to batch temp name

The name of the temporary table in batch mode is now generated
off of the original table name itself, to avoid conflicts for the
unusual case of multiple batch operations running against the same
database schema at the same time.

Change-Id: Idbeabf9558887d3f5525e7045d5de33bab6805a5
Fixes: #457

→ 9b80d35

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed