sql is ok to run but when you run python code with sqlalchemy you failed

system info

mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.20    | +-----------+ python -V Python 2.7.12 

sql is ok to run

i use the below sql which sqlalchemy output, it is ok to create table.

CREATE TABLE images (     created_at DATETIME,     updated_at DATETIME,     deleted_at DATETIME,     deleted INTEGER,     platform_id VARCHAR(36) NOT NULL,     image_id VARCHAR(36) NOT NULL,     PRIMARY KEY (platform_id, image_id) );   CREATE TABLE flavors (     created_at DATETIME,     updated_at DATETIME,     deleted_at DATETIME,     deleted INTEGER,     platform_id VARCHAR(36) NOT NULL,     flavor_id VARCHAR(36) NOT NULL,     PRIMARY KEY (platform_id, flavor_id) );   CREATE TABLE security_groups (     created_at DATETIME,     updated_at DATETIME,     deleted_at DATETIME,     deleted INTEGER,     platform_id VARCHAR(36) NOT NULL,     name VARCHAR(255) NOT NULL,     tenant_id VARCHAR(36),     user_id VARCHAR(36),     PRIMARY KEY (platform_id, name) );   CREATE TABLE instances (     created_at DATETIME,     updated_at DATETIME,     deleted_at DATETIME,     deleted INTEGER,     flavor_id VARCHAR(36),     image_id VARCHAR(36),     security_group_name VARCHAR(255),     platform_id VARCHAR(36) NOT NULL,     uuid VARCHAR(36) NOT NULL,     user_id VARCHAR(36),     tenant_id VARCHAR(36),     `accessIPv4` VARCHAR(255),     `accessIPv6` VARCHAR(255),     progress INTEGER,     config_drive BOOL,     status VARCHAR(255),     `hostId` VARCHAR(255),     key_name VARCHAR(255),     name VARCHAR(255),     updated DATETIME,     created DATETIME,     launched_at DATETIME,     terminated_at DATETIME,     task_state VARCHAR(255),     vm_state VARCHAR(255),     instance_name VARCHAR(255),     `diskConfig` VARCHAR(255),     power_state INTEGER,     availability_zone VARCHAR(255),     host VARCHAR(255),     hypervisor_hostname VARCHAR(255),     PRIMARY KEY (platform_id, uuid),     CHECK (config_drive IN (0, 1)) );  ALTER TABLE instances ADD CONSTRAINT flavor_instances_fk FOREIGN KEY(platform_id, flavor_id) REFERENCES flavors (platform_id, flavor_id) 

but when i use my migration script it fails with below error message

An error has occurred:

Traceback (most recent call last): File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context cursor, statement, parameters, context File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute cursor.execute(statement, parameters) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/cursors.py", line 163, in execute result = self._query(query) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/cursors.py", line 321, in _query conn.query(q) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/connections.py", line 505, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/connections.py", line 724, in _read_query_result result.read() File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/connections.py", line 1069, in read first_packet = self.connection._read_packet() File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/connections.py", line 676, in _read_packet packet.raise_for_error() File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/protocol.py", line 223, in raise_for_error err.raise_mysql_exception(self._data) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) pymysql.err.OperationalError: (3780, "Referencing column ‘platform_id’ and referenced column ‘platform_id’ in foreign key constraint ‘flavor_instances_fk’ are incompatible.")

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/root/work/cpmsv2/cpmsv2/tests/functional/db/test_migrate.py", line 118, in name=’flavor_instances_fk’).create() File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/migrate/changeset/constraint.py", line 44, in create self.__do_imports(‘constraintgenerator’, *a, **kw) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/migrate/changeset/constraint.py", line 32, in __do_imports run_single_visitor(engine, visitorcallable, self, *a, **kw) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/migrate/changeset/databases/visitor.py", line 85, in run_single_visitor fn(element, **kwargs) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/migrate/changeset/ansisql.py", line 285, in visit_migrate_foreign_key_constraint self._visit_constraint(*p, **k) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/migrate/changeset/ansisql.py", line 297, in _visit_constraint self.execute() File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/migrate/changeset/ansisql.py", line 44, in execute return self.connection.execute(self.buffer.getvalue()) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1003, in execute return self.execute_text(object, multiparams, params) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1178, in _execute_text parameters, File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1317, in execute_context e, statement, parameters, cursor, context File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1511, in handle_dbapi_exception sqlalchemy_exception, with_traceback=exc_info[2], from=e File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise raise exception File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context cursor, statement, parameters, context File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute cursor.execute(statement, parameters) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/cursors.py", line 163, in execute result = self._query(query) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/cursors.py", line 321, in _query conn.query(q) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/connections.py", line 505, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/connections.py", line 724, in _read_query_result result.read() File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/connections.py", line 1069, in read first_packet = self.connection._read_packet() File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/connections.py", line 676, in _read_packet packet.raise_for_error() File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/protocol.py", line 223, in raise_for_error err.raise_mysql_exception(self._data) File "/root/.virtualenvs/cpmsv2/lib/python3.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (3780, "Referencing column ‘platform_id’ and referenced column ‘platform_id’ in foreign key constraint ‘flavor_instances_fk’ are incompatible.") [SQL: ALTER TABLE instances ADD CONSTRAINT flavor_instances_fk FOREIGN KEY(platform_id, flavor_id) REFERENCES flavors (platform_id, flavor_id)] (Background on this error at: http://sqlalche.me/e/13/e3q8) the detail table is referenced above named instances. (Background on this error at: http://sqlalche.me/e/13/e3q8)

