Schema Updates
Prev | Chapter 7. Database Management | Next

## Schema Updates

This is a repository for Schema updates in case you have to upgrade from older installations.

- Tigase Server Schema v7.1 Updates Applies to v7.1.0 and v7.2.0

- Upgrades to v5.1 of Tigase

  1. Derby Database Schema Upgrade for Tigase 5.1
  2. MySQL Database Schema Upgrade for Tigase 5.1
  3. PostgreSQL Database Schema Upgrade for Tigase 5.1
- MySQL Database Schema Upgrade for Tigase 4.0

### Tigase Server Schema v7.1 Updates

#### FOR ALL USERS UPGRADING TO v7.1.0 FROM A v7.0.2 INSTALLATION

The schema has changed for the main database, and the pubsub repository. In order to upgrade to the new schemas, you will need to do the following:

1. Upgrade the Main database schema to v7.1 using the database/${DB_TYPE}-schema-upgrade-to-7-1.sql file
2. Upgrade the Pubsub Schema to v3.1.0 using the database/${DB_TYPE}-pubsub-schema-3.1.0.sql file
3. Upgrade the Pubsub Schema to v3.2.0 using the database/${DB_TYPE}-pubsub-schema-3.2.0.sql file

All three commands may be done at the same time in that order, it is suggested you make a backup of your current database to prevent data loss.

#### Tigase Schema Change for v7.1

Tigase has made changes to its database to include primary keys in the tig_pairs table to improve performance of the Tigase server. This is an auto-incremented column for Primary Key items appended to the previous schema.

You MUST update your database to be compliant with the new v7.1 schema. If you do not, Tigase will not function properly.

This change will affect all users of Tigase using v7.1.0 and newer.

If you are installing a new version of v7.1.0 on a new database, the schema should automatically be installed.

First, shut down any running instances of Tigase to prevent conflicts with database editing. Then from command line use the DBSchemaLoader class to run the -schema-upgrade-to-7.1.sql file to the database. The command is as follows:

In a linux environment

    java -cp "jars/*" tigase.util.DBSchemaLoader -dbHostname ${HOSTNAME} -dbType ${DB_TYPE} -rootUser ${ROOT_USER} -dbPass ${DB_USER_PASS} -dbName ${DB_NAME} -schemaVersion ${DB_VERSION} -rootPass ${ROOT_USER_PASS} -dbUser ${DB_USER}  -adminJID "${ADMIN_JID}" -adminJIDpass ${ADMIN_JID_PASS}  -logLevel ALL -file database/${DB_TYPE}-schema-upgrade-to-7-1.sql

