Category Archives: MySQL

[Solved] Rabbitmq Startup Error: Job for rabbitmq-server.service failed because the control process exited with

Rabbitmq Startup Error:

Redirecting to /bin/systemctl status rabbitmq-server.service
● rabbitmq-server.service - RabbitMQ broker
Loaded: loaded (/usr/lib/systemd/system/rabbitmq-server.service; enabled; vendor preset: disabled)
Active: inactive (dead) (Result: exit-code) since 四 2021-06-17 18:01:07 CST; 1min 21s ago
Process: 6558 ExecStop=/usr/sbin/rabbitmqctl shutdown (code=exited, status=69)
Process: 6398 ExecStart=/usr/sbin/rabbitmq-server (code=exited, status=1/FAILURE)
Main PID: 6398 (code=exited, status=1/FAILURE)

June 17 18:01:00 xxxx systemd[1]: Failed to start RabbitMQ broker.
June 17 18:01:00 xxxx systemd[1]: Unit rabbitmq-server.service entered failed state.
June 17 18:01:00 xxxx systemd[1]: rabbitmq-server.service failed.
June 17 18:01:07 xxxx systemd[1]: Stopped RabbitMQ broker.
[root@xxxx /]# /sbin/service rabbitmq-server start
Redirecting to /bin/systemctl start rabbitmq-server.service
Job for rabbitmq-server.service failed because the control process exited with error code. See “systemctl status rabbitmq-server.service” and “journalctl -xe” for details.

Operating Environment
Operating system/software

Version

Linux xxxx3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
CentOS 7.x
erlang-21.3-1.el7.x86_64
21.3-1
rabbitmq-server-3.8.8-1.el7.noarch
3.8.8-1

Cause Analysis
According to the error log, there are two common causes.

erlang is not installed , resulting in a startup error.
First check whether erlang is installed.

[root@xxxx /]# rpm -qa|grep erlang
erlang-21.3-1.el7.x86_64

If erlang is not installed, result like “erlang-21.3-1.el7.x86_64” will not be displayed.

A host file configuration problem that causes the mq to not be read at startup.

[root@xxxx /]# vim /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.38.110 xxxx

If in the hosts file, the ip-hostname mapping is not added, just add it.
Remark.
To get the hostname.

[root@xxxx /]# hostname
xxxx

XXXX is the host name

Restart rabbitmq

[root@xxxx /]# systemctl restart rabbitmq-server 
[root@xxxx /]# systemctl status rabbitmq-server
● rabbitmq-server.service - RabbitMQ broker
   Loaded: loaded (/usr/lib/systemd/system/rabbitmq-server.service; enabled; vendor preset: disabled)
   Active: active (running) since 四 2021-06-17 19:07:19 CST; 56s ago
  Process: 11504 ExecStop=/usr/sbin/rabbitmqctl shutdown (code=exited, status=0/SUCCESS)
 Main PID: 11551 (beam.smp)
   Status: "Initialized"
   CGroup: /system.slice/rabbitmq-server.service
           ├─11551 /usr/lib64/erlang/erts-10.3/bin/beam.smp -W w -K true -A 64 -MBas ageffcbf -MHas ageffcbf -MBlmbcs 512 -MHlmbcs 512 -MMmcs 30 -P 10485...
           ├─11655 erl_child_setup 32768
           ├─11699 inet_gethost 4
           └─11700 inet_gethost 4

