How to Detect MySql table update/difference

Hi, I'm a novel developer of MySql and now I am trying to create a mysql qu=
ery to detect table updates. I query a database table every X seconds,=
and i want to get only the different rows in the table. The result that I =
want to have is simply=0ATABLE (t =3D now) - TABLE (t =3D X second ago)=0A=
=0AEvery time that i query the database I store the table situation in anot=
her table, called TABLE_TEMP, so the operation of difference detection is =
=0ATABLE_DIFERENCE =3D TABLE - TABLE_TEMP. How can I do this query? T=
he problem that I have is that I don't know the structure of the table and =
I want to create a program with can be used for all types of data and table=
s. Please help me. Best regards =0A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: How to Detect MySql table update/difference

SSBoYXZlIGdvb2QgbmV3cyBhbmQgYmFkIG5ld3MgZm9yIHlvdSB3aGVuIGl0 IGNvbWVzIHRvIE15
U1FMIDUueC4NCg0KR29vZCBOZXdzIGlmIHlvdSBhcmUgY291bnRpbmcgYWdh aW5zdCBNeUlTQU0N
CkJhZCBOZXdzIGlmIHlvdSBhcmUgY291bnRpbmcgYWdhaW5zdCBJbm5vREIN Cg0KR29vZCBOZXdz
DQotLS0tLS0tLS0NCkZvciBNeUlTQU0NCkp1c3QgdXNlIGVpdGhlcg0KU0VM RUNUIHRhYmxlX3Jv
d3MgRlJPTSBpbmZvcm1hdGlvbl9zY2hlbWEudGFibGVzIFdIRVJFIHRhYmxl X3NjaGVtYT0nPGdp
dmVuIGRiIG5hbWU+JyBhbmQgdGFibGVfbmFtZT0nPGdpdmVuIHRhYmxlIG5h bWU+JzsNCk9yDQpT
RUxFQ1QgQ09VTlQoMSkgRlJPTSA8ZGItbmFtZT4uPHRibC1uYW1lPjsgKFRo aXMgd2lsbCB3b3Jr
IGluIE15U1FMIDQueCBhcyB3ZWxsKQ0KDQpCYWQgTmV3cw0KLS0tLS0tLS0N CkZvciBJbm5vREIN
ClNFTEVDVCBDT1VOVCgxKSBGUk9NIDxkYi1uYW1lPi48dGJsLW5hbWU+LCBl dmVuIHRob3VnaCBp
dCBjYW4gcnVuIGZhc3RlciB0aGFuIFNFTEVDVCBDT1VOVCgqKSwNCndpbGwg c3RpbGwgY291bnQg
ZXZlcnkgcm93IGluIHRoZSB0YWJsZSBhbnl3YXkuDQoNCklmIHlvdSBhcmUg dXNpbmcgSW5ub0RC
LCB5b3UgYXJlIG9uIHlvdXIgb3duICEhISENCklmIHlvdSBhcmUgdXNpbmcg TXlJU0FNLCBoYXZl
IGZ1biAhISEhDQoNClJvbGFuZG8gQS4gRWR3YXJkcw0KTXlTUUwgREJBIChD TURCQSkNCg0KMTU1
IEF2ZW51ZSBvZiB0aGUgQW1lcmljYXMsIEZpZnRoIEZsb29yDQpOZXcgWW9y aywgTlkgMTAwMTMN
CjIxMi02MjUtNTMwNyAoV29yaykNCjIwMS02NjAtMzIyMSAoQ2VsbCkNCkFJ TSAmIFNreXBlIDog
Um9sYW5kb0xvZ2ljV29yeA0KcmVkd2FyZHNAbG9naWN3b3Jrcy5uZXQNCg0K LS0tLS1PcmlnaW5h
bCBNZXNzYWdlLS0tLS0NCkZyb206IEJTIFRMQyBbbWFpbHRvOmJzdGxjQHlt YWlsLmNvbV0gDQpT
ZW50OiBXZWRuZXNkYXksIEF1Z3VzdCAwNSwgMjAwOSAxMDozMCBBTQ0KVG86 IG15c3FsQGxpc3Rz
Lm15c3FsLmNvbQ0KU3ViamVjdDogSG93IHRvIERldGVjdCBNeVNxbCB0YWJs ZSB1cGRhdGUvZGlm
ZmVyZW5jZQ0KDQoNCkhpLCBJJ20gYSBub3ZlbCBkZXZlbG9wZXIgb2YgTXlT cWwgYW5kIG5vdyBJ
IGFtIHRyeWluZyB0byBjcmVhdGUgYSBteXNxbCBxdWVyeSB0byBkZXRlY3Qg dGFibGUgdXBkYXRl
cy4NCg0KSSBxdWVyeSBhIGRhdGFiYXNlIHRhYmxlIGV2ZXJ5IFggc2Vjb25k cywgYW5kIGkgd2Fu
dCB0byBnZXQgb25seSB0aGUgZGlmZmVyZW50IHJvd3MgaW4gdGhlIHRhYmxl LiBUaGUgcmVzdWx0
IHRoYXQgSSB3YW50IHRvIGhhdmUgaXMgc2ltcGx5DQpUQUJMRSAodCA9IG5v dykgLSBUQUJMRSAo
dCA9IFggc2Vjb25kIGFnbykNCg0KRXZlcnkgdGltZSB0aGF0IGkgcXVlcnkg dGhlIGRhdGFiYXNl
IEkgc3RvcmUgdGhlIHRhYmxlIHNpdHVhdGlvbiBpbiBhbm90aGVyIHRhYmxl LCBjYWxsZWQgVEFC
TEVfVEVNUCwgc28gdGhlIG9wZXJhdGlvbiBvZiBkaWZmZXJlbmNlIGRldGVj dGlvbiBpcyANClRB
QkxFX0RJRkVSRU5DRSAgPSBUQUJMRSAtIFRBQkxFX1RFTVAuDQoNCkhvdyBj YW4gSSBkbyB0aGlz
IHF1ZXJ5PyBUaGUgcHJvYmxlbSB0aGF0IEkgaGF2ZSBpcyB0aGF0IEkgZG9u J3Qga25vdyB0aGUg
c3RydWN0dXJlIG9mIHRoZSB0YWJsZSBhbmQgSSB3YW50IHRvIGNyZWF0ZSBh IHByb2dyYW0gd2l0
aCBjYW4gYmUgdXNlZCBmb3IgYWxsIHR5cGVzIG9mIGRhdGEgYW5kIHRhYmxl cy4NCg0KUGxlYXNl
IGhlbHAgbWUuDQoNCkJlc3QgcmVnYXJkcw0KDQoNCiAgICAgIA0KDQotLSAN Ck15U1FMIEdlbmVy
YWwgTWFpbGluZyBMaXN0DQpGb3IgbGlzdCBhcmNoaXZlczogaHR0cDovL2xp c3RzLm15c3FsLmNv
bS9teXNxbA0KVG8gdW5zdWJzY3JpYmU6ICAgIGh0dHA6Ly9saXN0cy5teXNx bC5jb20vbXlzcWw/
dW5zdWI9cmVkd2FyZHNAbG9naWN3b3Jrcy5uZXQNCg0K

