Why is this SQL script only producing SQL for columns with the “varchar” data-type?

2 Views

Let me preface this by saying this script (at the bottom of this post) is not my work. A friend wrote it, and we found that it’s not working as we’d expect. I have zero knowledge of SQL.

Summary:

This SQL script (below) is supposed to produce results for tables with the char, text, tinytext, mediumtext, longtext, or varchar data-types. Instead, it is only producing results for varchar.

Read More

Intended purpose of this script:

When run on information_schema, generate a series of SQL commands that will, on each column:

  1. Cast the data as latin1
  2. Convert the data to binary
  3. Convert the binary data back to text, encoded in the UTF-8 character set.

I am trying to fix data in an old WordPress database, as instructed here. This script is supposed to automatically generate the SQL script I will use to convert all my columns.

Expected results when run:

Output that looks something like this for all tables with a data-type of char, text, tinytext, mediumtext, longtext, or varchar: 

[...]
alter table products change productName productName /*1*/ VARCHAR(70) character set latin1;
alter table products change productName productName /*2*/ VARBINARY(70);
alter table products change productName productName /*3*/ VARCHAR(70) character set utf8;
alter table products change productDescription productDescription /*1*/ LONGTEXT character set latin1;
alter table products change productDescription productDescription /*2*/ LONGBLOB;
alter table products change productDescription productDescription /*3*/ LONGTEXT character set utf8;
[...]

Actual results when run:

The script, when run on information_schema via phpMyAdmin, only creates commands for columns with the varchar data-type. Any other data-type (such as text or longtext) is ignored; no commands are created for tables with that data-type. 

[...]
alter table customers change addressLine1 addressLine1 /*1*/varchar(50) character set latin1; /* varchar latin1 */
alter table customers change addressLine1 addressLine1 /*2*/varbinary(50); /* varchar latin1 */
alter table customers change addressLine1 addressLine1 /*3*/varchar(50) character set utf8; /* varchar latin1 */
alter table customers change addressLine2 addressLine2 /*1*/varchar(50) character set latin1; /* varchar latin1 */
alter table customers change addressLine2 addressLine2 /*2*/varbinary(50); /* varchar latin1 */
alter table customers change addressLine2 addressLine2 /*3*/varchar(50) character set utf8; /* varchar latin1 */
[...]

Database used for testing:

classicmodels

The script:

select concat( 'alter table '
             , table_name
             , ' change '
             , column_name
             , ' '
             , column_name
             , ' /*1*/'
             , data_type
             , case
                  when data_type = 'varchar' then concat( '(', character_maximum_length, ')' )
                  end
             , ' character set latin1; /* '
             , data_type, ' ', character_set_name
             , ' */' ) as cmd
  from columns
 where table_schema = 'classicmodels'
   and data_type in ('char','text','tinytext','mediumtext','longtext','varchar')
   UNION
select concat( 'alter table '
             , table_name
             , ' change '
             , column_name
             , ' '
             , column_name
             , ' /*2*/'
             , case when data_type = 'char' then 'binary'
                  when data_type = 'text' then 'blob'
                  when data_type = 'tinytext' then 'tinyblob'
                  when data_type = 'mediumtext' then 'mediumblob'
                  when data_type = 'longtext' then 'longblob'
                  when data_type = 'varchar' then 'varbinary'
                  end
             , case
                  when data_type = 'varchar' then concat( '(', character_maximum_length, ')' )
                  end
             , '; /* '
             , data_type, ' ', character_set_name
             , ' */' ) as cmd
  from columns
 where table_schema = 'classicmodels'
   and data_type in ('char','text','tinytext','mediumtext','longtext','varchar')
   UNION
select concat( 'alter table '
             , table_name
             , ' change '
             , column_name
             , ' '
             , column_name
             , ' /*3*/'
             , data_type
             , case
                  when data_type = 'varchar' then concat( '(', character_maximum_length, ')' )
                  end
             , ' character set utf8; /* '
             , data_type, ' ', character_set_name
             , ' */' ) as cmd
  from columns
 where table_schema = 'classicmodels'
   and data_type in ('char','text','tinytext','mediumtext','longtext','varchar')
   order by 1;

