1--TEST--
2PDO->beginTransaction()
3--EXTENSIONS--
4pdo_mysql
5--SKIPIF--
6<?php
7require_once(__DIR__ . DIRECTORY_SEPARATOR . 'mysql_pdo_test.inc');
8MySQLPDOTest::skip();
9$db = MySQLPDOTest::factory();
10if (false == MySQLPDOTest::detect_transactional_mysql_engine($db))
11    die("skip Transactional engine not found");
12?>
13--FILE--
14<?php
15    require_once(__DIR__ . DIRECTORY_SEPARATOR . 'mysql_pdo_test.inc');
16    $db = MySQLPDOTest::factory();
17
18    MySQLPDOTest::createTestTable($db, MySQLPDOTest::detect_transactional_mysql_engine($db));
19
20    if (1 !== $db->getAttribute(PDO::ATTR_AUTOCOMMIT))
21        printf("[001] Autocommit should be on by default\n");
22
23    if (false == $db->beginTransaction())
24        printf("[002] Cannot start a transaction, [%s] [%s]\n",
25            $db->errorCode(), implode(' ', $db->errorInfo()));
26
27    if (1 !== $db->getAttribute(PDO::ATTR_AUTOCOMMIT))
28        printf("[003] Autocommit should be on by default, beginTransaction() shall not impact it\n");
29
30    if (0 == $db->exec('DELETE FROM test'))
31        printf("[004] No rows deleted, can't be true.\n");
32
33    /* This is the PDO way to close a connection */
34    $db = null;
35    $db = MySQLPDOTest::factory();
36    $db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);
37
38    /* Autocommit was off - by definition. Commit was not issued. DELETE should have been rolled back. */
39    if (!($stmt = $db->query('SELECT id, label FROM test ORDER BY id ASC')))
40        printf("[005] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
41
42    $row = $stmt->fetch(PDO::FETCH_ASSOC);
43    var_dump($row);
44
45    if (!$db->beginTransaction())
46        printf("[006] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
47
48    if (1 !== $db->exec(sprintf('DELETE FROM test WHERE id = %d', $row['id'])))
49        printf("[007] DELETE should have indicated 1 deleted row, [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
50
51    if (!$db->commit())
52        printf("[008] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
53
54    if (1 !== $db->getAttribute(PDO::ATTR_AUTOCOMMIT))
55        printf("[009] Autocommit should be on after commit()\n");
56
57    if (!($stmt = $db->query(sprintf('SELECT id, label FROM test WHERE id = %d', $row['id']))))
58        printf("[010] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
59
60    var_dump($stmt->fetch(PDO::FETCH_ASSOC));
61
62    if (!$db->beginTransaction())
63        printf("[011] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
64
65    $db->exec(sprintf("INSERT INTO test(id, label) VALUES (%d, 'z')", $row['id']));
66
67    if (!($stmt = $db->query(sprintf('SELECT id, label FROM test WHERE id = %d', $row['id']))))
68        printf("[012] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
69
70    $new_row1 = $stmt->fetch(PDO::FETCH_ASSOC);
71    var_dump($new_row1);
72
73    if (!$db->commit())
74        printf("[013] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
75
76    if (!($stmt = $db->query(sprintf('SELECT id, label FROM test WHERE id = %d', $row['id']))))
77        printf("[014] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
78
79    $new_row2 = $stmt->fetch(PDO::FETCH_ASSOC);
80    if ($new_row1 != $new_row2) {
81        printf("[015] Results must not differ!\n");
82        var_dump($new_row1);
83        var_dump($new_row2);
84    }
85
86    if (!$db->beginTransaction())
87        printf("[016] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
88
89    if (1 !== $db->exec(sprintf('DELETE FROM test WHERE id = %d', $row['id'])))
90        printf("[017] DELETE should have indicated 1 deleted row, [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
91
92    if (!$db->rollback())
93        printf("[018] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
94
95    if (1 !== $db->getAttribute(PDO::ATTR_AUTOCOMMIT))
96        printf("[019] Autocommit should be on after rollback\n");
97
98    if (!($stmt = $db->query(sprintf('SELECT id, label FROM test WHERE id = %d', $row['id']))))
99        printf("[020] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
100
101    $new_row2 = $stmt->fetch(PDO::FETCH_ASSOC);
102    if ($new_row1 != $new_row2) {
103        printf("[021] Results must not differ!\n");
104        var_dump($new_row1);
105        var_dump($new_row2);
106    }
107
108    // now, lets check the server variables
109    if (!($stmt = $db->query('SELECT @@autocommit as auto_commit')))
110        printf("[022] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
111
112    $tmp = $stmt->fetch(PDO::FETCH_ASSOC);
113    if ($tmp['auto_commit'] != 1)
114        printf("[023] MySQL Server should indicate autocommit mode, expecting 1, got '%s', [%d] %s\n",
115            $tmp['auto_commit'], $stmt->errorCode(), $stmt->errorInfo());
116
117    if (!$db->beginTransaction())
118        printf("[024] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
119
120    if (!($stmt = $db->query('SELECT @@autocommit as auto_commit')))
121        printf("[025] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
122
123    $tmp = $stmt->fetch(PDO::FETCH_ASSOC);
124    if ($tmp['auto_commit'] != 0)
125        printf("[026] Autocommit mode of the MySQL Server should be off, got '%s', [%d] %s\n",
126            $tmp['auto_commit'], $stmt->errorCode(), trim(implode(' ', $stmt->errorInfo())));
127
128    $db->commit();
129    // Now we should be back to autocommit - we've issues a commit
130    if ($tmp['auto_commit'] != 1)
131        printf("[027] MySQL Server should indicate autocommit mode, expecting 1, got '%s', [%d] %s\n",
132            $tmp['auto_commit'], $stmt->errorCode(), $stmt->errorInfo());
133
134    // Turn off autocommit using a server variable
135    $db->exec('SET @@autocommit = 0');
136    if (1 === $db->getAttribute(PDO::ATTR_AUTOCOMMIT))
137        printf("[028] I'm confused, how can autocommit be on? Didn't I say I want to manually control transactions?\n");
138
139    if (!$db->beginTransaction())
140        printf("[029] Cannot start a transaction, [%d] %s\n",
141            $db->errorCode(), implode(' ', $db->errorInfo()));
142
143    try {
144        if (false !== $db->beginTransaction()) {
145            printf("[030] No false and no exception - that's wrong.\n");
146        }
147    } catch (PDOException $e) {
148        assert($e->getMessage() != '');
149    }
150
151    // TODO: What about an engine that does not support transactions?
152    $db = MySQLPDOTest::factory();
153    MySQLPDOTest::createTestTable($db, 'MyISAM');
154
155    if (false == $db->beginTransaction())
156        printf("[031] Cannot start a transaction, [%s] [%s]\n",
157            $db->errorCode(), implode(' ', $db->errorInfo()));
158
159    if (1 !== $db->getAttribute(PDO::ATTR_AUTOCOMMIT))
160        printf("[032] Autocommit should be on my default, beginTransaction() should not change that\n");
161
162    if (0 == $db->exec('DELETE FROM test'))
163        printf("[033] No rows deleted, can't be true.\n");
164
165    if (!$db->commit())
166        printf("[034] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
167
168    if (false == $db->beginTransaction())
169        printf("[035] Cannot start a transaction, [%s] [%s]\n",
170            $db->errorCode(), implode(' ', $db->errorInfo()));
171
172    if (0 == $db->exec("INSERT INTO test(id, label) VALUES (1, 'a')"))
173        printf("[036] Cannot insert data, [%s] [%s]\n",
174            $db->errorCode(), implode(' ', $db->errorInfo()));
175
176    // Should cause a Server warning but no error
177    if (!$db->rollback())
178        printf("[037] [%s] %s\n", $db->errorCode(), implode(' ', $db->errorInfo()));
179
180    var_dump($db->errorCode());
181
182    if (1 != $db->exec('DELETE FROM test'))
183        printf("[038] No rows deleted, can't be true.\n");
184
185    print "done!";
186?>
187--CLEAN--
188<?php
189require __DIR__ . '/mysql_pdo_test.inc';
190MySQLPDOTest::dropTestTable();
191?>
192--EXPECT--
193array(2) {
194  ["id"]=>
195  string(1) "1"
196  ["label"]=>
197  string(1) "a"
198}
199bool(false)
200array(2) {
201  ["id"]=>
202  string(1) "1"
203  ["label"]=>
204  string(1) "z"
205}
206[026] Autocommit mode of the MySQL Server should be off, got '1', [0] 00000
207[028] I'm confused, how can autocommit be on? Didn't I say I want to manually control transactions?
208string(5) "00000"
209done!
210