Problem after moving to another server, SQLSTATE [42000], 1139 invalid UTF-8 string

[Susticle]

Hello,

I got a new development machine and installed a new XAMPP version.

After setting up a working ProcessWire installation I get this error on page save:

ProcessWire: ProcessPageEdit: SQLSTATE[42000]: Syntax error or access violation: 1139 Got error 'invalid UTF-8 string at offset 26' from regexp 

I cleared the cache, setup MariaDB for UTF-8, but the problem is still there. Do you have any suggestions?

Thank you!

Edit:

I found out that the problem happens in a self written module. There is the following statement:

foreach($arr_keywords as $str_keyword) {
	$arr_selectors[] = 'fieldname~='.wire('sanitizer')->selectorValue($str_keyword);
}

If there is a UTF-8 char in $str_keyword the error occurs. Changing the operator does help. But I would like to learn what I'm doing wrong there. Thank you!

[gmclelland]

I wonder if adding this to your site's /site/config.php file will work?

// I receive a warning when visiting the admin area
// Warning: your server locale is undefined and may cause issues. Please add this
// to /site/config.php file (adjust “en_US.UTF-8” as needed): setlocale(LC_ALL,'en_US.UTF-8');
setlocale(LC_ALL,'en_US.UTF-8');

 

[Gideon So]

What is your database collation??

[Susticle]

13 hours ago, gmclelland said:

I wonder if adding this to your site's /site/config.php file will work?

Thanks for your answer. This statement was already in there. There is no change whether it's there or not.

[Susticle]

13 minutes ago, Gideon So said:

What is your database collation??

For the database and all tables it's "utf8_general_ci". One exception which doesn't matter, I think: The table "field_pass" has "ascii_general_ci".

[flydev]

A fast answer, suggestion.

From there : https://dev.mysql.com/doc/refman/5.6/en/charset-charsets.html

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
+----------+-----------------------------+---------------------+--------+

 

My bet is that you might have a 4-byte characters in a keyword which is actually triggering the error where MySQL utf-8 character set only supports 3-byte characters.

My recommendation is to convert your database to the utf8mb4_general_ci collation.

 

PS:  Backup, Backup, Backup

 

Edit:

Blog post found on ProcessWire: https://processwire.com/blog/posts/pw-3.0.15/#utf8mb4-considerations

 

 

Good luck ?

Edited November 30, 2018 by flydev
Blog post link

[horst]

Not sure if this really match to that case, but possibly worth a check: are all template and module files are saved in utf-8?