Hi,
I struggled for a bit getting the custom database integration (auth and user) working with openfire 3.6.4 (Mac OS X) and a mysql database. I was receiving errors about duplicate entries in the property table and xml config file, so i decided to ignore the customer integration custom database guide and just update the appropriate table (ofProperty). I chose to run sql commands as opposed to editing through the admin interface to make my life easier (now and later).
Here are the steps I took… maybe it will help someone else out.
-
Performed clean install of openfire using mysql database
-
Stopped openfire service
-
Ran the sql listed below
-
Started the openfire service
-
Successfully logged in using data from another database
Here’s the sql (you’ll need to tweak a few things for your set up):
/* make modifications */
update ofProperty set propValue = ‘org.jivesoftware.openfire.auth.JDBCAuthProvider’ where name = ‘provider.auth.className’;
update ofProperty set propValue = ‘org.jivesoftware.openfire.user.JDBCUserProvider’ where name = ‘provider.user.className’;
/* connection */
insert into ofProperty (name, propValue) values (‘jdbcProvider.driver’, ‘com.mysql.jdbc.Driver’);
insert into ofProperty (name, propValue) values (‘jdbcProvider.connectionString’, ‘jdbc:mysql://localhost/[db_name]?user=[user_name]&password=[password]’);
/* authentication */
insert into ofProperty (name, propValue) values (‘jdbcAuthProvider.passwordSQL’, ‘select password from user where user_name=?’);
insert into ofProperty (name, propValue) values (‘jdbcAuthProvider.passwordType’, ‘plain’);
insert into ofProperty (name, propValue) values (‘admin.authorizedJIDs’, ‘some_user@some_jid, some_other_user@some_jid’);
/* user */
insert into ofProperty (name, propValue) values (‘jdbcUserProvider.loadUserSQL’, ‘select user_name, email from user where user_name=?’);
insert into ofProperty (name, propValue) values (‘jdbcUserProvider.userCountSQL’, ‘select count(*) from user’);
insert into ofProperty (name, propValue) values (‘jdbcUserProvider.allUsersSQL’, ‘select user_name from user’);
insert into ofProperty (name, propValue) values (‘jdbcUserProvider.searchSQL’, ‘select user_name from user where’);
insert into ofProperty (name, propValue) values (‘jdbcUserProvider.usernameField’, ‘user_name’);
insert into ofProperty (name, propValue) values (‘jdbcUserProvider.nameField’, ‘user_name’);
insert into ofProperty (name, propValue) values (‘jdbcUserProvider.emailField’, ‘email’);
I hope this helps!
–Marc