H2 Database - Unknown data type of MySQL script

h2 in-memory-database
vietsanto picture vietsanto · Nov 29, 2015 · Viewed 8.4k times · Source

I'm new in using in-memmory database.

I tried to use H2 database for developing project with spring boot, spring data JPA, but i got a problem when initialize application.

Caused by: org.h2.jdbc.JdbcSQLException: Unknown data type: "FK_PERSON__PERSONTYPE_IDX"; SQL statement:

Because this script was exported from MySQL. So i thinked there are some wrong syntax which H2 does not understand

For example, this is a part of script:

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `type` int(11) NOT NULL,
  `address` text,
  PRIMARY KEY (`id`),
  KEY `fk_person__persontype_idx` (`type`),
  CONSTRAINT `fk_person__persontype` FOREIGN KEY (`type`) REFERENCES `persontype` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='  ';

And i tried some solutions from these:

Convert MySQL script to H2

http://matthewcasperson.blogspot.de/2013/07/exporting-from-mysql-to-h2.html

Replace symbols with double quotes, single quotes,... even not use quote at all but not working. Please show me why? Thank you.

Answer

Søren picture Søren · Nov 2, 2018

I had a similar issue.

Removing the "KEY" line, which in your example corrosponds to:

KEY `fk_person__persontype_idx` (`type`),

worked for me.

Related questions

View content of H2 or HSQLDB in-memory database

Is there a way to browse the content of an H2 or an HSQLDB in-memory database for viewing? For example, during a debugging session with Hibernate in order to check when the flush is executed; or to make sure the …

Read More
Create an in-memory database structure from an Oracle instance

I have an application where many "unit" tests use a real connection to an Oracle database during their execution. As you can imagine, these tests take too much time to be executed, as they need to initialize some Spring contexts, …

Read More
Any ideas for persisting H2 Database In-Memory mode transaction?

The following code for H2 database with "in-memory mode" runs perfectly fine until connection is open or VM is running. But H2 db loses data when connection is closed or when VM shutdown takes place. Is there any other way …

Read More