The following issue has been RESOLVED. ====================================================================== http://www.dbmail.org/mantis/view.php?id=409 ====================================================================== Reported By: windowsrefund Assigned To: paul ====================================================================== Project: DBMail Issue ID: 409 Category: Database layer Reproducibility: always Severity: major Priority: normal Status: resolved target: Resolution: no change required Fixed in Version: ====================================================================== Date Submitted: 13-Sep-06 20:28 CEST Last Modified: 06-Oct-06 18:15 CEST ====================================================================== Summary: SQL script fails to create dbmail_envelope table Description: mysql version: 5.0.22-standard-log
====================================================================== Relationships ID Summary ---------------------------------------------------------------------- has duplicate 0000421 envelope check fail to fix problem afte... ====================================================================== ---------------------------------------------------------------------- windowsrefund - 13-Sep-06 20:31 ---------------------------------------------------------------------- This applies to 2268. Sorry for the typo. ---------------------------------------------------------------------- paul - 17-Sep-06 16:18 ---------------------------------------------------------------------- this any better: DROP TABLE IF EXISTS `dbmail_envelope`; CREATE TABLE `dbmail_envelope` ( `physmessage_id` bigint(20) NOT NULL default '0', `id` bigint(20) NOT NULL auto_increment, `envelope` text NOT NULL, PRIMARY KEY (`id`), KEY `physmessage_id` (`physmessage_id`), CONSTRAINT `dbmail_envelope_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ---------------------------------------------------------------------- aaron - 26-Sep-06 23:10 ---------------------------------------------------------------------- Paul, please be sure to put the contents of the 2_1_7-2_1_8 script into the main create_tables.sql. ---------------------------------------------------------------------- windowsrefund - 04-Oct-06 00:21 ---------------------------------------------------------------------- The constraint is causing this to fail on 5.0.22-standard-log For whatever reason, I was able to run this script successfully on 5.0.21-standard-log. ---------------------------------------------------------------------- alex - 04-Oct-06 18:37 ---------------------------------------------------------------------- The script should be changed to include a "set FOREIGN_KEY_CHECKS=0;" at the beginning and a set "FOREIGN_KEY_CHECKS=1;" at the end. That's all there is to it. ---------------------------------------------------------------------- windowsrefund - 04-Oct-06 19:02 ---------------------------------------------------------------------- This fix allowed me to create the table. However, now every time I run dbmail-util -by, it finds > 7000 missing envelope values Repairing DBMAIL for cached envelopes... Ok. Found [7882] missing envelope values. ......................................... Nothing gets inserted into the dbmail_envelope table. ---------------------------------------------------------------------- alex - 05-Oct-06 16:18 ---------------------------------------------------------------------- edit: This should not be here anyway. I'll file a separate bug report. I can confirm this. It finds missing envelope values, claims to have fixed it, but on the next run displays the same error. And what's with the dots? Example: test:~ # dbmail-util -by Opening connection to database... Opening connection to authentication... Ok. Connected. Repairing DBMAIL for incorrect is_header flags... Ok. Found [0] incorrect is_header flags. Repairing DBMAIL for rfcsize field... Ok. Found [0] missing rfcsize values. Repairing DBMAIL for cached envelopes... Ok. Found [96] missing envelope values. ................................................................................................ Repairing DBMAIL for cached header values... Ok. Found [0] un-cached physmessages. Maintenance done. Errors were found and fixed. Try running dbmail-util again to confirm that the errors were repaired. test:~ # dbmail-util -by Opening connection to database... Opening connection to authentication... Ok. Connected. Repairing DBMAIL for incorrect is_header flags... Ok. Found [0] incorrect is_header flags. Repairing DBMAIL for rfcsize field... Ok. Found [0] missing rfcsize values. Repairing DBMAIL for cached envelopes... Ok. Found [96] missing envelope values. ................................................................................................ Repairing DBMAIL for cached header values... Ok. Found [0] un-cached physmessages. Maintenance done. Errors were found and fixed. Try running dbmail-util again to confirm that the errors were repaired. ---------------------------------------------------------------------- aaron - 05-Oct-06 16:33 ---------------------------------------------------------------------- Adam and Alex, please check your logs and see if you see this error: "dbmail-message.c,dbmail_message_cache_envelope: insert envelope failed [%s]" where %s will hopefully be some useful error message ;-) ---------------------------------------------------------------------- windowsrefund - 05-Oct-06 17:30 ---------------------------------------------------------------------- Here is one such message. What is NIL? Oct 4 12:56:40 vtmail1 dbmail/maintenance[29961]: Error dbmail-message.c,dbmail_message_cache_envelope: insert envelope failed [INSERT INTO dbmail_envelope (physmessage_id, envelope) VALUES (4322,'(\"Fri, 13 Jan 2006 06:54:53 -0700\" \"RE: FW: Regarding Case Number 3213508816\" ((\"Thompson, Catherine M\" NIL \"catherine.m.thompson\" \"hp.com\")) ((\"Thompson, Catherine M\" NIL \"catherine.m.thompson\" \"hp.com\")) ((\"Thompson, Catherine M\" NIL \"catherine.m.thompson\" \"hp.com\")) ((\"Adam Kosmin\" NIL \"akosmin\" \"vtsystems.com\")) NIL NIL NIL \"<[EMAIL PROTECTED]>\")')] ---------------------------------------------------------------------- aaron - 05-Oct-06 23:26 ---------------------------------------------------------------------- NIL is a keyword used in the IMAP protocol. It's normal to be seeing lots of NILs in a typical IMAP session. For both of you guys, could you check which of your tables are InnoDB? If some are MyISAM tables, that would be a major reason for foreign key checks to be failing! ---------------------------------------------------------------------- alex - 06-Oct-06 10:43 ---------------------------------------------------------------------- Ugh, not all the tables are InnoDB. When the first main tables were created quite some time ago, then InnoDB had not been configured in my.cf, so it silently defaulted to myisam. Only the tables I added later or have recreated are InnoDB. Sorry for the noise. Alex ---------------------------------------------------------------------- windowsrefund - 06-Oct-06 18:02 ---------------------------------------------------------------------- wow, I appear to be in the same boat. Nice catch Arron :) ---------------------------------------------------------------------- aaron - 06-Oct-06 18:15 ---------------------------------------------------------------------- InnoDB tables are required for foreign keys. Issue History Date Modified Username Field Change ====================================================================== 13-Sep-06 20:28 windowsrefund New Issue 13-Sep-06 20:31 windowsrefund Note Added: 0001399 17-Sep-06 16:18 paul Note Added: 0001404 17-Sep-06 16:18 paul Status new => feedback 17-Sep-06 16:18 paul Status feedback => assigned 17-Sep-06 16:18 paul Assigned To => paul 26-Sep-06 23:10 aaron Note Added: 0001420 30-Sep-06 19:54 paul Status assigned => resolved 30-Sep-06 19:54 paul Resolution open => fixed 04-Oct-06 00:21 windowsrefund Status resolved => feedback 04-Oct-06 00:21 windowsrefund Resolution fixed => reopened 04-Oct-06 00:21 windowsrefund Note Added: 0001444 04-Oct-06 18:37 alex Note Added: 0001451 04-Oct-06 19:02 windowsrefund Note Added: 0001452 05-Oct-06 16:12 alex Note Added: 0001462 05-Oct-06 16:18 alex Note Edited: 0001462 05-Oct-06 16:33 aaron Note Added: 0001463 05-Oct-06 16:34 aaron Relationship added related to 0000421 05-Oct-06 17:30 windowsrefund Note Added: 0001465 05-Oct-06 23:26 aaron Note Added: 0001466 06-Oct-06 10:43 alex Note Added: 0001468 06-Oct-06 18:02 windowsrefund Note Added: 0001471 06-Oct-06 18:14 aaron Relationship replaced has duplicate 0000421 06-Oct-06 18:15 aaron Status feedback => resolved 06-Oct-06 18:15 aaron Resolution reopened => no change required 06-Oct-06 18:15 aaron Note Added: 0001474 ======================================================================
