LIXA Extensions reference guide (version 1.0.1)

A libre, free, open source implementation of the XA and the TX specifications

Christian Ferrari

Copyright © 2012 Christian Ferrari

Table of Contents

1. Introduction
What's LIXA extension?
2. PHP
Extension type
Prerequisites
Installation
PHP environment
Testing LIXA PHP integration
LIXA details
Using LIXA extension for PHP
LIXA PHP Application Programming Interface (API)
An example with dummy Resource Managers
An example with MySQL (mysqli driver)
MySQL (mysqli) connection string syntax
An example with two MySQL servers (mysqli)
An example with PostgreSQL (pgsql driver)
An example with MySQL (mysqli driver) & PostgreSQL (pgsql driver)
Bibliography

List of Figures

2.1. Deploy model of an example with MySQL DBMS (example8_mys => example03_lixa_mysqli.php)
2.2. Deploy model of an example with two MySQL DBMS (example11_mys_mys => example04_lixa_mysqli2.php)
2.3. Deploy model of an example with PostgreSQL (example5_pql => example06_lixa_pgsql.php)
2.4. Deploy model of an example with MySQL & PostgreSQL (example9_mys_pql => example07_lixa_mysqli_pgsql.php)

Chapter 1. Introduction

What's LIXA extension?

LIXA project is designed to implement a C interface as described in [TXspec]; alternatively C++ can be used without any specific issue: the C function exposed by LIXA API are wrapped by standard extern "C": 

#ifdef __cplusplus
extern "C" {
#endif /* __cplusplus */

[... function prototypes ...]

#ifdef __cplusplus
}
#endif /* __cplusplus */

LIXA API is not an object oriented interface, but this should not hurt you too much if you were developing a C++ software.

If you are developing a software using a completely different language as Perl, PHP, Python, Ruby and so on, the LIXA C interface will be quite useless. You could think all you need is a LIXA wrapper for your preferred language, but unfortunately wrapping LIXA interface is not enough: XA specification (see [XAspec]) needs a live interface between the Transaction Manager (i.e. LIXA) and the Resource Managers (databases, message oriented middlewares, etc...). Most database “drivers” were developed with the intent of an exclusive management of the database API; this is fine until you need two phase commit (Distributed Transaction Processing) and a third uninvited guest, the Trasaction Manager, appears.

LIXA extensions are intended to supply you alternative programming languages for Distributed Transaction Processing.

Important

LIXA extensions are 100% experimental code and should not used for production environments. The code is provided in the hope someone will improve it.

Chapter 2. PHP

Note

There is not a porting of LIXA to Microsoft Windows operating system; LIXA PHP extension is not suitable to operate in a Microsoft Windows environment as well as LIXA base technology.

Most of the PHP technology is platform independent, but this does not apply to LIXA PHP extension: it has been developed and tested using GNU/Linux operating system.

Extension type

LIXA PHP extension is a patch for Zend engine. The patch contains:

  • a PHP LIXA wrapper generated using SWIG

  • a set of changes to some PHP database drivers

  • all the build & deploy stuff needed to produce a Zend (LIXA patched) PHP engine

Prerequisites

LIXA wrapper might be dynamically generated using SWIG: you should install SWIG on your system in advance if you desired to build the wrapper from scratch. Most times, the LIXA distributed wrapper works fine and you don't have to generate a fresh one.

PHP needs some specific versions of autoconf as explained in Talking to the UNIX build system: config.m4; you must install the right version of autoconf before going on. Without a valid version of autoconf you will not be able to build a Zend PHP engine.

For the sake of easiness, this manual shows how to build a fresh PHP Zend environment patched with the LIXA provided extension. You could patch your own previously downloaded PHP environment, but this manual tries to keep it simple and uses a freshly downloaded tarball.

Installation

Locate the ext/php directory inside the LIXA source tree and enter it; you should find an executable shell script named download_and_patch.sh inside it: 

tiian@mojan:~/lixa/ext/php$ ls -la download_and_patch.sh
-rwxr-xr-x 1 tiian tiian 4855 2012-03-31 23:37 download_and_patch.sh

