asked on
check the last time the table schema change.
Dear all,
Right now need to check whick table has schema change since the time it create and the last time it change.
should I just use this:
and use the UPDATE_TIME field ?
Right now need to check whick table has schema change since the time it create and the last time it change.
should I just use this:
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='database name' and table_type<> 'view' ;
and use the UPDATE_TIME field ?
MySQL Server
Last Comment
marrowyung
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Yes, NULL means only creation.
No, schema contains only the structure of the table and not the contents. UPDATE_TIME should be the time of the latest DDL command executed on that particular object.
No, schema contains only the structure of the table and not the contents. UPDATE_TIME should be the time of the latest DDL command executed on that particular object.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
any other way to detect or show out which tables has schema change but not data change ?
Yes, it works for me in mysql 5.5. I gave it a try and found that for some reason update_time is always NULL for me, even after DDL commands, while create_time is updated when I change the table structure.
I suspect it depends on engine: we use InnoDB and update_time is constant NULL. Same for MEMORY, CSV and PERFORMANCE_SCHEMA engines. However, MyISAM engine always has an update time, so it seems. Will be back soon with some more tests.
I suspect it depends on engine: we use InnoDB and update_time is constant NULL. Same for MEMORY, CSV and PERFORMANCE_SCHEMA engines. However, MyISAM engine always has an update time, so it seems. Will be back soon with some more tests.
OK I dug a bit deeper. Update_time is actually the modification time of the data file which means structure *or* data.
Some changes in table structure modify the create_time field (sic!) *but* even some such ddl commands don't change neither create_time nor update_time.
So it's more like create_time you need and even it is not reliable.
The only thing I can come up with as alternative solution is to use "show create table" in two different points in time and you'll be able to tell (most of) the changes that happened between those two timestamps.
Some changes in table structure modify the create_time field (sic!) *but* even some such ddl commands don't change neither create_time nor update_time.
So it's more like create_time you need and even it is not reliable.
The only thing I can come up with as alternative solution is to use "show create table" in two different points in time and you'll be able to tell (most of) the changes that happened between those two timestamps.
ASKER
show create table don't show the time, it just provide you the statement to create the table.
yes, it can be used only to compare an "old" version to the "current" version. It makes sense if you do it in an automated and well defined way, e.g. every night you compare the current state to last night's state. But I'm afraid that's the most MySQL (at least 5.5) can offer you. Sad, isn't it.
ASKER
one thing is, once show create table, how can we compare 2x very long string ? this is the pint then..
ASKER
I just think the other issue, like once we found out the diff. how can we tell what field has been changed? by comparing string, we can only found out the length diff, but not which field it is ?
I'd use the "diff" command. That tells you all the info you need. If using windows, consider having a look at GNU tools for windows (if need a command line tool) or use any file compare tool, e.g. Total Commander.
You may also use a versioning system for keeping track of changes, like SVN or CVS.
You may also use a versioning system for keeping track of changes, like SVN or CVS.
ASKER
"I'd use the "diff" command."
no! it is my short typing, I don't want to type the whole word..
"If using windows, consider having a look at GNU tools for windows (if need a command line tool) or use any file compare tool, e.g. Total Commander.
You may also use a versioning system for keeping track of changes, like SVN or CVS. "
no, I need to build an automated method, not like that!!
what if, for any kind of schema cahnge, I tell the developer to include all chance in the delivery notes and I can just cahnge it manually ?
much easlier, right?
no! it is my short typing, I don't want to type the whole word..
"If using windows, consider having a look at GNU tools for windows (if need a command line tool) or use any file compare tool, e.g. Total Commander.
You may also use a versioning system for keeping track of changes, like SVN or CVS. "
no, I need to build an automated method, not like that!!
what if, for any kind of schema cahnge, I tell the developer to include all chance in the delivery notes and I can just cahnge it manually ?
much easlier, right?
ASKER
it seems it works in the other way around.
so inserting new record do not affect update_time ?