Brian Aker wrote:
Hi!
This is a bit like SERIAL (which I happen to actually really like)... my
only real issue with TIMESTAMP is that we don't update all of the
TIMESTAMPs... AKA magic behavior on first is bad, but saying "this is
this" for all timestamps I think is useful shorthand (and without it I
suspect it will break a lot of people).
Nothing wrong with a shorthand or alias like SERIAL, but this is
different. This is an actual different column type than any other
column type, not just an alias like SERIAL is (an alias for BIGINT
UNSIGNED NOT NULL AUTOINCREMENT -- which isn't even UNSIGNED in
Drizzle...). But TIMESTAMP, declared with nothing else, is not an alias
for a different column type. It's a different behaviour of an existing
column type, an an implicit/magic one at that.
Moreover, the dangerous part is that currently, TIMESTAMP is *the only
column type* which defaults to NOT NULL. All other column types default
to NULL. This is extremely confusing in my opinion. I prefer simple
things to confusing things :)
What I think would be better is to do this:
1) Fix behavior for all declarations of TIMESTAMP.
That is precisely what my proposal was...fix the declaration of
TIMESTAMP in the following way:
* All declarations are explicit. A timestamp column declared with no
DEFAULT or ON UPDATE clause is a TIMESTAMP NULL, not TIMESTAMP NOT NULL
DEFAULT NOW() ON UPDATE NOW().
* TIMESTAMP is NULL by default, which would match the rest of the column
types
2) Move the code that performs TIMESTAMP, out of the handler and up to
the layer above so that engine writers don't have to deal with this in
their code.
:) Agreed, but that's another "chunk" :)
-jay
Cheers,
-Brian
On Feb 16, 2009, at 4:10 PM, Jay Pipes wrote:
All,
Has anyone ever been completely confused by MySQL's handling of
TIMESTAMP columns? I know I have.
For a primer, read here:
http://dev.mysql.com/doc/refman/6.0/en/timestamp.html
In an effort NOT to go down a rathole but to refactor the server's
handling of TIMESTAMP columns to be less "magic" and more explicit,
here is my proposed change in behaviour. Please vote!
Currently, there is some "magic" in the way one can define TIMESTAMP
columns in MySQL. The magic occurs for the *first* TIMESTAMP column
that is defined without any DEFAULT or ON UPDATE clause, like so:
CREATE TABLE t1 (
ts1 TIMESTAMP
);
What the above does silently is convert that timestamp column's
definition to this:
CREATE TABLE t1 (
ts1 TIMESTAMP DEFAULT NOW() ON UPDATE NOW()
);
The reason for this is for backwards compatibility with Unireg,
MyISAM's predecessor. The above magic, implicit definition is
implemented as a hack in the server (namely, in prepare_create_field()
and in the parser itself. It's a hack that I would prefer to get rid
of (and I know Stewart would as well, since this would allow us to get
rid of the unireg_check argument for Field creation...)
If you've ever run into the following behaviour, you've probably
scratched your head at the error message. The reason is because many
users try to execute something like the following:
mysql> CREATE TABLE t1 (
-> some_time TIMESTAMP
-> , update_time TIMESTAMP DEFAULT NOW() ON UPDATE NOW()
-> );
ERROR 1293 (HY000): Incorrect table definition; there can be only one
TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
Looking at the error, many users think "but I *did* specify only a
single column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause!!"
So, in concrete terms, here is my proposal to change the behaviour of
TIMESTAMP columns:
a) If a user specifies TIMESTAMP with no DEFAULT or ON UPDATE clause,
the TIMESTAMP column will *always* be defined as TIMESTAMP NULL, with
no magic happening behind the scenes.
b) Users must always explicitly specify what they wish the TIMESTAMP
column's behaviour on insert and update. This means:
b1) To specify a TIMESTAMP column which sets itself to NOW() when a
record is inserted, but does NOT set itself to some value on update
automatically, do:
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT NOW()
);
b2) To specify a TIMESTAMP column which sets itself to NOW() when a
record is updated, but does NOT set itself to some value on insert, do:
CREATE TABLE t1 (
ts TIMESTAMP NULL ON UPDATE NOW()
);
b3) To specify a TIMESTAMP column which sets itself to NOW() when a
record is inserted, and ALSO sets itself to NOW() on update
automatically, do:
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT NOW() ON UPDATE NOW()
);
b4) The final variation on this would be:
CREATE TABLE t1 (
ts TIMESTAMP NOT NULL ON UPDATE NOW()
);
This would be for timestamp columns that, as a user, you wish to
manually set to a value on INSERT (and *be forced to set this value*)
and on update, have the column set itself to NOW()
As far as I can tell, this proposal would be inline with 99% of
MySQL's timestamp behaviour with the sole exception of no magic on the
first TIMESTAMP column with no DEFAULT and ON UPDATE clause.
Votes please!
Thanks,
Jay
p.s. Stewart: if this proposal goes through, we can get rid of
unireg_check and the associated stuff in unireg.cc:pack_screens() and
pack_header() this week...
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
--
_______________________________________________________
Brian "Krow" Aker, brian at tangent.org
Seattle, Washington
http://krow.net/ <-- Me
http://tangent.org/ <-- Software
_______________________________________________________
You can't grep a dead tree.
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
