Log Maintenance on MySQL affected tables in a log table

[s.prema@yahoo.com]

Hello

 

How to maintain and update a log table often for a database????

Like storing the affected table name, column name, user, time...

 

Thanks in advance

[jskywalker]

Triggers:

A trigger is defined to activate when an INSERT, DELETE, or UPDATE statement executes for the associated table. A trigger can be set to activate either before or after the triggering statement. For example, you can have a trigger activate before each row that is inserted into a table or after each row that is updated

(http://dev.mysql.com/doc/refman/5.0/en/triggers.html)

 

But you will have to define a trigger for every table you want to keep a log for..

 

(i think those guys here: http://www.hotscripts.com/forums/php/56694-log-maintenance-mysql-affected-tables-log-table.html will give the same answer...  )

[s.prema@yahoo.com]

Hmmm I knew intelligent people would find my posts on other forums easily

Thank u

But I m having having more than 100 tables............

[jskywalker]

ok, i tried

1) create a table for the logging

CREATE TABLE `LOGGING` (
  `id` bigint(20) NOT NULL auto_increment,
  `table` varchar(20) default NULL,
  `username` varchar(20) default NULL,
  `logtime` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

 

2) create a script which will create the triggers (in this example i use my 'test' database...

#!/bin/bash
echo "DELIMITER ;;" >tmp.sql

for f in `mysqlshow test | sed -e 's/|//g' | grep -v '-'`; 
do 
echo 'CREATE TRIGGER t_'$f' AFTER INSERT ON '$f' FOR EACH ROW BEGIN insert into LOGGING (`table`, `username`, `logtime`) values ("'$f'",CURRENT_USER(), NOW()); end ;; ' >>tmp.sql ;
done
echo "DELIMITER ;" >>tmp.sql

 

3) its a quick and dirty script, so you have to delete some lines from the tmp.sql script...

 

at least the 1st three lines, and the line that whould create a trigger for the 'logging' table....

 

4) when you think its OK, you can execute this script to create the triggers in your database...

 

5) this works for INSERTS, you have to tweak this do the same for UPDATES and DELETES....

[jskywalker]

for f in `mysqlshow test | sed -e 's/|//g' | grep -v '-'`;

 

for f in `mysqlshow test | sed '1,4d;s/|//g;$d' | grep -v 'LOGGING'`;

 

this might be code....

now you dont have to delete thos 1st three lines, and the line for the 'LOGGING'-table,  anymore.. ;-)

[s.prema@yahoo.com]

Error occurring

 

<?php

#!/bin/bash

 

$hostname='localhost';

$un= 'root';

$pwd='';

$db='test';

$link=mysql_connect($hostname, $un, $pwd);

if(!$link)

  die('Could not connect: ' . mysql_error());

if(!mysql_select_db($db,$link))

  die('Could not link db: ' . mysql_error());

$f='ttt'; 

$d='DESCRIPTION';

 

echo "DELIMITER ;;" >tmp.sql ;

 

for f in "mysqlshow test | sed '1,4d;s/|//g;$d' | grep -v 'LOGGING'";

do

  echo "CREATE TRIGGER t_'$f' AFTER INSERT ON '$f' FOR EACH ROW BEGIN insert into LOGGING (table, username, logtime) values ('$f',CURRENT_USER(), NOW()); end ;; " >>tmp.sql ;

done

echo "DELIMITER ;" >>tmp.sql

 

?>

 

can u explain the mysqlshow command..

$f holds the table name whereas $d ??

 

giving error as Parse error: syntax error, unexpected T_STRING, expecting '(' in C:\xampp\htdocs\xampp\Prema\trig.php on line 16

 

ie., in the command..

expalin pls...

 

 

[Mchl]

Thought about enabling query log and then parsing what's saved in it?

[jskywalker]

the 1st line in my script says:

#!/bin/bash

 

this indicates that this is not a PHP-script, but a linux-BASH-script.

you are trying to run the linux-script, as i it was PHP-code

 

mysqlshow test

This is a linux command (it also comes with the windows-install of MySQL), which will show all tablenames in the database 'test'

 

[s.prema@yahoo.com]

All ok...

Understood.  But php error  in that 'for' statement...

dont know how to correct.....

how to insert linux for statement in php??

[Mchl]

http://dev.mysql.com/doc/refman/5.1/en/query-log.html

[jskywalker]

a solution in PHP might be better, because this is PHPfreaks...

 

select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='test' ;

This MySQL statement returns all tablenames in the database 'test'.

You can write a script to loop over the result,

and create the statements to create the triggers for every name returned

[Mchl]

a solution in PHP might be better, because this is PHPfreaks...

 

Errr... what? Actually KISS rule is to be obeyed no matter how this site is called.

 

I can't see how dynamically creating dozens of triggers is better than querying one table...

[jskywalker]

true, but it also depends on what you want to do with this table..

 

and i did not say that i was in KISS-mode....

[Mchl]

and i did not say that i was in KISS-mode....

 

Happens to every guy sometimes this does