0

I am receiving an "Incorrect string value" error when inserting into a MySql 5.6 DB using php (I have tried multiple versions of PHP from multiple servers, including v5.5 - all Windows machines).

I have scoured the forums and tried everything, but cannot seem to get this figured out! Below are the details. I hope I am just missing something simple, any and all help is greatly appreciated. Thank you in advance!

=====================================================================

Current MySQL 5.6 Config (relevant parts):

[client]
default-character-set = utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Validation of Server Settings:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

---------------------------------------------------
Variable_name             |  Value
---------------------------------------------------
character_set_client      |  utf8mb4
---------------------------------------------------
character_set_connection  |  utf8mb4
---------------------------------------------------
character_set_database    |  utf8mb4
---------------------------------------------------
character_set_filesystem  |  binary
---------------------------------------------------
character_set_results     |  utf8mb4
---------------------------------------------------
character_set_server      |  utf8mb4
---------------------------------------------------
character_set_system      |  utf8
---------------------------------------------------
collation_connection      |  utf8mb4_general_ci
---------------------------------------------------
collation_database        |  utf8mb4_general_ci
---------------------------------------------------
collation_server          |  utf8mb4_unicode_ci
---------------------------------------------------

Create Database Statement (This is a test DB I created strictly to solve this problem):

CREATE DATABASE `chartest` /*!40100 COLLATE 'utf8mb4_general_ci' */

Create Table Statement:

CREATE TABLE IF NOT EXISTS `testtable` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `testcol` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Test / Sample PHP Code: (One note - I know we should be using PDO or MySQLi, and we are in the progress of migrating / upgrading, but we have a lot of legacy code and thus this issue needs to be resolved in the meantime / short-term using the now deprecated mysql functions).

<?php   
    //Establish DB connection
    $DBConnection = mysql_connect('my_db_server_IP', 'my_db_username', 'my_db_password');

    if (!$DBConnection) {
        die('Could not connect: ' . mysql_error());
    }

    if (!mysql_select_db('chartest', $DBConnection)) {
        echo 'Could not select database';
        exit;
    }    

    //Set misc charset options in mysql
    /*Note: these should not really be needed given the 
    character-set-client-handshake = FALSE setting in the MySQL config file.  
    Also, I have tried every combination of these, building 
    from the ground up - no luck. 
    I have left these in here just for the sake of being thorough */

    mysql_query("SET character_set_client=utf8mb4", $DBConnection); 
    mysql_query("SET character_set_connection=utf8mb4", $DBConnection); 
    mysql_query("SET character_set_database=utf8mb4", $DBConnection); 
    mysql_query("SET character_set_results=utf8mb4", $DBConnection); 
    mysql_query("SET character_set_server=utf8mb4", $DBConnection); 


    mysql_set_charset("utf8mb4", $DBConnection);            
    mysql_query("SET CHARACTER SET utf8mb4", $DBConnection);
    mysql_query("SET NAMES utf8mb4", $DBConnection);



    //The query below contains a "middle dot" which is what causes the error (or at least is an example)
    $SQLQuery = "INSERT INTO testtable (testcol) VALUES ('test · middle dot insert')";  

    //Execute query
    $result = mysql_query($SQLQuery, $DBConnection);

    if (!$result)
    {
        echo mysql_error();
        exit;
    }
    else
    {
        echo 'worked - '. mysql_insert_id();
    }

    mysql_close($DBConnection); 
?>

Error Returned

Incorrect string value: '\xB7 middle dot insert' for column 'testcol' at row 1

...Thank you again for your wisdom.

==================================================================

EDIT RE: Duplicate question: I have reviewed the suggested duplicate question / answer UTF-8 all the way through, but I do not see what I am missing (although I clearly must be missing something!!).

More specifically (per the referenced post), my 'data storage' is utf8mb4, my 'data access' is using mysql_set_charset, I am not actually trying to 'output' these characters (at least that is not my issue here), The character/SQL query is hard coded - thus this issue is not related the 'input' of any data. And lastly, regarding the 'other considerations' I am not using any string functions so that would not be an issue best I can tell.

Once again, any feedback is greatly appreciated. Thanks.

Community
  • 1
  • 1
Dan
  • 11
  • 4
  • [This](http://stackoverflow.com/a/11013986/2518525) might be of interest to you Dan. – Darren Feb 09 '15 at 00:07
  • The most likely explanation here is that "test · middle dot insert" is not actually encoded in UTF-8. You need to save your source code file as UTF-8. Try `echo bin2hex('·')`, if that doesn't show up as `C2B7` then that confirms the problem. – deceze Feb 09 '15 at 01:35
  • Thank you for the response, @deceze. I would hugely appreciate it if you could shed some light on what I am missing. I have reviewed the "UTF All the way through" answer and, as my initial questions outlines, I believe I have addressed all of the key points this answer outlines (although I clearly must be missing something :)... – Dan Feb 09 '15 at 01:47
  • Specifically, I have set the data storage to utf8mb4, I have set charset for the the client connection (both in PHP, and also by setting "character-set-client-handshake = FALSE" in the MySQL config, I am not outputting or accepting inputs in the example code I posted, but I still receive the error. Regarding your comment specifically, I have echoed bin2hex('·') and it outputs "b7", but I am unclear on what you mean by "save your source code file as UTF-8" you mean actually how I save the PHP file? Again, I greatly appreciate your time and response. Best, Dan – Dan Feb 09 '15 at 01:52
  • That means your source code file is saved as ISO-8859-1, so all your string literals are in that encoding. You need to save your .php file as UTF-8 in your text editor. How to do that exactly depends on your editor of choice. See [Handling Unicode Front To Back In A Web App](http://kunststube.net/frontback/) and [What Every Programmer Absolutely, Positively Needs To Know About Encodings And Character Sets To Work With Text](http://kunststube.net/encoding/) for generally more information on this topic. – deceze Feb 09 '15 at 01:55
  • @deceze, thank you for the response. I'll give that a shot and report back. Your feedback is much appreciated. – Dan Feb 09 '15 at 02:04
  • @deceze, that did it! I saved the file as UTF8 and it worked. This charset stuff ran me in circles today. Huge thanks! I am new to posting on stack overflow, let me know if I should do anything specific (up vote, post an answer, etc). Thanks again – Dan Feb 09 '15 at 02:12
  • This question is a duplicate and closed as such already, not much for you to do. :) – deceze Feb 09 '15 at 02:31

0 Answers0