1--TEST-- 2API vs. SQL LAST_INSERT_ID() 3--SKIPIF-- 4<?php 5 require_once('skipif.inc'); 6 require_once('skipifconnectfailure.inc'); 7?> 8--FILE-- 9<?php 10 /* 11 CAUTION: the insert_id() API call is not supposed to return 12 the same value as a call to the LAST_INSERT_ID() SQL function. 13 It is not necessarily a bug if API and SQL function return different 14 values. Check the MySQL C API reference manual for details. 15 */ 16 require_once("connect.inc"); 17 18 function get_sql_id($link) { 19 if (!($res = $link->query("SELECT LAST_INSERT_ID() AS _id"))) { 20 printf("[003] [%d] %s\n", $link->errno, $link->error); 21 return NULL; 22 } 23 $row = $res->fetch_assoc(); 24 $res->close(); 25 26 return $row['_id']; 27 } 28 29 if (!$link = my_mysqli_connect($host, $user, $passwd, $db, $port, $socket)) 30 printf("[001] Cannot connect to the server using host=%s, user=%s, passwd=***, dbname=%s, port=%s, socket=%s\n", 31 $host, $user, $db, $port, $socket); 32 33 if (!$link->query("DROP TABLE IF EXISTS test") || 34 !$link->query("CREATE TABLE test (id INT auto_increment, label varchar(10) not null, PRIMARY KEY (id)) ENGINE=MyISAM") || 35 !$link->query("INSERT INTO test (id, label) VALUES (null, 'a')")) { 36 printf("[002] [%d] %s\n", $link->errno, $link->error); 37 } 38 39 $api_id = $link->insert_id; 40 $sql_id = get_sql_id($link); 41 printf("API: %d, SQL: %d\n", $api_id, $sql_id); 42 43 if ($api_id < 1) 44 printf("[004] Expecting id > 0 got %d, [%d] %s\n", $api_id, $link->errno, $link->error) ; 45 if ($api_id != $sql_id) 46 printf("[005] SQL id %d should be equal to API id %d\n", $sql_id, $api_id); 47 48 // Not an INSERT, API value must become 0 49 if (!($res = $link->query("SELECT 1 FROM DUAL"))) 50 printf("[006] [%d] %s\n", $link->errno, $link->error); 51 else 52 $res->close(); 53 54 $api_id = $link->insert_id; 55 $new_sql_id = get_sql_id($link); 56 if (0 !== $api_id) { 57 printf("[007] API id should have been reset to 0 because previous query was SELECT, got API %d, SQL %d\n", 58 $api_id, $new_sql_id); 59 } 60 if ($new_sql_id != $sql_id) { 61 printf("[008] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id); 62 } 63 64 // Insert fails, LAST_INSERT_ID shall not change, API shall return 0 65 if ($link->query("INSERT INTO test (id, label) VALUES (null, null)")) { 66 printf("[009] The INSERT did not fail as planned, [%d] %s\n", $link->errno, $link->error); 67 } 68 $api_id = $link->insert_id; 69 $new_sql_id = get_sql_id($link); 70 71 if (0 !== $api_id) { 72 printf("[010] API id should have been reset to 0 because previous query was SELECT, got API %d, SQL %d\n", 73 $api_id, $new_sql_id); 74 } 75 if ($new_sql_id != $sql_id) { 76 printf("[011] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id); 77 } 78 79 // Sequence counter pattern... 80 if (!$link->query("UPDATE test SET id=LAST_INSERT_ID(id+1)")) 81 printf("[012] [%d] %s\n", $link->errno, $link->error); 82 83 $api_id = $link->insert_id; 84 $new_sql_id = get_sql_id($link); 85 if ($api_id < 1) 86 printf("[013] Expecting id > 0 got %d, [%d] %s\n", $api_id, $link->errno, $link->error) ; 87 if ($api_id != $new_sql_id) 88 printf("[014] SQL id %d should be equal to API id %d\n", $new_sql_id, $api_id); 89 if ($sql_id == $new_sql_id) 90 printf("[015] SQL id %d should have had changed, got %d\n", $sql_id, $new_sql_id); 91 92 $sql_id = $new_sql_id; 93 94 // Not an INSERT (after UPDATE), API value must become 0 95 if (!$link->query("SET @myvar=1")) 96 printf("[016] [%d] %s\n", $link->errno, $link->error); 97 98 $api_id = $link->insert_id; 99 $new_sql_id = get_sql_id($link); 100 if (0 !== $api_id) { 101 printf("[017] API id should have been reset to 0 because previous query was SET, got API %d, SQL %d\n", 102 $api_id, $new_sql_id); 103 } 104 if ($new_sql_id != $sql_id) { 105 printf("[018] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id); 106 } 107 108 if (!$link->query("INSERT INTO test(id, label) VALUES (LAST_INSERT_ID(id + 1), 'b')")) 109 printf("[019] [%d] %s\n", $link->errno, $link->error); 110 111 $api_id = $link->insert_id; 112 $sql_id = get_sql_id($link); 113 if ($api_id != $sql_id) 114 printf("[020] SQL id %d should be equal to API id %d\n", $sql_id, $api_id); 115 116 if (!$link->query("INSERT INTO test(label) VALUES ('c')")) 117 printf("[021] [%d] %s\n", $link->errno, $link->error); 118 119 $api_id = $link->insert_id; 120 $sql_id = get_sql_id($link); 121 if ($api_id != $sql_id) 122 printf("[022] SQL id %d should be equal to API id %d\n", $sql_id, $api_id); 123 124 if (!($res = $link->query("SELECT id, label FROM test ORDER BY id ASC"))) 125 printf("[023] [%d] %s\n", $link->errno, $link->error); 126 127 printf("Dumping table contents before INSERT...SELECT experiments...\n"); 128 while ($row = $res->fetch_assoc()) { 129 printf("id = %d, label = '%s'\n", $row['id'], $row['label']); 130 } 131 $res->close(); 132 133 if (!$link->query("INSERT INTO test(label) SELECT CONCAT(label, id) FROM test ORDER BY id ASC")) 134 printf("[024] [%d] %s\n", $link->errno, $link->error); 135 136 $api_id = $link->insert_id; 137 $sql_id = get_sql_id($link); 138 if ($api_id != $sql_id) 139 printf("[025] SQL id %d should be equal to API id %d\n", $sql_id, $api_id); 140 141 if ($link->query("INSERT INTO test(id, label) SELECT id, CONCAT(label, id) FROM test ORDER BY id ASC")) 142 printf("[026] INSERT should have failed because of duplicate PK value, [%d] %s\n", $link->errno, $link->error); 143 144 $api_id = $link->insert_id; 145 $new_sql_id = get_sql_id($link); 146 if (0 !== $api_id) { 147 printf("[027] API id should have been reset to 0 because previous query failed, got API %d, SQL %d\n", 148 $api_id, $new_sql_id); 149 } 150 if ($new_sql_id != $sql_id) { 151 printf("[028] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id); 152 } 153 154 /* API insert id will be 101 because of UPDATE, SQL unchanged */ 155 if (!$link->query(sprintf("INSERT INTO test(id, label) VALUES (%d, 'z') ON DUPLICATE KEY UPDATE id = 101", $sql_id) )) 156 printf("[029] [%d] %s\n", $link->errno, $link->error); 157 158 $api_id = $link->insert_id; 159 $new_sql_id = get_sql_id($link); 160 if ($api_id != 101) 161 printf("[030] API id should be %d got %d\n", $sql_id, $api_id); 162 if ($new_sql_id != $sql_id) { 163 printf("[031] The servers LAST_INSERT_ID() changed unexpectedly from %d to %d\n", $sql_id, $new_sql_id); 164 } 165 166 if (!($res = $link->query("SELECT id, label FROM test ORDER BY id ASC"))) 167 printf("[032] [%d] %s\n", $link->errno, $link->error); 168 169 printf("Dumping table contents after INSERT...SELECT...\n"); 170 while ($row = $res->fetch_assoc()) { 171 printf("id = %d, label = '%s'\n", $row['id'], $row['label']); 172 } 173 $res->close(); 174 175 print "done!"; 176?> 177--CLEAN-- 178<?php 179 require_once("clean_table.inc"); 180?> 181--EXPECTF-- 182API: %d, SQL: %d 183Dumping table contents before INSERT...SELECT experiments... 184id = %d, label = 'b' 185id = %d, label = 'a' 186id = %d, label = 'c' 187Dumping table contents after INSERT...SELECT... 188id = %d, label = 'b' 189id = %d, label = 'a' 190id = %d, label = 'c' 191id = %d, label = 'a%d' 192id = %d, label = 'c%d' 193id = 101, label = 'b%d' 194done!
