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