After Upgrade to VPS MySQL UTF-8 CHARSET ENCODING is adding "Â"

[jstepp28]

In preparation to "go live" I recently upgraded from a shared service account to a dedicated VPS. I mistakenly thought that when they migrated my site they would migrate the setup that I was using on the Shared server to ensure no loss of functionality/compatibility however I have had nothing but trouble since I made the switch.  It's a good thing since I believe that if you don't have hell you don't learn but this is ridiculous.  My provider said this is an application issue and they won't assist me with troubleshooting it.
 

Current Config:

Server information: Linux #1 SMP Mon Feb 17 21:00:14 MSK 2014 x86_64

Server software version: Apache

PHP version: 5.4.27

Memory limit: 384M

Max execution time: 50000

Database information

MySQL version: 5.6.16

MySQL engine: InnoDB

Tables prefix: ps_

Store information

PrestaShop version: 1.5.6.2

Shop URL: https://capvapor.com/

Current theme in use: default

 
Here is the story so you have some background:
 
I didn't realize there was an issue until my Products' short descriptions started complaining about being too long.  I was adding attributes to Products and hit the save button and it kept happening to each Product so I went to look and that's when I saw multiple Â characters between the period and the start of the next sentence.  I am showing my age but I always double space even though it isn't required because I like the way it looks.  It inserts the Â character between the spaces every time I change anything associated with the product.
 
When I started looking for the cause I noticed that there were different Collation settings in MySQL and Character settings even a few MyISAM tables peppered through the database.  I loaded my first Prestashop site using the Softalicious tool.  It is on a cPanel/WHM CentOS/Redhat Linux box.  My unix is rusty but I used to manage several Informix SQL Sun boxes for SWB Wireless (if that doesn't tell you my age nothing will).  So I can trudge through most things but for the life of me this MySQL configuration is eating my lunch...
 
Since I first noticed the problem I have searched everywhere to find and implement a fix to this issue without success.  Here is a list of items that I have found and changed in the attempt to get this resolved.

 

Entered:
This has a strong peppermint flavor with a menthol kick that really pops in your mouth. It has a super throat hit and reminds me of the great new chewing gum flavors out there. It really is amazing!

Displays/Commits:
This has a strong peppermint flavor with a menthol kick that really pops in your mouth.  It has a super throat hit and reminds me of the great new chewing gum flavors out there.  It really is amazing!
 
1) Set the my.cnf file to the following trying to get the defaults set to utf8:
[mysqld]
max_connections = 100
query_cache_size = 32M
query_cache_limit=1M
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 8M
table_open_cache = 64M
sort_buffer_size = 256K
join_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 1024K
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 16M
innodb_file_per_table
tmp_table_size = 32M
max_heap_table_size = 32M
thread_cache_size = 50
 
table_open_cache=500
table_definition_cache=500
 
open_files_limit=26520
 
wait_timeout = 30
connect_timeout = 1
 
low_priority_updates=1
concurrent_insert=2
 
character-set-server = utf8
collation-server = utf8_unicode_ci
init_connect = 'SET NAMES utf8'
character-set-client-handshake = FALSE
 
# This setting ensures that aio limits are not exceeded
# (default is 65536, each instance of mysql takes 2661 with this enabled)
innodb_use_native_aio = 0
#open_files_limit=32768
 
Here are my prestashop db results of: show variables like "%character%";show variables like "%collation%";

Variable_name               Value

collation_connection      utf8mb4_general_ci

collation_database        utf8_unicode_ci

collation_server             utf8_unicode_ci

 

Anyone that has fixed this that can point me in the right direction would be very appreciated!  I am attaching some current files that are from MySQL and the modules installed.  If you need anything else please let me know.

 

Thanks in advance!

J Stepp

capvapdb_mysql.txt

mysql-variables.txt

show-installed.txt

Edited May 9, 2014 by jstepp28 (see edit history)

[jstepp28]

Does Prestashop use the following to establish the MySQL connection encoding?

 

mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'", $conn);

 

I am still trying to find a solution to this...

 

Thanks in advance!

J Stepp

[Bill Dalton]

The descriptions are handled by tinyMCE

 

prestashop\js\tiny_mce

 

