When second query in multple-queries-string have an error, the Exception is not thrown!
For example, next string has error in second query:
'insert into t1 (val) value (1); insert into t1 (_ERROR_) value (3); insert into t1 (val) value (2);'
But the transaction will be fully executed and committed!
I think this is not right behavior relative to common sense.
There are any way to get the thrown Exception where occurs an sql error in not-first query of multiple-queries-string?
Or there are any around way to safely execute multiple queries in one string?
At now I have next conclusion:
PDOStatement can execute multiple queries in one string,
but if second and next queries has an error execution,
no any Exception will be thrown.
That is, if string contains three sql-query, first and third sql is valid,
but the second is not, then first sql will be done,
but second and third sql are NOT, but transaction will be committed!
My environment: PHP 5.4.7; Yii 1.1.13; MySQL 5.5.27
I wrote simple unit test for testing it:
/**
* Testing transaction in MySQL.
* The instruction of DROP TABLE, CREATE TABLE is excluded,
* because they doing implicit COMMIT in MySQL.
*
* NOTE: Your application must have "db" component.
*/
class TransactionTest extends CTestCase
{
/**
* echo additional information
*/
protected function echoInfo()
{
echo 'PDO::ATTR_EMULATE_PREPARES='.PDO::ATTR_EMULATE_PREPARES."\n"; // =20
echo 'PDO::ATTR_AUTOCOMMIT='.PDO::ATTR_AUTOCOMMIT."\n"; // =0
$row = Yii::app()->db->createCommand('select @@global.init_connect, '
.'@@global.autocommit, @@session.autocommit;')->queryRow();
echo var_export($row,true)."\n\n";
}
/**
* recreate a table
*/
public function setUp()
{
$sql = 'drop table if exists t1; '
.'create table t1 (id int(11) not null auto_increment, val varchar(45) default null, '
.'primary key (id)) engine=innodb default charset=utf8;';
Yii::app()->db->createCommand($sql)->execute();
echo 'Table recreated.'."\n\n";
}
public function testForEchoInfo()
{
$this->echoInfo();
}
/**
* a few queries in one string
*/
public function testMultipleQueries()
{
echo __METHOD__."\n";
$sql = 'insert into t1 (val) value (1);'
.'insert into t1 (_ERROR_) value (3);' // (re-)comment to test different variant
.'insert into t1 (val) value (2);'
;
/** @var CDbConnection $conn */
$conn = Yii::app()->db;
$transaction = $conn->beginTransaction();
try {
$conn->createCommand($sql)->execute();
$transaction->commit();
echo 'commit'."\n";
}
catch(Exception $e)
{
$transaction->rollback();
echo 'rollback'."\n";
}
}
/**
* one query per string
*/
public function testSqlByOrder()
{
echo __METHOD__."\n";
$sql1 = 'insert into t1 (val) value (1);';
$sql2 = 'insert into t1 (_ERROR_) value (3);'; // invalid SQL
$sql3 = 'insert into t1 (val) value (2);';
/** @var CDbConnection $conn */
$conn = Yii::app()->db;
$transaction = $conn->beginTransaction();
try {
$conn->createCommand($sql1)->execute();
$conn->createCommand($sql2)->execute(); // invalid SQL
$conn->createCommand($sql3)->execute();
$transaction->commit();
echo 'commit'."\n";
}
catch(Exception $e)
{
$transaction->rollback();
echo 'rollback'."\n";
}
}
/**
* a few queries in one string with PDO
*/
public function testPDO_MultipleQueries()
{
echo __METHOD__."\n";
$db = new PDO(Yii::app()->db->connectionString,
Yii::app()->db->username, Yii::app()->db->password);
$sql = 'insert into t1 (val) value (1);'
.'insert into t1 (_ERROR_) value (3);'
.'insert into t1 (val) value (2);';
$this->assertTrue( $db->beginTransaction() );
try {
$success = $db->prepare($sql)->execute();
if (!$success)
throw new Exception('error!');
$db->commit();
echo 'commit'."\n";
}
catch(Exception $e)
{
$db->rollBack();
echo 'rollback'."\n";
}
}
/**
* one query per string with PDO
*
* $db->prepare($sql2)->execute(); // return false if a sql error occurs
* see http_//www.php.net/manual/en/pdostatement.execute.php
*/
public function testPDO_SqlByOrder()
{
echo __METHOD__."\n";
$db = new PDO(Yii::app()->db->connectionString,
Yii::app()->db->username, Yii::app()->db->password);
$sql1 = 'insert into t1 (val) value (1);';
$sql2 = 'insert into t1 (_ERROR_) value (3);';
$sql3 = 'insert into t1 (val) value (2);';
$this->assertTrue( $db->beginTransaction() );
try {
$success = true;
$success = $success && $db->prepare($sql1)->execute();
$success = $success && $db->prepare($sql2)->execute();
$success = $success && $db->prepare($sql3)->execute();
if (!$success)
throw new Exception('error!');
$db->commit();
echo 'commit'."\n";
}
catch(Exception $e)
{
$db->rollBack();
echo 'rollback'."\n";
}
}
}
The execution log is:
Table recreated.
PDO::ATTR_EMULATE_PREPARES=20
PDO::ATTR_AUTOCOMMIT=0
array (
'@@global.init_connect' => '',
'@@global.autocommit' => '1',
'@@session.autocommit' => '1',
)
Table recreated.
TransactionTest::testMultipleQueries
commit
Table recreated.
TransactionTest::testSqlByOrder
rollback
Table recreated.
TransactionTest::testPDO_MultipleQueries
commit
Table recreated.
TransactionTest::testPDO_SqlByOrder
rollback
Thanks.