Logging to a Database

Richard_Lanham · September 9, 2020, 8:38pm

Great, glad we could help.

Richard

Richard_Lanham · September 9, 2020, 8:38pm

I tested it and it worked. Here is my access appender:

# Access appender
log4j.appender.serverAccess=org.apache.log4j.DailyRollingFileAppender
log4j.appender.serverAccess.DatePattern='.'yyyy-MM-dd
log4j.appender.serverAccess.File=${com.wowza.wms.ConfigHome}/logs/wowzamediaserver_access.log
log4j.appender.serverAccess.layout=com.wowza.wms.logging.ECLFPatternLayout
log4j.appender.serverAccess.layout.Fields=x-severity,x-category,x-event;date,time,c-client-id,c-ip,c-port,cs-bytes,sc-bytes,x-duration,x-sname,x-stream-id,x-spos,sc-stream-bytes,cs-stream-bytes,x-file-size,x-file-length,x-ctx,x-comment
log4j.appender.serverAccess.layout.OutputHeader=true
log4j.appender.serverAccess.layout.QuoteFields=false
log4j.appender.serverAccess.layout.Delimeter=tab
log4j.appender.serverAccess.layout.EventExclude=comment

Richard

Richard_Lanham · September 9, 2020, 8:38pm

That is right.

scstreambytes (sc-stream-bytes), or scbytes (sc-bytes) includes some overhead for the netconnection.

The destroy event is the cumulative number, you can ignore other data for a clientid.

And xapp (x-app) for a application is correct.

Richard_Lanham · September 9, 2020, 8:38pm

Make sure you have restarted Wowza since you added the jar file.

You either get data in the table or errors in the log. Even sql errors in the insert statement of the Appender are logged.

Richard

Richard_Lanham · September 9, 2020, 9:51pm

Are you following Attilah’s guide on page 2 of this thread:

http://www.wowza.com/community/t/-/236&page=2

There are some prerequisities what you must check before begin:

Check in the SQL Server Configuration tool that TCP/IP is enabled for the used SQL Server instance.

Check in the SQL Server configuration that SQL authentication is enabled.

Richard

Richard_Lanham · September 9, 2020, 8:38pm

Great! One down. Thanks for the update

Richard

Richard_Lanham · September 9, 2020, 8:39pm

You could use a stored procedure in the appender instead of simple insert statement and do whatever you want on the database side. And you can add indexing to the table(s) so that your queries are efficient. The details of that are particular to the dbms, and beyond the scope of Wowza support, but it’s standard database stuff.

Richard

Richard_Lanham · September 9, 2020, 8:39pm

If you take out the “SQ” it all works again?

Did you also add the actual appender?

When I add SQ to the header but do not have the SQ appender in place, I only get this error:

log4j:ERROR Could not find value for key log4j.appender.SQ

log4j:ERROR Could not instantiate appender named “SQ”.

So I think there is more wrong than just adding that header. You have to re-install.

Make sure you install as root.

Richard

Richard_Lanham · September 9, 2020, 8:39pm

Did you install Wowza as other than root? Sounds very messed up. If it is new install, you might want to start over. You can try to undo all your changes first and see if it is okay, then start again from there.

Richard

Lester_Fajardo · September 9, 2020, 8:45pm

Hello Roger.

This is also required when using DailyRollingFileAppender and text files ?

I have this settings

Access appender

log4j.appender.serverAccess=org.apache.log4j.DailyRollingFileAppender

#each hour

log4j.appender.serverAccess.DatePattern=’.'yyyy-MM-dd-HH

log4j.appender.serverAccess.File=${com.wowza.wms.ConfigHome}/logs/wowzamediaserverpro_access.log

log4j.appender.serverAccess.layout=com.wowza.wms.logging.ECLFPatternLayout

.

log4j.appender.serverAccess.layout.CategoryExclude=server

log4j.appender.serverAccess.layout.EventExclude=comment

after the service restart, the log file is filled with comment rows

2010-06-07 18:58:44 comment server ERROR 500 - loadModFunctions: java.lang.ClassNotFoundException: com.wowza.wms.plugin.mediacasterstreammanager.ModuleMediaCasterStreamManager - - - 1.797 - - - - - - - - - - - - - - - - - - - - - - - - -

Lester

To be able to use the CategoryInclude & EventExclude filters that are available with the ECLFPatternLayout, you need to extend org.apache.log4j.jdbc.JDBCAppender and write your own execute method.

