Unable to connect to Mysql database with jdbc

ronald_odero · October 10, 2007, 12:34pm

Hi all,

Im just new to openfire and in the configuration stage where I’m stuck with the database settings. I would really love to use MySql but I’m unable to connect with jdbc. This is what I have as the database URL:

dbc:mysql://localhost:3306/openfire

I s there anything wrong with my syntax or is there something I should tweak?

I’m using Ubuntu 7.04, ip 192.168.1.12.

Juan_Carlos_Garrido · October 10, 2007, 1:20pm

Hi otis.

I think it should be something like " jdbc:mysql://localhost:3306/openfire" (notice that you missed a ‘j’).

ronald_odero · October 11, 2007, 4:01am

juancarlos,

Well, that must have been a typo when I was posting this message. I still actually can’t establish a connection to MySQL. Please help!!!

Gaston_Dombiak · October 11, 2007, 7:27am

Hey Ronald,

Have you checked the log files of the server? You can also try executing telnet localhost 3306 to see if MySQL is actually running and listening on that port.

Regards,

– Gato

Juan_Carlos_Garrido · October 11, 2007, 7:37am

You can check the whole database configuration section in your openfire.xml file.

As an example, I’ll post mine:

ronald_odero · October 11, 2007, 8:49am

Gato,

The error log file states: could not start admin console server

java.net.BindException: Address already in use…

If I try telnet, this is what I get: Unable to connect to remote host: Connection refused

After that, I wnet straight into inserting explicit privileges into the host table of mysql ( i mean granting all manner of access) but still in vain. Do you have any more ideas?

Cheers,

Ronald.

ronald_odero · October 11, 2007, 8:53am

juancarlos,

I have exactly the same configurations except for the ip adress part and I’m using openfire version 3_3_2. But still nothing works. So I’m going to do a whole reinstallation again to see what happens.

Juan_Carlos_Garrido · October 11, 2007, 8:56am

Hi Otis.

If telnet fails, the problem is at mysql side.

Make sure that your mysqld server was not started with the --skip-networking option. If it was, you cannot connect to it using TCP/IP.

Here you can find more info about that:

http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html

Best regards.

Gaston_Dombiak · October 11, 2007, 4:13pm

Hey Ronald,

Installing the server again will not help you with this problem. What you are seeing is a connectivity problem to the DB that affects ANY application that wants to connect to your DB (at least from the same machine where Openfire is running). As Juan Carlos said in his last post you can check how you started your DB and how its configured. Until the telnet command that I mentioned works you won’t be able to connect to the DB.

Regards,

– Gato

ronald_odero · October 12, 2007, 10:23am

hi dombiak_gaston,

I managed to get telnet working and this is its output:

connected to localhost.

Escape character is ‘^]’.

5.0.38-Ubuntu…]:ICConnection closed by foreignhost.

But if I go back to the configuratin it’s the same old story of unable to connect.

Any more ideas?

Juan_Carlos_Garrido · October 12, 2007, 3:51pm

Hi.

Does the error “could not start admin console server java.net.BindException: Address already in use” appear still?

If it does, there could be another process listening at the same ports that Openfire does. By default, Openfire admin console server listens at port 9090. You can find if there is a process listening at that port using the command lsof -i :9090

Best regards.

ronald_odero · October 13, 2007, 12:38pm

Juancarlos,

I have stayed all night but still unseccessfull, anyway, I’m not going to give up:)

This is how my openfire error.log file looks like;

2007.10.13 15:15:37 org.jivesoftware.database.ConnectionPool.(ConnectionPool.java:111) Failed to create new connections on startup. Attempt 0 of 3

java.sql.SQLException: Access denied for user ‘’@‘localhost’ (using password: NO)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798)

at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3700)

at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1203)

at com.mysql.jdbc.Connection.createNewIO(Connection.java:2568)

at com.mysql.jdbc.Connection.<init>(Connection.java:1485)

at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)

at java.sql.DriverManager.getConnection(DriverManager.java:582)

at java.sql.DriverManager.getConnection(DriverManager.java:185)

at org.jivesoftware.database.ConnectionPool.createCon(ConnectionPool.java:443)

at org.jivesoftware.database.ConnectionPool.<init>(ConnectionPool.java:88)

