SQL error when registering gateways

Openfire Plugins Kraken - IM Gateway
1

I’‘ve been following this IM server for some time now. With the gateway beta release yesterday, I’'ve started the install and config for my own setup.

Steps:

  1. Download complete zip (never had an install)

  2. Created a ‘‘wildfire’’ DB on MS SQL 2005 Express

  3. Created a ‘‘wildfire’’ user and password, allowed this user to connect to ‘‘wildfire’’ db

  4. Ran the install script for the beta in the wildfire DB, no errors (but a warning) on SQL install

  5. Started the config, pointed to my DB, created admin user

  6. Logged in as admin

  7. Added a new user

  8. Gateway -> Settings: allowed AIM, ICQ, MSN, Yahoo

  9. Registrations: added a new registration to for yahoo

Result: SQL error in error.log

2006.09.19 11:28:33 [org.jivesoftware.wildfire.gateway.Registration.(Registration.java:81)

at org.jivesoftware.wildfire.gateway.RegistrationManager.createRegistration(Regist rationManager.java:69)

at org.jivesoftware.wildfire.gateway.BaseTransport.addNewRegistration(BaseTranspor t.java:1124)

at org.jivesoftware.wildfire.plugin.gateway.gateway_002dregistrations_jsp._jspServ ice(gateway_002dregistrations_jsp.java:136)

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

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

at org.jivesoftware.wildfire.container.PluginServlet.handleJSP(PluginServlet.java: 234)

at org.jivesoftware.wildfire.container.PluginServlet.service(PluginServlet.java:92 )

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

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:830)

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

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.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler. java:471)

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

at org.mortbay.http.HttpContext.handle(HttpContext.java:1530)

at org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.ja va:633)

at org.mortbay.http.HttpContext.handle(HttpContext.java:1482)

at org.mortbay.http.HttpServer.service(HttpServer.java:909)

at org.mortbay.http.HttpConnection.service(HttpConnection.java:816)

at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:982)

at org.mortbay.http.HttpConnection.handle(HttpConnection.java:833)

at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244)

at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357)

at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534)

This is a clean install, so I’‘m betting an install script/setting isn’'t correct for SQL. Using the built in DB (vs. an external DB) works just fine – I can Register transports for my user just fine (numerous problems with yahoo, different issue).

My profession is software testing, I can provide any/all info on my setup.

2

This is fixed via GATE-41, and will be included in 1.0 Beta 2, to be released “in a bit”. =)

3

Hi Daniel,

the flat database design makes me think of an Excel table and not of a database, so I don’‘t wonder why one may get such errors. I assume it’'s on your roadmap to change the design for the final version?