The problem is that the filters return an empty string when the filter is applied and the default execute method throws an SQLException which is what you see when first starting the server. It is also coded to only show 1 exception so you only see 1 warning. When the exception is thrown, the log entry is not cleared from the buffer so when a valid entry is processed, an insert is done for each entry in the buffer resulting in the repeated lines. The following class fixes the problem.
package com.wowza.wms.plugin.logging;
import java.sql.SQLException;
import org.apache.log4j.jdbc.JDBCAppender;
public class WowzaJDBCAppender extends JDBCAppender {
	protected void execute(String sql) throws SQLException {
		if (sql.isEmpty()) {
			return;
		}
		StringBuffer sbSql = new StringBuffer();
		String[] bits = sql.split(",");
		for (int i = 0; i < bits.length; i++) {
			if (bits[i].trim().startsWith("-"))
				bits[i] = bits[i].replace("-", "null");
			 if (bits[i].trim().startsWith("'-'"))
				 bits[i] = bits[i].replace("'-'", "null");
			sbSql.append(bits[i]);
			if(i + 1 < bits.length)
				sbSql.append(",");
		}
		super.execute(sbSql.toString());
	}
}
The only bit it really needs is
		if (sql.isEmpty()) {
			return;
		}
		super.execute(sbSql.toString());
but I have also included code to replace the - with null when the field is empty so you can use proper types in your database table. It is a little bit messy but it does the job.

Richard, Charlie, Would it be possible to add a NullValueString to the ECLFPatternLayout so that we could define this in the log4j.properties file and do away with the extra bit of code above.

