Problems and solutions when replacing Oracle with MySQL

Migration Tools

During testing, powerdesinger was used to convert the table structure and Navicat was used to import data. The amount of data in the production environment is large, and colleagues in the data group will choose other tools to migrate it and supplement it when the time comes.

Application transformation

Add mysql8.0 driver package

Use mysql-connector-java-8.0.15.jar. If it is managed by Maven, add dependencies directly:

                <!--MySql Driver-->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.15</version>
			<scope>runtime</scope>
		</dependency>

Modify data source configuration

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://host:ip/database?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=password

Object transformation

• If you use hibernate, delete the auto-increment sequence of the primary key in the configuration file, set the primary key to auto-increment in MySQL; or create a corresponding function for the sequence. If you use MySQL cluster, the way the primary key is generated will change.
• Check each sql statement, especially the sql statements spliced ​​in mybatis, including:
  • Primary key modification: delete the sequence, change the primary key in the database to auto-increment; or create a corresponding auto-increment function
  • To process the fields involving mysql keywords, use the `` identifier
  • Date format handling
  • Change rownum condition query to limit condition query
    

Problem Summary

Problem: Local remote connection to MySQL database, report 10060 login exception

• Possible reasons for this problem:
  

1. The network is not accessible; 2. The service is not started; 3. The firewall is not closed; 4. The firewall port on the server is not open; 5. The port is not being monitored; 6. Insufficient permissions. I am here to troubleshoot and find out why port 3306 on the test database server is not open.

• Solution:

sudo vim /etc/sysconfig/iptables
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
sudo service iptables restart
sudo iptables -L -n

Problem: Navicat connecting to MySQL8 results in 2059 error

• Reason: The encryption rule in versions before mysql8 is mysql_native_password, and after mysql8, the encryption rule is caching_sha2_password
  
• Solution: Change the encryption rules

mysql -uroot -ppassword #Loginuse mysql; #Select databaseALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #Change encryption methodALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; #Update user passwordFLUSH PRIVILEGES; #Refresh permissions

Problem: Using Navicat to migrate data reports an error --> [Err] [Dtf] 1426 - Too-big precision 7 specified for 'TIME_CREATE'. Maximum is 6.

• Reason: Oracle's DATE type is 7 digits, while MySQL's time type is at most 6 digits, so it cannot be imported.
  
• Solution: Change DATE in the Oracle database to TIMESTAMP and change the length to 6 (be sure to change both the type and length before saving), then you can import.

Problem: Error when inserting varchar field during data migration --> Data too long for column 'DESIGNER' at row 1

• Reason: Oracle and MySQL use different encoding sets, resulting in different storage length requirements for the same characters (further in-depth understanding is required)
  
• Solution: You need to extend the field length in msyql
  

Problem: SpringBoot connects to MySQL and reports an error --> Unknown system variable 'query_cache_size'

• Reason: The MySQL driver jar package version is too low and is not compatible with MySQL 8.0.
• Solution: Use mysql-connector-java-8.0.15.jar and change the driver name to com.mysql.cj.jdbc.Driver
  

Problem: After changing to MySQL, the Chinese characters displayed in the front-end page of the application are garbled.

• Cause: Check the encoding format from three dimensions: database, server, and page
  
  • MySQL database encoding format check
  • Check the server encoding format
  • Front-end page coding format check
    

Finally, it was discovered that the encoding format was set to utf-8 in the Navicat connection, which caused the imported data to appear normal in Navicat, but garbled in the database and the query results. I really checked this for a long time, but I didn't notice the reason for the tool. .

• Solution: Reset the encoding format, import the data, and display it normally.

The above are the details of the problems encountered and solutions when switching from Oracle to MySQL. For more information about switching from Oracle to MySQL, please pay attention to other related articles on 123WORDPRESS.COM!