Submit a ticketCall us

WebinarUpcoming Webinar: How Help Desk and Remote Support Pays for Itself

Learn how help desk software can simplify ticketing management, allow you to track hardware and software assets, and accelerate the speed of IT support and service delivery. Gain insights on how remote support tools allow your IT team to maximize their efficiency and ticket resolution by expediting desktop troubleshooting, ultimately helping keep end-users happy and productive.

Register here.

Home > Success Center > Web Help Desk (WHD) > WHD - Knowledgebase Articles > 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: 1,117 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