python - 创建一堆表时出现神秘的 "errno 150"Mysql.connector python

Question

所以问题是，我在一个表中有一个列，我引用其他表中的所有外键。我仔细检查了所有键，它们都是 INT(11)，我引用的列是 INT(11) NOT NULL AUTO_INCRMENT - 完全相同的类型。

所以，一开始我创建了 5 个表，一切都很好，但是添加第 6 个表后，它开始抛出 errno 150，并且无法创建 6 个表中的 5 个，所以基本上它可以完美地创建5 个表，但是当我执行 6 个表时，无论我包含哪些表 - 它都会失败(创建 5 个表并抛出 errno:150)。

我还尝试忽略外键约束，但最终得到的是空表..

这是我的代码:

RESULTS_TABLES['ResultHeader'] = (
            "CREATE TABLE IF NOT EXISTS `ResultHeader` ("
            " `ResultHeaderID` int(11) NOT NULL AUTO_INCREMENT,"
            " `DateTime` DATETIME NOT NULL,"
            " `ClientIP` VARCHAR(16) NOT NULL,"
            " `RequestNumber` VARCHAR(20) NOT NULL, "
            " `TechName` VARCHAR(150) NOT NULL, "
            "  PRIMARY KEY (`ResultHeaderID`)"
            ") ENGINE=InnoDB"
    )

    RESULTS_TABLES['Results'] = (
            "CREATE TABLE IF NOT EXISTS `Results` ("
            "`ResultsID` int(11) NOT NULL AUTO_INCREMENT,"
            "`ResultHeaderID` int(11) NOT NULL,"
            "`TariffPlan` VARCHAR(70), "
            "`AccountNum` VARCHAR(50), "
            "`CustomerName` VARCHAR(70), "
            "`PlanSettingStatus` ENUM('Настроен корректно','Настроен не корректно','Настроен корректно: Блокировка','Настроен не корректно: Блокировка'),"
            "`PortSpeed` VARCHAR(15), "
            "`LinkStatus` VARCHAR(20), "
            "`CableTest` VARCHAR(50), "
            "`CableLength` VARCHAR(50), "
            "`PortErrorsStatus` ENUM('OK', 'ERROR'), "
            "`LLDPStatus` ENUM('Enabled', 'Disabled'), "
            " PRIMARY KEY (`ResultsID`),"
            " CONSTRAINT `resHeadKey` FOREIGN KEY (`ResultHeaderID`)"
            "REFERENCES `ResultHeader` (`ResultHeaderID`) ON DELETE CASCADE"
            ") ENGINE=InnoDB"
    )

    RESULTS_TABLES['Speed'] = (
            "CREATE TABLE IF NOT EXISTS `Speed` ("
            "`SpeedID` INT(11) NOT NULL AUTO_INCREMENT,"
            "`ResultHeaderID` INT(11), "
            "`ServerName` VARCHAR(100), "
            "`ServerLocation` VARCHAR(70), "
            "`UploadSpeed` VARCHAR(15),"
            "`DownloadSpeed` VARCHAR(15),"
            "PRIMARY KEY (`SpeedID`), "
            "CONSTRAINT `spResHeadKey` FOREIGN KEY (`ResultHeaderID`)"
            "REFERENCES `ResultHeader` (`ResultHeaderID`) ON DELETE CASCADE"
            ") ENGINE=InnoDB"
    )

    RESULTS_TABLES['PortErrors'] = (
            "CREATE TABLE IF NOT EXISTS `PortErrors` ("
            "`PortErrorsID` INT(11) NOT NULL AUTO_INCREMENT, "
            "`ResultHeaderID` INT(11), "
            "`ErrorType` ENUM('TX', 'RX'), "
            "`ErrorName` VARCHAR(50), "
            "`ErrorAmount` VARCHAR(50), "
            "PRIMARY KEY (`PortErrorsID`), "
            "CONSTRAINT `pErrResHeadKey` FOREIGN KEY (`ResultHeaderID`)"
            "REFERENCES `ResultHeader` (`ResultHeaderID`) ON DELETE CASCADE"
            ") ENGINE=InnoDB"
    )


    RESULTS_TABLES['LocalHosts'] = (
            "CREATE TABLE IF NOT EXISTS `LocalHosts` ("
            "`LocalHostsID` INT(11) NOT NULL AUTO_INCREMENT, "
            "`ResultHeaderID` INT(11), "
            "`IPAddress` VARCHAR(20), "
            "`DNSName` VARCHAR(50), "
            "`PacketsSent` TINYINT(4), "
            "`Errors` TINYINT(4), "
            "`AverageResponseTime` VARCHAR(5), "
            "PRIMARY KEY (`LocalHostsID`), "
            "CONSTRAINT `lhResHeadKey` FOREIGN KEY (`ResultHeaderID`)"
            "REFERENCES `ResultHeader` (`ResultHeaderID`) ON DELETE CASCADE"
            ") ENGINE=InnoDB"
    )

    RESULTS_TABLES['RemoteHosts'] = (
            "CREATE TABLE IF NOT EXISTS `RemoteHosts` ("
            "`RemoteHostsID` INT(11) NOT NULL AUTO_INCREMENT, "
            "`ResultHeaderID` INT(11), "
            "`IPAddress` VARCHAR(20), "
            "`DNSName` VARCHAR(50), "
            "`PacketsSent` TINYINT(4), "
            "`Errors` TINYINT(4), "
            "`AverageResponseTime` VARCHAR(5), "
            "PRIMARY KEY (`RemoteHostsID`),"
            "CONSTRAINT `rhResHeadKey` FOREIGN KEY (`ResultHeaderID`)"
            "REFERENCES `ResultHeader` (`ResultHeaderID`) ON DELETE CASCADE"
            ") ENGINE=InnoDB"
    )


    cur.execute("SET FOREIGN_KEY_CHECKS = 1")



    for name, ddl in RESULTS_TABLES.iteritems():
        try:

            cur.execute(ddl)

        except mysql.connector.Error as err:
            cur.execute("SHOW ENGINE INNODB STATUS")
            for i in cur:
                    print(err.msg )
                    for j in i:
                            print j
                    print '===========================END OF ERROR DATA============================='
        else:
            print("OK")

    cur.execute("SET FOREIGN_KEY_CHECKS = 1")

