MySQL5.7升级MySQL8.0.18升级填坑记录

|9909字|995阅读

安装(Windows10 + Docker(Docker Desktop for Windows) +MySQL8.0.X Image)

  • 配置文件映射本地文件
  • 数据卷映射本地文件夹
  • 日志映射本地文件

准备工作

  • 安装docker
  • 新建本地文件夹
  • 编写配置文件(mysql配置、日志)

安装命令:

1
2
3
4
5
6
7
8
# 拉取mysql镜像
docker pull mysql

# 启动一个MySQL容器实例
docker run -it -v F:/ProgramFiles/Docker/HostFileSyetem/MySQL/data:/var/lib/mysql:rw -v F:/ProgramFiles/Docker/HostFileSyetem/MySQL/log:/var/log/mysql:rw -v F:/ProgramFiles/Docker/HostFileSyetem/MySQL/config:/etc/mysql/conf.d:rw --name mysql8.0.18-dev3309 -e MYSQL_ROOT_PASSWORD=12345678ACANX -p 3309:3306 -d mysql

# 查看启动的容器
docker ps -a

登录容器修改MySQL服务器配置

  • 修改root用户的登录IP限制
  • 修改用户的密码模式,仍然使用mysql5.x时代的密码模式

变更

Docker端口冲突

  • 至今还未找到合适的解决办法

登录密码加密模式变更

  • MySQL5.x版本 使用 mysql_native_password
  • MySQL8.0.x 版本 使用 caching_sha2_password

MySQL方言类变更

  • @Deprecated org.hibernate.dialect.MySQL5InnoDBDialect
  • ClassNotFoundException: Could not load requested class : org.hibernate.dialect.MySQL8InnoDBDialect
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1803)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
	at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1108)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:868)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550)
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:315)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215)
	at com.acanx.blog.BlogServerApplication.main(BlogServerApplication.java:32)
Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:275)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
	at org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:152)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
	at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:175)
	at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:118)
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:900)
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:931)
	at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58)
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:378)
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1862)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1799)
	... 16 common frames omitted
Caused by: org.hibernate.boot.registry.selector.spi.StrategySelectionException: Unable to resolve name [org.hibernate.dialect.MySQL8InnoDBDialect] as strategy [org.hibernate.dialect.Dialect]
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.selectStrategyImplementor(StrategySelectorImpl.java:133)
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.resolveStrategy(StrategySelectorImpl.java:212)
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.resolveDefaultableStrategy(StrategySelectorImpl.java:160)
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.resolveDefaultableStrategy(StrategySelectorImpl.java:147)
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.resolveStrategy(StrategySelectorImpl.java:141)
	at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.constructDialect(DialectFactoryImpl.java:74)
	at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.buildDialect(DialectFactoryImpl.java:51)
	at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:137)
	at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
	at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263)
	... 33 common frames omitted
Caused by: org.hibernate.boot.registry.classloading.spi.ClassLoadingException: Unable to load class [org.hibernate.dialect.MySQL8InnoDBDialect]
	at org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.classForName(ClassLoaderServiceImpl.java:133)
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.selectStrategyImplementor(StrategySelectorImpl.java:129)
	... 43 common frames omitted
Caused by: java.lang.ClassNotFoundException: Could not load requested class : org.hibernate.dialect.MySQL8InnoDBDialect
	at org.hibernate.boot.registry.classloading.internal.AggregatedClassLoader.findClass(AggregatedClassLoader.java:210)
	at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:588)
	at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:521)
	at java.base/java.lang.Class.forName0(Native Method)
	at java.base/java.lang.Class.forName(Class.java:398)
	at org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.classForName(ClassLoaderServiceImpl.java:130)
	... 44 common frames omitted

1.什么是Hibernate方言?

Hibernate方言是用来告诉Hibernte如何对指定的数据库生成相应的SQL语句。 尽管做了很多尝试去使SQL语句标准化,但是不同的数据库支持的SQL语句还是有很多不同的地方。 所以Hibernate使用方言来辅助生成正确的SQL语句。

2.MySQL5Dialect与MySQL5InnoDBDialect有什么区别?

他们最大的区别是,在使用Hibernate创建表时MySQL5InnoDBDialect会在生成的建表SQL语句最后加上”ENGINE=InnoDB”。 InnoDB是一种MySQL数据库引擎.MySQL5.5及之后使用它做为默认引擎。它提供了ACID兼容的事务(Transaction)功能,并提供外键支持。

3.MySQLDialect与MySQL5Dialect有什么区别?

MySQLDialect是针对MySQL5之前的版本。主要变化还是在于建表SQL语句。 MySQL由于4到5还是有不小的变化。比如varchar在4及之前版本最大长度限制为255,5及之后版本最大长度限制为65535。 MySQLInnoDBDialect会在生成的建表SQL语句最后加上”TYPE=InnoDB”。

4.MySQL5InnoDBDialect过时了

  • 升级到 Hibernate 5 的时候,就会发现 MySQL5InnoDBDialect,被标注@Deprecated也就是过时了。不仅仅是 MySQL5InnoDBDialect 过时了,所有带InnoDB的 Dialect 都被标注过时了@Deprecated;在标注有 InnoDBDialect 过时的同时 新加了 MySQL55Dialect 及 MySQL57Dialect。 如果查看源码就会发现 MySQL55Dialect 与 MySQL5InnoDBDialect 源码一模一样。
  • 毕竟 MySQL 从 5.5 开始就默认使用 InnoDB 引擎,MySQL 8 已经移除了 MyISAM 引擎。Hibernate 的作者认为Dialect分为两类就没有什么必要了。
1
2
org.hibernate.dialect.MySQL55Dialect
org.hibernate.dialect.MySQL57Dialect

MySQL8.0.X以后建议在SpringBoot中使用一下配置:

1
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

后记

  • Hibernate 作者最坑的地方是标注了@Deprecated之时,补了一个注释“Use “hibernate.dialect.storage_engine=innodb” environment variable or JVM system property instead.”,导致我以为是通过配置的方式来指定引擎。我在 Spring Boot 中各种拼配置都没成功,后来发现这个配置是要写在 classpath 下的 hibernate.properties 内。

参考

|9909字|995阅读
点击加载Disqus评论
         
返回顶部