Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSy

Question

I am developing springboot-springsession-jdbc-demo. When I simply run the code I get the following error. It looks to me some property must need to set in application.properties in order create the schema/tables before hand. Required configuration is already in placed and still it gives error. The code is present in https://github.com/sivaprasadreddy/spring-session-samples

The error for reference:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DELETE FROM SPRING_SESSION WHERE LAST_ACCESS_TIME < ?]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test.spring_session' doesn't exist
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:941) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.session.jdbc.JdbcOperationsSessionRepository$6.doInTransaction(JdbcOperationsSessionRepository.java:481) ~[spring-session-1.2.1.RELEASE.jar:na]
    at org.springframework.session.jdbc.JdbcOperationsSessionRepository$6.doInTransaction(JdbcOperationsSessionRepository.java:478) ~[spring-session-1.2.1.RELEASE.jar:na]
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) ~[spring-tx-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.session.jdbc.JdbcOperationsSessionRepository.cleanUpExpiredSessions(JdbcOperationsSessionRepository.java:478) ~[spring-session-1.2.1.RELEASE.jar:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_45]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_45]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_45]
    at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_45]
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65) ~[spring-context-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81) [spring-context-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_45]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_45]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_45]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [na:1.8.0_45]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_45]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_45]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_45]
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test.spring_session' doesn't exist
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_45]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_45]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_45]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[na:1.8.0_45]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.Util.getInstance(Util.java:387) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:877) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:870) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    ... 21 common frames omitted

pom.xml

    <!-- Parent pom providing dependency and plugin management for applications 
        built with Maven -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.0.RELEASE</version>
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>


    <dependencies>
        <!-- Spring Boot Starter Test -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- Spring Boot Starter JDBC -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!-- Spring Boot Starter Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- Spring Boot Starter Thymeleaf -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <!-- Spring Session -->
        <dependency>
            <groupId>org.springframework.session</groupId>
            <artifactId>spring-session</artifactId>
        </dependency>


        <!-- Spring Boot devtools -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>

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

        <!-- H2 DB -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
        </dependency>
    </dependencies>
</project>

Application.java

@SpringBootApplication
@EnableJdbcHttpSession
@EnableAutoConfiguration
public class Application{
    public static void main(String[] args){
        SpringApplication.run(Application.class, args);
    }
}

HomeController.java

@Controller
public class HomeController {
    private static final AtomicInteger UserId = new AtomicInteger(0);

    @RequestMapping("/")
    public String home(Model model){
        return "index";
    }

    @RequestMapping("/add-simple-attrs")
    public String handleSimpleSessionAttributes(HttpServletRequest req, HttpServletResponse resp){
        String attributeName = req.getParameter("attributeName");
        String attributeValue = req.getParameter("attributeValue");

        req.getSession().setAttribute(attributeName, attributeValue);

        User user = new User();
        user.setName(attributeValue);

        req.getSession().setAttribute(attributeName, user);
        return "redirect:/";
    }


    @RequestMapping("/add-object-attrs")
    public String handleObjectSessionAttributes(HttpServletRequest req, HttpServletResponse resp){
        String name = req.getParameter("name");
        User user = new User();

        user.setId(UserId.incrementAndGet());
        user.setName(name);

        req.getSession().setAttribute("USER_ID_"+user.getId(), user);
        return "redirect:/";
    }
}

User.java

public class User implements Serializable {
    private static final long serialVersionUID = 1L;

    private Integer id;
    private String name;

    public User(){

    }

    public User(Integer id, String name){
        this.id = id;
        this.name = name;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + "]";
    }

    public Integer getId(){
        return id;
    }

    public void setId(Integer id){
        this.id = id;
    }

    public String getName(){
        return name;
    }

    public void setName(String name){
        this.name = name;
    }
}

application.properties

logging.level.org.springframework=INFO

    ################### DataSource Configuration ##########################
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.url=jdbc:mysql://localhost:3306/test
    spring.datasource.username=root
    spring.datasource.password=root

    spring.datasource.initialize=true
    #spring.datasource.data=classpath:org/springframework/session/jdbc/schema-h2.sql
    spring.datasource.data=classpath:org/springframework/session/jdbc/schema-mysql.sql
    spring.datasource.continue-on-error=true
    spring.jpa.hibernate.ddl-auto=create
    #spring.jpa.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