First thing to do is by pass this and enter your description directly in the database, this will let us know when the error is happening.

 

For example, if you enter the short description directly here,

 

Table: ps_product_lang

Field: description_short

 

... and if that fixes the display problem then we'll know the problem is with the java script. If that doesn't fix the problem then we'll need to find out what is wrong with either your calling procedure or your product theme.

 

Currently the link you provided doesn't show the error you describe. Also there are no products. You'll also need to disable all third party modules, including the smoke background.

[jstepp28]

Thank you for replying!  I really appreciate it...

 

I have some news!  I created a subdomain and installed Prestashop from scratch and the problem persisted so I thought that pointed to a Server/MySQL/PHP/Apache configuration issue.  I realized that UTF-8 encoding was critical to getting characters to commit to the MySQL server correctly.  I'd been focused on finding an opcode cache solution before I found this problem.

 

I started peeling back all the changes I'd made from a cache standpoint.  I'd installed modpagespeed, xcache, Zend 6.0, memcache, all of which made numerous changes to the php.ini, and I'd made quite a few other things trying things.  So I would disable one then test and then another, etc...   One thing I did was install a new PDO MySQL version with PHP 5.4.23 so I disabled it because the tools I was using to check the connection's encoding were MySQLite and it didn't work but I got this error and another complaining about the French language:

 

Error thrown: [object Object]

Text status: parsererror

 

I'd installed French months ago but I found this:

http://www.prestashop.com/forums/topic/217531-help-details-error-thrown-object-object-text-status-parsererror/page-2

 

 

I found the reason of the fault:

 

I installed the german module "GC German für Prestashop".

 

In the description of the Module is written it is not kompatible with foreign templates, only with the basic template.

 

So I uninstalled the French language and the problem seems to be resolved.  I must have installed a module that didn't like foreign languages??  I learned that one of the first things you do to troubleshoot issues is disable non-Prestashop modules.  The only thing I can think of that caused the fresh install to have the same issue was due to caching?  After I got the main site to work the new install still had issues until I cleared the local cache and did a hard reload in Chrome.

 

 

I tried to find a way to see the client/server connection encoding from the apache web root.  Here are some of the php code examples from the PHP site.  I'm wondering which method Prestashop utilizes of these methods to establish a UTF-8 encoded connection (and persistent if it doesn't lock the tables) to ensure that they are creating the proper utf8 encoding every time.

 

 

Here are some of the numerous sites I read trying to figure out what was causing this problem.  These are the best that I found concerning MySQL and PHP/PDO.

 

'PHP Connection to MySQL encoding check for utf8' http://php.net/manual/en/mysqli.set-charset.php

'PDO MySQL Settings' http://www.php.net/manual/en/book.pdo.php

'Detect encoding and make everything UTF-8 (nice tool)' http://stackoverflow.com/questions/910793/detect-encoding-and-make-everything-utf-8

'PHP the right Way PDO/PHP (reference that keeps up with changes)']http://www.phptherightway.com/#php_and_utf8

List of php.ini directives (PHP.NET)']http://www.php.net/manual/en/ini.list.php

'Enable UTF-8 in PHP']http://www.webmasterworld.com/php/3553642.htm

'Connection Character Sets and Collations']http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

'Check the character sets that MySQL database, table and column use']http://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-database-table-column-is-in-mysql

'mysql_client_encoding from MySQL site']http://dev.mysql.com/doc/apis-php/en/apis-php-function.mysql-client-encoding.html

 

Some of the code I used to see if I could tell what kind of MySQL connection I was getting from the site to my DB.  The first one would only return Latin1 but the second would return UTF-8.  This is the reading in the top left corner of my site right now because I put this in the index.php file before anything else ran.

 

<?php
$link    = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$charset = mysql_client_encoding($link);

echo "The current character set is: $charset\n";
?>

The above example will output something similar to:

The current character set is: latin1

Procedural style

<?php
$link = mysqli_connect('localhost', 'my_user', 'my_password', 'test');

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* change character set to utf8 */
if (!mysqli_set_charset($link, "utf8")) {
    printf("Error loading character set utf8: %s\n", mysqli_error($link));
} else {
    printf("Current character set: %s\n", mysqli_character_set_name($link));
}

