phpmyadmin Question posted in
The official documentation can be found here.

How do I insert UTF-8 data into MySQL using Lucee? – Phpmyadmin

I am trying to insert UTF-8 characters into a MySQL table using Lucee and having no luck.

  • I have the MySQL table set to use utf8mb4_unicode_ci but have also tried utf8mb4_bin.
  • I have tried Apache both with and without “AddDefaultCharset UTF-8” enabled.
  • The connection string to the database includes “characterEncoding=UTF-8”
  • Lucee config includes UTF-8 in the charset settings.

The code I am running is as follows…

<cfset textValue = '🙋 Person Raising Hand'>
<cfdump var="#textValue#">
<cfquery name="insert">
INSERT INTO TEST_UTF8 (TestText)
VALUES ('#textValue#');
</cfquery>

The dump works fine and displays as it should but the insert returns the following error…

“Incorrect string value: ‘xF0x9Fx99x8B P…’ for column ‘TestText’ at row 1”

I have tried inserting the same string via phpmyadmin and it goes in fine suggesting that the MySQL config is okay.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Okay, with a little more help and reading, I needed to add "character_set_server=utf8mb4" to the MySQL config file at "/etc/mysql/my.conf". Looks like this is an issue caused by an older version of the connector.

    From MySQL docs (thanks to CfSimplicity for leading me to the page)...

    For Connector/J 8.0.12 and earlier: In order to use the utf8mb4 character set for the connection, the server MUST be configured with character_set_server=utf8mb4; if that is not the case, when UTF-8 is used for characterEncoding in the connection string, it will map to the MySQL character set name utf8, which is an alias for utf8mb3.


  2. 0

    That 4-byte Emoji needs MySQL’s utf8mb4; utf8 will not do. To the outside world, utf8mb4 is called UTF-8 (with a dash)`.

    The column in the table needs to say CHARACTER SET utf8mb4 and the connection needs to say it also:

    Add ?useUnicode=yes&characterEncoding=UTF-8 to the JDBC URL

    <%@ page language="java" pageEncoding="UTF-8"%>
<%@ page contentType="text/html;charset=UTF-8" %>

compileJava.options.encoding = 'UTF-8'

<form method="post" action="/your/url/" accept-charset="UTF-8">

    To use 4-byte UTF8 with Connector/J configure the MySQL server with character_set_server=utf8mb4. Connector/J will then use that setting as long as characterEncoding has not been set in the connection string. This is equivalent to autodetection of the character set.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search