Submit a ticketCall us

Systems Monitoring for Dummies
Our new eBook will teach you the fundamentals and help you create monitors and alerts that are effective, meaningful, and actionable. Monitoring is more than a checkbox on your to-do list. This free eBook will give you practical advice to help you succeed in all aspects of monitoring – discovery, alerting, remediation, and troubleshooting. Don’t miss out on this indispensable resource for newbies, experienced IT pros, and everyone in between. Register Now.

Home > Success Center > Web Help Desk (WHD) > Database migration error during upgrade to Web Help Desk 12.0.0

Database migration error during upgrade to Web Help Desk 12.0.0

Created by Interspire Import, last modified by Steve.Hawkins on Oct 31, 2016

Views: 75 Votes: 0 Revisions: 11

Overview

The following error occurs when migrating a pre-version 12.0.0 Web Help Desk (WHD) embedded database to a WHD 12.0.0 platform:

An error occurred while migrating the database:

Error occurred during dump import, error code: 3 

 12.0.0_error.png

The following error message also appears in the whd-spring.log file:
 

ERROR c.s.whd.util.db.PostgreSQLDumpLoader - Error occured during dump import, error code: C:\Program Files\WebHelpDesk\temp\whd_fb2postgres_dump\TICKET_CUSTOM_FIELD_#EXPORTED#.sql.

ERROR c.s.whd.util.db.PostgreSQLDumpLoader - Error occured during dump import, error code: 3
java.io.IOException: Error occured during dump import, error code: 3

Environment

WHD 12.0.0

Cause

WHD had a previous problem with shrinking custom field texts stored in the SHORT_STRING_VALUE fields (max 255 characters). Installing WHD versions 10.2.0.38 (and later) fixes the issue in WHD application. However, old data in the database may not have been fixed. This data could be the causing errors during database migration.

Resolution

Update the short string value field values on the embedded FrontBase database of your pre-12.0.0 WHD installation by performing the following:

  1. Copy and paste the contents of the SQL script file into the SQL Interpreter in FrontBase Manager.
  2. Click Execute SQL.
  3. Rename the dump directory from \temp\whd_fb2postgres_dump to \temp\whd_fb2postgres_dump_bak or similar.
  4. Repeat the database migration. The error should no longer appear.

 


-- Fixes all short string values that contains invalid characters.
-- Updates the "short_string_value" field values in all the following tables:
--
-- asset_custom_field
-- client_custom_field
-- department_custom_field
-- location_custom_field
-- part_custom_field
-- po_custom_field
-- ticket_custom_field

UPDATE asset_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value)) WHERE OCTET_LENGTH(string_value) <= 255;
UPDATE asset_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value) + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) <= 255 AND OCTET_LENGTH(string_value) > 255;
UPDATE asset_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR 255 + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) > 255;

UPDATE client_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value)) WHERE OCTET_LENGTH(string_value) <= 255;
UPDATE client_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value) + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) <= 255 AND OCTET_LENGTH(string_value) > 255;
UPDATE client_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR 255 + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) > 255;

UPDATE department_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value)) WHERE OCTET_LENGTH(string_value) <= 255;
UPDATE department_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value) + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) <= 255 AND OCTET_LENGTH(string_value) > 255;
UPDATE department_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR 255 + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) > 255;

UPDATE location_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value)) WHERE OCTET_LENGTH(string_value) <= 255;
UPDATE location_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value) + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) <= 255 AND OCTET_LENGTH(string_value) > 255;
UPDATE location_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR 255 + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) > 255;

UPDATE part_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value)) WHERE OCTET_LENGTH(string_value) <= 255;
UPDATE part_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value) + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) <= 255 AND OCTET_LENGTH(string_value) > 255;
UPDATE part_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR 255 + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) > 255;

UPDATE po_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value)) WHERE OCTET_LENGTH(string_value) <= 255;
UPDATE po_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value) + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) <= 255 AND OCTET_LENGTH(string_value) > 255;
UPDATE po_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR 255 + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) > 255;

UPDATE ticket_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value)) WHERE OCTET_LENGTH(string_value) <= 255;
UPDATE ticket_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR CHAR_LENGTH(string_value) + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) <= 255 AND OCTET_LENGTH(string_value) > 255;
UPDATE ticket_custom_field SET short_string_value = SUBSTRING(string_value FROM 0 FOR 255 + CHAR_LENGTH(string_value) - OCTET_LENGTH(string_value)) WHERE CHAR_LENGTH(string_value) > 255;


 

Last modified

Tags

Classifications

Public