Solved

The checking of MySQL field length

Posted on 2014-02-16
14
447 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 142

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 142

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 142

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 142

Expert Comment

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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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