okay, so it sounds like the way mysql uses timestamps is lame and need to work
around it.
unfortunately we can't use '0000-00-00 00:00:00' because java doesn't support
that date. it's pretty funny really ...
this ...
System.out.println(Timestamp.valueOf("0000-00-00 00:00:00"));
yields this ...
0002-11-30 00:00:00.0
so we are now left with a situation where we can't set the pubtime to NULL
because mysql doesn't allow that with Timestamps, and we can't set it to
'0000-00-00 00:00:00' because java doesn't like that date.
i'm not sure how best to work around this issue. we could simply alter the
table using Elias' suggestion (pubtime TIMESTAMP NULL DEFAULT NULL) and tell
our users it will only work on mysql 4.1.6 and above. it won't cause any
problems for users on older mysql dbs it is only a bit inconvenient.
ideas?
-- Allen
On Wed, 2005-10-19 at 13:28, Elias Torres wrote:
> On 10/19/05, Allen Gilliland <[EMAIL PROTECTED]> wrote:
> > okay ... i've tested this and it works on mysql 4.1.x, but not on mysql
> > 4.0.x. i don't think we should drop support for mysql 4.0.x, especially
> > because we use a 4.0.x db for one of our roller installs here at Sun.
> >
> > Elias,
> >
> > what exactly is broken about it? does Derby/DB2/others not have a column
> > type of DATETIME? or does the conversion now work from TIMESTAMP to
> > DATETIME?
> >
> > -- Allen
>
> Derby/DB2 do no have a column type DATETIME, only TIMESTAMP. :-(
>
> I've checked for Oracle/PostgreSQL and they don't have one either by a
> quick search. I love mySQL because of its installation ease and
> extremely easy php access to it on linux. But with the little db
> experience I have, I've come to find that it's the least non-standard
> database we are using in Roller. Therefore, if it was up to me, we
> should start with a more standards-compliant SQL and especialize it
> for the less non-standards ones. I did a quick check on the
> createdb-raw.sql and we don't have any other use for DATETIME except
> here. Is there any other way to work around this problem? Can we turn
> the NULL into some 0000000 date?
>
> Elias
>
> >
> >
> > On Mon, 2005-10-17 at 13:57, Elias Torres wrote:
> > > On 10/17/05, Allen Gilliland <[EMAIL PROTECTED]> wrote:
> > > > On Mon, 2005-10-17 at 13:00, Elias Torres wrote:
> > > > > I have checked this with DB2 and it works fine:
> > > > >
> > > > > [[[
> > > > > CREATE TABLE t1 ( ts1 TIMESTAMP DEFAULT NULL );
> > > > > ]]]
> > > >
> > > > Does this work as expected with Mysql 4.0.x though?
> > > >
> > > > what about this paragraph from the link you gave ...
> > > >
> > > > "If a DEFAULT value is specified for the first TIMESTAMP column in a
> > > > table, it is not ignored. The default can be CURRENT_TIMESTAMP or a
> > > > constant date and time value.
> > > >
> > >
> > > We could make pubtime be the second column. :-)
> > >
> > > > DEFAULT NULL is the same as DEFAULT CURRENT_TIMESTAMP for the first
> > > > TIMESTAMP column. For any other TIMESTAMP column, DEFAULT NULL is
> > > > treated as DEFAULT 0."
> > > >
> > > > I suppose we need to test this a bit.
> > >
> > > Let's please do that, because I don't think that there should be many
> > > requirements that would make us drop support for any of the databases
> > > we currently support.
> > >
> > > >
> > > > -- Allen
> > > >
> > > >
> > > > > Regards,
> > > > >
> > > > > Elias
> > > > >
> > > > > [1] http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html
> > > > > [2] http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html
> > > > >
> > > > >
> > > > >
> > > > > On 10/17/05, Allen Gilliland <[EMAIL PROTECTED]> wrote:
> > > > > > On Sun, 2005-10-16 at 17:12, Elias Torres wrote:
> > > > > > > I just noticed that change in the schema (date field in
> > > > > > > weblogentry)
> > > > > > > too and it breaks DB2 since the field type is not supported. I
> > > > > > > checked
> > > > > > > documentation and datetime is also not supported on Derby either.
> > > > > > > Anyways, I don't know the differences between datetime and
> > > > > > > timestamp.
> > > > > > > Why does pubtime need to be a datetime as opposed to a timestamp?
> > > > > > > Is
> > > > > > > this something that can be done via a customizable datatype as in
> > > > > > > TEXT? Just a thought.
> > > > > >
> > > > > > I put in the change from Timestamp to Datetime because in Mysql if
> > > > > > you insert a NULL value into a Timestamp column then Mysql will
> > > > > > automatically set it to the current date/time. The modification I
> > > > > > made to the way pubtime works requires that we be able to save
> > > > > > draft entries with a NULL pubtime.
> > > > > >
> > > > > > In Mysql the Timestamp and Datetime are the exact same thing except
> > > > > > that a Datetime will never be set automatically.
> > > > > >
> > > > > > -- Allen
> > > > > >
> > > > > >
> > > > > > >
> > > > > > > Elias
> > > > > > >
> > > > > > > On 10/16/05, Dave Johnson <[EMAIL PROTECTED]> wrote:
> > > > > > > >
> > > > > > > > Here's my theory. We changed the weblog entry pubtime into a
> > > > > > > > date field
> > > > > > > > rather than a timestamp and I bet we didn't test against
> > > > > > > > PostgreSQL (I
> > > > > > > > know I didn't).
> > > > > > > >
> > > > > > > > According to the docs below, we need to do the temp table trick
> > > > > > > > if we
> > > > > > > > want to change a column type in PostgreSQL:
> > > > > > > >
> > > > > > > > http://techdocs.postgresql.org/techdocs/updatingcolumns.php
> > > > > > > >
> > > > > > > > Then, we may have data type issues. I hope PostgreSQL has a
> > > > > > > > compatible
> > > > > > > > DATETIME field.
> > > > > > > >
> > > > > > > > I'm out of the office for the next couple of days, so I won't
> > > > > > > > have a
> > > > > > > > chance to look into this.
> > > > > > > >
> > > > > > > > - Dave
> > > > > > > >
> > > > > > > >
> > > > > > > > On Oct 16, 2005, at 12:19 AM, Henri Yandell wrote:
> > > > > > > >
> > > > > > > > > On 10/12/05, Dave Johnson <[EMAIL PROTECTED]> wrote:
> > > > > > > > >> Is this complete? Can we close the JIRA issues for DB2 and
> > > > > > > > >> Derby
> > > > > > > > >> support and if not, what remains to be done?
> > > > > > > > >>
> > > > > > > > >> And BTW, the 2.0 bug list is getting pretty slim.
> > > > > > > > >
> > > > > > > > > Heh, there I was thinking it had a long time to go; which
> > > > > > > > > explained
> > > > > > > > > the problems I was seeing where posting an entry to a new blog
> > > > > > > > > buggered it up big time.
> > > > > > > > >
> > > > > > > > > http://opensource2.atlassian.com/projects/roller/browse/ROL-844
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
