mysql Linux系统区分表名大小写问题

记录一个mysql数据库表区分大小写导致项目启动失败问题

之前一直在windows系统连接数据库一直没问题，后再吧数据库连接换到阿里云上linux系统后报如下错误，

 

Caused by: org.quartz.JobPersistenceException: Couldn't check for existence of job: Table 'iot_breed.QRTZ_JOB_DETAILS' doesn't exist
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.checkExists(JobStoreSupport.java:1964)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport$22.execute(JobStoreSupport.java:1955)
    at org.quartz.impl.jdbcjobstore.JobStoreCMT.executeInLock(JobStoreCMT.java:245)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeWithoutLock(JobStoreSupport.java:3785)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.checkExists(JobStoreSupport.java:1952)
    at org.quartz.core.QuartzScheduler.checkExists(QuartzScheduler.java:1513)
    at org.quartz.impl.StdScheduler.checkExists(StdScheduler.java:583)
    at com.ruoyi.quartz.service.impl.SysJobServiceImpl.updateSchedulerJob(SysJobServiceImpl.java:237)
    at com.ruoyi.quartz.service.impl.SysJobServiceImpl.init(SysJobServiceImpl.java:45)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:363)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:307)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:136)
    ... 36 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: Table 'iot_breed.QRTZ_JOB_DETAILS' doesn't exist
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1024)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3188)
    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3185)
    at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:181)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)
    at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.jobExists(StdJDBCDelegate.java:776)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.checkExists(JobStoreSupport.java:1962)
    ... 51 common frames omitted

 

分析后的主要原因是quartz查看的表名是大写的，而我们数据库的创建的表名是小写的；其中windows下mysql默认不区分表的大小写，而linux下是区分大小写的，可以用

show Variables like '%table_names'

命令查看是否区分大小写 （0 区分大小写  1 不区分）

要修改为不区分大小写，在网上查阅资料是在my.cnf文件中，mysqld节下添加如下参数（my.cnf文件一般在这个路径：/etc/my.cnf）：

 

set-variable=lower_case_table_names=1

但是改完以后数据库一直启动不成功，查看了mysql的启动日志（var/log/mysqld.log），提示未知的变量

 

后来查看其它资料吧set-variable去掉在添加

lower_case_table_names =1

修改完重启mysql后即可

最后查看已改为不区分大小写，项目也启动成功啦