Solved

The checking of table field of MySQL

Posted on 2013-10-25
26
753 Views
Last Modified: 2013-11-10
Dear all,

if I want clone out Table A to Table AB, so AB and A with the same fields, if Table A changed fields (add/remove), how can we knew that ? any script to find out and compare to Table AB.

There can be 2 x situtation:
1) Table A has new field added.
2) Table A has field removed.

Then anyway to change the same change to AB so that A and AB has the same number of field again?
0
Comment
Question by:marrowyung
  • 15
  • 5
  • 2
  • +2
26 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 39599914
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?
0
 
LVL 24

Expert Comment

by:chaau
ID: 39599926
Are you talking about table structure changes (field added/removed) or data changes (value in a field changes, row added/deleted)?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39599977
yes. very smart, DDL change, seems on update trigger only focus on DML, right ?
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39605170
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
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39605210
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?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39605237
What do you mean by "field?"  Are you speaking of adding a COLUMN or adding / deleting / changing the contents of a ROW?
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39605239
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39605773
" 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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39605774
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 ?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39606000
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/
0
 
LVL 24

Expert Comment

by:chaau
ID: 39606639
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39607944
"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;

Open 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 ;

Open 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

Open 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.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 39608744
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
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39610705
"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 ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39610719
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 ;

Open 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 

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39610738
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';

Open 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' ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39610739
What should I say is how to check field type change one by one.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39611611
"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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39615958
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>';

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39615970
"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.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39616408
1. There is no DDL triggers in MySQL. Even in SQL server were introduced only with 2008 version. You will have to create a job that will compare every 5 min or so the content of the "witness" table with the INFORMATION_SCHEMA.COLUMNS view.

2. Right.

3. You will just have to compare the tables with

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

and use it to generate the ALTER statements dinamically. An example:

PREPARE stmt FROM 'select count(*)  from information_schema.schemata where schema_name = ? or schema_name = ?'
;

EXECUTE stmt USING @schema1,@schema2

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39624483
"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?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39627131
"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...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39627176
"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,
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39637845
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now