General error when trying to access templates

Question

Running EE 3.5.12

In the process of creating a number of new templates I suddenly got the following error:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xD5ve fo...' for column 'template_data' at row 1:

ee/legacy/database/drivers/mysqli/mysqli_connection.php:122

I checked the template_data column contents of the most recent templates in exp_templates and could not see anything amiss.

I would appreciate any guidance on what might be causing this and how to resolve it.

I've attached an image showing the encoding for the table:

Answer 1

I believe your issue is MySQL attempting to encode an invalid UTF-8 byte sequence. I checked (even though it is obvious if you work with encoding/decoding UTF-8 on string sequences) and \xD5ve is not a valid hex code for a UTF-8 character. I have no idea why that string is in one of your templates, but it is.

A partial guess is that upgrading to EE3 forced your encoding from Latin-1 (or something else) to UTF-8, and now it can't handle the invalid sequence. Or the new templates you are adding just plain have invalid UTF-8 sequences.

This is kind of a far out guess, but could be it. To test this, I would:

1. Backup your database. Preferably, don't run this on a production server, but only on a staging/local dev instance.

2. Attempt to find, and remove the violating byte sequence. I would do it in both your file template assets, as well as in their template_data column.

To find a code sequence, I would log on to a server and navigate to your template group. This command is really handy:

grep -nr search\-string

You may have to try a few different versions to match it exactly (escaping charcters for grep):

grep -nr \\xD5ve

For your database, just use a LIKE query on the template_data column. Should do the trick:

USE my_schema;
SELECT * FROM exp_templates WHERE template_data LIKE "%\xD5Ve%"; 

Hrm, did I get that right? Been a million years since I had to write a query. Anyways, that is my current suggestion; find and remove invalid UTF-8 sequences from all of your templates, but start with the one.

Answer 2

All efforts to locate the offending encoded entity failed. I therefore made another backup of the database and all template files and proceeded to systematically delete template files. It ended up being one of the most recent templates created but it is not obvious what was wrong with it. The system is up and running now and the error is gone.