Deploying MySQL updates
At some point every developer has to address the elephant in the project, the database. MySQL/MariaDB is the preferred database in the PHP world. It is the ‘M’ in LAMP. But most developers only grudgingly learn enough about MySQL to make it work. Even fewer become proficient at it. Less still master it. In this post we will delve into some techniques for maintaining and deploying MySQL updates.
Then a Miracle Occurs
I have seen many good CI/CD implementations, with good testing and code deployment strategies. In these same good CI/CD implementations I have seen a “then a miracle occurs” approach to the database.
Deploying MySQL updates Rules
Lets start creating a database deployment strategy by setting up some simple base rules:
- All script must be runnable/re-runnable without damaging the current schema or data.
- All script files names must start with 5 numeric digits plus a descriptive name.
- All currently active script must live in a well-known directory like “sql”
- After script have been deployed for awhile they should move to an “old” directory.
- Optional script, like test data, should live in an “optional” directory.
These rules will help take some of the “fear” out of maintaining the the database.
Rule 1: Re-runnable SQL scripts
Starting with rule number 1 we need to write our sql scripts with a particular style.
Creating Tables
Our create tables statements should look something like:
|
1 2 3 4 5 6 |
CREATE TABLE IF NOT EXISTS oauth_scopes ( type VARCHAR(255) NOT NULL DEFAULT "supported", scope VARCHAR(2000), client_id VARCHAR (80), is_default SMALLINT DEFAULT NULL ); |
Note the “IF NOT EXISTS” creates the new table only if the tables does not yet exists.
Inserting Rows
If the table has a primary key you can insert row in your script like:
|
1 |
INSERT IGNORE INTO `oauth_users` VALUES ('test','$2y$14$f3qml4G2hG6sxM26VMq.geDYbsS089IBtVJ7DlD05BoViS9PFykE2','test','user'); |
Inserts a new value but ignores errors if the primary key already exists. Variation include using “WHERE” clauses and using the “UPDATE” MySQL statement.
Altering Tables
Several techniques exists, but this one is a good example of the concept:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT count(*) INTO @exist FROM information_schema.columns WHERE table_schema = database() and COLUMN_NAME = 'original_data' AND table_name = 'mytable'; set @query = IF(@exist <= 0, 'alter table intent add column mycolumn4 varchar(2048) NULL after mycolumn3', 'select \'Column Exists\' status'); prepare stmt from @query; EXECUTE stmt; |
Variations include altering column sizes change changing column types.
Triggers
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DROP TRIGGER IF EXISTS before_delete_concept_access; DELIMITER // CREATE TRIGGER before_delete_concept_access BEFORE DELETE ON `concept_access` FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM concept_access_log WHERE map=OLD.map AND accesstype=OLD.accesstype AND startdate=OLD.startdate AND stopdate=OLD.stopdate) = 0 THEN INSERT INTO concept_access_log (map, accesstype, startdate, stopdate) VALUES (OLD.map, OLD.accesstype, OLD.startdate, OLD.stopdate); END IF; END// DELIMITER ; |
Re-Creating triggers that already exists should be safe.
Going Further
These techniques show the basic of re-runnable MySQL scripts. By combining these techniques you should be able to write scripts that can be run and re-run safely allowing anyone to run these scripts at will without worry of damaging the existing database.
RULE 2: MySQL script file names
When sorted we want our script file to list in the order they should run. To achieve this we will left pad our filename with numeric digits.
|
1 2 3 |
-rwxr-xr-x 1 jstormes jstormes 2040 May 15 15:37 0001_CreateStartingTables.sql -rwxr-xr-x 1 jstormes jstormes 32 May 19 17:19 0002_AddStatusColumn.sql -rwxr-xr-x 1 jstormes jstormes 57 May 19 17:19 0003_ExpandPasswordColumn.sql |
RULE 3: SQL Directory
Deploying MySQL Updates Directory Structure
The “sql” directory is where to put the MySQL script files. This is the well-known location so all developers know where to look for the SQL script files.
RULE 4: The “Old” Directory
The “Old” directory is for SQL scripts that are no longer needed. This is also a well-know location so all the team developers know where the old files are.
RULE 5: The “Optional” Directory
The “Optional” directory is for SQL script that load test data. Again, all the team developers should know where this directory is.
Automation
The shell scripts updatedb.sh and updatedb.ps1 are Bash and Windows PowerShell scripts for running all the MySQL scrips in the directory in order. This script can be used in the Continuous Delivery process to deploy the scrips. As any script can be run at any time without worry, running the updatedb script will not damage the target database.
Conclusion of Deploying MySQL updates
This general ideas in this post give you methods to maintain your SQL alongside your code, leveraging your Git repository to maintain a history of database changes over time. This also lets other developers working on the project to setup their own instances of the database.