[0.5.1] Unable to create contactgroupmembers table in MySQL 4.0.27

[ensignricky]

I'm trying to set up a new install of 0.5.1, and I'm getting an error from MySQL when I try importing the mysql.initial.sql file into an empty database. I'm using MySQL 4.0.27.

Code Select Expand

ERROR 1005: Can't create table './roundcube/contactgroupmembers.frm' (errno: 150)

All of the tables which come before contactgroupmembers in the SQL script seem to be created successfully. Does anyone have any ideas?

[alec]

Execute SHOW ENGINE INNODB STATUS; after this .

[ensignricky]

MySQL throws a syntax error when I try SHOW ENGINE INNODB STATUS;. It looks like SHOW ENGINE wasn't supported until 4.1.12 according to the MySQL manual.

[alec]

Use SHOW INNODB STATUS instead.

[ensignricky]

Output of SHOW INNODB STATUS:

Code Select Expand

=====================================
110221 15:31:31 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 28 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2372, signal count 2355
Mutex spin waits 5153, rounds 35299, OS waits 1303
RW-shared spins 2014, OS waits 1005; RW-excl spins 36, OS waits 27
------------------------
LATEST FOREIGN KEY ERROR
------------------------
110221 15:31:18 Error in foreign key constraint of table `roundcube/contactgroupmembers`:
There is no index in table `roundcube/contactgroupmembers` where the columns appear
as the first columns. Constraint:
 FOREIGN KEY (`contact_id`)
    REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 0 820898
Purge done for trx's n:o < 0 820889 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 179624960
show 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
1357 OS file reads, 18667 OS file writes, 9368 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.00 writes/s, 0.39 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
69 inserts, 69 merged recs, 29 merges
Hash table size 34679, used cells 23852, node heap has 41 buffer(s)
0.00 hash searches/s, 6.46 non-hash searches/s
---
LOG
---
Log sequence number 0 240381568
Log flushed up to   0 240381568
Last checkpoint at  0 240381568
0 pending log writes, 0 pending chkp writes
5904 log i/o's done, 0.29 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 16114474; in additional pool allocated 1024384
Buffer pool size   512
Free buffers       1
Database pages     470
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1341, created 136, written 13154
0.00 reads/s, 0.00 creates/s, 1.39 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 176006656, state: waiting for server activity
Number of rows inserted 1034, updated 3700, deleted 160, read 2299598
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================


[alec]

I have no such problems on mysql 5.0. Try to change contactgroupmembers table definition adding index:

Code Select Expand


CREATE TABLE `contactgroupmembers` (
  `contactgroup_id` int(10) UNSIGNED NOT NULL,
  `contact_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  PRIMARY KEY (`contactgroup_id`, `contact_id`),
  CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`)
    REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`)
    REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `contactgroupmembers_contact_index` (`contact_id`)
) /*!40000 ENGINE=INNODB */;


[ensignricky]

That fixed it, thanks!

[isttoptan]

thank you, nice sharing..