[syslog-ng] mysql destinations - more options for table creation

[Scheidler, Balázs]

Hi,

I was thinking about a more generic approach, but this could solve your
problem:

https://github.com/balabit/syslog-ng/pull/1367

This creates a create-statement-append() option to the sql destination
where you can specify a string that gets appended to the CREATE SQL
statement.

If it indeed solves your problem, I'd welcome feedback on the #1367 github
issue. This is just a quick and dirty patch from me, without tests etc.


-- 
Bazsi

On Mon, Mar 6, 2017 at 7:58 PM, Nik Ambrosch <nik at ambrosch.com> wrote:

> 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 <(1)%20048%205923> 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:
>
> https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic.html
> https://signalvnoise.com/posts/3571-scaling-your-
> database-via-innodb-table-compression
>
>
>
>
> On Mar 6, 2017, at 1:46 PM, Fekete, Róbert <robert.fekete at balabit.com>
> wrote:
>
> Hi,
>
> syslog-ng can automatically create tables if you use macros in the table
> names.
> As for additional options, newer syslog-ng versions have a dbd-option()
> that can be used to set database options when syslog-ng connects the
> database (it might or might not be enough for your needs), see
> https://www.balabit.com/documents/syslog-ng-ose-
> latest-guides/en/syslog-ng-ose-guide-admin/html/
> reference-destination-sql.html
>
> HTH,
>
> Robert
>
>
> ____________________________________________________________
> __________________
> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
> Documentation: http://www.balabit.com/support/documentation/?
> product=syslog-ng
> FAQ: http://www.balabit.com/wiki/syslog-ng-faq
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20170307/0c060fe8/attachment-0001.html>