Re: How to Detect MySql table update/difference

Ok, but in this way I can only detect if it's done ONE type of operation, f=
or example if I add a row and I remove an another one with this query I det=
ect "no difference".=0AThe principal point of the query that I want is to s=
ay "which" rows are changed (added or deleted). I think it's not a easy que=
ry (or set of queries), but I want to create one for this goal. Howeve=
r thanks for the hint about the difference between COUNT(1) and COUNT(*)!=
Thanks. =0A-----Original Message-----=0AFrom: Rolando Edwards edwards@logicworks.net>=0ASent: Wednesday, August 05, 2009 23:12:09=0ASubje=
ct: RE: How to Detect MySql table update/difference I have good news a=
nd bad news for you when it comes to MySQL 5.x. Good News if you are c=
ounting against MyISAM=0ABad News if you are counting against InnoDB G=
ood News=0A---------=0AFor MyISAM=0AJust use either=0ASELECT table_rows FRO=
M information_schema.tables WHERE table_schema=3D'' and tabl=
e_name=3D'';=0AOr=0ASELECT COUNT(1) FROM . ame>; (This will work in MySQL 4.x as well) Bad News=0A--------=0AFor =
InnoDB=0ASELECT COUNT(1) FROM ., even though it can run =
faster than SELECT COUNT(*),=0Awill still count every row in the table anyw=
ay. If you are using InnoDB, you are on your own !!!!=0AIf you are usi=
ng MyISAM, have fun !!!! Rolando A. Edwards=0AMySQL DBA (CMDBA) 1=
55 Avenue of the Americas, Fifth Floor=0ANew York, NY 10013=0A212-625-5307 =
(Work)=0A201-660-3221 (Cell)=0AAIM & Skype : RolandoLogicWorx=0Aredwards@lo=
gicworks.net -----Original Message-----=0AFrom: BS TLC [mailto:bstlc@y=
mail.com] =0ASent: Wednesday, August 05, 2009 10:30 AM=0ATo: mysql@lists.my=
sql.com=0ASubject: How to Detect MySql table update/difference =0AHi, =
I'm a novel developer of MySql and now I am trying to create a mysql query =
to detect table updates. I query a database table every X seconds, and=
i want to get only the different rows in the table. The result that I want=
to have is simply=0ATABLE (t =3D now) - TABLE (t =3D X second ago) Ev=
ery time that i query the database I store the table situation in another t=
able, called TABLE_TEMP, so the operation of difference detection is =0ATAB=
LE_DIFERENCE =3D TABLE - TABLE_TEMP. How can I do this query? The pro=
blem that I have is that I don't know the structure of the table and I want=
to create a program with can be used for all types of data and tables.=0A=
=0APlease help me. Best regards =0A -- =0AMySQL Genera=
l Mailing List=0AFor list archives: http://lists.mysql.com/mysql =0ATo unsu=
bscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks.net =
=0A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: How to Detect MySql table update/difference

