SQL database connection issue with Openfire 3.6.3

Openfire Openfire Support
1

Hi,

I have an issue with the MS SQL database connection after the Openfire 3.6.3 was installed. After the installation, when I tried to set up the database Microsoft SQL , the message “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.” appeared.

Below is the copy of the file error.log. Could anyone help look into this issue? Thanks.

Frank

==========================================================

2009.01.23 14:28:26 [org.jivesoftware.util.log.util.CommonsLogFactory$1.error(CommonsLogFactory.jav a:92)
] Prototype
java.sql.SQLException: Login failed for user ‘online_jive_local’.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:602)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:344)
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.(ConnectionJDBC3.java:50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
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)
2009.01.23 14:28:26 [org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnecti onManager.java:501)
]
java.sql.SQLException: Login failed for user ‘online_jive_local’.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:602)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:344)
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.(ConnectionJDBC3.java:50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at org.logicalcobwebs.proxool.DefaultConnectionBuilder.buildConnection(DefaultConn ectionBuilder.java:39)
at org.logicalcobwebs.proxool.Prototyper.buildConnection(Prototyper.java:159)
at org.logicalcobwebs.proxool.ConnectionPool.getConnection(ConnectionPool.java:211 )
at org.logicalcobwebs.proxool.ProxoolDriver.connect(ProxoolDriver.java:89)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConnec tionProvider.java:75)
at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectio nManager.java:494)
at org.jivesoftware.openfire.admin.setup.setup_002ddatasource_002dstandard_jsp._js pService(setup_002ddatasource_002dstandard_jsp.java:206)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
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:66)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingF ilter.java:42)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:70)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:146)
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:726)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollect ion.java:206)
at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:324)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843 )
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:648)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)

2

Did you verify that you configured you database with the correct permissions on the MSSQL server. you need to set a user and access permissions for the database.

3

Yes, the database has been working with Wildfire 3.2.2 for over 2 years.

4

So you tried to move from Wildfire 3.2 to Openfire 3.6.3 in one step? This could cause any number of issues. There are many significant issues between the 2 programs, not just the name and install locations.

5

Thanks for the info. However I unstalled the Wildfire completely, then I installed the Openfire 3.6.3.

6

Is it a new or old database though? The database is the point of concern.

7

The database is old. It had been working for over 2 years with Wildfire, about a half year ago. I’s thinking if username/password to access is incorrect. Can we find any clues from the error.log attached? Or I can re-recreate a new database for testing purpose and see if Openfire can make a connection to it.

8

Another info. When I opened openfire_sqlserver.sql file and under Query Analyser tool, used F5 to run the script, I got some warning messages below. I attached the script file in the bottom. Does the warning matter?

==========================

Warning! The maximum key length is 900 bytes. The index ‘ofRoster_jid_idx’ has maximum length of 2048 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index ‘ofSASLAuthorized_pk’ has maximum length of 4128 bytes. For some combination of large values, the insert/update operation will fail.
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object ‘ofID’, database ‘jabber_trackit’, owner ‘jabber_trackit’.
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object ‘ofID’, database ‘jabber_trackit’, owner ‘jabber_trackit’.
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object ‘ofID’, database ‘jabber_trackit’, owner ‘jabber_trackit’.
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object ‘ofID’, database ‘jabber_trackit’, owner ‘jabber_trackit’.
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object ‘ofVersion’, database ‘jabber_trackit’, owner ‘jabber_trackit’.
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object ‘ofUser’, database ‘jabber_trackit’, owner ‘jabber_trackit’.
Server: Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object ‘ofMucService’, database ‘jabber_trackit’, owner ‘jabber_trackit’.

========================================================

/* $Revision: 1650 $ /
/ $Date: 2005-07-20 00:18:17 -0300 (Wed, 20 Jul 2005) $ */

CREATE TABLE ofUser (
username NVARCHAR(64) NOT NULL,
plainPassword NVARCHAR(32),
encryptedPassword NVARCHAR(255),
name NVARCHAR(100),
email VARCHAR(100),
creationDate CHAR(15) NOT NULL,
modificationDate CHAR(15) NOT NULL,
CONSTRAINT ofUser_pk PRIMARY KEY (username)
);
CREATE INDEX ofUser_cDate_idx ON ofUser (creationDate ASC);

CREATE TABLE ofUserProp (
username NVARCHAR(64) NOT NULL,
name NVARCHAR(100) NOT NULL,
propValue NVARCHAR(2000) NOT NULL,
CONSTRAINT ofUserProp_pk PRIMARY KEY (username, name)
);

CREATE TABLE ofUserFlag (
username NVARCHAR(64) NOT NULL,
name NVARCHAR(100) NOT NULL,
startTime CHAR(15),
endTime CHAR(15),
CONSTRAINT ofUserFlag_pk PRIMARY KEY (username, name)
);
CREATE INDEX ofUserFlag_sTime_idx ON ofUserFlag (startTime ASC);
CREATE INDEX ofUserFlag_eTime_idx ON ofUserFlag (endTime ASC);

CREATE TABLE ofPrivate (
username NVARCHAR(64) NOT NULL,
name NVARCHAR(100) NOT NULL,
namespace NVARCHAR(200) NOT NULL,
privateData NTEXT NOT NULL,
CONSTRAINT ofPrivate_pk PRIMARY KEY (username, name, namespace)
);

CREATE TABLE ofOffline (
username NVARCHAR(64) NOT NULL,
messageID INTEGER NOT NULL,
creationDate CHAR(15) NOT NULL,
messageSize INTEGER NOT NULL,
stanza NTEXT NOT NULL,
CONSTRAINT ofOffline_pk PRIMARY KEY (username, messageID)
);

9

I have never heard of anyone successfully upgrading from such an old version in one step. The requirements of the database servers has changed, database table structure, and many other things. I do not believe this is going to work. I upgraded in steps when I made the move from wildfire to openfire. That worked.

10

I have said that I uninstalled the Wildfire and then installed Openfire. So this is a new installation, not an upgrading.

11

He meant upgrades in the database. You removed the old program, but, as I understood, you are still using the old tables. Those need to be upgraded as well as many things changed. The current Openfire is probably not able to handle such an upgrade directly so you either need to upgrade step by step (by installing at least the bigger releases) or fix the DB by hand.

One thing I recommend anyways is: Restart the initial setup (via Setup=true in openfire.xml). This does not brake anything but it assures that openfire has its basic data to run and you may check what works and what does not afterwards. 3.6. changed a lot as many things moved from the XML to the db, so something might be broken in there.

Cleaner and maybe easier as well would be a complete resetup.

12

This is exactly what I was trying to convey. The database is too old for a single step conversion. Everytime Wildfire/Openfire is upgraded there are small changes that done to the database. This is cumulative. Also there has been changes to the minimum versions of database servers required to host the database for new versions of openfire.

13

For testing purpose, do you think if this is a good idea if I re-create a new database to be connected with Openfire?

14

could not hurt.