Getting MySQLSyntaxErrorException with Hibernate Spatial and MySQL 5.6

Question

I'm using a following stack: Hibernate 5.2.10.FINAL (hibernate-spatial, hibernate-java8) MySQL 5.6.38 (also tested on latest 5.7 with same results) querydsl-apt, querydsl-jpa, querydsl-sql-spatial
It's all based on Spring Boot.

I'm trying to persist this simple class in my DB:

@Entity
public class ParkingPlace extends AbstractEntity {

@Column(name = "location", nullable = false, columnDefinition = "Point")
private Point geoLoc;

private String address;
private String city;
private String name;
private String desc;

private int capacity;
private int freePlacesLeft;

@ManyToOne
@JoinColumn(name = "owner_id")
private Owner owner;
}

AbstractEntity is:

@MappedSuperclass
public abstract class AbstractEntity implements Serializable, Cloneable {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Version
private int version;
}

I also have this Repository interface:

@NoRepositoryBean
public interface JpaQueryDslPredicateRepository<T, ID extends Serializable>
    extends QueryDslPredicateExecutor<T>, JpaRepository<T, ID> {
@Override
List<T> findAll(OrderSpecifier<?>... orders);

@Override
List<T> findAll(Predicate predicate);

@Override
List<T> findAll(Predicate predicate, OrderSpecifier<?>... orders);

@Override
List<T> findAll(Predicate predicate, Sort sort);
}

And my ParkingPlaceRepository:

public interface ParkingPlaceRepository
    extends JpaQueryDslPredicateRepository<ParkingPlace, Long> {}

Last important beat are properties that I'm using:

spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.mysql.MySQL56SpatialDialect
spring.jpa.show-sql=true
spring.datasource.platform=mysql
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
logging.level.org.hibernate.type=TRACE

I've also tried this dialects: MySQL5SpatialDialect, MySQL56InnoDBSpatialDialect (deprecated but used in a project I'm basing on), MySQLSpatialDialect.

I'm trying to fill the table with initial dataset with this function:

    private static void addPlaceToRepo(ParkingPlaceRepository repo, GeometryFactory factory, double lat, double lon, String city, String address, String name, int capacity) {
    ParkingPlace parkingPlace = new ParkingPlace();

    parkingPlace.setGeoLoc(factory.createPoint(new Coordinate(lat, lon)));
    parkingPlace.setCity(city);
    parkingPlace.setAddress(address);
    parkingPlace.setName(name);
    parkingPlace.setCapacity(capacity);

    repo.save(parkingPlace);
}

Sadly, with all of my best efforts, I'm still getting errors like this:

Hibernate: alter table order drop foreign key FKbb5wakyppwqmfuhp53p3jvs5u
gru 19, 2017 9:51:15 PM org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl handleException
WARN: GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
...
Caused by: 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 'order drop foreign key FKbb5wakyppwqmfuhp53p3jvs5u' at line 1

Here it states that the table isn't actually created and I've seen during the debug in MySQL Workbench that indeed, it isn't:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'ppclone.parking_place' doesn't exist

But why? It's create-drop, should work.

Full log can be seen here. I'll be glad for your help!

score 0 · Answer 1 · answered Dec 20 '17 at 05:30

You have to annotate your entity class as follows :

    @Entity
    @Table(name = "parking_place")
    public class ParkingPlace extends AbstractEntity {
    ...
      @Column(name = "name")
      private String name;
    ...
    }

@Entity

@Entity marks this class as an entity bean, so it must have a no-argument constructor that is visible with at least protected scope.

@Table

The @Table annotation allows you to specify the details of the table that will be used to persist the entity in the database.

@Column

@Column annotation is used to specify the details of the column to which a field or property will be mapped.

check this for more details click here

Sorry for responding late. The solution you provided doesn't work. The class is marked with '@Entity', '@Table' and '@Column' annotations gets the names you mentioned by default. Although I know that, I tested and confirmed it still doesn't work. I'm still getting this awkward error about incorrect SQL syntax. Do you have any idea what can be done to solve this? — przemulala, Jan 03 '18 at 18:36

score 0 · Accepted Answer · answered Jan 03 '18 at 19:22

0

I found the solution thanks to this SO thread. Answer: desc is reserved keyword in MySQL, change the field name or provide column name with sth else than 'desc'!

answered Jan 03 '18 at 19:22

przemulala

85
1
12

Getting MySQLSyntaxErrorException with Hibernate Spatial and MySQL 5.6

2 Answers2