MySQL Error Summary (Continuously Updated)

1. Problem description:

An error occurs during the connection test saying "No suitable driver found for localhost."

2. Cause analysis:

If all JDBC drivers contained in DriverManager cannot recognize the input URL format, the “no suitable driver found for XX” error is reported.

3. Troubleshooting steps:

a. Check if the URL format of MySQL database is jdbc:mysql://<ip>:<port>/. For example, jdbc:mysql//localhost:3306/ or jdbc:mysql//localhost:3306/test.

b. If the format is correct, check if the current driver matches the database driver.

4. Solution:

a. If this issue is caused by an incorrect URL, modify the URL to a format supported by the driver, such as jdbc:mysql://localhost:3306/.

b. If this issue is caused by a driver mismatch, use the corresponding driver according to the customer's database driver.

1. Problem description:

MySQL reports an error saying "Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES)."

2. Cause analysis:

The database server rejected the user from this IP address.

3. Troubleshooting steps:

a. Check whether the username and password are correct.

b. Check the permission setting.

4. Solution:

a. If this issue is caused by an incorrect username or password, use the correct one.

b. If this issue is caused by permission setting, modify it as below.

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION.

Note:

If the foregoing issue still exists, check if there is a mysqlzt. If this process is running, it may cause a username and password conflict, resulting in an abnormal connection, where you can connect to a MySQL database using Navicat, but the data connection defined in FR and data connection widgets trigger the above error. (The cause is under investigation.)

1. Problem description:

com.mysq1.jdbc.PacketTooBigException: Packet for query is too large (4739923 > 1948576). You can change this value on the server by setting the 'max_allowed_packet' variable.

2. Cause analysis:

The error message prompts to set the max_ allowed_ packet parameter, which refers to the maximum amount of data that the client or database server can accept. If you want to process the big data packet, increase this parameter value in both the client and the server.

The error message indicates that the size of the data packet being queried is 4739923 bytes, which is larger than the set value of 1948576 bytes (about 2MB). The value of max_allowed_packet set on the server is too small.

3. Troubleshooting steps:

Enter the database and execute the following command.

show variables like 'max_allowed_packet'

Check if the returned value is 1948576. If so, see the solution.

4. Solution:

Modify the MySQL configuration file my.ini or my.cnf. Add or modify the value of max_allowed_packetd below [mysqld].

[mysqld]
max_allowed_packet=128M

1. Problem description:

Your password has expired. To log in, you need to change it using a client that supports expired passwords.

2. Cause analysis:

The password has expired.

3. Troubleshooting steps:

Log in as a root user using a command. (Login in this way does not expire, and you can log in with the original password.)

4. Solution:

Log in as a root user using a command. (Login in this way does not expire, and you can log in with the original password.)

Reset the password as shown below.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'

1. Problem description:

The server time zone value 'XXX' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

2. Cause analysis:

The time zone of the server cannot be recognized, or it represents more than one time zone, where the time zone needs to be specified.

3. Troubleshooting steps:

Check if the URL has a specified time zone parameter.

4. Solution:

Add the time zone parameter ?serverTimezone=UTC (&serverTimezone=UTC) after the URL. 

1. Problem description:

java.sql.SQLException: null, message from server: "Host 'xxx.xxx.xxx.xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'."

2. Cause analysis:

If the client’s connection to the server takes longer than the value of connect_timeout, the server will record an error for this client. If the number of errors reaches max_connect_errors, this client will be locked.

3. Troubleshooting steps:

Check if the value of max_connect_errors is too small.

4. Solution:

a. The default value in MySQL is 10, and you can set it larger based on specific needs, such as setting it to 1000.

Use the following command.

set global max_connect_errors=1000;

b. Enter the MySQL console and execute the following command.

flush hosts;

1. Problem description:

Establishing SSL connection without server's identity verification is not recommended.

2. Cause analysis:

This issue lies in MySQL SSL and only occurs in MySQL 5.5.45+, 5.6.26+, and 5.7.6+.

3. Troubleshooting steps:

Check if the URL contains useSSL=false, which means not to use SSL.

4. Solution:

Add ?useSSL=false to the URL. 

1. Problem description:

It reports an error saying "incorrect string value: 'xf0x9f."

2. Cause analysis:

The data to be inserted contains an Emoji or certain special characters, which are 4 bytes, while the UTF-8 charset in MySQL can handle up to 3 bytes, so the data cannot be inserted.

3. Troubleshooting steps:

Check the Unicode character sets supported by MySQL using the following command.

show variables like'character%';

4. Solution:

a. Set character_set_server=utf8mb4 in the MySQL configuration file.

Modify the my.ini file for Windows system or the /etc/my.cnf file for Linux system.

[mysqld]
character-set-server=utf8mb4
[mysql]
default-character-set=utf8mb4

Restart MySQL service.

b. Or directly log in to MySQL by inputting a command in the terminal.

set character_set_server = utf8mb4;

1. Problem description:

The server selected protocol version TLS10 is not accepted by client preferences [TLS12].

2. Cause analysis:

This issue may be caused by that the higher version of JDK enables the TLSv1 and TLSv1.1 protocols by default which are of high-security level to ensure the security of information transmission, while the server still uses a protocol of a lower version.

This issue has occurred in JDK 8U291 and higher versions.

3. Troubleshooting steps:

Check the security settings of JDK to see if TLSv1 and TLSv1.1 are enabled.

4. Solution:

Modify the security settings of JDK. Find the jdk.tls.disabledAlgorithms item in /JAVA%HOME%/jre/lib/security/java.security.

jdk.tls.disabledAlgorithms=SSLv3,TLSv1, TLSv1.1, RC4, DES, MD5withRSA, \
DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL, \
include jdk.disabled.namedCurves

