0

In a C# Winforms app (NO Datagrids :-)), how would one write a query to check if a MySQL Database has been updated? I want to say something like: "Have you updated anything since [date the client latest got updates]?"

Thanks :)

2 Answers2

2

You can make use on the information_schema, like 

select max(update_time) 
from information_schema.tables 
where table_schema='YOUR_DATABASE'

mysql> desc information_schema.tables;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512) | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       |
| TABLE_NAME      | varchar(64)  | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)  | NO   |     |         |       |
| ENGINE          | varchar(64)  | YES  |     | NULL    |       |
| VERSION         | bigint(21)   | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)  | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21)   | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21)   | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21)   | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21)   | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21)   | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21)   | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21)   | YES  |     | NULL    |       |
| CREATE_TIME     | datetime     | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime     | YES  |     | NULL    |       |
| CHECK_TIME      | datetime     | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(64)  | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21)   | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255) | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(80)  | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+
ajreal
  • 46,720
  • 11
  • 89
  • 119
  • +1 That's neat, but what if you want to know on a per-table basis. Is that possible too? – Kim Burgaard Dec 18 '10 at 04:28
  • Thanks :) I have gone with @Kim's answer as it would be a better way to also minimize data usage on out rather limited internet plan. –  Dec 18 '10 at 04:32
0

You could use a version number scheme instead. Comparing a simple number might be faster than comparing dates. But if you know that your application is the only one that update the database, then you can build a notification service into your data access layer that would asynchronously notify subscribers on updates.

Kim Burgaard
  • 3,508
  • 18
  • 11
  • Thanks! This will be a better fit for me :) I will accept when it lets me ;) –  Dec 18 '10 at 04:31