#3102 messages_web problems

[shane]

we're seeing weird behavior with messages_web with it's two
timestamp fields. from irc:

[15:18] <tf23> can someone who's running section-topics do a
describe on table message_web and tell me what column date's
default is? this is what i get on a stock-install: | date |
timestamp(14) | YES | | 00000000000000 | |
[15:19] <tf23> the "00000000000000" is what I'm concerned
about
[15:20] <jamie_> on slashdot its default is NULL
[15:20] <jamie_> hm, and on my test sites, it's 00000
[15:20] <tf23> i just grabbed the drop/create straight out of
the plugin and threw that at the db, it comes back 000000
[15:21] <tf23> i wonder, is it because the column's name is
'date'?
[15:23] <tf23> it caught my attention, because if you do a
mysqldump of your test site's data, and try and reload the data, it
won't import it because of that weird default value.
[15:24] <jamie_> huh
[15:24] <jamie_> what version mysql?
[15:25] <tf23> mysql Ver 12.22 Distrib 4.0.17, for apple-
darwin7.3.0 (powerpc)
[15:27] <tf23> mysql Ver 12.22 Distrib 4.0.20, for pc-linux
(i686) didn't complain one bit
[15:31] <jamie_> huh
[15:31] <tf23> here's the error: ERROR 1067 at line 1902:
Invalid default value for 'date'
[15:31] <tf23> that's cuz the CREATE table from the dump has
this now:
[15:31] <tf23> date timestamp(14) NOT NULL default
'00000000000000',
[15:31] <tf23> weird!
[15:34] <tf23> i think i see the problem
[15:34] <tf23> the date field should be either date or
datetime.
[15:34] <tf23> there shouldn't be two timestamp fields in a
single table.
[15:36] <jamie_> pudge you want to take a look at this? I don't
know about message_wbe
[15:44] <pty> odd, everything looks ok in slash/plugins/Messages/
mysql_schema
[15:55] <tf23> http://dev.mysql.com/doc/mysql/en/
TIMESTAMP_pre-4.1.html
[15:55] <tf23> 2 timestamp columns looks to be ok, it
supposedly will only update the first column automatically.

mysql> describe message_web;
+---------+-----------------------+------+-----+----------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+----------------+-------+
| id | int(11) | | PRI | 0 | |
| user | mediumint(8) unsigned | | MUL | 0 | |
| fuser | mediumint(8) unsigned | | MUL | 0 | |
| code | int(11) | | | -1 | |
| updated | timestamp(14) | YES | | NULL | |
| readed | tinyint(1) | | | 0 | |
| date | timestamp(14) | YES | | 00000000000000 |
|
+---------+-----------------------+------+-----+----------------+-------+
7 rows in set (0.00 sec)

[Chris Nandor]

Logged In: YES
user_id=3660

So what is the actual problem, then? Not being able to reimport?

[Chris Nandor]

Logged In: YES
user_id=3660

tf23, you there? :)

[shane]

Logged In: YES
user_id=52587

Yeah, sorry, I missed your first ping.

The problem is that the column default values are different *and* it
makes it impossible to import your exported data.

[Chris Nandor]

Logged In: YES
user_id=3660

Do you have a solution? I am not sure what can be done.

[Chris Nandor]

Logged In: YES
user_id=3660

Ping!

[shane]

Logged In: YES
user_id=52587

yes, that's the major thing. you can't reimport the data from a
mysqldump.

[Chris Nandor]

Logged In: YES
user_id=3660

But what is the solution?

[shane]

Logged In: YES
user_id=52587

Yes, that's the biggest problem with it. If you dump your data, and try to
import the data, you can't and you're in trouble.

Secondly, there is the question of why are there two timestamp fields in
table at all?

[Chris Nandor]

Logged In: YES
user_id=3660

There are two timestamps because one is the date it is saved, one is the
date it is updated.

And I see the problem I think, but i have no solution.