Anyone moved Openfire from Windows to Linux?

David_Goadby1 · January 16, 2013, 11:10pm

I am moving my system from Windows Linux.

I just noticed that Openfire uses table names that are in camel case ie not all lower case. I copied across my Windows database but Openfire ignored the tables and created new ones so it obviously is using the tables in mixed case internally.

I could set MySQL to force all lower case but it is not recommended as it may break other aplications. To be honest anyone who writes an application that uses tables with the same names and differing case is just plain nuts but it’s a strange world sometimes.

The only fix I can come up with is to get the current Openfire schema and write some SQL to rename the tables to the correct camel case names using the forced lowercase names as the source. Before I start this task has anyone actually done it? If not then I will submit the SQL here later on.

I can honestly say that this is the first time I have come across anyone using anything but all lower case MySQL table names. But someone has to be first.

Sorted!

If you are migrating from Windows to Linux then copy the database over to Linux and then run the SQL below. Start up openfire and it works just as it did before.

Note: If you do not rename the tables then Openfire doesn’t complain. What does happen is the log in fails to work even with your previous admin password.

Here is the SQL. Not all tables are camelcase so they are not altered.

Rename tables from lowercase Windows to camelcase Linux

Here the table is called messaging.

USE messaging;

RENAME TABLE ofextcomponentconf TO ofExtComponentConf;

RENAME TABLE ofgroup TO ofGroup;

RENAME TABLE ofgroupprop TO ofGroupProp;

RENAME TABLE ofgroupuser TO ofGroupUser;

RENAME TABLE ofid TO ofID;

RENAME TABLE ofmucaffiliation TO ofMucAffiliation;

RENAME TABLE ofmucconversationlog TO ofMucConversationLog;

RENAME TABLE ofmucmember TO ofMucMember;

RENAME TABLE ofmucroom TO ofMucRoom;

RENAME TABLE ofmucroomprop TO ofMucRoomProp;

RENAME TABLE ofmucservice TO ofMucService;

RENAME TABLE ofmucserviceprop TO ofMucServiceProp;

RENAME TABLE ofoffline TO ofOffline;

RENAME TABLE ofpresence TO ofPresence;

RENAME TABLE ofprivacylist TO ofPrivacyList;

RENAME TABLE ofprivate TO ofPrivate;

RENAME TABLE ofproperty TO ofProperty;

RENAME TABLE ofpubsubaffiliation TO ofPubsubAffiliation;

RENAME TABLE ofpubsubdefaultconf TO ofPubsubDefaultConf;

RENAME TABLE ofpubsubitem TO ofPubsubItem;

RENAME TABLE ofpubsubnode TO ofPubsubNode;

RENAME TABLE ofpubsubnodegroups TO ofPubsubNodeGroups;

RENAME TABLE ofpubsubnodejids TO ofPubsubNodeJIDs;

RENAME TABLE ofpubsubsubscription TO ofPubsubSubscription;

RENAME TABLE ofremoteserverconf TO ofRemoteServerConf;

RENAME TABLE ofroster TO ofRoster;

RENAME TABLE ofrostergroups TO ofRosterGroups;

RENAME TABLE ofsaslauthorized TO ofSASLAuthorized;

RENAME TABLE ofsecurityauditlog TO ofSecurityAuditLog;

RENAME TABLE ofuser TO ofUser;

RENAME TABLE ofuserflag TO ofUserFlag;

RENAME TABLE ofuserprop TO ofUserProp;

RENAME TABLE ofvcard TO ofVCard;

RENAME TABLE ofversion TO ofVersion;

end

Message was edited by: David Goadby.

Anderson_Zardo · June 24, 2015, 3:33pm

Two years late, Here I am to say yes, I did it 2 months ago. In my case I’ve dumped the database to a file, copied it to linux, opened it with VI and I used it to change table names. After that I’ve imported the file to Linux’s mysql. But I have to say: Your method is faster and easier
Here the VI commands that I used:

%s/ofconparticipant/ofConParticipant/g

%s/ofconversation/ofConversation/g

%s/ofextcomponentconf/ofExtComponentConf/g

%s/ofgatewayavatars/ofGatewayAvatars/g

%s/ofgatewaypseudoroster/ofGatewayPseudoRoster/g

%s/ofgatewayregistration/ofGatewayRegistration/g

%s/ofgatewayrestrictions/ofGatewayRestrictions/g

%s/ofgatewayvcards/ofGatewayVCards/g

%s/ofgroup/ofGroup/g

%s/ofgroupprop/ofGroupprop/g

%s/ofgroupuser/ofGroupuser/g

%s/ofid/ofID/g

%s/ofmessagearchive/ofMessageArchive/g

%s/ofmucaffiliation/ofMucAffiliation/g

%s/ofmucconversationlog/ofMucConversationLog/g

%s/ofmucmember/ofMucMember/g

%s/ofmucroom/ofMucRoom/g

%s/ofmucroomprop/ofMucRoomprop/g

%s/ofmucservice/ofMucService/g

%s/ofmucserviceprop/ofMucServiceprop/g

