Drizzle

Merge lp:~stewart/drizzle/embedded-innodb-autoincrement into lp:~drizzle-trunk/drizzle/development

  1. embedded-innodb-autoincrement
  2. Merge into development
Proposed by Stewart Smith
Status: Merged
Merged at revision: 1536
Proposed branch: lp:~stewart/drizzle/embedded-innodb-autoincrement
Merge into: lp:~drizzle-trunk/drizzle/development
Prerequisite: lp:~stewart/drizzle/reenable-embedded_innodb
Diff against target: 1325 lines (+982/-44)
14 files modified
plugin/embedded_innodb/embedded_innodb_engine.cc (+245/-34)
plugin/embedded_innodb/embedded_innodb_engine.h (+11/-10)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/alter_table_drop_temp.result (+6/-0)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/auto_increment.result (+346/-0)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/auto_increment_neg_gaps.result (+21/-0)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/basic_auto_increment.result (+9/-0)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/alter_table_drop_temp-master.opt (+1/-0)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/alter_table_drop_temp.test (+4/-0)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment-master.opt (+1/-0)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment.test (+318/-0)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment_neg_gaps-master.opt (+1/-0)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment_neg_gaps.test (+14/-0)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/basic_auto_increment-master.opt (+1/-0)
plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/basic_auto_increment.test (+4/-0)
To merge this branch: bzr merge lp:~stewart/drizzle/embedded-innodb-autoincrement
Reviewer Review Type Date Requested Status
Jay Pipes (community) Approve
Brian Aker Pending
Drizzle Developers Pending
Review via email: mp+23814@code.launchpad.net

Description of the change

AUTO_INCREMENT support for embedded_innodb.

Jay will no doubt have fun with the abismal bits that is the SE API for auto_increment.

auto inc offset and auto inc increment are not yet supported.

To post a comment you must log in.
lp:~stewart/drizzle/embedded-innodb-autoincrement updated
1428. By Stewart Smith

merge trunk

1429. By Stewart Smith

Merged reenable-embedded_innodb into embedded-innodb-autoincrement.

Revision history for this message
Jay Pipes (jaypipes) wrote :

Hi Stew!

Yes, craptastic indeed. It would be useful to see some documenation in the code as to what this block is attempting to do:

282 + if (temp_auto <= innobase_get_int_col_max_value(table->next_number_field))
283 + {
284 + while (true)
285 + {
286 + uint64_t fetched_auto= share->auto_increment_value;
287 +
288 + if (temp_auto >= fetched_auto)
289 + {
290 + uint64_t store_value= temp_auto+1;
291 + if (store_value == 0)
292 + store_value++;
293 +
294 + if (share->auto_increment_value.compare_and_swap(store_value, fetched_auto) == fetched_auto)
295 + break;
296 + }
297 + else
298 + break;
299 + }
300 + }

Also, the above algorithm doesn't seem to be starvation-free, though starvation is highly unlikely. Perhaps a comment saying so might be worth it.

Other than that, good test cases and good code, though a bit awkward from an API standpoint (as you acknowledge)

-jay

review: Approve
lp:~stewart/drizzle/embedded-innodb-autoincrement updated
1430. By Stewart Smith

merge trunk

1431. By Stewart Smith

getInitialAutoIncrementValue() calls new names for index_init and index_end()

1432. By Stewart Smith

Merged reenable-embedded_innodb into embedded-innodb-autoincrement.

1433. By Stewart Smith

