Tag Archives: mysql

[Solved] Navicat Connect MySQL error: Authentication plugin ‘caching_sha2_password‘ cannot be loaded

The reason for this problem is: the different encryption rules before and after MySQL8 cause the problem, now let’s deal with it.

Windows system operation steps, Linux can refer to

Run CMD with administrator command

mysql -u root -p

If the command fails, the environment variable is not configured. After configuring the environment variable, execute it, or go to the bin folder of MySQL installation directory to execute the above statement.

Modify encryption rules

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;

update user password

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

refresh permission

 FLUSH PRIVILEGES;

reset password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';

After the modification is completed, go to Navicat to test the connection, and you can succeed.

[Solved] init datasource error, url: jdbc:mysql://lcoalhost:3306/test com.mysql.cj.jdbc.exceptions.Com

Error message:

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
	at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174) ~[mysql-connector-java-8.0.18.jar:8.0.18]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64) ~[mysql-connector-java-8.0.18.jar:8.0.18]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836) ~[mysql-connector-java-8.0.18.jar:8.0.18]
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456) ~[mysql-connector-java-8.0.18.jar:8.0.18]
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246) ~[mysql-connector-java-8.0.18.jar:8.0.18]
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199) ~[mysql-connector-java-8.0.18.jar:8.0.18]
	at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:156) ~[druid-1.1.10.jar:1.1.10]
	at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:218) ~[druid-1.1.10.jar:1.1.10]
	at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:150) ~[druid-1.1.10.jar:1.1.10]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1560) ~[druid-1.1.10.jar:1.1.10]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1623) ~[druid-1.1.10.jar:1.1.10]
	at com.alibaba.druid.pool.DruidDataSource.init(DruidDataSource.java:861) ~[druid-1.1.10.jar:1.1.10]
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1229) ~[druid-1.1.10.jar:1.1.10]
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1225) ~[druid-1.1.10.jar:1.1.10]
	at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:90) ~[druid-1.1.10.jar:1.1.10]
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:263) ~[spring-jdbc-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:376) ~[spring-tx-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:560) ~[spring-tx-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:347) ~[spring-tx-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99) ~[spring-tx-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747) ~[spring-aop-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) ~[spring-aop-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at com.baidu.fsg.uid.worker.DisposableWorkerIdAssigner$$EnhancerBySpringCGLIB$$17ab474d.assignWorkerId(<generated>) ~[classes/:?]
	at com.baidu.fsg.uid.impl.DefaultUidGenerator.afterPropertiesSet(DefaultUidGenerator.java:89) ~[classes/:?]
	at com.baidu.fsg.uid.impl.CachedUidGenerator.afterPropertiesSet(CachedUidGenerator.java:69) ~[classes/:?]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1862) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1799) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1287) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1207) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.autowireResource(CommonAnnotationBeanPostProcessor.java:537) ~[spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.getResource(CommonAnnotationBeanPostProcessor.java:513) ~[spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor$ResourceElement.getResourceToInject(CommonAnnotationBeanPostProcessor.java:653) ~[spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:224) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:116) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessProperties(CommonAnnotationBeanPostProcessor.java:334) ~[spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1429) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) ~[spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) [spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) [spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) [spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:879) [spring-beans-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:878) [spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550) [spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
	at cn.com.icbf.framework.uid.UidStartApplication.main(UidStartApplication.java:20) [classes/:?]
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

Solution:

This is because MySQL needs to specify whether to connect with SSL in the higher version: ?useSSL=false

MySQL Batch Add Data and Store Example 

DELIMITER $$
-- If the procedure already exists, delete it
DROP PROCEDURE IF EXISTS SAVERANDDATA;
-- Create procedure name
CREATE PROCEDURE SAVERANDDATA()
BEGIN
	DECLARE i INT;
	SET i = 0;
	-- Defining the MYSQL REPEAT Statement
	REPEAT
		-- SQL content
		INSERT INTO `lzhstore`.`almart_all` (
			`date_key`,
			`hour_key`,
			`client_key`,
			`item_key`,
			`account`,
			`expense`
		)
		VALUES
		(
			"2016-05-01",
			FLOOR(RAND() * 24),
			FLOOR(RAND() * 1000000) + 1,
			FLOOR(RAND() * 100000) + 1,
			FLOOR(RAND() * 20) + 1,
			FLOOR(RAND() * 10000) + 1
		);	
	SET i = i + 1;
  -- Ends the loop if executed one million times
  UNTIL i = 1000000 END REPEAT;
	END $$
	CALL SAVERANDDATA();

How to Solve gtid master-slave replication connecting Issue

Project scenario:

Master slave connection method

Problem Description:

Last_IO_Errno: 2003 Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 7

Cause analysis:

    1. when trying to log in to the master database to copy users from the server, an error of 113 is found

[ root@localhost mysql]# mysql -urepl -p123 -h 192.168.8.131 -P 3306 mysql: [Warning] Using a password on the command line interface can be insecure. Error 2003 (HY000): can't connect to MySQL server on '192.168.8.131' (113)

    1. check whether the main server firewall is closed. It is found that it is closed. It is speculated that the port is not open

systemctl status firewalld service

    1. 3. Check whether the 3306 port of the main database is open to the public. It is found that it is no

firewall CMD -- zone = public -- Query port = 3306/TCP

    1. 4 Open 3306 port

firewall CMD -- zone = public -- add port = 3306/TCP -- permanent

    1. 5 Update firewall

firewall CMD -- reload

 

Solution:

The master-slave replication connecting problem is mainly caused by the master and slave libraries can not be connected, after this problem found that although the firewall has been closed, but the port is not necessarily open to the public state, you need to set their own.

[Solved] Artisan error: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes

Problem Description:

php artisan migrate Error:

Illuminate\Database\QueryException 

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table `users` add unique `users_em
ail_unique`(`email`))

