MySQL create table script for SQLLogging using mysql 5.5

[Scott B. Anderson]

It took me some time to figure out why the default scripts for creating the tables for @SQLLogging was failing on ERROR 1064 when trying to source the SQL script.

Eventually I learned that since MySQL 4.1, TIMESTAMP type ignores the (14) size declaration, but starting with 5.5, it will throw an error and fail.

Also, TYPE=MyISAM is not compatible anymore, so you will need to replace it with ENGINE=InnoDB  to get the script to work.
After these changes, the three table creation definitions that will work with MySQL 5.5 and up look like this:

CREATE TABLE maillog_mail (
  id int(10) unsigned NOT NULL auto_increment,
  time timestamp NOT NULL,
  msg_id varchar(15) binary default NULL,
  size bigint(20) default NULL,
  from_user varchar(35) default NULL,

  from_domain varchar(35) default NULL,

  subject text,

  clientip text,

  archives text,
  isspam int(1) default NULL,
  ishighspam int(1) default NULL,
  sascore decimal(7,2) default NULL,
  spamreport text,
  PRIMARY KEY  (id),
  KEY msg_id (msg_id),
  KEY from_domain (from_domain,from_user)
) ENGINE=InnoDB;

CREATE TABLE maillog_recipient (
  id bigint(20) NOT NULL auto_increment,
  msg_id varchar(15) binary default NULL,
  to_user varchar(35) default NULL,
  to_domain varchar(35) default NULL,
  PRIMARY KEY  (id),
  KEY msg_id (msg_id),
  KEY to_domain (to_domain,to_user)
) ENGINE=InnoDB;


CREATE TABLE maillog_report (
  id bigint(20) NOT NULL auto_increment,
  msg_id varchar(15) binary default NULL,
  filename text,
  filereport text,
  PRIMARY KEY  (id),
  KEY msg_id (msg_id)
) ENGINE=InnoDB;




I mostly posted this to help out the next person that upgrades from an old server running MySQL 3.x to one running 5.x

If someone would like to verify, critique, or comment, that would be great as well.  The FAQs and READMEs should probably get updated as well.


Scott Anderson
IT Administrator

...

-- 
ImproMed LLC
--