How to solve Can't create/write to file '/var/tmp/#sql_85b_0.MAI' error?

Question

At the time of deploying the spring boot war file on the server, we are facing below error.

    Caused by: java.sql.SQLException: Can't create/write to file '/var/tmp/#sql_85b_0.MAI' (Errcode: 2)
            at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.18.jar:8.0.18]
            at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.18.jar:8.0.18]
            at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.18.jar:8.0.18]
            at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) ~[mysql-connector-java-8.0.18.jar:8.0.18]
            at com.mysql.cj.jdbc.DatabaseMetaData$2.forEach(DatabaseMetaData.java:2124) ~[mysql-connector-java-8.0.18.jar:8.0.18]
            at com.mysql.cj.jdbc.DatabaseMetaData$2.forEach(DatabaseMetaData.java:2053) ~[mysql-connector-java-8.0.18.jar:8.0.18]
            at com.mysql.cj.jdbc.IterateBlock.doForAll(IterateBlock.java:56) ~[mysql-connector-java-8.0.18.jar:8.0.18]
            at com.mysql.cj.jdbc.DatabaseMetaData.getColumns(DatabaseMetaData.java:2229) ~[mysql-connector-java-8.0.18.jar:8.0.18]
            at com.zaxxer.hikari.pool.ProxyDatabaseMetaData.getColumns(ProxyDatabaseMetaData.java:90) ~[HikariCP-3.4.1.jar:na]
            at com.zaxxer.hikari.pool.HikariProxyDatabaseMetaData.getColumns(HikariProxyDatabaseMetaData.java) ~[HikariCP-3.4.1.jar:na]
            at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.populateTablesWithColumns(InformationExtractorJdbcDatabaseMetaDataImpl.java:354) ~[hibernate-core-5.4.6.Final.jar:5.4.6.Final]
            ... 46 common frames omitted
    01-Jan-2020 16:39:47.746 SEVERE [localhost-startStop-2] org.apache.catalina.core.ContainerBase.addChildInternal ContainerBase.addChild: start:
            org.apache.catalina.LifecycleException: Failed to start component [StandardEngine[Catalina].StandardHost[localhost].StandardContext[/daily-report-code-1.0]]
                    at org.apache.catalina.util.LifecycleBase.handleSubClassException(LifecycleBase.java:440)
                    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:198)
                    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:743)
                    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:719)
                    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:705)
                    at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:970)
                    at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1841)
                    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
                    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
                    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
                    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
                    at java.lang.Thread.run(Thread.java:748)
            Caused by: 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 javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.GenericJDBCException: Error accessing tables metadata
                    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.web.servlet.support.SpringBootServletInitializer.run(SpringBootServletInitializer.java:152)
                    at org.springframework.boot.web.servlet.support.SpringBootServletInitializer.createRootApplicationContext(SpringBootServletInitializer.java:132)
                    at org.springframework.boot.web.servlet.support.SpringBootServletInitializer.onStartup(SpringBootServletInitializer.java:92)
                    at org.springframework.web.SpringServletContainerInitializer.onStartup(SpringServletContainerInitializer.java:172)
                    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5154)
                    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
                    ... 10 more
            Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.GenericJDBCException: Error accessing tables metadata
                    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:403)
                    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)
                    ... 29 more
            Caused by: org.hibernate.exception.GenericJDBCException: Error accessing tables metadata
                    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
                    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
                    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
                    at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.convertSQLException(InformationExtractorJdbcDatabaseMetaDataImpl.java:102)
                    at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.populateTablesWithColumns(InformationExtractorJdbcDatabaseMetaDataImpl.java:387)
                    at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getTables(InformationExtractorJdbcDatabaseMetaDataImpl.java:341)
                    at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.getTablesInformation(DatabaseInformationImpl.java:120)
                    at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:65)
                    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
                    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
                    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
                    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
                    at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:320)
                    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462)
                    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:935)
                    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)
                    ... 33 more

application.property file contains,

        spring.datasource.url = jdbc:mysql://some-server-ip:3306/daily_report_db
        spring.datasource.username=root
        spring.datasource.password=root
        server.servlet.contextPath=/dailyreportcode
        spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver

   spring.datasource.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect 
        # Hibernate ddl auto (create, create-drop, validate, update)
        spring.jpa.hibernate.ddl-auto=update
        spring.jpa.show-sql=false
        spring.jpa.generate-ddl=true

It would be really appreciable if anyone of you can extend your help.

Answer 1

I am using mariaDB. /etc/my.cnf file contains,

    [mysqld]
    tmpdir=/tmp

So the solution is,

1. Create new folder using mkdir command

   mkdir tmp1

2. provide ownership permission to mysqld

   chown mysql:mysql /tmp1

3. Change tmpdir=/tmp to tmpdir=/tmp1 in my.cnf file

   tmpdir=/tmp1

4. restart mariadb service.

   service mariadb restart