• How to log to a mySQL database

    This article describes how to set up logging to a MySQL database using the Apache log4j logging utility.

    Note: Database logging is provided by the log4j open source logging system, which Wowza media server software uses for its logging framework. There have been reports of issues with database logging when the connection to the database is lost. The log4j system will not automatically try to reconnect to the database server.

    Note: For information about PostgreSQL logging, see How to log to a PostgreSQL database.

    First download the MySQL JDBC Connector, then open the ZIP file and extract the file mysql-connector-java-5.1.8-bin.jar file to the Wowza media server software installation folder [install-dir]/lib.

    In MySQL, create a catalog named "wowzalogs". Then create the following table to store all of the Wowza log fields. (Table attributes have the same name as log4j fields but without hyphens.)
    CREATE TABLE  `wowzalogs`.`accesslog` (
      `logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `date` varchar(100) DEFAULT NULL,
      `time` varchar(100) DEFAULT NULL,
      `tz` varchar(100) DEFAULT NULL,
      `xevent` varchar(20) DEFAULT NULL,
      `xcategory` varchar(20) DEFAULT NULL,
      `xseverity` varchar(100) DEFAULT NULL,
      `xstatus` varchar(100) DEFAULT NULL,
      `xctx` varchar(100) DEFAULT NULL,
      `xcomment` varchar(255) DEFAULT NULL,
      `xvhost` varchar(100) DEFAULT NULL,
      `xapp` varchar(100) DEFAULT NULL,
      `xappinst` varchar(100) DEFAULT NULL,
      `xduration` varchar(100) DEFAULT NULL,
      `sip` varchar(100) DEFAULT NULL,
      `sport` varchar(100) DEFAULT NULL,
      `suri` varchar(255) DEFAULT NULL,
      `cip` varchar(100) DEFAULT NULL,
      `cproto` varchar(100) DEFAULT NULL,
      `creferrer` varchar(255) DEFAULT NULL,
      `cuseragent` varchar(100) DEFAULT NULL,
      `cclientid` varchar(25) DEFAULT NULL,
      `csbytes` varchar(20) DEFAULT NULL,
      `scbytes` varchar(20) DEFAULT NULL,
      `xstreamid` varchar(20) DEFAULT NULL,
      `xspos` varchar(20) DEFAULT NULL,
      `csstreambytes` varchar(20) DEFAULT NULL,
      `scstreambytes` varchar(20) DEFAULT NULL,
      `xsname` varchar(100) DEFAULT NULL,
      `xsnamequery` varchar(100) DEFAULT NULL,
      `xfilename` varchar(100) DEFAULT NULL,
      `xfileext` varchar(100) DEFAULT NULL,
      `xfilesize` varchar(100) DEFAULT NULL,
      `xfilelength` varchar(100) DEFAULT NULL,
      `xsuri` varchar(255) DEFAULT NULL,
      `xsuristem` varchar(255) DEFAULT NULL,
      `xsuriquery` varchar(255) DEFAULT NULL,
      `csuristem` varchar(255) DEFAULT NULL,
      `csuriquery` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`logid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
    Open the the file [install-dir]/conf/log4j.properties in a text editor. In the first line, add a new root category "SQ":
    log4j.rootCategory=INFO, stdout, serverAccess, serverError, SQ
    In the same file, copy in the following appender. You will have to modify the password at least, and possibly the URL for the location of the database, and the user as necessary:
    log4j.appender.SQ=org.apache.log4j.jdbc.JDBCAppender
    log4j.appender.SQ.Driver=com.mysql.jdbc.Driver
    log4j.appender.SQ.URL=jdbc:mysql://localhost:3306/wowzalogs
    log4j.appender.SQ.user=root
    log4j.appender.SQ.password=CHANGEME
    log4j.appender.SQ.layout=com.wowza.wms.logging.ECLFPatternLayout
    log4j.appender.SQ.layout.OutputHeader=false
    log4j.appender.SQ.sql=INSERT INTO accesslog (date, time, tz, xevent, xcategory, xseverity,xstatus,xctx,xcomment,xvhost,xapp,xappinst,xduration,sip,sport,suri,cip,cproto,creferrer,cuseragent,cclientid,csbytes,scbytes,xstreamid, xspos,csstreambytes,scstreambytes,xsname,xsnamequery,xfilename,xfileext,xfilesize,xfilelength,xsuri,xsuristem,xsuriquery,csuristem,csuriquery) VALUES ('%X{date}', '%X{time}', '%X{tz}', '%X{x-event}', '%X{x-category}', '%X{x-severity}', '%X{x-status}', '%X{x-ctx}', '%X{x-comment}', '%X{x-vhost}', '%X{x-app}', '%X{x-appinst}', '%X{x-duration}', '%X{s-ip}', '%X{sport}', '%X{s-uri}', '%X{c-ip}', '%X{c-proto}', '%X{c-referrer}', '%X{c-user-agent}', '%X{c-client-id}', '%X{cs-bytes}', '%X{scbytes}', '%X{x-stream-id}', '%X{x-spos}','%X{cs-stream-bytes}', '%X{sc-stream-bytes}', '%X{x-sname}', '%X{x-sname-query}', '%X{x-file-name}', '%X{x-file-ext}', '%X{x-file-size}', '%X{x-file-length}','%X{x-suri}','%X{x-suri-stem}', '%X{x-suri-query}', '%X{c-suri-stem}', '%X{c-suri-query}');
    Then restart the Wowza media server and check the table:
    select * from accesslog;
    Note: You should start the Wowza media server in the console or IDE to see any errors that occur.

    Note: Some fields might need to be longer to accommodate all possible values.

    If you're having problems or want to discuss this article, post in our forum.