Prepare the MySQL database for the Tigase server
This guide describes how to prepare the MySQL database for connecting the Tigase server to it.
Basic setup
The MySQL database can be prepared in many ways. Most of Linux distributions contain tools which allow you to go through all steps from the shell command line. To make sure, however it works on all platforms the same way I show first how to do it under MySQL command line client.
Configuring from MySQL command line tool
Run the MySQL command line client in either Linux or MS Windows environment and enter following instructions:
- Create the database for the Tigase server:
mysql> create database tigasedb;
- Add the
tigase_useruser and grant him access to thetigasedbdatabase. Depending on how you plan to connect to the database (locally or over the network) use one of following commands or all if you are not sure:
Grant access to tigase_user connecting from any network address.mysql> GRANT ALL ON tigasedb.* TO tigase_user@'%' IDENTIFIED BY 'tigase_passwd';Grant access to tigase_user connecting from localhost.mysql> GRANT ALL ON tigasedb.* TO tigase_user@'localhost' IDENTIFIED BY 'tigase_passwd';Grant access to tigase_user connecting from local machine only.mysql> GRANT ALL ON tigasedb.* TO tigase_user IDENTIFIED BY 'tigase_passwd';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. To our knowledge best solution, right now, to this is to grant following permissions to this user. For the Tigase server version 4.x and later additional permissions must be granted for the database user: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';
And now you can update user permission changes in the database:mysql> FLUSH PRIVILEGES;
- Load database schema to initialize the Tigase server database space with the schema file corresponding to the Tigase version you use. First, switch to the database you have just created:
mysql> use tigasedb;
Assuming you run the mysql client in Linux from the Tigase installation directory. If you run the Tigase server all versions below 4.0:mysql> source database/mysql-schema-3.sql;
For the Tigase server version 4.x and later you have to use proper schema version:mysql> source database/mysql-schema-4.sql;
For the Tigase server version 5.0 and later you have to use proper schema version:mysql> source database/mysql-schema-5.sql;
For the Tigase server version 5.1 and later you have to use proper schema version:mysql> source database/mysql-schema-5-1.sql;
On Windows you have probably to enter the full path:mysql> source c:/Program Files/Tigase/database/mysql-schema-5.1.sql;
The initialization schema file should be also available locally indatabase/directory of your Tigase installation.
Configuring from the Linux shell command line
Follow steps below to prepare the MySQL database:
- Create the database space for the Tigase server:
mysqladmin -p create tigasedb
- Add the
tigase_useruser and grant him access to thetigasedbdatabase. Depending on how you plan to connect to the database (locally or over the network) use one of following commands or all if you are not sure:
Grant access to tigase_user connecting from any network address.echo "GRANT ALL ON tigasedb.* TO tigase_user@'%' \ IDENTIFIED BY 'tigase_passwd'; \ FLUSH PRIVILEGES;" | mysql -u root -pdbpass mysqlGrant access to tigase_user connecting from localhost.echo "GRANT ALL ON tigasedb.* TO tigase_user@'localhost' \ IDENTIFIED BY 'tigase_passwd'; \ FLUSH PRIVILEGES;" | mysql -u root -pdbpass mysqlGrant access to tigase_user connecting from local machine only.echo "GRANT ALL ON tigasedb.* TO tigase_user \ IDENTIFIED BY 'tigase_passwd'; \ FLUSH PRIVILEGES;" | mysql -u root -pdbpass mysql - Load database schema to initialize the Tigase server from the file that corresponds to the Tigase version you want to use. If you run the Tigase server all versions below 4.0:
mysql -u dbuser -p tigasedb < database/mysql-schema-3.sql
For the Tigase server version 4.x and later you have to use proper schema version:mysql -u dbuser -p tigasedb < database/mysql-schema-4.sql;
For the Tigase server version 5.0 and later you have to use proper schema version:mysql -u dbuser -p tigasedb < database/mysql-schema-5.sql;
For the Tigase server version 5.1 and later you have to use proper schema version:mysql -u dbuser -p tigasedb < database/mysql-schema-5-1.sql;
Above commands should be executed from the main Tigase directory. The initialization schema file should be also available locally indatabase/directory of your Tigase installation.
Configuring MySQL for UTF-8 support
In the my.conf put following lines:
[mysql] default-character-SET=utf8 [client] default-character-SET=utf8 [mysqld] init_connect='SET collation_connection = utf8_general_ci; SET NAMES utf8;' character-set-server=utf8 default-character-SET=utf8 collation-server=utf8_general_ci skip-character-set-client-handshake
Then connect to the database and from the command line shell check settings:
SHOW VARIABLES LIKE 'character_set_database'; SHOW VARIABLES LIKE 'character_set_client';
If any of these shows something else then 'utf8' then you have to correct it:
ALTER DATABASE tigasedb DEFAULT CHARACTER SET utf8;
You can now also test your database installation if it accepts UTF-8 data. Best way is just to create an account with UTF-8 characters:
call TigAddUserPlainPw('żółw@some.domain.com', 'żółw');
And then check of the account has been created:
SELECT * FROM tig_users WHERE user_id = 'żółw@some.domain.com';
If the last command gives you no results it means there is still something wrong with settings. You might also check you shell settings to make sure your command line shell supports UTF-8 characters and passes them correctly to MySQL:
export LANG=en_US.UTF-8 export LOCALE=UTF-8 export LESSCHARSET='utf-8'
It seems to me that MySQL 5.0.x also needs an extra parameters in the connection string: '&useUnicode=true&characterEncoding=UTF-8' while MySQL 5.1.x seems to not need it but it doesn't hurt to have it for both versions. You have to edit 'etc/init.properties' file and append this to the database connection string.
For MySQL 5.1.x, however, you need also updated code for all database stored procedures and functions used by the Tigase. They are updated for Tigase version 4.4.x and up, for the time being if you use older version of the Tigase server you can reload stored procedures using the file from SVN.
Other MySQL setting worth considering
There is a number of other options useful, especially for a performance reasons. Please note, you have to review them as some of them may impact data reliability and are useful for performance or load tests installations only.
# InnoDB seems to be a better choice # so lets make it a default DB engine default-storage-engine = innodb
Some the general MySQL settings which mainly affect performance:
key_buffer = 64M max_allowed_packet = 32M sort_buffer_size = 64M net_buffer_length = 64K read_buffer_size = 16M read_rnd_buffer_size = 16M thread_stack = 192K thread_cache_size = 8 query_cache_limit = 10M query_cache_size = 64M
InnoDB specific settings:
# Keep data in a separate file for each table innodb_file_per_table = 1 # Allocate memory for data buffers innodb_buffer_pool_size = 1000M innodb_additional_mem_pool_size = 100M # A location of the MySQL database innodb_data_home_dir = /home/databases/mysql/ innodb_log_group_home_dir = /home/databases/mysql/ # The main thing here is the 'autoextend' property # without it your data file may reach maximum size and # no more records can be added to the table. innodb_data_file_path = ibdata1:10M:autoextend innodb_log_file_size = 10M innodb_log_buffer_size = 32M # Some other performance affecting settings innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 16
Timezone settings. Tigase use TIMESTAMP fields for several fields. Values of such fields are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. In some cases timezone conversion could cause problems and therefore it might be helpful to avoid it. The simplest way to achieve it is by setting timezone to +00:00:
time-zone = '+00:00'
I am certainly not a database expert nor MySQL expert and I do not pretend to be one. So any comments or suggestions you may have are very welcome and appreciated.
- Add new comment
- 16510 reads





