12D: Upgrade MySql 5.5 temporals

This weekend I converted all temporals in the Data Warehouse from pre-version 5.6 to the version 5.6 internal

format. It affected about 600 tables, the conversion went hassle free ‘ALTER TABLE FORCE’

on every of those 600 did the trick. Happily I went to the office this morning. First thing that happened after

starting the PC I got a chat message ‘We have a problem with an application all part names are missing

and there are no vendor info either. I almost died, what had I done this time? I had checked as carefully

I could for any errors during the temporals conversion and there was none. When i had calmed down a bit I

digged into the application and found the material master for a factory was empty. At the same time

the BI developer who contacted me called and told me the material master is empty and it is because we

archived the old material master, it is actually used when we update the material master, can we restore it.

Oh that's a relieve, this time I didn’t fuck up.

It turned out the old material master was from my own old ERP system, that was replaced by SAP 2006.

It came as a big surprise it was in use. We restored the old material master and rerun the material master

update job and we were back in business. But we will remove the old material master from all jobs and

then archive it again as part of the preparation for the Mysql 5.6 to 5.7 conversion.

The weekend temporal conversion was simple but it took quite a lot of reading, finding what the problem

was how to identify tables affected and then run this long running conversion. This SQL script found affected

tables and created ‘ALTER TABLE’ statements for each of the tables:

SET show_old_temporals = ON;

SELECT distinct concat('ALTER TABLE ',a.table_schema,'.',a.table_name,' FORCE;')

FROM information_schema.columns a

join information_schema.tables b on b.table_schema = a.table_schema and b.table_name = a.table_name

WHERE a.column_type LIKE '%time% /* 5.5 binary format */' and b.TABLE_TYPE = 'BASE TABLE'

;

Then it was just a matter of cut and paste these statements into an ITL job and run it:

<?xml version='1.0' encoding='UTF-8' standalone='yes'?>

<sql>

ALTER TABLE FEVENTEST.PD013PTD_STOCK FORCE;

ALTER TABLE FEVENTEST.PD014PTD_OPEN_ORDERS FORCE;

ALTER TABLE FEVENTEST.PD015PTD_REC_ORDERS FORCE;

ALTER TABLE FEVENTEST.PD016PTD_STOCK FORCE;

ALTER TABLE FEVENTEST.PD017PTD_INV_ORDERS FORCE;

...

</sql>

</job>

</schedule>

By this conversion I hope the 5.7 upgrade conversion time is cut by a lot.

2018-09-10