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.