6月 17 19:07:16 xxxx rabbitmq-server[11551]: ##########  Licensed under the MPL 2.0. Website: https://rabbitmq.com
6月 17 19:07:16 xxxx rabbitmq-server[11551]: Doc guides: https://rabbitmq.com/documentation.html
6月 17 19:07:16 xxxx rabbitmq-server[11551]: Support:    https://rabbitmq.com/contact.html
6月 17 19:07:16 xxxx rabbitmq-server[11551]: Tutorials:  https://rabbitmq.com/getstarted.html
6月 17 19:07:16 xxxx rabbitmq-server[11551]: Monitoring: https://rabbitmq.com/monitoring.html
6月 17 19:07:16 xxxx rabbitmq-server[11551]: Logs: /var/log/rabbitmq/[email protected]
6月 17 19:07:16 xxxx rabbitmq-server[11551]: /var/log/rabbitmq/rabbit@xxxx_upgrade.log
6月 17 19:07:16 xxxx rabbitmq-server[11551]: Config file(s): (none)
6月 17 19:07:19 xxxx rabbitmq-server[11551]: Starting broker... completed with 3 plugins.
6月 17 19:07:19 xxxx systemd[1]: Started RabbitMQ broker.

The operation is successful, and the problem has been solved.

MYSQL Insert Data Error: check the manual that corresponds to your MySQL server version for the right syntax

Reason for error reporting

There are fields in the table with the same names as MySQL reserved keywords.

Solution:

Modify the conflict field name. For example, the field key in this article is changed to api_key

Error reporting environment

Mysql 8.0+Mybatis-Plus 3.0+SpringBoot

Error reporting scenario

Use the Mybatis-plus enhanced Service layer to insert a single piece of data, where the ID is self increasing and the inserted field is key.

Structure of error report

Main information of error reporting

2022-08-22 17:48:38.865 ERROR 26088 --- [nio-8001-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key )  VALUES  ( 'login' )' at line 1
### The error may involve com.integration.dao.IomImApiDao.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO iom_im_api  ( key )  VALUES  ( ?)
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key )  VALUES  ( 'login' )' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key )  VALUES  ( 'login' )' at line 1] with root cause

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key )  VALUES  ( 'login' )' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.12.jar:8.0.12]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.12.jar:8.0.12]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.12.jar:8.0.12]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:975) ~[mysql-connector-java-8.0.12.jar:8.0.12]
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:392) ~[mysql-connector-java-8.0.12.jar:8.0.12]
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498) ~[druid-1.1.10.jar:1.1.10]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) ~[mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) ~[mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) ~[mybatis-3.4.4.jar:3.4.4]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_281]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_281]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_281]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_281]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.1.jar:1.3.1]
	at com.sun.proxy.$Proxy77.insert(Unknown Source) ~[na:na]

Solution

Modify the database field key to api_key
Modify entity fields.

Modified table structure

[Solved] MySQL Startup Error: Job for mysqld.service failed because the control process exited with error code

Question

An error is reported when starting MySQL service, as shown below:

[root@node2 hadoop]# systemctl start mysqld.service
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.

[root@node2 hadoop]# systemctl start mysqld.service
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.

Solution

This is because the /var/lib/MySQL directory has insufficient permissions

[root@node2 hadoop]# cd /var/lib/mysql
[root@node2 mysql]# ll
all 167348
-rw-r-----. 1 root  root        56 6月  19 20:00  auto.cnf
-rw-r-----. 1 mysql mysql        0 6月  19 20:01  binlog.index
-rw-------. 1 root  root      1676 6月  19 20:00  ca-key.pem
-rw-r--r--. 1 root  root      1112 6月  19 20:00  ca.pem
-rw-r--r--. 1 root  root      1112 6月  19 20:00  client-cert.pem
-rw-------. 1 root  root      1676 6月  19 20:00  client-key.pem
-rw-r-----. 1 root  root    196608 6月  19 20:00 '#ib_16384_0.dblwr'
-rw-r-----. 1 root  root   8585216 6月  19 20:00 '#ib_16384_1.dblwr'
-rw-r-----. 1 root  root      3595 6月  19 20:00  ib_buffer_pool
-rw-r-----. 1 root  root  12582912 6月  19 20:00  ibdata1
-rw-r-----. 1 root  root  50331648 6月  19 20:00  ib_logfile0
-rw-r-----. 1 root  root  50331648 6月  19 20:00  ib_logfile1
drwxr-x---. 2 root  root         6 6月  19 20:00 '#innodb_temp'
drwxr-x---. 2 root  root         6 6月  19 20:00  mysql
-rw-r-----. 1 root  root  15728640 6月  19 20:00  mysql.ibd
drwxr-x---. 2 root  root      8192 6月  19 20:00  performance_schema
-rw-------. 1 root  root      1676 6月  19 20:00  private_key.pem
-rw-r--r--. 1 root  root       452 6月  19 20:00  public_key.pem
-rw-r--r--. 1 root  root      1112 6月  19 20:00  server-cert.pem
-rw-------. 1 root  root      1676 6月  19 20:00  server-key.pem
-rw-r-----. 1 root  root  16777216 6月  19 20:00  undo_001
-rw-r-----. 1 root  root  16777216 6月  19 20:00  undo_002

