List:Commits« Previous MessageNext Message »
From:Praveenkumar Hulakund Date:March 19 2012 6:02pm
Subject:bzr push into mysql-trunk branch (praveenkumar.hulakund:3799 to 3800)
Bug#11747847
View as plain text  
 3800 Praveenkumar Hulakund	2012-03-19
      BUG#11747847 - 34280: Create table fails if in NO_ZERO_DATE SQL mode.
      
      Analysis:
      -----------------
      The behavior of SQL Modes "NO_ZERO_DATE" and "NO_ZERO_IN_DATE" is as below,
      
          NO_ZERO_DATE:
          ------------
              Strict Mode: Do not permit '0000-00-00' or 0 as valid date
      
              Non-strict Mode: Date '0000-00-00' or 0 is accepted but warning is 
                               generated
          NO_ZERO_IN_DATE:
          ----------------
              Strict Mode: Do not accept the dates where year is non-zero and month
                           or day is 0 (e.g. 2012-00-00)
      
              Non-strict Mode: Date having non-zero year and month or day 0 is 
                               accepted but warning is generated.
      
      
          Strict mode: Setting SQL modes STRICT_ALL_TABLES, STRICT_TRANS_TABLES will
                       enable the strict mode.
      
      CREATE TABLE and ALTER TABLE fails in non-strict mode when NO_ZERO_DATE (NO_ZERO_IN_DATE)
      is set and the default value is zero (contains zero month or day).
      
      INSERT and UPDATE statements work properly.
      
      Fix:
      -----------------
      While inserting/updating, flag "abort_on_warning" of THD is set, if strict SQL
      MODE is set. And if NO_ZERO_DATE or NO_ZERO_IN_DATE mode is also set then for
      zero date or zero in (month/day) of date, error will be generated. But for 
      non-strict mode only warning message is generated as "abort_on_warning" is set
      to "false".
      
      "abort_on_warning" flag of THD decides whether to generate warning or error
      for invalid value in NO_ZERO_DATE or NO_ZERO_IN_DATE SQL modes. 
      
      But "abort_on_warning" was not set for the CREATE TABLE and ALTER TABLE statements.
      
      The fix is to set "abort_on_warning" also for these statements.
     @ include/my_time.h
        To handle errors or warnings when NO_ZERO_IN_DATE is set new name constant
        "MYSQL_TIME_WARN_ZERO_IN_DATE" is added.
     @ mysql-test/r/create.result
        As we are not reporting error when mode is NO_ZERO_DATE in non-strict mode,
        we wont get errors for CREATE and ALTER in these cases of this test.
     @ mysql-test/r/partition_innodb.result
        As we are not reporting error when mode is NO_ZERO_DATE in non-strict mode,
        we wont get error for test.t1 in this test case.
     @ mysql-test/t/create.test
        As we are not reporting error when mode is NO_ZERO_DATE in non-strict mode,
        we wont get errors for CREATE and ALTER in these cases of this test.
     @ sql-common/my_time.c
        We use to return error "MYSQL_TIME_WARN_OUT_OF_RANG", when value is out of 
        range and when 0 is used in day or month of date when NO_ZERO_IN_DATE is set.
        To handle errors or warning for NO_ZERO_IN_DATE, setting warning with new
        name constant "MYSQL_TIME_WARN_ZERO_IN_DATE" added in my_time.h.
     @ sql/field.cc
          change 1: @4750
                    Calling "set_datetime_warning" function if warning 
                    "MYSQL_TIME_WARN_ZERO_IN_DATE" is set.
        
          change 2: @4857
                    Returning value "4" if "MYSQL_TIME_WARN_ZERO_IN_DATE" or
                    "MYSQL_TIME_WARN_ZERO_DATE" is set. Otherwise value "2" is returned.
     @ sql/sql_table.cc
        Setting flag "abort_on_warning" of THD while creating a table in "CREATE" 
        and "ALTER" operations.

    modified:
      include/my_time.h
      mysql-test/r/create.result
      mysql-test/r/partition_innodb.result
      mysql-test/r/type_date.result
      mysql-test/r/type_datetime.result
      mysql-test/r/type_timestamp.result
      mysql-test/t/create.test
      mysql-test/t/type_date.test
      mysql-test/t/type_datetime.test
      mysql-test/t/type_timestamp.test
      sql-common/my_time.c
      sql/field.cc
      sql/item.cc
      sql/item_strfunc.cc
      sql/sp_head.cc
      sql/sql_class.h
      sql/sql_insert.cc
      sql/sql_load.cc
      sql/sql_signal.cc
      sql/sql_table.cc
      sql/sql_update.cc
      sql/unireg.cc
 3799 Georgi Kodinov	2012-03-12
      Bug #12969156: SEGMENTATION FAULT ON UNINSTALLING 
      DAEMON_EXAMPLE PLUGIN
      
      The deinit() function of the daemon example plugin was 
      calling pthread_cancel, but not waiting for the worker 
      thread to actually complete before deallocating the data
      buffer and closing the file that it writes to.
      Fixed by adding a pthread_join to wait for the thread 
      to complete before doing the cleanup work that may 
      affect it.
      Test case added.
      Removed a stray 'x' variable from the example code.

    added:
      mysql-test/r/bug12969156.result
      mysql-test/t/bug12969156-master.opt
      mysql-test/t/bug12969156.test
    modified:
      plugin/daemon_example/daemon_example.cc
