The checking of table field of MySQL

can the on update trigger help on this ? please give an example.

so if table A has field add/removed, then the respective AB table has the respective field add/dropped?

Are you talking about table structure changes (field added/removed) or data changes (value in a field changes, row added/deleted)?

yes. very smart, DDL change, seems on update trigger only focus on DML, right ?

what are you trying to do here? Very odd to me to have two identical tables. Seems there may be something amiss with your design

We want to buid a method here that do data level audit automaticaly, image one table has changed fielded, how can the respective audit table konw there are changed of field and then add/delete the respective new field to the audit table to start data level audit again.

some kind of DDL trigger to modify the respective data audit talbe on the fly ?

on update trigger?

What do you mean by "field?"  Are you speaking of adding a COLUMN or adding / deleting / changing the contents of a ROW?

I still don't understand.  That wouldn't be an audit in my mind.  The audit should have a trail describing what has happened.  In your case, that wouldn't exist as it would be modified when the original table was modified.
What you're after is a log of the events that are happening to your database.  MySQL has a plugin http://dev.mysql.com/doc/refman/5.5/en/audit-log-plugin.html but it is for the enterprise version.  If you don't have that then you'll have to code it yourself.  There is the mcafee mysql audit plugin https://github.com/mcafee/mysql-audit but I haven't used it.

" MySQL has a plugin http://dev.mysql.com/doc/refman/5.5/en/audit-log-plugin.html but it is for the enterprise version"

we tried that, this one is action level audit, which log something worse than General log.

We need data level audit, like what data cell has been change and what is the value !

"https://github.com/mcafee/mysql-audit "

this one seems doing something in  C language.

Oralce has some kind of Fine-grained auditing (FGA), introduced in Oracle9i, allowed recording of these row-level changes, so we know what data has been write to a data cell.

for example,
update SCOTT.EMP set salary = 12000 where empno = 123456;
How do you track this activity in the database?

usually Audit trial lets you know that Joe updated the table EMP owner by SCOTT, but it does not show that he updated the salary column for the table for employee number 123456. It does not show the value of the salary column before the change, either¿ to capture such detailed changes.

Can MySQL has any audit feature do this on this to show what that the value 123456 is updated by Joe and the colume he/she udpate is the "salary" one ?

or we need trigger for all insert/update/delete/select on a table anyway and write to a table created for this kind of information?

what I need is data level, log down what vaule has been changed.

or is there any third party tools can do this for us without building trigger for each table?

if the application has 3000 > tables, then we have to turn on trigger for each of them, this make the audit hightly unmanageable.

the tools has to do somehting like if table is removed/added, the audit tools change the underlayer audit table/entries automatically,

any ?

If you need data audit then the only way is to build a trigger on insert/update/delete that will insert into an audit table what was the row before and after the operation. I don't think mysql has any built in audit functionality. In terms of DDL I am not sure if it is possible unless you will build a schedule job that will check at some regular intervals the INFORMATION_SCHEMA.TABLE to compare the columns and their definitions for each table you want to audit. This means you will have to use an audit table for DDL where you will keep the initial table structure and then use it to compare to INFORMATION_SCHEMA every time you do the check.

This could help:

http://serge.frezefond.com/2013/04/how-can-we-audit-a-mysql-server/

If you are stuck with MySql then you probably have to write it yourself. For SQL Server there is a third party tool that does exactly that. Check this product. They claim that it records all database changes, including structure changes with an undo functionality. I have not used this product and can't comment.

"If you need data audit then the only way is to build a trigger on insert/update/delete that will insert into an audit table what was the row before and after the operation."

we have this already:

Create respective Audit table:

CREATE TABLE DBAudit_AB LIKE AB;

Select allOpen in new window

add more column to store related audit information:

ALTER TABLE DBAudit_AB 
ADD TriggerAction VARCHAR( 10 ) NULL AFTER DBAuditID,
ADD Action CHAR( 10 ) NULL AFTER TriggerAction ,
ADD ActionDate DATETIME NULL AFTER Action ,
ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;