Modify permissions and start MySQL

[root@node2 ~]# setenforce 0 
[root@node2 ~]# chown -R mysql:mysql /var/lib/mysql
[root@node2 ~]# chmod -R 777 /var/lib/mysql
[root@node2 ~]# systemctl start mysqld.service
[root@node2 ~]# ps -ef |grep mysql
mysql      26627       1  4 23:57 ?       00:00:00 /usr/sbin/mysqld
root       26671   10438  0 23:57 pts/0    00:00:00 grep --color=auto mysql
[root@node2 ~]# cd /var/lib/mysql
[root@node2 mysql]# ll
all 190916
-rw-r-----. 1 mysql mysql       56 6月  19 23:57  auto.cnf
-rw-r-----. 1 mysql mysql      156 6月  19 23:57  binlog.000001
-rw-r-----. 1 mysql mysql       16 6月  19 23:57  binlog.index
-rwxrwxrwx. 1 mysql mysql     1680 6月  19 23:50  ca-key.pem
-rwxrwxrwx. 1 mysql mysql     1112 6月  19 23:50  ca.pem
-rwxrwxrwx. 1 mysql mysql     1112 6月  19 23:50  client-cert.pem
-rwxrwxrwx. 1 mysql mysql     1676 6月  19 23:50  client-key.pem
-rwxrwxrwx. 1 mysql mysql   196608 6月  19 23:57 '#ib_16384_0.dblwr'
-rwxrwxrwx. 1 mysql mysql  8585216 6月  19 23:50 '#ib_16384_1.dblwr'
-rwxrwxrwx. 1 mysql mysql     6059 6月  19 23:50  ib_buffer_pool
-rwxrwxrwx. 1 mysql mysql 12582912 6月  19 23:57  ibdata1
-rwxrwxrwx. 1 mysql mysql 50331648 6月  19 23:57  ib_logfile0
-rwxrwxrwx. 1 mysql mysql 50331648 6月  19 23:50  ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 6月  19 23:57  ibtmp1
drwxrwxrwx. 2 mysql mysql      187 6月  19 23:57 '#innodb_temp'
drwxrwxrwx. 2 mysql mysql      143 6月  19 23:50  mysql
-rwxrwxrwx. 1 mysql mysql 27262976 6月  19 23:57  mysql.ibd
srwxrwxrwx. 1 mysql mysql        0 6月  19 23:57  mysql.sock
-rw-------. 1 mysql mysql        6 6月  19 23:57  mysql.sock.lock
drwxrwxrwx. 2 mysql mysql     8192 6月  19 23:50  performance_schema
-rwxrwxrwx. 1 mysql mysql     1676 6月  19 23:50  private_key.pem
-rwxrwxrwx. 1 mysql mysql      452 6月  19 23:50  public_key.pem
-rwxrwxrwx. 1 mysql mysql     1112 6月  19 23:50  server-cert.pem
-rwxrwxrwx. 1 mysql mysql     1680 6月  19 23:50  server-key.pem
drwxrwxrwx. 2 mysql mysql       28 6月  19 23:50  sys
-rwxrwxrwx. 1 mysql mysql 16777216 6月  19 23:57  undo_001
-rwxrwxrwx. 1 mysql mysql 16777216 6月  19 23:57  undo_002
[root@node2 mysql]#

