MySQL Database Schema Upgrade for Tigase 4.0

Artur Hefczyc <> v2.0, June 2014: Reformatted for AsciiDoc. :toc: :numbered: :website: :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:

  • Compliance with the XMPP RFC which says that each part of JID may have up to 1023 characters. We store in the database user JIDs without resource name thus the maximum possible size of the user id is 2047. There aren’t really JIDs that long yet but we experienced quite long JIDs in a few installations already so we decided to prepare Tigase to accept any JID allowed by RFC.
  • Performance and flexibility - the Tigase server now accesses database using stored procedures. This allows for any database storage format and it doesn’t really matter for the Tigase server what is the database schema how data is organized inside. What it needs is just bunch of stored procedures to access the data. This allows for much more flexibility in storing user data as well as much easier integration with third-party systems and also organize data in more efficient way.

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:

  1. You really, really have to do the DB backup (database dump) before upgrading. If you don’t you might not be able to revert database on your own. Contact me in case of problems.
  2. In case of error: ERROR 1419 (HY000) at line 31 in file: 'database/mysql-schema-4-sp.schema': You do not have the SUPER privilege and binary logging is enabled (you *might want to use the less safe log_bin_trust_function_creators variable)* Restore the database following description found below and run the update again as MySQL super user.
  3. 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';


  1. tigasedb is a database name
  2. tigase_user is a database user name
  3. mypass is database user password

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!