From: Gopal Shankar Date: June 11 2012 6:11pm Subject: bzr push into mysql-trunk-wl6292 branch (gopal.shankar:3925 to 3926) WL#6292 List-Archive: http://lists.mysql.com/commits/144203 Message-Id: <201206111811.q5BIBmM0005021@acsmt357.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3926 Gopal Shankar 2012-06-11 WL#6292 - Make TIMESTAMP columns nullable by default. This additional patch contains, * Fix comments from Jon Olav. * Added test case for CREATE SELECT. modified: mysql-test/include/mtr_warnings.sql mysql-test/r/type_timestamp.result mysql-test/t/type_timestamp.test sql/mysqld.cc sql/sql_class.h sql/sys_vars.cc 3925 Gopal Shankar 2012-06-11 WL#6292 - Make TIMESTAMP columns nullable by default. This patch contains, * Making use of new Relay_log_info::adapt_to_master_version(), that represent an interface to provide adapting the slave applier to master versions. This is provided by Andrei as part of, Bug#14162406 - INSUFFICIENT MASTER VERSION ADAPTATION INFRASTRUCTURE ON SLAVE With the above patch, the slave applier thread sets --explicit_default_for_timestamp ONLY when the master version is upgraded or downgraded, rather than per statement execution. modified: sql/log_event.cc sql/log_event.h sql/rpl_rli.cc sql/rpl_rli.h sql/rpl_rli_pdb.cc sql/rpl_rli_pdb.h sql/sql_parse.cc === modified file 'mysql-test/include/mtr_warnings.sql' --- a/mysql-test/include/mtr_warnings.sql 2012-05-31 14:13:20 +0000 +++ b/mysql-test/include/mtr_warnings.sql 2012-06-11 18:07:29 +0000 @@ -174,7 +174,7 @@ INSERT INTO global_suppressions VALUES /*It will print a warning if a new UUID of server is generated.*/ ("No existing UUID has been found, so we assume that this is the first time that this server has been started.*"), /*It will print a warning if server is run without --explicit_defaults_for_timestamp.*/ - ("TIMESTAMP with implicit DEFAULT value is depreciated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)*"), + ("TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)*"), /* Added 2009-08-XX after fixing Bug #42408 */ === modified file 'mysql-test/r/type_timestamp.result' --- a/mysql-test/r/type_timestamp.result 2012-06-11 05:25:14 +0000 +++ b/mysql-test/r/type_timestamp.result 2012-06-11 18:07:29 +0000 @@ -716,11 +716,24 @@ Warning 1264 Out of range value for colu DROP TABLE t1; SET @@sql_mode= @org_mode; # END of Test for bug#11747847 - 34280 +# +# WL6292 - Test cases to test new behavior with +# --explicit_defaults_for_timestamp +# Almost all the scenario's required to test this WL, is already tested +# by most of existing test case. Adding some basic tests here. +# CREATE TABLE t1 (f1 TIMESTAMP, f2 TIMESTAMP); ALTER TABLE t1 ADD COLUMN (f3 TIMESTAMP NOT NULL); ALTER TABLE t1 ADD COLUMN (f4 TIMESTAMP DEFAULT NULL); ALTER TABLE t1 ADD COLUMN (f5 TIMESTAMP DEFAULT '0:0:0'); ALTER TABLE t1 ADD COLUMN (f6 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); +# Following is expected out of SHOW CREATE TABLE +# `f1` timestamp NULL DEFAULT NULL, +# `f2` timestamp NULL DEFAULT NULL, +# `f3` timestamp NOT NULL, +# `f4` timestamp NULL DEFAULT NULL, +# `f5` timestamp NULL DEFAULT '0000-00-00 00:00:00' +# `f6` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -731,7 +744,29 @@ t1 CREATE TABLE `t1` ( `f5` timestamp NULL DEFAULT '0000-00-00 00:00:00', `f6` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# The new behavior affects CREATE SELECT with column definitions +# before SELECT keyword. The columns f1,f2 in t2 do not get promoted +# with the new behavior. Following is expected out of SHOW CREATE TABLE +# +# `f1` timestamp NULL DEFAULT NULL, +# `f2` timestamp NULL DEFAULT NULL, +# `f3` timestamp NOT NULL, +# `f4` timestamp NULL DEFAULT NULL, +# `f5` timestamp NULL DEFAULT '0000-00-00 00:00:00', +# `f6` timestamp NULL DEFAULT NULL +CREATE TABLE t2 (f1 TIMESTAMP, f2 TIMESTAMP) SELECT f1,f2,f3,f4,f5,f6 FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `f1` timestamp NULL DEFAULT NULL, + `f2` timestamp NULL DEFAULT NULL, + `f3` timestamp NOT NULL, + `f4` timestamp NULL DEFAULT NULL, + `f5` timestamp NULL DEFAULT '0000-00-00 00:00:00', + `f6` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; +DROP TABLE t2; # # End of 5.6 tests # === modified file 'mysql-test/t/type_timestamp.test' --- a/mysql-test/t/type_timestamp.test 2012-06-11 05:25:14 +0000 +++ b/mysql-test/t/type_timestamp.test 2012-06-11 18:07:29 +0000 @@ -509,27 +509,43 @@ DROP TABLE t1; SET @@sql_mode= @org_mode; --echo # END of Test for bug#11747847 - 34280 -# -# WL6292 - Test cases to test new behavior with --explicit_defaults_for_timestamp -# Almost all the scenario's required to test this WL, is already tested -# by most of existing test case. Adding some basic tests here. -# +--echo # +--echo # WL6292 - Test cases to test new behavior with +--echo # --explicit_defaults_for_timestamp +--echo # Almost all the scenario's required to test this WL, is already tested +--echo # by most of existing test case. Adding some basic tests here. +--echo # CREATE TABLE t1 (f1 TIMESTAMP, f2 TIMESTAMP); ALTER TABLE t1 ADD COLUMN (f3 TIMESTAMP NOT NULL); ALTER TABLE t1 ADD COLUMN (f4 TIMESTAMP DEFAULT NULL); ALTER TABLE t1 ADD COLUMN (f5 TIMESTAMP DEFAULT '0:0:0'); ALTER TABLE t1 ADD COLUMN (f6 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); -# Following is expected out of SHOW CREATE TABLE -# `f1` timestamp NULL DEFAULT NULL, -# `f2` timestamp NULL DEFAULT NULL, -# `f3` timestamp NOT NULL, -# `f4` timestamp NULL DEFAULT NULL, -# `f5` timestamp NULL DEFAULT '0000-00-00 00:00:00' -# `f6` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMW CREATE TABLE t1; - +--echo # Following is expected out of SHOW CREATE TABLE +--echo # `f1` timestamp NULL DEFAULT NULL, +--echo # `f2` timestamp NULL DEFAULT NULL, +--echo # `f3` timestamp NOT NULL, +--echo # `f4` timestamp NULL DEFAULT NULL, +--echo # `f5` timestamp NULL DEFAULT '0000-00-00 00:00:00' +--echo # `f6` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP SHOW CREATE TABLE t1; + +--echo # The new behavior affects CREATE SELECT with column definitions +--echo # before SELECT keyword. The columns f1,f2 in t2 do not get promoted +--echo # with the new behavior. Following is expected out of SHOW CREATE TABLE +--echo # +--echo # `f1` timestamp NULL DEFAULT NULL, +--echo # `f2` timestamp NULL DEFAULT NULL, +--echo # `f3` timestamp NOT NULL, +--echo # `f4` timestamp NULL DEFAULT NULL, +--echo # `f5` timestamp NULL DEFAULT '0000-00-00 00:00:00', +--echo # `f6` timestamp NULL DEFAULT NULL + +CREATE TABLE t2 (f1 TIMESTAMP, f2 TIMESTAMP) SELECT f1,f2,f3,f4,f5,f6 FROM t1; +SHOW CREATE TABLE t2; + DROP TABLE t1; +DROP TABLE t2; --echo # --echo # End of 5.6 tests === modified file 'sql/mysqld.cc' --- a/sql/mysqld.cc 2012-06-11 05:25:14 +0000 +++ b/sql/mysqld.cc 2012-06-11 18:07:29 +0000 @@ -8358,10 +8358,10 @@ static int get_options(int *argc_ptr, ch /* TIMESTAMP columns get implicit DEFAULT values when --explicit_defaults_for_timestamp is not set. - This behavior is depreciated now. + This behavior is deprecated now. */ if (!global_system_variables.explicit_defaults_for_timestamp) - sql_print_warning("TIMESTAMP with implicit DEFAULT value is depreciated. " + sql_print_warning("TIMESTAMP with implicit DEFAULT value is deprecated. " "Please use --explicit_defaults_for_timestamp server " "option (see documentation for more details)."); === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2012-06-11 02:27:08 +0000 +++ b/sql/sql_class.h 2012-06-11 18:07:29 +0000 @@ -524,9 +524,11 @@ typedef struct system_variables Time_zone *time_zone; /* - TIMESTAMP fields are created with DEFAULT clauses implicitly without - users request. This flag when set, disables implicit default values - and expect users to provide explicit default clause. + TIMESTAMP fields are by default created with DEFAULT clauses + implicitly without users request. This flag when set, disables + implicit default values and expect users to provide explicit + default clause. i.e., when set columns are defined as NULL, + instead of NOT NULL by default. */ my_bool explicit_defaults_for_timestamp; === modified file 'sql/sys_vars.cc' --- a/sql/sys_vars.cc 2012-06-11 05:25:14 +0000 +++ b/sql/sys_vars.cc 2012-06-11 18:07:29 +0000 @@ -787,7 +787,7 @@ static Sys_var_mybool Sys_binlog_direct( NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(binlog_direct_check)); /** - This variable is not visible to && is read only to users. It can be + This variable is not visible to and is read only to users. It can be enabled or disabled only at mysqld startup. This variable is used by User thread and as well as by replication SQL Thread to apply relay_log. SQL thread enables/disables this option based on relay_log's from @@ -798,11 +798,9 @@ static Sys_var_mybool Sys_binlog_direct( static Sys_var_mybool Sys_explicit_defaults_for_timestamp( "explicit_defaults_for_timestamp", "This option causes CREATE TABLE to make all TIMESTAMP columns " - "to have DEFAULT NULL property, the behavior as that of other " + "to have DEFAULT NULL property, the same behavior as that of other " "column types. TIMESTAMP columns get implicit DEFAULT clauses " - "when this options is disabled.\n" - "It is recommended to start the server with this option enabled, " - "because old behavior is deprecated.", + "when this options is disabled. The old behavior is deprecated.", READ_ONLY NOT_VISIBLE SESSION_VAR(explicit_defaults_for_timestamp), CMD_LINE(OPT_ARG), DEFAULT(FALSE)); No bundle (reason: useless for push emails).