Delete TLSv1 and TLSv1.1.

1. Problem description:

An error is reported during MySQL data connection saying that "Streaming result set com.mysql.cj.protocol.a.result.ResultsetRowsStreaming@6480db 19 is still active."

2. Cause analysis:

If a result set is open in streaming mode on a MySQL connection, you cannot use that same database connection to run other queries until the result set is closed.

Such problems occur if the last result set is not closed correctly due to a connection leak caused by business reasons or bugs.

3. Troubleshooting steps:

Consult with the R&D team to confirm if it is a bug. If it is not a bug, see the solution.

4. Solution:

Add clobberStreamingResults=true after the URL, which can automatically close the streaming result set and any incomplete data stream from the server will be discarded if another query is executed.

1. Problem description:

An error is reported during MySQL data connection saying that "Unknown system variable 'query_cache_size'."

2. Cause analysis:

This issue lies in the MySQL driver version. The query_cache_size parameter has been removed in MySQL 8, but it still works with driver 5.1.44.

3. Troubleshooting steps:

Check the customer's database version and the version of the currently used driver.

4. Solution:

Update to the latest MySQL driver.

1. Problem description:

An error is reported during MySQL data connection saying that "Your password has expired." To log in, you need to change it using a client that supports expired passwords.

2. Cause analysis:

The password expires. From MySQL 5.7.4 to 5.7.10 the default value of default_password_lifetime variable is 360 (1 year-ish).

For these versions, if you make no changes to this variable or individual user accounts, all passwords expire after 360 days.

3. Troubleshooting steps:

See the solution.

4. Solution:

The following three methods require logging in as the root user using the command line. (Login in this way does not expire, and you can log in with the original password.)

mysql  -u root  -p

a. Reset password.

ALTER USER   'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your new password';

b. Or set the current user's password to never expire.

ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

c. Or disable password expiration for all user accounts.

SET GLOBAL default_password_lifetime =0;

1. Problem description:

An error is reported during MySQL data connection saying that "The last packet successfully received from the server was 74,689,844 milliseconds ago."

2. Cause analysis:

The connection has been kept for too long, and the server has not received any data for a while.

3. Troubleshooting steps:

Check the connection pooling to see if no validation is enabled.

4. Solution:

a. Always stay connected.

Enable idle connection test, or add autoReconnect=true into the URL to maintain automatic reconnection.

b. Enable pre-connection validation and check whether the connection is valid before using it (Recommended).

1. Problem description:

Message from server: "Host 'xxx.xxx.xxxx.xxx' is not allowed to connect to this MySQL server.

2. Cause analysis:

Access from the client host with this IP address is not allowed by the database.

3. Troubleshooting steps:

Run the following statement in the MySQL database.

select host from mysql.user where user ='username';

If the displayed value is localhost, the database only allows access from localhost and you need to allow access from other hosts to address this issue.

4. Solution:

a. Allow access from specified hosts.

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

b. Or allow access from all hosts.

update mysql.user set host='%' where user='username';
FLUSH PRIVILEGES;

1. Problem description:

An error is reported during MySQL data connection saying that "net_write_timeout".

2. Cause analysis:

It takes longer for the network to be written to the database than the set time, which defaults to 600s for MySQL, and the MySQL server aborts the write.

3. Troubleshooting steps:

Check if the write takes too long, or execute the following command.

show variables like 'net_write_timeout';

Check if the value of net_write_timeout is set too small.

4. Solution:

a. Adjust the logic of writing to the database to reduce the workload and avoid occupying the connection for a long time.

b. Or increase the net_write_timeout value according to actual needs.

1. Problem description:

An error is reported during MySQL data connection saying "java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'."

2. Cause analysis:

Starting from MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password, and this plugin cannot be used if the driver version is too old.

3. Troubleshooting steps:

Check if the database version is higher than 8.0.4, and if the driver version is 5.x. If so, the driver version is lower than required.

4. Solution:

Update MySQL driver to 8.x.

1. Problem description:

An error is reported during MySQL data connection saying "Caused by: java.sql.SQLException: The MySQL server is running with the --read-only option so it cannot execute this statement."

2. Cause analysis:

The currently accessed database is in a read-only mode. It may be a backup server of a cloud server or a read-only node of database clusters, or it has been set to read-only by the operation and maintenance personnel.

3. Troubleshooting steps:

a. Check whether the database server is a read-only node or a backup node. If so, see the Solution a.

b. If it isn't, this database is set to read-only. Execute the following command.

show variables like 'read_only'

If the value is TRUE, see the Solution b.

4. Solution:

a. Change it to a read-write node.

b. Execute the following statement to close the read-only mode.

set GLOBAL read_only = false

1. Problem description:

There is an error message in MySQL Data Connection log saying "Caused by: java.net.ssl.SSLException: Unsupported record version Unknown-0.0."

The data connection widget reports an error saying "Caused by: javax.net.ssl.SSLHandshakeException:No appropriate protocol(protocol is disabled or cipher suites are inappropriate)."

2. Cause analysis:

SSL connection fails.

3. Troubleshooting steps:

See the solution.

4. Solution:

Add the parameter ?useSSL=false after the data connection to disable SSL. If this issue occurs in external databases, causing access failure by the platform, you can modify the URL in the db.properties file.

1. Problem description:

An error occurs in the MySQL dataset during template previewing saying "java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic."

2. Cause analysis:

This issue is caused by high CPU usage of the customer's database server.

3. Troubleshooting steps:

MySQL can be used normally before and this issue occurred suddenly. The customer replaced the driver but it didn't work, and the SQL statement responded slowly in the database. This issue was solved after a restart and reconnection.

4. Solution:

Reduce database CPU usage and ask the database administrator to check the performance of the database server.