📋

Description

MySQL Error 3145 indicates that a JSON function received character data that is not encoded in UTF-8, which is a strict requirement for all JSON string literals within MySQL. This typically occurs when input data originates from a non-UTF-8 character set and is passed to a JSON function without proper character set conversion.

💬

Error Message

Invalid JSON character data provided to function %s: '%s'; utf8 is required.

🔍

Known Causes

4 known causes

⚠️

Client Connection Encoding Mismatch

The client application's connection to MySQL is not configured for UTF-8, leading to character encoding issues when sending JSON data.

⚠️

Database Column Character Set

JSON data is being retrieved from a table column that is defined with a character set other than UTF-8, causing encoding conflicts when processed by JSON functions.

⚠️

External Data Encoding

Data originating from an external file, API, or service is not encoded in UTF-8 before being processed by MySQL's JSON functions.

⚠️

Application-Level Encoding

The application code is processing or constructing JSON strings using a character encoding other than UTF-8 before sending them to MySQL.

🛠️

Solutions

3 solutions available

1. Ensure UTF-8 Encoding for JSON Data easy

Verify and convert your JSON data to UTF-8 before inserting or updating.

1

Inspect the source of your JSON data to determine its current encoding. If it's not UTF-8, you'll need to convert it.

2

If you are generating JSON programmatically (e.g., in Python, Node.js, PHP), ensure your serialization process explicitly outputs UTF-8. For example, in Python:

import json

data = {'key': 'value'}
json_string = json.dumps(data, ensure_ascii=False).encode('utf-8')

3

If you are inserting JSON from a file or another source, use tools that allow you to specify the input encoding as UTF-8. For example, when using the `mysql` client:

mysql --default-character-set=utf8 -u your_user -p your_database < your_data.sql

4

When loading data via `LOAD DATA INFILE` or similar methods, ensure the file itself is UTF-8 encoded and that your MySQL connection is configured for UTF-8.

2. Set Connection Character Set to UTF-8 easy

Configure your MySQL client or application to use UTF-8 for the connection.

1

When connecting to MySQL, explicitly set the character set for the connection to UTF-8. This can be done in several ways:

2

Using the `mysql` command-line client:

mysql --default-character-set=utf8 -u your_user -p

3

In your application code (example for PHP PDO):

$pdo = new PDO('mysql:host=localhost;dbname=your_database;charset=utf8', 'your_user', 'your_password');

4

In your application code (example for Python with `mysql.connector`):

import mysql.connector

cnx = mysql.connector.connect(user='your_user', password='your_password', host='localhost', database='your_database', charset='utf8')

5

If you are using a connection pool, ensure the pool is configured to establish connections with the UTF-8 character set.

3. Convert Stored JSON to UTF-8 medium

If existing JSON data is causing the error, convert it to UTF-8 within the database.

1

Identify the table and column containing the problematic JSON data. You can use a `SELECT` statement to preview the data and identify potential issues.

SELECT your_json_column FROM your_table WHERE ...;

2

If the JSON data is stored as a character type (e.g., `VARCHAR`, `TEXT`) and is not UTF-8, you may need to convert it. This is a more complex operation and might involve exporting, converting, and re-importing if direct in-place conversion is not feasible or safe.

3

A safer approach is to export the data, convert it to UTF-8 using external tools (like `iconv` on Linux/macOS or PowerShell on Windows), and then re-import it. Alternatively, if you can identify the specific non-UTF-8 characters, you might be able to use MySQL functions like `CONVERT` or `CAST` if the data is stored in a compatible way, but this is often not straightforward for arbitrary JSON strings.

4

Consider creating a new column with a `JSON` data type and UTF-8 collation, and then migrating the data, ensuring proper encoding during the transfer.

ALTER TABLE your_table ADD COLUMN new_json_column JSON;
UPDATE your_table SET new_json_column = CAST(your_json_column AS JSON); -- This assumes your_json_column is already somewhat compatible or can be coerced.

🔗

Related Errors

5 related errors

MySQL Error 3145: Invalid JSON Character Encoding