terrymanu commented on issue #30008:
URL:
https://github.com/apache/shardingsphere/issues/30008#issuecomment-3508259429
You are experiencing a character encoding configuration issue, not a
ShardingSphere bug. There are important differences in character set handling
between MySQL Connector/J 5.1.49 and 8.x versions, which causes issues
with emoji character insertion.
Root Cause
MySQL Connector/J Version Differences
5.1.49 Version Characteristics:
- Character set handling is more lenient, automatically detects MySQL
server character set
- Good backward compatibility, less strict configuration requirements
- Can work properly even without explicitly specifying character set
parameters
8.x Version Characteristics:
- More strict character set validation, requires explicit character set
configuration
- Uses standard UTF-8 by default, not utf8mb4
- 4-byte character processing (like emoji) requires explicit configuration
Emoji Character Technical Requirements
The emoji character "🍃" is a 4-byte Unicode character that requires:
1. MySQL 5.5.3+ version support
2. Database connection using utf8mb4 character set
3. Correct JDBC connection parameter configuration
Solution
Method 1: Modify JDBC URL (Recommended)
Modify your data source configuration in your ShardingSphere configuration
file as follows:
databaseName: readwrite_splitting_db
dataSources:
write_ds:
url:
jdbc:mysql://127.0.0.1:3306/your_db?useSSL=false&characterEncoding=utf8mb4&serverTimezone=UTC&connectionCollation=utf8mb4_unicode_ci
username: root
password: your_password
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
read_ds_0:
url:
jdbc:mysql://127.0.0.1:3306/your_read_db_0?useSSL=false&characterEncoding=utf8mb4&serverTimezone=UTC&connectionCollation=utf8mb4_unicode_ci
username: root
password: your_password
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !READWRITE_SPLITTING
dataSourceGroups:
readwrite_ds:
writeDataSourceName: write_ds
readDataSourceNames:
- read_ds_0
loadBalancerName: random
loadBalancers:
random:
type: RANDOM
Method 2: Use connectionInitSqls
If the above method doesn't work, you can add connection initialization
SQL:
dataSources:
write_ds:
url:
jdbc:mysql://127.0.0.1:3306/your_db?useSSL=false&serverTimezone=UTC
username: root
password: your_password
connectionInitSqls:
- "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;"
# Other connection pool configuration...
Important Configuration Notes
Required JDBC Parameters
- characterEncoding=utf8mb4: Ensures connection uses utf8mb4 character set
- connectionCollation=utf8mb4_unicode_ci: Specifies correct collation rules
- serverTimezone=UTC: MySQL Connector/J 8.x version requires timezone
specification
Database-Level Configuration Confirmation
Ensure your MySQL database is properly configured:
-- Check database character set
SHOW VARIABLES LIKE 'character_set_%';
-- Check collation rules
SHOW VARIABLES LIKE 'collation_%';
-- Ensure database and tables use utf8mb4
ALTER DATABASE your_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Verification Steps
1. Update ShardingSphere configuration file
2. Restart ShardingSphere Proxy
3. Test emoji character insertion
4. Confirm data is correctly stored and retrieved
Driver Version Recommendation
It is recommended to use the latest version of MySQL Connector/J 8.x, as
it provides more complete and secure support for utf8mb4. As long as character
set parameters are correctly configured, it will perfectly support
4-byte characters like emoji.
If you still have issues after following the above configuration, please
provide:
1. Complete configuration file
2. MySQL database character set settings
3. Specific error information
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
