Analisis ketika Gagal Membuat Tabel InnoDB pada MySQL (InnoDB Troubleshooting - MySQL)

Kegagalan saat membuat tabel InnoDB seringkali disebabkan oleh masalah sepele diantaranya perbedaan definisi kolom.

MySQL memiliki fasilitas untuk troubleshooting untuk masalah tersebut. Gunakan perintah berikut:

SHOW ENGINE INNODB STATUS

Mysql akan memberikan informasi error kira-kira seperti berikut:

Type	Name	Status
InnoDB		===================================== 150513 9:29:01 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 5 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 51 1_second, 51 sleeps, 4 10_second, 12 background, 12 flush srv_master_thread log flush and writes: 57 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5, signal count 5 Mutex spin waits 1, rounds 30, OS waits 0 RW-shared spins 5, rounds 150, OS waits 5 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 ------------------------ 150513 9:22:01 Error in foreign key constraint of table inventori/pengadaan: FOREIGN KEY (`approval_id`) REFERENCES `inventori`.`user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign key definition. ------------ TRANSACTIONS ------------ Trx id counter D7B48 Purge done for trx's n:o < D7B47 undo n:o < 0 History list length 1551 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 34, OS thread handle 0x19e0, query id 709 localhost 127.0.0.1 root show engine innodb status ---TRANSACTION D7B05, not started MySQL thread id 8, OS thread handle 0x1b4c, query id 676 localhost 127.0.0.1 root ---TRANSACTION 0, not started MySQL thread id 7, OS thread handle 0x18d4, query id 677 localhost 127.0.0.1 root -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: wait Windows aio (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 693 OS file reads, 219 OS file writes, 46 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.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 69257, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 672691005 Log flushed up to 672691005 Last checkpoint at 672691005 0 pending log writes, 0 pending chkp writes 41 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 17006592; in additional pool allocated 0 Dictionary memory allocated 402804 Buffer pool size 1024 Free buffers 327 Database pages 695 Old database pages 273 Modified db pages 0 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 682, created 13, written 177 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 695, 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 id 3340, state: waiting for server activity Number of rows inserted 2, updated 0, deleted 0, read 21 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================

Sekian post kali ini... Kritik saran silahkan berkomentar :D