Im attempting a migration of a Dbmail 2.2.17 installation to 3.0.2 with a backup of our production database on a spare machine to test the procedure.
Our dbmail installation contains some 900k messages and is some 360gig (data; index 1.9gig). The primary reason for wishing to migrate is for the single instance storage of mime-parts to hopefully reduce the storage space significantly (when I tried the same migration last year with the RC there was a reduction by some 60%). I am at the stage of running dbmail-util My to convert the message attachments to the new storage system and am hitting a brick wall in the sense that dbmail-util is repeatedly coredumping. If I run the dbmail-util command immediately again I receive foreign key errors: May 22 10:34:35 dbm3 dbmail-util[6041]: [0x80240b040] Error:[db] db_exec(+319): SQLException: Cannot add or update a child row: a foreign key constraint fails (`dbmail`.`dbmail_partlists`, CONSTRAINT `dbmail_partlists_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) May 22 10:34:35 dbm3 dbmail-util[6041]: [0x80240b040] Error:[db] db_exec(+320): failed query [INSERT INTO dbmail_partlists (physmessage_id, is_header, part_key, part_depth, part_order, part_id) VALUES (612844,1,1,0,0,27)] migrating physmessage_id: 612844 failed The show innodb status; gives 120522 10:34:35 Transaction: TRANSACTION 0 10128353, ACTIVE 0 sec, OS thread id 34384941632 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 105, query id 3033949 localhost dbmail update INSERT INTO dbmail_partlists (physmessage_id, is_header, part_key, part_depth, part_order, part_id) VALUES (612844,1,1,0,0,27) Foreign key constraint fails for table `dbmail`.`dbmail_partlists`: , CONSTRAINT `dbmail_partlists_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index `message_parts` tuple: DATA TUPLE: 8 fields; 0: len 8; hex 00000000000959ec; asc Y ;; 1: len 2; hex 8001; asc ;; 2: len 2; hex 8000; asc ;; 3: len 2; hex 8000; asc ;; 4: len 6; hex 0000009a8be1; asc ;; 5: len 7; hex 800000002d0110; asc - ;; 6: len 1; hex 81; asc ;; 7: len 8; hex 000000000000001b; asc ;; But in parent table `dbmail`.`dbmail_physmessage`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 00000000000959ee; asc Y ;; 1: len 6; hex 00000009d994; asc ;; 2: len 7; hex 800012000c0335; asc 5;; 3: len 8; hex 0000000000001124; asc $;; 4: len 8; hex 000000000000116f; asc o;; 5: len 8; hex 8000124a4658ef9f; asc JFX ;; which I can avoid by deleting the offending message i.e. delete from dbmail_messageblks where physmessage_id = 609063; Deleting them is fine for my testing purposes but obviously is going to be an issue if/when I carry out the migration for real. Can anyone suggest to me what might be going wrong, or if there is anything useful I can examine? So far this has happened 7 times up to physmessage_id 612844 (out of the 900k or so) so it is infrequent but being a coredump it does mean it halts the migration which is inconvenient. I am running MySQL 5.1.55, on freebsd 8.2 for general information. Also, and separately, the migration is pretty slow overall which is an issue for the downtime it will cause. Looking at the test box (4 core Xeon, 4gig ram, standard hdd) during the conversion the processor load is low (20% overall i.e. 1 processor at under 100%), the disk utility is low (normally under 50%) and innodb pool is set to 500meg which is obviously full, so can someone suggest what is rate limiting the conversion? Thank you Daniel Schütze ------------------------ CWA International Balmoral House 9 John Street London WC1N 2ES (t) + 44 (0)20 7242 8444 (e) d...@cwa.uk.com (w) http://www.cwa.uk.com/
_______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
