2022. 6. 16. 10:27ㆍDataBase/DB 오류정리
스프링을 사용해서 mysql 데이터베이스에 연결하여 테이블을 만드는 과정이었다. 기존에는 문제없이 잘 만들어지던 table이었는데 이번에는 반복적으로 오류가 존재하여 생성 불가하다는 문구가 반복돼서 나왔다.
@Entity
@Getter
@Setter
@NoArgsConstructor
public class Item {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(nullable = false)
private Long id;
@Column(nullable = false)
private String work;
@Column
private boolean check;
@Builder
public Item(Long id, String work, boolean check) {
this.id = id;
this.work = work;
this.check = check;
}
}만들고자 하던 Item이라는 테이블이다. 연결이 되었는지 확인하고자 실행버튼을 누르면 아래의 오류 문구가 등장한다.
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table item (id bigint not null auto_increment, check bit, work varchar(255) not null, primary key (id)) engine=InnoDB" via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:458) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:442) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:325) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:169) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:138) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:124) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:168) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:85) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:335) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:471) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1498) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.3.20.jar:5.3.20]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.3.20.jar:5.3.20]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-5.3.20.jar:5.3.20]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) ~[spring-orm-5.3.20.jar:5.3.20]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.20.jar:5.3.20]
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154) ~[spring-context-5.3.20.jar:5.3.20]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:908) ~[spring-context-5.3.20.jar:5.3.20]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.20.jar:5.3.20]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:147) ~[spring-boot-2.7.0.jar:2.7.0]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:734) ~[spring-boot-2.7.0.jar:2.7.0]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:408) ~[spring-boot-2.7.0.jar:2.7.0]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:308) ~[spring-boot-2.7.0.jar:2.7.0]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1306) ~[spring-boot-2.7.0.jar:2.7.0]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1295) ~[spring-boot-2.7.0.jar:2.7.0]
at com.self.webtodo.WebToDoApplication.main(WebToDoApplication.java:10) ~[main/:na]
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bit, work varchar(255) not null, primary key (id)) engine=InnoDB' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.29.jar:8.0.29]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.29.jar:8.0.29]
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763) ~[mysql-connector-java-8.0.29.jar:8.0.29]
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648) ~[mysql-connector-java-8.0.29.jar:8.0.29]
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-4.0.3.jar:na]
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-4.0.3.jar:na]
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
... 34 common frames omitted
오류의 내용 중 확인해야 할 부분이다.
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bit, work varchar(255) not null, primary key (id)) engine=InnoDB' at line 1말그대로 문법의 오류이다. db의 칼럼명은 예약어를 피해서 지정해야 한다 처음에는 id부분이 문제인 줄 알고 있었다.
하지만 지속적으로 에러가 나왔고 제타위키에서 mysql 예약어를 살펴보니 check도 예약어로 지정이 되어있어서 오류가 났던 것이다.
| ADD | ALL | ALTER |
| ANALYZE | AND | AS |
| ASC | ASENSITIVE | BEFORE |
| BETWEEN | BIGINT | BINARY |
| BLOB | BOTH | BY |
| CALL | CASCADE | CASE |
| CHANGE | CHAR | CHARACTER |
| CHECK | COLLATE | COLUMN |
| CONDITION | CONSTRAINT | CONTINUE |
| CONVERT | CREATE | CROSS |
| CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP |
| CURRENT_USER | CURSOR | DATABASE |
| DATABASES | DAY_HOUR | DAY_MICROSECOND |
| DAY_MINUTE | DAY_SECOND | DEC |
| DECIMAL | DECLARE | DEFAULT |
| DELAYED | DELETE | DESC |
| DESCRIBE | DETERMINISTIC | DISTINCT |
| DISTINCTROW | DIV | DOUBLE |
| DROP | DUAL | EACH |
| ELSE | ELSEIF | ENCLOSED |
| ESCAPED | EXISTS | EXIT |
| EXPLAIN | FALSE | FETCH |
| FLOAT | FLOAT4 | FLOAT8 |
| FOR | FORCE | FOREIGN |
| FROM | FULLTEXT | GRANT |
| GROUP | HAVING | HIGH_PRIORITY |
| HOUR_MICROSECOND | HOUR_MINUTE | HOUR_SECOND |
| IF | IGNORE | IN |
| INDEX | INFILE | INNER |
| INOUT | INSENSITIVE | INSERT |
| INT | INT1 | INT2 |
| INT3 | INT4 | INT8 |
| INTEGER | INTERVAL | INTO |
| IS | ITERATE | JOIN |
| KEY | KEYS | KILL |
| LEADING | LEAVE | LEFT |
| LIKE | LIMIT | LINES |
| LOAD | LOCALTIME | LOCALTIMESTAMP |
| LOCK | LONG | LONGBLOB |
| LONGTEXT | LOOP | LOW_PRIORITY |
| MATCH | MEDIUMBLOB | MEDIUMINT |
| MEDIUMTEXT | MIDDLEINT | MINUTE_MICROSECOND |
| MINUTE_SECOND | MOD | MODIFIES |
| NATURAL | NOT | NO_WRITE_TO_BINLOG |
| NULL | NUMERIC | ON |
| OPTIMIZE | OPTION | OPTIONALLY |
| OR | ORDER | OUT |
| OUTER | OUTFILE | PRECISION |
| PRIMARY | PROCEDURE | PURGE |
| READ | READS | REAL |
| REFERENCES | REGEXP | RELEASE |
| RENAME | REPEAT | REPLACE |
| REQUIRE | RESTRICT | RETURN |
| REVOKE | RIGHT | RLIKE |
| SCHEMA | SCHEMAS | SECOND_MICROSECOND |
| SELECT | SENSITIVE | SEPARATOR |
| SET | SHOW | SMALLINT |
| SONAME | SPATIAL | SPECIFIC |
| SQL | SQLEXCEPTION | SQLSTATE |
| SQLWARNING | SQL_BIG_RESULT | SQL_CALC_FOUND_ROWS |
| SQL_SMALL_RESULT | SSL | STARTING |
| STRAIGHT_JOIN | TABLE | TERMINATED |
| THEN | TINYBLOB | TINYINT |
| TINYTEXT | TO | TRAILING |
| TRIGGER | TRUE | UNDO |
| UNION | UNIQUE | UNLOCK |
| UNSIGNED | UPDATE | USAGE |
| USE | USING | UTC_DATE |
| UTC_TIME | UTC_TIMESTAMP | VALUES |
| VARBINARY | VARCHAR | VARCHARACTER |
| VARYING | WHEN | WHERE |
| WHILE | WITH | WRITE |
| XOR | YEAR_MONTH | ZEROFILL |
따라서 아래표에서 나온 예약어들을 피해서 Item을 재정의하고 실행을 눌러주면 오류 없이 실행이 된다.
@Entity
@Getter
@Setter
@NoArgsConstructor
public class Item {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(nullable = false)
private Long id;
@Column(nullable = false)
private String work;
@Column
private boolean click;
@Builder
public Item(Long id, String work, boolean click) {
this.id = id;
this.work = work;
this.click = click;
}
}
'DataBase > DB 오류정리' 카테고리의 다른 글
| [MYSQL] 워크벤치 error code:1046. No database selected (0) | 2022.05.01 |
|---|