Database Upgrade Scripts Process

— Colm Carew 2018/05/17 08:11

The scripts are located in each project that the scripts need to be run for i.e. Snmp Manager upgrade scripts are in the Snmp Manager codebase

Presentation on this : https://docs.google.com/presentation/d/1Yv7KJqZBW4f90VWs9tpXdU1xn6j4hwy9cGZzeNlGnWY/edit#slide=id.p4

• Adding a column/foreign key has logic to check does the column already exists before adding it - see below for example
• Dropping a column/foreign key has logic to check does the column exist before dropping it
• Creating a table has if not exists statement
• Drop table has an if exists statement
• INSERTS specify the order and values i.e. INSERT INTO table(col1, col2) values(1,2) is valid. INSERT INTO table values(1,2) IS NOT VALID.(A quick way to generate insert statements in the correct ordering is show below in section Generate INSERTS from Workbench.)
• UPDATE/DELETE statements should have a where clause in most cases, not necessarily all cases.
• ids should not be inserted or update where possible, MySQL should be left to autogenerate the ids
• specific customer data should be wrapped in if customer name condition
• when inserting into a table with a version, remember to set that to 0 in the insert
• Ensure INSERTS/UPDATES/DELETES are grouped and wrapped inside “START TRANSACTION;” and “COMMIT;”

• Flyway is being used to automatically run the SQL scripts in all our deployed projects (if not, contact Colm)
• Currently we use older plugin to get free usage of it with MySQL 5.1 (it also works with 5.5 and onwards)
• There is a naming convention for scripts :

  Vnumber__description_Ticket_Num.sql

  e.g. :

  V5__New_Feature_IDMS_1234.sql

• The format is V followed by the next number, followed by 2 underscores and then a short description

It depends on the project for Grails 2 and 3 they are in :

grails-app/conf/db/migration

For Spring Boot apps they are in :

src/main/resources/db/migration

• A channel has been created which you should join called sql-upgrade-scripts
• When you want to make a new script send a message out in this channel say you are for instance making a new script in the Ticketer, a message would look like :

  "Taking V4 for Ticketer for IDMS-1234"

• This makes life easier so if we are both working on the Ticketer I can then take V5 and not V4

• Never ever ever ever ever ever ever ever ever….ever edit a script once it has been pushed and run on production…ever
• In general, always make a new script if you need to do something, if you edit the script flyway will know via the checksum and prevent the application from starting!
• For customer specific inserts/updates we are making a new table in every schema called “errigal_customer” which has a single column called name which will store the name of the customer, use this in a stored procedure to run your inserts
• Try keep the DB backwards compatible for up to 2 releases so rolling back of the DB is never needed. To do this, if a column should be dropped make a ticket to drop this column in the next release or for the one after.

/* For Customer Specific Inserts/Updates we need to use a Stored Procedure*/
DELIMITER //
DROP PROCEDURE IF EXISTS load_of_dummy_inserts //
CREATE PROCEDURE load_of_dummy_inserts()

BEGIN
 SELECT @customerName := name from errigal_customer limit 1;
 IF @customerName = 'EXT' THEN
 CREATE TABLE if not exists colm_test (
   id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   firstname VARCHAR(30) NOT NULL,
   lastname VARCHAR(30) NOT NULL
);
   INSERT INTO colm_test(firstname, lastname) values('Colm', 'Carew');
 END IF;
END; //

CALL load_of_dummy_inserts() //
DROP PROCEDURE IF EXISTS load_of_dummy_inserts //
DELIMITER ;

CREATE TABLE IF NOT EXISTS errigal_customer (
  name VARCHAR(255) NOT NULL
);

INSERT INTO errigal_customer(name) values(CUST_ABBREVIATION);

1. run the app with ddl_auto set to update
2. export the DDL for newly created tables (or extract the new columns for ALTER commands)
3. make sure the tables/constraints order is ok in the script (e.g. not having the constraint for a table before creating it)
4. for any apps that are using table based sequences (e.g. fiber, the tables usually have the _SEQ sufix), provide inserts for the sequence table (look at V4__IDMS-2805_Demarcation.sql for more inspiration)
5. prepare your Flyway script (it is also recommended to create a rollback script for further steps)
6. stop the application
7. rollback the changes (possibly using the rollback script created above)
8. put your Flyway script into the right location of the project (documented in the sections above)
9. run the app again, verify logs for Flyway messages (upgrading the schema) and possibly test the new tables via application
10. repeat until it all works

