Unable to connect to mysql

Openfire Openfire Support
1

Hi

I have installed openfire on debian production system and it was working fine. Once I moved to Live server and am installing openfire, I am not getting over **Database Settings - Standard Connection. **I am not able to connect to database. My database is in mysql. I get error

A connection to the database could not be made. View the error message by opening the “/logs/error.log” log file, then go back to fix the problem.

On seeing the error log I get

2008.10.14 10:02:28 [org.jivesoftware.util.log.util.CommonsLogFactory$1.error(CommonsLogFactory.jav a:92)] Prototype
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException
MESSAGE: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

STACKTRACE:

java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:573)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1044)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)
at com.mysql.jdbc.Connection.(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:154)
at org.logicalcobwebs.proxool.DefaultConnectionBuilder.buildConnection(DefaultConn ectionBuilder.java:39)
at org.logicalcobwebs.proxool.Prototyper.buildConnection(Prototyper.java:159)
at org.logicalcobwebs.proxool.Prototyper.sweep(Prototyper.java:102)
at org.logicalcobwebs.proxool.PrototyperThread.run(PrototyperThread.java:44)

** END NESTED EXCEPTION **

Last packet sent to the server was 3 ms ago.

STACKTRACE:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException
MESSAGE: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

STACKTRACE:

Any help on this would be helpful…

Thanks

Philip

2

Seems to be an issue with your mysql server setup. There’s quite some discussion on this subject to be found on the mysql forums. Have a look at this thread, for example: http://forums.mysql.com/read.php?39,180347

3

Here is my.cnf

[mysqld]

* Basic Settings

user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
#datadir = /var/lib/mysql
datadir = /home/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking

bind-address = 127.0.0.1

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10

* Query Cache Configuration

query_cache_limit = 1M
query_cache_size = 16M

log = /home/logs/mysql.log

log_bin = /var/log/mysql/mysql-bin.log

WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!

expire_logs_days = 10
max_binlog_size = 100M

* BerkeleyDB

Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.

skip-bdb

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

One more thing

Even if I change the mysql credintials(username, password) or supply wrong username/password, I don’t get any change in the error log. So might be mysql is not the culprit.

Any help would be greatful. The forum was helpful but didn’t solve my problem…

4

I had similar problems using MS SQL 2008 Express. It just would not connect. I could connect to the database, but the Openfire could not. Not sure why. Someone told me it was a permissions issue but I couldn’t figure out where. So I just used the embedded database.

5

Firstly, what OS are you running on your DB server - Linux, Windows ?

If either of those, is the firewall allowing connections in on the correct TCP port?

Also, have you configured MySQL to allow connections in for that user from a remote machine ?

6

Have you created the database to house the openfire tables in MySQL? Did you create user specifically for the openfire dabase and grant that user full rights? Is the MySQL server on the same machine as the openfire server? If not have you allowed access to the database for openfire from remote machines?

7

I am running Linux as the OS. The firewall allowing commection on correct TCP port. Yes even I tried MySQL to allow connections in for that user from a remote machine and was successful, but openfire was not able to connect to mysql even when both reside on same machine…

8

I have created database to house openfire tables in MYSQL. I have given full rights to the user for the database . Also both openfire and mysql are on same machine. even I tried to connect to this system from remote machine and was successful.

9

I assume you have allowed Openfire to connect to MySQL by allowing the ID you’re using (for example jive) to connect to the database by issuing the grant statement within MySQL, for example:

cd /usr/bin (this is example for CentOS)

mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1292 to server version: 5.0.22

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>grant all to jive on ‘<db name, openfire>’ identified by ‘@’%’;

‘%’ means any IP but you can set that as loopback or host IP of the machine - please note you should check the syntax as it may not be 100% correct, note semi colon at the end.

10

what is the connection string you are using from the openfire config.