The installation did run fine for me but I did not yet add a buddy (;

LG

4

Huh? Not really sure what you mean. Many database tables I look at aren’'t more than glorified spreadsheets.

5

Hi Daniel,

so we totally agree, don’'t we? Wildfire is probably not a good example to look at, especially as there are no FK used.

Is one able to use more than one MSN contacts per JID?

“transportType VARCHAR(15)” is probably a good example, I’'d expect a reference transportTypeID there to a table which stores the transportTypes. Or do I want to read there “ICQ” in every line if I just use ICQ?

LG

6

I don’'t know what you mean by FK.

In the future, yes possibly more than one MSN contact could be registered per JID. Right now I’'m only supporting 1. More will require some actual client interaction. =)

If I were an admin trying to track down issues by looking directly at the databases, yes, I would rather see “icq” in there than a transport type id. Otherwise, instead of doing:

select * from gatewayRegistrations where type = “icq”

I would be going:

select * from gatewayRegistrations where type … dammit delete

select * from gatewayTypes

select * from gatewayRegistrations where type = IDNUM

Yes I could do a join or something along those lines, but why bother myself with having to type a lot more just to get a list of ICQ users?

Too many layers of abstraction just makes it that much harder to track down problems. Typically I would abstract such things only if the transport type was something that was user editable. (like the end user could end up adding other transport types)

7

The Beta 1.0 Release2 gateway.jar does not fix the problem with SQL Server.

8

Crud, I wonder if it’'s already created the table poorly on your db? Any chance you could either stop your server or delete the gateway plugin, go in to your wildfire db and:

drop table gatewayRegistrations

drop table gatewayPseudoRoster

delete from jiveVersion where name = ‘‘gateway’’

Then reinstall the plugin (or restart your server if you stopped it)? (some of those commands may fail depending on how far the script got last time)

9

drop table gatewayRegistrations

– nothing to drop

The other two are there, however and I didn’'t finish the delete.

select *

from gatewayPseudoRoster

– 0 records

select *

from jiveVersion where name = ‘‘gateway’’

– gateway, 1

10

Please execute the other commands (even if they all fail) and then let wildfire recreate the db. (basically I’‘m getting you to make wildfire think it’'s never heard of this plugin so it recreates everything fresh)

11

Deleted what you asked for, here is the startup from the console:

Missing database schema for gateway. Attempting to install…

gateway - Database update failed. Please manually upgrade your database.

12

Any ideas? =/ (you or Matt) I don’'t know anything about MS SQL. =/

13

Do you see anything in your error.log that relates to this? There should be a stack trace indicating exactly what caused the problem. Could you post this?

14

The error message I posted on top was from a 100% clean install. I now have a dirty install on my box, but I’'ll post the messages for startup (after the DB mods have been applied)

error.log:

2006.09.20 07:43:35 [org.jivesoftware.database.SchemaManager.checkSchema(SchemaManager.java:204)

]

java.sql.SQLException: There is already an object named ‘‘gatewayRegistration’’ in the database.

at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)

at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)

at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)

at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)

at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)

at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:664)

at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1114)

at org.jivesoftware.database.SchemaManager.executeSQLScript(SchemaManager.java:332 )

at org.jivesoftware.database.SchemaManager.checkSchema(SchemaManager.java:201)

at org.jivesoftware.database.SchemaManager.checkPluginSchema(SchemaManager.java:10 9)

at org.jivesoftware.wildfire.container.PluginManager.loadPlugin(PluginManager.java :372)

at org.jivesoftware.wildfire.container.PluginManager.access$200(PluginManager.java :47)

at org.jivesoftware.wildfire.container.PluginManager$PluginMonitor.run(PluginManag er.java:888)

at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.util.concurrent.FutureTask$Sync.innerRunAndReset(Unknown Source)

at java.util.concurrent.FutureTask.runAndReset(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101 (Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodi c(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknow n Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

2006.09.20 07:43:35 [org.jivesoftware.wildfire.container.PluginManager.loadPlugin(PluginManager.jav a:374)

] gateway - Database update failed. Please manually upgrade your database.

15

Can I get you to clean up the install (remove gateway.jar, drop gatewayRegistrations, drop gatewayPseudoRoster, delete from jiveVersion where name = ‘‘gateway’’, reinstall gateway.jar) and grab the traceback/error message generated from that?

16

I have the same problem. I went to my MS SQL admin console and to the gatewayRegistrations table and change the registrationdate and lastlogin datetype from int to bigint

17

And that did the trick?

18

I did that and it works. Do post again if it do not work. I can post up the SQL script

19

Ok, did a 100% clean install:

  1. Deleted the DB

  2. Deleted the wildfire folder

  3. Recreated ‘‘wildfire’’ DB (user was already there, associated)

  4. Unzipped beta1

  5. Used install scripts from /resources/ folder

  6. Started bin/wildfire.exe (WinXP Pro)

  7. Configured connections

  8. Stopped wildfire

  9. Copied in the newer gateway.jar

  10. Started wildfire

  11. Checked DB columns for gatewayregistration. lastlogin and registration dates are bigint

  12. Added gateway

BOOYA! Fixed! Now onto bigger fish to fry!

20

Awesome! Sorry for the hooha! =)