How to Fix MySQL java.sql.SQLException: Incorrect string value

1. Overview

MySQL is one of the most common relational database systems. Consequently, integrating it with Java applications via the MySQL Connector/J JDBC driver is a fairly standard task. This enables working with the database directly from Java. Yet, even when handling pure text data, we might encounter errors such as java.sql.SQLException: Incorrect string value.

In this tutorial, we explain how to fix the Incorrect string value error in Java applications using MySQL. First, we explore what the exception means and why it occurs. Then, we examine configuration changes at different levels as a means to work around the problem. Finally, we provide other practical solutions such as MySQL schema updates, JDBC configuration, and server settings.

2. Understanding the Error

Before attempting to fix java.sql.SQLException: Incorrect string value, let’s understand how and when the error appears.

2.1. General Description

Initially, we can explore an example error text. To that end, let’s execute an INSERT or UPDATE operation with problematic characters:

java.sql.SQLException: Incorrect string value: '\x00\x00\x27\x4C' for column 'textcol'

In this case, we see that the textcol column can’t handle the string value \x00\x00\x27\x4C. In short, MySQL rejected the input. One of the main reasons for this is the fact that the column character set can’t represent the provided data.

2.2. Character Encoding Mismatch

So, the way that Java handles Unicode isn’t entirely compatible with the default or current MySQL character set.

Specifically, Java uses full UTF-8 encoding, where more than three bytes can represent a single character. On the other hand, the default utf8 character set in MySQL supports a maximum of 3 bytes per character. In this situation, if we try to store a 4-byte character, such as an emoji, within the database, we might encounter the exception in question.

For instance, let’s consider inserting a string containing an emoji into a table as a common action:

INSERT INTO messages (text) VALUES ('❌');

If the column uses utf8, MySQL rejects the value because of its inability to store 4-byte characters.

3. MySQL Character Set Configuration and Conversion

In short, the most reliable solution is often to configure MySQL, the database, and its components for full Unicode support.

3.1. Change utf8mb4 to utf8

MySQL provides the utf8mb4 character set, which supports all Unicode characters, including emojis and similar:

ALTER DATABASE mydb 
CHARACTER SET = utf8mb4 
COLLATE = utf8mb4_unicode_ci;

This command ensures the database itself can store 4-byte characters correctly.

3.2. Convert Tables

Existing tables may still use incompatible encodings, even after updating the database configuration:

ALTER TABLE xtab 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

Thus, we should update all tables with the new encoding.

3.3. Update Columns

In some cases, we might need to modify individual columns:

ALTER TABLE xtab 
MODIFY text_column TEXT 
CHARACTER SET utf8mb4;

To reiterate, the database, all tables, and each column must all use utf8mb4 exclusively.

4. JDBC Configuration Issues

Even after configuring MySQL correctly, we should set up the JDBC connection, so it also handles Unicode properly.

4.1. Configure Connection URL

The JDBC URL can include parameters to enforce Unicode behavior:

jdbc:mysql://localhost:3306/mydb
  ?useUnicode=true
  &characterEncoding=utf8

These options ensure that Java correctly encodes and transmits string data to MySQL.

4.2. Connector/J Behavior

Modern versions of Connector/J automatically negotiate character sets with the MySQL server. However, using older drivers or misconfigurations may still lead to encoding mismatches.

Therefore, explicitly setting encoding or upgrading the driver can help avoid inconsistencies.

5. Verifying MySQL Server Settings

In addition to schema and connection settings, server-level configuration plays a role in encoding behavior.

5.1. Check Character Set Variables

Let’s inspect the current MySQL character set configuration:

SHOW VARIABLES LIKE 'character_set%';

Ideally, the server, database, and connection should all use utf8mb4 to ensure compatibility.

5.2. Update MySQL Configuration

If necessary, we might need to update the MySQL configuration file to enforce the needed defaults:

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

After making changes, we should restart the MySQL service for them to take effect. At this point, the server should only employ utf8mb4 as its encoding.

6. Common Pitfalls

Even after applying fixes, certain common mistakes can still trigger the java.sql.SQLException: Incorrect string value error.

6.1. Partial Configuration

Updating only one component can result in inconsistent behavior.

For example, fixing the database but not the tables may still cause failures. Even single columns can result in problems. Thus, all components must conform to the same encoding.

This process can be tedious, but it is required, as even a single misaligned component can break the character set negotiation.

6.2. New Database Components

Any new database component with the default encoding utf8 can break character support.

Thus, we should only create new tables and columns with the utf8mb4 encoding.

6.3. Outdated JDBC Driver

Older JDBC drivers may not fully support modern MySQL encoding behavior:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

That’s because the above Maven artifact (with its groupId and artifactId) is outdated and superseded:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>9.7.0</version>
</dependency>

Keeping dependencies up to date is one of the best ways to improve compatibility and stability. To find the latest artifacts, we can always go to MySQL Connector/J on the Maven Repository.

7. Summary

In this article, we explored how to diagnose and fix the java.sql.SQLException: Incorrect string value error in MySQL.

In summary, the problem is caused by character encoding mismatches, most commonly due to the limited utf8 implementation of MySQL. By switching to utf8mb4, aligning JDBC configuration, verifying server settings, and ensuring consistency across all layers, we can fully support Unicode and often eliminate this error.