Note

You need a running Internet connection before command execution because it will try to download the PHP tarball for you from http://docs.php.net/

Command download_and_patch.sh can do most operation for you, but you can perform the same manually. Try to execute the script from a terminal session:

[Shell terminal session]
tiian@mojan:~/lixa/ext/php$ ./download_and_patch.sh 
PHP 5.4.1 will be downloaded...
Checking if wget command is available... wget is /usr/bin/wget
Checking if patch command is available... patch is /usr/bin/patch
Checking if bunzip2 is available... bunzip2 is /bin/bunzip2
--2012-04-29 22:47:03--  http://docs.php.net/get/php-5.4.1.tar.bz2/from/docs.php.net/mirror
Resolving docs.php.net... 213.175.195.123
Connecting to docs.php.net|213.175.195.123|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://docs.php.net/distributions/php-5.4.1.tar.bz2 [following]
--22:19:05--  http://docs.php.net/distributions/php-5.4.1.tar.bz2
           => `php-5.4.1.tar.bz2'
Connecting to docs.php.net|213.175.195.123|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 11145742 (11M) [application/octet-stream]
Salvataggio in: "php-5.4.1.tar.bz2"

100%[======================================>] 11.145.742   681K/s   in 15s     

2012-04-29 22:47:18 (743 KB/s) - "php-5.4.1.tar.bz2" salvato [11145742/11145742]

Deflating PHP source code...
Creating LIXA extension directory...
mkdir: directory "php-5.4.1/ext/lixa" created
"config.m4" -> "php-5.4.1/ext/lixa/config.m4"
"lixa.c" -> "php-5.4.1/ext/lixa/lixa.c"
"php_lixa.h" -> "php-5.4.1/ext/lixa/php_lixa.h"
mkdir: directory "php-5.4.1/ext/lixa/tests" created
"tests/Makefile" -> "php-5.4.1/ext/lixa/tests/Makefile"
"tests/Makefile.am" -> "php-5.4.1/ext/lixa/tests/Makefile.am"
"tests/Makefile.in" -> "php-5.4.1/ext/lixa/tests/Makefile.in"
"tests/lixa_001.phpt" -> "php-5.4.1/ext/lixa/tests/lixa_001.phpt"
"tests/lixa_002.phpt" -> "php-5.4.1/ext/lixa/tests/lixa_002.phpt"
"tests/lixa_011.phpt" -> "php-5.4.1/ext/lixa/tests/lixa_011.phpt"
"tests/lixa_012.phpt" -> "php-5.4.1/ext/lixa/tests/lixa_012.phpt"
"tests/lixa_021.phpt" -> "php-5.4.1/ext/lixa/tests/lixa_021.phpt"
"tests/lixa_022.phpt" -> "php-5.4.1/ext/lixa/tests/lixa_022.phpt"
"tests/lixa_023.phpt" -> "php-5.4.1/ext/lixa/tests/lixa_023.phpt"
Checking if the downloaded PHP source code can be patched...
File lixa-php-5.4.1-patch.diff exists, trying patch command...
patching file php-5.4.1/ext/mysqli/mysqli_api.c
patching file php-5.4.1/ext/mysqli/mysqli_nonapi.c
patching file php-5.4.1/ext/mysqli/php_mysqli_structs.h
patching file php-5.4.1/ext/pgsql/pgsql.c
Patching PHP source code...
patching file php-5.4.1/ext/mysqli/mysqli_api.c
patching file php-5.4.1/ext/mysqli/mysqli_nonapi.c
patching file php-5.4.1/ext/mysqli/php_mysqli_structs.h
patching file php-5.4.1/ext/pgsql/pgsql.c
PHP source code successfully patched!
Removing php-5.4.1/configure and some stuff...
"php-5.4.1/configure" removed
Creating new configure script...
Forcing buildconf
Removing configure caches
buildconf: checking installation...
buildconf: autoconf version 2.65 (ok)
rebuilding configure
rebuilding main/php_config.h.in
Checking configure recognize LIXA extension...
  --with-lixa=FILE        Include LIXA support. File is the path to lixa-config program
  --enable-lixa-swig      Generate LIXA wrapper using SWIG

************************************************************************
  PHP source code in directory php-5.4.1 is ready for build and deploy;

  use --with-lixa=/opt/lixa/bin/lixa-config (or different path if you
  not using LIXA default path) to enable LIXA extension for PHP

  use --enable-lixa-swig if you want to generate LIXA PHP wrapper from
  scratch using SWIG software (this should not be necessary most times)
************************************************************************

Note

Script download_and_patch.sh is configured with a default PHP version, but it may happen you need to download, patch, build and install a different version. Pass the desired PHP version as the first argument to command download_and_patch.sh: 

	    ./download_and_patch.sh 5.4.3

These are the steps performed by the script:

  1. download PHP tarball using wget from http://docs.php.net/

    if bunzip2 is installed, .tar.bz2 will be downloaded; if gunzip is installed, .tar.gz will be downloaded;

  2. extract tarball locally

  3. create php-X.Y.Z/ext/lixa directory and populate it with config.m4 lixa.c lixa.php php_lixa.h

  4. patch PHP Zend source code

  5. clean-up PHP build environment (rm -vf configure autom4te.cache/*)

  6. reset PHP build environment (./buildconf --force)

  7. check PHP build is LIXA enabled (./configure --help | grep lixa)

You are now ready to run configure specifying all the desired values; you should refer to official Zend documentation “Installation and Configuration” and “PHP at the Core: A Hacker's Guide to the Zend Engine” to retrieve all the information necessary to build your own PHP engine starting from the source code.

The command shown in the below table is an example for a test environment; you would probably need something different (HTTP server modules and so on...).

[Shell terminal session]
tiian@mojan:~/lixa/ext/php$ cd php-5.4.1
tiian@mojan:~/lixa/ext/php/php-5.4.1$ nice ./configure --enable-debug \
> --enable-maintainer-zts --enable-cgi --enable-cli --enable-shared \
> --with-mysqli=/usr/bin/mysql_config --with-pgsql \
> --with-oci8=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server \
> --with-lixa=/opt/lixa/bin/lixa-config \
> --prefix=/opt/php-5.4-lixa

In case you might want to generate LIXA PHP wrapper source code using SWIG you should add --enable-lixa-swig to configure command line, but most times this should not be necessary.

Important

Most times the wrapper supplied with LIXA distribution works fine and you don't have to generate a fresh one. Sometimes, a locally generated wrapper does not work due to some SWIG issue.

The --enable-lixa-swig option is basically designed for LIXA maintainers.

Warning

LIXA software must be compiled and installed with all the Resource Managers you want to use from PHP. If you did not configure and install LIXA for MySQL, you would not be able to use MySQL from PHP as a Resource Manager coordinated by LIXA Transaction Manager. The same is true for PostgreSQL, Oracle, and so on...

Use command /opt/lixa/bin/lixa-config -r to discover which Resource Managers can be used by your installed LIXA software.

After configure you must proceed with make and make install; please refer to “Installation on Unix systems” for the details related to PHP installation. This is what should happen with make:

[Shell terminal session]
tiian@mojan:~/lixa/ext/php$ cd php-5.4.1
tiian@mojan:~/lixa/ext/php/php-5.4.1$ nice make
[...]
Generating phar.php
Generating phar.phar
PEAR package PHP_Archive not installed: generated phar will require PHP's phar extension be enabled.
directorytreeiterator.inc
directorygraphiterator.inc
pharcommand.inc
clicommand.inc
invertedregexiterator.inc
phar.inc

Build complete.
Don't forget to run 'make test'.

and with make install

[Shell terminal session]
tiian@ubuntu:~/lixa/ext/php/php-5.4.1$ sudo nice make install
[sudo] password for tiian:
Installing PHP CLI binary:        /opt/php-5.4-lixa/bin/
Installing PHP CLI man page:      /opt/php-5.4-lixa/php/man/man1/
Installing PHP CGI binary:        /opt/php-5.4-lixa/bin/
Installing build environment:     /opt/php-5.4-lixa/lib/php/build/
Installing header files:          /opt/php-5.4-lixa/include/php/
Installing helper programs:       /opt/php-5.4-lixa/bin/
  program: phpize
  program: php-config
Installing man pages:             /opt/php-5.4-lixa/php/man/man1/
  page: phpize.1
  page: php-config.1
Installing PEAR environment:      /opt/php-5.4-lixa/lib/php/
[PEAR] Archive_Tar    - installed: 1.3.7
[PEAR] Console_Getopt - installed: 1.3.0
[PEAR] Structures_Graph- installed: 1.0.4
[PEAR] XML_Util       - installed: 1.2.1
[PEAR] PEAR           - installed: 1.9.4
Wrote PEAR system config file at: /opt/php-5.4-lixa/etc/pear.conf
You may want to add: /opt/php-5.4-lixa/lib/php to your php.ini include_path
/home/tiian/lixa/ext/php/php-5.4.1/build/shtool install -c ext/phar/phar.phar /opt/php-5.4-lixa/bin
ln -s -f /opt/php-5.4-lixa/bin/phar.phar /opt/php-5.4-lixa/bin/phar
Installing PDO headers:          /opt/php-5.4-lixa/include/php/ext/pdo/

PHP environment

If everything went right you would build a PHP environment patched for LIXA. It should behaves exactly as a standard PHP environment until you start to use LIXA specific features.

Testing LIXA PHP integration

LIXA PHP integration supplies some basic case tests you can execute to verify the build and install process went right.

Move to PHP source root directory and execute the LIXA related tests as shown below:

[Shell terminal session]
tiian@mojan:~/lixa/ext/php/php-5.4.1$ TEST_PHP_EXECUTABLE=/opt/php-5.4-lixa/bin/php /opt/php-5.4-lixa/bin/php run-tests.php ext/lixa/tests/

=====================================================================
PHP         : /opt/php-5.4-lixa/bin/php 
PHP_SAPI    : cli
PHP_VERSION : 5.4.1
ZEND_VERSION: 2.4.0
PHP_OS      : Linux - Linux mojan 2.6.32-41-generic #88-Ubuntu SMP Thu Mar 29 13:08:43 UTC 2012 i686
INI actual  : /home/tiian/src/lixa/ext/php/php-5.4.1
More .INIs  :  
CWD         : /home/tiian/src/lixa/ext/php/php-5.4.1
Extra dirs  : 
VALGRIND    : Not used
=====================================================================
Running selected tests.
PASS LIXA T.M. availability - basic test: the T.M. is up and running [ext/lixa/tests/lixa_001.phpt] 
PASS LIXA T.M. trivial commit - basic test: a trivial commit transaction [ext/lixa/tests/lixa_002.phpt] 
PASS LIXA mysqli availability - basic test: MySQL is reachable with mysqli [ext/lixa/tests/lixa_011.phpt] 
PASS LIXA mysqli integration - a simple transaction using LIXA and MySQL (mysqli driver) [ext/lixa/tests/lixa_012.phpt] 
PASS LIXA PostgreSQL availability - basic test: PostgreSQL is reachable [ext/lixa/tests/lixa_021.phpt] 
PASS LIXA pgsql integration - a simple transaction using LIXA and PostgreSQL (pgsql driver) [ext/lixa/tests/lixa_022.phpt] 
PASS LIXA pgsql mysqli integration - a simple transaction using LIXA, PostgreSQL (pgsql driver) and MySQL (mysqli driver) [ext/lixa/tests/lixa_023.phpt] 
=====================================================================
Number of tests :    7                 7
Tests skipped   :    0 (  0.0%) --------
Tests warned    :    0 (  0.0%) (  0.0%)
Tests failed    :    0 (  0.0%) (  0.0%)
Expected fail   :    0 (  0.0%) (  0.0%)
Tests passed    :    7 (100.0%) (100.0%)
---------------------------------------------------------------------
Time taken      :    2 seconds
=====================================================================

Pay attention you must specify the PHP installation path twice in command TEST_PHP_EXECUTABLE=/opt/php-5.4-lixa/bin/php /opt/php-5.4-lixa/bin/php run-tests.php ext/lixa/tests/

Note

If you were interested in executing the whole PHP test suite, you would use command make test: the PHP environment would execute all the PHP tests, LIXA supplied too.

Warning

Some case tests need specific third party Resource Managers configuration: the same configuration required to run LIXA PHP examples (see the section called “Using LIXA extension for PHP”) is required for the test cases as well.

LIXA details

If you were building a Zend LIXA patched PHP engine, you should pay attention to some specific issues:

  • it's not mandatory, but it's strongly suggested you will use a specific verbose installation path (--prefix=/opt/php-5.4-lixa in the above example)

  • to enable LIXA support you must specify something like --with-lixa=/opt/lixa/bin/lixa-config (if you didn't install LIXA in default path, you would specify a different path for lixa-config utility program)

  • “mysqli” extension provided by Zend engine can be used with LIXA only if standard MySQL client library is wrapped by mysqli (this is not the defaul behavior in the latest releases of Zend PHP)[1].

    You can use something like this to force the usage of standard MySQL client library instead of “Native Driver” provided by Zend: --with-mysqli=/usr/bin/mysql_config

Using LIXA extension for PHP

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.

LIXA PHP Application Programming Interface (API)

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.

An example with dummy Resource Managers

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.

An example with MySQL (mysqli driver)

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).

Figure 2.1. Deploy model of an example with MySQL DBMS (example8_mys => example03_lixa_mysqli.php)

Deploy model of an example with MySQL DBMS (example8_mys => example03_lixa_mysqli.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 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)

MySQL (mysqli) connection string syntax

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() function

  • pos|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.

An example with two MySQL servers (mysqli)

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 row 

    bind-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)

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!

An example with PostgreSQL (pgsql driver)

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).

Figure 2.3. Deploy model of an example with PostgreSQL (example5_pql => example06_lixa_pgsql.php)

Deploy model of an example with PostgreSQL (example5_pql => example06_lixa_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 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:

  1. the syntax of the connection string passed to function pg_connect

  2. the absence of parameter persistent when calling function pg_connect

  3. the close sequence with both pg_close(...) and tx_close() functions

pg_connect connection string syntax

If you want to use a database connection managed by LIXA Transaction Manager you have to:

  1. call function tx_open(): LIXA Transaction Manager will open all the resource managers specified by the current LIXA_PROFILE profile (see file /opt/lixa/etc/lixac_conf.xml)

  2. call function pg_connect using 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 file

  3. avoid to use persistent parameter when calling pg_connect(...) function because persistent connections managed by PostgreSQL database driver conflict with LIXA's XA implementation

the pg_close(...), tx_close() closing sequence

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 then pg_connect(...) when connecting to PostgreSQL under the control of LIXA Transaction Manager

  • first pg_close(...) and then tx_close() when disconnecting from PostgreSQL under the control of LIXA Transaction Manager

An example with MySQL (mysqli driver) & PostgreSQL (pgsql driver)

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)

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).


[1] MySQL “Native Driver” can not be used with LIXA because it should be managed as a distinct Resource Manager, not as a standard MySQL one

[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

Bibliography

Books

[LIXArg] Copyright © 2011 Christian Ferrari. LIXA reference guide: a libre, free, open source implementation of the XA and the TX specifications .

[RefModel] Copyright © 1996 X/Open Company Limited. 1-85912-170-5. X/Open Company Ltd., U.K.. Distributed Transaction Processing: Reference Model, Version 3 .

[TXspec] Copyright © 1995 X/Open Company Limited. 1-85912-094-6. X/Open Company Ltd., U.K.. Distributed Transaction Processing: The TX (Transaction Demarcation) Specification .

[XAspec] Copyright © 1991 X/Open Company Limited. 1-872630-24-3. X/Open Company Ltd., U.K.. Distributed Transaction Processing: The XA Specification .

[XA+spec] Copyright © 1994 X/Open Company Limited. 1-85912-046-6. X/Open Company Ltd., U.K.. Distributed Transaction Processing: The XA+ Specification Version 2 .