Select allOpen in new window

AFTER INSERT trigger:

CREATE TRIGGER AB_INSERT_TR AFTER INSERT ON AB
FOR EACH ROW
BEGIN 
INSERT INTO DBAudit_AB SET 
TriggerAction='AFTER',
Action='INSERT',
ActionDate=now(),
ActionBy=USER(),
i=NEW.i,
vendor=NEW.vendor,
sku=NEW.sku;
END

Select allOpen in new window

here we go !!

"
http://serge.frezefond.com/2013/04/how-can-we-audit-a-mysql-server"

this one is about the action level audit and this is not we want, we want trigger on data level.

the above works perfectly and we want to automate that for >100 tables, any deployment from development team done but didn't change the trigger themselves, we have to scan that out by the SP, and then let the SP output the batch of script for us that include:

1) the new respective DBAudit_<source table> we need to create.
2) the after insert/update and before delete trigger script, show up on the screen and we just review that and execute that ourselves.

The hardest part is the field change detection, add a DDL trigger on the fire so that when source table changed on the time of deployment, the respective DBAudit_<source table> will have that field changed too?

The hardest part is what if hte field has type change, but not add/drop fields. t

this confuse me a lot .

"you will build a schedule job that will check at some regular intervals the INFORMATION_SCHEMA.TABLE to compare the columns and their definitions for each table you want to audit. "

this only scan out how many talbe has been create/dropped, not the fields, rihgt?

"means you will have to use an audit table for DDL where you will keep the initial table structure and then use it to compare to INFORMATION_SCHEMA every time you do the check.
"
some example ?

chaau,

The SQL serve r also has a feature called change data capture, CDC, but we only focus on MySQL first and then transform the whole thing to Oracle.

MS SQL ,we have the vendor applicaton done that already.

Sorry, you will have to check the INFORMATION_SCHEMA.COLUMNS view in order to get the column definition for each table. You will have to create a table with the initial info for all the tables you want to monitor and then build a procedure that will compare the INFORMATION_SCHEMA.COLUMNS with the content of that table and see if anything changed. This way you will be able to detect the change in type, size and new or dropped columns. With that you will build dynamic queries to apply to your audit tables.

Here is info abut dynamic queries:

http://forums.mysql.com/read.php?60,27979,30437

"Sorry, you will have to check the INFORMATION_SCHEMA.COLUMNS view in order to get the column definition for each table. "

I knew this one and I am sure is the result is ok:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = '<database>' AND TABLE_NAME = '<table name>';

"You will have to create a table with the initial info for all the tables you want to monitor and then build a procedure that will compare the INFORMATION_SCHEMA.COLUMNS with the content of that table and see if anything changed."

I plan to create the same table in other DB which server only for this purpose, so all table on that DB is the information we do it LAST TIME. then I just compare with this each time I do  the above query again.

or even copy the existing information schema database to another one and compare the whole thing with the new one next time we check that?


"This way you will be able to detect the change in type, size and new or dropped columns. With that you will build dynamic queries to apply to your audit tables."

that's the one I want to know, any easy way instead of human eyes to check the difference between 2 x table in terms of fields?

what is the script to compare that? I am worry about the field type, Just find out which table is add/dropped is easy one, I just use NOT IN (result set) statement.

but fields.........

I read this too:http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

I don't know what the prepare statement for ?

EXECUTE can be just execute the whole statement the prepare statement STORED.

Any on the fire trigger on the source table the trigger the table change on the respective audit table at the time the field/column add/change/drop ?

I read that link, what is prepare statement for ?

all composite statement will be built and store inside prepare variable and use EXECUTE to execute it?

this link: stackoverflow.com/questions/8910617/using-trigger-to-update-table-in-another-database?rq=1 also show that one is not DDL trigger:

delimiter |
DROP TRIGGER IF EXISTS after_update_user|
 CREATE TRIGGER after_update_user AFTER UPDATE ON db_test.user
  FOR EACH ROW BEGIN
     UPDATE db_testplus.user SET name = NEW.name WHERE id = NEW.id;
  END;