Merged reenable-embedded_innodb into embedded-innodb-autoincrement.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'plugin/embedded_innodb/embedded_innodb_engine.cc'
2--- plugin/embedded_innodb/embedded_innodb_engine.cc 2010-05-17 13:02:27 +0000
3+++ plugin/embedded_innodb/embedded_innodb_engine.cc 2010-05-17 13:02:28 +0000
4@@ -16,6 +16,61 @@
5 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
6 */
7
8+/* innobase_get_int_col_max_value() comes from ha_innodb.cc which is under
9+ the following license and Copyright */
10+
11+/*****************************************************************************
12+
13+Copyright (c) 2000, 2009, MySQL AB & Innobase Oy. All Rights Reserved.
14+Copyright (c) 2008, 2009 Google Inc.
15+
16+Portions of this file contain modifications contributed and copyrighted by
17+Google, Inc. Those modifications are gratefully acknowledged and are described
18+briefly in the InnoDB documentation. The contributions by Google are
19+incorporated with their permission, and subject to the conditions contained in
20+the file COPYING.Google.
21+
22+This program is free software; you can redistribute it and/or modify it under
23+the terms of the GNU General Public License as published by the Free Software
24+Foundation; version 2 of the License.
25+
26+This program is distributed in the hope that it will be useful, but WITHOUT
27+ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
28+FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
29+
30+You should have received a copy of the GNU General Public License along with
31+this program; if not, write to the Free Software Foundation, Inc., 59 Temple
32+Place, Suite 330, Boston, MA 02111-1307 USA
33+
34+*****************************************************************************/
35+/***********************************************************************
36+
37+Copyright (c) 1995, 2009, Innobase Oy. All Rights Reserved.
38+Copyright (c) 2009, Percona Inc.
39+
40+Portions of this file contain modifications contributed and copyrighted
41+by Percona Inc.. Those modifications are
42+gratefully acknowledged and are described briefly in the InnoDB
43+documentation. The contributions by Percona Inc. are incorporated with
44+their permission, and subject to the conditions contained in the file
45+COPYING.Percona.
46+
47+This program is free software; you can redistribute it and/or modify it
48+under the terms of the GNU General Public License as published by the
49+Free Software Foundation; version 2 of the License.
50+
51+This program is distributed in the hope that it will be useful, but
52+WITHOUT ANY WARRANTY; without even the implied warranty of
53+MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
54+Public License for more details.
55+
56+You should have received a copy of the GNU General Public License along
57+with this program; if not, write to the Free Software Foundation, Inc.,
58+59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
59+
60+***********************************************************************/
61+
62+
63 #include "config.h"
64 #include <drizzled/table.h>
65 #include <drizzled/error.h>
66@@ -48,7 +103,7 @@
67 using namespace google;
68 using namespace drizzled;
69
70-int read_row_from_innodb(ib_crsr_t cursor, ib_tpl_t tuple, Table* table);
71+int read_row_from_innodb(unsigned char* buf, ib_crsr_t cursor, ib_tpl_t tuple, Table* table);
72 static void fill_ib_search_tpl_from_drizzle_key(ib_tpl_t search_tuple,
73 const drizzled::KeyInfo *key_info,
74 const unsigned char *key_ptr,
75@@ -134,13 +189,11 @@
76 HA_READ_ORDER |
77 HA_KEYREAD_ONLY);
78 }
79-private:
80 virtual int doStartTransaction(Session *session,
81 start_transaction_option_t options);
82 virtual void doStartStatement(Session *session);
83 virtual void doEndStatement(Session *session);
84
85-public:
86 virtual int doSetSavepoint(Session* session,
87 drizzled::NamedSavepoint &savepoint);
88 virtual int doRollbackToSavepoint(Session* session,
89@@ -156,6 +209,7 @@
90 void addOpenTable(const std::string &table_name, EmbeddedInnoDBTableShare *);
91 void deleteOpenTable(const std::string &table_name);
92
93+ uint64_t getInitialAutoIncrementValue(EmbeddedInnoDBCursor *cursor);
94 };
95
96 static drizzled::plugin::StorageEngine *embedded_innodb_engine= NULL;
97@@ -296,6 +350,59 @@
98
99 static pthread_mutex_t embedded_innodb_mutex= PTHREAD_MUTEX_INITIALIZER;
100
101+uint64_t EmbeddedInnoDBCursor::getInitialAutoIncrementValue()
102+{
103+ uint64_t nr;
104+ int error;
105+
106+ (void) extra(HA_EXTRA_KEYREAD);
107+ table->mark_columns_used_by_index_no_reset(table->s->next_number_index);
108+ doStartIndexScan(table->s->next_number_index, 1);
109+ if (table->s->next_number_keypart == 0)
110+ { // Autoincrement at key-start
111+ error=index_last(table->record[1]);
112+ }
113+ else
114+ {
115+ unsigned char key[MAX_KEY_LENGTH];
116+ key_copy(key, table->record[0],
117+ table->key_info + table->s->next_number_index,
118+ table->s->next_number_key_offset);
119+ error= index_read_map(table->record[1], key,
120+ make_prev_keypart_map(table->s->next_number_keypart),
121+ HA_READ_PREFIX_LAST);
122+ }
123+
124+ if (error)
125+ nr=1;
126+ else
127+ nr= ((uint64_t) table->found_next_number_field->
128+ val_int_offset(table->s->rec_buff_length)+1);
129+ doEndIndexScan();
130+ (void) extra(HA_EXTRA_NO_KEYREAD);
131+
132+ if (table->s->getTableProto()->options().auto_increment_value() > nr)
133+ nr= table->s->getTableProto()->options().auto_increment_value();
134+
135+ return nr;
136+}
137+
138+EmbeddedInnoDBTableShare::EmbeddedInnoDBTableShare(const char* name, uint64_t initial_auto_increment_value)
139+ : use_count(0)
140+{
141+ table_name.assign(name);
142+ auto_increment_value.fetch_and_store(initial_auto_increment_value);
143+}
144+
145+uint64_t EmbeddedInnoDBEngine::getInitialAutoIncrementValue(EmbeddedInnoDBCursor *cursor)
146+{
147+ doStartTransaction(current_session, START_TRANS_NO_OPTIONS);
148+ uint64_t initial_auto_increment_value= cursor->getInitialAutoIncrementValue();
149+ doCommit(current_session, true);
150+
151+ return initial_auto_increment_value;
152+}
153+
154 EmbeddedInnoDBTableShare *EmbeddedInnoDBCursor::get_share(const char *table_name, int *rc)
155 {
156 pthread_mutex_lock(&embedded_innodb_mutex);
157@@ -305,7 +412,11 @@
158
159 if (!share)
160 {
161- share= new EmbeddedInnoDBTableShare(table_name);
162+ uint64_t initial_auto_increment_value= 0;
163+ if (table->found_next_number_field)
164+ initial_auto_increment_value= a_engine->getInitialAutoIncrementValue(this);
165+
166+ share= new EmbeddedInnoDBTableShare(table_name, initial_auto_increment_value);
167
168 if (share == NULL)
169 {
170@@ -400,15 +511,34 @@
171 return to;
172 }
173
174+void EmbeddedInnoDBCursor::get_auto_increment(uint64_t, //offset,
175+ uint64_t, //increment,
176+ uint64_t, //nb_dis,
177+ uint64_t *first_value,
178+ uint64_t *nb_reserved_values)
179+{
180+fetch:
181+ *first_value= share->auto_increment_value.fetch_and_increment();
182+ if (*first_value == 0)
183+ {
184+ /* if it's zero, then we skip it... why? because of ass.
185+ set auto-inc to -1 and the sequence is:
186+ -1, 1.
187+ Zero is still "magic".
188+ */
189+ share->auto_increment_value.compare_and_swap(1, 0);
190+ goto fetch;
191+ }
192+ *nb_reserved_values= 1;
193+}
194+
195 static void TableIdentifier_to_innodb_name(TableIdentifier &identifier, std::string *str)
196 {
197 str->reserve(identifier.getSchemaName().length() + identifier.getTableName().length() + 1);
198- str->append(identifier.getPath().c_str()+2);
199-/*
200+// str->append(identifier.getPath().c_str()+2);
201 str->assign(identifier.getSchemaName());
202 str->append("/");
203 str->append(identifier.getTableName());
204-*/
205 }
206
207 EmbeddedInnoDBCursor::EmbeddedInnoDBCursor(drizzled::plugin::StorageEngine &engine_arg,
208@@ -1192,22 +1322,86 @@
209 return err;
210 }
211
212+static uint64_t innobase_get_int_col_max_value(const Field* field)
213+{
214+ uint64_t max_value = 0;
215+
216+ switch(field->key_type()) {
217+ /* TINY */
218+ case HA_KEYTYPE_BINARY:
219+ max_value = 0xFFULL;
220+ break;
221+ /* MEDIUM */
222+ case HA_KEYTYPE_UINT24:
223+ max_value = 0xFFFFFFULL;
224+ break;
225+ /* LONG */
226+ case HA_KEYTYPE_ULONG_INT:
227+ max_value = 0xFFFFFFFFULL;
228+ break;
229+ case HA_KEYTYPE_LONG_INT:
230+ max_value = 0x7FFFFFFFULL;
231+ break;
232+ /* BIG */
233+ case HA_KEYTYPE_ULONGLONG:
234+ max_value = 0xFFFFFFFFFFFFFFFFULL;
235+ break;
236+ case HA_KEYTYPE_LONGLONG:
237+ max_value = 0x7FFFFFFFFFFFFFFFULL;
238+ break;
239+ case HA_KEYTYPE_DOUBLE:
240+ /* We use the maximum as per IEEE754-2008 standard, 2^53 */
241+ max_value = 0x20000000000000ULL;
242+ break;
243+ default:
244+ assert(false);
245+ }
246+
247+ return(max_value);
248+}
249+
250 int EmbeddedInnoDBCursor::doInsertRecord(unsigned char *)
251 {
252+ ib_err_t err;
253+ int ret= 0;
254+
255+ ib_trx_t transaction= *get_trx(ha_session());
256+
257+ tuple= ib_clust_read_tuple_create(cursor);
258+
259+ ib_cursor_attach_trx(cursor, transaction);
260+
261+ err= ib_cursor_first(cursor);
262+ assert(err == DB_SUCCESS || err == DB_END_OF_INDEX);
263+
264+
265 if (table->next_number_field)
266+ {
267 update_auto_increment();
268
269- ib_err_t err;
270- int ret= 0;
271-
272- ib_trx_t transaction= *get_trx(ha_session());
273-
274- tuple= ib_clust_read_tuple_create(cursor);
275-
276- ib_cursor_attach_trx(cursor, transaction);
277-
278- err= ib_cursor_first(cursor);
279- assert(err == DB_SUCCESS || err == DB_END_OF_INDEX);
280+ uint64_t temp_auto= table->next_number_field->val_int();
281+
282+ if (temp_auto <= innobase_get_int_col_max_value(table->next_number_field))
283+ {
284+ while (true)
285+ {
286+ uint64_t fetched_auto= share->auto_increment_value;
287+
288+ if (temp_auto >= fetched_auto)
289+ {
290+ uint64_t store_value= temp_auto+1;
291+ if (store_value == 0)
292+ store_value++;
293+
294+ if (share->auto_increment_value.compare_and_swap(store_value, fetched_auto) == fetched_auto)
295+ break;
296+ }
297+ else
298+ break;
299+ }
300+ }
301+
302+ }
303
304 write_row_to_innodb_tuple(table->field, tuple);
305
306@@ -1242,9 +1436,13 @@
307 err= ib_cursor_update_row(cursor, tuple, update_tuple);
308
309 ib_tuple_delete(update_tuple);
310+ ib_err_t err2= ib_cursor_next(cursor);
311+ (void)err2;
312
313 if (err == DB_SUCCESS)
314 return 0;
315+ else if (err == DB_DUPLICATE_KEY)
316+ return HA_ERR_FOUND_DUPP_KEY;
317 else
318 return -1;
319 }
320@@ -1319,7 +1517,15 @@
321
322 int EmbeddedInnoDBCursor::doStartTableScan(bool)
323 {
324- ib_trx_t transaction= *get_trx(ha_session());
325+ ib_trx_t transaction;
326+
327+ if(*get_trx(current_session) == NULL)
328+ {
329+ EmbeddedInnoDBEngine *innodb_engine= static_cast<EmbeddedInnoDBEngine*>(engine);
330+ innodb_engine->doStartTransaction(current_session, START_TRANS_NO_OPTIONS);
331+ }
332+
333+ transaction= *get_trx(ha_session());
334
335 ib_cursor_attach_trx(cursor, transaction);
336
337@@ -1332,9 +1538,10 @@
338 return(0);
339 }
340
341-int read_row_from_innodb(ib_crsr_t cursor, ib_tpl_t tuple, Table* table)
342+int read_row_from_innodb(unsigned char* buf, ib_crsr_t cursor, ib_tpl_t tuple, Table* table)
343 {
344 ib_err_t err;
345+ ptrdiff_t row_offset= buf - table->record[0];
346
347 err= ib_cursor_read_row(cursor, tuple);
348
349@@ -1348,6 +1555,8 @@
350 if (! (**field).isReadSet())
351 continue;
352
353+ (**field).move_field_offset(row_offset);
354+
355 (**field).setWriteSet();
356
357 uint32_t length= ib_col_get_len(tuple, colnr);
358@@ -1369,18 +1578,21 @@
359 {
360 ib_col_copy_value(tuple, colnr, (*field)->ptr, (*field)->data_length());
361 }
362+
363+ (**field).move_field_offset(-row_offset);
364+
365 }
366
367 return 0;
368 }
369
370-int EmbeddedInnoDBCursor::rnd_next(unsigned char *)
371+int EmbeddedInnoDBCursor::rnd_next(unsigned char *buf)
372 {
373 ib_err_t err;
374 int ret;
375
376 tuple= ib_tuple_clear(tuple);
377- ret= read_row_from_innodb(cursor, tuple, table);
378+ ret= read_row_from_innodb(buf, cursor, tuple, table);
379
380 err= ib_cursor_next(cursor);
381
382@@ -1398,7 +1610,7 @@
383 return 0;
384 }
385
386-int EmbeddedInnoDBCursor::rnd_pos(unsigned char *, unsigned char *pos)
387+int EmbeddedInnoDBCursor::rnd_pos(unsigned char *buf, unsigned char *pos)
388 {
389 ib_err_t err;
390 int res;
391@@ -1414,7 +1626,7 @@
392 ib_tuple_delete(search_tuple);
393
394 tuple= ib_tuple_clear(tuple);
395- ret= read_row_from_innodb(cursor, tuple, table);
396+ ret= read_row_from_innodb(buf, cursor, tuple, table);
397
398 err= ib_cursor_next(cursor);
399
400@@ -1641,7 +1853,6 @@
401 ib_err_t err;
402 int ret;
403 ib_srch_mode_t search_mode;
404- (void)buf;
405
406 search_mode= ha_rkey_function_to_ib_srch_mode(find_flag);
407
408@@ -1661,13 +1872,13 @@
409 return HA_ERR_END_OF_FILE;
410
411 tuple= ib_tuple_clear(tuple);
412- ret= read_row_from_innodb(cursor, tuple, table);
413+ ret= read_row_from_innodb(buf, cursor, tuple, table);
414 err= ib_cursor_next(cursor);
415
416 return 0;
417 }
418
419-int EmbeddedInnoDBCursor::index_next(unsigned char *)
420+int EmbeddedInnoDBCursor::index_next(unsigned char *buf)
421 {
422 int ret= HA_ERR_END_OF_FILE;
423
424@@ -1675,7 +1886,7 @@
425 return HA_ERR_END_OF_FILE;
426
427 tuple= ib_tuple_clear(tuple);
428- ret= read_row_from_innodb(cursor, tuple, table);
429+ ret= read_row_from_innodb(buf, cursor, tuple, table);
430 next_innodb_error= ib_cursor_next(cursor);
431
432 return ret;
433@@ -1688,7 +1899,7 @@
434 return doEndTableScan();
435 }
436
437-int EmbeddedInnoDBCursor::index_prev(unsigned char *)
438+int EmbeddedInnoDBCursor::index_prev(unsigned char *buf)
439 {
440 int ret= HA_ERR_END_OF_FILE;
441 ib_err_t err;
442@@ -1696,7 +1907,7 @@
443 if (active_index == 0)
444 {
445 tuple= ib_tuple_clear(tuple);
446- ret= read_row_from_innodb(cursor, tuple, table);
447+ ret= read_row_from_innodb(buf, cursor, tuple, table);
448 err= ib_cursor_prev(cursor);
449 }
450
451@@ -1704,7 +1915,7 @@
452 }
453
454
455-int EmbeddedInnoDBCursor::index_first(unsigned char *)
456+int EmbeddedInnoDBCursor::index_first(unsigned char *buf)
457 {
458 int ret= HA_ERR_END_OF_FILE;
459 ib_err_t err;
460@@ -1719,14 +1930,14 @@
461 }
462
463 tuple= ib_tuple_clear(tuple);
464- ret= read_row_from_innodb(cursor, tuple, table);
465+ ret= read_row_from_innodb(buf, cursor, tuple, table);
466 next_innodb_error= ib_cursor_next(cursor);
467
468 return ret;
469 }
470
471
472-int EmbeddedInnoDBCursor::index_last(unsigned char *)
473+int EmbeddedInnoDBCursor::index_last(unsigned char *buf)
474 {
475 int ret= HA_ERR_END_OF_FILE;
476 ib_err_t err;
477@@ -1743,7 +1954,7 @@
478 if (active_index == 0)
479 {
480 tuple= ib_tuple_clear(tuple);
481- ret= read_row_from_innodb(cursor, tuple, table);
482+ ret= read_row_from_innodb(buf, cursor, tuple, table);
483 err= ib_cursor_prev(cursor);
484 }
485
486
487=== modified file 'plugin/embedded_innodb/embedded_innodb_engine.h'
488--- plugin/embedded_innodb/embedded_innodb_engine.h 2010-05-17 13:02:27 +0000
489+++ plugin/embedded_innodb/embedded_innodb_engine.h 2010-05-17 13:02:28 +0000
490@@ -20,18 +20,18 @@
491 #define PLUGIN_EMBEDDED_INNODB_EMBEDDED_INNODB_ENGINE_H
492
493 #include <drizzled/cursor.h>
494+#include <drizzled/atomics.h>
495
496 class EmbeddedInnoDBTableShare
497 {
498 public:
499- EmbeddedInnoDBTableShare(const char* name) : use_count(0)
500- {
501- table_name.assign(name);
502- }
503+ EmbeddedInnoDBTableShare(const char* name, uint64_t intial_auto_increment_value);
504
505 drizzled::THR_LOCK lock;
506 int use_count;
507 std::string table_name;
508+
509+ drizzled::atomic<uint64_t> auto_increment_value;
510 };
511
512 class EmbeddedInnoDBCursor: public drizzled::Cursor
513@@ -71,12 +71,6 @@
514 int delete_all_rows(void);
515 int doUpdateRecord(const unsigned char * old_data, unsigned char * new_data);
516
517- void get_auto_increment(uint64_t, uint64_t,
518- uint64_t,
519- uint64_t *,
520- uint64_t *)
521- {}
522-
523 EmbeddedInnoDBTableShare *get_share(const char *table_name, int *rc);
524 int free_share();
525
526@@ -86,6 +80,13 @@
527 drizzled::THR_LOCK_DATA **to,
528 drizzled::thr_lock_type);
529
530+ uint64_t getInitialAutoIncrementValue();
531+ void get_auto_increment(uint64_t ,
532+ uint64_t ,
533+ uint64_t ,
534+ uint64_t *first_value,
535+ uint64_t *nb_reserved_values);
536+
537 private:
538 ib_crsr_t cursor;
539 ib_tpl_t tuple;
540
541=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/alter_table_drop_temp.result'
542--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/alter_table_drop_temp.result 1970-01-01 00:00:00 +0000
543+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/alter_table_drop_temp.result 2010-05-17 13:02:28 +0000
544@@ -0,0 +1,6 @@
545+create table t1 (a int primary key);
546+alter table t1 add column b varchar(10);
547+show tables;
548+Tables_in_test
549+t1
550+drop table t1;
551
552=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/auto_increment.result'
553--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/auto_increment.result 1970-01-01 00:00:00 +0000
554+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/auto_increment.result 2010-05-17 13:02:28 +0000
555@@ -0,0 +1,346 @@
556+drop table if exists t1;
557+drop table if exists t2;
558+SET SQL_WARNINGS=1;
559+create TEMPORARY table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
560+insert into t1 values (1,1),(NULL,3),(NULL,4);
561+delete from t1 where a=4;
562+insert into t1 values (NULL,5),(NULL,6);
563+select * from t1;
564+a b
565+1 1
566+3 3
567+5 5
568+6 6
569+delete from t1 where a=6;
570+show table status like "t1";
571+Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
572+# test t1 TEMPORARY MyISAM # # # # #
573+replace t1 values (3,1);
574+ALTER TABLE t1 add c int;
575+replace t1 values (3,3,3);
576+insert into t1 values (NULL,7,7);
577+update t1 set a=8,b=b+1,c=c+1 where a=7;
578+insert into t1 values (NULL,9,9);
579+select * from t1;
580+a b c
581+1 1 NULL
582+3 3 3
583+5 5 NULL
584+8 8 8
585+9 9 9
586+drop table t1;
587+create table t1 (
588+skey int NOT NULL auto_increment PRIMARY KEY,
589+sval char(20)
590+);
591+insert into t1 values (NULL, "hello");
592+insert into t1 values (NULL, "hey");
593+select * from t1;
594+skey sval
595+1 hello
596+2 hey
597+select _rowid,t1._rowid,skey,sval from t1;
598+_rowid _rowid skey sval
599+1 1 1 hello
600+2 2 2 hey
601+drop table t1;
602+create table t1 (a int not null primary key auto_increment);
603+insert into t1 values (0);
604+update t1 set a=0;
605+select * from t1;
606+a
607+0
608+check table t1;
609+Table Op Msg_type Msg_text
610+test.t1 check status OK
611+drop table t1;
612+create table t1 (a int not null auto_increment primary key);
613+insert into t1 values (NULL);
614+insert into t1 values (-1);
615+select last_insert_id();
616+last_insert_id()
617+1
618+insert into t1 values (NULL);
619+select * from t1;
620+a
621+-1
622+1
623+2
624+drop table t1;
625+create temporary table t1 (a int not null auto_increment primary key) /*!40102 engine=MEMORY */;
626+insert into t1 values (NULL);
627+insert into t1 values (-1);
628+select last_insert_id();
629+last_insert_id()
630+1
631+insert into t1 values (NULL);
632+select * from t1;
633+a
634+1
635+-1
636+2
637+drop table t1;
638+create table t1 (i int not null auto_increment, key (i));
639+insert into t1 set i = 254;
640+insert into t1 set i = null;
641+select last_insert_id();
642+last_insert_id()
643+255
644+insert into t1 set i = null;
645+select last_insert_id();
646+last_insert_id()
647+256
648+drop table t1;
649+create table t1 (i int not null auto_increment primary key, b int, unique (b));
650+insert into t1 values (NULL, 10);
651+select last_insert_id();
652+last_insert_id()
653+1
654+insert into t1 values (NULL, 15);
655+select last_insert_id();
656+last_insert_id()
657+2
658+insert into t1 values (NULL, 10);
659+Got one of the listed errors
660+select last_insert_id();
661+last_insert_id()
662+2
663+drop table t1;
664+create table t1(a int auto_increment,b int null,primary key(a));
665+insert into t1(a,b)values(NULL,1);
666+insert into t1(a,b)values(200,2);
667+insert into t1(a,b)values(0,3);
668+insert into t1(b)values(4);
669+insert into t1(b)values(5);
670+insert into t1(b)values(6);
671+insert into t1(b)values(7);
672+select * from t1 order by b;
673+a b
674+1 1
675+200 2
676+0 3
677+201 4
678+202 5
679+203 6
680+204 7
681+alter table t1 modify b int;
682+show tables;
683+Tables_in_test
684+t1
685+select * from t1 order by b;
686+a b
687+1 1
688+200 2
689+0 3
690+201 4
691+202 5
692+203 6
693+204 7
694+create table t2 (a int primary key);
695+insert t2 values (1),(2);
696+alter table t2 drop primary key, add b int auto_increment primary key;
697+select * from t2;
698+a b
699+1 1
700+2 2
701+drop table t2;
702+delete from t1 where a=0;
703+select * from t1;
704+a b
705+1 1
706+200 2
707+201 4
708+202 5
709+203 6
710+204 7
711+show create table t1;
712+Table Create Table
713+t1 CREATE TABLE `t1` (
714+ `a` int NOT NULL AUTO_INCREMENT,
715+ `b` int DEFAULT NULL,
716+ PRIMARY KEY (`a`) USING BTREE
717+) ENGINE=InnoDB
718+update t1 set a=0 where b=5;
719+select * from t1 order by b;
720+a b
721+1 1
722+200 2
723+201 4
724+0 5
725+203 6
726+204 7
727+delete from t1 where a=0;
728+update t1 set a=NULL where b=6;
729+ERROR 23000: Column 'a' cannot be null
730+update t1 set a=300 where b=7;
731+insert into t1(a,b)values(NULL,8);
732+insert into t1(a,b)values(400,9);
733+insert into t1(a,b)values(0,10);
734+insert into t1(b)values(11);
735+insert into t1(b)values(12);
736+insert into t1(b)values(13);
737+insert into t1(b)values(14);
738+select * from t1 order by b;
739+a b
740+1 1
741+200 2
742+201 4
743+203 6
744+300 7
745+205 8
746+400 9
747+0 10
748+401 11
749+402 12
750+403 13
751+404 14
752+delete from t1 where a=0;
753+update t1 set a=0 where b=12;
754+select * from t1 order by b;
755+a b
756+1 1
757+200 2
758+201 4
759+203 6
760+300 7
761+205 8
762+400 9
763+401 11
764+0 12
765+403 13
766+404 14
767+delete from t1 where a=0;
768+update t1 set a=NULL where b=13;
769+ERROR 23000: Column 'a' cannot be null
770+update t1 set a=500 where b=14;
771+select * from t1 order by b;
772+a b
773+1 1
774+200 2
775+201 4
776+203 6
777+300 7
778+205 8
779+400 9
780+401 11
781+403 13
782+500 14
783+drop table t1;
784+create table t1 (a bigint, b int auto_increment primary key);
785+insert into t1 (a) values (1), (2), (3), (NULL), (NULL);
786+alter table t1 drop column b, modify a bigint not null auto_increment primary key;
787+select * from t1;
788+a
789+1
790+2
791+3
792+4
793+5
794+drop table t1;
795+create table t1 (a bigint, b int auto_increment primary key);
796+insert into t1 (a) values (1), (2), (3), (0), (0);
797+alter table t1 drop column b, modify a bigint not null auto_increment primary key;
798+Got one of the listed errors
799+select * from t1;
800+a b
801+1 1
802+2 2
803+3 3
804+0 4
805+0 5
806+drop table t1;
807+create table t1 (a bigint, b int auto_increment primary key);
808+insert into t1 (a) values (0), (1), (2), (3);
809+alter table t1 drop column b, modify a bigint not null auto_increment primary key;
810+select * from t1;
811+a
812+0
813+1
814+2
815+3
816+drop table t1;
817+create table t1 (a int auto_increment primary key , b int null);
818+insert into t1 values (0,1),(1,2),(2,3);
819+select * from t1;
820+a b
821+0 1
822+1 2
823+2 3
824+alter table t1 modify b varchar(255);
825+insert into t1 values (0,4);
826+Got one of the listed errors
827+select * from t1;
828+a b
829+0 1
830+1 2
831+2 3
832+drop table t1;
833+CREATE TABLE t1 ( a INT AUTO_INCREMENT, b VARBINARY(1000), PRIMARY KEY (a,b(10)));
834+INSERT INTO t1 (b) VALUES ('aaaa');
835+CHECK TABLE t1;
836+Table Op Msg_type Msg_text
837+test.t1 check status OK
838+INSERT INTO t1 (b) VALUES ('');
839+CHECK TABLE t1;
840+Table Op Msg_type Msg_text
841+test.t1 check status OK
842+INSERT INTO t1 (b) VALUES ('bbbb');
843+CHECK TABLE t1;
844+Table Op Msg_type Msg_text
845+test.t1 check status OK
846+DROP TABLE IF EXISTS t1;
847+CREATE TABLE t1 (
848+t1_name VARCHAR(255) DEFAULT NULL,
849+t1_id INT not null AUTO_INCREMENT,
850+KEY (t1_name),
851+PRIMARY KEY (t1_id)
852+) AUTO_INCREMENT = 1000;
853+Warnings:
854+Warning 1071 Specified key was too long; max key length is 1000 bytes
855+INSERT INTO t1 (t1_name) VALUES('MySQL');
856+INSERT INTO t1 (t1_name) VALUES('MySQL');
857+INSERT INTO t1 (t1_name) VALUES('MySQL');
858+SELECT * from t1;
859+t1_name t1_id
860+MySQL 1000
861+MySQL 1001
862+MySQL 1002
863+SHOW CREATE TABLE `t1`;
864+Table Create Table
865+t1 CREATE TABLE `t1` (
866+ `t1_name` varchar(255) DEFAULT NULL,
867+ `t1_id` int NOT NULL AUTO_INCREMENT,
868+ PRIMARY KEY (`t1_id`) USING BTREE,
869+ KEY `t1_name` (`t1_name`(250)) USING BTREE
870+) ENGINE=InnoDB
871+DROP TABLE `t1`;
872+create table t1(a int not null auto_increment primary key);
873+create table t2(a int not null auto_increment primary key, t1a int);
874+insert into t1 values(NULL);
875+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
876+insert into t1 values (NULL);
877+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
878+(NULL, LAST_INSERT_ID());
879+insert into t1 values (NULL);
880+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
881+(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
882+select * from t2;
883+a t1a
884+1 1
885+2 1
886+3 2
887+4 2
888+5 2
889+6 3
890+7 3
891+8 3
892+9 3
893+drop table t1, t2;
894+End of 4.1 tests
895+CREATE TABLE t1 (t1 INT PRIMARY KEY, t2 INT);
896+INSERT INTO t1 VALUES(0, 0);
897+INSERT INTO t1 VALUES(1, 1);
898+ALTER TABLE t1 CHANGE t1 t1 INT auto_increment;
899+INSERT INTO t1 VALUES(0,0);
900+Got one of the listed errors
901+DROP TABLE t1;
902
903=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/auto_increment_neg_gaps.result'
904--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/auto_increment_neg_gaps.result 1970-01-01 00:00:00 +0000
905+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/auto_increment_neg_gaps.result 2010-05-17 13:02:28 +0000
906@@ -0,0 +1,21 @@
907+create table t1 (a int not null auto_increment primary key);
908+insert into t1 values (NULL);
909+insert into t1 values (NULL);
910+insert into t1 values (NULL);
911+insert into t1 values (NULL);
912+insert into t1 values (NULL);
913+insert into t1 values (NULL);
914+insert into t1 values (-1);
915+select last_insert_id();
916+last_insert_id()
917+6
918+delete from t1 where a in (2,3,4);
919+insert into t1 values (NULL);
920+select * from t1;
921+a
922+-1
923+1
924+5
925+6
926+7
927+drop table t1;
928
929=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/basic_auto_increment.result'
930--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/basic_auto_increment.result 1970-01-01 00:00:00 +0000
931+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/r/basic_auto_increment.result 2010-05-17 13:02:28 +0000
932@@ -0,0 +1,9 @@
933+create table t1 (a int auto_increment primary key);
934+insert into t1 values (),(),(),();
935+select * from t1;
936+a
937+1
938+2
939+3
940+4
941+drop table t1;
942
943=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/alter_table_drop_temp-master.opt'
944--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/alter_table_drop_temp-master.opt 1970-01-01 00:00:00 +0000
945+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/alter_table_drop_temp-master.opt 2010-05-17 13:02:28 +0000
946@@ -0,0 +1,1 @@
947+--plugin_add=embedded_innodb --plugin_remove=innobase
948
949=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/alter_table_drop_temp.test'
950--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/alter_table_drop_temp.test 1970-01-01 00:00:00 +0000
951+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/alter_table_drop_temp.test 2010-05-17 13:02:28 +0000
952@@ -0,0 +1,4 @@
953+create table t1 (a int primary key);
954+alter table t1 add column b varchar(10);
955+show tables;
956+drop table t1;
957
958=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment-master.opt'
959--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment-master.opt 1970-01-01 00:00:00 +0000
960+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment-master.opt 2010-05-17 13:02:28 +0000
961@@ -0,0 +1,1 @@
962+--plugin_add=embedded_innodb --plugin_remove=innobase
963
964=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment.test'
965--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment.test 1970-01-01 00:00:00 +0000
966+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment.test 2010-05-17 13:02:28 +0000
967@@ -0,0 +1,318 @@
968+#
969+# Test of auto_increment; The test for BDB tables is in bdb.test
970+#
971+--disable_warnings
972+drop table if exists t1;
973+drop table if exists t2;
974+--enable_warnings
975+SET SQL_WARNINGS=1;
976+
977+create TEMPORARY table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
978+insert into t1 values (1,1),(NULL,3),(NULL,4);
979+delete from t1 where a=4;
980+insert into t1 values (NULL,5),(NULL,6);
981+select * from t1;
982+delete from t1 where a=6;
983+--replace_column 1 # 6 # 7 # 8 # 9 # 10 #
984+show table status like "t1";
985+replace t1 values (3,1);
986+ALTER TABLE t1 add c int;
987+replace t1 values (3,3,3);
988+insert into t1 values (NULL,7,7);
989+update t1 set a=8,b=b+1,c=c+1 where a=7;
990+insert into t1 values (NULL,9,9);
991+select * from t1;
992+drop table t1;
993+
994+create table t1 (
995+ skey int NOT NULL auto_increment PRIMARY KEY,
996+ sval char(20)
997+);
998+insert into t1 values (NULL, "hello");
999+insert into t1 values (NULL, "hey");
1000+select * from t1;
1001+select _rowid,t1._rowid,skey,sval from t1;
1002+drop table t1;
1003+
1004+#
1005+# Test auto_increment on sub key
1006+#
1007+#Drizzle does not support auto_increment on sub key.
1008+#create table t1 (a char(10) not null, b int not null auto_increment, primary key(b));
1009+#insert into t1 values ("a",1),("b",2),("a",2),("c",1);
1010+#insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
1011+#insert into t1 (a) values ("a"),("b"),("c"),("d");
1012+#insert into t1 (a) values ('k'),('d');
1013+#insert into t1 (a) values ("a");
1014+#insert into t1 values ("d",last_insert_id());
1015+#select * from t1;
1016+#drop table t1;
1017+
1018+#create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ordid), index(ord,ordid));
1019+#insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj');
1020+#select * from t1;
1021+#drop table t1;
1022+
1023+#create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid));
1024+#insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc');
1025+#select * from t1;
1026+#drop table t1;
1027+
1028+#create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid, id));
1029+#create table t2 (sid char(20), id int(2));
1030+#insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);
1031+#insert into t1 select * from t2;
1032+#select * from t1;
1033+#drop table t1,t2;
1034+
1035+#
1036+# Test of auto_increment columns when they are set to 0
1037+#
1038+
1039+create table t1 (a int not null primary key auto_increment);
1040+insert into t1 values (0);
1041+update t1 set a=0;
1042+select * from t1;
1043+check table t1;
1044+drop table t1;
1045+
1046+#
1047+# Test negative values (Bug #1366)
1048+#
1049+
1050+create table t1 (a int not null auto_increment primary key);
1051+insert into t1 values (NULL);
1052+insert into t1 values (-1);
1053+select last_insert_id();
1054+insert into t1 values (NULL);
1055+--sorted_result
1056+select * from t1;
1057+drop table t1;
1058+
1059+create temporary table t1 (a int not null auto_increment primary key) /*!40102 engine=MEMORY */;
1060+insert into t1 values (NULL);
1061+insert into t1 values (-1);
1062+select last_insert_id();
1063+insert into t1 values (NULL);
1064+select * from t1;
1065+drop table t1;
1066+
1067+create table t1 (i int not null auto_increment, key (i));
1068+insert into t1 set i = 254;
1069+insert into t1 set i = null;
1070+select last_insert_id();
1071+insert into t1 set i = null;
1072+select last_insert_id();
1073+drop table t1;
1074+
1075+create table t1 (i int not null auto_increment primary key, b int, unique (b));
1076+insert into t1 values (NULL, 10);
1077+select last_insert_id();
1078+insert into t1 values (NULL, 15);
1079+select last_insert_id();
1080+--error ER_DUP_ENTRY,1022
1081+insert into t1 values (NULL, 10);
1082+select last_insert_id();
1083+
1084+drop table t1;
1085+
1086+create table t1(a int auto_increment,b int null,primary key(a));
1087+insert into t1(a,b)values(NULL,1);
1088+insert into t1(a,b)values(200,2);
1089+insert into t1(a,b)values(0,3);
1090+insert into t1(b)values(4);
1091+insert into t1(b)values(5);
1092+insert into t1(b)values(6);
1093+insert into t1(b)values(7);
1094+select * from t1 order by b;
1095+#mediumint is not supported. changed to b int.
1096+alter table t1 modify b int;
1097+show tables;
1098+select * from t1 order by b;
1099+create table t2 (a int primary key);
1100+insert t2 values (1),(2);
1101+alter table t2 drop primary key, add b int auto_increment primary key;
1102+select * from t2;
1103+drop table t2;
1104+delete from t1 where a=0;
1105+select * from t1;
1106+show create table t1;
1107+update t1 set a=0 where b=5;
1108+select * from t1 order by b;
1109+delete from t1 where a=0;
1110+--error 1048
1111+update t1 set a=NULL where b=6;
1112+update t1 set a=300 where b=7;
1113+insert into t1(a,b)values(NULL,8);
1114+insert into t1(a,b)values(400,9);
1115+insert into t1(a,b)values(0,10);
1116+insert into t1(b)values(11);
1117+insert into t1(b)values(12);
1118+insert into t1(b)values(13);
1119+insert into t1(b)values(14);
1120+select * from t1 order by b;
1121+delete from t1 where a=0;
1122+update t1 set a=0 where b=12;
1123+select * from t1 order by b;
1124+delete from t1 where a=0;
1125+--error 1048
1126+update t1 set a=NULL where b=13;
1127+update t1 set a=500 where b=14;
1128+select * from t1 order by b;
1129+drop table t1;
1130+
1131+#
1132+# Test of behavior of ALTER TABLE when coulmn containing NULL or zeroes is
1133+# converted to AUTO_INCREMENT column
1134+#
1135+create table t1 (a bigint, b int auto_increment primary key);
1136+insert into t1 (a) values (1), (2), (3), (NULL), (NULL);
1137+alter table t1 drop column b, modify a bigint not null auto_increment primary key;
1138+select * from t1;
1139+drop table t1;
1140+
1141+create table t1 (a bigint, b int auto_increment primary key);
1142+insert into t1 (a) values (1), (2), (3), (0), (0);
1143+--error 1022,1062
1144+alter table t1 drop column b, modify a bigint not null auto_increment primary key;
1145+select * from t1;
1146+drop table t1;
1147+
1148+# We still should be able to preserve zero in NO_AUTO_VALUE_ON_ZERO mode
1149+create table t1 (a bigint, b int auto_increment primary key);
1150+insert into t1 (a) values (0), (1), (2), (3);
1151+
1152+# Bug314567 - ALTER TABLE causes auto_increment resequencing,
1153+# resulting in duplicate entry since sql_mode=NO_AUTO_VALUE_ON_ZERO
1154+#is not supported.
1155+# NO_AUTO_VALUE_ON_ZERO is now default for Drizzle
1156+
1157+alter table t1 drop column b, modify a bigint not null auto_increment primary key;
1158+#set sql_mode= '';
1159+select * from t1;
1160+drop table t1;
1161+
1162+# It also sensible to preserve zeroes if we are converting auto_increment
1163+# column to auto_increment column (or not touching it at all, which is more
1164+# common case probably)
1165+create table t1 (a int auto_increment primary key , b int null);
1166+#sql_mode is not supported.
1167+#set sql_mode=NO_AUTO_VALUE_ON_ZERO;
1168+insert into t1 values (0,1),(1,2),(2,3);
1169+select * from t1;
1170+#set sql_mode= '';
1171+alter table t1 modify b varchar(255);
1172+--error 1022,1062
1173+insert into t1 values (0,4);
1174+select * from t1;
1175+drop table t1;
1176+
1177+#
1178+# BUG #10045: Problem with composite AUTO_INCREMENT + BLOB key
1179+
1180+CREATE TABLE t1 ( a INT AUTO_INCREMENT, b VARBINARY(1000), PRIMARY KEY (a,b(10)));
1181+INSERT INTO t1 (b) VALUES ('aaaa');
1182+CHECK TABLE t1;
1183+INSERT INTO t1 (b) VALUES ('');
1184+CHECK TABLE t1;
1185+INSERT INTO t1 (b) VALUES ('bbbb');
1186+CHECK TABLE t1;
1187+DROP TABLE IF EXISTS t1;
1188+
1189+# BUG #19025:
1190+#changed syntax of int(10) to int
1191+CREATE TABLE t1 (
1192+ t1_name VARCHAR(255) DEFAULT NULL,
1193+ t1_id INT not null AUTO_INCREMENT,
1194+ KEY (t1_name),
1195+ PRIMARY KEY (t1_id)
1196+) AUTO_INCREMENT = 1000;
1197+
1198+INSERT INTO t1 (t1_name) VALUES('MySQL');
1199+INSERT INTO t1 (t1_name) VALUES('MySQL');
1200+INSERT INTO t1 (t1_name) VALUES('MySQL');
1201+
1202+SELECT * from t1;
1203+
1204+SHOW CREATE TABLE `t1`;
1205+
1206+DROP TABLE `t1`;
1207+
1208+#
1209+# Bug #6880: LAST_INSERT_ID() within a statement
1210+#
1211+
1212+create table t1(a int not null auto_increment primary key);
1213+create table t2(a int not null auto_increment primary key, t1a int);
1214+insert into t1 values(NULL);
1215+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
1216+insert into t1 values (NULL);
1217+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
1218+(NULL, LAST_INSERT_ID());
1219+insert into t1 values (NULL);
1220+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
1221+(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
1222+select * from t2;
1223+drop table t1, t2;
1224+
1225+--echo End of 4.1 tests
1226+
1227+#
1228+# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error
1229+#
1230+#changed syntax of int (11) to int
1231+#CREATE TABLE t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`));
1232+#insert into t1 (b) values (1);
1233+#replace into t1 (b) values (2), (1), (3);
1234+#select * from t1;
1235+#truncate table t1;
1236+#insert into t1 (b) values (1);
1237+#replace into t1 (b) values (2);
1238+#replace into t1 (b) values (1);
1239+#replace into t1 (b) values (3);
1240+#select * from t1;
1241+#drop table t1;
1242+
1243+#create table t1 (rowid int not null auto_increment, val int not null,primary
1244+#key (rowid), unique(val));
1245+#replace into t1 (val) values ('1'),('2');
1246+#replace into t1 (val) values ('1'),('2');
1247+#--error ER_DUP_ENTRY
1248+#insert into t1 (val) values ('1'),('2');
1249+#select * from t1;
1250+#drop table t1;
1251+
1252+#
1253+# Test that update changes internal auto-increment value
1254+# Bug 314570
1255+
1256+#create table t1 (a int not null auto_increment primary key, val int);
1257+#insert into t1 (val) values (1);
1258+#update t1 set a=2 where a=1;
1259+#insert into t1 (val) values (1);
1260+#select * from t1;
1261+#drop table t1;
1262+
1263+#
1264+# Test key duplications with auto-increment in ALTER TABLE
1265+# bug #14573
1266+#
1267+#changed syntax of int(11) to int.
1268+CREATE TABLE t1 (t1 INT PRIMARY KEY, t2 INT);
1269+INSERT INTO t1 VALUES(0, 0);
1270+INSERT INTO t1 VALUES(1, 1);
1271+ALTER TABLE t1 CHANGE t1 t1 INT auto_increment;
1272+--error 1022, ER_DUP_ENTRY
1273+INSERT INTO t1 VALUES(0,0);
1274+DROP TABLE t1;
1275+
1276+# Test of REPLACE when it does INSERT+DELETE and not UPDATE:
1277+# see if it sets LAST_INSERT_ID() ok
1278+#create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
1279+#insert into t1 values(null,1,1,now());
1280+#insert into t1 values(null,0,0,null);
1281+# this will delete two rows
1282+#replace into t1 values(null,1,0,null);
1283+#select last_insert_id();
1284+
1285+#drop table t1;
1286
1287=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment_neg_gaps-master.opt'
1288--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment_neg_gaps-master.opt 1970-01-01 00:00:00 +0000
1289+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment_neg_gaps-master.opt 2010-05-17 13:02:28 +0000
1290@@ -0,0 +1,1 @@
1291+--plugin_add=embedded_innodb --plugin_remove=innobase
1292
1293=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment_neg_gaps.test'
1294--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment_neg_gaps.test 1970-01-01 00:00:00 +0000
1295+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/auto_increment_neg_gaps.test 2010-05-17 13:02:28 +0000
1296@@ -0,0 +1,14 @@
1297+create table t1 (a int not null auto_increment primary key);
1298+insert into t1 values (NULL);
1299+insert into t1 values (NULL);
1300+insert into t1 values (NULL);
1301+insert into t1 values (NULL);
1302+insert into t1 values (NULL);
1303+insert into t1 values (NULL);
1304+insert into t1 values (-1);
1305+select last_insert_id();
1306+delete from t1 where a in (2,3,4);
1307+insert into t1 values (NULL);
1308+--sorted_result
1309+select * from t1;
1310+drop table t1;
1311
1312=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/basic_auto_increment-master.opt'
1313--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/basic_auto_increment-master.opt 1970-01-01 00:00:00 +0000
1314+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/basic_auto_increment-master.opt 2010-05-17 13:02:28 +0000
1315@@ -0,0 +1,1 @@
1316+--plugin_add=embedded_innodb --plugin_remove=innobase
1317
1318=== added file 'plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/basic_auto_increment.test'
1319--- plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/basic_auto_increment.test 1970-01-01 00:00:00 +0000
1320+++ plugin/embedded_innodb/test-suite-dir/embedded_innodb/tests/t/basic_auto_increment.test 2010-05-17 13:02:28 +0000
1321@@ -0,0 +1,4 @@
1322+create table t1 (a int auto_increment primary key);
1323+insert into t1 values (),(),(),();
1324+select * from t1;
1325+drop table t1;