Re: [phpesp-dev] Proposed changes.

[Matthew G. <gr...@mu...>]

On Sun, 2004-02-08 at 10:06, James E. Flemer wrote: 
> Matthew Gregg wrote:
> > I've fielded several problems lately on #phpESP relating to the
> > mysql_populate.sql script.  It appears it is failing on mySQL vs 4 and
> > above.
> > The fix looks to be simply changing all:
> > NOT NULL DEFAULT '',
> > to:
> > NOT NULL,
> > 
> > See bug #: 890830
> > 
> > I want to commit these changes to CVS, any commments?
> 
> That sounds good.  The only thing I'm worried about is changing the 
> behavior.  I think most "NOT NULL DEFAULT ''" clauses are used on 
> timestamps, and by doing so causes MySQL3.x to set the field to the time 
> the row was inserted.  I think it should be pretty easy to retain this 
> behavior ... but I want to be sure it will DTRT on both MySQL 3 & 4.
You are correct the problem is with the "changed" column of type
TIMESTAMP.  Other TIMESTAMP columns did not specify the "DEFAULT ''".

Documentation for mysql 3 and 4(when not running in MAXDB mode) appear
to have identical behavior:
--
Automatic updating of the first TIMESTAMP column occurs under any of the
following conditions:

The column is not specified explicitly in an INSERT or LOAD DATA INFILE
statement.

The column is not specified explicitly in an UPDATE statement and some
other column changes value. (Note that an UPDATE that sets a column to
the value it already has will not cause the TIMESTAMP column to be
updated, because if you set a column to its current value, MySQL ignores
the update for efficiency.)

You explicitly set the TIMESTAMP column to NULL.

If mysql 4 is running in MAXDB mode no auto updates to timestamp field
occur.
--

It appears mysql 4 just disallows the "DEFAULT ''", clause on TIMESTAMP
fields.  A grep of the ESP sources, reveals only two explicit references
to the "changed" column in "admdesigner.inc" and "admrespondent.inc" and
they specify "changed=now()".  So.. based on this and a mysql dump of
our ESP installation the corrected SQL should be:
changed TIMESTAMP(14) DEFAULT NULL,

instead of

changed TIMESTAMP(14) NOT NULL DEFAULT '',


Does anyone have access to mysql 3.x to test this?

...snip...



-- 
mcg
--
The IT Lab (http://www.itlab.musc.edu)