=== modified file 'include/my_time.h'
--- a/include/my_time.h	2011-12-09 21:08:37 +0000
+++ b/include/my_time.h	2012-03-19 17:59:14 +0000
@@ -84,6 +84,7 @@ typedef long my_time_t;
 #define MYSQL_TIME_WARN_INVALID_TIMESTAMP 4
 #define MYSQL_TIME_WARN_ZERO_DATE         8
 #define MYSQL_TIME_NOTE_TRUNCATED        16
+#define MYSQL_TIME_WARN_ZERO_IN_DATE     32
 
 /* Usefull constants */
 #define SECONDS_IN_24H 86400L

=== modified file 'mysql-test/r/create.result'
--- a/mysql-test/r/create.result	2012-02-27 07:12:58 +0000
+++ b/mysql-test/r/create.result	2012-03-19 17:59:14 +0000
@@ -1824,7 +1824,9 @@ CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTA
 SET sql_mode = NO_ZERO_DATE;
 
 CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0);
-ERROR 42000: Invalid default value for 'c2'
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 1
+DROP TABLE t2;
 
 CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);
 ERROR 42000: Invalid default value for 'c2'
@@ -1834,7 +1836,8 @@ CREATE TABLE t2(c1 TIMESTAMP NULL);
 
 # -- Check ALTER TABLE.
 ALTER TABLE t1 ADD INDEX(c1);
-ERROR 42000: Invalid default value for 'c2'
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 1
 
 # -- Check DATETIME.
 SET sql_mode = '';

=== modified file 'mysql-test/r/partition_innodb.result'
--- a/mysql-test/r/partition_innodb.result	2012-03-14 22:36:01 +0000
+++ b/mysql-test/r/partition_innodb.result	2012-03-19 17:59:14 +0000
@@ -550,11 +550,9 @@ SET SESSION sql_mode = 'NO_ZERO_DATE';
 OPTIMIZE TABLE t1;
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
-test.t1	optimize	error	Invalid default value for 'b'
-test.t1	optimize	status	Operation failed
+test.t1	optimize	status	OK
 Warnings:
 Warning	1264	Out of range value for column 'b' at row 1
-Error	1067	Invalid default value for 'b'
 SET SESSION sql_mode = @old_mode;
 DROP TABLE t1;
 #

=== modified file 'mysql-test/r/type_date.result'
--- a/mysql-test/r/type_date.result	2012-03-01 08:09:04 +0000
+++ b/mysql-test/r/type_date.result	2012-03-19 17:59:14 +0000
@@ -480,4 +480,70 @@ DATE(MIN(a))
 2011-11-24
 DROP TABLE t1;
 SET timestamp=DEFAULT;
+#
+# Test for bug#11747847 - 34280: create table fails if NO_ZERO_DATE
+# or NO_ZERO_IN_DATE SQL mode is set.
+DROP TABLE IF EXISTS t1, t2, t3;
+SET @org_mode=@@sql_mode;
+#Table creation in strict mode with NO_ZERO_IN_DATE/NO_ZERO_DATE
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+CREATE TABLE t1 (c1 DATE DEFAULT 0);
+ERROR 42000: Invalid default value for 'c1'
+CREATE TABLE t1 (c1 DATE DEFAULT '0000-00-00');
+ERROR 42000: Invalid default value for 'c1'
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+CREATE TABLE t1 (c1 DATE DEFAULT '2012-02-00');
+ERROR 42000: Invalid default value for 'c1'
+#Table creation in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+CREATE TABLE t1 (c1 DATE DEFAULT 0);
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+CREATE TABLE t2 (c1 DATE DEFAULT '0000-00-00');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+SET @@sql_mode='NO_ZERO_IN_DATE';
+CREATE TABLE t3 (c1 DATE DEFAULT '2012-02-00');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+DROP TABLE t1, t2, t3;
+#Table creation with out any SQL modes
+SET @@sql_mode='';
+CREATE TABLE t1 (c1 DATE DEFAULT 0);
+CREATE TABLE t2 (c1 DATE DEFAULT '0000-00-00');
+CREATE TABLE t3 (c1 DATE DEFAULT '2012-02-00');
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (c1 INT);
+#Alter table in strict mode with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+ALTER TABLE t1 ADD c2 DATE DEFAULT 0;
+ERROR 42000: Invalid default value for 'c2'
+ALTER TABLE t1 ADD c2 DATE DEFAULT '0000-00-00';
+ERROR 42000: Invalid default value for 'c2'
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+ALTER TABLE t1 ADD c2 DATE DEFAULT '2012-02-00';
+ERROR 42000: Invalid default value for 'c2'
+#Alter table in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+ALTER TABLE t1 ADD c2 DATE DEFAULT 0;
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 1
+ALTER TABLE t1 ADD c3 DATE DEFAULT '0000-00-00';
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 1
+Warning	1264	Out of range value for column 'c3' at row 1
+SET @@sql_mode='NO_ZERO_IN_DATE';
+ALTER TABLE t1 ADD c4 DATE DEFAULT '2012-02-00';
+Warnings:
+Warning	1264	Out of range value for column 'c4' at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (c1 int);
+#Alter table with out any SQL modes
+SET @@sql_mode='';
+ALTER TABLE t1 ADD c2 DATE DEFAULT 0;
+ALTER TABLE t1 ADD c3 DATE DEFAULT '0000-00-00';
+ALTER TABLE t1 ADD c4 DATE DEFAULT '2012-02-00';
+DROP TABLE t1;
+SET @@sql_mode= @org_mode;
+# END of Test for bug#11747847 - 34280
 End of 5.6 tests

