| Bug #11005 | Replace behaving like insert | ||
|---|---|---|---|
| Submitted: | 1 Jun 2005 0:02 | Modified: | 16 Jun 2005 17:06 |
| Reporter: | Matthew Hewitt | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 5.0.6 & 5.0.7bk | OS: | Linux (Redhat 9) |
| Assigned to: | Heikki Tuuri | CPU Architecture: | Any |
[1 Jun 2005 1:08]
Jorge del Conde
Thanks for your bug report. I was able to reproduce these results with 5.0.7 bk. NOTE: the create statement should actually be: create table test (rowid int not null auto_increment, val int not null,primary key (rowid), unique(val)) ENGINE=INNODB; (just in case innodb is not the default engine)
[1 Jun 2005 6:18]
Jan Lindström
Tested this with 4.1.13bk and it worked fine. This could be a bug in the auto_increment code in
5.0.x.
jan@hundin:~/mysql-4.1/client> ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.13-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table test (rowid int not null auto_increment, val int not
-> null,primary key (rowid), unique(val));
Query OK, 0 rows affected (0.10 sec)
mysql> replace into test (val) values ('1'),('2');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test;
+-------+-----+
| rowid | val |
+-------+-----+
| 1 | 1 |
| 2 | 2 |
+-------+-----+
2 rows in set (0.00 sec)
mysql> replace into test (val) values ('1'),('2');
Query OK, 4 rows affected (0.00 sec)
Records: 2 Duplicates: 2 Warnings: 0
mysql> select * from test;
+-------+-----+
| rowid | val |
+-------+-----+
| 3 | 1 |
| 4 | 2 |
+-------+-----+
2 rows in set (0.00 sec)
mysql> insert into test (val) values ('1'),('2');
ERROR 1062 (23000): Duplicate entry '1' for key 2
[1 Jun 2005 16:12]
Heikki Tuuri
Assigning this to Jan Lindström. --Heikki
[2 Jun 2005 10:14]
Jan Lindström
Bug is indeed in the auto_increment code. Replace tries first to insert a new row (ha_innobase::write_row()) with value (3,1) but that returns a duplicate key error. Thus replace tries now to update the row with value (1,1) to (3,1) and returns ok. Similarly for a second row first insert is issued with values (3,2)! but again a duplicate key error is returned. Thus again replace tries to update the row with value (2,2) to (3,2) ! This is because in ha_innodb.cc code uses dict_table_autoinc_read(prebuilt->table) function which does not increase auto_increment value of the table. Code should use dict_table_autoinc_get(prebuilt->table) to get a new auto_increment value if the auto_increment value of the table is initialized because this function will automatically increase auto_increment value of the table to next value. If the value is not initialized then dict_table_autoinc_read(prebuilt->table) and dict_table_autoinc_initialize(prebuilt->table, auto_inc) should be used.
[2 Jun 2005 11:12]
Jan Lindström
Changing category to Server because this bug is not inside of InnoDB. While executing command replace into test (val) values ('1'),('2'); second time gdb shows that MySQL calls for both updates get_auto_increment() which only initializes auto_increment value of the table to max-value+1 found from the table and that search does not see uncommitted changes. It is not clear to me why thd->next_insert_id == 0 for the second call i.e. for the second update. Remember that ha_innobase::get_auto_increment() function initializes the auto-increment counter if it has not been initialized yet. That function does not change the value of the auto-increment counter if it already has been initialized. Function returns the value of the auto-increment counter.
jan@hundin:~/mysql-5.0/sql> gdb mysqld
GNU gdb 6.0
Copyright 2003 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "i686-pc-linux-gnu"...
(gdb) break handler::update_auto_increment
Breakpoint 1 at 0x824c2f5: file handler.cc, line 1433.
(gdb) run
Starting program: /home/jan/mysql-5.0/sql/mysqld
[New Thread 16384 (LWP 24630)]
050602 13:00:17 [Warning] You have enabled the binary log, but you haven't set server-id to a non-zero value: we force server id to 1; updates will be logged to the binary log, but connections from slaves will not be accepted.
[New Thread 32769 (LWP 24632)]
[New Thread 16386 (LWP 24633)]
[New Thread 32771 (LWP 24634)]
[New Thread 49156 (LWP 24635)]
[New Thread 65541 (LWP 24636)]
[New Thread 81926 (LWP 24637)]
[New Thread 98311 (LWP 24638)]
[New Thread 114696 (LWP 24639)]
[New Thread 131081 (LWP 24640)]
050602 13:00:19 InnoDB: Started; log sequence number 0 49234
050602 13:00:19 [Note] Recovering after a crash using binlog
050602 13:00:19 [Note] Starting crash recovery...
050602 13:00:19 [Note] Crash recovery finished.
[New Thread 147466 (LWP 24641)]
050602 13:00:19 [Note] /home/jan/mysql-5.0/sql/mysqld: ready for connections.
Version: '5.0.7-beta-debug-log' socket: '/home/jan/bugsocket' port: 3306 Source distribution
[New Thread 163851 (LWP 24661)]
[Switching to Thread 163851 (LWP 24661)]
Breakpoint 1, handler::update_auto_increment() (this=0x8c3b480) at handler.cc:1433
1433 THD *thd= table->in_use;
(gdb) next
1434 struct system_variables *variables= &thd->variables;
(gdb)
1436 bool result= 0;
(gdb)
1437 DBUG_ENTER("handler::update_auto_increment");
(gdb)
1443 thd->prev_insert_id= thd->next_insert_id;
(gdb)
1444 auto_increment_field_not_null= table->auto_increment_field_not_null;
(gdb)
1445 table->auto_increment_field_not_null= FALSE;
(gdb)
1447 if ((nr= table->next_number_field->val_int()) != 0 ||
(gdb) step
Field_long::val_int() (this=0x8c3b610) at field.cc:3425
3425 DBUG_ASSERT(table->in_use == current_thd);
(gdb) next
3431 longget(j,ptr);
(gdb)
3432 return unsigned_flag ? (longlong) (uint32) j : (longlong) j;
(gdb)
3433 }
(gdb)
handler::update_auto_increment() (this=0x8c3b480) at handler.cc:1467
1467 if (!(nr= thd->next_insert_id))
(gdb) p nr
$1 = 0
(gdb) next
1469 if ((nr= get_auto_increment()) == ~(ulonglong) 0)
(gdb) step
ha_innobase::get_auto_increment() (this=0x8c3b480) at ha_innodb.cc:6628
6628 error = innobase_read_and_init_auto_inc(&nr);
(gdb) next
6630 if (error) {
(gdb) p error
$2 = 0
(gdb) next
6642 return((ulonglong) nr);
(gdb) p nr
$3 = 3
(gdb) next
6643 }
(gdb)
handler::update_auto_increment() (this=0x8c3b480) at handler.cc:1472
1472 if (variables->auto_increment_increment != 1)
(gdb)
1479 thd->next_insert_id= nr;
(gdb)
1482 DBUG_PRINT("info",("auto_increment: %lu", (ulong) nr));
(gdb)
1485 thd->clear_next_insert_id= 1;
(gdb)
1487 if (!table->next_number_field->store((longlong) nr))
(gdb)
1488 thd->insert_id((ulonglong) nr);
(gdb)
1497 if (!table->s->next_number_key_offset)
(gdb)
1504 thd->next_insert_id= next_insert_id(nr, variables);
(gdb)
1510 auto_increment_column_changed=1;
(gdb) p thd->next_insert_id
$4 = 4
(gdb) next
1511 DBUG_RETURN(result);
(gdb)
1512 }
(gdb)
ha_innobase::write_row(char*) (this=0x8c3b480, record=0x8c3b5b0 "\003")
at ha_innodb.cc:3194
3194 auto_inc_used = 1;
(gdb) c
Continuing.
Breakpoint 1, handler::update_auto_increment() (this=0x8c3b480) at handler.cc:1433
1433 THD *thd= table->in_use;
(gdb) next
1434 struct system_variables *variables= &thd->variables;
(gdb)
1436 bool result= 0;
(gdb)
1437 DBUG_ENTER("handler::update_auto_increment");
(gdb)
1443 thd->prev_insert_id= thd->next_insert_id;
(gdb)
1444 auto_increment_field_not_null= table->auto_increment_field_not_null;
(gdb) p thd->prev_insert_id
$5 = 0
(gdb) next
1445 table->auto_increment_field_not_null= FALSE;
(gdb)
1447 if ((nr= table->next_number_field->val_int()) != 0 ||
(gdb)
1467 if (!(nr= thd->next_insert_id))
(gdb)
1469 if ((nr= get_auto_increment()) == ~(ulonglong) 0)
(gdb) p nr
$6 = 0
(gdb) step
ha_innobase::get_auto_increment() (this=0x8c3b480) at ha_innodb.cc:6628
6628 error = innobase_read_and_init_auto_inc(&nr);
(gdb) next
6630 if (error) {
(gdb)
6642 return((ulonglong) nr);
(gdb) p nr
$7 = 3
(gdb)
[2 Jun 2005 13:12]
Heikki Tuuri
Hi!
ha_innodb.cc in 5.0.6 relies on ::write_row() to update the InnoDB internal auto-inc counter to a higher value when the row is actually inserted to the table. But it does not work if the REPLACE is internally converted to an update! A possible fix is that I let also an UPDATE to increment the value. This would be an incompatible change from 4.1, where an UPDATE does not affect the counter.
But I have to study this bug more.
Regards,
Heikki
::write_row():
"
error = row_insert_for_mysql((byte*) record, prebuilt);
if (error == DB_SUCCESS && auto_inc_used) {
/* Fetch the value that was set in the autoincrement field */
auto_inc = table->next_number_field->val_int();
if (auto_inc != 0) {
/* This call will update the counter according to the
value that was inserted in the table */
dict_table_autoinc_update(prebuilt->table, auto_inc);
}
}
"
[3 Jun 2005 12:15]
Heikki Tuuri
Hi! I would rather keep a REPLACE internally as an update whenever possible, since an update is faster. I think a solution where every UPDATE of an auto-inc column also updates the InnoDB internal auto-inc counter to max(current counter value, updated value) makes a lot of sense. Some user complained about a year ago that it is illogical to update the value in and INSERT but not in an UPDATE. Regards, Heikki
[3 Jun 2005 17:16]
Heikki Tuuri
MyISAM seems to update its own auto-inc counter to max(old counter value, new updated auto-inc column value) also at an UPDATE. Thus, it makes a lot of sense to change the InnoDB behavior to the same. --Heikki
[6 Jun 2005 10:55]
Michael Widenius
Yes, InnoDB should do as MyISAM and update the internal auto-inc value during an update to max(current counter value, updated value). This is what MyISAM has always done. This would also fix the following bug we had a long time (and only in InnoDB): create table t1 (a int not null auto_increment primary key, val int) engine=innodb; insert into t1 (val) values (1); update t1 set a=2 where a=1; insert into t1 (val) values (1); ERROR 1062 (23000): Duplicate entry '2' for key 1 It's ok that the behaviour changes in 5.0 as the above is a long outstanding bug that have made InnoDB incompatible with MyISAM tables.
[7 Jun 2005 9:46]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/25681
[7 Jun 2005 9:49]
Heikki Tuuri
Hi! I decided not to change the behavior in an UPDATE. That would have required a bigger patch. The committed patch simply assumes that even if a REPLACE command produces a duplicate key error in ::write_row(), that error will be handled by REPLACE, and eventually the REPLACE succeeds. Thus we can update the internal InnoDB auto-inc counter already in ::write_row(). Regards, Heikki
[7 Jun 2005 10:22]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/25687
[7 Jun 2005 10:24]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/25688
[7 Jun 2005 10:27]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/25689
[7 Jun 2005 10:36]
Heikki Tuuri
Hi! The fix will probably be in 5.0.6, because the patch was just pushed to the 5.0 tree. I did NOT change the InnoDB behavior in an UPDATE. Just fixed the behavior in a REPLACE. Regards, Heikki
[7 Jun 2005 10:54]
Heikki Tuuri
Of course I mean that the fix will probaboly be in 5.0.7. --Heikki
[16 Jun 2005 17:06]
Heikki Tuuri
Fixed in 5.0.7.
Description: When I issue a replace to 5.0.6 Innodb it behaves as though I issued an insert. How to repeat: create table test (rowid int not null auto_increment, val int not null,primary key (rowid), unique(val)); Query OK, 0 rows affected (0.23 sec) replace into test (val) values ('1'),('2'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 replace into test (val) values ('1'),('2'); ERROR 1062 (23000): Duplicate entry '3' for key 1 insert into test (val) values ('1'),('2'); ERROR 1062 (23000): Duplicate entry '1' for key 2 alter table test type='myisam'; Query OK, 2 rows affected, 1 warning (0.08 sec) Records: 2 Duplicates: 0 Warnings: 0 replace into test (val) values ('1'),('2'); Query OK, 4 rows affected (0.00 sec) Records: 2 Duplicates: 2 Warnings: 0 insert into test (val) values ('1'),('2'); ERROR 1062 (23000): Duplicate entry '1' for key 2 Suggested fix: My guess is that it has something to do with the auto_increment function.