Database upgrade failed

Openfire Openfire Support
1

Following on from another issue, I have just re-installed the Enterprise plugin in OF 3.3.0. I cleared the error log before stating and it now contains the following errors:

One that stands out is: enterprise - Database update failed. Please manually upgrade your database.

Why would this occur and how do I resolve it? Is it an indication of a more serious OF error?

Steve

2007.04.26 12:26:47 [org.jivesoftware.util.log.util.CommonsLogFactory$1.error(CommonsLogFactory.jav a:87)

] Line=19 The content of element type “dwr” must match “(init?,allow?,signatures?)”.

2007.04.26 12:27:27 [org.jivesoftware.database.SchemaManager.checkSchema(SchemaManager.java:251)

]

java.sql.SQLException: [SQLServer 2000 Driver for JDBC][SQLServer]Column names in each table must be unique. Column name ‘‘room’’ in table ‘‘entConversation’’ is specified more than once.

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)

at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)

at com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest.processReplyToken(Unknown Source)

at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)

at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)

at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)

at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)

at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)

at com.microsoft.jdbc.base.BaseStatement.executeInternal(Unknown Source)

at com.microsoft.jdbc.base.BaseStatement.execute(Unknown Source)

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

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

at org.jivesoftware.database.SchemaManager.checkPluginSchema(SchemaManager.java:11 1)

at org.jivesoftware.openfire.container.PluginManager.loadPlugin(PluginManager.java :378)

at org.jivesoftware.openfire.container.PluginManager.access$200(PluginManager.java :46)

at org.jivesoftware.openfire.container.PluginManager$PluginMonitor.run(PluginManag er.java:916)

at org.jivesoftware.openfire.container.PluginManager.installPlugin(PluginManager.j ava:142)

at org.jivesoftware.openfire.update.UpdateManager.downloadPlugin(UpdateManager.jav a:243)

at org.jivesoftware.openfire.update.PluginDownloadManager.installPlugin(PluginDown loadManager.java:56)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at uk.ltd.getahead.dwr.impl.ExecuteQuery.execute(ExecuteQuery.java:239)

at uk.ltd.getahead.dwr.impl.DefaultExecProcessor.handle(DefaultExecProcessor.java: 48)

at uk.ltd.getahead.dwr.impl.DefaultProcessor.handle(DefaultProcessor.java:81)

at uk.ltd.getahead.dwr.AbstractDWRServlet.doPost(AbstractDWRServlet.java:162)

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

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

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

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

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:1065)

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

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

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

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

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

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

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:285)

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

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

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

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

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

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

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

