This example shows as you can implement DTP (Distributed Transaction Processing) with two Resource Managers of the same type (MySQL) coordinated by the LIXA Transaction Manager. It's strongly suggested you have played with the examples previously shown in this chapter (see Chapter 5, Developing C Application Programs using TX (Transaction Demarcation) interface) before starting this more complex one.
Note
If you did not yet installed the software provided by MySQL, please refer to the official MySQL site to download the software and to pick-up the information necessary to install and configure the database. This manual does not give you information related to MySQL technology: it is assumed you already installed and configured the database.
Important
The LIXA software must be configured to support the MySQL resource managers as explained in the section called “Linking third party resource managers”. As a little hint, you should configure LIXA as below:
./configure --with-mysql
Please don't forget you must compile and install every time you re-configure.
Please follow the instructions explained
in the section called “MySQL/MariaDB Configuration” to set-up a running environment for MySQL server
in the section called “Starting the state server (lixad)” to start up the LIXA state server
To address a remote MySQL server some additional steps must be completed. The example explained here uses this configuration:
IP address of the server running lixad (LIXA state server), application program and local MySQL server (reached using localhost
127.0.0.1address):192.168.1.2IP address of the server running the remote MySQL server:
192.168.1.3
Connect to server
192.168.1.3
and change the parameter
bind-address in the file
/etc/mysql/my.cnf to allow network reachability:
| [Host:192.168.1.3 /etc/mysql/my.cnf] |
# Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 bind-address = 192.168.1.3 |
restart MySQL server:
| [Host:192.168.1.3 terminal session] |
tiian@ubuntu:~$ sudo /etc/init.d/mysql restart [sudo] password for tiian: * Stopping MySQL database server mysqld [ OK ] * Starting MySQL database server mysqld [ OK ] * Checking for corrupt, not cleanly closed and upgrade needing tables. |
connect to that MySQL server from the remote host and grant access to
user lixa from the server
192.168.1.2:
| [Host:192.168.1.3 terminal session] |
tiian@mojan:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.0.51a-3ubuntu5.8 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> GRANT ALL ON lixa.* TO 'lixa'@'192.168.1.2'; Query OK, 0 rows affected (0.00 sec) |
now you should be able to connect to the server running at
192.168.1.3
from the server
192.168.1.2:
| [Host:192.168.1.2 terminal session] |
tiian@ubuntu:~$ mysql -h 192.168.1.3 -u lixa lixa Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.0.51a-3ubuntu5.8 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. |
and you must create the table necessary for the example program:
| [MySQL (remote) terminal session] |
mysql> CREATE TABLE authors (id INTEGER NOT NULL PRIMARY KEY, last_name TEXT, first_name TEXT) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> DESCRIBE authors; +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | last_name | text | YES | | NULL | | | first_name | text | YES | | NULL | | +------------+---------+------+-----+---------+-------+ 3 rows in set (0.01 sec) |
Prepare the client (Application Program) using the below commands (gcc command was splitted on several lines using \ to help readability, but you may use a single line):
| [Shell terminal session] |
tiian@ubuntu:~$ mkdir tmp tiian@ubuntu:~$ cd tmp tiian@ubuntu:~/tmp$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/example11_mys_mys.c . tiian@ubuntu:~/tmp$ gcc example11_mys_mys.c $(/opt/lixa/bin/lixa-config -c -f -m -d) \ > $(mysql_config --include --libs_r) -o example11_mys_mys |
Verify the executable produced by gcc:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ldd example11_mys_mys
linux-gate.so.1 => (0xb777d000)
liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0xb7762000)
liblixamy.so.0 => /opt/lixa/lib/liblixamy.so.0 (0xb775a000)
libmysqlclient_r.so.15 => /usr/lib/libmysqlclient_r.so.15 (0xb7569000)
libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb741a000)
libgmodule-2.0.so.0 => /usr/lib/libgmodule-2.0.so.0 (0xb7416000)
libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb7412000)
libgthread-2.0.so.0 => /usr/lib/libgthread-2.0.so.0 (0xb740d000)
librt.so.1 => /lib/tls/i686/cmov/librt.so.1 (0xb7403000)
libglib-2.0.so.0 => /usr/lib/libglib-2.0.so.0 (0xb7352000)
libxml2.so.2 => /usr/lib/libxml2.so.2 (0xb7232000)
liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0xb721d000)
libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb7205000)
libcrypt.so.1 => /lib/tls/i686/cmov/libcrypt.so.1 (0xb71d3000)
libnsl.so.1 => /lib/tls/i686/cmov/libnsl.so.1 (0xb71ba000)
libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb7195000)
libz.so.1 => /usr/lib/libz.so.1 (0xb7180000)
/lib/ld-linux.so.2 (0xb777e000)
libpcre.so.3 => /usr/lib/libpcre.so.3 (0xb7159000)
libuuid.so.1 => /lib/libuuid.so.1 (0xb7155000)
|
Set-up the necessary environment variables:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE tiian@ubuntu:~/tmp$ export LIXA_PROFILE=MYS_STA_MYS_STA tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE MYS_STA_MYS_STA |
It is suggested to set the necessary environment variable
(LIXA_PROFILE) in your
profile if you are going to execute the programs many times.
We set LIXA_PROFILE to value
“MYS_STA_MYS_STA”, looking at
/opt/lixa/etc/lixac_conf.xml:
<profile name="MYS_STA_MYS_STA">
<sttsrvs>
<sttsrv>local_1</sttsrv>
</sttsrvs>
<rsrmgrs>
<rsrmgr>MySQL_stareg</rsrmgr>
<rsrmgr>MySQL2_stareg</rsrmgr>
</rsrmgrs>
</profile>
the profile references two Resource Managers: “MySQL_stareg” and “MySQL2_stareg” looking again at the config file:
<rsrmgr name="MySQL_stareg" switch_file="/opt/lixa/lib/switch_mysql_stareg.so" xa_open_info="host=localhost,user=lixa,passwd=,db=lixa,client_flag=0" xa_close_info="" />
<rsrmgr name="MySQL2_stareg" switch_file="/opt/lixa/lib/switch_mysql_stareg.so" xa_open_info="host=192.168.1.3,user=lixa,passwd=,db=lixa,client_flag=0" xa_close_info="" />
we can discover how our application will access the resource managers [37].
It is suggested to open three different terminals: the first one connected to “lixa” local MySQL database, the second one connected to “lixa” remote MySQL database and the third one pointing to the directory where the compiled program example11_mys_mys lives.
| [MySQL (local) terminal session] |
tiian@ubuntu:~$ mysql -h localhost -u lixa lixa Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 37 Server version: 5.0.51a-3ubuntu5.8 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> |
| [MySQL (remote) terminal session] |
tiian@ubuntu:~$ mysql -h 192.168.1.3 -u lixa lixa Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.0.51a-3ubuntu5.8 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. |
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ls -la total 168 drwxr-xr-x 2 tiian tiian 4096 2011-11-13 21:04 . drwxr-xr-x 40 tiian tiian 4096 2011-11-13 19:09 .. -rwxr-xr-x 1 tiian tiian 9201 2011-11-13 21:04 example11_mys_mys -rw-r--r-- 1 tiian tiian 3977 2011-11-13 21:04 example11_mys_mys.c |
Check the content of the (local) MySQL table (“authors”):
| [MySQL (local) terminal session] |
mysql> SELECT * FROM authors; Empty set (0.02 sec) |
Check the content of the (remote) MySQL table (“authors”):
| [MySQL (remote) terminal session] |
mysql> SELECT * FROM authors; Empty set (0.01 sec) |
Insert a row in all the tables and check the contents of the tables after the transaction execution:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ./example11_mys_mys insert Inserting a row in MySQL(1) table... Inserting a row in MySQL(2) table... |
Now you can verify the content of the tables after the transaction:
| [MySQL (local) terminal session] |
mysql> SELECT * FROM authors; +----+-----------+------------+ | id | last_name | first_name | +----+-----------+------------+ | 1 | Foo | Bar | +----+-----------+------------+ 1 row in set (0.00 sec) |
| [MySQL (remote) terminal session] |
mysql> SELECT * FROM authors; +----+-----------+------------+ | id | last_name | first_name | +----+-----------+------------+ | 1 | Foo | Bar | +----+-----------+------------+ 1 row in set (0.01 sec) |
With the opposite command you can remove the rows from the tables:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ./example11_mys_mys delete Deleting a row from MySQL(1) table... Deleting a row from MySQL(2) table... |
and check the content of the tables again:
| [MySQL (local) terminal session] |
mysql> SELECT * FROM authors; Empty set (0.00 sec) |
| [MySQL (remote) terminal session] |
mysql> SELECT * FROM authors; Empty set (0.00 sec) |
Finally, you can verify the two phase commit protocol is running as expected. Insert a row in all the tables:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ./example11_mys_mys insert Inserting a row in MySQL(1) table... Inserting a row in MySQL(2) table... |
manually remove the row from the local MySQL server only:
| [MySQL (local) terminal session] |
mysql> SELECT * FROM authors; +----+-----------+------------+ | id | last_name | first_name | +----+-----------+------------+ | 1 | Foo | Bar | +----+-----------+------------+ 1 row in set (0.00 sec) mysql> DELETE FROM authors; Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM authors; Empty set (0.00 sec) |
try to insert the row again in all the tables:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ./example11_mys_mys insert Inserting a row in MySQL(1) table... Inserting a row in MySQL(2) table... INSERT INTO authors: 1062/Duplicate entry '1' for key 1 |
the first (local) INSERT is ok, while the second one can not be performed due to a duplicated key. Check the content of the tables again:
| [MySQL (local) terminal session] |
mysql> SELECT * FROM authors; Empty set (0.00 sec) |
| [MySQL (remote) terminal session] |
mysql> SELECT * FROM authors; +----+-----------+------------+ | id | last_name | first_name | +----+-----------+------------+ | 1 | Foo | Bar | +----+-----------+------------+ 1 row in set (0.01 sec) |
If you inspect the program source code you will discover what the program will do:
example11_mys_mys.c |
[...]
printf("Inserting a row in MySQL(2) table...\n");
if (mysql_query(conn2,
"INSERT INTO authors VALUES(1, 'Foo', 'Bar')")) {
fprintf(stderr, "INSERT INTO authors: %u/%s\n",
mysql_errno(conn2), mysql_error(conn2));
exit_nicely(conn1, conn2);
}
[...]
|
in case of error the program exits closing the connections...
XA protocol is a “two phase commit with presumed
rollback”: if the transaction does not complete
xa_prepare, the transaction will be rolled back.
An alternative way is the usage of tx_rollback():
you can rollback the current transaction and start a new one without
program (and connection) termination.
Clean up the (remote) table:
| [MySQL (remote) terminal session] |
mysql> DELETE FROM authors; Query OK, 1 row affected (0.04 sec) mysql> SELECT * FROM authors; Empty set (0.00 sec) |
[37]
The content of xa_open_info for MySQL is
described in
the section called “Some checks before program execution”.