这是 InnoDB 状态的输出:

    Can't create table 'experimentalBase.Results' (errno: 150)
InnoDB


=====================================
150817 15:36:09 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 517 1_second, 516 sleeps, 45 10_second, 68 background, 68 flush
srv_master_thread log flush and writes: 516
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 94, signal count 94
Mutex spin waits 45, rounds 1350, OS waits 44
RW-shared spins 50, rounds 1500, OS waits 50
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
150817 15:36:09 Error in foreign key constraint of table experimentalBase/Results:
 FOREIGN KEY (`ResultHeaderID`)REFERENCES `ResultHeader` (`ResultHeaderID`) ON DELETE CASCADE) ENGINE=InnoDB:
Cannot resolve table name close to:
 (`ResultHeaderID`) ON DELETE CASCADE) ENGINE=InnoDB
------------
TRANSACTIONS
------------
Trx id counter 1493
Purge done for trx's n:o < 147C undo n:o < 0
History list length 446
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 1517, OS thread handle 0x7fe8dc0bb700, query id 2480 94.137.243.254 adminRemote
SHOW ENGINE INNODB STATUS
---TRANSACTION 12A4, not started
MySQL thread id 1456, OS thread handle 0x7fe8dc17f700, query id 2467 94.137.243.254 adminRemote
---TRANSACTION 0, not started
MySQL thread id 1412, OS thread handle 0x7fe8dc1b0700, query id 748 localhost root
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
403 OS file reads, 1818 OS file writes, 519 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1000.00 writes/s, 1000.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 1 buffer(s)
0.00 hash searches/s, 41000.00 non-hash searches/s
---
LOG
---
Log sequence number 3079269
Log flushed up to   3079269
Last checkpoint at  3056940
0 pending log writes, 0 pending chkp writes
366 log i/o's done, 1000.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 107688
Buffer pool size   8191
Free buffers       7753
Database pages     437
Old database pages 0
Modified db pages  45
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 392, created 45, written 1378
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 437, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2510, id 140637741238016, state: sleeping
Number of rows inserted 11, updated 0, deleted 0, read 100
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

===========================END OF ERROR DATA=============================

Answer 1

发现我的错误。如果有 5 个表，顺序是正确的，但后来我意识到它在添加另一个表后混合了字典键，因此所有其他引用的主表几乎是最后创建的。