I'm having some problems with a db migration when trying to add a new column with a foreign key constraint. Here is the model (the new column is reference_oil and I've also added the oil_ingredient relation):
class UserProfileInfo(DECLARATIVE_BASE):
__tablename__ = 'userProfileInfo'
__table_args__ = ({'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'})
id = Column( # pylint: disable=invalid-name
"userId", INTEGER, ForeignKey("user.id", ondelete="CASCADE"), primary_key=True, nullable=False, index=True
)
name = Column(TEXT, nullable=True)
surname = Column(TEXT, nullable=True)
sex = Column(SMALLINT, nullable=True)
lifestyle = Column(TEXT, nullable=True)
weight = Column(FLOAT, nullable=True)
height = Column(FLOAT, nullable=True)
birth = Column(DATE, nullable=True)
timezone = Column(TEXT, nullable=True, default="CET")
default_portion_size = Column(TEXT, nullable=True)
default_salt_level = Column(TEXT, nullable=True)
default_sugar_level = Column(TEXT, nullable=True)
reference_oil = Column(INTEGER, ForeignKey("ingredient.id", ondelete="SET NULL"), index=True, nullable=True)
user = relationship("User", foreign_keys=[id], backref="userprofileinfo")
oil_ingredient = relationship("Ingredient", foreign_keys=[reference_oil], backref="userProfileInfo")
Here is the model of the referenced table:
class Ingredient(DECLARATIVE_BASE):
__tablename__ = 'ingredient'
__table_args__ = ({'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'})
id = Column(INTEGER, autoincrement=True, primary_key=True, nullable=False) # pylint: disable=invalid-name
name = Column(sa.TEXT, nullable=False)
category = Column(sa.VARCHAR(30), nullable=True)
nutrition = Column(sa.TEXT, nullable=True)
avgQuantity = Column(sa.FLOAT, nullable=False)
And this is the migration code:
def upgrade():
op.add_column('userProfileInfo', sa.Column('reference_oil', sa.INTEGER, nullable=True, index=True))
op.create_foreign_key(None, 'userProfileInfo', 'ingredient', ['reference_oil'], ['id'], ondelete='SET NULL')
dh.db_session.commit()
I've also tried this way, which results in the same error:
def upgrade():
op.add_column('userProfileInfo',
Column('reference_oil', INTEGER, ForeignKey('ingredient.id'))
)
dh.db_session.commit()
Running the migration results in the following error:
File "/code/alembic/versions/a49125f843c9_add_user_oil_columns_30_05_2022.py", line 33, in upgrade
op.create_foreign_key(None, 'userProfileInfo', 'ingredient', ['reference_oil'], ['id'], ondelete='SET NULL')
File "<string>", line 8, in create_foreign_key
File "<string>", line 3, in create_foreign_key
File "/usr/local/lib/python2.7/site-packages/alembic/operations/ops.py", line 643, in create_foreign_key
return operations.invoke(op)
File "/usr/local/lib/python2.7/site-packages/alembic/operations/base.py", line 373, in invoke
return fn(self, operation)
File "/usr/local/lib/python2.7/site-packages/alembic/operations/toimpl.py", line 152, in create_constraint
operation.to_constraint(operations.migration_context)
File "/usr/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 245, in add_constraint
self._exec(schema.AddConstraint(const))
File "/usr/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 141, in _exec
return conn.execute(construct, *multiparams, **params)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1306, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 81, in _execute_on_connection
self, multiparams, params, execution_options
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1404, in _execute_ddl
compiled,
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1863, in _execute_context
e, statement, parameters, cursor, context
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2044, in _handle_dbapi_exception
sqlalchemy_exception, with_traceback=exc_info[2], from_=e
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1820, in _execute_context
cursor, statement, parameters, context
File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/usr/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/usr/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 239, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1050, "Table './qaav729/userprofileinfo' already exists")
[SQL: ALTER TABLE `userProfileInfo` ADD FOREIGN KEY(reference_oil) REFERENCES ingredient (id) ON DELETE SET NULL]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
ERROR: 1
Any ideas what may be the problem? Thanks!
Update: We're now trying to store all the data in the table in a variable, drop the table and recreate it again, but without success. It seems retrieving the information works fine, dropping the table also works but then the sqlalchemy create table method fails:
File "/usr/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 239, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.ProgrammingError: (MySQLdb._exceptions.ProgrammingError) (1146, "Table 'qaav729.userprofileinfo' doesn't exist")
[SQL:
CREATE TABLE `userProfileInfo` (
`userId` INTEGER NOT NULL,
name TEXT,
surname TEXT,
sex SMALLINT,
lifestyle TEXT,
weight FLOAT,
height FLOAT,
birth DATE,
timezone TEXT,
default_portion_size TEXT,
default_salt_level TEXT,
default_sugar_level TEXT,
default_oil_level TEXT,
reference_oil INTEGER,
PRIMARY KEY (`userId`),
FOREIGN KEY(`userId`) REFERENCES user (id) ON DELETE CASCADE,
FOREIGN KEY(reference_oil) REFERENCES ingredient (id) ON DELETE SET NULL
)ENGINE=InnoDB CHARSET=utf8
]
Could there be an issue with the table name? After dropping the table trying to recreate it results in the above error, but recreating it with a different name works just fine.