index.html

 <!DOCTYPE html>
    <html xmlns="http://www.w3.org/1999/xhtml" 
          xmlns:th="http://www.thymeleaf.org">
    <head>
    <meta charset="utf-8"/>
    <title>Home</title>
    </head>
    <body>
        <h2 th:text="#{app.title}">App Title</h2>

        <h4>Add Simple Attributes To Session</h4>
        <form class="form-inline" role="form" action="add-simple-attrs" method="post">
            <label for="attributeName">Attribute Name</label>
            <input id="attributeName" type="text" name="attributeName"/>
            <label for="attributeValue">Attribute Value</label>
            <input id="attributeValue" type="text" name="attributeValue"/>
            <input type="submit" value="Set Attribute"/>
        </form>

        <h4>Add Object Attributes To Session</h4>
        <form class="form-inline" role="form" action="add-object-attrs" method="post">
            <label for="name">User Name</label>
            <input id="name" type="text" name="name"/>
            <input type="submit" value="Save"/>
        </form>


        <h3>Session Attributes</h3>
        <table>
            <thead>
                <tr>
                    <th>Attribute Name</th>
                    <th>Attribute Value</th>
                </tr>
            </thead>
            <tbody>
                <tr th:each="attr : ${session}">
                    <td th:text="${attr.key}">Name</td>
                    <td th:text="${attr.value}">Value</td>
                </tr>
            </tbody>
        </table>
    </body>
    </html>

Answer 1

I'm not sure this is the best solution (or even the solution as i'm new to spring and am not aware of the repercussions of doing this) but I was having the same issue, managed to solve it by deleting the annotation @EnableJdbcHttpSession

According to Vedran Pavic:

The reason spring.session.* do not work for you is because your are using @EnableJdbcHttpSession. That means that you are configuring Spring Session explicitly, so Spring Boot backs off with its auto-configuration.

You should remove @EnableJdbcHttpSession and ensure Spring Boot is auto-configuring Spring Session. Additionally, you could also leave spring.session.store-type out, since Spring Boot should be able to deduce which session repository implementation are you using as long as you have only only SessionRepository implementation on the classpath (i.e. you depend only on spring-session-jdbc and no other Spring Session modules).

Original Post

This will solve your immediate problem, but i'm unsure if it will break something else...

Answer 2

I've just had a quite similar error while using spring-boot 2.0.5 (as opposed to 1.4.0 used by OP) with Postgres driver:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DELETE FROM spring_sessions WHERE EXPIRY_TIME < ?]; nested exception is org.postgresql.u
til.PSQLException: ERROR: relation "spring_sessions" does not exist
  Position: 13
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234) ~[spring-jdbc-5.0.8.RELEASE.jar!/:5.0.8.RELEA
SE]
        // redacted...
        at java.lang.Thread.run(Thread.java:748) [na:1.8.0_181]
Caused by: org.postgresql.util.PSQLException: ERROR: relation "spring_sessions" does not exist
  Position: 13
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433) ~[postgresql-42.2.2.jar!/:42.2.2]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178) ~[postgresql-42.2.2.jar!/:42.2.2]
        // redacted...
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-5.0.8.RELEASE.jar!/:5.0.8.RELEASE]
        ... 16 common frames omitted

According to documentation, setting up Spring Session backed by a relational database is as simple as adding a single configuration property to your application.properties:

spring.session.store-type=jdbc

Note that in order to get the session tables auto-created, I had to also specify:

spring.session.jdbc.initialize-schema=always

Once this setting specified, Spring used the correct SQL initialization script from spring-session-jdbc jar. My mistake was not specifying that option - in which case embedded was being used as default value.

Answer 3

I think you have multiple issues By default jdbc has H2 database. It is automatically created by spring. Check if it is there. So first run it on H2. Then copy the same database and table to MySQL. create same schema and table to MySQL then change connection to MySQL.

Spring Boot automatically creates a DataSource that connects Spring Session to an embedded instance of H2 database src/main/resources/application.properties

spring.datasource.url= # JDBC URL of the database.
spring.datasource.username= # Login username of the database.
spring.datasource.password= # Login password of the database.

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root