PT- Archiver Error

MySQL & MariaDB
, ,
1

Hi,
Getting below error from PT-Archiver…

Exhausted retries while deleting; rolling back 0 rows can’t call method “rollback” on an undefined value at /bin/pt-archiver line 7185.

Please suggest !!

2

Hello BKB,
Could you please provide more information, such as the command you ran at the time of issue?

Regards,
Denis Subbota.
Managed Services, Percona.

3

Hi Denis,

Thanks !!

The issue have been resolved now using increasing the retry settings.

4

Hi Denis,

Good Morning !!

Thanks for your support PT Archiver error we faced earlier and resolved and informed you in previous emails.

We are getting error below for PT Archiver of different table.

Unicode non-character U+FFFF is illegal for open interchange at /bin/pt-archiver line 6923.

Any thought please share how to fix this error.

5

Hi @Denis_Subbota ,

Any update ?

6

You can try to add --charset=UTF8 flag for your command

7

@Denis_Subbota As per suggestion , we used --charset=utf8 but same error . Please find below script

pt-archiver --source h=localhost,u=$USERNAME,p=$PASSWORD,D=$DATABASE,t=$TABLE --where ‘AUDIT_DATE <(NOW() - INTERVAL 240 DAY)’ --charset=utf8 --limit=5000 --commit-each --file ${ARCHIVAL_PATH}/%Y-%m-%d-%D.%t 2>> $LOGPATH/arch_cp.err

8

@Denis_Subbota , Requesting …for an update please

9

The error you’re encountering, “Unicode non-character U+FFFF is illegal for open interchange,” indicates that the data you are trying to archive includes an invalid Unicode character (U+FFFF), which is not allowed in standard Unicode interchange. This character may have been introduced inadvertently and needs to be handled appropriately.

Here are some steps and suggestions to help you fix this issue:

  1. Identify the Problematic Data

First, identify which rows contain the illegal Unicode character. You can run a query to find any rows containing the U+FFFF character:

SELECT * 
FROM your_table 
WHERE your_column LIKE CONCAT('%', CHAR(0xFFFF), '%');

Replace your_table and your_column with the appropriate table and column names.
2. Clean the Data

Once you identify the rows with the problematic character, you can either clean or remove the character. To clean the data, you can update the column to remove or replace the character. For example, to remove U+FFFF:

UPDATE your_table 
SET your_column = REPLACE(your_column, CHAR(0xFFFF), '')
WHERE your_column LIKE CONCAT('%', CHAR(0xFFFF), '%');
  1. Modify the pt-archiver Command

If the problematic data cannot be cleaned easily, you can modify your pt-archiver command to handle such cases. Ensure that your charset setting matches your database’s character set. Additionally, you might need to handle such characters programmatically or through additional preprocessing steps before running pt-archiver.
4. Use Character Encoding Properly

Make sure that the character encoding you are using in pt-archiver matches the database encoding. In your case, you have already tried --charset=utf8. If the issue persists, it might be due to the encoding mismatch.
Example pt-archiver Command

Here’s your pt-archiver command with the suggested flag for charset:

pt-archiver --source h=localhost,u=$USERNAME,p=$PASSWORD,D=$DATABASE,t=$TABLE --where 'AUDIT_DATE < (NOW() - INTERVAL 240 DAY)' --charset=utf8 --limit=5000 --commit-each --file ${ARCHIVAL_PATH}/%Y-%m-%d-%D.%t 2>> $LOGPATH/arch_cp.err

If the error persists, you may need to:

Verify Database Charset: Ensure your MySQL database and tables are set to use the correct character set (UTF-8).

SHOW VARIABLES LIKE 'character_set%';
SHOW TABLE STATUS WHERE Name = 'your_table';

Update MySQL Configurations: If needed, update the MySQL configuration to enforce the correct charset.

  1. Additional Considerations

    Logging: Enable verbose logging in pt-archiver to get more detailed error messages.
    Testing: Test the archiving process on a small subset of data to identify and fix issues without affecting the entire dataset.

Feel free to reach out if you need further assistance.