|
delimiter ;

Select allOpen in new window

the most cloest one is stackoverflow.com/questions/8657405/mysql-trigger-to-fire-on-alter-or-drop, but it seems that MySQL do not have DDL trigger, right?

0down vote
 

PROCEDURE `pr_new_type`( IN column_name varchar(10) )
BEGIN
SET @queryText = CONCAT('ALTER TABLE `user_rights` ADD ', column_name, ' BINARY( 9 ) NULL');
PREPARE query FROM @queryText;
EXECUTE query;
DEALLOCATE PREPARE query;
END 

Select allOpen in new window

I run this :

SELECT COLUMN_NAME,  DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT  FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'database name' AND TABLE_NAME = 'Table name';

Select allOpen in new window

I see column_Default is {null}, is it equal to the NOT NULL in field DDL statment:

ALTER TABLE tbl_Country MODIFY IsDeleted tinyint(1) NOT NULL; ?

or I should check the IS_NULLABLE column on 'NO' or 'YES' ?

What should I say is how to check field type change one by one.

"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = '<database>' AND TABLE_NAME = '<table name>';"

You will have to look at the type, size, decimal places, defaults and if column is nullable as well. Any changes to these columns will trigger your audit actions.

You will have to figure out for yourself what are the values kept in INFORMATION_SCHEMA for every type. You should create a test table with columns that will have all types, with defaults, nulleble, not nullable and then see what you get in INFORMATION_SCHEMA for them.

Based on that you will create a procedure that will compare your "witness" table to the INFORMATION_SCHEMA if any changes occured since last chek. If it did you should generate the ALTER command and also update your "witness" table.

you mean this:

    SELECT COLUMN_NAME,  DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = '<database name>' AND TABLE_NAME = '<table name>';

Select allOpen in new window

"Any changes to these columns will trigger your audit actions."

any DDL trigger for this in schema level ? then we just do that ont the fire when table schema change, right?

"You should create a test table with columns that will have all types, with defaults, nulleble, not nullable and then see what you get in INFORMATION_SCHEMA for them."

For the first time I will just create the same table with different name on other DB, then everytime check against this old record, right?

but how to compare table fields? minus  and union ?

"If it did you should generate the ALTER command and also update your "witness" table. "

This is the hardest part! any example for me to reference ? comparing table is the easy bit I think.

"2. Right."

what is 2 you are referring to?

"SELECT ... FROM INFORMATION_SCHEMA.COLUMNS C WHERE NOT EXISTS (SELECT * FROM witness WHERE colname<>C.colname OR type<>C.type or size<>C.size...)"

wait, this one will compare table column one by one  and only show up the added/drop field/column?

do we have to implement other way to check column type one by one ?
what should it be ?

ok, one thing, I think the code mess up:

"EXECUTE stmt USING @schema1,@schema2"

What is the @variable about? that one is not defined? or it is what's the '?' about?

"what is 2 you are referring to?"

I was answering in order to your questions from the post before that.

That query will bring up any differences because uses OR in the WHERE clause. Regardless what the differences are if you just build all ALTER statements for each column accordingly in one batch and then execute it will bring the tables in sync with the originals.

Every ? will be replaced with a variable in the order from left to right: first ? will be replaced by first variable, second ? will be replaced by second variable...

"Every ? will be replaced with a variable in the order from left to right: first ? will be replaced by first variable, second ? will be replaced by second variable... "

this reply seems a bit simple. any sample data set ?

but one thing, it seems that we can MINUS the different from "SELECT ... FROM INFORMATION_SCHEMA.COLUMNS" before and after, is it possible ? this is to replace NOT EXISTS statement,

Zberteoc,

MysQL even 5.5 enterprise has Oracle confirmed that it can't do DDL trigger, but I can tell you that I just attend the first Oracle admin workshop, the lecture said even Oracle 11g DDL trigger can't help on this. we can fire the trigger whenever there are change in the schema and tell the trigger to modify the respective DBAudit_<souce table> to change field  immediately.