What do I need to change in the script to make it properly run on all data-types?

Thank you.

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. This is an interesting script.

    It appears that you’re not getting the non-VARCHAR() types because the CASE statements which CONCAT() the varchar’s character_maximum_length do not have an ELSE case, and I think those rows are therefore being excluded from the UNION output. A quick test shows that I can add an ELSE '' to return an empty string when case when data_type = 'varchar' is not matched:

    For example:

             , data_type
         , case
              when data_type = 'varchar' then concat( '(', character_maximum_length, ')' )
              /* Here.... */
              else ''
              end
         , ' character set latin1; /* '
         , data_type, ' ', character_set_name

    You’ll need to add that in a few places, three in all. The whole script becomes:

    select concat( 'alter table '
             , table_name
             , ' change '
             , column_name
             , ' ' 
             , column_name
             , ' /*1*/'
             , data_type
             , case
                  when data_type = 'varchar' then concat( '(', character_maximum_length, ')' )
                  else ''
                  end
             , ' character set latin1; /* '
             , data_type, ' ', character_set_name
             , ' */' ) as cmd 
  from columns
 where table_schema = 'test'
   and data_type in ('char','text','tinytext','mediumtext','longtext','varchar')
   UNION
select concat( 'alter table '
             , table_name
             , ' change '
             , column_name
             , ' ' 
             , column_name
             , ' /*2*/'
             , case when data_type = 'char' then 'binary'
                  when data_type = 'text' then 'blob'
                  when data_type = 'tinytext' then 'tinyblob'
                  when data_type = 'mediumtext' then 'mediumblob'
                  when data_type = 'longtext' then 'longblob'
                  when data_type = 'varchar' then 'varbinary'
                  end
             , case
                  when data_type = 'varchar' then concat( '(', character_maximum_length, ')' )
                  else ''
                  end
             , '; /* '
             , data_type, ' ', character_set_name
             , ' */' ) as cmd 
  from columns
 where table_schema = 'test'
   and data_type in ('char','text','tinytext','mediumtext','longtext','varchar')
   UNION
select concat( 'alter table '
             , table_name
             , ' change '
             , column_name
             , ' ' 
             , column_name
             , ' /*3*/'
             , data_type
             , case
                  when data_type = 'varchar' then concat( '(', character_maximum_length, ')' )
                  else ''
                  end
             , ' character set utf8; /* '
             , data_type, ' ', character_set_name
             , ' */' ) as cmd 
  from columns
 where table_schema = 'test'
   and data_type in ('char','text','tinytext','mediumtext','longtext','varchar')
   order by 1;

    I loaded your test database and ran the test script against information_schema. It produced the following output (abbreviated):

    ...SNIP -- a text column....
alter table orders change comments comments /*1*/text character set latin1; /* text latin1 */
alter table orders change comments comments /*2*/blob; /* text latin1 */
alter table orders change comments comments /*3*/text character set utf8; /* text latin1 */
alter table orders change status status /*1*/varchar(15) character set latin1; /* varchar latin1 */
alter table orders change status status /*2*/varbinary(15); /* varchar latin1 */
alter table orders change status status /*3*/varchar(15) character set utf8; /* varchar latin1 */
...SNIP -- a mediumtext column...
alter table productlines change htmlDescription htmlDescription /*1*/mediumtext character set latin1; /* mediumtext latin1 */
alter table productlines change htmlDescription htmlDescription /*2*/mediumblob; /* mediumtext latin1 */
alter table productlines change htmlDescription htmlDescription /*3*/mediumtext character set utf8; /* mediumtext latin1 */
...SNIP -- another text column....
alter table products change productDescription productDescription /*1*/text character set latin1; /* text latin1 */
alter table products change productDescription productDescription /*2*/blob; /* text latin1 */
alter table products change productDescription productDescription /*3*/text character set utf8; /* text latin1 */
...SNIP --etc...
alter table products change productVendor productVendor /*1*/varchar(50) character set latin1; /* varchar latin1 */
alter table products change productVendor productVendor /*2*/varbinary(50); /* varchar latin1 */
alter table products change productVendor productVendor /*3*/varchar(50) character set utf8; /* varchar latin1 */