Wildifre 2.6 SQL script errors

Openfire Openfire Dev
1

When running the SQL script against a new database the following errors are reported:

Warning: The table ‘‘pubsubNode’’ has been created but its maximum row size (38671) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Warning! The maximum key length is 900 bytes. The index ‘‘pubsubAffil_pk’’ has maximum length of 2448 bytes. For some combination of large values, the insert/update operation will fail.

(1 row(s) affected)

Warning: The table ‘‘pubsubNode’’ has been created but its maximum row size (38671) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

(1 row(s) affected)

I am running this against SQL2000 with service pack 4.

Created a new database ( ie: no existing tables etc.)

Script name C:\program files\wildfire\resources\database\wilfire_sqlserver.sql

regards,

Matthew.

2

Hey Matthew,

It seems that the 8*** limit was removed since SQLServer 2005 so my MSDE installation never detected this problem. Based on what I found regarding this restriction the most recommended solution is to change the table definition. If you are doing a fresh install then you will need to edit resources/database/wildfire_sqlserver.sql otherwise edit resources/database/7/wildfire_sqlserver.sql. Replace NVARCHAR(4000) with NVARCHAR(400) and NVARCHAR(1024) with NVARCHAR(255). Recreate the missing pubsub tables and you will be good to go.

Regards,

– Gato

3

Thanks for the info.

I will do some more testing in my VMWare setup.

Looks like it’'s time to start looking at upgrade to SQL2005.

Is it possible the developers could include scripts for both SQL2000 and SQL2005??

There must still be a lot of users out there running SQL2000.

4

Hey Matthew,

In fact, we are evaluating the option to change the structure of pubsubNode and split it into more smaller tables. This modification will probably allow us to use the same script for all databases and their different versions. Meanwhile, the options are the ones I mentioned in the previous post.

Let me know if you need further help.

Regards,

– Gato

5

I’'m seeing the same issue. Assuming we make the recommended changes, will later automatic db upgrades handle the changed column sizes or will we have to do some manual table manipulations on the next upgrade?

6

What about the script detecting the version of SQL running

and modify the tables accordingly??

This must be why the installation fails on the database upgrade.

(from the script warnings about table/row sizes)

Doesn’'t seem very user friendly if you have to modify scripts and update

the database yourself.

I also noticed that the script drops all the Groups tables.

That’'s why I lost all my roster groups and their members.

Now I know to export my data , so I can import it later.

7

Hey Matthew,

Wildfire 2.6.2 includes a modification to the pubsubNode table that will reduce its current size. Therefore, there is no longer the need to manually change the db scripts. BTW, which scripts are droping group tables?

Thanks,

– Gato

8

Script is in upgrade folder: C:\Program Files\Wildfire\resources\database\upgrade\1

Here is a copy of the script:

"

  1. $Revision: 795 $

  2. $Date: 2005-01-06 07:44:42 -0300 (Thu, 06 Jan 2005) $

  3. jiveGroup: Recreate table from scratch

DROP TABLE jiveGroup;

CREATE TABLE jiveGroup (

groupName VARCHAR(50) NOT NULL,

description VARCHAR(255),

PRIMARY KEY (groupName)

);

  1. jiveGroupProp: Recreate table from scratch

DROP TABLE jiveGroupProp;

CREATE TABLE jiveGroupProp (

groupName VARCHAR(50) NOT NULL,

name VARCHAR(100) NOT NULL,

propValue TEXT NOT NULL,

PRIMARY KEY (groupName, name)

);

  1. jiveGroupUser: Recreate table from scratch

DROP TABLE jiveGroupUser;

CREATE TABLE jiveGroupUser (

groupName VARCHAR(50) NOT NULL,

username VARCHAR(32) NOT NULL,

administrator TINYINT NOT NULL,

PRIMARY KEY (groupName, username, administrator)

);

"

9

Hey Matthew,

The \resources\database\upgrade\1 upgrade scripts should be applied when upgrading a Wildfire 2.0 version to Wildfire 2.1. If you are creating a database from scratch then you should only execute the script located in \resources\database. Is the server trying to execute the script located in \resources\database\upgrade\1 automatically?

Regards,

– Gato

10

Hi Gato

I was also getting a similar error with MySQL 5 - details below:

2006.04.10 17:05:43 org.jivesoftware.wildfire.pubsub.PubSubPersistenceManager.saveSubscription(PubSu bPersistenceManager.java:740)

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value adjusted for column ‘‘digest_frequency’’ at row 1

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

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)

at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.ja va:1124)

at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement. java:676)

12:45 PM 4/25/06at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1166)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1082)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1067)

at org.jivesoftware.wildfire.pubsub.PubSubPersistenceManager.saveSubscription(PubS ubPersistenceManager.java:699)

at org.jivesoftware.wildfire.pubsub.Node.saveToDB(Node.java:1674)

at org.jivesoftware.wildfire.pubsub.PubSubModule.initialize(PubSubModule.java:400)

at org.jivesoftware.wildfire.XMPPServer.initModules(XMPPServer.java:472)

at org.jivesoftware.wildfire.XMPPServer.start(XMPPServer.java:369)

at org.jivesoftware.wildfire.XMPPServer.(XMPPServer.java:142)

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 java.lang.Class.newInstance0(Unknown Source)

at java.lang.Class.newInstance(Unknown Source)

at org.jivesoftware.wildfire.starter.ServerStarter.start(ServerStarter.java:88)

at org.jivesoftware.wildfire.starter.ServerStarter.main(ServerStarter.java:49)

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 com.exe4j.runtime.LauncherEngine.launch(Unknown Source)

at com.exe4j.runtime.WinLauncher.main(Unknown Source)

Thoughts ?

Martyn

11

Hey Martyn,

Thanks for the bug report. The problem has been fixed and it only affected MySQL. Anyway, the truncated information is not really used by Wildfire since digest mode is not being supported by the Pubsub service.

To manually change your database you just need to execute the following statement:

ALTER TABLE pubsubSubscription MODIFY digest_frequency INT NOT NULL;[/code]

Regards,

– Gato

12

Hi Gato

Thanks for the reply, I’'ll do this and continue with my testing. Wifi is certainly getting very feature rich - look forward to version 3 !

Martyn