Archive for November, 2012

Exim: Clearing the internal eximstats database (cPanel)

Clearing the eximstats database isn’t something difficult to do via phpMyAdmin or a similar web-based database management application, and shouldn’t harm anything in your system.
However, if you run into any issues, or just want to re-initialize the database and re-create the tables structures manually via SSH, here’s what you should do:

mysqladmin drop eximstats ; mysqladmin create eximstats ; mysql eximstats < /usr/local/cpanel/etc/eximstats_db.sql


If for any reason you do not have your default eximstats .sql file (which includes the database structure), you can copy it from the block below.
Just make sure you save it as /usr/local/cpanel/etc/eximstats_db.sql (pay close attention to the path).

— Host: localhost Database: eximstats
— ——————————————————

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’+00:00′ */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;


— Table structure for table `defers`

DROP TABLE IF EXISTS `defers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `defers` (
`mailtime` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
`msgid` char(16) NOT NULL DEFAULT ”,
`email` char(255) NOT NULL DEFAULT ”,
`transport_method` char(45) DEFAULT ‘remote_smtp’,
`host` char(240) DEFAULT NULL,
`ip` char(60) DEFAULT NULL,
`message` char(240) DEFAULT NULL,
`router` char(65) DEFAULT ”,
`deliveryuser` char(30) NOT NULL DEFAULT ”,
`deliverydomain` char(255) NOT NULL DEFAULT ”,
PRIMARY KEY (`mailtime`,`msgid`,`email`),
KEY `email_mailtime_index` (`email`,`mailtime`),
KEY `msgid_mailtime_index` (`msgid`,`mailtime`),
KEY `deliverydomain_mailtime_index` (`deliverydomain`,`mailtime`),
KEY `deliveryuser_mailtime_index` (`deliveryuser`,`mailtime`),
KEY `email_deliveryuser_mailtime_index` (`email`,`deliveryuser`,`mailtime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


— Table structure for table `failures`

DROP TABLE IF EXISTS `failures`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `failures` (
`mailtime` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
`msgid` char(16) NOT NULL DEFAULT ”,
`email` char(255) NOT NULL DEFAULT ”,
`transport_method` char(45) DEFAULT ‘remote_smtp’,
`host` char(240) DEFAULT NULL,
`ip` char(60) DEFAULT NULL,
`message` char(240) DEFAULT NULL,
`router` char(65) DEFAULT ”,
`deliveryuser` char(30) NOT NULL DEFAULT ”,
`deliverydomain` char(255) NOT NULL DEFAULT ”,
PRIMARY KEY (`mailtime`,`msgid`,`email`),
KEY `email_mailtime_index` (`email`,`mailtime`),
KEY `deliverydomain_mailtime_index` (`deliverydomain`,`mailtime`),
KEY `deliveryuser_mailtime_index` (`deliveryuser`,`mailtime`),
KEY `email_deliveryuser_mailtime_index` (`email`,`deliveryuser`,`mailtime`),
KEY `msgid_mailtime_index` (`msgid`,`mailtime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


— Table structure for table `sends`

DROP TABLE IF EXISTS `sends`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sends` (
`mailtime` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
`msgid` char(16) NOT NULL DEFAULT ”,
`email` char(255) NOT NULL DEFAULT ”,
`processed` int(11) DEFAULT NULL,
`user` char(30) NOT NULL DEFAULT ”,
`size` int(11) DEFAULT NULL,
`ip` char(60) DEFAULT NULL,
`auth` char(30) DEFAULT NULL,
`host` char(240) DEFAULT NULL,
`domain` char(255) NOT NULL DEFAULT ”,
`localsender` int(1) DEFAULT ‘1’,
`spamscore` double DEFAULT NULL,
PRIMARY KEY (`mailtime`,`msgid`,`email`),
KEY `mailtime_domain_user_msgid_index` (`mailtime`,`domain`,`user`,`msgid`),
KEY `user_mailtime_index` (`user`,`mailtime`),
KEY `msgid_user_index` (`msgid`,`user`),
KEY `domain_user_mailtime_index` (`domain`,`user`,`mailtime`),
KEY `email_mailtime_user_index` (`email`,`mailtime`,`user`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


— Table structure for table `smtp`

DROP TABLE IF EXISTS `smtp`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `smtp` (
`mailtime` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
`msgid` char(16) NOT NULL DEFAULT ”,
`email` char(255) NOT NULL DEFAULT ”,
`processed` int(2) NOT NULL DEFAULT ‘0’,
`transport_method` char(45) DEFAULT ‘remote_smtp’,
`transport_is_remote` int(1) NOT NULL DEFAULT ‘1’,
`host` char(240) DEFAULT NULL,
`ip` char(60) DEFAULT NULL,
`deliveredto` char(255) NOT NULL DEFAULT ”,
`router` char(65) NOT NULL DEFAULT ”,
`deliveryuser` char(30) NOT NULL DEFAULT ”,
`deliverydomain` char(255) NOT NULL DEFAULT ”,
`counteddomain` char(255) DEFAULT NULL,
`countedtime` datetime DEFAULT NULL,
`countedhour` datetime DEFAULT NULL,
PRIMARY KEY (`mailtime`,`msgid`,`email`,`deliveredto`,`router`),
KEY `msgid_index` (`msgid`),
KEY `deliverydomain_mailtime_index` (`deliverydomain`,`mailtime`),
KEY `deliveryuser_mailtime_index` (`deliveryuser`,`mailtime`),
KEY `email_mailtime_index` (`email`,`mailtime`),
KEY `email_deliveryuser_mailtime_index` (`email`,`deliveryuser`,`mailtime`),
KEY `processed_transport_is_remote_index` (`processed`,`transport_is_remote`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET [email protected]_TIME_ZONE */;

/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;

That’s about it. 🙂