You need a timestamp column that autoupdates upon insert.
http://dev.mysql.com/doc/refman/5.0/en/datetime.html

Then use the DATE_SUB function for x seconds.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functio ns.html#function
_date-sub
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functio ns.html#function
_date-add

Not sure what all this "count" stuff is about as that's not what the OP
asked for.

He wanted to know what NEW rows there was, not a count of them, at least
that's how I read it.

> -----Original Message-----
> From: BS TLC [mailto:bstlc@ymail.com]
> Sent: Wednesday, August 05, 2009 2:57 PM
> To: Rolando Edwards; mysql@lists.mysql.com
> Subject: Re: How to Detect MySql table update/difference
>
>
> Ok, but in this way I can only detect if it's done ONE type
> of operation, for example if I add a row and I remove an
> another one with this query I detect "no difference".
> The principal point of the query that I want is to say
> "which" rows are changed (added or deleted). I think it's not
> a easy query (or set of queries), but I want to create one
> for this goal.
>
> However thanks for the hint about the difference between
> COUNT(1) and COUNT(*)!
>
> Thanks.
>
>
> -----Original Message-----
> From: Rolando Edwards
> Sent: Wednesday, August 05, 2009 23:12:09
> Subject: RE: How to Detect MySql table update/difference
>
> I have good news and bad news for you when it comes to MySQL 5.x.
>
> Good News if you are counting against MyISAM
> Bad News if you are counting against InnoDB
>
> Good News
> ---------
> For MyISAM
> Just use either
> SELECT table_rows FROM information_schema.tables WHERE
> table_schema='' and table_name='';
> Or
> SELECT COUNT(1) FROM .; (This will work in
> MySQL 4.x as well)
>
> Bad News
> --------
> For InnoDB
> SELECT COUNT(1) FROM ., even though it can
> run faster than SELECT COUNT(*),
> will still count every row in the table anyway.
>
> If you are using InnoDB, you are on your own !!!!
> If you are using MyISAM, have fun !!!!
>
> Rolando A. Edwards
> MySQL DBA (CMDBA)
>
> 155 Avenue of the Americas, Fifth Floor
> New York, NY 10013
> 212-625-5307 (Work)
> 201-660-3221 (Cell)
> AIM & Skype : RolandoLogicWorx
> redwards@logicworks.net
>
> -----Original Message-----
> From: BS TLC [mailto:bstlc@ymail.com]
> Sent: Wednesday, August 05, 2009 10:30 AM
> To: mysql@lists.mysql.com
> Subject: How to Detect MySql table update/difference
>
>
> Hi, I'm a novel developer of MySql and now I am trying to
> create a mysql query to detect table updates.
>
> I query a database table every X seconds, and i want to get
> only the different rows in the table. The result that I want
> to have is simply
> TABLE (t = now) - TABLE (t = X second ago)
>
> Every time that i query the database I store the table
> situation in another table, called TABLE_TEMP, so the
> operation of difference detection is
> TABLE_DIFERENCE = TABLE - TABLE_TEMP.
>
> How can I do this query? The problem that I have is that I
> don't know the structure of the table and I want to create a
> program with can be used for all types of data and tables.
>
> Please help me.
>
> Best regards


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to Detect MySql table update/difference

Hi,

>Hi, I'm a novel developer of MySql and now I am trying to create a mysql
>query to detect table updates.
>
>I query a database table every X seconds, and i want to get only the
>different rows in the table. The result that I want to have is simply
>TABLE (t = now) - TABLE (t = X second ago)
>
>Every time that i query the database I store the table situation in another
>table, called TABLE_TEMP, so the operation of difference >detection is
>TABLE_DIFERENCE = TABLE - TABLE_TEMP.
>
>How can I do this query? The problem that I have is that I don't know the
>structure of the table and I want to create a program with can be >used for
>all types of data and tables.

Although in relational theory, a "relation" means the table or view and you
should be able to modify one relation to another, in SQL databases, it
doesn't work that way.

In SQL databases, a "table" doesn't get modified, you modify rows. A
table is nothing but a description of a certain structure. So in order to
know
if any row in a table has been modified, you either need to have some kind
of timestamp in each row or keep a copy of the table since it's last
modification
check.

Instead of querying the table, I guess you could write a trigger that
updates
a "metatable" that says a row has been added/deleted/modified for a
particular table.


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org