Note:

Setenforce 0 is used to solve [InnoDB] Operating system error number 13 in a file operation.
Directory permissions must be set to 777, just 755 will also cause an error

[Solved] MySQL Install Error: GPG check FAILED

I just got a Huawei Cloud ECS and chose Centos8 system, today I installed mysql, the version I chose is 5.7.

Import of key(s) didn't help, wrong key(s)?
Public key for mysql-community-client-5.7.39-1.el7.x86_64.rpm is not installed. Failing package is: mysql-community-client-5.7.39-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Public key for mysql-community-common-5.7.39-1.el7.x86_64.rpm is not installed. Failing package is: mysql-community-common-5.7.39-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Public key for mysql-community-libs-5.7.39-1.el7.x86_64.rpm is not installed. Failing package is: mysql-community-libs-5.7.39-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Public key for mysql-community-server-5.7.39-1.el7.x86_64.rpm is not installed. Failing package is: mysql-community-server-5.7.39-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
The downloaded packages were saved in cache until the next successful transaction.
You can remove cached packages by executing 'yum clean packages'.
Error: GPG check FAILED

Solution:

Re-import secret key

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

Then execute the installation command

yum -y install mysql-community-server

[Solved] MySQL Lock error: Lock wait timeout exceeded; try restarting transaction ; Lock wait timeout

The error is as follows:

Lock wait timeout exceeded; try restarting transaction ; Lock wait timeout

Finally, it was found that it was caused by a local debug jam. Just stop it

If a deadlock occurs, you can use the following command to differential lock and kill it

select * from information_schema.innodb_trx

Check Lock WAIT of trx_status field
copy trx_id

kill trx_id

If trx_status is RUNNING, ignore it, it means that the transaction is being executed without locks

[Solved] mongodump Error: assertion: 2 { ok: 0.0, errmsg: “Auth mechanism not specified”, code: 2, codeName: “BadValue”…

Mongodump error

assertion: 2 { ok: 0.0, errmsg: “Auth mechanism not specified”, code: 2, codeName: “BadValue”, operationTime: Timestamp 1573815888000|1, $clusterTime: { clusterTime: Timestamp 1573815888000|1,

reason

It is caused by the lower version of mongodump. You need to uninstall the default Yum source installation version and reinstall the new version.

System default installation version: 2. X
requirement Version (support cluster version): 4. X

terms of settlement:

create a file

/etc/yum.repos.d/mongodb-org-4.0.repo

[mongodb-org-4.0]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/4.0/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-4.0.asc

Installation configuration

yum install -y mongodb-org

[Solved] MYSQL Connect Error: Communications link failure

org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: 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.
### The error may exist in mappers/UserMapper.xml
### The error may involve com.chunqiu.mybatis.mapper.UserMapper.insertUser
### The error occurred while executing an update
### Cause: 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.

When we report the first few lines of error information as shown in the figure above, we can try to exclude common errors on the Internet as below:
1. MySQL data service is not enabled
2. serverTimezone parameter
3. wait_timeout settings

If the error is still reported, check whether there is this line after the error message
Caused by: javax.net.ssl.SSLHandshakeException

Caused by: javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)
	at sun.security.ssl.HandshakeContext.<init>(HandshakeContext.java:171)
	at sun.security.ssl.ClientHandshakeContext.<init>(ClientHandshakeContext.java:106)
	at sun.security.ssl.TransportContext.kickstart(TransportContext.java:238)
	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:410)
	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:389)
	at com.mysql.cj.protocol.ExportControlled.performTlsHandshake(ExportControlled.java:316)
	at com.mysql.cj.protocol.StandardSocketFactory.performTlsHandshake(StandardSocketFactory.java:188)
	at com.mysql.cj.protocol.a.NativeSocketConnection.performTlsHandshake(NativeSocketConnection.java:99)
	at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:352)
	... 55 more

Solution:
add the parameter usessl = false to the URL