These rules are elaboration of what has been mentioned Dos and Don'ts above. The main goal of this is to make sure the changes are backwards compatible so we can rollback the application without having to rollback the DB.

• Never change a name of a table or column in the DB
  • if this is really needed then you should create a new field and make the current application work with both fields (writing to both of them) until the old field gets deprecated
• Never change a type of a column (unless increasing size of the string types)
  • if this is really needed then you should create a new field (with new type) and make the current application work with both fields (writing to both of them, reading from the new) until the old field gets deprecated (more on that below)
• Never drop a table or a column (unless it is time to deprecate it - more on that below)
• You should generally only add new columns and tables

If you think some of the fields/tables has to be changed or dropped, they should be handled us mentioned above. Additionally we would need to mark these fields as deprecated somewhere (there is no current example of that yet, but the best place seems to be the README.md in each project). The record could have following structure:

• Field/Table, sample: network_element.auditable
• Deprecated in, sample: 3.4
• To be removed in, sample: 3.6
• Action, sample: Remove from the domain, create flyway script to drop the column, inform all customers about the change (ahead)

Always make sure the column does not already exists first

DELIMITER //
DROP PROCEDURE IF EXISTS add_column //
CREATE PROCEDURE add_column() 

BEGIN
	DECLARE colName TEXT;
	SELECT column_name INTO colName
	FROM information_schema.columns 
	WHERE table_schema = 'the_database'
	    AND table_name = 'the_table'
	AND column_name = 'column_to_add';

	IF colName is null THEN 
	    ALTER TABLE the_table ADD COLUMN the_column THE_TYPE;
	END IF; 
END//

CALL add_column() //

DROP PROCEDURE IF EXISTS add_column //
DELIMITER ;

Only do this if the field has not been used for 2 or more releases ago

DELIMITER //
DROP PROCEDURE IF EXISTS remove_column //
CREATE PROCEDURE remove_column() 

BEGIN
	DECLARE colName TEXT;
	SELECT column_name INTO colName
	FROM information_schema.columns 
	WHERE table_schema = 'the_database'
	    AND table_name = 'the_table'
	AND column_name = 'column_to_drop';

	IF colName is not null THEN 
	    ALTER TABLE the_table DROP COLUMN the_column;
	END IF; 
END//

CALL remove_column() //

DROP PROCEDURE IF EXISTS remove_column //
DELIMITER ;

In MySQL workbench you can export an insert statement, which gives you the full column order for that table if you are unsure of your insert statement. To get this select a row from a table for example:

select * from ticketer.ticket where id = 10;

In the results you will see above the row a save icon with the name Export/Import as show below: Click this and select the Format of SQL INSERT statements to save the sql export as:

Open the exported file and remove the smart quotes around column names and also remove the id column and its associated value. Set the version number to 0 for the insert you are creating and you should have a correctly ordered and formatted insert statement to use for the table you exported.

To troubleshoot the Flyway scripts - make sure to understand how it works by reading How Flyway works - it will great help you fix the problems. Flyway logs it's initialisation with following logs, so that is the first place to look at when something doesn't work as expected:

2018-09-14 11:07:42.427  INFO 74355 --- [  restartedMain] o.f.core.internal.util.VersionPrinter    : Flyway 4.2.0 by Boxfuse
2018-09-14 11:07:42.782  INFO 74355 --- [  restartedMain] o.f.c.i.dbsupport.DbSupportFactory       : Database: jdbc:mysql://localhost:3306/fiber?useUnicode=yes&characterEncoding=UTF-8 (MySQL 5.1)
2018-09-14 11:07:42.861  INFO 74355 --- [  restartedMain] o.f.core.internal.command.DbValidate     : Successfully validated 4 migrations (execution time 00:00.023s)
2018-09-14 11:07:42.881  INFO 74355 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : Current version of schema `fiber`: 4
2018-09-14 11:07:42.882  INFO 74355 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : Schema `fiber` is up to date. No migration necessary.

Flyway allows the application to run in the rollback scenario - e.g. if the DB is version 4, but the app has just version 3 it will run anyway. Flyway will fail to start in following situations:

• Any of the migrations (records in schema_version) in the DB is different to what is in the application (so changing already applied scripts will cause this)
  • This should generally only happen locally (otherwise it is very bad) for 2 reasons - you changed some of the older scripts, or you changed the last script that you are developing
  • Solution (only locally for the last script, which you are developing) is to manually rollback all the changes performed by the script and delete the last record in the schema_version table, fix the script and run the app again
• The new script fails for some reason on the DB level (typo, bad column names, etc.)
  • The same rules/solutions apply as for previous failure