The modified appender based on the normal statistics appender is as follows. Make sure the appender class is com.wowza.wms.plugin.logging.WowzaJDBCAppender.
# Statistics database appender (to use this appender add "SQ" to the list of appenders in the first line of this file)
log4j.appender.SQ=com.wowza.wms.plugin.logging.WowzaJDBCAppender
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.layout.CategoryInclude=session,stream
log4j.appender.SQ.layout.EventExclude=comment
log4j.appender.SQ.sql=INSERT INTO statslog (xseverity, xcategory, xevent, date, time, xapp, cclientid, cip, cproto, csbytes, scbytes, xduration, xsname, xstreamid, xspos, scstreambytes, csstreambytes, xfilesize, xfilelength, xctx, xcomment) VALUES ('%X{x-severity}', '%X{x-category}', '%X{x-event}','%X{date}', '%X{time}', '%X{x-app}', %X{c-client-id}, '%X{c-ip}', '%X{c-proto}', %X{cs-bytes}, %X{sc-bytes}, %X{x-duration}, '%X{x-sname}', %X{x-stream-id}, %X{x-spos}, %X{sc-stream-bytes}, %X{cs-stream-bytes}, %X{x-file-size}, %X{x-file-length}, '%X{x-ctx}', '%X{x-comment}');
Note, I hafe removed the ’ ’ from around the numeric values. The sql for the table is
CREATE TABLE `statslog` (
  `logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `time` time DEFAULT NULL,
  `xevent` varchar(20) DEFAULT NULL,
  `xcategory` varchar(20) DEFAULT NULL,
  `xseverity` varchar(20) DEFAULT NULL,
  `xctx` varchar(255) DEFAULT NULL,
  `xcomment` varchar(255) DEFAULT NULL,
  `xapp` varchar(100) DEFAULT NULL,
  `xduration` double(11,3) DEFAULT NULL,
  `cip` varchar(100) DEFAULT NULL,
  `cproto` varchar(100) DEFAULT NULL,
  `cclientid` int(11) DEFAULT NULL,
  `csbytes` int(11) DEFAULT NULL,
  `scbytes` int(11) DEFAULT NULL,
  `xstreamid` int(11) DEFAULT NULL,
  `xspos` int(11) DEFAULT NULL,
  `csstreambytes` int(11) DEFAULT NULL,
  `scstreambytes` int(11) DEFAULT NULL,
  `xsname` varchar(100) DEFAULT NULL,
  `xfilesize` int(11) DEFAULT NULL,
  `xfilelength` double(11,3) DEFAULT NULL,
  PRIMARY KEY (`logid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
Hope this helps someone.

Roger

Paul_Molloy · September 9, 2020, 8:45pm

Is it possible to have set up odbc logging so that only requests to certain applications are logged to the database?

I’d like to have several similar applications on one server that log to different databases.

Ezequiel_Merino · September 9, 2020, 8:46pm

Thanks Charlie,

At the list appears the 1.4 version, I had changed:

[root@XXXXXXXXXXXXXX]# java -version

java version “1.6.0_20”

Java™ SE Runtime Environment (build 1.6.0_20-b02)

Java HotSpot™ 64-Bit Server VM (build 16.3-b01, mixed mode)

But the problem stil been the same, if I remove the “SQ” sentence and the rest of the code for connect to MySQL, any erroas has showed the the WoWzaMedia Server still not working.

[root@XXXXXXXXXXXXXX]# ./startup.sh

Configure logging: file:///usr/local/WowzaMediaServer/conf/log4j.properties

INFO server server-start Wowza Media Server 2 Perpetual 2.0.0 build22912 -

INFO server comment - Serial number: XXXXX-XXXXX-XXXXX-XXXXX-BBBBB

INFO server comment - Maximum connections: Unlimited

INFO server comment - Hardware Available Processors: 4

INFO server comment - Hardware Physical Memory: 1338MB/3953MB

INFO server comment - Hardware Swap Space: 5951MB/5951MB

INFO server comment - Max File Descriptor Count: 1024

INFO server comment - Open File Descriptor Count: 38

INFO server comment - OS Name: Linux

INFO server comment - OS Version: 2.6.18-164.15.1.el5

INFO server comment - OS Architecture: amd64

INFO server comment - Java Name: Java HotSpot™ 64-Bit Server VM

INFO server comment - Java Vendor: Sun Microsystems Inc.

INFO server comment - Java Version: 1.6.0_20

INFO server comment - Java VM Version: 16.3-b01

INFO server comment - Java Spec Version: 1.6

INFO server comment - Java Home: /usr/java/jdk1.6.0_20/jre

INFO server comment - Java Max Heap Size: 682MB

INFO server comment - Java Architecture: 64

INFO server comment - CMDInterface now listening: [any]:8083

INFO server comment - defaultVHost home directory: /usr/local/WowzaMediaServer

INFO vhost vhost-start defaultVHost -

INFO vhost comment defaultVHost Bind attempt ([any]:1935)

INFO vhost comment defaultVHost Bind successful ([any]:1935)

INFO vhost comment defaultVHost Bind attempt ([any]:8086)

INFO vhost comment defaultVHost Bind successful ([any]:8086)

The version recognized by the WoWza was correcct.

¿Any more idea?

Thanks

Ezequiel_Merino · September 9, 2020, 8:46pm

My problem to connect to WoWza was simply, the firewall doesnt have the port open.

But my problem to logging on the database continue, that’s the WoWza message:

Configure logging: file:///usr/local/WowzaMediaServer/conf/log4j.properties

log4j:ERROR Failed to load driver

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

at java.net.URLClassLoader$1.run(URLClassLoader.java:202)

at java.security.AccessController.doPrivileged(Native Method)

at java.net.URLClassLoader.findClass(URLClassLoader.java:190)

at java.lang.ClassLoader.loadClass(ClassLoader.java:307)

at java.lang.ClassLoader.loadClass(ClassLoader.java:248)

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:169)

