Table name (macros) are not the issue, here is how I’m currently creating a table:
destination mysql {
sql(type(mysql)
host("localhost") username("syslog") password("xxx")
database("syslog")
table("syslog_${R_YEAR}${R_MONTH}${R_DAY}")
columns("id int(11) unsigned not null auto_increment primary key", "host varchar(40) not null", "facility varchar(20)", "priority varchar(10)", "level varchar(10)", "program text", "date date not null", "time time not null", "message text not null")
values("", "$FULLHOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$PROGRAM", "$R_YEAR-$R_MONTH-$R_DAY", "$R_HOUR:$R_MIN:$R_SEC", "$MSG")
indexes("id","host","priority"));
};
This creates a table like this:
syslog_20170306 | CREATE TABLE `syslog_20170306` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`host` varchar(40) NOT NULL,
`facility` varchar(20) DEFAULT NULL,
`priority` varchar(10) DEFAULT NULL,
`level` varchar(10) DEFAULT NULL,
`program` text,
`date` date NOT NULL,
`time` time NOT NULL,
`message` text NOT NULL,
PRIMARY KEY (`id`),
KEY `syslog_20170306_id_idx` (`id`),
KEY `syslog_20170306_host_idx` (`host`),
KEY `syslog_20170306_priority_idx` (`priority`)
) ENGINE=MyISAM AUTO_INCREMENT=10485923 DEFAULT CHARSET=latin1
That is the output from my old syslog server, the new will be using InnoDB tables, not MyISAM, as listed. If you notice the last parameters, starting with “ENGINE” - that is where the additional parameters can be added. in a compressed table you would append a ROW_FORMAT variable. Here is a little information if you’re curious: