fixed bug 663 and WL 1052 (sql_mode is safe for mysqldump)

10 files changed, 71 insertions, 3 deletions

diff --git a/client/mysqldump.c b/client/mysqldump.c
index 82737836485..4eb6d74cdf1 100644
--- a/client/mysqldump.c
+++ b/client/mysqldump.c
@@ -352,6 +352,11 @@ static void write_header(FILE *sql_file, char *db_name)
 	    mysql_get_server_info(&mysql_connection));
     if (!opt_set_names)
       fprintf(sql_file,"\n/*!40101 SET NAMES %s*/;\n",default_charset);
+    fprintf(md_result_file,"\
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\n\
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n\
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;\n\
+");
   }
   return;
 } /* write_header */
@@ -361,6 +366,14 @@ static void write_footer(FILE *sql_file)
 {
   if (opt_xml)
     fprintf(sql_file,"</mysqldump>");
+  else
+  {
+    fprintf(md_result_file,"\n
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;\n\
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\n\
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;\n\
+");
+  }
   fputs("\n", sql_file);
 } /* write_footer */
 
diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result
index 19f8ffa84d4..99e5e596fd3 100644
--- a/mysql-test/r/auto_increment.result
+++ b/mysql-test/r/auto_increment.result
@@ -150,3 +150,28 @@ select last_insert_id();
 last_insert_id()
 0
 drop table t1;
+drop table if exists t1;
+Warnings:
+Note	1051	Unknown table 't1'
+create table t1(a int auto_increment,b int null,primary key(a));
+SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
+insert into t1(a,b)values(NULL,1);
+insert into t1(a,b)values(200,2);
+insert into t1(a,b)values(0,3);
+insert into t1(b)values(4);
+SET SQL_MODE='';
+insert into t1(a,b)values(NULL,5);
+insert into t1(a,b)values(300,6);
+insert into t1(a,b)values(0,7);
+insert into t1(b)values(8);
+select * from t1;
+a	b
+1	1
+200	2
+0	3
+201	4
+202	5
+300	6
+301	7
+302	8
+drop table t1;
diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test
index 189320a8dcb..e187d42625e 100644
--- a/mysql-test/t/auto_increment.test
+++ b/mysql-test/t/auto_increment.test
@@ -106,3 +106,17 @@ select last_insert_id();
 
 drop table t1;
 
+drop table if exists t1;
+create table t1(a int auto_increment,b int null,primary key(a));
+SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
+insert into t1(a,b)values(NULL,1);
+insert into t1(a,b)values(200,2);
+insert into t1(a,b)values(0,3);
+insert into t1(b)values(4);
+SET SQL_MODE='';
+insert into t1(a,b)values(NULL,5);
+insert into t1(a,b)values(300,6);
+insert into t1(a,b)values(0,7);
+insert into t1(b)values(8);
+select * from t1;
+drop table t1;
diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index ec2488f520a..6e4d96ad5af 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -172,7 +172,10 @@ set_field_to_null_with_conversions(Field *field, bool no_conversions)
   }
   field->reset();
   if (field == field->table->next_number_field)
+  {
+    field->table->auto_increment_field_is_null= true;
     return 0;					// field is set in handler.cc
+  }
   if (current_thd->count_cuted_fields)
   {
     field->set_warning(MYSQL_ERROR::WARN_LEVEL_WARN,ER_WARN_NULL_TO_NOTNULL);
diff --git a/sql/handler.cc b/sql/handler.cc
index 150a0d5329e..7f4b63e7551 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -697,7 +697,10 @@ void handler::update_auto_increment()
   longlong nr;
   THD *thd;
   DBUG_ENTER("update_auto_increment");
-  if (table->next_number_field->val_int() != 0)
+  if (table->auto_increment_field_is_null)
+    table->auto_increment_field_is_null= false;
+  else if (table->next_number_field->val_int() != 0 ||
+	   current_thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO)
   {
     auto_increment_column_changed=0;
     DBUG_VOID_RETURN;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 5ad2cc56b8c..205617a910e 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -221,6 +221,7 @@ extern CHARSET_INFO *national_charset_info, *table_alias_charset;
 #define MODE_MYSQL323                  32768
 #define MODE_MYSQL40                   65536
 #define MODE_ANSI	               (MODE_MYSQL40*2)
+#define MODE_NO_AUTO_VALUE_ON_ZERO     (MODE_ANSI*2)
 
 #define RAID_BLOCK_SIZE 1024
 
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 0a3985c3ee9..78c02df0daf 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -212,7 +212,7 @@ const char *sql_mode_names[] =
   "?", "ONLY_FULL_GROUP_BY", "NO_UNSIGNED_SUBTRACTION",
   "POSTGRESQL", "ORACLE", "MSSQL", "DB2", "SAPDB", "NO_KEY_OPTIONS",
   "NO_TABLE_OPTIONS", "NO_FIELD_OPTIONS", "MYSQL323", "MYSQL40", "ANSI",
-  NullS
+  "NO_AUTO_VALUE_ON_ZERO", NullS
 };
 TYPELIB sql_mode_typelib= { array_elements(sql_mode_names)-1,"",
 			    sql_mode_names };
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 641ab46e5e0..a7798588d59 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2245,7 +2245,11 @@ fill_record(List<Item> &fields,List<Item> &values, bool ignore_errors)
   while ((field=(Item_field*) f++))
   {
     value=v++;
-    if (value->save_in_field(field->field, 0) > 0 && !ignore_errors)
+    Field *rfield= field->field;
+    TABLE *table= rfield->table;
+    if (rfield==table->next_number_field)
+      table->auto_increment_field_is_null= false;
+    if (value->save_in_field(rfield, 0) > 0 && !ignore_errors)
       DBUG_RETURN(1);
   }
   DBUG_RETURN(0);
@@ -2263,6 +2267,9 @@ fill_record(Field **ptr,List<Item> &values, bool ignore_errors)
   while ((field = *ptr++))
   {
     value=v++;
+    TABLE *table= field->table;
+    if (field==table->next_number_field)
+      table->auto_increment_field_is_null= false;
     if (value->save_in_field(field, 0) == 1 && !ignore_errors)
       DBUG_RETURN(1);
   }
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 091de9d406f..10f1a2587b4 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -253,6 +253,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list,
   else
     bulk_insert=0;
 
+  table->auto_increment_field_is_null= true;
   while ((values= its++))
   {
     if (fields.elements || !value_count)
diff --git a/sql/table.h b/sql/table.h
index 3132e72fb2f..8137f47aa1f 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -116,6 +116,7 @@ struct st_table {
   my_bool crashed;
   my_bool is_view;
   my_bool no_keyread;
+  my_bool auto_increment_field_is_null;     
   Field *next_number_field,		/* Set if next_number is activated */
 	*found_next_number_field,	/* Set on open */
         *rowid_field;