mysqli_close($link);
?>

The above examples will output:

Current character set: utf8

So as you can see I've been working this from the wrong angle but at least I learned a lot about troubleshooting MySQL because I crashed the database while I was working on it and I know tons more that I did about encoding and Prestashop.  

 

This is the problem with "shotgun" changes and no real testing protocol in place.  I always say that education has it's cost...

 

Thanks again!

J Stepp

Edited May 10, 2014 by jstepp28 (see edit history)

[Bill Dalton]

Thank you for sharing J Stepp. I truly hope to see you around the forum more often if you have the time.

[jstepp28]

Do you think that it would be best to use utf8mb4_general_ci instead of utf8_general_ci?  The reasons are two fold:

 

1) even with all the changes I've made to the php.ini and my.cnf I still can't seem to get Prestashop to connect using utf8_general_ci encoding

 

2) this seems to be the recommended encoding because it's the "last" encoding that the "world" wide web will ever need... just like nobody will ever need more than 640K of RAM

 

I am in the process of rebuilding my shop from the ground up now that I am not testing modules and have a firm grasp on optimization techniques now.  I just want to know the Prestashop "best practices" as they relate to VPS server setup and I am way beyond the articles and manuals available on this site.  There's enough to learn in Prestashop let alone PHP, Apache, WHM/cPanel (which I hate), MySQL, Memcache vs APC vs XCache, Zend, and I can go on of course.

 

I was working on my sprites when I noticed this little show stopper...

 

Thanks again buddy!

J Stepp

[Bill Dalton]

Both will do the job, utf8 has a fixed byte size, mb4 will allow for variance in size, possibly useful for some specialized application.

 

I have found that with InnoDB the more ram you give it the better. It varies its size as needed so giving it 50% of available ram is not as crazy as it sounds. Just my opinion, one that many would disagree with.

 

I'm on a windows box so for PHP I use Windows Cache Extension for PHP, and PS Smarty

has it's own cache.

 

IIS 8.5 has a fantastic static cache built in, turn it on and forget about it. Nice! In addition to that rather than messing around with dynamic content cache I use,

 

Page Cache
http://www.prestashop.com/forums/topic/281654-module-page-cache-speedup-your-shop/

 

This module turns most of PS into static HTML. This is then cached by the IIS static cache. Once again, you can just turn this on and forget about it.

 

I've recently began to use,

 

JavaPro
http://www.prestashop.com/forums/topic/317189-module-prestashop-javapro-remove-page-render-blocking-javascripts/?hl=%2Bjavapro&do=findComment&comment=1646146

 

It seems to be a winner for my setup up.

Edited May 11, 2014 by Bill Dalton (see edit history)

[ed_humberto]

Hello,

 Im having a similiar issue with a shop, recently i noticed that some words in the backoffice were not showing, just blank spaces were showed and i realized that the words that had 

special characters like the word "Módulo" were the ones with the trouble. There is a function in smarty "escape" wich was causing the blank spaces, i removed the function and the words started to appear but with strange symbols.

 

A little bit o background on this issue,

Ive connected my shop to my sql database and it works like a charm but as soon as i connect it to the database where the shop is, the back starts showing strange symbols, ive checked the encoding in both databases and both have the same, so at this point i dont know what else could be causing this problem, any help would be aprreciated

[El Patron]

Hello,

 Im having a similiar issue with a shop, recently i noticed that some words in the backoffice were not showing, just blank spaces were showed and i realized that the words that had 

special characters like the word "Módulo" were the ones with the trouble. There is a function in smarty "escape" wich was causing the blank spaces, i removed the function and the words started to appear but with strange symbols.

 

A little bit o background on this issue,

Ive connected my shop to my sql database and it works like a charm but as soon as i connect it to the database where the shop is, the back starts showing strange symbols, ive checked the encoding in both databases and both have the same, so at this point i dont know what else could be causing this problem, any help would be aprreciated

 

Hi, it's possible when you created database that the collation of the db is wrong.  I use 'utf8_general_ci', there may be other choices.

 

you can check collation from phpmyadmin operations tab when your db is selected.