%s/ofoffline/ofOffline/g

%s/ofpfrules/ofPfRules/g

%s/ofpresence/ofPresence/g

%s/ofprivacylist/ofPrivacyList/g

%s/ofprivate/ofPrivate/g

%s/ofproperty/ofProperty/g

%s/ofpubsubaffiliation/ofPubsubAffiliation/g

%s/ofpubsubdefaultconf/ofPubsubDefaultConf/g

%s/ofpubsubitem/ofPubsubItem/g

%s/ofpubsubnode/ofPubsubNode/g

%s/ofpubsubnodegroups/ofPubsubNodegroups/g

%s/ofpubsubnodejids/ofPubsubNodejids /g

%s/ofpubsubsubscription/ofPubsubSubscription/g

%s/ofremoteserverconf/ofRemoteServerConf/g

%s/ofroster/ofRoster/g

%s/ofrostergroups/ofRostergroups/g

%s/ofrrds/ofRRDs/g

%s/ofsaslauthorized/ofSASLAuthorized/g

%s/ofsecurityauditlog/ofSecurityAuditLog/g

%s/ofuser/ofUser/g

%s/ofuserflagofUserflag /g

%s/ofuserprop/ofUserprop /g

%s/ofvcard/ofVCard/g

%s/ofversion/ofVersion/g

If any table has missed, you can dump only that table, change the name in VI an import it to mysql.

mattch · March 18, 2019, 5:00pm

sorry for resurrecting an old thread but ant to drop some updates that drove me nuts, the solution was a lot of typing so pasting here.

if exporting dump from windows i have to convert to unix eol. open in notepad++ edit> EOL > convert unix
rename all the tables. here is sql you can import or copy paste

-- convert from windows mysql db to unix, change the damn table case
-- ALTER TABLE exampletable RENAME TO new_table_name;
-- change db name **************
USE of_pooters;

ALTER TABLE ofbookmark RENAME TO ofBookMark;
ALTER TABLE ofbookmarkperm RENAME TO ofBookMarkPerm;
ALTER TABLE ofbookmarkprop RENAME TO ofBookMarkProp;
ALTER TABLE ofconparticipant RENAME TO ofConparticipant;
ALTER TABLE ofconversion RENAME TO ofConversion;
ALTER TABLE ofextcomponentconf RENAME TO ofExtComponentConf;
ALTER TABLE ofgroup RENAME TO ofGroup;
ALTER TABLE ofgroupprop RENAME TO ofGroupProp;
ALTER TABLE ofgroupuser RENAME TO ofGroupUser;
ALTER TABLE ofid RENAME TO ofID;
ALTER TABLE ofmessagearchive RENAME TO ofMessageArchive;
ALTER TABLE ofmucaffiliation RENAME TO ofMucAffiliation;
ALTER TABLE ofmucconversionlog RENAME TO ofMucConversionLog;
ALTER TABLE ofmucmember RENAME TO ofMucMember;
ALTER TABLE ofmucroom RENAME TO ofMucRoom;
ALTER TABLE ofmucroomprop RENAME TO ofMucRoomProp;
ALTER TABLE ofmucservice RENAME TO ofMucService;
ALTER TABLE ofmucserviceprop RENAME TO ofMucServiceProp;
ALTER TABLE ofoffline RENAME TO ofOffline;
ALTER TABLE ofpfrules RENAME TO ofpfrules;
ALTER TABLE ofpresence RENAME TO ofPresence;
ALTER TABLE ofprivacylist RENAME TO ofPrivacyList;
ALTER TABLE ofprivate RENAME TO ofPrivate;
ALTER TABLE ofproperty RENAME TO ofProperty;
ALTER TABLE ofpubsubaffiliation RENAME TO ofPubsubAffiiation;
ALTER TABLE ofpubsubdefaultconf RENAME TO ofPubsubDefaultConf;
ALTER TABLE ofpubsubitem RENAME TO ofPubsubItem;
ALTER TABLE ofpubsubnode RENAME TO ofPubsubNode;
ALTER TABLE ofpubsubnodegroups RENAME TO ofPubsubNodeGroups;
ALTER TABLE ofpubsubnodejids RENAME TO ofPubsubNodeJIDs;
ALTER TABLE ofpubsubsubscription RENAME TO ofPubsubSubscription;
ALTER TABLE ofremoteserverconf RENAME TO ofRemoteServerConf;
ALTER TABLE ofroster RENAME TO ofRoster;
ALTER TABLE ofrostergroups RENAME TO ofRosterGroups;
ALTER TABLE ofrrds RENAME TO ofrrds;
ALTER TABLE ofsaslauthorized RENAME TO ofSASLAuthorized;
ALTER TABLE ofsecurityauditlog RENAME TO ofSecurityAuditLog;
ALTER TABLE ofuser RENAME TO ofUser;
ALTER TABLE ofuserfag RENAME TO ofUserFlag;
ALTER TABLE ofuserprop RENAME TO ofUserProp;
ALTER TABLE ofvcard RENAME TO ofVCard;
ALTER TABLE ofversion RENAME TO ofVersion;