jdbc:mysql://xxx.xxx.xxx.xxx/db?useSSL=false

Cause:
Communications link failure
Caused by: javax.net.ssl
Before MySQL 5.7, the security was low and there were test libraries that any user could connect to, so the official version 5.7 increased the privacy protection. The default value of useSSL = true was used to prevent arbitrary changes to the database. In version 8.0, SSL is still retained and the default value is true, so just set “?useSSL= false” and you’re done!

[Solved] flicksql cdc mysql to kafka Connect Error: org.apache.flink.table.api.ValidationException…

Error Messages: org.apache.flink.table.api.ValidationException: Could not find any factory for identifier 'debezium-json' that implements 'org.apache.flink.table.factories.SerializationFormatFactory' in the classpath.

Check if there is any package that I forgot to import
I didn’t import the flink-json package here

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-json</artifactId>
            <version>${flink.version}</version>
        </dependency>

The import is successful and Kafka can be connected normally~

[Solved] Worker 1 failed executing transaction ‘ANONYMOUS‘ at master log mall-mysql-bin.000001, end_log_pos

A problem encountered while configuring MySQL master-slave server in Docker.

The following error:

Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘ANONYMOUS’ at master log mall-mysql-bin.000001, end_log_pos 2251. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

Based on the hints given in the error message, execute in the mysql client to view the detailed error message.

select * from performance_schema.replication_applier_status_by_worker;

Worker 1 failed executing transaction ‘ANONYMOUS’ at master log
mall-mysql-bin.000001, end_log_pos 889; Error ‘Can’t create database
‘t1’; database exists’ on query. Default database: ‘t1’. Query:
‘create database t1’

Reasons:
1. The password policy problem of MySQL8, change the configuration file and use the policy of the previous version.
Execute these two commands in MySQL host client.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

Add a line to my.cnf that aligns MySQL8 with MySQL5.7 password authentication.

default_authentication_plugin=mysql_native_password

After the master and slave have changed this configuration, restart the master and slave.
(Each line of configuration in my.cnf file must remember to check if there are spaces at the end of the line. If there are spaces, delete them.)

docker restart mysql-master（Your own mysql host container name）

docker ps

docker restart mysql-slave（Your own mysql slave container name）

docker ps

2. My understanding is that the table already exists does not mean that your slave already exists. It means that the table already exists on the host before you configure the slave, so this problem will be reported.

Execute the following command on the slave MySQL client.

stop slave;

reset master;

Go to MySQL master and delete the database added by your own test.

drop database Add the database for your own testing;

show master status;

According to the values of File and Position of mysql-master, change the master_log_file and master_log_pos of the following command.

change master to master_host=‘192.168.159.200’, master_user=‘slave’,
master_password=‘root’, master_port=3307,
master_log_file=‘mall-mysql-bin.000002’, master_log_pos=331,
master_connect_retry=30;

After the change, execute this command on mysql-slave;

start slave;

show slave status\G

If you find that both Slave_IO_Running and Slave_SQL_Running show Yes, the MySQL master-slave configuration is successful.

As long as one of them is not Yes, it is something like Connecting or No, it means that the configuration is not successful.

After configuring the master-slave, create a new database and table on mysql-master, insert the data, and then go to the slave to verify that the data is synchronized over.

mysql-master

mysql-slave

So far, the installation of MySQL master-slave in docker is completed.

[Solved] Canal Error: Could not find first log file name in binary log index file

Check /home/admin/canal-server/logs/example/example.log and find the following error:

2022-07-20 00:00:08.473 [destination = example , address = mall-mysql/192.168.38.131:3306 , EventParser] ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:e
xample[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)

reason:

The binlog file set in the configuration file was not found

Solution:

Because the configuration file of instance.properties is packaged into the docker image, so it can only be modified in the instance

First check the binlog log file name and position in the database

Query in the mall-mysql database of this example:

mysql> show master status;

Output file: File: mysql-binlog.000233, Position: 652645

Enter the instance:

kubectl exec -ti mall-canal-84f6f7d7cc-xbghn bash -n nsName
xxx> vi /home/admin/canal-server/conf/example/instance.properties

Modify the position Info section:

canal.instance.master.address=mall-mysql:3306                                                                                                                                      
canal.instance.master.journal.name=mysql-binlog.000233                                                                                                                             
canal.instance.master.position=652645                                                                                                                                              
canal.instance.master.timestamp=                                                                                                                                                   
canal.instance.master.gtid=

Restart service:

xxx> cd /home/admin/canal-server
xxx> ./restart.sh

Check the log after restart and solve this error.

[Solved] pymysql.err.OperationalError: (1045, “Access denied for user ‘root‘@‘192.168.118.44‘

Error: pymysql.err OperationalError: (1045, “Access denied for user ‘root’@‘192.168.118.44’ (using password: YES)”)

Possible problems:
1. Firewall blocking,
Solution: log in to the server and turn off the firewall

systemctl stop firewalld.service

2. User empowerment (the root user I use here)
Solution: log in to the MySQL database for empowerment

grant all privileges on *.* to 'root'@'%' identified by 'password';
flush privileges;

3. Check whether the password is entered incorrectly

[Solved] dynamic-datasource can not find primary datasource

Error reporting details

When using mybatis plus multiple data sources, the startup message cannot find the master data source

com.baomidou.dynamic.datasource.exception.CannotFindDataSourceException: dynamic-datasource can not find primary datasource
	at com.baomidou.dynamic.datasource.DynamicRoutingDataSource.determinePrimaryDataSource(DynamicRoutingDataSource.java:91) ~[dynamic-datasource-spring-boot-starter-3.5.1.jar:3.5.1]
	at com.baomidou.dynamic.datasource.DynamicRoutingDataSource.getDataSource(DynamicRoutingDataSource.java:120) ~[dynamic-datasource-spring-boot-starter-3.5.1.jar:3.5.1]
	at com.baomidou.dynamic.datasource.DynamicRoutingDataSource.determineDataSource(DynamicRoutingDataSource.java:78) ~[dynamic-datasource-spring-boot-starter-3.5.1.jar:3.5.1]
	at com.baomidou.dynamic.datasource.ds.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:48) ~[dynamic-datasource-spring-boot-starter-3.5.1.jar:3.5.1]
......

Solution:

① The dependency of multiple data sources is introduced, but multiple data sources are not used

<!--This is the dependent version I use-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>

<!--document-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>${version}</version>
</dependency>

Multi data source usage: use @ds to switch data sources.

@DS can be annotated on methods or classes, and there is a proximity principle that annotations on methods take precedence over annotations on classes.

annotation	result
no @DS	Default data source
@DS(“databaseName”)	databaseName can be a group name or the name of a specific library

② Multiple data sources are used but the main data source is not specified

spring:
  datasource:
    dynamic:
      primary: master # Set the default data source or data source group, the default value is master
      strict: false #Strictly match the datasource, default false. true does not match the specified datasource throw an exception, false uses the default datasource
      datasource:
        master:
          url: jdbc:mysql://xx.xx.xx.xx:3306/dynamic
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver # This configuration can be omitted for SPI support since 3.2.0
        slave_1:
          url: jdbc:mysql://xx.xx.xx.xx:3307/dynamic
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.
        slave_2:
          url: ENC(xxxxxx) # Built-in encryption, please check the detailed documentation for use
          username: ENC(xxxxxxxxxx)
          password: ENC(xxxxxxxxxx)
          driver-class-name: com.mysql.jdbc.
       #...... omit
       #The above will configure a default library master, a group slave with two sub-banks slave_1,slave_2

③ Check carefully if there is any alignment in the configuration

# Correct format
spring:
  datasource:
    dynamic:
      strict: false
      primary: one
      datasource:
        one:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/demo?allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
          username: root
          password: 123456
        two:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/demo1?allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
          username: root
          password: 123456

ProgrammerAH

Programmer Guide, Tips and Tutorial