this is not really a problem and is triggered by the fact the we truncated only the header-tables while the references table had already data
more a problem is that headervalues is growing and growing over the time and after few months it uses more index-space as the rest of the database Am 02.04.2012 12:25, schrieb ITronic Harald Leithner: > Paul, > > any statement to the "referencesfield" Problem? > > thx > Harald > > Am 29.03.2012, 10:34 Uhr, schrieb ITronic Harald Leithner > <harald.leith...@itronic.at>: > >> Hi, >> >> So I truncating the cache was successful, but I got duplicate error message >> while rebuilding: >> >> --- >> Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] >> insert_field_cache(+1775): insert >> referencesfield failed >> [1d4e44360608081238p3dfdaea5xac3aabc057bd5...@mail.gmail.com] >> ..Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] >> insert_field_cache(+1773): >> SQLException: Duplicate entry '221-44d97bce.9070...@domainname.com' for key >> 'physmessage_id' >> Mar 29 08:37:31 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] >> insert_field_cache(+1775): insert >> referencesfield failed [44d97bce.9070...@domainname.com] >> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] >> insert_field_cache(+1773): >> SQLException: Duplicate entry '220-44d97c5...@domainname.com' for key >> 'physmessage_id' >> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] >> insert_field_cache(+1775): insert >> referencesfield failed [44d97c5...@domainname.com] >> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] >> insert_field_cache(+1773): >> SQLException: Duplicate entry '219-44d97f0d.3070...@domainname.com' for key >> 'physmessage_id' >> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] >> insert_field_cache(+1775): insert >> referencesfield failed [44d97f0d.3070...@domainname.com] >> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] >> insert_field_cache(+1773): >> SQLException: Duplicate entry '218-44d97f9a.2080...@domainname.com' for key >> 'physmessage_id' >> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] >> insert_field_cache(+1775): insert >> referencesfield failed [44d97f9a.2080...@domainname.com] >> ..Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] >> insert_field_cache(+1773): >> SQLException: Duplicate entry '217-44d983b3.8030...@domainname.com' for key >> 'physmessage_id' >> Mar 29 08:37:32 mailstore dbmail-util[19249]: [0x1e7c040] Error:[message] >> insert_field_cache(+1775): insert >> referencesfield failed [44d983b3.8030...@domainname.com] >> --- >> >> But I don't get this for all messages, is this a problem? >> >> Btw webmail works perfect now. >> >> >> About the Headervalues: >> >> before rebuild I had about 1,9 Million entries after rebuild 1,7 Million >> entries. >> >> If I interpret the source of dbmail util correctly, it only delete messages >> from messages table. >> Thats good for all tables using constraints but the dbmail_headervalue table >> has no constraints. >> Only dmail_header has a constraint to dbmail_headervalue which cascade on >> delete but does this every happen? >> Would the other direction be better? Is this possible? >> >> Another fix would be a simple query for clean up dbmail_headervalue (maybe >> trigger?) >> "delete from dbmail_headervalue where id not in >> (dbmail_header.headervalue_id);" (not tested) >> >> Regards, >> >> Harald >> >> >> Am 28.03.2012, 20:04 Uhr, schrieb Reindl Harald <h.rei...@thelounge.net>: >> >>> >>> Am 28.03.2012 14:57, schrieb Paul J Stevens: >>>> On 03/28/2012 02:48 PM, ITronic Harald Leithner wrote: >>>>> no Problem. I will do this tonight because this would take sometime to >>>>> rebuild 3 GB on header informations. >>>>> >>>>> to be safe I just connect to mysql and do: >>>>> TRUNCATE `dbmail_header`; >>>>> TRUNCATE `dbmail_headername`; >>>>> TRUNCATE `dbmail_headervalue`; >>>> >>>> correct. >>>> >>>>> after that I make. >>>>> dbmail-util -ay >>>> >>>> dbmail-util -by >>>> >>>> is sufficient >>> >>> Hi >>> >>> i just let this run on a copy of our replication slave >>> foreign keys have to be disabled for the operation! >>> >>> a very long time with messages missing subjects >>> Mi 28. Mär 18:40:49 CEST 2012 >>> Mi 28. Mär 19:54:48 CEST 2012 >>> >>> i think an extra cli-switch for "dbmail-util" which does >>> this message for message would be a good idea. loop >>> phymessages, remove all header-records for this message >>> and re-create them, this way only one message without >>> subject would appear at the same time >>> ___________________________________________________________ >>> >>> BTW: >>> "dbmail_headervalue" is not cleaned up by "dbmail-util" >>> see the differnce between rebuilt snapshot to liveserver >>> after get rid around 20000 messages the last weeks >>> >>> mysql> select count(*) from dbmail_headervalue; >>> +----------+ >>> | count(*) | >>> +----------+ >>> | 519747 | >>> +----------+ >>> 1 row in set (0.16 sec) >>> >>> mysql> select count(*) from dbmail_headervalue; >>> +----------+ >>> | count(*) | >>> +----------+ >>> | 1603038 | >>> +----------+ >>> 1 row in set (1.80 sec) >>> ___________________________________________________________ >>> >>> SET FOREIGN_KEY_CHECKS=0; >>> TRUNCATE `dbmail_header`; >>> TRUNCATE `dbmail_headername`; >>> TRUNCATE `dbmail_headervalue`; >>> SET FOREIGN_KEY_CHECKS=1; >>> >>> dbmail-util -by
signature.asc
Description: OpenPGP digital signature
_______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
