Note: Even though this is an old known issue not specific to 10.10, I think it becomes much more important now when MDEV-28632 is about to be pushed into 10.10 main; so I'm marking it as 10.10V1 against the standard procedure. Please feel free to adjust both the marking and the priority after deciding what to do about it.
With explicit_defaults_for_timestamp, if a TIMESTAMP column is defined as NOT NULL without a default value, it still partially retains an implicit (only undeclared) logic and gets a current timestamp value if NULL is attempted to be inserted.
Run with --mysqld=--explicit-defaults-for-timestamp=on
CREATETABLE t (a TIMESTAMPNOTNULL);
SHOW CREATETABLE t;
INSERTINTO t VALUES (NULL);
SELECT * FROM t;
# Cleanup
DROPTABLE t;
10.3 efdbb3cf
CREATETABLE t (a TIMESTAMPNOTNULL);
SHOW CREATETABLE t;
TableCreateTable
t CREATETABLE `t` (
`a` timestampNOTNULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERTINTO t VALUES (NULL);
SELECT * FROM t;
a
2022-06-30 00:34:21
Attachments
Issue Links
relates to
MDEV-3929Add system variable explicit_defaults_for_timestamp for compatibility with MySQL
Closed
MDEV-28632Change default of explicit_defaults_for_timestamp to ON
That's different. It's a timestamp behavior on NULL. If you'd try
INSERT t1 () VALUES ();
you'd get "no default" error. If you'd set a default for your table, like
CREATETABLE t (a TIMESTAMPNOTNULLDEFAULT 20201010020304);
then NULL would still insert CURRENT_TIMESTAMP, while () would insert the default value.
Sergei Golubchik
added a comment - That's different. It's a timestamp behavior on NULL. If you'd try
INSERT t1 () VALUES ();
you'd get "no default" error. If you'd set a default for your table, like
CREATE TABLE t (a TIMESTAMP NOT NULL DEFAULT 20201010020304);
then NULL would still insert CURRENT_TIMESTAMP , while () would insert the default value.
Yes, I know. I didn't say it was a default, it's non-standard timestamp logic, while explicit_defaults_for_timestamp presumes making timestamps behave the same as other column types. Of course, in MariaDB KB the variable is barely documented at all, so one can argue that we didn't promise anything other than the change NULL and DEFAULT clauses, but in reality, the variable was introduced for compatibility with MySQL, and MySQL documentation is quite specific about this:
This system variable determines whether the server enables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns.
The description shouldn't have had the "without a default value" note. It was meant to emphasize that it doesn't have DEFAULT current_timestamp(), but I see how it could have been misinterpreted.
Elena Stepanova
added a comment - - edited Yes, I know. I didn't say it was a default, it's non-standard timestamp logic , while explicit_defaults_for_timestamp presumes making timestamps behave the same as other column types. Of course, in MariaDB KB the variable is barely documented at all , so one can argue that we didn't promise anything other than the change NULL and DEFAULT clauses, but in reality, the variable was introduced for compatibility with MySQL, and MySQL documentation is quite specific about this:
This system variable determines whether the server enables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns.
The description shouldn't have had the "without a default value" note. It was meant to emphasize that it doesn't have DEFAULT current_timestamp() , but I see how it could have been misinterpreted.
I'd prefer explicit_defaults_for_timestamp to mean that one needs to specify DEFAULT clause for TIMESTAMP columns explicitly, otherwise they'll have no default value.
As such, it does not affect behavior on NULL in NOT NULL columns. One can even argue that it's a bug in MySQL that explicit_defaults_for_timestamp affects behavior completely unrelated to defaults. It should've been standard_timestamp or no_implicit_timestamp_magic or something.
Sergei Golubchik
added a comment - I'd prefer explicit_defaults_for_timestamp to mean that one needs to specify DEFAULT clause for TIMESTAMP columns explicitly , otherwise they'll have no default value.
As such, it does not affect behavior on NULL in NOT NULL columns. One can even argue that it's a bug in MySQL that explicit_defaults_for_timestamp affects behavior completely unrelated to defaults. It should've been standard_timestamp or no_implicit_timestamp_magic or something.
One can argue (and more than one argued in the past) that the original idea of weird incomprehensible non-default timestamp behavior – all aspects of it – is a bug in MySQL, which wasn't easy to fix due to years of legacy, so the variable was introduced as a temporary measure to make the transition smoother. Introducing two different variables for this would have been clearly an overkill.
Although one can't squeeze all subtleties into a variable name, yes, in the hindsight probably a better name would have been standard_behavior_for_timestamp or something, but it didn't happen. I don't think an imperfect variable name is a good enough reason to start another 10+ years of legacy with "almost standard behavior but not quite". Better to make it sensible once for all.
Elena Stepanova
added a comment - One can argue (and more than one argued in the past) that the original idea of weird incomprehensible non-default timestamp behavior – all aspects of it – is a bug in MySQL, which wasn't easy to fix due to years of legacy, so the variable was introduced as a temporary measure to make the transition smoother. Introducing two different variables for this would have been clearly an overkill.
Although one can't squeeze all subtleties into a variable name, yes, in the hindsight probably a better name would have been standard_behavior_for_timestamp or something, but it didn't happen. I don't think an imperfect variable name is a good enough reason to start another 10+ years of legacy with "almost standard behavior but not quite". Better to make it sensible once for all.
People
Sergei Golubchik
Elena Stepanova
Votes:
0Vote for this issue
Watchers:
2Start watching this issue
Dates
Created:
Updated:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":792.0999999046326,"ttfb":186.79999995231628,"pageVisibility":"visible","entityId":112371,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"77037c5d-7619-46fc-8a0b-043f8f76cf7b","navigationType":0,"readyForUser":913.5,"redirectCount":0,"resourceLoadedEnd":525,"resourceLoadedStart":192.29999995231628,"resourceTiming":[{"duration":56.09999990463257,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":192.29999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":192.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":248.39999985694885,"responseStart":0,"secureConnectionStart":0},{"duration":56.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":192.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":192.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":249,"responseStart":0,"secureConnectionStart":0},{"duration":209.70000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":192.59999990463257,"connectEnd":192.59999990463257,"connectStart":192.59999990463257,"domainLookupEnd":192.59999990463257,"domainLookupStart":192.59999990463257,"fetchStart":192.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":252.29999995231628,"responseEnd":402.2999999523163,"responseStart":271.7999999523163,"secureConnectionStart":192.59999990463257},{"duration":332.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":192.70000004768372,"connectEnd":192.70000004768372,"connectStart":192.70000004768372,"domainLookupEnd":192.70000004768372,"domainLookupStart":192.70000004768372,"fetchStart":192.70000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":253.59999990463257,"responseEnd":525,"responseStart":290.2000000476837,"secureConnectionStart":192.70000004768372},{"duration":92.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":192.79999995231628,"connectEnd":192.79999995231628,"connectStart":192.79999995231628,"domainLookupEnd":192.79999995231628,"domainLookupStart":192.79999995231628,"fetchStart":192.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":257.5,"responseEnd":285.2999999523163,"responseStart":282.59999990463257,"secureConnectionStart":192.79999995231628},{"duration":82.90000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":192.89999985694885,"connectEnd":192.89999985694885,"connectStart":192.89999985694885,"domainLookupEnd":192.89999985694885,"domainLookupStart":192.89999985694885,"fetchStart":192.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":257.59999990463257,"responseEnd":275.7999999523163,"responseStart":274.7000000476837,"secureConnectionStart":192.89999985694885},{"duration":92.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":193,"connectEnd":193,"connectStart":193,"domainLookupEnd":193,"domainLookupStart":193,"fetchStart":193,"redirectEnd":0,"redirectStart":0,"requestStart":258.89999985694885,"responseEnd":285.09999990463257,"responseStart":281.7999999523163,"secureConnectionStart":193},{"duration":62,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":193.09999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":193.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":255.09999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":92.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":193.09999990463257,"connectEnd":193.09999990463257,"connectStart":193.09999990463257,"domainLookupEnd":193.09999990463257,"domainLookupStart":193.09999990463257,"fetchStart":193.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":259.2000000476837,"responseEnd":285.5,"responseStart":283.2999999523163,"secureConnectionStart":193.09999990463257},{"duration":63.200000047683716,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":193.29999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":193.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":256.5,"responseStart":0,"secureConnectionStart":0},{"duration":95.60000014305115,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":193.39999985694885,"connectEnd":193.39999985694885,"connectStart":193.39999985694885,"domainLookupEnd":193.39999985694885,"domainLookupStart":193.39999985694885,"fetchStart":193.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":260.39999985694885,"responseEnd":289,"responseStart":286.2000000476837,"secureConnectionStart":193.39999985694885},{"duration":285.10000014305115,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":199.39999985694885,"connectEnd":199.39999985694885,"connectStart":199.39999985694885,"domainLookupEnd":199.39999985694885,"domainLookupStart":199.39999985694885,"fetchStart":199.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":289.39999985694885,"responseEnd":484.5,"responseStart":476,"secureConnectionStart":199.39999985694885},{"duration":286.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":199.5,"connectEnd":199.5,"connectStart":199.5,"domainLookupEnd":199.5,"domainLookupStart":199.5,"fetchStart":199.5,"redirectEnd":0,"redirectStart":0,"requestStart":306.89999985694885,"responseEnd":486,"responseStart":480,"secureConnectionStart":199.5},{"duration":64.79999995231628,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":548.2000000476837,"connectEnd":548.2000000476837,"connectStart":548.2000000476837,"domainLookupEnd":548.2000000476837,"domainLookupStart":548.2000000476837,"fetchStart":548.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":583.2000000476837,"responseEnd":613,"responseStart":612.0999999046326,"secureConnectionStart":548.2000000476837},{"duration":94.70000004768372,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":784,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":784,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":878.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":125.29999995231628,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":790.7999999523163,"connectEnd":790.7999999523163,"connectStart":790.7999999523163,"domainLookupEnd":790.7999999523163,"domainLookupStart":790.7999999523163,"fetchStart":790.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":881.3999998569489,"responseEnd":916.0999999046326,"responseStart":915.2999999523163,"secureConnectionStart":790.7999999523163}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":40,"responseStart":187,"responseEnd":197,"domLoading":191,"domInteractive":985,"domContentLoadedEventStart":985,"domContentLoadedEventEnd":1034,"domComplete":1369,"loadEventStart":1369,"loadEventEnd":1370,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":959.3999998569489},{"name":"bigPipe.sidebar-id.end","time":960.2000000476837},{"name":"bigPipe.activity-panel-pipe-id.start","time":960.2999999523163},{"name":"bigPipe.activity-panel-pipe-id.end","time":962.0999999046326},{"name":"activityTabFullyLoaded","time":1054.3999998569489}],"measures":[],"correlationId":"6d0333cf91eeb2","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":80,"dbReadsTimeInMs":15,"dbConnsTimeInMs":22,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
That's different. It's a timestamp behavior on NULL. If you'd try
you'd get "no default" error. If you'd set a default for your table, like
then NULL would still insert CURRENT_TIMESTAMP, while () would insert the default value.