Category Archives: MYSQL

[Solved] Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

1. Today’s learning content

ERROR [com.alibaba.druid.pool.DruidDataSource] – create connection error, url: jdbc: mysql://127.0.0.1:3306/db_ local?serverTimezone=UTC& useSSL=false& autoReconnect=true, errorCode 0, state 08001 java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

This is actually caused by the database not being opened. Just open it

However, the default “wait_timeout” of the MySQL server is 28800 seconds, that is, 8 hours, which means that if a connection is idle for more than 8 hours, MySQL will automatically disconnect the connection, but the connection pool thinks the connection is still valid (because the validity of the connection is not verified). When the application applies for the connection, the above error will be caused.

So we’d better revise the time

Problem solving steps
1. Versions before mysql5 can directly add “autoreconnect = true” to the configuration of JDBC connection URL.

2. Set the MySQL global variable Change the value of wait_timeout to the maximum. Check the manual of mysql5 and find the wait under windows and the maximum value of Linux_timeout is 24 days and 365 days respectively.

Add a line at the end of the file my.ini: wait_timeout=1814400. (the file is in MySQL installation directory under windows and/etc/my.ini under Linux) the author’s directory is MySQL\data\MySQL server 8.0

Restart MySQL.

The specific exceptions are as follows:

[2019-04-20 18:05:24.161] [Druid-ConnectionPool-Create-1967852205] ERROR [com.alibaba.druid.pool.DruidDataSource] - create connection error, url: jdbc:mysql://127.0.0.1:3306/db_local?serverTimezone=UTC&useSSL=false&autoReconnect=true, errorCode 0, state 08001
java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:108)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:87)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:61)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:71)
    at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:932)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:857)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:444)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226)
    at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:148)
    at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:211)
    at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:142)
    at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1410)
    at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1464)
    at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:1969)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

Linux Install MySQL Error: error: Failed dependencies: libaio.so.1()(64bit) is needed by mysql-community-server-5.7.26-1.el7.x86_64

Therefore, this dependency may be missing and needs to be installed

Installation command: Yum install libaio

After executing this command, you can continue to install mysql.

[Solved] SQLyog Connect MYSQL Error: 2058 plugin caching_sha2_password could not be loaded

Sqlyog database connection error plugin caching_sha2_password could not be loaded

Download the new version MySQL 8.0.11 installation.

To facilitate installation and viewing, I downloaded the sqlyog tool to connect to MySQL

An error is reported when configuring a new connection: the error number is 2058. It is analyzed that the MySQL password encryption method has changed.

Solution: under windows, CMD log in to MySQL – U root – P log in to your MySQL database, and then execute this SQL:

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

#Password is the root password you set yourself

MYSQL 8.0 Login Error: caching_sha2_password [How to Solve] 

problem causes:
1 In the past, the mysql password authentication plug-in was mysql_native_passwd
2 After mysql8.0 version, the password authentication plug-in uses caching_sha2_password

Method:
Modify the password authentication method and change back to the mysql_native_passwd plug-in

Solution:
1 vim /etc/my.cnf add the following content:
[mysqld]
default_authentication_plugin=mysql_native_password

2 Log in to mysql and execute the following sql:
ALTER USER'root'@'%' IDENTIFIED WITH mysql_native_password BY'password';
flush privileges;

[Solved] MYSQL8.0 Error: the user specified as a definer (‘root’@’%’) does not exist

Description (can be ignored, see the solution below directly)

When modifying database data, the user-specified as a definer (‘root ‘@’% ‘) does not exist error is encountered

Using the method of Online

grant all privileges on *.* to root@"%" identified by "Passwd"

Prompt syntax error

The reason is that mysql8.0 grant authorization is not followed by identified by

Re-enter

grant all privileges on *.* to 'root'@'%';

Error again

After querying the data, it is found that it is a version problem. After version 8.0.11, the grant statement is removed to add users, that is, Grant… Can only be applied to existing accounts, not through grant   To add an account.

resolvent

mysql> create user 'root'@'%' identified by 'password';
Query OK, 0 rows affected (2.35 sec)
 
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.06 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

Local processing

create user 'root'@'%' identified by '1234';

grant all privileges on *.* to 'root'@'%';

flush privileges;

end!

mysqldump[Warning] Using a password on the command line interface can be insecure

First of all, I want to say that this thing is a warning. You don’t have a problem. OK, you can retreat

When I wrote a script today, I found that this warning was reported and then I didn’t respond. I always thought it was an error. In fact, it was because the execution time of mysqldump command was a little long (related to the amount of data you exported);

This script is given below to synchronize data between different databases

@ECHO OFF

@REM Remote database password
set remoteDbPwd=123
@REM local database password
set localDbPwd=123

@ECHO ON
REM source
set sourceDatabase=db1

REM target
set targetDatabase=db2

set sourceIp=127.0.0.1

@ECHO OFF
echo begin dump...

mysqldump --opt %sourceDatabase% -h %sourceIp% -uroot -p%remoteDbPwd% > config.sql

echo end dump...

echo insert to:%targetDatabase...
mysql -uroot -p%localDbPwd% %targetDatabase% < config.sql
echo success...

del config.sql /s /q

pause
exit

[Solved] Pandas Connect MSYQL Error: 1115, “unknown character set: ‘utf8mb4′”

The code is as follows:

Errors are reported as follows:

First, why does this error occur:

The analysis is as follows:

With the popularity of smartphones, we begun to often use emoticons to help us communicate better. However, the utf8 encoding of MySQL only supports 3 bytes of data, while the expression data of the mobile terminal is 4 bytes of characters, so utf8mb4 appears (its minimum MySQL version supports version 5.5.3 +, while mine is 5.1, so an error is caused)

Problem-solving:

Uninstall MySQL version 5.1 and install the new version

Here is an installation blog: CentOS installation mysql8.0

After installation, run the program again,

The program runs normally

[Solved] MYSQL Error: django.db.utils.OperationalError: (1045:Access denied for user ‘root’@’localhost’ (using password: NO)

Error Messages:
django.db.utils.OperationalError: (1045:Access denied for user ‘root’@’localhost’ (using password: NO)
Solution:
Run the following in mysql console, to change the password encryption method to the old version in Mysql(it is changed to use cha2 in Mysql 8.0)


mysql -u root -p
use mysql；
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';  
FLUSH PRIVILEGES;

Reference:

https://stackoverflow.com/questions/50652587/django-db-utils-operationalerror-1045access-denied-for-user-rootlocalhost

MySQL Changes the configuration file (my.ini) Error [How to Solve]

1. Background

After installing mysql, change the configuration file my.int, and change its data directory. Restart MySQL and it cannot be started.

2. Troubleshooting

Check the log and find that the error is:

[ERROR] Can't find error-message file '/data/mysql/share/mysql/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.

By searching the errmsg.sys file, copy it to the error directory and give corresponding permissions. Restart or report the same error. Change the MySQL configuration file back to the default. It is found that it can be started normally, and there is no other relevant error information in the log.

3. Solution

After troubleshooting for a period of time without results, I thought about whether the file could not be found due to the Linux security group SELinux. I closed SELinux randomly and found that the problem was solved.

setenforce 0           #Temporarily disable selinux
Change SELINUX=enforcing to SELINUX=disabled in the /etc/selinux/config file # permanently disable selinux
getenforce #Check the status of selinux