=== modified file 'mysql-test/r/type_datetime.result'
--- a/mysql-test/r/type_datetime.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/type_datetime.result	2012-03-19 17:59:14 +0000
@@ -762,5 +762,71 @@ a
 2001-01-01 10:10:11
 DROP TABLE t1;
 #
+# Test for bug#11747847 - 34280: create table fails if NO_ZERO_DATE
+# or NO_ZERO_IN_DATE SQL mode is set.
+DROP TABLE if EXISTS t1, t2, t3;
+SET @org_mode=@@sql_mode;
+#Table creation in strict mode
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+CREATE TABLE t1 (c1 DATETIME DEFAULT 0);
+ERROR 42000: Invalid default value for 'c1'
+CREATE TABLE t1 (c1 DATETIME DEFAULT '0000-00-00 00:00:00');
+ERROR 42000: Invalid default value for 'c1'
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+CREATE TABLE t1 (c1 DATETIME DEFAULT '2012-02-00 12:12:12');
+ERROR 42000: Invalid default value for 'c1'
+#Table creation in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+CREATE TABLE t1 (c1 DATETIME DEFAULT 0);
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+CREATE TABLE t2 (c1 DATETIME DEFAULT '0000-00-00 00:00:00');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+SET @@sql_mode='NO_ZERO_IN_DATE';
+CREATE TABLE t3 (c1 DATETIME DEFAULT '2012-02-00 12:12:12');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+DROP TABLE t1, t2, t3;
+#Table creation with out any SQL modes
+SET @@sql_mode='';
+CREATE TABLE t1 (c1 DATETIME DEFAULT 0);
+CREATE TABLE t2 (c1 DATETIME DEFAULT '0000-00-00 00:00:00');
+CREATE TABLE t3 (c1 DATETIME DEFAULT '2012-02-00 12:12:12');
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (c1 int);
+#Alter table in strict mode with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+ALTER TABLE t1 ADD c2 DATETIME DEFAULT 0;
+ERROR 42000: Invalid default value for 'c2'
+ALTER TABLE t1 ADD c2 DATETIME DEFAULT '0000-00-00';
+ERROR 42000: Invalid default value for 'c2'
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+ALTER TABLE t1 ADD c2 DATETIME DEFAULT '2012-02-00';
+ERROR 42000: Invalid default value for 'c2'
+#Alter table in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+ALTER TABLE t1 ADD c2 DATETIME DEFAULT 0;
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 1
+ALTER TABLE t1 ADD c3 DATETIME DEFAULT '0000-00-00';
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 1
+Warning	1264	Out of range value for column 'c3' at row 1
+SET @@sql_mode='NO_ZERO_IN_DATE';
+ALTER TABLE t1 ADD c4 DATETIME DEFAULT '2012-02-00';
+Warnings:
+Warning	1264	Out of range value for column 'c4' at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (c1 INT);
+#Alter table with out any SQL modes
+SET @@sql_mode='';
+ALTER TABLE t1 ADD c2 DATETIME DEFAULT 0;
+ALTER TABLE t1 ADD c3 DATETIME DEFAULT '0000-00-00';
+ALTER TABLE t1 ADD c4 DATETIME DEFAULT '2012-02-00';
+DROP TABLE t1;
+SET @@sql_mode= @org_mode;
+# END of Test for bug#11747847 - 34280
+#
 # End of 5.6 tests
 #

=== modified file 'mysql-test/r/type_timestamp.result'
--- a/mysql-test/r/type_timestamp.result	2012-02-23 16:32:32 +0000
+++ b/mysql-test/r/type_timestamp.result	2012-03-19 17:59:14 +0000
@@ -647,5 +647,75 @@ TIMESTAMP('2000-01-27','23:13:41') - TIM
 -30389003988
 DROP TABLE t1;
 #
+# Test for bug#11747847 - 34280: create table fails if NO_ZERO_DATE
+# or NO_ZERO_IN_DATE SQL mode is set.
+DROP TABLE IF EXISTS t1, t2, t3;
+SET @org_mode=@@sql_mode;
+#Table creation in strict mode
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
+ERROR 42000: Invalid default value for 'c1'
+CREATE TABLE t1 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
+ERROR 42000: Invalid default value for 'c1'
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+CREATE TABLE t1 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
+ERROR 42000: Invalid default value for 'c1'
+#Table creation in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+CREATE TABLE t2 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+SET @@sql_mode='NO_ZERO_IN_DATE';
+CREATE TABLE t3 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+DROP TABLE t1, t2, t3;
+#Table creation with out any SQL modes
+SET @@sql_mode='';
+CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
+CREATE TABLE t2 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
+CREATE TABLE t3 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (c1 INT);
+#Alter table in strict mode with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT 0;
+ERROR 42000: Invalid default value for 'c2'
+ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT '0000-00-00';
+ERROR 42000: Invalid default value for 'c2'
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT '2012-02-00';
+ERROR 42000: Invalid default value for 'c2'
+#Alter table in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT 0;
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 1
+ALTER TABLE t1 ADD c3 TIMESTAMP DEFAULT '0000-00-00';
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 1
+Warning	1264	Out of range value for column 'c3' at row 1
+SET @@sql_mode='NO_ZERO_IN_DATE';
+ALTER TABLE t1 ADD c4 TIMESTAMP DEFAULT '2012-02-00';
+Warnings:
+Warning	1264	Out of range value for column 'c4' at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (c1 INT);
+#Alter table with out any SQL modes
+SET @@sql_mode='';
+ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT 0;
+ALTER TABLE t1 ADD c3 TIMESTAMP DEFAULT '0000-00-00';
+ALTER TABLE t1 ADD c4 TIMESTAMP DEFAULT '2012-02-00';
+Warnings:
+Warning	1264	Out of range value for column 'c4' at row 1
+DROP TABLE t1;
+SET @@sql_mode= @org_mode;
+# END of Test for bug#11747847 - 34280
+#
 # End of 5.6 tests
 #

