summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/type_timestamp.result47
-rw-r--r--mysql-test/t/type_timestamp.test21
-rw-r--r--sql/mysql_priv.h2
-rw-r--r--sql/sql_insert.cc94
-rw-r--r--sql/table.h4
5 files changed, 154 insertions, 14 deletions
diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index 6c46d308e7e..c0396e4640d 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -432,3 +432,50 @@ t1 CREATE TABLE "t1" (
)
set sql_mode='';
drop table t1;
+create table t1 (a int auto_increment primary key, b int, c timestamp);
+insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
+(2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
+select * from t1;
+a b c
+1 0 2001-01-01 01:01:01
+2 0 2002-02-02 02:02:02
+3 0 2003-03-03 03:03:03
+update t1 set b = 2, c = c where a = 2;
+select * from t1;
+a b c
+1 0 2001-01-01 01:01:01
+2 2 2002-02-02 02:02:02
+3 0 2003-03-03 03:03:03
+insert into t1 (a) values (4);
+select * from t1;
+a b c
+1 0 2001-01-01 01:01:01
+2 2 2002-02-02 02:02:02
+3 0 2003-03-03 03:03:03
+4 NULL 2001-09-09 04:46:59
+update t1 set c = '2004-04-04 04:04:04' where a = 4;
+select * from t1;
+a b c
+1 0 2001-01-01 01:01:01
+2 2 2002-02-02 02:02:02
+3 0 2003-03-03 03:03:03
+4 NULL 2004-04-04 04:04:04
+insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
+select * from t1;
+a b c
+1 0 2001-01-01 01:01:01
+2 2 2002-02-02 02:02:02
+3 3 2003-03-03 03:03:03
+4 NULL 2004-04-04 04:04:04
+5 NULL 2001-09-09 04:46:59
+insert into t1 (a, c) values (4, '2004-04-04 00:00:00'),
+(6, '2006-06-06 06:06:06') on duplicate key update b = 4;
+select * from t1;
+a b c
+1 0 2001-01-01 01:01:01
+2 2 2002-02-02 02:02:02
+3 3 2003-03-03 03:03:03
+4 4 2001-09-09 04:46:59
+5 NULL 2001-09-09 04:46:59
+6 NULL 2006-06-06 06:06:06
+drop table t1;
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index 783e310f02d..3c7f07dfbce 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -298,3 +298,24 @@ show create table t1;
# restore default mode
set sql_mode='';
drop table t1;
+
+#
+# Bug#7806 - insert on duplicate key and auto-update of timestamp
+#
+create table t1 (a int auto_increment primary key, b int, c timestamp);
+insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
+ (2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
+select * from t1;
+update t1 set b = 2, c = c where a = 2;
+select * from t1;
+insert into t1 (a) values (4);
+select * from t1;
+update t1 set c = '2004-04-04 04:04:04' where a = 4;
+select * from t1;
+insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
+select * from t1;
+insert into t1 (a, c) values (4, '2004-04-04 00:00:00'),
+ (6, '2006-06-06 06:06:06') on duplicate key update b = 4;
+select * from t1;
+drop table t1;
+
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 2b13b2053d7..e75a6f25957 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -668,8 +668,6 @@ void mysql_sql_stmt_execute(THD *thd, LEX_STRING *stmt_name);
void mysql_stmt_free(THD *thd, char *packet);
void mysql_stmt_reset(THD *thd, char *packet);
void mysql_stmt_get_longdata(THD *thd, char *pos, ulong packet_length);
-int check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
- List<Item> &values, ulong counter);
/* sql_error.cc */
MYSQL_ERROR *push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, uint code,
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 1f190a450de..bb115b9d548 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -42,15 +42,29 @@ static void unlink_blobs(register TABLE *table);
#define DELAYED_LOG_UPDATE 1
#define DELAYED_LOG_BIN 2
+
/*
Check if insert fields are correct.
- Sets table->timestamp_field_type to TIMESTAMP_NO_AUTO_SET or leaves it
- as is, depending on if timestamp should be updated or not.
+
+ SYNOPSIS
+ check_insert_fields()
+ thd The current thread.
+ table The table for insert.
+ fields The insert fields.
+ values The insert values.
+
+ NOTE
+ Clears TIMESTAMP_AUTO_SET_ON_INSERT from table->timestamp_field_type
+ or leaves it as is, depending on if timestamp should be updated or
+ not.
+
+ RETURN
+ 0 OK
+ -1 Error
*/
-int
-check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
- List<Item> &values, ulong counter)
+static int check_insert_fields(THD *thd, TABLE *table, List<Item> &fields,
+ List<Item> &values)
{
if (fields.elements == 0 && values.elements != 0)
{
@@ -58,7 +72,7 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
{
my_printf_error(ER_WRONG_VALUE_COUNT_ON_ROW,
ER(ER_WRONG_VALUE_COUNT_ON_ROW),
- MYF(0),counter);
+ MYF(0), 1);
return -1;
}
#ifndef NO_EMBEDDED_ACCESS_CHECKS
@@ -66,7 +80,7 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
check_grant_all_columns(thd,INSERT_ACL,table))
return -1;
#endif
- table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
+ (int) table->timestamp_field_type&= ~ (int) TIMESTAMP_AUTO_SET_ON_INSERT;
}
else
{ // Part field list
@@ -74,7 +88,7 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
{
my_printf_error(ER_WRONG_VALUE_COUNT_ON_ROW,
ER(ER_WRONG_VALUE_COUNT_ON_ROW),
- MYF(0),counter);
+ MYF(0), 1);
return -1;
}
TABLE_LIST table_list;
@@ -96,7 +110,7 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
}
if (table->timestamp_field && // Don't set timestamp if used
table->timestamp_field->query_id == thd->query_id)
- table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
+ (int) table->timestamp_field_type&= ~ (int) TIMESTAMP_AUTO_SET_ON_INSERT;
}
// For the values we need select_priv
#ifndef NO_EMBEDDED_ACCESS_CHECKS
@@ -106,6 +120,62 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
}
+/*
+ Check update fields for the timestamp field.
+
+ SYNOPSIS
+ check_update_fields()
+ thd The current thread.
+ insert_table_list The insert table list.
+ table The table for update.
+ update_fields The update fields.
+
+ NOTE
+ If the update fields include the timestamp field,
+ remove TIMESTAMP_AUTO_SET_ON_UPDATE from table->timestamp_field_type.
+
+ RETURN
+ 0 OK
+ -1 Error
+*/
+
+static int check_update_fields(THD *thd, TABLE *table,
+ TABLE_LIST *insert_table_list,
+ List<Item> &update_fields)
+{
+ ulong timestamp_query_id;
+ LINT_INIT(timestamp_query_id);
+
+ /*
+ Change the query_id for the timestamp column so that we can
+ check if this is modified directly.
+ */
+ if (table->timestamp_field)
+ {
+ timestamp_query_id= table->timestamp_field->query_id;
+ table->timestamp_field->query_id= thd->query_id-1;
+ }
+
+ /*
+ Check the fields we are going to modify. This will set the query_id
+ of all used fields to the threads query_id.
+ */
+ if (setup_fields(thd, 0, insert_table_list, update_fields, 1, 0, 0))
+ return -1;
+
+ if (table->timestamp_field)
+ {
+ /* Don't set timestamp column if this is modified. */
+ if (table->timestamp_field->query_id == thd->query_id)
+ (int) table->timestamp_field_type&= ~ (int) TIMESTAMP_AUTO_SET_ON_UPDATE;
+ else
+ table->timestamp_field->query_id= timestamp_query_id;
+ }
+
+ return 0;
+}
+
+
int mysql_insert(THD *thd,TABLE_LIST *table_list,
List<Item> &fields,
List<List_item> &values_list,
@@ -450,11 +520,11 @@ int mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
if (!table->insert_values)
DBUG_RETURN(-1);
}
- if ((values && check_insert_fields(thd, table, fields, *values, 1)) ||
+ if ((values && check_insert_fields(thd, table, fields, *values)) ||
setup_tables(insert_table_list) ||
(values && setup_fields(thd, 0, insert_table_list, *values, 0, 0, 0)) ||
(duplic == DUP_UPDATE &&
- (setup_fields(thd, 0, insert_table_list, update_fields, 1, 0, 0) ||
+ (check_update_fields(thd, table, insert_table_list, update_fields) ||
setup_fields(thd, 0, insert_table_list, update_values, 1, 0, 0))))
DBUG_RETURN(-1);
if (values && find_real_table_in_list(table_list->next, table_list->db,
@@ -1457,7 +1527,7 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
DBUG_ENTER("select_insert::prepare");
unit= u;
- if (check_insert_fields(thd,table,*fields,values,1))
+ if (check_insert_fields(thd, table, *fields, values))
DBUG_RETURN(1);
restore_record(table,default_values); // Get empty record
diff --git a/sql/table.h b/sql/table.h
index 054f24267b7..e822d68531e 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -60,6 +60,10 @@ typedef struct st_filesort_info
/*
Values in this enum are used to indicate during which operations value
of TIMESTAMP field should be set to current timestamp.
+ WARNING: The values are used for bit operations. If you change the enum,
+ you must keep the bitwise relation of the values. For example:
+ (int) TIMESTAMP_AUTO_SET_ON_BOTH ==
+ (int) TIMESTAMP_AUTO_SET_ON_INSERT | (int) TIMESTAMP_AUTO_SET_ON_UPDATE.
*/
enum timestamp_auto_set_type
{
View Lorry Controller Status