2007.04.26 12:27:27 [org.jivesoftware.openfire.container.PluginManager.loadPlugin(PluginManager.jav a:380)

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

2007.04.26 12:27:28 [com.jivesoftware.openfire.enterprise.archive.ArchiveIndexer.indexConversations (ArchiveIndexer.java:510)

]

java.sql.SQLException: [SQLServer 2000 Driver for JDBC]ResultSet can not re-read row data for column 2.

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.base.BaseResultSet.validateColumnIndex(Unknown Source)

at com.microsoft.jdbc.base.BaseResultSet.getLong(Unknown Source)

at com.jivesoftware.openfire.enterprise.archive.ArchiveIndexer.indexConversations( ArchiveIndexer.java:486)

at com.jivesoftware.openfire.enterprise.archive.ArchiveIndexer.access$300(ArchiveI ndexer.java:57)

at com.jivesoftware.openfire.enterprise.archive.ArchiveIndexer$4.run(ArchiveIndexe r.java:362)

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

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

at java.util.concurrent.FutureTask.run(Unknown 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)

2

I’‘m still getting a lot of errors. I’'ve just deleted the Enterprise plugin again from the admin console, I then dropped all the ent* tables from the database, I then deleted the enterprise version number from the jiveVersion table.

I then downloaded and reinstalled the plugin again and it recreated the tables, added version 7 to the version table and STILL complained about the database upgrade.

This error is repeated in the error log every minute:

2007.04.26 12:45:19 [com.jivesoftware.openfire.enterprise.archive.ArchiveIndexer.indexConversations (ArchiveIndexer.java:510)

]

java.sql.SQLException: [SQLServer 2000 Driver for JDBC]ResultSet can not re-read row data for column 2.

3

a bit further along now. I tried the clearout again and this time removed the following tables:

ent*

fp*

curiously, the fp* tables were all duplicated, one with an owner of JiveMessenger and one with an owner of dbo.

Reloaded the plugin, all the tables have been created without error now. and the errors above seem to have gone.

4

aargh! spoke to soon:

2007.04.26 13:32:55 [com.jivesoftware.openfire.enterprise.archive.ArchiveIndexer.indexConversations (ArchiveIndexer.java:510)

]

java.sql.SQLException: [SQLServer 2000 Driver for JDBC]ResultSet can not re-read row data for column 2.

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.base.BaseResultSet.validateColumnIndex(Unknown Source)

at com.microsoft.jdbc.base.BaseResultSet.getLong(Unknown Source)

at com.jivesoftware.openfire.enterprise.archive.ArchiveIndexer.indexConversations( ArchiveIndexer.java:486)

at com.jivesoftware.openfire.enterprise.archive.ArchiveIndexer.updateIndex(Archive Indexer.java:276)

at com.jivesoftware.openfire.enterprise.archive.ArchiveIndexer$2.run(ArchiveIndexe r.java:153)

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

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

at java.util.concurrent.FutureTask.run(Unknown 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)

5

Steve,

This is not a bug due to your upgrade. It’'s actually a bug in the code. The application is erroneously trying to read the column value from the result set two times (you can only pull it once from a result set).

I have logged this as a bug with an ID of: http://www.igniterealtime.org/issues/browse/ENT-115

Make sure to vote for it!

6

There is also a problem with the DB schripts of the Enterprise Edition when using MySQL and UTF-8 as character set because the varchar columns that make up the index are too large then:

CREATE TABLE entConParticipant (

-> conversationID BIGINT NOT NULL,

-> joinedDate BIGINT NOT NULL,

-> leftDate BIGINT NULL,

-> bareJID VARCHAR(255) NOT NULL,

-> jidResource VARCHAR(255) NOT NULL,

-> nickname VARCHAR(255) NULL,

-> INDEX entConParticipant_conv_idx (conversationID, bareJID, jidResource, joinedDate),

-> INDEX entConParticipant_jid_idx (bareJID)

-> );

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

7

Can you help me reproduce this? To try to create this error, I did the following:

  1. Create database using utf8: create database deleteme character set utf8;

  2. Run the following script (from your post):

CREATE TABLE entConParticipant (

conversationID BIGINT NOT NULL,

joinedDate BIGINT NOT NULL,

leftDate BIGINT NULL,

bareJID VARCHAR(255) NOT NULL,

jidResource VARCHAR(255) NOT NULL,

nickname VARCHAR(255) NULL,

INDEX entConParticipant_conv_idx (conversationID, bareJID, jidResource, joinedDate),

INDEX entConParticipant_jid_idx (bareJID)

);

That worked fine for me. I am running on MySQL v. 5.0

8

Maybe you forgot to actually use the deleteme database.

Anyway here is exactly what I did to reproduce this:

root@samael:~# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 31

Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

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

mysql> create database deleteme character set utf8;

Query OK, 1 row affected (0.00 sec)

mysql> use deleteme;

Database changed

mysql> CREATE TABLE entConParticipant (

-> conversationID BIGINT NOT NULL,

-> joinedDate BIGINT NOT NULL,

-> leftDate BIGINT NULL,

-> bareJID VARCHAR(255) NOT NULL,

-> jidResource VARCHAR(255) NOT NULL,

-> nickname VARCHAR(255) NULL,

-> INDEX entConParticipant_conv_idx (conversationID, bareJID, jidResource, joinedDate),

-> INDEX entConParticipant_jid_idx (bareJID)

-> );

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

mysql>

Hope that helps.

9

I did select the database, but forgot to add that command–thanks

What version of MySQL are you running? On what OS? As this worked fine for me on my local instance, I am pretty confident that this issue is specific to a certain version.

10

So you were able to reproduce it now?

I am running MySQL 5.0.38 on Ubuntu feisty (7.04).

11

Thanks for the additional information. This issue is specific to MySQL on Linux. I have reproduced it, and logged it ENT-116

Included in that description are workarounds to this problem.

Sorry for the inconvenience!