Solved

check the last time the table schema change.

Posted on 2014-01-05
14
880 Views
Last Modified: 2014-01-11
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:

        SELECT *	
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA='database name' and table_type<> 'view' ;

Open in new window


and use the UPDATE_TIME field ?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 8

Assisted Solution

by:Surrano
Surrano earned 500 total points
ID: 39758641
Yes but take into account that update_time may be null e.g. for freshly created tables.

select table_name, create_time, update_time 
from information_schema.tables 
where table_schema='dbname' and coalesce(update_time,create_time)>'2014-01-06';

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39758650
so null ONLY means for FIRST time created talbe and no schema change after that?

so inserting new record do not affect update_time ?
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39758657
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.
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 1

Accepted Solution

by:
marrowyung earned 0 total points
ID: 39758688
do you tested it on MySQL 5.5? I tested it and it seems that even record updates, this update_time also will change.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39758706
any other way to detect or show out which tables has schema change but not data change ?
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39758754
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.
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39758768
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39758875
show create table don't show the time, it just provide you the statement to create the table.
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39758958
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39759115
one thing is, once show create table, how can we compare 2x very long string ? this is the pint then..
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39759120
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 ?
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39759207
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39761126
"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?
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39773141
it seems it works in the other way around.
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
running code or pseudo code of table structure 5 41
MS Access - need to reduce row size 25 76
FrontEnd tools to create web database application 7 115
SubQuery link 4 56
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question