NEED HELP: migrating openfire database (mysql)

win5hit · November 26, 2013, 10:12am

Hi there,

I’ve been trying to migrate the openfire-database (mysql based in my case) to a new database host.

Right now it’s running on a win2k3 based vm that was build for testing.(with openfire 3.8.2) DB is mysql 5.6.

Now I’d like to move the database to a new database host. The vm is based on ubuntu 12.04 LTS - x64.

mysql-server in repo is 5.5.34-0ubuntu0.12.04.1. So it’s a downgrade from 5.6(windows) to 5.5(linux).

I’ve exported and tried to move the openfire database several times, each time a differen way.

(via mysqldump, mysql workbench with migration assistant, heidisql…) But without luck.

If i start openfire pointing to the new database host i get a message like:

Missing database schema for openfire. updating database schema: successfull

Missing database schema for gateway. updating database schema: successfull

After this “update” i can’t login to the webinterface any more. Also no user is able to log in.

Could someone help on this one? It would be very nice.

Kind regards

Exact Messages:

2013.11.26 15:46:53 org.jivesoftware.database.SchemaManager - Missing database schema for openfire. Attempting to install…

2013.11.26 15:46:58 org.jivesoftware.database.SchemaManager - Database update successful.

2013.11.26 15:47:04 org.jivesoftware.database.SchemaManager - Missing database schema for gateway. Attempting to install…

2013.11.26 15:47:05 org.jivesoftware.database.SchemaManager - Database update successful.

win5hit · December 5, 2013, 10:32am

Is there anyone that tried this before? I can’t move the database to the new db-host without getting this error.

Is there any Information missing?

LG1 · December 6, 2013, 9:44pm

It seems that your restore / import on the linux server is not recognized by Openfire. So you may want to start with an empty linux database and let Openfire create the schema. Stop Openfire, import the dump into the proper schema and start Openfire. Then it should work.

Jason60 · December 6, 2013, 11:09pm

have you checked the user permissions on the database? it seems like it’s either having issues using the database, or the database was not imported properly. Mysqldump should be able to replicate your databse just about exactly how it was before (although i don’t think it will re-setup user accounts and permissions, those are database dependent)

Jason60 · December 6, 2013, 11:48pm

ok, after looking a little, it looks like openfire creates a random pasword for the openfire@localhost user it sets up on first run and/or empty database schema, then it stores it someplace.

you can do:

mysql> SHOW GRANTS FOR ‘openfire’@‘localhost’;

and it will list all the permissions. the only problem, the password is a hash of the original, so you wont be able to exactly re-create this user without the original password.

you can *try *having openfire create a brand new database by importing the schema fresh per the doc: http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/database .html

and then re-run the openfire first start configuration - follow this thread: http://community.igniterealtime.org/thread/33825

then, after openfire has set things back up, you can try deleting the newly created openfire database and then import your mysqldump file which will re-create the openfire database.

it’s a long shot, but perhaps the newly setup mysql user will retain permissions of the imported database.

win5hit · December 7, 2013, 10:26am

Hi there!

Thanks for your reply! I’ve done it this way also… But, maybe i did something wrong.

On the database-host run several databases, one of them is the openfire-db.

Every database has it’s own user and password.

I did create a new user for the openfire-database on the new host and changed it in the openfire.xml file.

I did export the database from the old database host via mysqldump:

mysqldump -u user - p -P 3308 databasename(openfiredb) > backup.sql

(on the old / testing db host mysql is running on different port 3308)

I transfered the sql file to the new host and imported it there via:

mysql -u user -p < backup.sql

(yes, mysqldump contains CREATE DATABASE IF NOT EXISTS openfiredb …; )

afterwards i created the user for openfire, gave him all rights for the database openfiredb and let it connect from the ip that it’s running on.

Then i get the missing schema stuff and the messages described in my first post.

Kind regards

win5hit · December 7, 2013, 10:30am

Hi there!

Lot’s of help now Thanks a lot!!!

I’ve got all users and passwords written down in a password safe, so no problems with that.

BUT, i just figured out that there are some problems with the ubuntu version and the hypervisor i’m using.

I need to setup a new database host with a different ubuntu server version.

I’m doing it right now, so expect some reply the next hours.

With the new database host, i’m going to try your advice and report back.

Again, thanks a lot for your help!

in addition:

On the old database host, show grants for the openfire database user returned:

