Last modified date for DB table Options

[Christian J]

I'd like to log when a DB table was last modified. Apparently InnoDB tables return null for the "Update time" column of TABLE STATUS, so some other solution is needed. A couple of ideas:

-Create a second table. This would contain a single row with the last modified date of the first table. Sounds like the simplest approach to me.

-Use a single table, and give it a "last modified" column. This way each row would get its own date, but to find the most recent one I must sort all the rows.

Maybe it's good to have a separate date for each individual row, but then why not go one step further and keep a last modified date for all fields in the row while you're at it? I guess the latter requires a second table.

If a row is deleted, doesn't its "last modified" field disappear too? Will I have to give the other fields in a row something like a null value instead of deleting the whole row in order to retain its deletion date?

[Brian Chandler]

I'd like to log when a DB table was last modified. Apparently InnoDB tables return null for the "Update time" column of TABLE STATUS, so some other solution is needed. A couple of ideas:

-Create a second table. This would contain a single row with the last modified date of the first table. Sounds like the simplest approach to me.

Well, yes. If you want one piece of information for the table, then you need a new table entry. I have got round to doing this yet, but I guess the best thing really is to have one 2-column table in your database that just holds the various "constants" you need to keep. Column 1 is the constant name, column 2 its value.

-Use a single table, and give it a "last modified" column. This way each row would get its own date, but to find the most recent one I must sort all the rows.

Make this column an index, meaning "pre-sorted" essentially. But this doesn't seem to be the right way to do it anyway.

Maybe it's good to have a separate date for each individual row, but then why not go one step further and keep a last modified date for all fields in the row while you're at it? I guess the latter requires a second table.

Don't see why: twice the number of columns, with NAME and NAME_LASTMOD for each "NAME". I don't think this is a very workable idea, though, because it means deciding exactly what "modified" means. (Sometimes you might set a value to what it is already, for example.) Anyway, unless the DB system provides this function I wouldn't think it worth writing.

If a row is deleted, doesn't its "last modified" field disappear too? Will I have to give the other fields in a row something like a null value instead of deleting the whole row in order to retain its deletion date?

If you are doing this just to remember the last modified date of the *table*, then you are not representing the information relating to the *table* correctly, and it is no wonder that there are bits that don't work.

Perhaps if you could be more explicit about the application it would be easier to see what the question really is.

[Christian J]

-Use a single table, and give it a "last modified" column.

...

If a row is deleted, doesn't its "last modified" field disappear too? Will I have to give the other fields in a row something like a null value instead of deleting the whole row in order to retain its deletion date?

If you are doing this just to remember the last modified date of the *table*, then you are not representing the information relating to the *table* correctly, and it is no wonder that there are bits that don't work.

This applied to the single table idea, where each row got its own date and you'd sort by the date column to get the table's most recent modification date. Maybe I could set the date column as primary key, since the latter apparently doesn't go away even when you delete a row...

Perhaps if you could be more explicit about the application it would be easier to see what the question really is.

Just trying to find out if there are any commonly accepted methods...

In my current DB experiment (a control panel) I'm trying a second table which is given a new row every time the first table is modified. This way the second table will also work as a log. Seems to work so far.

[Brian Chandler]

-Use a single table, and give it a "last modified" column.

...

If a row is deleted, doesn't its "last modified" field disappear too? Will I have to give the other fields in a row something like a null value instead of deleting the whole row in order to retain its deletion date?

If you are doing this just to remember the last modified date of the *table*, then you are not representing the information relating to the *table* correctly, and it is no wonder that there are bits that don't work.

This applied to the single table idea, where each row got its own date and you'd sort by the date column to get the table's most recent modification date.

But you won't get the table's most recent modification date. You will (obviously) get the last modified row in the table. Which is why this won't work to remember the last modification, if this includes "deletion".

Maybe I could set the date column as primary key, since the latter apparently doesn't go away even when you delete a row...

You must surely be misreading something?! You think that if you request the row with the latest primary key value, the DB returns a row that isn't there??

