Cuando estamos alterando la estructura de un tabla en MySQL, nos podemos encontrar con el siguiente error al añadir una llave foránea:
MySQL Cannot Add Foreign Key Constraint
Estos errores con poca información son más molestos de lo habitual. Como las posibles razones por las que el motor de la base de datos no ha podido crearla son diversas, sería ventajoso evitar tener que estudiarlas una por una. Esto lo podemos lograr con la siguiente orden:
SHOW ENGINE INNODB STATUS;
En la sección «LATEST FOREIGN KEY ERROR» encontraremos una descripción del error más completa.
| InnoDB | |
=====================================
2018-02-20 11:22:34 0x7f075c175700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 25 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 12622 srv_active, 0 srv_shutdown, 5333759 srv_idle
srv_master_thread log flush and writes: 5344820
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 19660
OS WAIT ARRAY INFO: signal count 17755
RW-shared spins 0, rounds 11780, OS waits 6140
RW-excl spins 0, rounds 17899, OS waits 382
RW-sx spins 738, rounds 11751, OS waits 121
Spin rounds per wait: 11780.00 RW-shared, 17899.00 RW-excl, 15.92 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-02-19 09:36:53 0x7f075c208700 Cannot drop table `serca`.`promo_landing`
because it is referenced by `serca`.`promo_landing_i18n`
------------
TRANSACTIONS
------------
Trx id counter 1864352
Purge done for trx's n:o < 1864352 undo n:o < 0 state: running but idle
History list length 808
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421144897607520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
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] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
310347 OS file reads, 83964 OS file writes, 23539 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.92 writes/s, 0.52 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 7 merges
merged operations:
insert 4, delete mark 3, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 14 buffer(s)
Hash table size 34673, node heap has 3 buffer(s)
Hash table size 34673, node heap has 35 buffer(s)
Hash table size 34673, node heap has 8 buffer(s)
Hash table size 34673, node heap has 9 buffer(s)
Hash table size 34673, node heap has 4 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
11.56 hash searches/s, 1.68 non-hash searches/s
---
LOG
---
Log sequence number 567281250
Log flushed up to 567281250
Pages flushed up to 567281250
Last checkpoint at 567281241
0 pending log flushes, 0 pending chkp writes
15014 log i/o's done, 0.28 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 42107376
Buffer pool size 8191
Free buffers 1024
Database pages 7090
Old database pages 2597
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 150106, not young 4595614
0.16 youngs/s, 0.00 non-youngs/s
Pages read 308306, created 2548, written 65550
0.00 reads/s, 0.00 creates/s, 0.60 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: 7090, unzip_LRU len: 0
I/O sum[18]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1022, Main thread ID=139669620905728, state: sleeping
Number of rows inserted 12470971, updated 1849, deleted 367, read 104405344
0.24 inserts/s, 0.00 updates/s, 0.00 deletes/s, 2852.41 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Como excusa para probar Simple Screen Recorder en mi portátil, he hecho el siguiente vídeo en el que explico lo mismo: