|
Home > Archive > MySQL ODBC Connector > April 2006 > TIMESTAMP field not automatically updating last_updated field
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
TIMESTAMP field not automatically updating last_updated field
|
|
| Ferindo Middleton Jr 2006-03-30, 8:26 pm |
| I think I've seen this complaint posted before but I ignored but now I
realize that in some of my db tables' last_updated field the value is
automatically updating on UPDATEs to records while in other tables the
last_updated fields for some strange reason aren't automatically updating.
I'll usually use the following line in my table declarations:
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
In some tables it automatically updates on subsequent updates to the
table and in others it will not. The purpose here is to have the
last_updated field automatically append to the current timestamp... the
application on the front end doesn't specify the time to MySQL but
rather expects that it's always going to be UPDATEd to the current time
slot.
What am I doing wrong what command should I issue to my tables to
correct it? Thanks
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Scott Haneda 2006-03-30, 8:26 pm |
| > I think I've seen this complaint posted before but I ignored but now I
> realize that in some of my db tables' last_updated field the value is
> automatically updating on UPDATEs to records while in other tables the
> last_updated fields for some strange reason aren't automatically updating.
>
> I'll usually use the following line in my table declarations:
>
> last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
>
> In some tables it automatically updates on subsequent updates to the
> table and in others it will not. The purpose here is to have the
> last_updated field automatically append to the current timestamp... the
> application on the front end doesn't specify the time to MySQL but
> rather expects that it's always going to be UPDATEd to the current time
> slot.
>
> What am I doing wrong what command should I issue to my tables to
> correct it? Thanks
What veriosn of mysql, timestamp handling has changed from one version to
the next?
Perhaps you have others in your table, I think only the first is updated, at
least, in pre 4.1 days, after that, check the docs for the correct behavior.
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| jonathan 2006-03-30, 8:26 pm |
| are you having two timestamp fields in a table (ie a created and a
last_updated)?
-j
On Mar 30, 2006, at 5:17 PM, Ferindo Middleton Jr wrote:
> I think I've seen this complaint posted before but I ignored but
> now I realize that in some of my db tables' last_updated field the
> value is automatically updating on UPDATEs to records while in
> other tables the last_updated fields for some strange reason aren't
> automatically updating.
>
> I'll usually use the following line in my table declarations:
>
> last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
>
> In some tables it automatically updates on subsequent updates to
> the table and in others it will not. The purpose here is to have
> the last_updated field automatically append to the current
> timestamp... the application on the front end doesn't specify the
> time to MySQL but rather expects that it's always going to be
> UPDATEd to the current time slot.
>
> What am I doing wrong what command should I issue to my tables to
> correct it? Thanks
>
> Ferindo
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=news_php@arclo
cal.com
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Ferindo Middleton Jr 2006-03-30, 8:26 pm |
| jonathan wrote:
> are you having two timestamp fields in a table (ie a created and a
> last_updated)?
>
> -j
> On Mar 30, 2006, at 5:17 PM, Ferindo Middleton Jr wrote:
>
>
>
No just the one timestamp field (last_updated) which I expect to be
given a timestamp on the initial INSERT and then continue to be
automatically updated to the current time on subsequent UPDATEs to any
given row...
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| Are the other fields in the update statement actually changing the
data? I don't know for sure, but if the data on disk is the same as
the update statement, mysql won't actually update the record, and
therefore might not update the last_updated field also. Just a
thought.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
| Hank wrote:
> Are the other fields in the update statement actually changing the
> data? I don't know for sure, but if the data on disk is the same as
> the update statement, mysql won't actually update the record, and
> therefore might not update the last_updated field also. Just a
> thought.
It's true. No Update = No change of timestamp!
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Ferindo Middleton Jr 2006-03-31, 8:25 pm |
| Hank wrote:
> Are the other fields in the update statement actually changing the
> data? I don't know for sure, but if the data on disk is the same as
> the update statement, mysql won't actually update the record, and
> therefore might not update the last_updated field also. Just a
> thought.
>
>
Yes, I understand that one concept. I have seen it before.... If you do
an update on a record but the actually values that you are passing in
the statement are the exact values as were there before, no update to
the timestamp field is made because none of the records values actually
changed....
But no, that is not my situation. I've tested it.... and I am actually
changing the values in the table (of course not specifying a new value
for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update.
What disturbes me is that it works fine in one particular table but
all the others it works.
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Ferindo Middleton Jr 2006-03-31, 8:25 pm |
| Ferindo Middleton Jr wrote:
> Hank wrote:
> Yes, I understand that one concept. I have seen it before.... If you
> do an update on a record but the actually values that you are passing
> in the statement are the exact values as were there before, no update
> to the timestamp field is made because none of the records values
> actually changed....
>
> But no, that is not my situation. I've tested it.... and I am actually
> changing the values in the table (of course not specifying a new value
> for the TIMESTAMP field) but still the TIMESTAMP field doesn't
> auto-update.
>
> What disturbes me is that it works fine in one particular table but
> all the others it works.
>
> Ferindo
>
I'm running 5.0.19-nt. I haven't had a chance to test it but should it
make any difference if I say:
last_updated TIMESTAMP,
than if I say all this:
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
I think this may be the difference in why some tables are auto
incrementing and others aren't.
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Jonathan Mangin 2006-04-02, 11:24 am |
|
----- Original Message -----
From: "Ferindo Middleton Jr" <fmiddleton@verizon.net>
To: "Ferindo Middleton Jr" <fmiddleton@verizon.net>
Cc: "Hank" <heskin@gmail.com>; <mysql@lists.mysql.com>
Sent: Friday, March 31, 2006 7:30 PM
Subject: Re: TIMESTAMP field not automatically updating last_updated field
> Ferindo Middleton Jr wrote:
> I'm running 5.0.19-nt. I haven't had a chance to test it but should it
> make any difference if I say:
>
> last_updated TIMESTAMP,
>
> than if I say all this:
>
> last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
>
> I think this may be the difference in why some tables are auto
> incrementing and others aren't.
>
> Ferindo
>
I'm using 4.1.11 on Solaris. I've explicitly created a table
with one timestamp field and
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
I haven't tried an explicit update (useless to me) but an
insert...on duplicate key update does not update the timestamp
field.
Have you, Ferindo, had any success yet? Does anyone have any
further thoughts? (I just realized I need this also)
--Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|