=== modified file 'mysql-test/t/create.test'
--- a/mysql-test/t/create.test	2012-02-27 07:12:58 +0000
+++ b/mysql-test/t/create.test	2012-03-19 17:59:14 +0000
@@ -1482,8 +1482,8 @@ CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTA
 SET sql_mode = NO_ZERO_DATE;
 
 --echo
---error ER_INVALID_DEFAULT
 CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0);
+DROP TABLE t2;
 
 --echo
 --error ER_INVALID_DEFAULT
@@ -1495,7 +1495,6 @@ CREATE TABLE t2(c1 TIMESTAMP NULL);
 
 --echo
 --echo # -- Check ALTER TABLE.
---error ER_INVALID_DEFAULT
 ALTER TABLE t1 ADD INDEX(c1);
 
 --echo

=== modified file 'mysql-test/t/type_date.test'
--- a/mysql-test/t/type_date.test	2012-01-24 13:17:11 +0000
+++ b/mysql-test/t/type_date.test	2012-03-19 17:59:14 +0000
@@ -398,4 +398,68 @@ SELECT DATE(MIN(a)) FROM t1;
 DROP TABLE t1;
 SET timestamp=DEFAULT;
 
+--echo #
+--echo # Test for bug#11747847 - 34280: create table fails if NO_ZERO_DATE
+--echo # or NO_ZERO_IN_DATE SQL mode is set.
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, t3;
+--enable_warnings
+SET @org_mode=@@sql_mode;
+
+--echo #Table creation in strict mode with NO_ZERO_IN_DATE/NO_ZERO_DATE
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (c1 DATE DEFAULT 0);
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (c1 DATE DEFAULT '0000-00-00');
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (c1 DATE DEFAULT '2012-02-00');
+
+--echo #Table creation in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+CREATE TABLE t1 (c1 DATE DEFAULT 0);
+CREATE TABLE t2 (c1 DATE DEFAULT '0000-00-00');
+SET @@sql_mode='NO_ZERO_IN_DATE';
+CREATE TABLE t3 (c1 DATE DEFAULT '2012-02-00');
+DROP TABLE t1, t2, t3;
+
+--echo #Table creation with out any SQL modes
+SET @@sql_mode='';
+CREATE TABLE t1 (c1 DATE DEFAULT 0);
+CREATE TABLE t2 (c1 DATE DEFAULT '0000-00-00');
+CREATE TABLE t3 (c1 DATE DEFAULT '2012-02-00');
+DROP TABLE t1, t2, t3;
+
+CREATE TABLE t1 (c1 INT);
+--echo #Alter table in strict mode with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+ALTER TABLE t1 ADD c2 DATE DEFAULT 0;
+--error ER_INVALID_DEFAULT
+ALTER TABLE t1 ADD c2 DATE DEFAULT '0000-00-00';
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+ALTER TABLE t1 ADD c2 DATE DEFAULT '2012-02-00';
+
+--echo #Alter table in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+ALTER TABLE t1 ADD c2 DATE DEFAULT 0;
+ALTER TABLE t1 ADD c3 DATE DEFAULT '0000-00-00';
+SET @@sql_mode='NO_ZERO_IN_DATE';
+ALTER TABLE t1 ADD c4 DATE DEFAULT '2012-02-00';
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 int);
+--echo #Alter table with out any SQL modes
+SET @@sql_mode='';
+ALTER TABLE t1 ADD c2 DATE DEFAULT 0;
+ALTER TABLE t1 ADD c3 DATE DEFAULT '0000-00-00';
+ALTER TABLE t1 ADD c4 DATE DEFAULT '2012-02-00';
+DROP TABLE t1;
+
+SET @@sql_mode= @org_mode;
+--echo # END of Test for bug#11747847 - 34280
+
 --echo End of 5.6 tests

=== modified file 'mysql-test/t/type_datetime.test'
--- a/mysql-test/t/type_datetime.test	2011-11-01 11:52:24 +0000
+++ b/mysql-test/t/type_datetime.test	2012-03-19 17:59:14 +0000
@@ -559,5 +559,69 @@ SELECT * FROM t1;
 DROP TABLE t1;
 
 --echo #
