Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
Description
--delimiter $
|
create procedure pr1() |
begin
|
set explicit_defaults_for_timestamp= off; |
create table t1 (ts timestamp); |
end $ |
create procedure pr2() |
begin
|
set explicit_defaults_for_timestamp= on; |
create table t2 (ts timestamp); |
end $ |
--delimiter ;
|
|
set explicit_defaults_for_timestamp= on; |
call pr1();
|
select @@explicit_defaults_for_timestamp; |
show create table t1; |
|
set explicit_defaults_for_timestamp= off; |
call pr2();
|
select @@explicit_defaults_for_timestamp; |
show create table t2; |
|
# Cleanup
|
drop procedure pr1; |
drop procedure pr2; |
drop table t1, t2; |
bb-10.10-MDEV-28632 c4407143a2 |
set explicit_defaults_for_timestamp= on; |
call pr1();
|
select @@explicit_defaults_for_timestamp; |
@@explicit_defaults_for_timestamp
|
0
|
show create table t1; |
Table Create Table |
t1 CREATE TABLE `t1` ( |
`ts` timestamp NULL DEFAULT NULL |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
set explicit_defaults_for_timestamp= off; |
call pr2();
|
select @@explicit_defaults_for_timestamp; |
@@explicit_defaults_for_timestamp
|
1
|
show create table t2; |
Table Create Table |
t2 CREATE TABLE `t2` ( |
`ts` timestamp NOT NULL |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
So, the procedures do change the variable value, but within the procedure it doesn't work the same way as outside.
If it's changed within the procedure from ON to OFF, then there seems to be no effect at all, the table is still created with TIMESTAMP NULL DEFAULT NULL.
If it's changed from OFF to ON, there is a partial effect, the column is created as NOT NULL, but without default/on update.
MySQL 8.0 behaves in a similar way, only it adds default zero date in the second case, which isn't what one would expect either.
Attachments
Issue Links
- is blocked by
-
MDEV-29206 DEFAULT NULL for NOT NULL columns is not always detected
- Closed
- is caused by
-
MDEV-28632 Change default of explicit_defaults_for_timestamp to ON
- Closed