Oh boy. Well, luckily for me, I happened to have about 400,000 records in the messages table, delete where status = 6 trimmed that down to four more than my physmessages table.
Not sure where all the extra messages came from, maybe a bug in a long discarded version. The DB is in 2.0 format and working well. Now, I may awful person for saying this, but the thing with InnoDB is that it's exceedingly complicated to make hot backups of, and I like having a nice revisioned history of my mysql store, updated nightly with mysqlhotcopy and rdiff-backup. I know that innodb has wonderful binary logging, but I'm still not sure how to make backups of the DB without taking the database offline.. Anyway, that discussion should be reserved for the mysql user mailing list. Thanks for all your help! -Sri On Wed, Mar 23, 2005 at 05:07:03PM +0100, Paul J Stevens wrote: > Then you have messages and messageblks, but no physmessages to connect > them. Ergo, they are unreachable by the IMAP and POP servers. > > backup your data *now* > > Perhaps your database really is in 1.2 format (the messageblk table has a > message_idnr field). In that case you're saved; you simply botched the > migration from 1.2 to 2.x format. Clone your database, cleanout the recent > clean messages, and re-run the migration script, and access the new > database on a separate dbmail instance. But again: make sure you backup > first. > > If your database really is in 2.x format you're in deep. No way to recover > the connection between messageblks and messages without re-inserting the > whole shebang. > > Dump the unconnected messageblks into a mbox file, and reload them with > mailbox2dbmail. That sucks, I know. But I know of no better way. > > And migrate to InnoDB while you're at it.... > > > Sri Gupta wrote: > >Erm, and what if I'm recklessly running MyISAM with wild abandon? > > > >-Sri > > > >On Wed, Mar 23, 2005 at 04:44:23PM +0100, Paul J Stevens wrote: > > > >>Then you have a problem, because those message records don't have any > >>physmessages associated with them. And no physmessage means no > >>messageblks if you run innodb or pgsql. > >> > >>Unless our sql is totally off base: > >> > >>SELECT msg.message_idnr FROM dbmail_messages msg > >>LEFT JOIN dbmail_sphysmessage pm ON > >>msg.physmessage_id = pm.id WHERE pm.id is NULL; > >> > >> > >>Do did you perhaps delete physmessages by hand (naughty, naughty) at some > >>point? Like: > >> > >>DELETE from dbmail_physmessage WHERE internal_date < '2005-01-01'; > >> > >>That would explain this alright.... > >> > >> > >> > >>Sri Gupta wrote: > >> > >>>Wait a sec. I'm sure that the 170,000-odd messages in my database being > >>>seen as null, shouldn't be. This dbmail installation only has one > >>>account (it's my workstation), and I only have about 180,000 messages > >>>total stored. There's no way 170,000 of them should be purged. > >>> > >>>-sri > >>> > >>>On Wed, Mar 23, 2005 at 02:50:19PM +0100, Paul J Stevens wrote: > >>> > >>> > >>>>Will fix this. > >>>> > >>>>Ilja Booij wrote: > >>>> > >>>> > >>>>>On Wed, 23 Mar 2005 09:05:00 +0100, Paul J Stevens <[EMAIL PROTECTED]> > >>>>>wrote: > >>>>> > >>>>> > >>>>> > >>>>>>Ok. I checked the source. > >>>>>> > >>>>>>Looks like do_null_messages correctly finds all the NULL messages, > >>>>>>and does set > >>>>>>MESSAGE_STATUS_ERROR on all these messages. But there's no code to > >>>>>>actually > >>>>>>flush all those messages from the database. I suspect that somewhere > >>>>>>in the > >>>>>>past, this was handled subtly different. > >>>>>> > >>>>>>Aaron, Ilja: any reason why do_null_messages calls > >>>>>>db_set_message_status to set > >>>>>>MESSAGE_STATUS_ERROR instead of MESSAGE_STATUS_PURGE? > >>>>> > >>>>> > >>>>>I guess it does this for purely historical reasons. I checked some old > >>>>>code from the 1.2 branch, and it does exactly the same there. It would > >>>>>certainly be better to set the status to MESSAGE_STATUS_PURGE. > >>>>> > >>>>>Ilja > > > >_______________________________________________ > >Dbmail mailing list > >[email protected] > >https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > -- > ________________________________________________________________ > Paul Stevens [EMAIL PROTECTED] > NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 > The Netherlands_______________________________________www.nfg.nl > _______________________________________________ > Dbmail mailing list > [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail -- Sri Gupta ICQ: 9208831 | AIM: Oopszie | Yahoo IM: Oopsz | MSN: [EMAIL PROTECTED] http://www.iPlur.com/ -- Avarice. Gluttony. Envy. Lust. Pride. Sloth. Wrath. The seven deadly sins. Collect them all!