>>For the Tigase server
>>For the Tigase server version 4.x additional permissions must be granted for the database user:
I have also found it necessary for Tigase 5.1
Thank you, I've corrected the
Thank you, I've corrected the article.
On windows, for mysql
On windows, for mysql install, you should also specify that you have to run mysql client from path .\database\.. otherwise other sql scripts won't be found.
It is assumed (for all
It is assumed (for all platforms), that all commands are executed from the main Tigase directory.
For the mysql UTF-8 setting
For the mysql UTF-8 setting default-character-SET=utf8 has been deprecated in favour of character-set-server=utf8
see here:
http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_character-set-server
This should be stated in docs above.
You are correct, but I think
You are correct, but I think it should also contain information what works for which version of MySQL.
Hi Regarding setup of Tigase
Hi
Regarding setup of Tigase in a Cluster Mode.
Can I connect all cluster Nodes to the same database?
Or do I have to have a database behind each node?
And..
Setup of a cluster with services as a external components like in: http://www.tigase.org/content/load-balancing-external-components-cluster-mode
My Question is if I f.e. have a 3 node cluster, and 2 extra nodes to loadbalance a MUC and PUB sub component... do I need to have a sepperate mysql-server for all of the 5 nodes or can I use one mysql server?
Yes, you can connect all
Yes, you can connect all nodes to the same database even when you use external components.
I would say you MUST connect
I would say you MUST connect all Tigase cluster nodes to the same database. The only way you could have separate database for each node is if you have a way to synchronize databases.
The Tigase server does not keep data in sync. This is database level stuff.
You can also connect external components to the same database.
In most cases, where we setup Tigase in a cluster mode, even with external components we install MySQL on two servers in master-slave mode and connect the whole Tigase installation to this MySQL.