Obviously, another way to do it is to have a status field, with values including "deleted" -- then you can get a row in the db marked as "deleted", (but not "actually" deleted).

[Christian J]

But you won't get the table's most recent modification date. You will (obviously) get the last modified row in the table. Which is why this won't work to remember the last modification, if this includes "deletion".

Agreed.

Maybe I could set the date column as primary key, since the latter apparently doesn't go away even when you delete a row...

You must surely be misreading something?! You think that if you request the row with the latest primary key value, the DB returns a row that isn't there??

I was thinking the DB must store the ID values in an index somewhere (to make sure new rows are given proper ID values), but maybe that index is not accessible. Anyway it might be a silly idea, hence the smiley:

[Brian Chandler]

I was thinking the DB must store the ID values in an index somewhere (to make sure new rows are given proper ID values), but maybe that index is not accessible. Anyway it might be a silly idea...

AFAIK, there is nothing special about a primary index. It must be unique, so if you try to set it to a value that already exists there will be an error. You don't need a _separate_ list: the values in the DB _are_ the list.

Again, AFAIK, an autoindex column simply increments from the largest value currently present, or possibly the last value assigned even if it has been deleted. But anyway, it is only necessary for the DB to store a single value to be next assigned.

[Christian J]

possibly the last value assigned even if it has been deleted.

Which means it's kept somewhere in the DB. Seems you can find it with

CODE

$query=mysql_query("SELECT MAX(primary_key_column) FROM table");
$highest_value=mysql_fetch_array($query);
echo $highest_value[0];

So if the primary key values are timestamps the above should return the last modified date.
EDIT: but this means editing a row also means giving its primary key field a new value (the timestamp of the latest edit), which may not be a good idea.

This post has been edited by Christian J: Jul 23 2010, 05:17 PM

[Brian Chandler]

possibly the last value assigned even if it has been deleted.

Which means it's kept somewhere in the DB. Seems you can find it with

CODE

$query=mysql_query("SELECT MAX(primary_key_column) FROM table");
$highest_value=mysql_fetch_array($query);
echo $highest_value[0];

So if the primary key values are timestamps the above should return the last modified date.

No, it will (obviously, as usual) return the highest value of primary_key_column from among the (actual, existing, real) rows in the db table. If there was once a higher value for primary_key_column, but it has been deleted, then it is no longer there, and will not be returned.

You seem to have this idea that somehow the DB "remembers" everything that every happened. If this were the case, the disk space occupied by the DB would never, ever go down, because nothing could ever actually be deleted. But it does go down if you delete lots of rows. Therefore your idea is wrong.

EDIT: but this means editing a row also means giving its primary key field a new value (the timestamp of the latest edit), which may not be a good idea.

Well, it's a good idea if the primary key field is latest_edit_time. If the primary key field is something else, it's a very bad idea.

To go back to your original question: if you want to save the timestamp of the last operation on this table, including deletes, then you need to save it somewhere else. We do seem to be going round in circles here...

[Christian J]

No, it will (obviously, as usual) return the highest value of primary_key_column from among the (actual, existing, real) rows in the db table.

You're right.

If there was once a higher value for primary_key_column, but it has been deleted, then it is no longer there, and will not be returned.

That's the crucial part: if you delete the row with the highest value, and then create a new row, the new one will get a value higher than the (previously highest) deleted one. So apparently the "all time high" value is kept somewhere.

We do seem to be going round in circles here...

Not at all, I'm learning things.

[Brian Chandler]

If there was once a higher value for primary_key_column, but it has been deleted, then it is no longer there, and will not be returned.

That's the crucial part: if you delete the row with the highest value, and then create a new row, the new one will get a value higher than the (previously highest) deleted one. So apparently the "all time high" value is kept somewhere.

I think you are confusing primary keys with autoindex keys. When you insert a record with a primary key, you have to set the primary key, and you have to use a value that does not already exist. The DB does not supply any value automatically.

An autoindex key, on the other hand: you don't (can't?) suppy a value, and the next one is allocated. I seems then that the db keeps the current index value in the housekeeping info.