bits4beatsIdee e pensieri di Roberto Rossi

A chi sviluppa con database MySQL può capitare di incappare ogni tanto in alcuni errori all’apparenza criptici, come il seguente:

Error executing SQL commands to update table.
MySQL Error Nr. 1025
Error on rename of './dbtest/#sql-2993_a' to './dbtest/table' (errno: 150)

Sia che si utilizzi la command line (il comando mysql) sia che si utilizzi un’interfaccia grafica più evoluta, come ad esempio MySQL Query Browser, spesso gli errori che MySQL riporta sull’esecuzione di script o comandi sql non vengono opportunamente descritti.

L’unico modo efficiente che ho trovato per poter visualizzare la causa esatta di tali errori è la seguente:

SHOW ENGINE INNODB STATUS;

Questo comando MySQL permette di conoscere lo stato degli indici InnoDB di MySQL (a patto ovviamente che usiate tale motore per le vostre tabelle) e di visualizzare l’ultimo errore verificatosi. Molto spesso infatti gli errori che si verificano durante la modifica di un db sono legati a vincoli o indici e quindi attraverso questo comando è possibile avere una descrizione dettagliata e decisamente esauriente rispetto al messaggio ricevuto via console o nella gui di amministrazione.

Ecco l’esempio di un output di tale comando:

mysql> SHOW ENGINE INNODB STATUS;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Status                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
=====================================
100323  8:17:40 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 576, signal count 576
Mutex spin waits 0, rounds 127, OS waits 2
RW-shared spins 31, OS waits 18; RW-excl spins 569, OS waits 556
------------------------
LATEST FOREIGN KEY ERROR
------------------------
100323  8:17:17 Error in foreign key constraint of table dbname/table:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match to the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT fk_table FOREIGN KEY (FK_ID) REFERENCES table2 (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
------------
TRANSACTIONS
------------
Trx id counter 0 6370649
Purge done for trx's n:o < 0 6370649 undo n:o < 0 0
History list length 17
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 6370637, not started, process no 10643, OS thread id 3018464144
MySQL thread id 10, query id 5029 localhost root
---TRANSACTION 0 0, not started, process no 10643, OS thread id 3018599312
MySQL thread id 9, query id 5030 localhost root
SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
7100 OS file reads, 119 OS file writes, 65 OS fsyncs
0.58 reads/s, 18274 avg bytes/read, 1.87 writes/s, 0.67 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 1398, node heap has 3 buffer(s)
0.11 hash searches/s, 6.42 non-hash searches/s
---
LOG
---
Log sequence number 0 347867737
Log flushed up to   0 347867737
Last checkpoint at  0 347867737
0 pending log writes, 0 pending chkp writes
46 log i/o's done, 0.40 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 34357238; in additional pool allocated 1048576
Buffer pool size   512
Free buffers       0
Database pages     509
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10026, created 4, written 79
0.64 reads/s, 0.09 creates/s, 1.62 writes/s
Buffer pool hit rate 987 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 10643, id 2990709648, state: sleeping
Number of rows inserted 8, updated 0, deleted 0, read 460381
0.18 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.18 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

L’errore sull’indice viene evidenziato nelle righe comprese tra (19-25 comprese):

------------------------
LATEST FOREIGN KEY ERROR
------------------------

e

------------
TRANSACTIONS
------------

In questo caso segnala l’impossibilità di eseguire la cancellazione di una colonna della tabella perché utilizzata all’interno di un indice, bisogna prima cancellare l’indice agganciato.