In a windows environment

    java -cp jars/* tigase.util.DBSchemaLoader -dbHostname ${HOSTNAME} -dbType ${DB_TYPE} -rootUser ${ROOT_USER} -dbPass ${DB_USER_PASS} -dbName ${DB_NAME} -schemaVersion ${DB_VERSION} -rootPass ${ROOT_USER_PASS} -dbUser ${DB_USER}  -adminJID "${ADMIN_JID}" -adminJIDpass ${ADMIN_JID_PASS}  -logLevel ALL -file database/${DB_TYPE}-schema-upgrade-to-7-1.sql

All variables will be required, they are as follows:

- ${HOSTNAME} - Hostname of the database you wish to upgrade.
- ${DB_TYPE} - Type of database [derby, mysql, postgresql, sqlserver].
- ${ROOT_USER} - Username of root user.
- ${ROOT_USER_PASS} - Password of specified root user.
- ${DB_USER} - Login of user that can edit database.
- ${DB_USER_PASS} - Password of the specified user.
- ${DB_NAME} - Name of the database to be edited.
- ${DB_VERSION} - In this case, we want this to be 7.1.
- ${ADMIN_JID} - Bare JID of a database user with admin privileges. Must be contained within quotation marks.
- ${ADMIN_JID_PASS} - Password of associated admin JID.

Please note that the SQL file for the update will have an associated database with the filename. i.e. postgresql-update-to-7.1.sql for postgresql database.

A finalized command will look something like this:

    java -cp "jars/*" tigase.util.DBSchemaLoader -dbHostname localhost -dbType mysql -rootUser root -rootPass root -dbUser admin -dbPass admin -schemaVersion 7.1 -dbName Tigasedb -adminJID "admin@local.com" -adminJIDPass adminpass -logLevel ALL -file database/mysql-schema-upgrade-to-7.1.sql

Once this has successfully executed, you may restart you server. Watch logs for any db errors that may indicate an incomplete schema upgrade.

#### Changes to Pubsub Schema

Tigase has had a change to the PubSub Schema, to upgrade to PubSub Schema v7.1 without having to reform your databases, use this guide to update your databases to be compatible with the new version of Tigase.

NOTE Current PubSub Schema is v3.2.0, you will need to repeat these instructions for v3.1.0 and then v3.2.0 before you run Tigase V7.1.0.

The PubSub Schema has been streamlined for better resource use, this change affects all users of Tigase. To prepare your database for the new schema, first be sure to create a backup! Then apply the appropriate PubSub schema to your MySQL and it will add the new storage procedure.

All these files should be in your /database folder within Tigase, however if you are missing the appropriate files, use the links below and place them into that folder.

The MySQL schema can be found Here.

The Derby schema can be found Here.

The PostGRESQL schema can be found Here.

The MS SQL schema can be found Here.

The same files are also included in all distributions of v7.1.0 in [tigaseroot]/database/ . All changes to database schema are meant to be backward compatible.

You can use a utility in Tigase to update the schema using the following command from the Tigase root: Linux

    java -cp "jars/*" tigase.util.DBSchemaLoader

or from a Windows environment

    java -cp jars/* tigase.util.DBSchemaLoader

NOTE: Some variation may be necessary depending on how your java build uses -cp option

Use the following options to customize. Options in bold are required.
  - [-dbType database_type {derby, mysql, postgresql, sqlserver}]
  - [-schemaVersion schema version {4, 5, 5-1} ]
  - [-dbName database name]
  - [-dbHostname database hostname] (default is localhost)
  - [-dbUser tigase username]
  - [-dbPass tigase user password]
  - [-rootUser database root username]
  - [-rootPass database root password]
  - [-file path to sql schema file]
  - [-query sql query to execute]
  - [-logLevel java logger Level]
  - [-adminJID comma separated list of admin JIDs]
  - [-adminJIDpass password (one for all entered JIDs]

Arguments take following precedent: query, file, whole schema

As a result your final command should look something like this:

    java -cp "jars/*" tigase.util.DBSchemaLoader -dbType mysql -dbName tigasedb -dbUser root -dbPass password -file database/mysql-pubsub-schema-3.1.0.sql

### Tigase 5.1 Database Schema Upgrade

We had to make a small change to the database schema for Tigase version 5.1.0.

It does not affect data or data structure, only the way in which some data is accessed in database. We added one more stored procedure which has to be installed in database if you upgrade your installation from a previous Tigase version

The schema upgrade is very simple and safe but make sure the current database schema is in version 4.0. If you happen to use ancient version of Tigase earlier than 4.0 and you want to upgrade to 5.1, you have to run 4.0 upgrade script first.

Please follow detailed guide for the database applicable to your installation: Derby, MySQL, PostgreSQL.

- Derby Database Schema Upgrade for Tigase 5.1
- MySQL Database Schema Upgrade for Tigase 5.1
- PostgreSQL Database Schema Upgrade for Tigase 5.1

### Derby Database Schema Upgrade for Tigase 5.1

The schema upgrade is very simple and safe but make sure the current database schema is in version 4.0.

First things first - make a database backup:

    tar -czf derbyDB.tar.gz /path/to/derbyDB

If you need to restore database for any reason simply extract files from the backup archive:

    rm -rf /path/to/derbyDB
    tar -xf derbyDB.tar.gz

Now we can run schema upgrade script

    java -Dij.protocol=jdbc:derby: -Dij.database="/path/to/derbyDB" \
    		-Dderby.system.home=`pwd` \
    		-cp libs/derby.jar:libs/derbytools.jar:jars/tigase-server.jar \
    		org.apache.derby.tools.ij database/postgresql-schema-upgrade-to-5-1.sql

### MySQL Database Schema Upgrade for Tigase 5.1

The schema upgrade is very simple and safe but make sure the current database schema is in version 4.0.

Assumptions:

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.

Now we can run schema upgrade script

    mysql -u tigase_user -pmypass tigasedb < database/mysql-schema-upgrade-to-5-1.sql

### PostgreSQL Database Schema Upgrade for Tigase 5.1

The schema upgrade is very simple and safe but make sure the current database schema is in version 4.0.

Assumptions:

- tigasedb is a database name
- tigase_user is a database user name
- admin_db_user is database admin user name

First things first - make a database backup:

    pg_dump -U tigase_user -W tigasedb > tigasedb_dump.sql

If you need to restore database for any reason execute following commands:

    dropdb -U admin_db_user -W tigasedb
    createdb -U admin_db_user -W -O tigase_user tigasedb
    psql -U tigase_user -W tigasedb < tigasedb_dump.sql

Now we can run schema upgrade script

    psql -q -U tigase_user -W tigasedb -f database/postgresql-schema-upgrade-to-5-1.sql

### Tigase Database Minor but Useful Schema Change in Version 5.1.0

We have recently made a simple but very useful change to the DB schema in Tigase. It preserves backward compatibility and so your existing schema does not need to be changed, even if you upgrade your installation to the most recent 5.1.0 version.

However, the change is can help to track new accounts creation, therefore this article shows how to make modifications to you schema manually.

The change is related to adding a new field: acc_create_time which stores exact time of user account creation. The time is recorded automatically by the database when a new record in the user table is created. This allows for no extra overhead or resource usage on Tigase.

Along with the new field, there are slight modifications to 2 other fields: last_login and last_logout.

Here is how it looks now:

    -- Time the account has been created
    acc_create_time timestamp DEFAULT CURRENT_TIMESTAMP,
    -- Time of the last user login
    last_login timestamp DEFAULT 0,
    -- Time of the last user logout
    last_logout timestamp DEFAULT 0,

You can easily update your schema with above changes using a simple SQL query. Enter the MySQL shell and copy-paste following query:

    alter table tig_users
      modify last_login timestamp DEFAULT 0,
      modify last_logout timestamp DEFAULT 0,
      add acc_create_time timestamp DEFAULT CURRENT_TIMESTAMP;

Please note, after executing query above the column acc_create_time will have a null value. To populate the entry for existing records just copy last_login value to acc_create_time:

    update tig_users set acc_create_time = last_login;

### Tigase Server Version 4.x

Schema Upgrades for Tigase server version 4.x.

- MySQL Database Schema Upgrade for Tigase 4.0

### MySQL Database Schema Upgrade for Tigase 4.0

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 names 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 it was 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 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 as well as 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 the new version of Tigase server in a few minutes or even seconds depending on your database size. It took around 7 minutes to update our database with 200k user accounts on an average machine.

Note. Do not update the database schema before Tigase server tells you to do so. Be sure to do a database backup before starting the schema update.

Please note. I have done a few schema upgrades already in a few 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.
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 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:

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 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!

Prev | Up | Next
Importing Existing Data | Home | Chapter 8. Configuring the Tigase Server to Load a Component