2

I understand that there are probably hundreds of questions here addressing this issue but I haven't really found anything that worked yet.

The Scenario

I built a site on our staging site using Wordpress. Wordpress now uses utf8mb4 in the newer installations. Knowing this I checked the MySQL version on the live site to ensure compatibility and noticed it was using MySQL version 5.1.x.

I upgraded mysql on the live site following this tutorial with zero problems: https://kb.plesk.com/en/125589

At this point I do an export of the staging site database, replace all the staging site urls to the new live site urls and then import. Site runs fine until I see some diamond characters with question marks in them. I believe there is some character encoding issue at this point.

Just in case it wasn't a bad export I imported it locally on a MySQL version 5.5.3 on OSX. The import works fine. No character encoding issue.

I could be wrong but it might be treating the uft8mb4 encoding as utf8 on the live site.

Question(s)

If the export is working on my localhost with a similar MySQL version what could be causing the database on the live site to be spitting out missing characters?

Additional Info

Live Site MySQL version and status (encoding issues with import)

mysql  Ver 14.14 Distrib 5.5.52, for Linux (x86_64) using readline 5.1

Connection id:      20682
Current database:   
Current user:       admin@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.52-cll-lve MySQL Community Server (GPL) by Atomicorp
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/lib/mysql/mysql.sock
Uptime:         16 hours 11 min 18 sec

Local mysql version and status (imports fine)

mysql  Ver 14.14 Distrib 5.5.33, for osx10.6 (i386) using  EditLine wrapper

Connection id:      4402
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.33 Source distribution
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /Applications/MAMP/tmp/mysql/mysql.sock
Uptime:         14 days 8 hours 3 min 6 sec

Staging mysql version and status (Export source; site works fine)

