Large vlog table on vpopmail database

Update:

If you see: vchkpw: can’t write SQL logs in message log, then maybe you got same problem like me.

In these few weeks, sometimes our clients experience rejection when trying to connect via pop3 with their mail client. It’s about vpopmail+mysql can’t give the answer to password question so pop3 authentication become failed. Today the problem happen again but with another problem vlog table corrupted. Vlog table on vpopmail which log user pop3 authentication size is about 6Gbs when we check it, i don’t know if its large enough to become for a single mysql table.

We tried to repair but it’s look like to take much times, so we choose to drop the table and create the new one. Fortunately there is a command how to create vlog tables from vmysql.h file (vpopmail source), i copied here:

#define VLOG_TABLE_LAYOUT “id BIGINT PRIMARY KEY AUTO_INCREMENT, \
user char(32), passwd CHAR(32), \
domain CHAR(96), logon VARCHAR(200), \
remoteip char(18), message VARCHAR(255), \
timestamp bigint default 0 NOT NULL, error INT, \
INDEX user_idx (user), \
INDEX domain_idx (domain), INDEX remoteip_idx (remoteip), \
INDEX error_idx (error), INDEX message_idx (message)”
#endif

So, after drop vlog tables then just create again.

Now we need to figure out how to clear some entrys from vlog table so it doesn’t become large uncontrolled (again).