asked on

Error inserting special characters in mysql database

HI,
I have table with columns encoding as utf8.
when i try to insert insert into notes values("1","😁 ","title","preview");
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x81 ' for column 'body' at row 1
Its giving this error because i am using emoji .
How can i fix this at mysql level.
I found that running the command set names utf8mb4
and changing column encoding to utf8mb4 resolves the issue...

But this does not seems to be a correct approach.
As in the official setup the mysql server is deployed somewhere else and i cant expect everytime the server has started that someone runs set names utf8mb4 .
My table is the only table in the database. Is it possible to set some configuration at database level or even if possible at the table level so that the default encoding automatically becomes utf8mb4 everytime the server starts ?

And is it possible to bypass the requirement for changing the encoding for all columns to utf8mb4 ?
I tried running the command : ALTER TABLE notes CONVERT TO CHARACTER SET utf8mb4;
For some strange reason it also modifies the text type fields to MEDIUMTEXT

This can cause trouble. As there is already data on production mysql server.

Thanks

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rohit Bajaj

ASKER

Here is the table query which created it :

DROP TABLE IF EXISTS `notes`;
CREATE TABLE `notes` (
  `id` char(36) NOT NULL,
  `body` text NOT NULL,
  `title` varchar(255) NOT NULL,
  `preview` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window

Rohit Bajaj

ASKER

There probably is some issue with running the statement after opening a connection as mentioned here :
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-charsets.html

Warning

Do not issue the query set names with Connector/J, as the driver will not detect that the character set has changed, and will continue to use the character set detected during the initial connection setup.

Not sure if i am understanding it properly.

Rohit Bajaj

ASKER

This is confusing... On https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
its mentioned :
It is still necessary for applications to configure their connection using SET NAMES or equivalent after they connect, as described previously.

Rohit Bajaj

ASKER

I think i can go ahead with using set names thing for the time... But the real issue is what do i do with the already created table having lots of values...
Either i have to create a new table and copy all data from older table to this and remove the older table.
or modify the existing table.

Looks like this is not straight forward....
As alteration is causing change of types... dont know if that will be a trouble .
i can always again modify the type of column back after alter...
Please suggest...

Guy Hengel [angelIII / a3]

i am no specialist of j connector...
i think you can specify the nrcessay in the co nnection string
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html