+--echo # Test for bug#11747847 - 34280: create table fails if NO_ZERO_DATE
+--echo # or NO_ZERO_IN_DATE SQL mode is set.
+
+--disable_warnings
+DROP TABLE if EXISTS t1, t2, t3;
+--enable_warnings
+SET @org_mode=@@sql_mode;
+
+--echo #Table creation in strict mode
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (c1 DATETIME DEFAULT 0);
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (c1 DATETIME DEFAULT '0000-00-00 00:00:00');
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (c1 DATETIME DEFAULT '2012-02-00 12:12:12');
+
+--echo #Table creation in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+CREATE TABLE t1 (c1 DATETIME DEFAULT 0);
+CREATE TABLE t2 (c1 DATETIME DEFAULT '0000-00-00 00:00:00');
+SET @@sql_mode='NO_ZERO_IN_DATE';
+CREATE TABLE t3 (c1 DATETIME DEFAULT '2012-02-00 12:12:12');
+DROP TABLE t1, t2, t3;
+
+--echo #Table creation with out any SQL modes
+SET @@sql_mode='';
+CREATE TABLE t1 (c1 DATETIME DEFAULT 0);
+CREATE TABLE t2 (c1 DATETIME DEFAULT '0000-00-00 00:00:00');
+CREATE TABLE t3 (c1 DATETIME DEFAULT '2012-02-00 12:12:12');
+DROP TABLE t1, t2, t3;
+
+CREATE TABLE t1 (c1 int);
+--echo #Alter table in strict mode with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+ALTER TABLE t1 ADD c2 DATETIME DEFAULT 0;
+--error ER_INVALID_DEFAULT
+ALTER TABLE t1 ADD c2 DATETIME DEFAULT '0000-00-00';
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+ALTER TABLE t1 ADD c2 DATETIME DEFAULT '2012-02-00';
+
+--echo #Alter table in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+ALTER TABLE t1 ADD c2 DATETIME DEFAULT 0;
+ALTER TABLE t1 ADD c3 DATETIME DEFAULT '0000-00-00';
+SET @@sql_mode='NO_ZERO_IN_DATE';
+ALTER TABLE t1 ADD c4 DATETIME DEFAULT '2012-02-00';
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 INT);
+--echo #Alter table with out any SQL modes
+SET @@sql_mode='';
+ALTER TABLE t1 ADD c2 DATETIME DEFAULT 0;
+ALTER TABLE t1 ADD c3 DATETIME DEFAULT '0000-00-00';
+ALTER TABLE t1 ADD c4 DATETIME DEFAULT '2012-02-00';
+DROP TABLE t1;
+
+SET @@sql_mode= @org_mode;
+--echo # END of Test for bug#11747847 - 34280
+
+--echo #
 --echo # End of 5.6 tests
 --echo #

=== modified file 'mysql-test/t/type_timestamp.test'
--- a/mysql-test/t/type_timestamp.test	2012-02-23 16:32:32 +0000
+++ b/mysql-test/t/type_timestamp.test	2012-03-19 17:59:14 +0000
@@ -445,7 +445,71 @@ SELECT TIMESTAMP'2000-01-27 23:13:41' -
 SELECT TIMESTAMP('2000-01-27','23:13:41') - TIMESTAMP('2003-05-16','23:53:29'); 
 DROP TABLE t1;
 
+--echo #
+--echo # Test for bug#11747847 - 34280: create table fails if NO_ZERO_DATE
+--echo # or NO_ZERO_IN_DATE SQL mode is set.
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, t3;
+--enable_warnings
+SET @org_mode=@@sql_mode;
+
+--echo #Table creation in strict mode
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+CREATE TABLE t1 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
+
+--echo #Table creation in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
+CREATE TABLE t2 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
+SET @@sql_mode='NO_ZERO_IN_DATE';
+CREATE TABLE t3 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
+DROP TABLE t1, t2, t3;
+
+--echo #Table creation with out any SQL modes
+SET @@sql_mode='';
+CREATE TABLE t1 (c1 TIMESTAMP DEFAULT 0);
+CREATE TABLE t2 (c1 TIMESTAMP DEFAULT '0000-00-00 00:00:00');
+CREATE TABLE t3 (c1 TIMESTAMP DEFAULT '2012-02-00 12:12:12');
+DROP TABLE t1, t2, t3;
+
+CREATE TABLE t1 (c1 INT);
+--echo #Alter table in strict mode with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT 0;
+--error ER_INVALID_DEFAULT
+ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT '0000-00-00';
+SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
+--error ER_INVALID_DEFAULT
+ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT '2012-02-00';
+
+--echo #Alter table in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
+SET @@sql_mode='NO_ZERO_DATE';
+ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT 0;
+ALTER TABLE t1 ADD c3 TIMESTAMP DEFAULT '0000-00-00';
+SET @@sql_mode='NO_ZERO_IN_DATE';
+ALTER TABLE t1 ADD c4 TIMESTAMP DEFAULT '2012-02-00';
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 INT);
+--echo #Alter table with out any SQL modes
+SET @@sql_mode='';
+ALTER TABLE t1 ADD c2 TIMESTAMP DEFAULT 0;
+ALTER TABLE t1 ADD c3 TIMESTAMP DEFAULT '0000-00-00';
+ALTER TABLE t1 ADD c4 TIMESTAMP DEFAULT '2012-02-00';
+DROP TABLE t1;
+
+SET @@sql_mode= @org_mode;
+--echo # END of Test for bug#11747847 - 34280
 
 --echo #
 --echo # End of 5.6 tests
 --echo #
+

