+1 On Mon, Feb 16, 2009 at 8:47 PM, Monty Taylor <[email protected]> wrote:
> I agree, since it makes sense, but it _may_ make porting some MySQL apps > harder. not really, because once the table is defined, a SHOW CREATE TABLE gets rid of the magic. Porting an existing schema isn't a problem; it'd be porting "Custom DDL" that'd have to have a caveat. And since I believe UNSIGNED is already out, there's already some tweaking to do there. -Sheeri > > > Mark Atwood wrote: > > +1 > > > > 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<https://launchpad.net/%7Edrizzle-discuss> > >> Post to : [email protected] > >> Unsubscribe : > >> https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> > >> More help : https://help.launchpad.net/ListHelp > > > > > _______________________________________________ > Mailing list: > https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> > Post to : [email protected] > Unsubscribe : > https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> > More help : https://help.launchpad.net/ListHelp > -- - Sheeri K. Cabral http://tinyurl.com/mysqlbook will take you to the Amazon.com page for my upcoming book, "MySQL Administrator's Bible".
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
