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

0
votes

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
1
pythonmysqlsqlalchemypymysql

1 Answers

0
votes

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'