Artur Hefczyc <artur.hefczyc@tigase.net> v2.0, June 2014: Reformatted for AsciiDoc. :toc: :numbered: :website: http://tigase.net :Date: 2010-01-06 20:18
For number of reasons the database schema had to be changed for Tigase server version 4.0. The most important are:
Therefore when you run the Tigase server now it may (depending on what exact SVN revision you use) refuse to start if it detects that the database schema is not updated. If it happens just follow steps below to update the database schema and start the server again. Updating of the database schema is very easy and almost fully automated process. Just follow the steps below and you should be able to run new version of the Tigase server in a few minutes or even seconds depending on your database size. It takes around 7 minutes to update database with 200k user accounts on an average machine.
Note. Do not update the database schema before the Tigase server tells you to do so. And do a database backup before starting the schema update.
Please note. I have done a few schema upgrades already in a different configurations and here are a few tips which might be useful if something goes wrong:
The following error may manifest itself in many ways from the NullPointerException in the Tigase server log file to message like this: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types. The best solution to this is to grant proper permissions to this user. Enter the MySQL command line mode as MySQL super user:
$ mysql -u root -proot_passwd mysql mysql> GRANT SELECT, INSERT, UPDATE ON \`mysql\`.\`proc\` TO 'tigase_user'@'localhost'; mysql> GRANT SELECT, INSERT, UPDATE ON \`mysql\`.\`proc\` TO 'tigase_user'@'%'; mysql> GRANT SELECT, INSERT, UPDATE ON \`mysql\`.\`proc\` TO 'tigase_user'; mysql> FLUSH PRIVILEGES; $
Assumptions:
First things first - make a database backup:
mysqldump -u tigase_user -pmypass tigasedb > tigasedb_dump.sql
If you need to restore database for any reason execute following commands:
msyqladmin -u tigase_user -pmypass drop tigasedb mysqladmin -u tigase_user -pmypass create tigasedb mysql -u tigase_user -pmypass tigasedb < tigasedb_dump.sql
Note! You may be required to use root user and his password to execute mysqladmin commands. Ok we have the database backup and we know how to restore it. Now we can run schema upgrade script:
mysql -u tigase_user -pmypass tigasedb < database/mysql-schema-upgrade-to-4.sql
The script should generate output like this:
Droping index for user_id column Resizing user_id column to 2049 characters to comply with RFC Creating a new index for user_id column for first 765 bytes of the field Adding sha1_user_id column Adding user_pw column Adding last_login column Adding last_logout column Adding online_status column Adding failed_logins column Adding account_status column Creating a new index for user_pw column Creating a new index for last_login column Creating a new index for last_logout column Creating a new index for account_status column Creating a new index for online_status column Resizing node column to 255 characters Changing pval column type to mediumtext Loading stored procedures definitions Setting passwords encoding in the database Converting database to a new format Creating a new index for sha1_user_id column Setting schema version to 4.0 All done, database ready to use!