at org.apache.log4j.jdbc.JDBCAppender.setDriver(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.apache.log4j.config.PropertySetter.setProperty(Unknown Source)

at org.apache.log4j.config.PropertySetter.setProperty(Unknown Source)

at org.apache.log4j.config.PropertySetter.setProperties(Unknown Source)

at org.apache.log4j.config.PropertySetter.setProperties(Unknown Source)

at org.apache.log4j.PropertyConfigurator.parseAppender(Unknown Source)

at org.apache.log4j.PropertyConfigurator.parseCategory(Unknown Source)

at org.apache.log4j.PropertyConfigurator.configureRootCategory(Unknown Source)

at org.apache.log4j.PropertyConfigurator.doConfigure(Unknown Source)

at org.apache.log4j.PropertyConfigurator.configure(Unknown Source)

at com.wowza.wms.logging.WMSLoggerFactory.initializeLogging(Unknown Source)

at com.wowza.wms.logging.WMSLoggerFactory.initializeLogging(Unknown Source)

at com.wowza.wms.server.Server.initLogging(Unknown Source)

at com.wowza.wms.server.Server.start(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at com.wowza.wms.bootstrap.Bootstrap.startServer(Bootstrap.java:248)

at com.wowza.wms.bootstrap.Bootstrap.main(Bootstrap.java:47)

INFO server server-start Wowza Media Server 2 Perpetual 2.0.0 build22912 -

INFO server comment - Serial number: XXXXX-XXXXX-XXXXX-XXXXX-HHEHG

INFO server comment - Maximum connections: Unlimited

INFO server comment - Hardware Available Processors: 4

INFO server comment - Hardware Physical Memory: 3360MB/3953MB

INFO server comment - Hardware Swap Space: 5951MB/5951MB

INFO server comment - Max File Descriptor Count: 1024

INFO server comment - Open File Descriptor Count: 38

INFO server comment - OS Name: Linux

INFO server comment - OS Version: 2.6.18-164.15.1.el5

INFO server comment - OS Architecture: amd64

INFO server comment - Java Name: Java HotSpot™ 64-Bit Server VM

INFO server comment - Java Vendor: Sun Microsystems Inc.

INFO server comment - Java Version: 1.6.0_20

INFO server comment - Java VM Version: 16.3-b01

INFO server comment - Java Spec Version: 1.6

INFO server comment - Java Home: /usr/java/jdk1.6.0_20/jre

INFO server comment - Java Max Heap Size: 682MB

INFO server comment - Java Architecture: 64

INFO server comment - CMDInterface now listening: [any]:8083

INFO server comment - defaultVHost home directory: /usr/local/WowzaMediaServer

INFO vhost vhost-start defaultVHost -

INFO vhost comment defaultVHost Bind attempt ([any]:1935)

INFO vhost comment defaultVHost Bind successful ([any]:1935)

INFO vhost comment defaultVHost Bind attempt ([any]:8086)

INFO vhost comment defaultVHost Bind successful ([any]:8086)

I can connect and usit, but any record where writing on mysql database

Ezequiel_Merino · September 9, 2020, 8:46pm

I had downloaded yet the mysql JDBC jar file and copy on lib folder, I do it again and restart all the system and demons, but continue with the same problem.

Configure logging: file:///usr/local/WowzaMediaServer/conf/log4j.properties

log4j:ERROR Failed to load driver

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

at java.net.URLClassLoader$1.run(URLClassLoader.java:202)

at java.security.AccessController.doPrivileged(Native Method)

at java.net.URLClassLoader.findClass(URLClassLoader.java:190)

at java.lang.ClassLoader.loadClass(ClassLoader.java:307)

at java.lang.ClassLoader.loadClass(ClassLoader.java:248)

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:169)

at org.apache.log4j.jdbc.JDBCAppender.setDriver(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.apache.log4j.config.PropertySetter.setProperty(Unknown Source)

at org.apache.log4j.config.PropertySetter.setProperty(Unknown Source)

at org.apache.log4j.config.PropertySetter.setProperties(Unknown Source)

at org.apache.log4j.config.PropertySetter.setProperties(Unknown Source)

at org.apache.log4j.PropertyConfigurator.parseAppender(Unknown Source)

at org.apache.log4j.PropertyConfigurator.parseCategory(Unknown Source)

at org.apache.log4j.PropertyConfigurator.configureRootCategory(Unknown Source)

at org.apache.log4j.PropertyConfigurator.doConfigure(Unknown Source)

at org.apache.log4j.PropertyConfigurator.configure(Unknown Source)

at com.wowza.wms.logging.WMSLoggerFactory.initializeLogging(Unknown Source)

at com.wowza.wms.logging.WMSLoggerFactory.initializeLogging(Unknown Source)

at com.wowza.wms.server.Server.initLogging(Unknown Source)

at com.wowza.wms.server.Server.start(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at com.wowza.wms.bootstrap.Bootstrap.startServer(Bootstrap.java:248)

at com.wowza.wms.bootstrap.Bootstrap.main(Bootstrap.java:47)

INFO server server-start Wowza Media Server 2 Perpetual 2.0.0 build22912 -

INFO server comment - Serial number: XXXXX-XXXXX-XXXXX-XXXXX-HHEHG

INFO server comment - Maximum connections: Unlimited

INFO server comment - Hardware Available Processors: 4

INFO server comment - Hardware Physical Memory: 3370MB/3953MB

INFO server comment - Hardware Swap Space: 5951MB/5951MB

INFO server comment - Max File Descriptor Count: 1024

INFO server comment - Open File Descriptor Count: 38

INFO server comment - OS Name: Linux

INFO server comment - OS Version: 2.6.18-164.15.1.el5

INFO server comment - OS Architecture: amd64

INFO server comment - Java Name: Java HotSpot™ 64-Bit Server VM

INFO server comment - Java Vendor: Sun Microsystems Inc.

INFO server comment - Java Version: 1.6.0_20

INFO server comment - Java VM Version: 16.3-b01

INFO server comment - Java Spec Version: 1.6

INFO server comment - Java Home: /usr/java/jdk1.6.0_20/jre

INFO server comment - Java Max Heap Size: 682MB

INFO server comment - Java Architecture: 64

INFO server comment - CMDInterface now listening: [any]:8083

INFO server comment - defaultVHost home directory: /usr/local/WowzaMediaServer

INFO vhost vhost-start defaultVHost -

INFO vhost comment defaultVHost Bind attempt ([any]:1935)

INFO vhost comment defaultVHost Bind successful ([any]:1935)

INFO vhost comment defaultVHost Bind attempt ([any]:8086)

INFO vhost comment defaultVHost Bind successful ([any]:8086)

Help.

Thanks

Ezequiel_Merino · September 9, 2020, 8:46pm

I tried to.

Nothing new.

In MySQL Forums I found the

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

error and I try all the posibles solutions I found, copy the jdbc connector on java/lib/ext, modify the $CLASSPATH, add the file path to etc/profile, but nothing works…

I still trying, thanks for help.

Ezequiel_Merino · September 9, 2020, 8:46pm

Hi,

My problem with the connector where’s on the download method, if i download from a ftp o directly form the MySQL page, the file download corrupt.

I used directly that link: http://laketk.com/mysql-connector-java-5.1.8-bin.jar

and giving wraiths to execute the file “chmod 777 file”, works perfectly.

Thanks

Alejandro_Ferrari · September 9, 2020, 8:46pm

kalin,

Good Tip, thanks for share!

Alejandro

Andy_Wunderlich · September 9, 2020, 8:46pm

It works greatful. Is there a way to add own column with user data from swf file about metainformation?

Thank you, freddy

Karim_SLIMANI · September 9, 2020, 8:48pm

hi, i am testing database logging,

i have some problems when using the CategoryInclude, CategoryExclude, EventInclude or EventExclude parameters : it add multiple time the same line in database.

for exemple, if i add these two lines :

log4j.appender.SQ.layout.CategoryInclude=stream

log4j.appender.SQ.layout.EventInclude=destroy

It work, but it log multiple time the same line in database, i don’t understand why.

Also, i have the following error when i start the server :

log4j:ERROR Failed to excute sql

java.sql.SQLException: Can not issue empty query.

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1072)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:986)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:981)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)