GRANT ALL PRIVILEGES ON openfiredb.* TO ‘specificuser’@‘xxx.xxx.xxx.xxx’ WITH GRANT OPTION

Jason60 · December 7, 2013, 6:53pm

hyper-v, yuck! you Windows guys… lol

win5hit · December 29, 2013, 6:14pm

Hi there,

back again

Sorry for the long delay. I had some defect hardware and wasn’t able to continue my ‘work’.

Now the Hypervisor is “good” again and I’m trying to move the database, again.

NOTE: I’m not moving the openfire service, only the database!

NOTE: openfire is configured to use LDAP for auth!

I’ll try to be as specific as possible:

On the old host(openfire+mysql):

stop the openfire-service

On the new host(mysql):

login to mysql: ‘mysql -u root -p’
create database: ‘create database openfiredb;’

On the old host:

login to mysql: ‘mysql -u root -p’
list users ‘select user,host from mysql.user;’
list permissions for openfire user: ‘show grants for ‘openfire’@‘host’;’

Result:

GRANT USAGE ON . TO ‘openfire’@‘localhost’ IDENTIFIED BY PASSWORD ‘someMD5’;

GRANT ALL PRIVILEGES ON openfiredb.* TO ‘openfire’@‘localhost’ WITH GRANT OPTION;

On the new host:

create user ‘openfire’:

‘GRANT USAGE ON . TO ‘openfire’@‘oldhost’ IDENTIFIED BY PASSWORD ‘someMD5’;’

give permissions to new database

‘GRANT ALL PRIVILEGES ON openfiredb.* TO ‘openfire’@‘oldhost’ WITH GRANT OPTION;’

On the old host:

try logging into the new database : mysql -u openfire -p --host newhost openfiredb

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

Your MySQL connection id is ID

Server version: 5.5.34-0ubuntu0.13.10.1 (Ubuntu)

[…] Login is successfull, i can view a empty database!

Close Connection
dump database openfire: ‘mysqldump -u openfire -p openfiredb > openfiredb.sql’

move openfiredb.sql to new host

On the new host:

import database: ‘mysql -u root -p openfiredb < /home/user/openfiredb.sql’

On the old host:

test login to database again: ‘mysql -u openfire -p --host newhost openfiredb’

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

Your MySQL connection id is ID

Server version: 5.5.34-0ubuntu0.13.10.1 (Ubuntu)

[…] Login is successfull, i can view a restored database!

modify the openfire.xml to reflect the changes:

[…]

jdbc:mysql://newhost:3306/openfiredb?rewriteBatchedStatements=true</s erverURL>

[…]

start openfire service

Missing database schema for openfire. Attempting to install…

Database update successful.

Missing database schema for gateway. Attempting to install…

Database update successful.

Now nobody can login to the openfire service / webinterface.

Please help me to figure out what’s going wrong here, I really would like to be able to move this database…

NOTE:

I can get openfire working again by reverting the changes in openfire.xml, pointing to the old database.

win5hit · December 31, 2013, 10:35am

So i guess the openfire service is killing the database, even though it’s moved like “it should”. (afaik of course)

I’ve moved a couple of other databases the same way, mediawiki and firefox-sync and owncloud db.

Absolutely no problems.

But if i do the same thing with the openfire database it looks like it’s killing its own database.

Maybe something isn’t right with the code that “updates” database schemas??

Please, could someone help me with that?

It’s absolutely torture to log every sql command and try to understand why and what it is doing and what it should be doing!

win5hit · January 1, 2014, 12:53pm

Happy New Year!

I finally found a solution to this Problem.

I was comparing the old database to the database that was created and noticed that the table names on windows were all written in lower case letters, while the table names I found in the ‘resources/database/openfire_mysql.sql’ were all written in camel case. So I modified the mysqldump and fixed the names of the tables. After doing so I never got the missing schema message again.

But, I still wasn’t able to login to the webinterface, as no LDAP auth was occuring.

(The openfire server didn’t even ask the AD)

So I set the setup value in openfire.xml to false and run the configuration again.

I didn’t understand why it was needed, as all the information was stored in the database (table ofProperty), but it fixed the auth problem. Server is up again and all other information that is stored in the db (kraken gateway user accounts) is being used. So no user had to setup them again.

I only hat to restart the plugin after the setup procedure.

Thanks for your help!