This is a repository for Schema updates in case you have to upgrade from older installations.
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:
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 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:
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.
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
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
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.
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
The schema upgrade is very simple and safe but make sure the current database schema is in version 4.0.
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.
Now we can run schema upgrade script
mysql -u tigase_user -pmypass tigasedb < database/mysql-schema-upgrade-to-5-1.sql
The schema upgrade is very simple and safe but make sure the current database schema is in version 4.0.
Assumptions:
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
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;
Schema Upgrades for Tigase server version 4.x.
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 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:
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:
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!