Below is the list of changes that have just been committed into a local
5.0 repository of antony. When antony does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-06-22 10:04:09-07:00, antony@stripped +4 -0
Bug#25511
"Federated INSERT failures"
Federated does not correctly handle "INSERT ... ON DUPLICATE KEY UPDATE"
If it is in effect, we must permit mysqld to retry the insert
operation. We check if the local primary key definition is adequate
to identify a specific row on the remote server by its primary key.
This patch builds on Bug29019
mysql-test/r/federated.result@stripped, 2007-06-22 10:04:01-07:00, antony@stripped +16 -0
test for bug 25511
mysql-test/t/federated.test@stripped, 2007-06-22 10:04:01-07:00, antony@stripped +31 -0
test for bug 25511
sql/ha_federated.cc@stripped, 2007-06-22 10:04:01-07:00, antony@stripped +79 -11
bug25511
mysqld will request a lock_type of TL_WRITE when an "ON DUPLICATE
KEY UPDATE" is specified for inserting rows. We should not apply
IGNORE to the remote insert operation and should mysqld request the
key which failed, we must check if the remote server is using a
compatible primary key before replying with the local primary key.
sql/ha_federated.h@stripped, 2007-06-22 10:04:01-07:00, antony@stripped +2 -0
bug25511
new member variables:
maybe_dup_update, remote_primary_key_checked
new method:
perform_primary_key_check()
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: antony
# Host: ppcg5.local
# Root: /private/Network/Servers/anubis.xiphis.org/home/antony/work/p2-bug25511.4
--- 1.41/mysql-test/r/federated.result 2007-06-21 11:20:21 -07:00
+++ 1.42/mysql-test/r/federated.result 2007-06-22 10:04:01 -07:00
@@ -1867,6 +1867,22 @@
3 Curly
drop table federated.t1;
drop table federated.t1;
+create table federated.t1 (a int primary key, b int, c varchar(64));
+insert into federated.t1 values (1,2,"original");
+create table federated.t1 (a int primary key, b int, c varchar(64))
+ENGINE=FEDERATED
+connection='mysql://root@stripped:SLAVE_PORT/federated/t1';
+insert into federated.t1 values(1,3,"new") on duplicate key update b=b+100;
+select * from federated.t1;
+a b c
+1 102 original
+drop table federated.t1;
+create table federated.t1 (a int , b int primary key, c varchar(64));
+insert into federated.t1 values (1,2,"original");
+insert into federated.t1 values(1,2,"new") on duplicate key update b=b+100;
+ERROR 23000: Can't write; duplicate key in table 't1'
+drop table federated.t1;
+drop table federated.t1;
DROP TABLE IF EXISTS federated.t1;
DROP DATABASE IF EXISTS federated;
DROP TABLE IF EXISTS federated.t1;
--- 1.36/mysql-test/t/federated.test 2007-06-21 11:20:21 -07:00
+++ 1.37/mysql-test/t/federated.test 2007-06-22 10:04:01 -07:00
@@ -1603,4 +1603,35 @@
connection slave;
drop table federated.t1;
+
+#
+# Bug#25511 Federated INSERT failures
+#
+connection slave;
+create table federated.t1 (a int primary key, b int, c varchar(64));
+insert into federated.t1 values (1,2,"original");
+connection master;
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval create table federated.t1 (a int primary key, b int, c varchar(64))
+ ENGINE=FEDERATED
+ connection='mysql://root@stripped:$SLAVE_MYPORT/federated/t1';
+
+insert into federated.t1 values(1,3,"new") on duplicate key update b=b+100;
+select * from federated.t1;
+
+connection slave;
+drop table federated.t1;
+# if the primary key is not compatible, it doesn't work
+create table federated.t1 (a int , b int primary key, c varchar(64));
+insert into federated.t1 values (1,2,"original");
+connection master;
+
+--error ER_DUP_KEY
+insert into federated.t1 values(1,2,"new") on duplicate key update b=b+100;
+
+drop table federated.t1;
+connection slave;
+drop table federated.t1;
+
+
source include/federated_cleanup.inc;
--- 1.76/sql/ha_federated.cc 2007-06-21 11:20:22 -07:00
+++ 1.77/sql/ha_federated.cc 2007-06-22 10:04:01 -07:00
@@ -1614,7 +1614,7 @@
*/
if (replace_duplicates)
insert_string.append(STRING_WITH_LEN("REPLACE INTO "));
- else if (ignore_duplicates)
+ else if (ignore_duplicates && !maybe_dup_update)
insert_string.append(STRING_WITH_LEN("INSERT IGNORE INTO "));
else
insert_string.append(STRING_WITH_LEN("INSERT INTO "));
@@ -1820,7 +1820,7 @@
update_string.length(0);
where_string.length(0);
- if (ignore_duplicates)
+ if (ignore_duplicates && !maybe_dup_update)
update_string.append(STRING_WITH_LEN("UPDATE IGNORE "));
else
update_string.append(STRING_WITH_LEN("UPDATE "));
@@ -2428,7 +2428,6 @@
{
char error_buffer[FEDERATED_QUERY_BUFFER_SIZE];
char status_buf[FEDERATED_QUERY_BUFFER_SIZE];
- char escaped_table_name[FEDERATED_QUERY_BUFFER_SIZE];
int error;
uint error_code;
MYSQL_RES *result= 0;
@@ -2442,14 +2441,7 @@
{
status_query_string.length(0);
status_query_string.append(FEDERATED_INFO);
- status_query_string.append(FEDERATED_SQUOTE);
-
- escape_string_for_mysql(&my_charset_bin, (char *)escaped_table_name,
- sizeof(escaped_table_name),
- share->table_name,
- share->table_name_length);
- status_query_string.append(escaped_table_name);
- status_query_string.append(FEDERATED_SQUOTE);
+ append_ident(&status_query_string, share->table_name, share->table_name_length, '\'');
if (mysql_real_query(mysql, status_query_string.ptr(),
status_query_string.length()))
@@ -2498,6 +2490,9 @@
*/
block_size= 4096;
}
+
+ if (flag & HA_STATUS_ERRKEY && perform_primary_key_check())
+ errkey= table->s->primary_key;
if (result)
mysql_free_result(result);
@@ -2538,6 +2533,8 @@
case HA_EXTRA_RESET:
ignore_duplicates= FALSE;
replace_duplicates= FALSE;
+ remote_primary_key_checked= FALSE;
+ maybe_dup_update= FALSE;
break;
default:
/* do nothing */
@@ -2547,6 +2544,68 @@
}
+/**
+ @brief Check the remote table's Primary Key declarations for compatibility.
+ @return
+ @retval FALSE Not compatible
+ @retval TRUE Compatible
+ @note The remote table is compatible if all columns used for its primary
+ key are used in the local table's primary key declaration.
+*/
+
+bool ha_federated::perform_primary_key_check()
+{
+ char query_buf[FEDERATED_QUERY_BUFFER_SIZE];
+ MYSQL_RES *result= 0;
+ MYSQL_ROW row;
+ String query_string(query_buf, sizeof(query_buf), &my_charset_bin);
+ DBUG_ENTER("ha_federated::perform_primary_key_check");
+ if (remote_primary_key_checked || !maybe_dup_update ||
+ table->s->primary_key == MAX_KEY)
+ DBUG_RETURN(remote_primary_key_checked);
+
+ query_string.length(0);
+ query_string.append(STRING_WITH_LEN("SHOW COLUMNS FROM "));
+ append_ident(&query_string, share->table_name, share->table_name_length);
+
+ if (mysql_real_query(mysql, query_string.ptr(), query_string.length()))
+ goto error;
+ if (!(result= mysql_store_result(mysql)))
+ goto error;
+
+ /*
+ we check that all columns which compose a primary key on the
+ remote server are at least part of our primary key.
+ */
+
+ while ((row= mysql_fetch_row(result)))
+ {
+ Field **field;
+
+ if (strcmp(row[3], "PRI"))
+ continue;
+
+ for (field= table->field; *field; field++)
+ if (!strcmp(row[0], (*field)->field_name))
+ break;
+
+ if (!*field || !((*field)->flags & PRI_KEY_FLAG))
+ goto error;
+ }
+
+ remote_primary_key_checked= TRUE;
+ mysql_free_result(result);
+ DBUG_RETURN(TRUE);
+
+error:
+ mysql_free_result(result);
+ push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ HA_FEDERATED_ERROR_WITH_REMOTE_SYSTEM,
+ "PRIMARY KEY declaration mismatch");
+ DBUG_RETURN(FALSE);
+}
+
+
/*
Used to delete all rows in a table. Both for cases of truncate and
for cases where the optimizer realizes that all rows will be
@@ -2622,6 +2681,12 @@
if (lock_type != TL_IGNORE && lock.type == TL_UNLOCK)
{
/*
+ When MySQL is preparing to do a INSERT...ON DUPLICATE KEY,
+ it will request a less concurrent lock_type of TL_WRITE.
+ */
+ maybe_dup_update= (lock_type == TL_WRITE);
+
+ /*
Here is where we get into the guts of a row level lock.
If TL_UNLOCK is set
If we are not doing a LOCK TABLE or DISCARD/IMPORT
@@ -2677,6 +2742,9 @@
DBUG_ENTER("ha_federated::stash_remote_error()");
remote_error_number= mysql_errno(mysql);
strmake(remote_error_buf, mysql_error(mysql), sizeof(remote_error_buf)-1);
+ if (remote_error_number == ER_DUP_ENTRY ||
+ remote_error_number == ER_DUP_KEY)
+ DBUG_RETURN(HA_ERR_FOUND_DUPP_KEY);
DBUG_RETURN(HA_FEDERATED_ERROR_WITH_REMOTE_SYSTEM);
}
--- 1.31/sql/ha_federated.h 2007-06-21 11:20:22 -07:00
+++ 1.32/sql/ha_federated.h 2007-06-22 10:04:01 -07:00
@@ -153,6 +153,7 @@
MYSQL_ROW_OFFSET current_position; // Current position used by ::position()
int remote_error_number;
char remote_error_buf[FEDERATED_QUERY_BUFFER_SIZE];
+ bool maybe_dup_update, remote_primary_key_checked;
bool ignore_duplicates, replace_duplicates;
private:
@@ -167,6 +168,7 @@
const key_range *end_key,
bool records_in_range);
int stash_remote_error();
+ bool perform_primary_key_check();
public:
ha_federated(TABLE *table_arg);