VERSION is 5.5.5 (I couldn't get the status info of this one)

SQL dump file (a few tables only with some settings)

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DROP TABLE IF EXISTS `wp_blc_filters`;

CREATE TABLE `wp_blc_instances` (
  `instance_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `link_id` int(10) unsigned NOT NULL,
  `container_id` int(10) unsigned NOT NULL,
  `container_type` varchar(40) NOT NULL DEFAULT 'post',
  `link_text` varchar(250) NOT NULL DEFAULT '',
  `parser_type` varchar(40) NOT NULL DEFAULT 'link',
  `container_field` varchar(250) NOT NULL DEFAULT '',
  `link_context` varchar(250) NOT NULL DEFAULT '',
  `raw_url` text NOT NULL,
  PRIMARY KEY (`instance_id`),
  KEY `link_id` (`link_id`),
  KEY `source_id` (`container_type`,`container_id`),
  KEY `parser_type` (`parser_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `wp_postmeta`;

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Output of SHOW FULL COLUMNS FROM wp_posts;

ID                      bigint(20)      NULL    
post_author             bigint(20)      NULL    
post_date               datetime        NULL    
post_date_gmt           datetime        NULL    
post_content            longtext        utf8mb4_unicode_ci  
post_title              mediumtext      utf8mb4_unicode_ci  
post_excerpt            mediumtext      utf8mb4_unicode_ci  
post_status             varchar(20)     utf8mb4_unicode_ci  
comment_status          varchar(20)     utf8mb4_unicode_ci  
ping_status             varchar(20)     utf8mb4_unicode_ci  
post_password           varchar(20)     utf8mb4_unicode_ci  
post_name               varchar(200)    utf8mb4_unicode_ci  
to_ping                 mediumtext      utf8mb4_unicode_ci  
pinged                  mediumtext      utf8mb4_unicode_ci  
post_modified           datetime        NULL
post_modified_gmt       datetime        NULL
post_content_filtered   longtext        utf8mb4_unicode_ci 
post_parent             bigint(20)      NULL               
guid                    varchar(255)    utf8mb4_unicode_ci 
menu_order              int(11)         NULL               
post_type               varchar(20)     utf8mb4_unicode_ci 
post_mime_type          varchar(100)    utf8mb4_unicode_ci 
comment_count           bigint(20)      NULL               

I found some info that was different between the mysql servers when I ran the query SHOW VARIABLES LIKE 'char%'. Here are the results below if this helps distinguish the problem.

Local mysql

character_set_client        utf8
character_set_connection    utf8
character_set_database      utf8
character_set_filesystem    binary
character_set_results       utf8
character_set_server        latin1
character_set_system        utf8

Staging mysql

character_set_client        utf8
character_set_connection    utf8
character_set_database      latin1
character_set_filesystem    binary
character_set_results       utf8
character_set_server        latin1
character_set_system        utf8

Live mysql (remember this is the one having issues)

character_set_client        utf8mb4
character_set_connection    utf8mb4
character_set_database      utf8mb4
character_set_filesystem    binary
character_set_results       utf8mb4
character_set_server        latin1
character_set_system        utf8

This is the diff of the SHOW VARIABLES command in mysql for my live and local environments

Local Environment

-| basedir                                           | /Applications/MAMP/Library                                                                                             |
-| character_sets_dir                                | /Applications/MAMP/Library/share/charsets/                                                                             |
-| datadir                                           | /Applications/MAMP/db/mysql/                                                                                           |
-| general_log_file                                  | /Applications/MAMP/db/mysql/Joes-Mac.log                                                                               |
-| have_openssl                                      | NO                                                                                                                     |
-| have_ssl                                          | NO                                                                                                                     |
-| have_symlink                                      | YES                                                                                                                    |
-| hostname                                          | Joes-Mac.local                                                                                                         |
-| innodb_additional_mem_pool_size                   | 8388608                                                                                                                |
-| innodb_buffer_pool_size                           | 134217728                                                                                                              |
-| innodb_log_buffer_size                            | 8388608                                                                                                                |
-| innodb_thread_concurrency                         | 0                                                                                                                      |
-| innodb_use_native_aio                             | OFF                                                                                                                    |
-| innodb_version                                    | 5.5.33                                                                                                                 |
-| lc_messages_dir                                   | /Applications/MAMP/Library/share/                                                                                      |
-| local_infile                                      | ON                                                                                                                     |
-| log_error                                         | /Applications/MAMP/logs/mysql_error_log.err                                                                            |
-| lower_case_file_system                            | ON                                                                                                                     |
-| lower_case_table_names                            | 2                                                                                                                      |
-| open_files_limit                                  | 2560                                                                                                                   |
-| pid_file                                          | /Applications/MAMP/tmp/mysql/mysql.pid                                                                                 |
-| plugin_dir                                        | /Applications/MAMP/Library/lib/plugin/                                                                                 |
-| port                                              | 8889                                                                                                                   |
-| pseudo_thread_id                                  | 4465                                                                                                                   |
-| report_port                                       | 8889                                                                                                                   |
-| slave_load_tmpdir                                 | /var/folders/bj/t8j_n5qx3s1cgsq9c2_7nw4w0000gq/T/                                                                      |
-| slow_query_log_file                               | /Applications/MAMP/db/mysql/Joes-Mac-slow.log                                                                          |
-| socket                                            | /Applications/MAMP/tmp/mysql/mysql.sock                                                                                |
-| system_time_zone                                  | PDT                                                                                                                    |
-| timestamp                                         | 1477524853                                                                                                             |
-| tmpdir                                            | /var/folders/bj/t8j_n5qx3s1cgsq9c2_7nw4w0000gq/T/                                                                      |
-| version                                           | 5.5.33                                                                                                                 |
-| version_comment                                   | Source distribution                                                                                                    |
-| version_compile_machine                           | i386                                                                                                                   |
-| version_compile_os                                | osx10.6                                                                                                                |


Live Environment

+| basedir                                           | /usr                                                                                                                   |
+| character_sets_dir                                | /usr/share/mysql/charsets/                                                                                             |
+| datadir                                           | /var/lib/mysql/                                                                                                        |
+| general_log_file                                  | /var/lib/mysql/vps-1003610-982.log                                                                                     |
+| have_openssl                                      | DISABLED                                                                                                               |
+| have_ssl                                          | DISABLED                                                                                                               |
+| have_symlink                                      | DISABLED                                                                                                               |
+| hostname                                          | vps-1003610-982.clients.robsoninc.com                                                                                  |
+| innodb_additional_mem_pool_size                   | 524288                                                                                                                 |
+| innodb_buffer_pool_size                           | 5242880                                                                                                                |
+| innodb_log_buffer_size                            | 512000                                                                                                                 |
+| innodb_thread_concurrency                         | 2                                                                                                                      |
+| innodb_use_native_aio                             | ON                                                                                                                     |
+| innodb_version                                    | 5.5.52                                                                                                                 |
+| lc_messages_dir                                   | /usr/share/mysql/                                                                                                      |
+| local_infile                                      | OFF                                                                                                                    |
+| log_error                                         | /var/log/mysqld.log                                                                                                    |
+| lower_case_file_system                            | OFF                                                                                                                    |
+| lower_case_table_names                            | 0                                                                                                                      |
+| open_files_limit                                  | 1024                                                                                                                   |
+| pid_file                                          | /var/run/mysqld/mysqld.pid                                                                                             |
+| plugin_dir                                        | /usr/lib64/mysql/plugin/                                                                                               |
+| port                                              | 3306                                                                                                                   |
+| pseudo_thread_id                                  | 29088                                                                                                                  |
+| report_port                                       | 3306                                                                                                                   |
+| slave_load_tmpdir                                 | /tmp                                                                                                                   |
+| slow_query_log_file                               | /var/lib/mysql/vps-1003610-982-slow.log                                                                                |
+| socket                                            | /var/lib/mysql/mysql.sock                                                                                              |
+| system_time_zone                                  | MSD                                                                                                                    |
+| thread_statistics                                 | OFF                                                                                                                    |
+| timestamp                                         | 1477524780                                                                                                             |
+| tmpdir                                            | /tmp                                                                                                                   |
+| userstat                                          | OFF                                                                                                                    |
+| version                                           | 5.5.52-cll-lve                                                                                                         |
+| version_comment                                   | MySQL Community Server (GPL) by Atomicorp                                                                              |
+| version_compile_machine                           | x86_64                                                                                                                 |
+| version_compile_os                                | Linux                                                                                                                  |

Question 2

Does the database character settings affect the output (by output I mean what I see in the browser) at all?

Update

Posted wrong Staging site database status output. I forgot the site was on a different staging site. Will get that info soon.

Update 2

I checked the php info to see which MySQL it was using and found these two lines differ than my other environments:

Client API version  5.1.59

Client API library version  5.1.59

Does that mean its using MySQL version 5.1.59 when used with PHP? If so how would I go about updating that part after I initially made the upgrade from 5.1 to 5.5 in CentOS?

Update 3

On the live site I have ran the following query:

SELECT post_title FROM wp_posts WHERE LENGTH(post_content) > CHAR_LENGTH(post_content);

This gave me all the results where the encoding is different (or something like that). I received a bunch of rows in the query and one of them was the content that I was having an issue with. My assumption is that utf8mb4 is being imported into utf8 but the tables and columns are all utf8mb4 encoding and collation. So I don't know where the character sets transform between this transaction. I have tested the strings in PHP to test the encoding and it says its UTF-8 which means it could be utf8 or utf8mb4 in mysql.

It could also be worth mentioning that I have been using Sequel Pro to import and export the data back and forth. So a possibility that Sequel Pro could be interfering with the importing/exporting of the sql data

Update 4

Tried this command:

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Tested it out and no change. Then I tried to import the sql file again and still, no change

  • Can you do a SHOW FULL COLUMNS FROM wp_postmeta ( or whatever table contains the mis-mapped characters you've found ) on your live site's DB? If I had to guess, I would assume the upgrade didn't actually change the collation of any of the existing tables. – Avarkx Oct 26 '16 at 18:17
  • Does it matter if I did the upgrade before the import? Also just formatting the output right now. Its a little messy. – JoeMoe1984 Oct 26 '16 at 18:23
  • Updated answer to show live site full columns. I used the wp_posts table as that is the one that has the issue (as far as I know). Missing characters would be in the post_content column. Its possible there are more tables and columns with this issue though. – JoeMoe1984 Oct 26 '16 at 18:30
  • I have updated the question with some more details about the character sets for each database. – JoeMoe1984 Oct 26 '16 at 19:01
  • Is your import in binary mode? It looks to me like maybe you're importing data encoded at utf8 into a table which is trying to interpret it as utf8mb4. If you change the collation of the post_content column to utf8 on the live server, does the missing character behavior persist? – Avarkx Oct 26 '16 at 19:46
1

Question mark in a black diamond occurs in two cases. See this for details.

The black diamonds probably occurred by not setting the connection to be utf8/utf8mb4 when reading the data.

To dig further, we need to see some specifics, preferably dumped in hex.

If the data was correctly stored into CHARACTER SET utf8 in the old system, then the dump would have data that could correctly be loaded into columns defined either CHARACTER SET utf8 or CHARACTER SET utf8mb4. That is there is full compatibility in that direction. The utf8mb4 of 5.5.3+ is a superset of utf8 (before or after 5.5.3).

The dump was using mysqldump? Which version? The 5.1 version? Or a post-5.5.3 version? (I don't think it matters, but please check anyway.)

And what arguments were on mysqldump? This could be important, since, if it is converting to latin1, that could explain the mess.

Are you sure about the dump file? I see CHARSET=utf8mb4.

Q2: Does the database character settings affect the output at all? -- It is complicated. And what do you mean by "output"? The black diamonds are all the way out at the browser. We need to check what is in the table (see "Test the Data" in the link).

  • I did the dump by using phpmyadmin as well as using sequel pro. Just in case the differences in the sql file would make a difference. I tried importing through command line and with sequel pro. – JoeMoe1984 Oct 26 '16 at 23:11
  • Yes pretty sure about the dumpfile. I have tables with both utf8 and utf8mb4 because I had to import some old stuff to the new database. When I say new database I mean what I have in all 3 versions (staging, local and live). only the live one is having this problem. – JoeMoe1984 Oct 26 '16 at 23:16
  • "Only the live one has the problem" - That can be irritating! Get SHOW VARIABLES from each; diff them; show us the diffs. – Rick James Oct 26 '16 at 23:19
  • 1
    Unrelated: innodb_buffer_pool_size is probably too small on both. lower_case_table_names is different; possibility of table names messing up (can't really fix in config). – Rick James Oct 26 '16 at 23:54
  • 1
    open_files_limit = 1024 is low for a production server. – Rick James Oct 26 '16 at 23:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.