Jump to content
Susticle

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

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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');

 

  • Like 1

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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".

Share this post


Link to post
Share on other sites

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 by flydev
Blog post link

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...