MySQLSyntaxErrorException: You have an error in your SQL syntax while upgrading

Question

I am upgrading from MySQL 5.7.22 (connector v5.1.40) to MySQL version 8.0.18 with mysql-connector-java v5.1.48 (JRE and JDK 7). And I am creating table using ddl script, some of the tables are created successfully, but for one table I get following error.

create table servicegroup(
id bigint(20) auto_increment,
name varchar(255),
description varchar(255),
recursive BIT,
created datetime,
modified datetime,
type varchar(255),
primary key (id)
)

Error message.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'recursive BIT, created datetime, modified datetime, type varchar(255), primary k' at line 5

How can I resolve this?

I checked your schema and the problem is with the recursive BIT. I don't know why there is the problem with the data name recursive. It will work if you change the name of BIT field to any other the code works. Please try changing the name if you can. — Santosh Aryal
Try quoting your column names in backticks - I wonder if it is name or type that is a reserved word. — halfer
recursive is a registered keyword: dev.mysql.com/doc/refman/8.0/en/… , try to avoid name/type as well (its also in the list). Or surround them in backticks, as already suggested. — Caramiriel

Anshu Patel Anshu Patel · Accepted Answer · 2020-04-29T19:44:04

As from the last comment, recursive is a reserved keyword, so it cannot be used as any table name or column name.

MySQLSyntaxErrorException: You have an error in your SQL syntax while upgrading

1 Answers