DDMDB Migration Script Fails with MYSQL error.

Products

• CA Spectrum
• CA eHealth

Issue/Introduction

DDMDB Migration scripts fails on Windows Environment. 

Error :

Starting event partitioning
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
        )' at line 17
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1600337809,NULL)' at line 1
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') )' at line 1

 

This happens with both the script

convert_current_myisam_to_innodb.pl

convert_saved_myisam_to_innodb.pl 

 

Environment

Release : 10.4.1 and above

Component : Spectrum Core / SpectroSERVER

Cause

- The problem happens at like 261 and 326. 

Line 261  $PARTITION_TIME = POSIX::mktime(0,0,0,$DAY,$MONTH-1,$YEAR-1900,0,0,1);
Line 326  $PARTITION_TIME = POSIX::mktime(0,0,0,$DAY,$MONTH-1,$YEAR-1900,0,0,1);

The POSIX::mktime is not returning value. It returns blank and due to which the create event table command fails. 

 

- There were changes to Perl version on latest Spectrum releases and it is not accepting POSIX::mktime query. 

 

 

Resolution

This is currently under Development Review(DE479700). This will be fixed in future releases. 

 

You can use a WorkAround to fix this. 

---------------------------------------------------------

1) Edit the script $SPECROOT\SS\DDM\Scripts\convert_current_myisam_to_innodb.pl

2) In line 17 below use thread;  add this line.

use Time::Local;

3) In line 261 and 326. Replace POSIX::mktime with timelocal

Entry Before Editing.

$PARTITION_TIME = POSIX::mktime(0,0,0,$DAY,$MONTH-1,$YEAR-1900,0,0,1);

 

Entry After Change. 

$PARTITION_TIME =timelocal(0,0,0,$DAY,$MONTH-1,$YEAR-1900,0,0,1);

 

4) Save the file. Now you can run the migration script.