Alter a MySQL column in all databases

When you have a series of applications all running the same database structure it can be annoying to roll out schema updates across all the databases. If you’ve got migrations then great - script their deployment, but when you’re dealing with an old legacy application you probably don’t have the luxury.

I was firmly in the latter class of devops when working on a project a couple of years ago so I wrote a handy little snippet of SQL to help me out. It allowed me to automatically assemble an ALTER TABLE query for MySQL - it’s gonna be a hack in case you’d not already guessed.

It is MySQL specific so to make it portable across MySQL installations I have used backticks to escape identifiers rather than the double quotes I advocate in the SQL style guide. tl;dr: double quotes as escape is not enabled in MySQL by default.

This query hinges on the information_schema tables and the GROUP_CONCAT functionality provided in MySQL, which will allow us to create one big query file in tandem with OUTFILE.

So without further ado here is the SQL code:

SET SESSION group_concat_max_len = 1000000;

SELECT `query`
  FROM (SELECT GROUP_CONCAT(
                   CONCAT('ALTER TABLE `', `isc`.`table_schema`, '`.`', `isc`.`table_name`, '` ALTER `', `isc`.`column_name`, '` DROP DEFAULT;', '\n'),
                   CONCAT('ALTER TABLE `', `isc`.`table_schema`, '`.`', `isc`.`table_name`, '` CHANGE COLUMN `', `isc`.`column_name`, '` `', `isc`.`column_name`, '` VARCHAR(300) NOT NULL;')
                   SEPARATOR '\n'
               ) AS `query`,
               1 AS `groupbyme`
          FROM `information_schema`.`columns` AS `isc`
         WHERE `isc`.`table_schema` NOT IN ('information_schema', 'mysql', 'performance_schema')
           AND `isc`.`table_name` = 'users'
           AND `isc`.`column_name` = 'ipAddress'
         GROUP BY `groupbyme`
       ) AS T
   INTO OUTFILE '/tmp/alter_table.sql'
       FIELDS TERMINATED BY '\n'
       OPTIONALLY ENCLOSED BY ''
       ESCAPED BY ''
       LINES TERMINATED BY '\n';

Great, you say, but what does it do? Well here is a brief breakdown of its constituent components.

SET SESSION group_concat_max_len = 1000000;

By default MySQL has a much smaller GROUP_CONCAT maximum length and as we could be operating on a lot of tables (meaning many results to concatenate) we need to up this default for our particular query.

SELECT `query`
  FROM (SELECT GROUP_CONCAT(
                   CONCAT('ALTER TABLE `', `isc`.`table_schema`, '`.`', `isc`.`table_name`, '` ALTER `', `isc`.`column_name`, '` DROP DEFAULT;', '\n'),
                   CONCAT('ALTER TABLE `', `isc`.`table_schema`, '`.`', `isc`.`table_name`, '` CHANGE COLUMN `', `isc`.`column_name`, '` `', `isc`.`column_name`, '` VARCHAR(300) NOT NULL;')
                   SEPARATOR '\n'
               ) AS `query`

Here GROUP_CONCAT concatenates the results of the queries together separated by a newline character \n. Inside there are two CONCAT statements that are building two ALTER TABLE queries. The first removes a DEFAULT declaration from a column and the second changes column to be a VARCHAR of 300 characters in length.

               1 AS `groupbyme`

This simply creates a column that whole lot can easily be grouped by as every row is ascribed the same value of 1.

         WHERE `isc`.`table_schema` NOT IN ('information_schema', 'mysql', 'performance_schema')
           AND `isc`.`table_name` = 'users'
           AND `isc`.`column_name` = 'ipAddress'

We don’t want to accidentally perform actions against the information_schema or other internal MySQL tables so we exclude them here. To ensure we only operate on the correct table we then specify its name and the relevant column name. This way we can be sure that the DB has our target table name in it and that the target table has our target column name in it.

         GROUP BY `groupbyme`

This refers to the simple column we setup earlier - it meant all rows had a value of 1 allowing the results to be easily grouped.

       ) AS T

After all that the result must be set against an alias so for no particular reason I chose T here.

   INTO OUTFILE '/tmp/alter_table.sql'
       FIELDS TERMINATED BY '\n'
       OPTIONALLY ENCLOSED BY ''
       ESCAPED BY ''
       LINES TERMINATED BY '\n';

Finally, the results are written to a file on disk with a few custom options. Importantly, the field are prevented from being enclosed or escaped - if these were left enabled then MySQL would break our query output by escaping new lines.

Now all you have to do is run contents of the file we just created and the ALTER TABLE queries will be executed against your database.