How to fix error in SQL script?

Question

MySQL

There is an application (Spring mvc + Hibernate + MySQL), which is a book library service, student accounting, etc.
When starting the project, an error occurs in the initialization of one bean. Moreover, the consequence of it is an incorrect SQL query. Here is the full stack trace:

23-Aug-2018 11:36:25.367 WARNING [RMI TCP Connection(2)-127.0.0.1] org.springframework.context.support.AbstractApplicationContext.refresh Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.jdbc.datasource.init.DataSourceInitializer#0': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #15 of class path resource [META-INF/sql/test_data.sql]: INSERT INTO grades (grade) VALUES ('10Б'); nested exception is java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '10?' for key 'PRIMARY'
23-Aug-2018 11:36:25.379 SEVERE [RMI TCP Connection(2)-127.0.0.1] org.springframework.web.servlet.FrameworkServlet.initServletBean Context initialization failed
 org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.jdbc.datasource.init.DataSourceInitializer#0': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #15 of class path resource [META-INF/sql/test_data.sql]: INSERT INTO grades (grade) VALUES ('10Б'); nested exception is java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '10?' for key 'PRIMARY'
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1708)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:581)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:503)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:760)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:869)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550)
    at org.springframework.web.servlet.FrameworkServlet.configureAndRefreshWebApplicationContext(FrameworkServlet.java:672)
    at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:638)
    at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:686)
    at org.springframework.web.servlet.FrameworkServlet.initWebApplicationContext(FrameworkServlet.java:5

In principle, we can highlight the key (this error in the SQL query generated such a huge stack trace):

Failed to execute SQL script statement #15 of class path resource [META-INF/sql/test_data.sql]: INSERT INTO grades (grade) VALUES ('10Б'); nested exception is java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '10?' for key 'PRIMARY'

Actually, I have two SQL files in the recources/META-INF/sql folder. One of them creates tables, and the second one is this one:

insert into books (bookname, author, genre, country)
values ('Thinking in Java 4-th ed.', '1', 'tutorial', 'USA');
insert into books (bookname, author, genre, country)
values ('Thinking in Java 2-th ed.', '1', 'tutorial', 'USA');
insert into books (bookname, author, genre, country)
values ('The Walking Dead, Vol. 1', '2', 'comics', 'USA');
insert into books (bookname, author, genre, country)
values ('Super Mario Adventures', '3', 'comics', 'Japan');
insert into books (bookname, author, genre, country)
values ('Ghost in the Shell', '4', 'comics', 'Japan');

insert into authors (authorfirstname, authorlastname)
values('Bruce','Eckel');
insert into authors (authorfirstname, authorlastname)
values('Robert','Kirkman');
insert into authors (authorfirstname, authorlastname)
values('Kentaro','Takemura');
insert into authors (authorfirstname, authorlastname)
values('Shirow','Masamune');

insert into genres(genre) values ('tutorial');
insert into genres(genre) values ('comics');

INSERT INTO countries (country) VALUES ('USA');
INSERT INTO countries (country) VALUES ('Japan');

INSERT INTO grades (grade) VALUES ('10А');
INSERT INTO grades (grade) VALUES ('10Б');
INSERT INTO grades (grade) VALUES ('11А');
INSERT INTO grades (grade) VALUES ('11Б');

INSERT INTO students (firstname, lastname, grade)
    VALUES ('Максим', 'Иванов','10А');
INSERT INTO students (firstname, lastname, grade)
    VALUES ('Иван', 'Максимов','11Б');

INSERT INTO issues (book, student, issuedatetime, returndatetime)
VALUES (2, 1,'1990-01-01 00:00:01', '1992-01-01 00:00:01');

INSERT INTO issues (book, student, issuedatetime, returndatetime)
VALUES (3, 2, '2016-01-01 00:00:01', NULL );

INSERT INTO issues (book, student, issuedatetime, returndatetime)
VALUES (4, 1, '2018-01-01 00:00:01', NULL );

How can I fix this SQL file so that it doesn't exist Duplicate entry '10?' for key 'PRIMARY'?

Answer 1

Try the syntaxINSERT IGNORE INTO ...