at com.mysql.jdbc.StatementImpl.checkNullOrEmptyQuery(StatementImpl.java:461)

at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1585)

at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1566)

at org.apache.log4j.jdbc.JDBCAppender.execute(Unknown Source)

at org.apache.log4j.jdbc.JDBCAppender.flushBuffer(Unknown Source)

at org.apache.log4j.jdbc.JDBCAppender.append(Unknown Source)

at org.apache.log4j.AppenderSkeleton.doAppend(Unknown Source)

at org.apache.log4j.helpers.AppenderAttachableImpl.appendLoopOnAppenders(Unknown Source)

at org.apache.log4j.Category.callAppenders(Unknown Source)

at org.apache.log4j.Category.forcedLog(Unknown Source)

at org.apache.log4j.Category.log(Unknown Source)

at com.wowza.wms.logging.WMSLogger.log(Unknown Source)

at com.wowza.wms.logging.WMSLogger.info(Unknown Source)

at com.wowza.wms.server.Server.startServer(Unknown Source)

at com.wowza.wms.server.Server.start(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at com.wowza.wms.bootstrap.Bootstrap.startServer(Bootstrap.java:248)

at com.wowza.wms.bootstrap.Bootstrap.main(Bootstrap.java:47)

if i comment the lines :

log4j.appender.SQ.layout.CategoryInclude=stream

log4j.appender.SQ.layout.EventInclude=destroy

everything is working perfectly, but it log all event and categories.

Attila_Hajdrik · September 9, 2020, 8:48pm

Hi Richard!

Could you please post the instructions for MS SQL Server?

I see that now MS released a 3.0 CTP of their JDBC driver which I’d like to use for logging.

Thanks,

Attila

Wowza Community

Logging to a Database

Access appender

Popular Video Topics

Video Resources

Partners

Company

Stay Connected

Stay Up to Date with the Blog