How to log SQL Create,Update,Delete only?

#1

Hi everyone,

Does anyone know of a way to log SQL statements (but without the SELECT
queries)? I would like to keep track of the changes made to the
database, but I don’t want the log file to be flooded with SELECT
statements (I am using the default logger at debug level). Is it
possible to do that with the rails logger?

#2

You should look for a grep manual - grep only the lines with SQL but
not those with SELECT. Since I’m not a grep master, I’m not sure
how…

On Mar 28, 4:45 pm, Wai T. removed_email_address@domain.invalid

#3

alexbrie wrote:

You should look for a grep manual - grep only the lines with SQL but
not those with SELECT. Since I’m not a grep master, I’m not sure
how…

On Mar 28, 4:45 pm, Wai T. removed_email_address@domain.invalid

I know it is possible to grep from the log file, but it is very
inefficient. For example, if I want to look at the last 10 create
command, the entire 1GB log file would have to be loaded and “grepped”.

#4

Why not just use database triggers and use a table for logging?
That way you can also execute Rails logic against the log and do
something
useful with it.

#5

Erik A. wrote:

Why not just use database triggers and use a table for logging?
That way you can also execute Rails logic against the log and do
something
useful with it.

Thanks for the suggestion Erik. I am using MySQL, so I can try trigger.
However, this is my first time write one, do you have a sample trigger I
can use as reference? I can only find example where the trigger operate
on the same database that triggers it. Thanks.

#6

Hmm…

It doesn’t seem like trigger can add anything beside a simple “ITEM
ADDED” to a log table.

#7

U can parse the mysql bin log and track it.

On Mar 28, 9:45 pm, Wai T. removed_email_address@domain.invalid

#8

Wai, I don’t think it is a good idea to use database triggers to write
to
log files, if you mean that.
You can write a trigger that logs changes to a database table from which
you
can then SELECT from.

Erik

#9

I am using MySQL, so I can try trigger.
However, this is my first time write one, do you have a sample trigger I
can use as reference?

DELIMITER //

DROP TRIGGER tbl_Insert//
DROP TRIGGER tbl_Update//
DROP TRIGGER tbl_Delete//

commit //

CREATE TRIGGER tbl_Insert AFTER INSERT ON tbl
FOR EACH ROW
BEGIN
INSERT INTO _history (Action, ActDate, User)
values (‘Add’, Now(), SUBSTRING_INDEX(USER(),_utf8’@’,1));
END//

CREATE TRIGGER tbl_Update BEFORE UPDATE ON tbl
FOR EACH ROW
BEGIN
Declare Act varchar(400);

Set Act = ‘Edit:’;

if (select old.field1<>new.field1) then Select CONCAT(Act,’ Field1’)
into Act; end if;
if (select old.field2<>new.field2) then Select CONCAT(Act,’ Field2’)
into Act; end if;

INSERT INTO firm_history (Action, ActDate, User)
values (Act, Now(), SUBSTRING_INDEX(USER(),_utf8’@’,1));
END//

CREATE TRIGGER tbl_Delete BEFORE DELETE ON tbl
FOR EACH ROW
BEGIN
Declare Act varchar(400);

Set Act = ‘Del’;

INSERT INTO firm_history (Action, ActDate, User)
values (Act, Now(), SUBSTRING_INDEX(USER(),_utf8’@’,1));
END//

#10

Does anyone know of a way to log SQL statements (but without the SELECT
queries)? I would like to keep track of the changes made to the
database, but I don’t want the log file to be flooded with SELECT
statements (I am using the default logger at debug level). Is it
possible to do that with the rails logger?

Suggestion: do it using the logger and ActiveRecord callbacks.

http://api.rubyonrails.com/classes/ActiveRecord/Callbacks.html

That way you can choose to log before_create, before_save,
before_destroy or whatever.

Regards,
Dave

#11

urbanus wrote:

Does anyone know of a way to log SQL statements (but without the SELECT
queries)? I would like to keep track of the changes made to the
database, but I don’t want the log file to be flooded with SELECT
statements (I am using the default logger at debug level). Is it
possible to do that with the rails logger?

Suggestion: do it using the logger and ActiveRecord callbacks.

http://api.rubyonrails.com/classes/ActiveRecord/Callbacks.html

That way you can choose to log before_create, before_save,
before_destroy or whatever.

Regards,
Dave

Is any sample how to log changes with callback hooks, especially with
log changes in fields, such was in MySQL sample in this topic.

Reagards,
Romul