Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

The checking of MySQL field length

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
marrowyung
Asked:
marrowyung
  • 10
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
marrowyungAuthor Commented:
"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
 
marrowyungAuthor Commented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
marrowyungAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
marrowyungAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
marrowyungAuthor Commented:
"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
 
marrowyungAuthor Commented:
I verify the field with same length on both table are also in the result set !! so something is not working good !
0
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
I just try to verify what if I roll back the change and the script should return nothing to me, I am right !
0
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
I am improving !
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad I could help, sorry of the bad sql before ...
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now