Cause Analysis:
The maximum character length of utf8 encoding supported by MySql is 3 bytes, if a wide character of 4 bytes is encountered, an insertion exception will occur. The maximum Unicode character that can be encoded by three bytes UTF-8 is 0xffff, which is the basic multiliterate plane (BMP) in Unicode. Therefore, Unicode characters that are not in the Basic Multicultural Plane, including Emoji emojis (Emoji is a special Unicode encoding), cannot be stored using MySql’s utf8 character set.

This should also be one of the reasons why Laravel 5.4 switched to the 4-byte length utf8mb4 character encoding. However, it should be noted that the utf8mb4 character encoding is only supported from MySql version 5.5.3 onwards (check the version: selection version();). If the MySql version is too low, a version update is required.

Solution:
1. Upgrade MySql version to 5.5.3 or higher. Add in /app/providers/AppServiceProvider.php:

use Illuminate\Support\Facades\Schema;

public function boot()
    {
        Schema::defaultStringLength(191);
    }

2. Delete the table in the database and re execute php artisan migrate

How to Solve c3p0 error (Cause & Solution)

Error Cause & Solution:
1. Configuration is wrong: driver=com.mysql.jdbc.Driver.
2. Wrong database connection address: url=jdbc:mysql://localhost:3306/test (test is user-defined creation, variable).
3. Wrong password or account: username=root (default is root).
4. Wrong account: password=root (password is set by the user).
5. The database is not started or not authorized to access.
6. The corresponding driver jar package mysql-connector-java-5.1.6-bin.jar has not been introduced, or the mysql-connector-java dependency has not been injected in the Maven project.

[Solved] Node.js: Error: connect ECONNREFUSED ::1:3306

Use node JS database module MySQL , connection database query error

Error: connect ECONNREFUSED ::1:3306

reason

I annotated the mapping relationship of /ECT/hosts 

# 127.0.0.1   localhost

The original configuration used localhost , so the database could not be connected suddenly

{
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'data',
};

Treatment method

Method 1:

Change the mapping relationship of /ECT/hosts 

# 127.0.0.1   localhost
127.0.0.1   localhost

Method 2:

You can use 127.0.0.1 instead

{
  // host: 'localhost',
  host: '127.0.0.1',
  user: 'root',
  password: '123456',
  database: 'data',
};

Ubuntu Error: dpkg: error processing package mysql-community-server (–configure):

Dpkg: error dpkg: error processing package mysql-community-server (–configure):

Whether sudo apt upgrade or sudo apt autoremove is used, the problem remains.

Use mysql deb package to install report error:

sudo apt upgrade
Reading package lists... Done
Building dependency tree
Reading state information... Done
Calculating upgrade... Done
The following packages have been kept back:
  libmysqlclient-dev mysql-client mysql-server mysql-testsuite
