#StackBounty: #spring #spring-boot #spring-jdbc Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test.s…

Bounty: 50

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>


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.