java - Import data from mysql workbench to H2

Question

I'm trying to import data from mysql workbench to H2 and I got this error.

CREATE TABLE `jobs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `engjob` varchar(45) NOT NULL,
  `itajob` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=116 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci [42001-200] 42001/42001

which I am not sure why.
I would also like to ask if h2 is the right database to use. I'm trying to create a windows application using java with an embedded database.
Initially I used mySql workbench(mistake on my part). I'm currently trying to move all the data to H2 because I was told it might be easier this way but if you would like to suggest something different so I can make an application with an embedded database that would be helpful too.

thank you.

Answer 1

H2 and other standard-compliant DBMS expect something like

CREATE TABLE "JOBS" (
  "ID" INT GENERATED BY DEFAULT AS IDENTITY (START WITH 116),
  "ENGJOB" VARCHAR(45) NOT NULL,
  "ITAJOB" VARCHAR(45) NOT NULL,
  PRIMARY KEY ("ID")
)

But you're trying to execute non-standard MySQL-style SQL.

---

Actually current H2 accepts your non-standard definition in MySQL compatibility mode, but you need to compile H2 from its current sources (available on GitHub, building instructions are here) and enable this compatibility mode; these improvements weren't released yet.

---

Alternatively can simply remove all extra clauses after parentheses and use H2 1.4.200, but you may need its MySQL compatibility mode too: https://h2database.com/html/features.html#compatibility

In that case don't forget to restart the sequence with an additional command:

ALTER TABLE `jobs` ALTER COLUMN `id` RESTART WITH 116;

---

If you will have more questions about exceptions in SQL, please include the error message and the stack trace. In this question I see only the error code (42001-200), for more complex cases additional information may be needed.

---

There are three well-known pure Java embedded DBMS and neither of them can be used as drop-in replacement of MySQL.