Database upgrade mysql to version 2.6

Openfire Openfire Support
1

Hello,

when updating the mysql database to version 7 for the new wilfire 2.6 i get the following error:

CREATE TABLE pubsubNode(

serviceID VARCHAR( 100 ) NOT NULL ,

nodeID VARCHAR( 100 ) NOT NULL ,

leaf TINYINT NOT NULL ,

creationDate CHAR( 15 ) NOT NULL ,

modificationDate CHAR( 15 ) NOT NULL ,

parent VARCHAR( 100 ) NULL ,

deliverPayloads TINYINT NOT NULL ,

maxPayloadSize INTEGER NULL ,

persistItems TINYINT NULL ,

maxItems INTEGER NULL ,

notifyConfigChanges TINYINT NOT NULL ,

notifyDelete TINYINT NOT NULL ,

notifyRetract TINYINT NOT NULL ,

presenceBased TINYINT NOT NULL ,

sendItemSubscribe TINYINT NOT NULL ,

publisherModel VARCHAR( 15 ) NOT NULL ,

subscriptionEnabled TINYINT NOT NULL ,

configSubscription TINYINT NOT NULL ,

contacts VARCHAR( 4000 ) NULL ,

rosterGroups VARCHAR( 4000 ) NULL ,

accessModel VARCHAR( 10 ) NOT NULL ,

payloadType VARCHAR( 100 ) NULL ,

bodyXSLT VARCHAR( 100 ) NULL ,

dataformXSLT VARCHAR( 100 ) NULL ,

creator VARCHAR( 1024 ) NOT NULL ,

description VARCHAR( 255 ) NULL ,

language VARCHAR( 255 ) NULL ,

name VARCHAR( 50 ) NULL ,

replyPolicy VARCHAR( 15 ) NULL ,

replyRooms VARCHAR( 4000 ) NULL ,

replyTo VARCHAR( 1024 ) NULL ,

associationPolicy VARCHAR( 15 ) NULL ,

associationTrusted VARCHAR( 4000 ) NULL ,

maxLeafNodes INTEGER NULL ,

PRIMARY KEY ( serviceID, nodeID )

)

MySQL meldet:

#1074 - Too big column length for column ‘‘contacts’’ (max = 255). Use BLOB instead

all VARCHAR fields are limited to 255

Alex

2

Hi Alex,

Gato did post “MySQL should be upgraded automatically without any problem unless you are using MySQL 4.0.* that by default won’'t allow fields bigger than 255 characters. In that case, I would recommend upgrading to MySQL 5.0 or at least MySQL 4.1.18.” in thread http://www.jivesoftware.org/community/thread.jspa?threadID=19116 .

Are using MySQL 4.0.x or do you have another problem?

LG

3

To add to LG’'s comment, if you cannot upgrade MySQL you can do the following changes to resources/database/wildfire_mysql.sql:

  1. Replace all VARCHAR(1024) to VARCHAR(255)

  2. Replace all VARCHAR(4000) to TEXT

BTW, people doing an upgrade may apply the above changes to resources/database/7/wildfire_mysql.sql.

Regards,

– Gato

4

Hello,

i run MySQL 4.0.15 and can’'t update the server because of some other projects which use the same mySQL Server. But i hope to get a new box soon which will run mySQL 5 then.

I will try the changes which Gato suggested.

Thanx Alex

5

That’‘s not a very good news for me. I am so out of luck with MySQL(my local installation) set up. Everytime I upgrade WF for test server, I always got into trouble making it works. Fortunatly there is a MySQL installation in place for my production server. However it is MySQL 4 (It can’'t be upgraded because it is also used by a lot of application). Now WF2.6 need MySQL 5.0, SHUDDER