Openfire w/ Mysql Authentication issue

Openfire Openfire Support
1

I recently changed to using a MySQL db for authentication and I have noticed from my graphs that there are now lots of inneficient queries coming to the MySQL server from openfire. From packet trace, they seem to be opening a connection and doing this:

SET NAMES utf8

SET character_set_results = NULL

SHOW VARIABLES

SHOW COLLATION

SET autocommit=1

SET sql_mode=‘STRICT_TRANS_TABLES’

SELECT username,email FROM user WHERE user_id=?

Then closing the connection, and doing that very often. I’m guessing that the SHOW VARIABLES is the real slowdown, though I’m not sure. Is there a way to limit how often, or what type of scan openfire does? I could not see a setting in the config files.

2

Hey pquigley,

Are you using the JDBCAuthProvider as your AuthProvider? Do you see those queries when someone is authenticating? The plugin per se is not executing the SET or SHOW statements but it is possible that the internal DriverManager is creating them. What’s the value of your jdbcProvider.connectionString system property? Which JDBC driver are you using and what version of DB are you using?

Regards,

– Gato

3

Gato, thanks for your help. I’m using ths following settings:

<jdbcProvider>

<driver>com.mysql.jdbc.Driver</driver>

<connectionString>jdbc:mysql://jabber.mysite.com/core?useUnicode=true&amp ;characterEncoding=utf-8&user=my_user&password=mypass</connectionStri ng>

<mysql>

<useUnicode>true</useUnicode>

</mysql>

</jdbcProvider>

Version: Openfire 3.3.1

JVM Version and Vendor: 1.6.0 Sun Microsystems Inc. – Java HotSpot™ Server VM

Appserver: jetty-6.1.x

OS / Hardware: Linux / i386

4

Hey pquigley,

  1. Are you using the JDBCAuthProvider as your AuthProvider? You can check this info in your openfire.xml file. Look for provider.auth.

  2. Do you see those queries when someone is authenticating?

  3. What’s the value of your jdbcProvider.connectionString system property?

  4. Which JDBC driver are you using and what version of DB are you using?

Thanks,

– Gato

5
  1. Are you using the JDBCAuthProvider as your AuthProvider? You can

check this info in your openfire.xml file. Look for provider.auth.

I think this is what you mean?

<provider>

<auth>

<className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className& gt;

</auth>

<user>

<className>org.jivesoftware.openfire.user.JDBCUserProvider</className& gt;

</user>

</provider>

  1. I’m not sure if they are simultaneous with authentication.

  2. Do you mean this?

<defaultProvider>

<driver>com.mysql.jdbc.Driver</driver>

<serverURL>jdbc:mysql://localhost:3306/mydb</serverURL>

<username>myuser</username>

<password>mypass</password>

<minConnections>5</minConnections>

<maxConnections>15</maxConnections>

<connectionTimeout>1.0</connectionTimeout>

</defaultProvider>

  1. Which JDBC driver are you using and what version of DB are you using?

I’m not sure where to find the JDBC driver version

DB version: 5.0.37-community-log MySQL Community Edition