LIXA extension is useful to perform Distributed Transaction Processing with an Application Program that uses two or more Resource Managers. It can be used with an Application Program that uses one Resource Manager, but it gives no advantages if compared with the native transactional support supplied by the Resource Manager itself.
Some examples will show you some configurations with only one Resource Manager: they are provided as an introducing step to more complex examples.
If you compiled LIXA extension as a core extension, there's no need to specify something like this in your PHP source code:
| [PHP source code] |
<?php
include("lixa.php")
...
?>
|
where lixa.php is the file provided in
ext/php LIXA source directory.
If you compiled LIXA as a stand-alone extension you need to place
lixa.php file in a suitable directory and
configure your php.ini to allow LIXA extension
shared object retrieval using something like:
| [php.ini config file] |
extension=/path/to/php/extensions/lixa/modules/lixa.so |
This manual will not talk about this scenario because the standard procedure explained in the section called “Installation” produces a core extension by default.
The easiest LIXA PHP example might probably be the following one:
| [PHP source code] |
<?php
$rc=tx_open();
print "tx_open() --> $rc\n";
$rc=tx_close();
print "tx_close() --> $rc\n";
?>
|
You can copy the above source code in file foo.php
and execute it using something like:
| [Shell terminal session] |
tiian@mojan:~/tmp$ /opt/php-5.4-lixa/bin/php foo.sh tx_open() --> 0 tx_close() --> 0 |
If your LIXA state server (lixad) was not up and running, you would get something like this:
| [Shell terminal session] |
tiian@mojan:~/tmp$ /opt/php-5.4-lixa/bin/php foo.sh tx_open() --> -7 tx_close() --> -7 |
and you can activate your LIXA state server (and restart the example) with something like this:
| [Shell terminal session] |
tiian@mojan:~/tmp$ sudo su - lixa lixa@mojan:~$ /opt/lixa/sbin/lixad --daemon lixa@mojan:~$ ps -ef|grep lixad lixa 7388 1 0 22:44 ? 00:00:00 /opt/lixa/sbin/lixad --daemon lixa 7393 7378 0 22:44 pts/3 00:00:00 grep lixad lixa@mojan:~$ exit logout tiian@mojan:~/tmp$ /opt/php-5.4-lixa/bin/php foo.sh tx_open() --> 0 tx_close() --> 0 |
A second example can show how TX constants are defined; change your
foo.php source file as below:
| [PHP source code] |
<?php
$rc=tx_open();
print "tx_open() --> $rc\n";
$rc=tx_close();
print "tx_close() --> $rc\n";
$tx_ok=TX_OK;
$tx_fail=TX_FAIL;
echo "TX_OK=" . $tx_ok . "\tTX_FAIL=" . $tx_fail . "\n";
?>
|
And execute it again:
| [Shell terminal session] |
tiian@mojan:~/tmp$ /opt/php-5.4-lixa/bin/php foo.sh tx_open() --> 0 tx_close() --> 0 TX_OK=0 TX_FAIL=-7 |
All the stuff defined in [TXspec] (and available
in C header file tx.h) is available from PHP as
well as from C. File lixa.php define the
functions and the constants provided by LIXA PHP extension.
Please move to directory ext/php/examples
and take a look to example01_lixa.php: it's a
simple 4 steps program that tries to:
open (connect to) all the Resource Managers
start a new transaction
commit the transaction started before
close (disconnect from) all the Resource Managers
Take a look to the source code:
| [Terminal shell session] |
tiian@mojan:~/lixa/ext/php/examples$ cat example01_lixa.php
<?php
print "Trying to open all the Resource Managers with tx_open()...\n";
$rc=tx_open();
if (TX_OK != $rc)
exit("tx_open returned rc=$rc, can't going on!\n");
print "Trying to start a new transaction with tx_begin()...\n";
$rc=tx_begin();
if (TX_OK != $rc)
exit("tx_begin returned rc=$rc, can't going on!\n");
print "Trying to commit the transaction with tx_commit()...\n";
$rc=tx_commit();
if (TX_OK != $rc)
exit("tx_commit returned rc=$rc, can't going on!\n");
print "Trying to close all the Resource Managers with tx_close()...\n";
$rc=tx_close();
if (TX_OK != $rc)
exit("tx_close returned rc=$rc, can't going on!\n");
print "Execution completed!\n";
?>
|
It's time to execute this simple program without a specific value for
LIXA_PROFILE and without an active LIXA state
server:
| [Terminal shell session] |
tiian@mojan:~/lixa/ext/php/examples$ echo $LIXA_PROFILE tiian@mojan:~/lixa/ext/php/examples$ ps -ef|grep lixad|grep -v grep tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example01_lixa.php Trying to open all the Resource Managers with tx_open()... tx_open returned rc=-7, can't going on! |
tx_open returns the value
-7 (TX_FAIL)
[2]
because the LIXA state server is not
available.
Start the LIXA state server and try it again:
| [Terminal shell session] |
tiian@mojan:~/lixa/ext/php/examples$ sudo su - lixa lixa@mojan:~$ /opt/lixa/sbin/lixad --daemon lixa@mojan:~$ ps -ef|grep lixad|grep -v grep lixa 9050 1 0 17:13 ? 00:00:00 /opt/lixa/sbin/lixad --daemon lixa@mojan:~$ exit logout tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example01_lixa.php Trying to open all the Resource Managers with tx_open()... Trying to start a new transaction with tx_begin()... Trying to commit the transaction with tx_commit()... Trying to close all the Resource Managers with tx_close()... Execution completed! |
Assuming your LIXA installation was build with MySQL support and MySQL server is not available, you can try what happens when you try to execute the program using a profile that needs MySQL:
| [Terminal shell session] |
tiian@mojan:~/lixa/ext/php/examples$ /opt/lixa/bin/lixa-config -r|grep MySQL MySQL yes tiian@mojan:~/lixa/ext/php/examples$ ps -ef|grep lixad|grep -v grep lixa 9176 1 0 17:17 ? 00:00:00 /opt/lixa/sbin/lixad --daemon tiian@mojan:~/lixa/ext/php/examples$ ps -ef|grep mysql|grep -v grep tiian@mojan:~/lixa/ext/php/examples$ export LIXA_PROFILE=MYS_STA tiian@mojan:~/lixa/ext/php/examples$ echo $LIXA_PROFILE MYS_STA tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example01_lixa.php Trying to open all the Resource Managers with tx_open()... tx_open returned rc=-6, can't going on! |
tx_open returns the value
-6 (TX_ERROR)
because one Resource Manager (MySQL) is not available; start the
MySQL Resource Manager and this will be the new behavior:
| [Terminal shell session] |
tiian@mojan:~/lixa/ext/php/examples$ sudo service mysql start mysql start/running, process 9246 tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example01_lixa.php Trying to open all the Resource Managers with tx_open()... Trying to start a new transaction with tx_begin()... Trying to commit the transaction with tx_commit()... Trying to close all the Resource Managers with tx_close()... Execution completed! |
You can activate LIXA tracing for PHP programs as described in [LIXArg]:
| [Terminal shell session] |
tiian@mojan:~/lixa/ext/php/examples$ export LIXA_TRACE_MASK=0x00002000
tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example01_lixa.php
Trying to open all the Resource Managers with tx_open()...
2012-04-06 17:26:15.866963 [9324/3077965600] lixa_xa_open
2012-04-06 17:26:15.867094 [9324/3077965600] lixa_xa_open: fd = 4
2012-04-06 17:26:15.867200 [9324/3077965600] lixa_xa_open: sending 298 bytes ('000292<?xml version="1.0" encoding="UTF-8" ?><msg level="0" verb="1" step="8"><client job="ddd45999a1b1c6edd8983fbe5ca89b89/127.0.0.1 " config_digest="ddd45999a1b1c6edd8983fbe5ca89b89" maint="0"/><rsrmgrs><rsrmgr rmid="0" dynamic="0" name="MySQL_stareg" xa_name="MySQL[LIXA]"/></rsrmgrs></msg>') to the server for step 8 (socket fd 4)
2012-04-06 17:26:15.867753 [9324/3077965600] lixa_xa_open: received 95 bytes from the server |<?xml version="1.0" encoding="UTF-8" ?><msg level="0" verb="1" step="16"><answer rc="0"/></msg>|
2012-04-06 17:26:15.881197 [9324/3077965600] lixa_xa_open: xa_open_entry('host=localhost,user=lixa,passwd=,db=lixa,client_flag=0', 0, 0x0) = 0
2012-04-06 17:26:15.881293 [9324/3077965600] lixa_xa_open: sending 257 bytes to the server for step 24
2012-04-06 17:26:15.881624 [9324/3077965600] lixa_xa_open/excp=12/ret_cod=0/errno=0
Trying to start a new transaction with tx_begin()...
2012-04-06 17:26:15.883409 [9324/3077965600] lixa_xa_start
2012-04-06 17:26:15.883557 [9324/3077965600] lixa_xa_start: sending 213 bytes to the server for step 8
2012-04-06 17:26:15.884574 [9324/3077965600] lixa_xa_start: receiving 95 bytes from the server |<?xml version="1.0" encoding="UTF-8" ?><msg level="0" verb="3" step="16"><answer rc="0"/></msg>|
2012-04-06 17:26:15.893130 [9324/3077965600] lixa_xa_start: xa_start_entry(xid, 0, 0x0) = 0
2012-04-06 17:26:15.893228 [9324/3077965600] lixa_xa_start: sending 210 bytes to the server for step 24
2012-04-06 17:26:15.893552 [9324/3077965600] lixa_xa_start/excp=10/ret_cod=0/errno=0
Trying to commit the transaction with tx_commit()...
2012-04-06 17:26:15.893678 [9324/3077965600] lixa_xa_end
2012-04-06 17:26:15.894033 [9324/3077965600] lixa_xa_end: xa_end_entry(xid, 0, 0x4000000) = 0
2012-04-06 17:26:15.894125 [9324/3077965600] lixa_xa_end: sending 208 bytes to the server for step 8
2012-04-06 17:26:15.898574 [9324/3077965600] lixa_xa_end: receiving 95 bytes from the server |<?xml version="1.0" encoding="UTF-8" ?><msg level="0" verb="4" step="16"><answer rc="0"/></msg>|
2012-04-06 17:26:15.898876 [9324/3077965600] lixa_xa_end/excp=8/ret_cod=0/errno=0
2012-04-06 17:26:15.898959 [9324/3077965600] lixa_xa_commit
2012-04-06 17:26:15.899004 [9324/3077965600] lixa_xa_commit: one_phase_commit=1
2012-04-06 17:26:15.899403 [9324/3077965600] lixa_xa_commit: xa_commit_entry(xid, 0, 0x40000000) = 0
2012-04-06 17:26:15.899512 [9324/3077965600] lixa_xa_commit: sending 218 bytes to the server for step 8
2012-04-06 17:26:15.899632 [9324/3077965600] lixa_xa_commit/excp=7/ret_cod=0/errno=0
2012-04-06 17:26:15.899687 [9324/3077965600] lixa_xa_forget
2012-04-06 17:26:15.899747 [9324/3077965600] lixa_xa_forget/excp=3/ret_cod=0/errno=0
Trying to close all the Resource Managers with tx_close()...
2012-04-06 17:26:15.899856 [9324/3077965600] lixa_xa_close
2012-04-06 17:26:15.899975 [9324/3077965600] lixa_xa_close: xa_close_entry('', 0, 0x0) = 0
2012-04-06 17:26:15.900058 [9324/3077965600] lixa_xa_close: sending 121 bytes to the server for step 8
2012-04-06 17:26:15.900145 [9324/3077965600] lixa_xa_close/excp=5/ret_cod=0/errno=0
Execution completed!
|
Trace messages go to stderr.
Important
The examples shown below need the set up explained in [LIXArg] (see chapter “Development/An example with MySQL”). You won't be able to execute the following examples without a right configured MySQL database.
Before trying LIXA and MySQL together you should try MySQL alone
with example02_mysqli.php example:
| [Terminal shell session] |
tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example02_mysqli.php Using mysqli direct API Result set: id=969, last_name=Ferrari, first_name=Christian |
The source code is quite easy to understand:
| [example02_mysqli.php source code] |
<?php
/* retrieve MySQL connection */
$mysqli = new mysqli("localhost", "lixa", "", "lixa", 3306);
if ($mysqli->connect_errno)
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
/* remove all the rows from the table */
if (!$mysqli->query("DELETE FROM authors;"))
echo "DELETE failed: (" . $mysqli->errno . ") " . $mysqli->error;
/* populate the table */
if (!$mysqli->query("INSERT INTO authors VALUES(969,'Ferrari','Christian');"))
echo "INSERT failed: (" . $mysqli->errno . ") " . $mysqli->error;
/* check the content of the table */
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set:\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
?>
|
Special attention must be reserved to this row:
| [example02_mysqli.php source code (fragment)] |
$mysqli = new mysqli("localhost", "lixa", "", "lixa", 3306);
|
because it will be changed to use LIXA as a Transaction Manager.
What we are performing now is similar to a supplied C language example; below there's a picture copied from [LIXArg] (take a look to C example before this one).
Start the LIXA state server (if it was not yet started):
| [Shell terminal session] |
tiian@mojan:~$ ps -ef|grep lixad|grep -v grep tiian@mojan:~$ sudo su - lixa [sudo] password for tiian: lixa@mojan:~$ /opt/lixa/sbin/lixad --daemon lixa@mojan:~$ ps -ef|grep lixad|grep -v grep lixa 4164 1 0 20:56 ? 00:00:00 /opt/lixa/sbin/lixad --daemon lixa@mojan:~$ exit logout |
Set up LIXA_PROFILE environment variable to choose
a profile with MySQL as Resource Manager and execute the PHP
source code:
| [Shell terminal session] |
tiian@mojan:~/lixa/ext/php/examples$ export LIXA_PROFILE=MYS_STA tiian@mojan:~/lixa/ext/php/examples$ echo $LIXA_PROFILE MYS_STA tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example03_lixa_mysqli.php tx_open(): 0 tx_begin(): 0 DELETE FROM authors; Result set: tx_commit(): 0 tx_begin(): 0 Result set: INSERT INTO authors... Result set: id=969, last_name=Ferrari, first_name=Christian tx_rollback(): 0 Result set: tx_begin(): 0 INSERT INTO authors... Result set: id=969, last_name=Ferrari, first_name=Christian tx_commit(): 0 Result set: id=969, last_name=Ferrari, first_name=Christian tx_close(): 0 |
read the source code, and the comments, to understand how to develop an Application Program coordinated by LIXA Transaction Manager with MySQL Resource Manager:
| [example03_lixa_mysqli.php source code] |
<?php
/* open all the Resource Managers (MySQL instance in this example) */
$rc=tx_open();
print "tx_open(): $rc\n";
/* retrieve MySQL connection from LIXA Transaction Manager instead
of MySQL directly: lixa// means:
database name=lixa
chooser type (pos/rmid)=none
id (0, 1, 2, ...)=none
and can be read as "retrieve the first available MySQL connection
established by the Transaction Manager using the current
LIXA profile" */
$mysqli = new mysqli("localhost", "lixa", "", "lixa//", 3306);
if ($mysqli->connect_errno)
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error . "\n";
/* start a new transaction coordinated by LIXA */
$rc=tx_begin();
print "tx_begin(): $rc\n";
/* remove all the rows from the table */
print "DELETE FROM authors;\n";
if (!$mysqli->query("DELETE FROM authors;"))
echo "DELETE failed: (" . $mysqli->errno . ") " . $mysqli->error;
/* check the content of the table */
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set:\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
/* commit the transaction (DELETE FROM ...) */
$rc=tx_commit();
print "tx_commit(): $rc\n";
/* start a new transaction coordinated by LIXA */
$rc=tx_begin();
print "tx_begin(): $rc\n";
/* check the content of the table */
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set:\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
/* populate the table */
print "INSERT INTO authors...\n";
if (!$mysqli->query("INSERT INTO authors VALUES(969,'Ferrari','Christian');"))
echo "INSERT failed: (" . $mysqli->errno . ") " . $mysqli->error;
/* check the content of the table */
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set:\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
/* rollback the transaction (INSERT INTO ...) */
$rc=tx_rollback();
print "tx_rollback(): $rc\n";
/* check the content of the table */
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set:\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
/* start a new transaction coordinated by LIXA */
$rc=tx_begin();
print "tx_begin(): $rc\n";
/* populate the table */
print "INSERT INTO authors...\n";
if (!$mysqli->query("INSERT INTO authors VALUES(969,'Ferrari','Christian');"))
echo "INSERT failed: (" . $mysqli->errno . ") " . $mysqli->error;
/* check the content of the table */
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set:\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
/* commit the transaction (INSERT INTO ...) */
$rc=tx_commit();
print "tx_commit(): $rc\n";
/* check the content of the table */
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set:\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
/* close all the Resource Managers */
$rc=tx_close();
print "tx_close(): $rc\n";
?>
|
I suggest you to verify the table is really populated and the debug messages produced by the above program are truthful:
| [Shell terminal session] |
tiian@mojan:~/lixa/ext/php/examples$ mysql -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 40 Server version: 5.1.61-0ubuntu0.10.04.1 (Ubuntu) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT * FROM authors; +-----+-----------+------------+ | id | last_name | first_name | +-----+-----------+------------+ | 969 | Ferrari | Christian | +-----+-----------+------------+ 1 row in set (0.00 sec) |
As explained in the previous example (see
the section called “An example with MySQL (mysqli driver)”) the
database part of mysqli
connection function
[3]
must be changed when using LIXA and MySQL:
| [mysqli detail] |
/* Standard mysqli invocation for a database named "lixa" */
$mysqli = new mysqli("localhost", "lixa", "", "lixa", 3306);
/* LIXA enabled mysqli invocation */
$mysqli = new mysqli("localhost", "lixa", "", "lixa//", 3306);
|
This is the syntax of the database part for a LIXA patched PHP mysqli module:
lixa/[pos|rmid]/[id]
lixa[4]: the Resource Manager (database) has been already opened by LIXA Transaction Manager with
tx_open()functionpos|rmid: specify if the following field (id) must be picked up counting only the MySQL Resource Managers specified in the profile (file
etc/lixac_conf.xml) or counting all the Resource Managers (rmid as specified by XA specification)id: specify the position of the choosen Resource Manager
Important
If the database string passed to a LIXA patched PHP mysqli
driver does not match the above syntax, it will passed
unchanged
to mysqli function and the returned
connection will not
partecipate to LIXA coordinated transactions.
Warning
If you mixed LIXA managed Resource Managers with LIXA unmanaged
Resource Managers, you would not obtain
a distributed transaction: only LIXA managed Resource Managers
obey to tx_begin(), tx_commit(), tx_rollback()
functions. You may mix them in your Application Program, but
it will be your job to guarantee data consistency among them.
Note
The string is case insensitive, but this documentation will ever use it using lowercase letters.
If you had a profile as the following one:
| [mysqli detail] |
<rsrmgrs>
<rsrmgr>MySQL_stareg1</rsrmgr>
<rsrmgr>MySQL_stareg2</rsrmgr>
<rsrmgr>PostgreSQL_stareg</rsrmgr>
<rsrmgr>MySQL_stareg3</rsrmgr>
<rsrmgr>OracleXE_stareg</rsrmgr>
<rsrmgr>MySQL_stareg4</rsrmgr>
</rsrmgrs>
|
you could refer to the Resource Managers with these strings:
lixa// --> MySQL_stareg1
lixa//0 --> MySQL_stareg1
lixa//1 --> MySQL_stareg2
lixa//2 --> MySQL_stareg3
lixa//3 --> MySQL_stareg4
lixa/pos/0 --> MySQL_stareg1
lixa/pos/1 --> MySQL_stareg2
lixa/pos/2 --> MySQL_stareg3
lixa/pos/3 --> MySQL_stareg4
lixa/rmid/0 --> MySQL_stareg1
lixa/rmid/1 --> MySQL_stareg2
lixa/rmid/2 --> not valid, it's not MySQL (it's PostgreSQL!)
lixa/rmid/3 --> MySQL_stareg3
lixa/rmid/4 --> not valid, it's not MySQL (it's Oracle!)
lixa/rmid/5 --> MySQL_stareg4
You can use “pos” (default behaviour) or “rmid”, as you prefer.
Important
The examples shown below need the set up explained in [LIXArg] (see chapter “Development/An example with two MySQL servers”). You won't be able to execute the following examples without two right configured MySQL databases.
You need at least: one host with LIXA state server and a first MySQL server, a second host with another MySQL server to try this example. Adapt the example to your own environment, remember it was implemented using these hosts:
LIXA state server, MySQL local server, host of the sample application server: 192.168.10.2
MySQL remote server: 192.168.10.1
Follow these steps after proper adaptation:
edit the configuration of your remote MySQL server (file
/etc/mysql/my.cnf, the path might be slightly different) because it must accept network connections from 192.168.10.2; replace rowbind-address = 127.0.0.1
with something like
#bind-address = 127.0.0.1 bind-address = 192.168.10.1
connect locally (from host 192.168.10.2) to the “remote” MySQL server as root and grant access from the other host with something like:
tiian@presanella:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.1.61-0ubuntu0.10.04.1 (Ubuntu) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> GRANT ALL ON lixa.* TO 'lixa'@'192.168.10.2'; Query OK, 0 rows affected (0.00 sec)
restart MySQL server using something like sudo /etc/init.d/mysql restart
check you can connect remotely (from 192.168.10.2) using something like mysql -h 192.168.10.1 -u lixa lixa
edit config file (host 192.168.10.2)
/opt/lixa/ect/lixac_conf.xml, locate “MySQL2_stareg” Resource Manager definition and change the default IP address with the IP address of your remote MySQL server (192.168.10.1)
What we are performing now is similar to a supplied C language example; below there's a picture copied from [LIXArg] (take a look to C example before this one).
Figure 2.2. Deploy model of an example with two MySQL DBMS (example11_mys_mys => example04_lixa_mysqli2.php)
Start the LIXA state server (if it was not yet started):
| [Shell terminal session] |
tiian@mojan:~$ ps -ef|grep lixad|grep -v grep tiian@mojan:~$ sudo su - lixa [sudo] password for tiian: lixa@mojan:~$ /opt/lixa/sbin/lixad --daemon lixa@mojan:~$ ps -ef|grep lixad|grep -v grep lixa 4164 1 0 20:56 ? 00:00:00 /opt/lixa/sbin/lixad --daemon lixa@mojan:~$ exit logout |
Set up LIXA_PROFILE environment variable to choose
a profile with two MySQL Resource Managers and execute
the PHP source code:
| [Shell terminal session] |
tiian@mojan:~/lixa/ext/php/examples$ export LIXA_PROFILE=MYS_STA_MYS_STA tiian@mojan:~/lixa/ext/php/examples$ echo $LIXA_PROFILE MYS_STA_MYS_STA tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example04_lixa_mysqli2.php tx_open(): 0 tx_begin(): 0 DELETE FROM authors; DELETE FROM authors; (2) Result set: Result set (2): tx_commit(): 0 tx_begin(): 0 Result set: Result set (2): INSERT INTO authors... INSERT INTO authors (2)... Result set: id=969, last_name=Ferrari, first_name=Christian Result set (2): id=969, last_name=Ferrari, first_name=Christian tx_rollback(): 0 Result set: Result set (2): tx_begin(): 0 INSERT INTO authors... INSERT INTO authors (2)... Result set: id=969, last_name=Ferrari, first_name=Christian Result set (2): id=969, last_name=Ferrari, first_name=Christian tx_commit(): 0 Result set: id=969, last_name=Ferrari, first_name=Christian Result set (2): id=969, last_name=Ferrari, first_name=Christian tx_close(): 0 |
Now you can try to use a wrong
LIXA_PROFILE environemnt variable and check
the result...
| [Shell terminal session] |
tiian@mojan:~/lixa/ext/php/examples$ export LIXA_PROFILE=MYS_STA tiian@mojan:~/lixa/ext/php/examples$ echo $LIXA_PROFILE MYS_STA tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example04_lixa_mysqli2.php tx_open(): 0 Warning: mysqli::mysqli(): (99999/99999): Unable to retrieve a valid connection from LIXA Transaction Manager in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 18 Failed to connect to MySQL2: (99999) Unable to retrieve a valid connection from LIXA Transaction Manager tx_begin(): 0 DELETE FROM authors; DELETE FROM authors; (2) Warning: mysqli::query(): Couldn't fetch mysqli in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 31 Warning: main(): Couldn't fetch mysqli in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 32 Warning: main(): Couldn't fetch mysqli in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 32 DELETE (2) failed: () Result set: Warning: mysqli::real_query(): Couldn't fetch mysqli in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 40 Warning: mysqli::use_result(): Couldn't fetch mysqli in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 41 Result set (2): Fatal error: Call to a member function fetch_assoc() on a non-object in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 43 |
you can now inspect the source code to discover where the error happens:
| [example04_lixa_mysqli2.php source code] |
1 <?php
2 /* open all the Resource Managers (MySQL instance in this example) */
3 /* export LIXA_PROFILE=MYS_STA_MYS_STA */
4 $rc=tx_open();
5 print "tx_open(): $rc\n";
6
7 /* retrieve MySQL connections from LIXA Transaction Manager instead
8 of MySQL directly: lixa// means:
9 database name=lixa
10 chooser type (pos/rmid)=none
11 id (0, 1, 2, ...)=none
12 and can be read as "retrieve the first available MySQL connection
13 established by the Transaction Manager using the current
14 LIXA profile" */
15 $mysqli = new mysqli("localhost", "lixa", "", "lixa/pos/0", 3306);
16 if ($mysqli->connect_errno)
17 echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error . "\n";
18 $mysqli2 = new mysqli("localhost", "lixa", "", "lixa/pos/1", 3306);
19 if ($mysqli2->connect_errno)
20 echo "Failed to connect to MySQL2: (" . $mysqli2->connect_errno . ") " . $mysqli2->connect_error . "\n";
21
22 /* start a new transaction coordinated by LIXA */
23 $rc=tx_begin();
24 print "tx_begin(): $rc\n";
25
26 /* remove all the rows from the tables */
27 print "DELETE FROM authors;\n";
28 if (!$mysqli->query("DELETE FROM authors;"))
29 echo "DELETE failed: (" . $mysqli->errno . ") " . $mysqli->error;
30 print "DELETE FROM authors; (2)\n";
31 if (!$mysqli2->query("DELETE FROM authors;"))
32 echo "DELETE (2) failed: (" . $mysqli2->errno . ") " . $mysqli2->error;
33
34 /* check the content of the tables */
35 $mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
36 $res = $mysqli->use_result();
37 echo "Result set:\n";
38 while ($row = $res->fetch_assoc())
39 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
40 $mysqli2->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
41 $res = $mysqli2->use_result();
42 echo "Result set (2):\n";
43 while ($row = $res->fetch_assoc())
44 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
45
46 /* commit the transaction (DELETE FROM ...) */
47 $rc=tx_commit();
48 print "tx_commit(): $rc\n";
49
50 /* start a new transaction coordinated by LIXA */
51 $rc=tx_begin();
52 print "tx_begin(): $rc\n";
53
54 /* check the content of the tables */
55 $mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
56 $res = $mysqli->use_result();
57 echo "Result set:\n";
58 while ($row = $res->fetch_assoc())
59 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
60 $mysqli2->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
61 $res = $mysqli2->use_result();
62 echo "Result set (2):\n";
63 while ($row = $res->fetch_assoc())
64 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
65
66 /* populate the tables */
67 print "INSERT INTO authors...\n";
68 if (!$mysqli->query("INSERT INTO authors VALUES(969,'Ferrari','Christian');"))
69 echo "INSERT failed: (" . $mysqli->errno . ") " . $mysqli->error;
70 print "INSERT INTO authors (2)...\n";
71 if (!$mysqli2->query("INSERT INTO authors VALUES(969,'Ferrari','Christian');"))
72 echo "INSERT (2) failed: (" . $mysqli->errno . ") " . $mysqli->error;
73
74 /* check the content of the tables */
75 $mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
76 $res = $mysqli->use_result();
77 echo "Result set:\n";
78 while ($row = $res->fetch_assoc())
79 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
80 $mysqli2->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
81 $res = $mysqli2->use_result();
82 echo "Result set (2):\n";
83 while ($row = $res->fetch_assoc())
84 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
85
86 /* rollback the transaction (INSERT INTO ...) */
87 $rc=tx_rollback();
88 print "tx_rollback(): $rc\n";
89
90 /* check the content of the tables */
91 $mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
92 $res = $mysqli->use_result();
93 echo "Result set:\n";
94 while ($row = $res->fetch_assoc())
95 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
96 $mysqli2->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
97 $res = $mysqli2->use_result();
98 echo "Result set (2):\n";
99 while ($row = $res->fetch_assoc())
100 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
101
102 /* start a new transaction coordinated by LIXA */
103 $rc=tx_begin();
104 print "tx_begin(): $rc\n";
105
106 /* populate the tables */
107 print "INSERT INTO authors...\n";
108 if (!$mysqli->query("INSERT INTO authors VALUES(969,'Ferrari','Christian');"))
109 echo "INSERT failed: (" . $mysqli->errno . ") " . $mysqli->error;
110 print "INSERT INTO authors (2)...\n";
111 if (!$mysqli2->query("INSERT INTO authors VALUES(969,'Ferrari','Christian');"))
112 echo "INSERT (2) failed: (" . $mysqli->errno . ") " . $mysqli->error;
113
114 /* check the content of the tables */
115 $mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
116 $res = $mysqli->use_result();
117 echo "Result set:\n";
118 while ($row = $res->fetch_assoc())
119 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
120 $mysqli2->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
121 $res = $mysqli2->use_result();
122 echo "Result set (2):\n";
123 while ($row = $res->fetch_assoc())
124 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
125
126 /* commit the transaction (INSERT INTO ...) */
127 $rc=tx_commit();
128 print "tx_commit(): $rc\n";
129
130 /* check the content of the tables */
131 $mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
132 $res = $mysqli->use_result();
133 echo "Result set:\n";
134 while ($row = $res->fetch_assoc())
135 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
136 $mysqli2->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
137 $res = $mysqli2->use_result();
138 echo "Result set (2):\n";
139 while ($row = $res->fetch_assoc())
140 echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
141
142 /* close all the Resource Managers */
143 $rc=tx_close();
144 print "tx_close(): $rc\n";
145 ?>
|
The program fails because the used profile does not have a second
MySQL Resource Manager (take a look to file
/opt/lixa/etc/lixac_conf.xml for profile
“MYS_STA”).
A different error can be exploited fixing the
LIXA_PROFILE environment variable:
| [Shell terminal session] |
tiian@mojan:~/lixa/ext/php/examples$ export LIXA_PROFILE=MYS_STA_MYS_STA tiian@mojan:~/lixa/ext/php/examples$ echo $LIXA_PROFILE MYS_STA_MYS_STA |
and breaking the source code changing
15 $mysqli = new mysqli("localhost", "lixa", "", "lixa/pos/0", 3306);
into
15 $mysqli = new mysqli("localhost", "lixa", "", "lixa/pos/2", 3306);
| [Shell terminal session] |
tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example04_lixa_mysqli2.php tx_open(): 0 Warning: mysqli::mysqli(): (99999/99999): Unable to retrieve a valid connection from LIXA Transaction Manager in /home/tiian/src/lixa/ext/php/examples/example04 _lixa_mysqli2.php on line 15 Failed to connect to MySQL: (99999) Unable to retrieve a valid connection from L IXA Transaction Manager tx_begin(): 0 DELETE FROM authors; Warning: mysqli::query(): Couldn't fetch mysqli in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 28 Warning: main(): Couldn't fetch mysqli in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 29 Warning: main(): Couldn't fetch mysqli in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 29 DELETE failed: () DELETE FROM authors; (2) Warning: mysqli::real_query(): Couldn't fetch mysqli in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 35 Warning: mysqli::use_result(): Couldn't fetch mysqli in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 36 Result set: Fatal error: Call to a member function fetch_assoc() on a non-object in /home/tiian/src/lixa/ext/php/examples/example04_lixa_mysqli2.php on line 38 |
Now the program fails because there's no a third Resource Manager as required by
lixa//2
(the first one is 0, the second one is 1 and so on...)
Important
Fix example example04_lixa_mysqli2.php
restoring
lixa//0
at row 15!
Important
The examples shown below need the set up explained in [LIXArg] (see chapter “Development/An example with PostgreSQL”). You won't be able to execute the following examples without a right configured PostgreSQL database.
Before trying LIXA and PostgreSQL together you should try
PostgreSQL alone
with example05_pgsql.php example:
| [Terminal shell session] |
tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example05_pgsql.php Using pgsql direct API Result set: id=999, last_name=Ferrari, first_name=Christian |
The source code is quite easy to understand:
| [example05_pgsql.php source code] |
<?php
// Connect to database
$dbconn = pg_connect("dbname=testdb")
or die("Could not connect: " . pg_last_error() . "\n");
// Clean table
$query = 'DELETE FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error() . "\n");
// Insert a row in the table
$query = "INSERT INTO authors VALUES(999, 'Ferrari', 'Christian')";
$result = pg_query($query)
or die("Query failed: " . pg_last_error() . "\n");
// Perform SQL query
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
// Print query output
echo "Using pgsql direct API\n";
echo "Result set:\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// Free resultset
pg_free_result($result);
// Close connection
pg_close($dbconn);
?>
|
Special attention must be reserved to this row:
| [example05_pgsql.php source code (fragment)] |
$dbconn = pg_connect("dbname=testdb")
or die("Could not connect: " . pg_last_error() . "\n");
|
because it will be changed to use LIXA as a Transaction Manager.
What we are performing now is similar to a supplied C language example; below there's a picture copied from [LIXArg] (take a look to C example before this one).
Start the LIXA state server (if it was not yet started):
| [Shell terminal session] |
tiian@mojan:~$ ps -ef|grep lixad|grep -v grep tiian@mojan:~$ sudo su - lixa [sudo] password for tiian: lixa@mojan:~$ /opt/lixa/sbin/lixad --daemon lixa@mojan:~$ ps -ef|grep lixad|grep -v grep lixa 4164 1 0 20:56 ? 00:00:00 /opt/lixa/sbin/lixad --daemon lixa@mojan:~$ exit logout |
Set up LIXA_PROFILE environment variable to choose
a profile with PostgreSQL as Resource Manager and execute the PHP
source code:
| [Shell terminal session] |
tiian@mojan:~/lixa/ext/php/examples$ export LIXA_PROFILE=PQL_STA tiian@mojan:~/lixa/ext/php/examples$ echo $LIXA_PROFILE PQL_STA tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example06_lixa_pgsql.php tx_open(): 0 tx_begin(): 0 Result set: tx_commit(): 0 tx_begin(): 0 Result set: Result set: id=999, last_name=Ferrari, first_name=Christian tx_rollback(): 0 Result set: tx_begin(): 0 Result set: id=999, last_name=Ferrari, first_name=Christian tx_commit(): 0 Result set: id=999, last_name=Ferrari, first_name=Christian tx_close(): 0 |
take a look to the source code, and the comments, to understand how to develop an Application Program coordinated by LIXA Transaction Manager with PostgreSQL Resource Manager:
| [example06_lixa_pgsql.php source code] |
<?php
// open all the Resource Managers (PostgreSQL instance in this example)
$rc = tx_open();
print "tx_open(): $rc\n";
/* retrieve PostgreSQL connection from LIXA Transaction Manager instead
of PostgreSQL directly: lixa// means:
database name=lixa
chooser type (pos/rmid)=none
id (0, 1, 2, ...)=none
and can be read as "retrieve the first available PostgreSQL
connection established by the Transaction Manager using the current
LIXA profile" */
$dbconn = pg_connect("dbname=lixa//")
or die("Could not connect: " . pg_last_error() . "\n");
// start a new transaction coordinated by LIXA Transaction Manager
$rc=tx_begin();
print "tx_begin(): $rc\n";
// remove all the rows from the table
$query = 'DELETE FROM authors';
$result = pg_query($query)
or die("DELETE failed: " . pg_last_error() . "\n");
// check the table content
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set:\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// commit the transaction (DELETE FROM...)
$rc=tx_commit();
print "tx_commit(): $rc\n";
// start a new transaction coordinated by LIXA Transaction Manager
$rc=tx_begin();
print "tx_begin(): $rc\n";
// check the table content
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set:\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// populate the table
$query = "INSERT INTO authors VALUES(999, 'Ferrari', 'Christian')";
$result = pg_query($query)
or die("Query failed: " . pg_last_error() . "\n");
// check the table content
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set:\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// rollback the transaction (INSERT INTO...)
$rc=tx_rollback();
print "tx_rollback(): $rc\n";
// check the table content
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set:\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// start a new transaction coordinated by LIXA Transaction Manager
$rc=tx_begin();
print "tx_begin(): $rc\n";
// populate the table
$query = "INSERT INTO authors VALUES(999, 'Ferrari', 'Christian')";
$result = pg_query($query)
or die("Query failed: " . pg_last_error() . "\n");
// check the table content
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set:\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// commit the transaction (DELETE FROM...)
$rc=tx_commit();
print "tx_commit(): $rc\n";
// check the table content
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set:\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// release resultset
pg_free_result($result);
/* release PHP handler for dbconn (it does not close database
connection because it was opened by LIXA Transaction Manager).
You must call "pg_close(...)" before "tx_close()" to avoid
automatic clean-up at script end (it might fail due to dirty
memory if you didn't perform the correct close sequence */
pg_close($dbconn);
// close al the Resource Managers
$rc = tx_close();
print "tx_close(): $rc\n";
?>
|
I suggest you to verify the table is really populated and the debug messages produced by the above program are truthful:
| [Shell terminal session] |
tiian@mojan:~/lixa/ext/php/examples$ psql testdb psql (8.4.11) Type "help" for help. testdb=> SELECT * FROM authors; id | last_name | first_name -----+-----------+------------ 999 | Ferrari | Christian (1 rows) |
Example example06_lixa_pgsql.php should be
deeply understood for three main reasons:
the syntax of the connection string passed to function
pg_connectthe absence of parameter
persistentwhen calling functionpg_connectthe close sequence with both
pg_close(...)andtx_close()functions
If you want to use a database connection managed by LIXA Transaction Manager you have to:
call function
tx_open(): LIXA Transaction Manager will open all the resource managers specified by the currentLIXA_PROFILEprofile (see file/opt/lixa/etc/lixac_conf.xml)call function
pg_connectusing a connection string with the syntax explained in the section called “MySQL (mysqli) connection string syntax”; the same rules are true for both MySQL and PostgreSQL because they share the same LIXA provided XA switch fileavoid to use
persistentparameter when callingpg_connect(...)function because persistent connections managed by PostgreSQL database driver conflict with LIXA's XA implementation
The implementation of PostgreSQL pgsql driver is
different than MySQL mysqli driver and LIXA
patch tries to change as less as possible of the original code.
pgsql driver saves only the handler (pointer)
returned by PQconnectdb, while
mysqli saves a structure with some additional
information. In the case of mysqli, LIXA patch
adds a flag to identify the database connections provided by the
LIXA Transaction Manager.
With pgsql the same approach would have refactored
most of the code and a different approach was choosen:
pg_close(...) calls
lixa_pq_is_managed_conn(...) to understand the
connection type. If it was a LIXA managed connection,
pg_close(...) would avoid
PQfinish(...), but it would remove the
connection from the pool. The program calls
tx_close() later, and this function will
call PQfinish(...) to close the PostgreSQL
database connection.
Warning
If you didn't call pg_close(...), at
script end the automatic clean-up routine would try to close the
PostgreSQL connection because it is stored in the connection pool,
but it was already closed by
tx_close() and you could catch unexpected
results like segmentation faults and so on.
Important
Use these sequences to avoid unexpected results:
first
tx_open()and thenpg_connect(...)when connecting to PostgreSQL under the control of LIXA Transaction Managerfirst
pg_close(...)and thentx_close()when disconnecting from PostgreSQL under the control of LIXA Transaction Manager
Important
The example shown below need the set up explained in [LIXArg] (see chapter “Development/An example with MySQL & PostgreSQL”). You won't be able to execute the following example without right configured MySQL & PostgreSQL databases.
Before trying LIXA, MySQL and PostgreSQL together you should try the examples shown in the previous paragraphs: see the section called “An example with MySQL (mysqli driver)” and the section called “An example with PostgreSQL (pgsql driver)”.
What we are performing now is similar to a supplied C language example; below there's a picture copied from [LIXArg] (take a look to C example before this one).
Figure 2.4. Deploy model of an example with MySQL & PostgreSQL (example9_mys_pql => example07_lixa_mysqli_pgsql.php)
Start the LIXA state server (if it was not yet started):
| [Shell terminal session] |
tiian@mojan:~$ ps -ef|grep lixad|grep -v grep tiian@mojan:~$ sudo su - lixa [sudo] password for tiian: lixa@mojan:~$ /opt/lixa/sbin/lixad --daemon lixa@mojan:~$ ps -ef|grep lixad|grep -v grep lixa 4164 1 0 20:56 ? 00:00:00 /opt/lixa/sbin/lixad --daemon lixa@mojan:~$ exit logout |
Set up LIXA_PROFILE environment variable to choose
a profile with MySQL and PostgreSQL as Resource Managers and
execute the PHP source code:
| [Shell terminal session] |
tiian@mojan:~/lixa/ext/php/examples$ export LIXA_PROFILE=MYS_STA_PQL_STA tiian@mojan:~/lixa/ext/php/examples$ echo $LIXA_PROFILE MYS_STA_PQL_STA tiian@mojan:~/lixa/ext/php/examples$ /opt/php-5.4-lixa/bin/php example07_lixa_mysqli_pgsql.php tx_open(): 0 tx_begin(): 0 DELETE FROM authors; Result set (MySQL): Result set (PostgreSQL): tx_commit(): 0 tx_begin(): 0 Result set (MySQL): Result set (PostgreSQL): INSERT INTO authors... Result set (MySQL): id=969, last_name=Ferrari, first_name=Christian Result set (PostgreSQL): id=999, last_name=Ferrari, first_name=Christian tx_rollback(): 0 Result set (MySQL): Result set (PostgreSQL): tx_begin(): 0 INSERT INTO authors... Result set (MySQL): id=969, last_name=Ferrari, first_name=Christian Result set (PostgreSQL): id=999, last_name=Ferrari, first_name=Christian tx_commit(): 0 Result set (MySQL): id=969, last_name=Ferrari, first_name=Christian Result set (PostgreSQL): id=999, last_name=Ferrari, first_name=Christian tx_close(): 0 |
take a look to the source code, and the comments, to understand how to develop an Application Program coordinated by LIXA Transaction Manager with MySQL & PostgreSQL as Resource Managers:
| [example07_lixa_mysqli_pgsql.php source code] |
<?php
/* open all the Resource Managers (MySQL & PostgreSQL instances
in this example) */
$rc=tx_open();
print "tx_open(): $rc\n";
/* retrieve database connections from LIXA Transaction Manager instead
of MySQL/PostgreSQL directly: lixa// means:
database name=lixa
chooser type (pos/rmid)=none
id (0, 1, 2, ...)=none
and can be read as "retrieve the first available MySQL/PostgreSQL
connection established by the Transaction Manager
using the current LIXA profile" */
// retrieve first available MySQL connection
$mysqli = new mysqli("localhost", "lixa", "", "lixa//", 3306);
if ($mysqli->connect_errno)
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno .
") " . $mysqli->connect_error . "\n";
// retrieve first available PostgreSQL connection
$dbconn = pg_connect("dbname=lixa//")
or die("Could not connect: " . pg_last_error() . "\n");
// start a new transaction coordinated by LIXA
$rc=tx_begin();
print "tx_begin(): $rc\n";
// remove all the rows from MySQL table
print "DELETE FROM authors;\n";
if (!$mysqli->query("DELETE FROM authors;"))
echo "DELETE failed: (" . $mysqli->errno . ") " .
$mysqli->error;
// remove all the rows from PostgreSQL table
$query = 'DELETE FROM authors';
$result = pg_query($query)
or die("DELETE failed: " . pg_last_error() . "\n");
// check the table content (MySQL)
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set (MySQL):\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// check the table content (PostgreSQL)
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set (PostgreSQL):\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// commit the transaction (DELETE FROM ...)
$rc=tx_commit();
print "tx_commit(): $rc\n";
// start a new transaction coordinated by LIXA
$rc=tx_begin();
print "tx_begin(): $rc\n";
// check the table content (MySQL)
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set (MySQL):\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] . ", first_name=" . $row['first_name'] . "\n";
// check the table content (PostgreSQL)
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set (PostgreSQL):\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// populate the table (MySQL)
print "INSERT INTO authors...\n";
if (!$mysqli->query("INSERT INTO authors VALUES(969,'Ferrari','Christian');"))
echo "INSERT failed: (" . $mysqli->errno . ") " .
$mysqli->error;
// populate the table (PostgreSQL)
$query = "INSERT INTO authors VALUES(999, 'Ferrari', 'Christian')";
$result = pg_query($query)
or die("Query failed: " . pg_last_error() . "\n");
// check the table content (MySQL)
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set (MySQL):\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// check the table content (PostgreSQL)
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set (PostgreSQL):\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// rollback the transaction (INSERT INTO ...)
$rc=tx_rollback();
print "tx_rollback(): $rc\n";
// check the table content (MySQL)
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set (MySQL):\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// check the table content (PostgreSQL)
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set (PostgreSQL):\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// start a new transaction coordinated by LIXA
$rc=tx_begin();
print "tx_begin(): $rc\n";
// populate the table (MySQL)
print "INSERT INTO authors...\n";
if (!$mysqli->query("INSERT INTO authors VALUES(969,'Ferrari','Christian');"))
echo "INSERT failed: (" . $mysqli->errno . ") " .
$mysqli->error;
// populate the table (PostgreSQL)
$query = "INSERT INTO authors VALUES(999, 'Ferrari', 'Christian')";
$result = pg_query($query)
or die("Query failed: " . pg_last_error() . "\n");
// check the table content (MySQL)
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set (MySQL):\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// check the table content (PostgreSQL)
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set (PostgreSQL):\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// commit the transaction (INSERT INTO ...)
$rc=tx_commit();
print "tx_commit(): $rc\n";
// check the table content (MySQL)
$mysqli->real_query("SELECT id,last_name,first_name FROM authors ORDER BY id DESC;");
$res = $mysqli->use_result();
echo "Result set (MySQL):\n";
while ($row = $res->fetch_assoc())
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// check the table content (PostgreSQL)
$query = 'SELECT * FROM authors';
$result = pg_query($query)
or die("Query failed: " . pg_last_error(). "\n");
echo "Result set (PostgreSQL):\n";
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC))
echo "id=" . $row['id'] . ", last_name=" . $row['last_name'] .
", first_name=" . $row['first_name'] . "\n";
// release resultset
pg_free_result($result);
/* release PHP handler for dbconn (it does not close database
connection because it was opened by LIXA Transaction Manager */
pg_close($dbconn);
// close all the Resource Managers
$rc=tx_close();
print "tx_close(): $rc\n";
?>
|
I suggest you to verify the tables are really populated and the debug messages produced by the above program are truthful.
Check MySQL “authors” table:
| [Shell terminal session] |
tiian@mojan:~/lixa/ext/php/examples$ 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 35 Server version: 5.1.62-0ubuntu0.10.04.1 (Ubuntu) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT * FROM authors; +-----+-----------+------------+ | id | last_name | first_name | +-----+-----------+------------+ | 969 | Ferrari | Christian | +-----+-----------+------------+ 1 row in set (0.00 sec) |
Check PostgreSQL “authors” table:
| [Shell terminal session] |
tiian@mojan:~/lixa/ext/php/examples$ psql testdb psql (8.4.11) Type "help" for help. testdb=> SELECT * FROM authors; id | last_name | first_name -----+-----------+------------ 999 | Ferrari | Christian (1 rows) |
Example example07_lixa_mysqli_pgsql.php
does not add specific stuff if compared with the examples previously
described:
example03_lixa_mysqli.php and
example06_lixa_pgsql.php.
It combines two different Resource Managers
and perform a really distributed transaction.
If you already analyzed the source code of the previous examples,
the only interesting point should be this piece of code:
/* retrieve database connections from LIXA Transaction Manager instead
of MySQL/PostgreSQL directly: lixa// means:
database name=lixa
chooser type (pos/rmid)=none
id (0, 1, 2, ...)=none
and can be read as "retrieve the first available MySQL/PostgreSQL
connection established by the Transaction Manager
using the current LIXA profile" */
// retrieve first available MySQL connection
$mysqli = new mysqli("localhost", "lixa", "", "lixa//", 3306);
if ($mysqli->connect_errno)
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno .
") " . $mysqli->connect_error . "\n";
// retrieve first available PostgreSQL connection
$dbconn = pg_connect("dbname=lixa//")
or die("Could not connect: " . pg_last_error() . "\n");
We are using only one MySQL and only one PostgreSQL, so string
lixa//
is good enought for both because it means
lixa/pos/0
and every Resource Manager is in the first position considering there's only one for every type (see the section called “MySQL (mysqli) connection string syntax” for more information).
You may change the above code as shown below:
// retrieve MySQL connection (rmid=0)
$mysqli = new mysqli("localhost", "lixa", "", "lixa/rmid/0", 3306);
if ($mysqli->connect_errno)
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno .
") " . $mysqli->connect_error . "\n";
// retrieve PostgreSQL connection (rmid=1)
$dbconn = pg_connect("dbname=lixa/rmid/1")
or die("Could not connect: " . pg_last_error() . "\n");
and you will obtain a running program (MySQL is associated to rmid=0
and PostgreSQL is associated to rmid=1, take a look to file
/opt/lixa/etc/lixac_conf.xml for profile
MYS_STA_PQL_STA).
You wouldn't obtain a running program if you changed the source code as shown below:
// retrieve MySQL connection (rmid=1)
$mysqli = new mysqli("localhost", "lixa", "", "lixa/rmid/1", 3306);
if ($mysqli->connect_errno)
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno .
") " . $mysqli->connect_error . "\n";
// retrieve PostgreSQL connection (rmid=0)
$dbconn = pg_connect("dbname=lixa/rmid/0")
or die("Could not connect: " . pg_last_error() . "\n");
because there's no correspondence between rmid and the expected Resource Manager type (MySQL/PostgreSQL).
[2] Refer to file /opt/lixa/include/tx.h
for TX return codes and their meaning.
[3] Take a look to PHP official documentation
[4]
Do not confuse lixa token (it means:
“use a connection opened by LIXA
Transaction Manager”) with “lixa”
MySQL database name used in many examples.
Incidentally they are the same word,
but when using lixa/[pos|rmid]/[id] the
name of the used databases may be any: foo, bar, mydb,
etc... The real name of the databases opened by LIXA
Transaction Manager is defined in configuration file
/opt/lixa/ect/lixac_conf.xml