Tracking record insertion and deletion

[Dan Greenblatt]

I am writing some software that, among other things, needs to track
the state of database tables. This includes occasionally checking the
table to see what records or added, modified, or deleted. The added
and modified parts of this have not proven too difficult to implement,
but is there an easy/elegant way to determine what records have been
deleted from a mySQL table ??

I'm very to mySQL, and after reading through the docs about logs, the
only thing I can come up with is to make a slave of the target table,
and somehow catch the delete statements, but do a select instead
before deleting them from the slave.

Has anyone tackled this problem and can offer some advice, or pointers
to advice..
??

Thanks in advance,
Dan

[Siemel Naran]

"Dan Greenblatt" <an******@yahoo.com> wrote in message

I am writing some software that, among other things, needs to track
the state of database tables. This includes occasionally checking the
table to see what records or added, modified, or deleted. The added
and modified parts of this have not proven too difficult to implement,
but is there an easy/elegant way to determine what records have been
deleted from a mySQL table ??

I'm very to mySQL, and after reading through the docs about logs, the
only thing I can come up with is to make a slave of the target table,
and somehow catch the delete statements, but do a select instead
before deleting them from the slave.

Has anyone tackled this problem and can offer some advice, or pointers
to advice..
??

Perhaps the simplest suggestion is to log the SQL statements. Within the
command center you can say "tee c:\mylog\statements.txt". You can also
start mysql with the --tee option.

Then of course you have to parse the tee file. If so desired you can store
the pieces of statements into another MySql table (but don't use tee as you
want to log these insert statements). It all depends what you want to do
with the information.

[David L]

if it won't impact peformance too much i'd suggest setting up a log
table, and append to it straight after (or before) such changes. I'm
starting to do this with my projects and it's been great for tracking
who did what when, plus exactly what has been changed. Great also for
debugging. A log file will probably give you more info though - depends
i guess.
Dan Greenblatt wrote:

I am writing some software that, among other things, needs to track
the state of database tables. This includes occasionally checking the
table to see what records or added, modified, or deleted. The added
and modified parts of this have not proven too difficult to implement,
but is there an easy/elegant way to determine what records have been
deleted from a mySQL table ??

I'm very to mySQL, and after reading through the docs about logs, the
only thing I can come up with is to make a slave of the target table,
and somehow catch the delete statements, but do a select instead
before deleting them from the slave.

Has anyone tackled this problem and can offer some advice, or pointers
to advice..
??

Thanks in advance,
Dan

[Mike Chirico]

"Dan Greenblatt" <an******@yahoo.com> wrote in message
news:a2**************************@posting.google.c om...

I am writing some software that, among other things, needs to track
the state of database tables. This includes occasionally checking the
table to see what records or added, modified, or deleted. The added
and modified parts of this have not proven too difficult to implement,
but is there an easy/elegant way to determine what records have been
deleted from a mySQL table ??

I'm very to mySQL, and after reading through the docs about logs, the
only thing I can come up with is to make a slave of the target table,
and somehow catch the delete statements, but do a select instead
before deleting them from the slave.

Has anyone tackled this problem and can offer some advice, or pointers
to advice..
??

Hope this helps:

I think the log files automatically contain this information.

mysql> show binlog events;

or

mysql> show binlog events in '<logfile>';

will show the create statements, updates, and deletes.

Reference: ( TIP 24: and TIP 2) in
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

It's also possible to write a C api that will automatically extract this
information. For an example program you may want to download

http://prdownloads.sourceforge.net/s...ar.gz?download

and take a look at "selectbinlog.c". Or, a perl script can be written as
well...(See TIP 4: ) for the basics on perl with mysql.

This can contain a lot of information. Instead of putting this information
into a mysql table, you may want to put it into a BerkeleyDB file. If you're
running Linux or Unix you probably have this installed. BerkeleyDB is used
by MySQL. It's not really a database but a fast hash or b-tree, depending
on format option selected. For an example of how to write BerkeleyDB api's
see the following:
http://prdownloads.sourceforge.net/c...ar.gz?download

The latest version of BerkeleyDB allows for encryption, so that this data
can be protected. I use BerkeleyDB for this type of logging because I need
to do extremely quick lookups on this information.

Regards,

Mike Chirico

[Siemel Naran]

"Mike Chirico" <mc******@comcast.net> wrote in message
news:zsOdnWTnUKpN0V7dRVn-

I think the log files automatically contain this information.

mysql> show binlog events;

or

mysql> show binlog events in '<logfile>';

Do you have to turn on binlog events too? Can you use the command on the
tee file?

