Tag Archives: mysql

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

When closing the virtual machine, and then reopening the virtual machine, The following error will appear when executing the command systemctl start mysqld to start the mysql service: Job for mysqld.service failed because the control process exited with error code. See “systemctl status mysqld.service” and “journalctl -xe” for details.
 Insert picture description here
The above situation occurs because the virtual machine is shutting down, mysql service-related control process error, After my own learning and experience, I got a solution: First enter the specified directory/run, create a mysqld file in this directory, Then authorize this file, In this way, the mysql service can be started normally
Insert picture description here
In fact, when we install mysql, start When mysql service, use the command systemctl enable mysqld to set the boot to start automatically, the above error will not occur.

[Solved] TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:IllegalStateException: null

TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:IllegalStateException: null 

Solution:

select t1.citycode,count(distinct t1.cardno,t1.ndate) cnt,round(sum(t1.amount),2) amount from t2

inner join t1 on t1.cardno = t2.cardno

group by citycode

Execute the SQL commands above and report the following error:

SQL ERROR [500051] [HY000]: [Cloudera][ImpalaJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:IllegalStateException: null

Adjust the position of count(distinct t1.cardno,t1.ndate) and there will be no problem

[Solved] MYSQL Error: ERROR! MySQL server PID file could not be found!

After restarting the MySQL database on the Linux server, various errors were found, causing the MySQL database to fail to work normally.

No matter stop, start or mysql connection, there are different error messages. The specific error messages are:

# service mysqld stop
ERROR! MySQL server PID file could not be found!

# service mysqld start
Starting MySQL.. ERROR! The server quit without updating PID file (/data/mysql/mysql.pid).

# mysql -uroot -p
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

 

1. Error reason:

In fact, the above three different MySQL errors are all caused by one reason: the root directory disk is full.

2. Check the large files that cause the disk to be full:

1. First, use the df command to check the disk usage:
# df
You may see in the output:
/dev/mapper/VolGroup-lv_root 39841176 37788792 28552 100% /

2. Use # du – sh * to view large files. For example, the root directory is data. Use the command to view:
# cd/data
# du – sh *
You will find that the mysql folder occupies more than n gigabytes, and then view which files:
# cd mysql
# du – sh *
You will find many large files, such as
581M mysql-bin.000029
28K mysql-bin.000030
7.6G mysql-bin.000031
4.0K mysql-bin.000032

These files are MySQL Binary Log binary files, which are mainly used for data recovery and master-slave replication of master-slave servers.

Since I have no master and slave servers, I decided to delete them.

3. Solution:

1. Delete these large files:
# rm -rf mysql-bin.000031
~~~

2. After deleting them, open my.cnf under /etc/, find
log-bin=mysql-bin
binlog_format=mixed
Comment out these two lines, that is, add the “#” sign in front:
#log-bin=mysql-bin
#binlog_format=mixed

3. Restart the MySQL server, and it is OK
service mysqld restart

[Solved] sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, “You have an error in your SQ

Error Messages;

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "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 'fromt_user' at line 1")

 

The error message is like this, which means that my SQL syntax is wrong

Today, when pandas was connecting to the MySQL database, there was an error in the last run

Source code:

from sqlalchemy import create_engine

def query(table):
    host = 'localhost'
    user = 'root'
    password = '123456'
    database = 'ConstructionDB'
    port = 3306

    conn = create_engine('mysql+pymysql://{}:{}@{}:{}/{}'.format(user, password, host, port, database))

    sql = 'select * from' + table
    results = pd.read_sql(sql, conn)
    return results

Through debugging, it is found that there is no space in the original SQL statement when it is spliced, resulting in the last syntax of SQL

sql = [select * fromt_user]

Solved it!

def query(table):
    host = 'localhost'
    user = 'root'
    password = '123456'
    database = 'ConstructionDB'
    port = 3306
    conn = create_engine('mysql+pymysql://{}:{}@{}:{}/{}'.format(user, password, host, port, database))
    sql = 'select * from' +' ' + table
    results = pd.read_sql(sql, conn)
    return results

Pay attention to the SQL statement and splice a space in the middle of the code. The problem is solved

SqlSugar Connect MySql 8.0.29 Error [How to Solve]

1. Background

I exported the database table structure and data from the company server Mysql (version: 8.0.16) and restored it to my local computer Mysql (version: 8.0.29). Just after starting the project, I was prompted with a SqlSugar database connection error. The prompt error is as follows:

Execute Db.Ado.CheckConnection() reports an error
Connection open error . The given key ‘0’ was not present in the dictionary.

note: the connection string: database=stocks;server=127.0.0.1;port=3306;uid=root;pwd=123;

The following is the error report problem of individual table query.

DB.Queryable().Where(p=>true).ToList() error. ToList() error, after analysis, the main problem is the current table field character set and sorting rules are not uniform. The error is reported as follows.
MySqlException: “Fatal error encountered attempting to read the resultset.”
Internal exception MySqlException: Expected end of data packet

2. Solution

1. Solution to connection error

Add charset=utf8mb4, and the connection will no longer report errors note: if you want utf8mb4, I use utf8 locally and still report an error

database=stocks;server=127.0.0.1;port=3306;uid=root;pwd=123;charset=utf8mb4;

2. Modify the inconsistency of database character set

Execute the following SQL script as required.

(1) Change the encoding (character set) of a table and the encoding (character set) of all fields in the table:

ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
·TABLE_NAME replace to your table name.

(2) Change the encoding (character set) of all tables and the encoding (character set) of all fields in the table:

SELECT
	CONCAT(
		'ALTER TABLE ',
		TABLE_NAME,
		' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
	)
FROM
	information_schema.`TABLES`
WHERE
	TABLE_SCHEMA = 'DATABASE_NAME';
·DATABASE_NAME replace to your databese name.

3. Other contents

Query all table names of a database:

SHOW FULL COLUMNS FROM TABLE_NAME;
·TABLE_NAME replace to your table name.

[Solved] public key is not available client side (option serverRsaPublicKeyFile not set)

Error:

An occasional fault is found and an error is reported when initializing the database connection pool:

public key is not available client side (option serverRsaPublicKeyFile not set)

Detailed error reporting contents are as follows:

2022-08-24 16:35:08.008 ERROR 233504 --- [     main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Exception during pool initialization.

java.sql.SQLTransientConnectionException: Could not connect to address=(host=127.0.0.1)(port=3306)(type=master) : RSA public key is not available client side (option serverRsaPublicKeyFile not set)
 at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:79)
 at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:192)
 at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1372)
 at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:635)

After a long time of troubleshooting, it was finally found that the reason is:
the database used in the project is MySQL 8.0.X, but the connection driver used is MariaDB (MariaDB was used before). pom file dependencies are as follows:

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>2.6.1</version>
</dependency>

Solution:

Just change the driver to MySQL
pom.xml dependency

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

application.yml driver and URL configuration.

spring:
	datasource:
		driver: driver: com.mysql.cj.jdbc.Driver
		url: jdbc:mysql://127.0.0.1:3306/databaseName?serverTimezone=GMT%2B8&characterEncoding=utf8

[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.

    1. ①.rpm -qa | grep erlang
[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] 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] 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