the minimal code segment which could reproduce the error is like below

from sqlalchemy import create_engine from sqlalchemy import Boolean, Column, DateTime, Enum, Float from sqlalchemy import dialects from sqlalchemy import ForeignKey, ForeignKeyConstraint, Index, Integer, MetaData, PrimaryKeyConstraint, String, Table # noqa from sqlalchemy import Text  connection_string = 'your_connect_string' engine = create_engine(connection_string, echo=True) meta = MetaData() meta.bind = engine tables = []   instances = Table(     'instances', meta,     # Created by TimestampMixin     Column('created_at', DateTime),     Column('updated_at', DateTime),     # Created by SoftDeleteMixin     Column('deleted_at', DateTime),     Column('deleted', Integer),      Column('flavor_id', String(36)),     Column('image_id', String(36)),     Column('security_group_name', String(255)),      Column('platform_id', String(36), primary_key=True),     Column('uuid', String(36), primary_key=True),     Column('user_id', String(36)),     Column('tenant_id', String(36)),       Column('accessIPv4', String(255)),     Column('accessIPv6', String(255)),     Column('progress', Integer),     Column('config_drive', Boolean),     Column('status', String(255)),     Column('hostId', String(255)),     Column('key_name', String(255)),     Column('name', String(255)),      Column('updated', DateTime),     Column('created', DateTime),     Column('launched_at', DateTime),     Column('terminated_at', DateTime),      Column('task_state', String(255)),     Column('vm_state', String(255)),     Column('instance_name', String(255)),     Column('diskConfig', String(255)),     Column('power_state', Integer),     Column('availability_zone', String(255)),     Column('host', String(255)),     Column('hypervisor_hostname', String(255)),     mysql_engine='InnoDB',     mysql_charset='utf8' )   images = Table(     'images', meta,     # Created by TimestampMixin     Column('created_at', DateTime),     Column('updated_at', DateTime),     # Created by SoftDeleteMixin     Column('deleted_at', DateTime),     Column('deleted', Integer),      Column('platform_id', String(36), primary_key=True),     Column('image_id', String(36), primary_key=True), )   flavors = Table(     'flavors', meta,     # Created by TimestampMixin     Column('created_at', DateTime),     Column('updated_at', DateTime),     # Created by SoftDeleteMixin     Column('deleted_at', DateTime),     Column('deleted', Integer),      Column('platform_id', String(36), primary_key=True),     Column('flavor_id', String(36), primary_key=True), )   security_groups = Table(     'security_groups', meta,     # Created by TimestampMixin     Column('created_at', DateTime),     Column('updated_at', DateTime),     # Created by SoftDeleteMixin     Column('deleted_at', DateTime),     Column('deleted', Integer),      Column('platform_id', String(36), primary_key=True),     Column('name', String(255), primary_key=True),      Column('tenant_id', String(36)),     Column('user_id', String(36)), )   tables.append(images) tables.append(flavors) tables.append(security_groups) tables.append(instances)  meta.create_all(tables=tables) from migrate import ForeignKeyConstraint, UniqueConstraint  # UniqueConstraint(instances.c.platform_id, instances.c.flavor_id, #                  name='platform_flavor_constraint').create() ForeignKeyConstraint([instances.c.platform_id, instances.c.flavor_id],                      [flavors.c.platform_id, flavors.c.flavor_id],                      name='flavor_instances_fk').create() ForeignKeyConstraint([instances.c.platform_id, instances.c.image_id],                      [images.c.platform_id, images.c.image_id],                      name='image_instances_fk').create() ForeignKeyConstraint([instances.c.platform_id, instances.c.security_group_name], # noqa                      [security_groups.c.platform_id, security_groups.c.name],                      name='sec_groups_instances_fk').create() # noqa 
Asked on September 1, 2020 in Mysql,   Python.
Add Comment
1 Answer(s)

reminded by my friend yiwei, the charset and collect of the database of the two tables instances and flavors are different.

refer to the code is like below

    Column('hypervisor_hostname', String(255)),     mysql_engine='InnoDB',     mysql_charset='utf8' 

and other table don’t have following code snippet

    mysql_engine='InnoDB',     mysql_charset='utf8' 
Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.