at org.jivesoftware.database.DefaultConnectionProvider.start(DefaultConnectionProv ider.java:83)

at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectio nManager.java:459)…

Are you able to find out the problem you guide me? I believe the problem is on the MySQL side.I’m really desparate to get this thing up.

ronald_odero · October 13, 2007, 1:05pm

juancarlos,

Addionally, I have reinstalled mysql server and then left the root password blank. If I try to restart with /etc/init.d/mysql restart as regular user, it fails and indicates open: permission denied.

If I try as root, everything is ok.

Then if run netstat -tap, there is a line that indicates;

Proto Recv-Q Send-Q Local Address Foreign Address State PID/

tcp 0 0 softgenesis.local:mysql : LISTEN -

If I try telnet localhost 3306, this is what I get;

Trying 127.0.0.1…

telnet: Unable to connect to remote host: Connection refused

My ip address is :192.168.1.15 .

Please help.

Cheers.

Juan_Carlos_Garrido · October 13, 2007, 4:52pm

Hi Otis.

Looking at the logs I see that you’ve left your mysql username and password blank in openfire.xml:

java.sql.SQLException: Access denied for user ‘’@‘localhost’ (using password: NO)

denotes that there is no username and password configuration in openfire.xml.

Juan_Carlos_Garrido · October 13, 2007, 4:55pm

Hi.

It’s normal that you can start/stop mysql only as root user, but you must comfigure mysql the same way you did before to get telnet localhost 3306 connecting.

Otherwise, openfire won’t be able to connect.

Best regards.

ronald_odero · October 14, 2007, 9:42am

Juancarlos,

After much trial and errors I finally made it. Actually it was a matter of commenting out the bind address in the my.cnf file. Anyway, it has been a great learning experience for me.

Thanks to all those who read this, those who took time to reply and to even those who sympathized:)

Cheers.

Ronald.

Juan_Carlos_Garrido · October 15, 2007, 8:09am

Hi Ronald.

I’m glad of help you to manage that. You are always welcome.

Best regards,

Juan Carlos

Sunil_Shrestha · November 13, 2007, 10:51am

Hi Juancarlos,[~15861]

my mysql server is running. I am using CentOS 5. My mysql version is mysql-5.0.22-2.1. I am using openfire-3.4.1-1.

In Database Settings - Standard Connection page i gave as:

Database Driver Presets: Mysql

JDBC Driver Class: com.mysql.jdbc.Driver

Database URL: jdbc:mysql://[localhost]:3306/[openfire]

All other fields are defult when i press Continue button i got error as "+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."+

When i view error.log it show as:

2007.11.13 16:10:41 org.jivesoftware.database.ConnectionPool.(ConnectionPool.java:111) Failed to create new connectio

ns on startup. Attempt 0 of 3

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

at java.lang.reflect.Constructor.newInstance(Unknown Source)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)

at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2104)

at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:729)

at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

at java.lang.reflect.Constructor.newInstance(Unknown Source)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)

at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:283)

at java.sql.DriverManager.getConnection(Unknown Source)

at org.jivesoftware.database.ConnectionPool.createCon(ConnectionPool.java:443)

at org.jivesoftware.database.ConnectionPool.<init>(ConnectionPool.java:88)

at org.jivesoftware.database.DefaultConnectionProvider.start(DefaultConnectionProv ider.java:83)

at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectio nManager.java:459)

at org.jivesoftware.openfire.admin.setup.setup_002ddatasource_002dstandard_jsp._js pService(setup_002ddatasource_002ds

tandard_jsp.java:202)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1093)

at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:11 8)

at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:52)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:65)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingF ilter.java:41)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:69)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:98)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)

at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)

at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)

at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:712)

at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)

at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollect ion.java:211)

at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)

at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:139)

at org.mortbay.jetty.Server.handle(Server.java:313)

at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:506)

at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:844 )

at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:644)

at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)

at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:381)

at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:396)

at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:442)

Caused by: java.net.UnknownHostException: http://202.52.237.115

at java.net.InetAddress.getAllByName(Unknown Source)

at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:246)

at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:276)

at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2027)

… 47 more

2007.11.13 16:10:51 org.jivesoftware.database.ConnectionPool.(ConnectionPool.java:111) Failed to create new connectio

ns on startup. Attempt 1 of 3

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 1 ms ago.