[Mike Chirico]

"Siemel Naran" <Si*********@REMOVE.att.net> wrote in message
news:u8*********************@bgtnsc05-news.ops.worldnet.att.net...

"Mike Chirico" <mc******@comcast.net> wrote in message
news:zsOdnWTnUKpN0V7dRVn-

I think the log files automatically contain this information.

mysql> show binlog events;

or

mysql> show binlog events in '<logfile>';

Do you have to turn on binlog events too? Can you use the command on the
tee file?

I've always used the "tee file" for a single session. I think it returns
everything include the results of the select, delete statements? I don't
know of an easy way to parse the tee file, or to get information on all
users. The "show binlog events" has options for listing start positions
and limiting the number of events. For instance, "mysql> show binlog event
from 201 limit 2;" will show the 2 events starting at position 201, which is
handy. True, the results or return rows of a query are not shown; but, I
think this is a big advantage for a heavily used database?

I've never done anything special to turn on binlog events. However, doing a
"mysql> reset master" will delete and clear the log files.

FYI: I think in version 5 of Mysql these log files will be "all" binary.
Whereas in version 4-alpha, I can currently, using Linux, do the "strings"
command on these files "$ strings /usr/local/var/third-fl-71-bin.000001" and
get readable output, but, then there is no position tracking.

Regards,

Mike Chirico

[Mike Chirico]

"Siemel Naran" <Si*********@REMOVE.att.net> wrote in message
news:u8*********************@bgtnsc05-news.ops.worldnet.att.net...

"Mike Chirico" <mc******@comcast.net> wrote in message
news:zsOdnWTnUKpN0V7dRVn-

I think the log files automatically contain this information.

mysql> show binlog events;

or

mysql> show binlog events in '<logfile>';

Do you have to turn on binlog events too? Can you use the command on the
tee file?

/etc/my.cnf

[mysqld]
port = 3306
socket = /tmp/mysql.sock
log-bin = /usr/local/var/mysqlLOGb.log
or just

log-bin
Regards,

Mike Chirico

[Siemel Naran]

"Mike Chirico" <mc******@comcast.net> wrote in message
news:4_GdnQgFSZmPxF7dRVn-

Do you have to turn on binlog events too? Can you use the command on the tee file?
I've always used the "tee file" for a single session. I think it returns
everything include the results of the select, delete statements? I don't
know of an easy way to parse the tee file, or to get information on all
users. The "show binlog events" has options for listing start positions
and limiting the number of events. For instance, "mysql> show binlog

event from 201 limit 2;" will show the 2 events starting at position 201, which is handy. True, the results or return rows of a query are not shown; but, I
think this is a big advantage for a heavily used database?

I've never done anything special to turn on binlog events. However, doing a "mysql> reset master" will delete and clear the log files.

FYI: I think in version 5 of Mysql these log files will be "all" binary.
Whereas in version 4-alpha, I can currently, using Linux, do the "strings"
command on these files "$ strings /usr/local/var/third-fl-71-bin.000001" and get readable output, but, then there is no position tracking.

Thanks for your answers.

[Dan Greenblatt]

Excellent !! This looks like exactly the kind of thing I need..
Thanks for all the great info -
Dan
"Mike Chirico" <mc******@comcast.net> wrote in message news:<2e********************@comcast.com>...

"Siemel Naran" <Si*********@REMOVE.att.net> wrote in message
news:u8*********************@bgtnsc05-news.ops.worldnet.att.net...

"Mike Chirico" <mc******@comcast.net> wrote in message
news:zsOdnWTnUKpN0V7dRVn-

I think the log files automatically contain this information.

mysql> show binlog events;

or

mysql> show binlog events in '<logfile>';

Do you have to turn on binlog events too? Can you use the command on the
tee file?

/etc/my.cnf

[mysqld]
port = 3306
socket = /tmp/mysql.sock
log-bin = /usr/local/var/mysqlLOGb.log
or just

log-bin
Regards,

Mike Chirico

[Dan Greenblatt]

Hi David -
Would you be able to clarify your suggestion? Specifically, what do
you put in your 'log tables' ? What gets appended to it?
Thanks,
Dan
David L <[e-mail]> wrote in message news:<85*****************@news-server.bigpond.net.au>...

if it won't impact peformance too much i'd suggest setting up a log
table, and append to it straight after (or before) such changes. I'm
starting to do this with my projects and it's been great for tracking
who did what when, plus exactly what has been changed. Great also for
debugging. A log file will probably give you more info though - depends
i guess.
Dan Greenblatt wrote:

I am writing some software that, among other things, needs to track
the state of database tables. This includes occasionally checking the
table to see what records or added, modified, or deleted. The added
and modified parts of this have not proven too difficult to implement,
but is there an easy/elegant way to determine what records have been
deleted from a mySQL table ??

I'm very to mySQL, and after reading through the docs about logs, the
only thing I can come up with is to make a slave of the target table,
and somehow catch the delete statements, but do a select instead
before deleting them from the slave.

Has anyone tackled this problem and can offer some advice, or pointers
to advice..
??

Thanks in advance,
Dan

[David L]

it depends what you want to track. For my projects i make a selection
of the important fields in the data table, plus other fields such as the
user name who's making the change, a transaction number, what the action
is, and a timestamp. The action is just a text field that could say
"before-edit", "after-edit", "new", etc. All these fields would make up
the log table. If there are other fields from other tables that would
be helpful to put things into context, then those would be included as
well.

Whenever there is an update on a record, even on just one of the fields,
i append all these fields into the log table, before the update, and
append another row after the update. Because the 2 log records share a
transaction number (i just generate a random number for the purpose),
this allows me to see at a glance, what change look place. Of course to
be precise i probably should flag which field is changed, but i want to
be as lazy as possible.

Note that I'm actually appending the contents of my original table(s),
not sql statements.

And if the project has several main tables that don't relate closely to
each other, then i would have several of these log tables as well.

Dan Greenblatt wrote:

Hi David -
Would you be able to clarify your suggestion? Specifically, what do
you put in your 'log tables' ? What gets appended to it?
Thanks,
Dan
David L <[e-mail]> wrote in message news:<85*****************@news-server.bigpond.net.au>...

if it won't impact peformance too much i'd suggest setting up a log
table, and append to it straight after (or before) such changes. I'm
starting to do this with my projects and it's been great for tracking
who did what when, plus exactly what has been changed. Great also for
debugging. A log file will probably give you more info though - depends
i guess.
Dan Greenblatt wrote:

I am writing some software that, among other things, needs to track
the state of database tables. This includes occasionally checking the
table to see what records or added, modified, or deleted. The added
and modified parts of this have not proven too difficult to implement,
but is there an easy/elegant way to determine what records have been
deleted from a mySQL table ??

I'm very to mySQL, and after reading through the docs about logs, the
only thing I can come up with is to make a slave of the target table,
and somehow catch the delete statements, but do a select instead
before deleting them from the slave.

Has anyone tackled this problem and can offer some advice, or pointers
to advice..
??

Thanks in advance,
Dan

[Siemel Naran]

"David L" <[e-mail]> wrote in message news:85mwc.7220$rz4.2487@news-

if it won't impact peformance too much i'd suggest setting up a log
table, and append to it straight after (or before) such changes. I'm
starting to do this with my projects and it's been great for tracking
who did what when, plus exactly what has been changed. Great also for
debugging. A log file will probably give you more info though - depends
i guess.

An unrelated question. If there is a field date_last_updated, how can I
automatically set that field whenever I update a record? I know in MySql if
you have

create table product
(
id int ( 1) unsigned not null
auto_increment
, date_created timestamp not null
default ''

then when you insert a new record date_created defaults to now. But how to
set date_last_updated, so that whenever I issue an update statement, the
system automatically updates this column.

update product
set name = 'abc'
where name = 'def';

The above statement should automatically set product.date_last_updated to
now. Is this possible in MySql?

[David L]

Hi Siemel, sorry i haven't checked posts till now.
In my experience when a field is the timestamp type, the timestamp will
update itself automatically whenever there is a change in the contents
of any other fields in the record.

Regards.

Siemel Naran wrote:

"David L" <[e-mail]> wrote in message news:85mwc.7220$rz4.2487@news-

if it won't impact peformance too much i'd suggest setting up a log
table, and append to it straight after (or before) such changes. I'm
starting to do this with my projects and it's been great for tracking
who did what when, plus exactly what has been changed. Great also for
debugging. A log file will probably give you more info though - depends
i guess.

An unrelated question. If there is a field date_last_updated, how can I
automatically set that field whenever I update a record? I know in MySql if
you have

create table product
(
id int ( 1) unsigned not null
auto_increment
, date_created timestamp not null
default ''

then when you insert a new record date_created defaults to now. But how to
set date_last_updated, so that whenever I issue an update statement, the
system automatically updates this column.

update product
set name = 'abc'
where name = 'def';

The above statement should automatically set product.date_last_updated to
now. Is this possible in MySql?