Unable to backup or restore when non-printable ASCII non-UTF symbols are present in the database or in xml dump file

Refers to:

  • Plesk 12.5 for Linux
  • Plesk 10.4 for Linux
  • Plesk 11.5 for Windows
  • Plesk 12.0 for Linux

Created:

2016-11-16 13:21:25 UTC

Modified:

2016-12-21 20:32:08 UTC

0

Was this article helpful?


Have more questions?

Submit a request

Unable to backup or restore when non-printable ASCII non-UTF symbols are present in the database or in xml dump file

Symptoms

Backup creation or restoration failed with the following error:

Failed to load XML document. 
Line xxx Position xxx fatal error: PCDATA invalid Char value 11

Cause

Non-printable ASCII non-UTF symbols are present in the dump.xml file

Resolution

There are three ways to fix it:

  1. Remove non-printable symbols from the dump.xml file manually:

1.1 Check the dump file to identify the specific records in the database causing the error:

    /usr/bin/xmllint  --noout --schema /usr/local/psa/PMM/plesk.xsd /var/lib/psa/dumps/<dump_file.xml>

Similar output will be shown:

/var/lib/psa/dumps/dump.xml:851: parser error : PCDATA invalid Char value 11
<pinfo name="address">John Smith
Company Name Ltd
^K

1.2 Now when the trouble record is found (address field in some table in the database near the word 'Company'), find this line in .xml file:

    <pinfo name="address">John Smith ^KCompany Name  Ltd</pinfo>

1.3 The non-printable ASCII symbol was interpreted during the backup as ^K. Remove this symbol '^K' from .xml file.

1.4 Proceed the same way with all non-printable ASCII records.

  1. Remove non-printable symbols from the psa database as data in the backup file is taken from the psa database directly:

2.1. Check the dump file to identify the specific records in the database causing the error:

    /usr/bin/xmllint  --noout --schema /usr/local/psa/PMM/plesk.xsd /var/lib/psa/dumps/<dump_file.xml>

Similar output will be shown:

parser error : PCDATA invalid Char value 11
<pinfo name="address">John Smith
Company Name Ltd
^

The address value is present in the psa.clients and psa.smb_users table.

2.2. Create a backup of the psa database:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin psa > ~/psa.`date +%F_%H:%M`.sql

2.3. Run the following query to identify client ID, in case it is not known:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -Ne "select id from clients where address like '%John Smith%'"

2.4. Remove all non-printable ASCII values from the psa.clients and psa.smb_users tables:

    MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -Ne "update clients set address = replace(address, unhex('0b'), '') where id=xx"
MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -Ne "update smb_users set address = replace(address, unhex('0b'), '') where id=xx"

Replace xx with a proper ID and replace ('0b') with a proper hex value. In our case decimal char 11 corresponds to the 0B hex value.

The command will remove all non-printable '0B' symbols from psa.clients and psa.smb_users tables.

Please use the table with ASCII symbols attached for reference.

  1. Remove all non-printable symbols from an .xml file:

3.1 Create a backup copy of .xml file:

    # cp /var/lib/psa/dumps/<dump_file.xml> ./

3.2 Use the following command to remove non-prontable ASCII symbols:

    # tr -cd '\\11\\12\\15\\40-\\176' < dump_file.xml > clean.xml

The ' tr ' command in combination with the ' diff ' command might be used for diagnostic purposes:

    # tr -cd '\\11\\12\\15\\40-\\176' < tttt_info_1404011521.xml > clean.xml
# diff tttt_info_1404011521.xml clean.xml
Have more questions? Submit a request
Please sign in to leave a comment.