0 upgraded, 0 newly installed, 0 to remove and 4 not upgraded.
4 not fully installed or removed.
After this operation, 0 B of additional disk space will be used.
Do you want to continue?[Y/n] y
Setting up mysql-community-server (5.7.37-1ubuntu18.04) ...
update-alternatives: error: alternative path /etc/mysql/mysql.cnf doesn't exist
dpkg: error processing package mysql-community-server (--configure):
 installed mysql-community-server package post-installation script subprocess returned error exit status 2
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-community-server (= 5.7.37-1ubuntu18.04); however:
  Package mysql-community-server is not configured yet.

dpkg: error processing package mysql-server (--configure):
 dependency problems - leaving unconfigured
dpkg: dependency problems prevent configuration of mysql-community-test:No apport report written because the error message indicates its a followup error from a previous failure.

 mysql-community-test depends on mysql-community-server (= 5.7.37-1ubuntu18.04); however:
  Package mysql-community-server is not configured yet.

dpkg: error processing package mysql-community-test (--configure):
 dependency problems - leaving unconfigured
dpkg: dependency problems prevent configuration of mysql-testsuite:No apport report written because the error message indicates its a followup error from a previous failure.

 mysql-testsuite depends on mysql-community-test (= 5.7.37-1ubuntu18.04); however:
  Package mysql-community-test is not configured yet.

dpkg: error processing package mysql-testsuite (--configure):
 dependency problems - leaving unconfigured
No apport report written because MaxReports is reached already

The solution is as follows:

Try the following command

sudo apt-get install -f

To see if it can fix broken dependencies

If failed, clear MySQL server:

 sudo apt-get purge mysql-community-server mysql-community-server

Then clean up and update:

 sudo apt-get autoclean && sudo apt-get clean && sudo apt-get update

General upgrade:

 sudo apt-get upgrade

Now try reinstalling:

sudo apt-get install mysql-server and mysql-community-server

If you do this from the root prompt, the leading sudo command will not be booted

[Solved] canal Startup Error: error while reading from client socket java.io.IOException: Received error packet:

canal startup error: error while reading from client socket
java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file

canal server is canal-deployer

Use tail -f canal_server/logs/example/example.log or vi canal_server/logs/example/example.log

2022-02-18 02:09:30.403 [destination = example , address = /127.0.0.1:3306 , EventParser] ERROR c.a.o.canal.parse.inbound.mysql.dbsync.DirectLogFetcher - I/O error while reading from client socket
java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file
        at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102) ~[canal.parse-1.1.5.jar:na]
        at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:238) [canal.parse-1.1.5.jar:na]
        at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$1.run(AbstractEventParser.java:262) [canal.parse-1.1.5.jar:na]
        at java.lang.Thread.run(Thread.java:748) [na:1.8.0_312]
2022-02-18 02:09:30.403 [destination = example , address = /127.0.0.1:3306 , EventParser] ERROR c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - dump address /127.0.0.1:3306 has an error, retrying. caused by
java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file
        at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102) ~[canal.parse-1.1.5.jar:na]
        at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:238) ~[canal.parse-1.1.5.jar:na]
        at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$1.run(AbstractEventParser.java:262) ~[canal.parse-1.1.5.jar:na]
        at java.lang.Thread.run(Thread.java:748) [na:1.8.0_312]
2022-02-18 02:09:30.403 [destination = example , address = /127.0.0.1:3306 , EventParser] ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:example[java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file
        at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
        at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:238)
        at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$1.run(AbstractEventParser.java:262)
        at java.lang.Thread.run(Thread.java:748)
]

Solution:

There are three files in the instance directory

ubuntu03@ubuntu03:~/canal_server/conf/example$ ls -l
total 140
-rw-rw-r-- 1 ubuntu03 ubuntu03 135168 Feb 18 02:11 h2.mv.db
-rw-rw-r-- 1 ubuntu03 ubuntu03 135168 Feb 18 02:11 meta.dat
-rwxrwxr-x 1 ubuntu03 ubuntu03   2106 Feb 17 11:12 instance.properties

Delete h2.mv.db and meta.dat is OK

rm -rf canal_server/conf/example/h2.mv.db 
rm -rf canal_server/conf/example/meta.dat

restart canal-server(canal-deployer)

sh canal_server/bin/restart.sh