PMAC Upgrade Failed from 6.0.3 to PMAC 6.4 with ERROR 1067 (42000) in MySQL Phase

Asset ID:	1-72-2312508.1
Update Date:	2017-09-29
Keywords:

Solution Type Problem Resolution Sure

Solution 2312508.1 : PMAC Upgrade Failed from 6.0.3 to PMAC 6.4 with ERROR 1067 (42000) in MySQL Phase

Applies to:

Oracle Communications Diameter Signaling Router (DSR) - Version DSR 7.2.0 to DSR 8.1.1 [Release DSR 7.0 to DSR 8.0]
Information in this document applies to any platform.

Symptoms

During PMAC Upgrade failed from Rel 6.0.3.0.2-60.28.0 to PM&C 6.4.0.0.0-64.8.0, this is caused in the Post Script which is running Post Upgrade.

If you Check Upgrade Log /var/TKLC/log/upgrade/upgrade.log , Following is the Sequence of Error should be Displayed, Where it complains about Invalid Default for "Last_Password_Change"

1505976159::Version in the dbschema table updated.
1505976159::
1505976159::Current schema version is 6200
1505976160::
1505976160::DB Upgrade from 6200 to 6201
1505976160::
1505976160::mysql: [Warning] Using a password on the command line interface can be insecure.
1505976160::ERROR 1067 (42000) at line 866: Invalid default value for 'last_password_change'
1505976160::ERROR: Could not run system. cmd=[/usr/bin/mysql -p$(/usr/TKLC/smac/sbin/gcs) < /usr/TKLC/smac/html/sql/db_upgrade_6200_6201.sql]
1505976160::
1505976160::Stopping MySQL... [OK]
1505976164::
1505976164::ERROR: db_upgrade_schema failed! rc = 1
1505976164::ERROR: postTransactions() code failed for Upgrade::Policy::PMAC

Changes

Cause

This is caused by the fact that 1970-01-01 00:00:00 is epoch 1. If the site has been configured for UTC + 5.5 hours. In this scenario mysql saves data, it is written as UTC into the database. During data retrieval it is converted back to the current timezone.

When the script tries to create the table "userNew" it attempts to write it as epoch - 5.5 hours to convert it to UTC. This causes the epoch number to be negative which is invalid. Hence the Error is seen in the Upgrade Logs.

Solution

All you have to do get on the PMAC console from the TVOE HOST and change your time-zone on the PMAC (not MySQL) to UTC.

Execute the upgrade and then change it back to the timezone which it was using.

You need to make sure your global time_zone variable on your PMAC is set to SYSTEM for this to work.

READ CAREFULLY BEFORE EXECUTING THESE

The steps are as follows:

Login to the PMAC via the TVOE HOST Using virsh:

virsh:virsh console <pmac_name>
Set your level to root:

sudo su -
Go to MySQL and verify the global system variable is set to SYSTEM. Both of these should display the time_zone as SYSTEM. If not then these need to be set to SYSTEM.Display them again to make sure they are set properly.

/usr/bin/mysql -psmp-$((6**8))
show global variables like 'time%';
show session variables like 'time%';
Execute only If time_zone is not displayed as system
set GLOBAL time_zone=SYSTEM;
set SESSION time_zone=SYSTEM;
Make sure there are no entries in the SmacGUI.user table that have a timestamp of '0000-00-00 00:00:00':If any of these have a timestamp of 0 like above then it must be changed.Make sure it is no longer 0.

select * from SmacGUI.user;
update SmacGUI.user set <column_name>=NOW() where username='<user_name>';
Exit out of mysql
Now change the system timezone to UTC:

su - platcfg
select the Server Configuration->Time Zone->Edit
Search for UTC and select that value.
Exit out of platcfg
At the PMAC Shell check to see if the time zone is set right.These values should indicate UTC and be pretty close to the same value. They may differ by a second or two.

date
date -u
Do the normal clearing of the platform_revisions file and the alarms and syscheck.
Once the upgrade completes, you have to go back and set your time zone back to what it was originally from platcfg.

Attachments

This solution has no attachment