Solved

The checking of MySQL field length

Posted on 2014-02-16
14
452 Views
Last Modified: 2014-02-17
Right now I willl have a function that use hte cursor to filter out the all column type and fields and then I will compare the field length one by one to see which one get extended by:

DECLARE fieldname CURSOR FOR 	
Select DISTINCT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE ,  Column_default, COLUMN_COMMENT, CHARACTER_MAXIMUM_LENGTH, 
CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, 
numeric_scale, substring(COLUMN_TYPE, 5, instr(COLUMN_TYPE, ')')-5) AS int_length
 FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = Trigger_databasename AND TABLE_NAME = trigger_tablename and COLUMN_NAME
NOT IN
(
Select DISTINCT COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'Audit_info' AND TABLE_NAME = CONCAT(Trigger_databasename,'_DBAudit_',trigger_tablename)
);

Open in new window


.
.
.
.

FETCH fieldname INTO current_COLUMN_NAME, current_COLUMN_TYPE, current_IS_NULLABLE, current_Column_default, current_COLUMN_COMMENT, 
   current_CHARACTER_MAXIMUM_LENGTH, current_CHARACTER_OCTET_LENGTH, current_NUMERIC_PRECISION, current_numeric_scale, current_int_length ;	
         IF fieldnameCursor_finished = 1 THEN 	
          LEAVE get_fieldlist;	
          END IF;	

Open in new window



So anyone knows if I check the column length by checking     current_CHARACTER_MAXIMUM_LENGTH, current_CHARACTER_OCTET_LENGTH, current_NUMERIC_PRECISION, current_numeric_scale, current_int_length, can they all compared directly or I have to convert all of them to a number BEFORE compare ?
0
Comment
Question by:marrowyung
  • 10
  • 4
14 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39863970
so, your initial query is to find "new" fields (I presume, to add them to the audit table ...)

and the new/modifed query is to check "modified" fields (I presume to modify the audit table ... )

you have 2 options: write a new sql (suggested below), or combine the "new" with the current code into 1 with a return field which indicates if the field already existed, or if it was modified.
let's first try to find "modified" columns, the rest may come later
DECLARE fieldname CURSOR FOR 	
Select DISTINCT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE ,  Column_default, COLUMN_COMMENT, CHARACTER_MAXIMUM_LENGTH, 
CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, 
numeric_scale, substring(COLUMN_TYPE, 5, instr(COLUMN_TYPE, ')')-5) AS int_length
 FROM INFORMATION_SCHEMA.COLUMNS  t
WHERE TABLE_SCHEMA = Trigger_databasename AND TABLE_NAME = trigger_tablename AND EXISTS
(
Select NULL
 FROM INFORMATION_SCHEMA.COLUMNS  a
WHERE a.TABLE_SCHEMA = 'Audit_info' 
 AND a.TABLE_NAME = CONCAT(Trigger_databasename,'_DBAudit_',trigger_tablename)
  AND a.COLUMN_NAME = t.COLUMN_NAME
  AND ( a.CHARACTER_MAXIMUM_LENGTH <> t.CHARACTER_MAXIMUM_LENGTH
        OR a.CHARACTER_OCTET_LENGTH <> t.CHARACTER_OCTET_LENGTH
        OR a.NUMERIC_PRECISION <> t.NUMERIC_PRECISION
        OR a.numeric_scale <> t.NUMERIC_PRECISION
        OR substring(a.COLUMN_TYPE, 5, instr(a.COLUMN_TYPE, ')')-5) <> substring(t.COLUMN_TYPE, 5, instr(t.COLUMN_TYPE, ')')-5) 
         )
);

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39864052
"so, your initial query is to find "new" fields (I presume, to add them to the audit table ...)"

yes, but I have to modify it so that 2 x table with different name will have field compare head to head and see if the field LENGTH extended.

so when I do this:

FETCH fieldname INTO current_COLUMN_NAME, current_COLUMN_TYPE, current_IS_NULLABLE, current_Column_default, current_COLUMN_COMMENT, 
   current_CHARACTER_MAXIMUM_LENGTH, current_CHARACTER_OCTET_LENGTH, current_NUMERIC_PRECISION, current_numeric_scale, current_int_length ;	
         IF fieldnameCursor_finished = 1 THEN 

Open in new window


should I convert the current_int_length to number vaule before I can compare?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39864063
why "Select NULL"?


this one:

WHERE a.TABLE_SCHEMA = 'Audit_info' 
 AND a.TABLE_NAME = CONCAT(Trigger_databasename,'_DBAudit_',trigger_tablename)
  AND a.COLUMN_NAME = t.COLUMN_NAME

Open in new window


means now I compare to the same table in other dB? which I think about it too but not combine into signal statement.

