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> 2 Answers
Answers 1
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.
Answers 2
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 
0 comments:
Post a Comment