=== modified file 'sql-common/my_time.c'
--- a/sql-common/my_time.c	2012-03-06 14:29:42 +0000
+++ b/sql-common/my_time.c	2012-03-19 17:59:14 +0000
@@ -122,12 +122,16 @@ my_bool check_date(const MYSQL_TIME *lti
 {
   if (not_zero_date)
   {
-    if ((((flags & TIME_NO_ZERO_IN_DATE) || !(flags & TIME_FUZZY_DATE)) &&
-         (ltime->month == 0 || ltime->day == 0)) ||
-        (!(flags & TIME_INVALID_DATES) &&
-         ltime->month && ltime->day > days_in_month[ltime->month-1] &&
-         (ltime->month != 2 || calc_days_in_year(ltime->year) != 366 ||
-          ltime->day != 29)))
+    if (((flags & TIME_NO_ZERO_IN_DATE) || !(flags & TIME_FUZZY_DATE)) &&
+        (ltime->month == 0 || ltime->day == 0))
+    {
+      *was_cut= MYSQL_TIME_WARN_ZERO_IN_DATE;
+      return TRUE;
+    }
+    else if ((!(flags & TIME_INVALID_DATES) &&
+              ltime->month && ltime->day > days_in_month[ltime->month-1] &&
+              (ltime->month != 2 || calc_days_in_year(ltime->year) != 366 ||
+               ltime->day != 29)))
     {
       *was_cut= MYSQL_TIME_WARN_OUT_OF_RANGE;
       return TRUE;

=== modified file 'sql/field.cc'
--- a/sql/field.cc	2012-03-06 14:29:42 +0000
+++ b/sql/field.cc	2012-03-19 17:59:14 +0000
@@ -4750,7 +4750,8 @@ Field_temporal::set_warnings(ErrConvStri
                          str, ts_type, !cut_incremented);
     cut_incremented= 1;
   }
-  if (warnings & (MYSQL_TIME_WARN_OUT_OF_RANGE | MYSQL_TIME_WARN_ZERO_DATE))
+  if (warnings & (MYSQL_TIME_WARN_OUT_OF_RANGE | MYSQL_TIME_WARN_ZERO_DATE |
+                  MYSQL_TIME_WARN_ZERO_IN_DATE))
   {
     set_datetime_warning(Sql_condition::WARN_LEVEL_WARN,
                          ER_WARN_DATA_OUT_OF_RANGE,
@@ -4783,6 +4784,9 @@ int Field_temporal::store(longlong nr, b
   else
   {
     DBUG_ASSERT(warnings != 0); // Must be set by convert_number_to_TIME
+
+    if (warnings & (MYSQL_TIME_WARN_ZERO_DATE | MYSQL_TIME_WARN_ZERO_IN_DATE))
+      error= current_thd->is_strict_mode() ? 1 : 3;
   }
   if (warnings)
     set_warnings(ErrConvString(nr, unsigned_val), warnings);
@@ -4800,7 +4804,11 @@ int Field_temporal::store_lldiv_t(const
   else if (!*warnings)
   {
     DBUG_ASSERT(warnings != 0); // Must be set by convert_number_to_TIME
-   }
+  }
+
+  if (*warnings & (MYSQL_TIME_WARN_ZERO_DATE | MYSQL_TIME_WARN_ZERO_IN_DATE))
+    error= current_thd->is_strict_mode() ? 1 : 3;
+
   return error;
 }
 
@@ -4857,7 +4865,11 @@ Field_temporal::store(const char *str, u
   if (convert_str_to_TIME(str, len, cs, &ltime, &status))
   {
     reset();
-    error= 2;
+    if (status.warnings & (MYSQL_TIME_WARN_ZERO_DATE |
+                           MYSQL_TIME_WARN_ZERO_IN_DATE))
+      error= current_thd->is_strict_mode() ? 1 : 3;
+    else
+      error= 2;
   }
   else
   {
@@ -9667,9 +9679,7 @@ bool Create_field::init(THD *thd, char *
         A default other than '' is always an error, and any non-NULL
         specified default is an error in strict mode.
       */
-      if (res->length() || (thd->variables.sql_mode &
-                            (MODE_STRICT_TRANS_TABLES |
-                             MODE_STRICT_ALL_TABLES)))
+      if (res->length() || thd->is_strict_mode())
       {
         my_error(ER_BLOB_CANT_HAVE_DEFAULT, MYF(0),
                  fld_name); /* purecov: inspected */

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2012-03-15 14:41:06 +0000
+++ b/sql/item.cc	2012-03-19 17:59:14 +0000
@@ -5791,8 +5791,7 @@ String *Item::check_well_formed_result(S
                cs->csname,  hexbuf);
       return 0;
     }
-    if ((thd->variables.sql_mode &
-         (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES)))
+    if (thd->is_strict_mode())
     {
       null_value= 1;
       str= 0;

=== modified file 'sql/item_strfunc.cc'
--- a/sql/item_strfunc.cc	2012-03-06 14:29:42 +0000
+++ b/sql/item_strfunc.cc	2012-03-19 17:59:14 +0000
@@ -119,9 +119,7 @@ bool Item_str_func::fix_fields(THD *thd,
     In Item_str_func::check_well_formed_result() we may set null_value
     flag on the same condition as in test() below.
   */
-  maybe_null= (maybe_null ||
-               test(thd->variables.sql_mode &
-                    (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES)));
+  maybe_null= (maybe_null || thd->is_strict_mode());
   return res;
 }
 

=== modified file 'sql/sp_head.cc'
--- a/sql/sp_head.cc	2012-03-13 13:43:50 +0000
+++ b/sql/sp_head.cc	2012-03-19 17:59:14 +0000
@@ -398,9 +398,7 @@ sp_eval_expr(THD *thd, Field *result_fie
   */
 
   thd->count_cuted_fields= CHECK_FIELD_ERROR_FOR_NULL;
-  thd->abort_on_warning=
-    thd->variables.sql_mode &
-    (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES);
+  thd->abort_on_warning= thd->is_strict_mode();
   thd->transaction.stmt.reset_unsafe_rollback_flags();
 
   /* Save the value in the field. Convert the value if needed. */

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2012-03-13 13:16:27 +0000
+++ b/sql/sql_class.h	2012-03-19 17:59:14 +0000
@@ -3172,6 +3172,11 @@ public:
     return variables.sql_mode &
       (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES);
   }
+  inline bool is_strict_mode() const
+  {
+    return test(variables.sql_mode & (MODE_STRICT_TRANS_TABLES |
+                                      MODE_STRICT_ALL_TABLES));
+  }
   inline Time_zone *time_zone()
   {
     time_zone_used= 1;

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	2012-03-14 09:52:49 +0000
+++ b/sql/sql_insert.cc	2012-03-19 17:59:14 +0000
@@ -714,9 +714,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
                            FALSE,
                            (fields.elements || !value_count ||
                             table_list->view != 0),
-                           !ignore && (thd->variables.sql_mode &
-                                       (MODE_STRICT_TRANS_TABLES |
-                                        MODE_STRICT_ALL_TABLES))))
+                           !ignore && thd->is_strict_mode()))
     goto exit_without_my_ok;
 
   /* mysql_prepare_insert set table_list->table if it was not set */
@@ -834,9 +832,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
       table->file->ha_start_bulk_insert(values_list.elements);
   }
 
-  thd->abort_on_warning= (!ignore && (thd->variables.sql_mode &
-                                       (MODE_STRICT_TRANS_TABLES |
-                                        MODE_STRICT_ALL_TABLES)));
+  thd->abort_on_warning= (!ignore && thd->is_strict_mode());
 
   prepare_triggers_for_insert_stmt(table);
 
@@ -3317,9 +3313,9 @@ select_insert::prepare(List<Item> &value
   if (!res && fields->elements)
   {
     bool saved_abort_on_warning= thd->abort_on_warning;
-    thd->abort_on_warning= !ignore_errors && (thd->variables.sql_mode &
-                                            (MODE_STRICT_TRANS_TABLES |
-                                             MODE_STRICT_ALL_TABLES));
+
+    thd->abort_on_warning= !ignore_errors && thd->is_strict_mode();
+
     res= check_that_all_fields_are_given_values(thd, table_list->table, 
                                                 table_list);
     thd->abort_on_warning= saved_abort_on_warning;
@@ -3449,10 +3445,7 @@ select_insert::prepare(List<Item> &value
     table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE);
   if (duplicate_handling == DUP_UPDATE)
     table->file->extra(HA_EXTRA_INSERT_WITH_UPDATE);
-  thd->abort_on_warning= (!ignore_errors &&
-                          (thd->variables.sql_mode &
-                           (MODE_STRICT_TRANS_TABLES |
-                            MODE_STRICT_ALL_TABLES)));
+  thd->abort_on_warning= (!ignore_errors && thd->is_strict_mode());
   res= (table_list->prepare_where(thd, 0, TRUE) ||
         table_list->prepare_check_option(thd));
 
@@ -4068,10 +4061,7 @@ select_create::prepare(List<Item> &value
     table->file->extra(HA_EXTRA_INSERT_WITH_UPDATE);
   if (thd->locked_tables_mode <= LTM_LOCK_TABLES)
     table->file->ha_start_bulk_insert((ha_rows) 0);
-  thd->abort_on_warning= (!ignore_errors &&
-                          (thd->variables.sql_mode &
-                           (MODE_STRICT_TRANS_TABLES |
-                            MODE_STRICT_ALL_TABLES)));
+  thd->abort_on_warning= (!ignore_errors && thd->is_strict_mode());
   if (check_that_all_fields_are_given_values(thd, table, table_list))
     DBUG_RETURN(1);
   table->mark_columns_needed_for_insert();

=== modified file 'sql/sql_load.cc'
--- a/sql/sql_load.cc	2012-03-14 09:52:49 +0000
+++ b/sql/sql_load.cc	2012-03-19 17:59:14 +0000
@@ -490,10 +490,7 @@ int mysql_load(THD *thd,sql_exchange *ex
       table->file->ha_start_bulk_insert((ha_rows) 0);
     table->copy_blobs=1;
 
-    thd->abort_on_warning= (!ignore &&
-                            (thd->variables.sql_mode &
-                             (MODE_STRICT_TRANS_TABLES |
-                              MODE_STRICT_ALL_TABLES)));
+    thd->abort_on_warning= (!ignore && thd->is_strict_mode());
 
     if (ex->filetype == FILETYPE_XML) /* load xml */
       error= read_xml_field(thd, info, table_list, fields_vars,

=== modified file 'sql/sql_signal.cc'
--- a/sql/sql_signal.cc	2012-03-13 13:16:27 +0000
+++ b/sql/sql_signal.cc	2012-03-19 17:59:14 +0000
@@ -243,8 +243,7 @@ static int assign_condition_item(MEM_ROO
   truncated= assign_fixed_string(mem_root, & my_charset_utf8_bin, 64, ci, str);
   if (truncated)
   {
-    if (thd->variables.sql_mode & (MODE_STRICT_TRANS_TABLES |
-                                   MODE_STRICT_ALL_TABLES))
+    if (thd->is_strict_mode())
     {
       thd->raise_error_printf(ER_COND_ITEM_TOO_LONG, name);
       DBUG_RETURN(1);
@@ -348,8 +347,7 @@ int Sql_cmd_common_signal::eval_signal_i
                                    & utf8_text, str);
     if (truncated)
     {
-      if (thd->variables.sql_mode & (MODE_STRICT_TRANS_TABLES |
-                                     MODE_STRICT_ALL_TABLES))
+      if (thd->is_strict_mode())
       {
         thd->raise_error_printf(ER_COND_ITEM_TOO_LONG,
                                 "MESSAGE_TEXT");

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2012-03-14 12:52:51 +0000
+++ b/sql/sql_table.cc	2012-03-19 17:59:14 +0000
@@ -2704,8 +2704,7 @@ bool check_duplicates_in_interval(const
     {
       THD *thd= current_thd;
       ErrConvString err(*cur_value, *cur_length, cs);
-      if ((current_thd->variables.sql_mode &
-         (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES)))
+      if (current_thd->is_strict_mode())
       {
         my_error(ER_DUPLICATED_VALUE_IN_TYPE, MYF(0),
                  name, err.ptr(), set_or_name);
@@ -3934,8 +3933,7 @@ bool validate_comment_length(THD *thd, c
                                                    max_len);
   if (tmp_len < *comment_len)
   {
-    if ((thd->variables.sql_mode &
-        (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES)))
+    if (thd->is_strict_mode())
     {
        my_error(err_code, MYF(0),
        comment_name, static_cast<ulong>(max_len));
@@ -4009,8 +4007,7 @@ static bool prepare_blob_field(THD *thd,
     /* Convert long VARCHAR columns to TEXT or BLOB */
     char warn_buff[MYSQL_ERRMSG_SIZE];
 
-    if (sql_field->def || (thd->variables.sql_mode & (MODE_STRICT_TRANS_TABLES |
-                                                      MODE_STRICT_ALL_TABLES)))
+    if (sql_field->def || thd->is_strict_mode())
     {
       my_error(ER_TOO_BIG_FIELDLENGTH, MYF(0), sql_field->field_name,
                static_cast<ulong>(MAX_FIELD_VARCHARLENGTH /
@@ -4663,6 +4660,9 @@ bool mysql_create_table(THD *thd, TABLE_
   /* Got lock. */
   DEBUG_SYNC(thd, "locked_table_name");
 
+  /* We can abort create table for any table type */
+  thd->abort_on_warning= thd->is_strict_mode();
+
   promote_first_timestamp_column(&alter_info->create_list);
 
   result= mysql_create_table_no_lock(thd, create_table->db,
@@ -4692,6 +4692,8 @@ bool mysql_create_table(THD *thd, TABLE_
       result= write_bin_log(thd, TRUE, thd->query(), thd->query_length(), is_trans);
     }
   }
+
+  thd->abort_on_warning= false;
 end:
   DBUG_RETURN(result);
 }
@@ -6840,6 +6842,8 @@ bool mysql_alter_table(THD *thd,char *ne
   DEBUG_SYNC(thd, "alter_table_before_create_table_no_lock");
   DBUG_EXECUTE_IF("sleep_before_create_table_no_lock",
                   my_sleep(100000););
+  /* We can abort alter table for any table type */
+  thd->abort_on_warning= !ignore && thd->is_strict_mode();
 
   promote_first_timestamp_column(&alter_info->create_list);
 
@@ -6854,6 +6858,7 @@ bool mysql_alter_table(THD *thd,char *ne
                                     alter_info,
                                     1, 0, NULL);
   reenable_binlog(thd);
+  thd->abort_on_warning= false;
   if (error)
     goto err;
 
@@ -7451,9 +7456,7 @@ copy_data_between_tables(TABLE *from,TAB
   alter_table_manage_keys(to, from->file->indexes_are_disabled(), keys_onoff);
 
   /* We can abort alter table for any table type */
-  thd->abort_on_warning= !ignore && test(thd->variables.sql_mode &
-                                         (MODE_STRICT_TRANS_TABLES |
-                                          MODE_STRICT_ALL_TABLES));
+  thd->abort_on_warning= !ignore && thd->is_strict_mode();
 
   from->file->info(HA_STATUS_VARIABLE);
   to->file->ha_start_bulk_insert(from->file->stats.records);

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2012-03-06 14:29:42 +0000
+++ b/sql/sql_update.cc	2012-03-19 17:59:14 +0000
@@ -659,10 +659,8 @@ int mysql_update(THD *thd,
   THD_STAGE_INFO(thd, stage_updating);
 
   transactional_table= table->file->has_transactions();
-  thd->abort_on_warning= test(!ignore &&
-                              (thd->variables.sql_mode &
-                               (MODE_STRICT_TRANS_TABLES |
-                                MODE_STRICT_ALL_TABLES)));
+  thd->abort_on_warning= (!ignore && thd->is_strict_mode());
+
   if (table->triggers &&
       table->triggers->has_triggers(TRG_EVENT_UPDATE,
                                     TRG_ACTION_AFTER))
@@ -1445,9 +1443,7 @@ bool mysql_multi_update(THD *thd,
     DBUG_RETURN(TRUE);
   }
 
-  thd->abort_on_warning= test(thd->variables.sql_mode &
-                              (MODE_STRICT_TRANS_TABLES |
-                               MODE_STRICT_ALL_TABLES));
+  thd->abort_on_warning= thd->is_strict_mode();
 
   if (thd->lex->describe)
     res= explain_multi_table_modification(thd, *result);

=== modified file 'sql/unireg.cc'
--- a/sql/unireg.cc	2012-03-14 11:56:51 +0000
+++ b/sql/unireg.cc	2012-03-19 17:59:14 +0000
@@ -1128,6 +1128,12 @@ static bool make_empty_rec(THD *thd, Fil
       /* If not ok or warning of level 'note' */
       if (res != 0 && res != 3)
       {
+        /*
+          clear current error and report INVALID DEFAULT value error message
+          */
+        if (thd->is_error())
+          thd->clear_error();
+
         my_error(ER_INVALID_DEFAULT, MYF(0), regfield->field_name);
         error= 1;
         delete regfield; //To avoid memory leak

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (praveenkumar.hulakund:3799 to 3800)Bug#11747847Praveenkumar Hulakund19 Mar