this one:

 AND ( a.CHARACTER_MAXIMUM_LENGTH <> t.CHARACTER_MAXIMUM_LENGTH
        OR a.CHARACTER_OCTET_LENGTH <> t.CHARACTER_OCTET_LENGTH
        OR a.NUMERIC_PRECISION <> t.NUMERIC_PRECISION
        OR a.numeric_scale <> t.NUMERIC_PRECISION
        OR substring(a.COLUMN_TYPE, 5, instr(a.COLUMN_TYPE, ')')-5) <> substring(t.COLUMN_TYPE, 5, instr(t.COLUMN_TYPE, ')')-5) 
         )

Open in new window


means any of the field of that rows has changed then return it to the cursor's table ?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 1

Author Comment

by:marrowyung
ID: 39864069
one more thing:

AND EXISTS

Open in new window


should there fields in between AND and EXISTS? MySQL might complain about the syntax as it is looking for what EXITS in the result set of subquery.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39864084
first, let's sort out the syntax stuff about EXISTS():
http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39864239
I have just modify the field length of only one field but the query you gave me return a lot if field for me. but the one I changed is on the list.

now has to make the result set only return the field I changed .

any idea?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39864269
>now has to make the result set only return the field I changed .

well, the query will return all the fields for which there are differences, I must presume that your audit tables still have some differences, hence the query reports them ...

you could build a JOIN (instead of EXISTS)  to compare the data directly:
Select t.COLUMN_NAME, t.COLUMN_TYPE, t.IS_NULLABLE ,  t.Column_default, t.COLUMN_COMMENT
, t.CHARACTER_MAXIMUM_LENGTH, t.CHARACTER_OCTET_LENGTH, t.NUMERIC_PRECISION, t.numeric_scale, substring(t.COLUMN_TYPE, 5, instr(t.COLUMN_TYPE, ')')-5) AS int_length
, a.CHARACTER_MAXIMUM_LENGTH CHARACTER_MAXIMUM_LENGTH_audit, a.CHARACTER_OCTET_LENGTH CHARACTER_OCTET_LENGTH_audit, a.NUMERIC_PRECISION NUMERIC_PRECISION_audit, a.numeric_scale numeric_scale_audit, substring(a.COLUMN_TYPE, 5, instr(a.COLUMN_TYPE, ')')-5) AS int_length_audit

FROM INFORMATION_SCHEMA.COLUMNS  t
JOIN INFORMATION_SCHEMA.COLUMNS  a
    ON a.TABLE_SCHEMA = 'Audit_info' 
  AND a.TABLE_NAME = CONCAT(Trigger_databasename,'_DBAudit_',trigger_tablename)
  AND a.COLUMN_NAME = t.COLUMN_NAME
  AND ( a.CHARACTER_MAXIMUM_LENGTH <> t.CHARACTER_MAXIMUM_LENGTH
        OR a.CHARACTER_OCTET_LENGTH <> t.CHARACTER_OCTET_LENGTH
        OR a.NUMERIC_PRECISION <> t.NUMERIC_PRECISION
        OR a.numeric_scale <> t.NUMERIC_PRECISION
        OR substring(a.COLUMN_TYPE, 5, instr(a.COLUMN_TYPE, ')')-5) <> substring(t.COLUMN_TYPE, 5, instr(t.COLUMN_TYPE, ')')-5) 
         )

WHERE t.TABLE_SCHEMA = Trigger_databasename 
AND t.TABLE_NAME = trigger_tablename 

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39864367
"well, the query will return all the fields for which there are differences, I must presume that your audit tables still have some differences, hence the query reports them"

no, I just modify one field ! how can it report all ?

the new one you gave me return the same result set !
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39864370
I verify the field with same length on both table are also in the result set !! so something is not working good !
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39864386
I think I found out why:

your query match the wrong field:

      OR a.numeric_scale <> t.NUMERIC_PRECISION

Open in new window


it should be:


      OR a.numeric_scale <> t.numeric_scale 

Open in new window


same field, right !
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39864387
I just try to verify what if I roll back the change and the script should return nothing to me, I am right !
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39864394
now I change the key condition to :

 AND ( a.CHARACTER_MAXIMUM_LENGTH < t.CHARACTER_MAXIMUM_LENGTH
        OR a.CHARACTER_OCTET_LENGTH < t.CHARACTER_OCTET_LENGTH
        OR a.NUMERIC_PRECISION < t.NUMERIC_PRECISION
        OR a.numeric_scale < t.numeric_scale 
        OR substring(a.COLUMN_TYPE, 5, instr(a.COLUMN_TYPE, ')')-5) < substring(t.COLUMN_TYPE, 5, instr(t.COLUMN_TYPE, ')')-5) 

Open in new window


I only need to take care the situation where length extended, not shortened ! this should be smart enought!
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39864397
I am improving !
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39864405
glad I could help, sorry of the bad sql before ...
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

740 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