All geek questions in one place

SQL Commands Cannot Be Satisfied With H2

The following SQL commands are actually for MySQL. I am not a SQL expert and know little about H2. The Spring application throws an exception because it is not possible to create a user_roles table. This is the problem with fk_username_idx :

  DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS user_roles; CREATE TABLE users (  userid VARCHAR(5) NOT NULL,  username VARCHAR(45) NOT NULL ,  password VARCHAR(60) NOT NULL ,  enabled TINYINT NOT NULL DEFAULT 1 ,  PRIMARY KEY (userid));  CREATE TABLE user_roles (  user_role_id int(11) NOT NULL AUTO_INCREMENT,  userid varchar(5) NOT NULL,  role varchar(45) NOT NULL,  PRIMARY KEY (user_role_id),  UNIQUE KEY uni_username_role (role,userid),  KEY fk_username_idx (userid),  CONSTRAINT fk_username FOREIGN KEY (userid) REFERENCES users (userid));

ERROR:

 Caused by: org.h2.jdbc.JdbcSQLException: Unbekannter Datentyp: "FK_USERNAME_IDX" Unknown data type: "FK_USERNAME_IDX"; SQL statement:  CREATE TABLE user_roles (   user_role_id int(11) NOT NULL AUTO_INCREMENT,   userid varchar(5) NOT NULL,   role varchar(45) NOT NULL,   PRIMARY KEY (user_role_id),   UNIQUE KEY uni_username_role (role,userid),   KEY fk_username_idx (userid),   CONSTRAINT fk_username FOREIGN KEY (userid) REFERENCES users (userid)) [50004-191] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) at org.h2.message.DbException.get(DbException.java:179) at org.h2.message.DbException.get(DbException.java:155) at org.h2.command.Parser.parseColumnWithType(Parser.java:4059) at org.h2.command.Parser.parseColumnForTable(Parser.java:3922) at org.h2.command.Parser.parseCreateTable(Parser.java:5864) at org.h2.command.Parser.parseCreate(Parser.java:4217) at org.h2.command.Parser.parsePrepared(Parser.java:360) at org.h2.command.Parser.parse(Parser.java:315) at org.h2.command.Parser.parse(Parser.java:287) at org.h2.command.Parser.prepareCommand(Parser.java:252) at org.h2.engine.Session.prepareLocal(Session.java:560) at org.h2.engine.Session.prepareCommand(Session.java:501) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188) at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:170) at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158) at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:472) ... 47 more
+1
source share
1 answer

  • Are you sure your H2 is running in MySQL compatibility mode? Check it out first. Look at the connection string. For example, for example:

     final SimpleDriverDataSource ds = new SimpleDriverDataSource(); ds.setDriverClass(Driver.class); ds.setUrl("jdbc:h2:mem:test;MODE=mysql;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"); ds.setUsername("sa"); ds.setPassword("");

  • FOREIGN KEY fk_username_idx (userid), not accompanied by H2. Remove FOREIGN here.

Then he will work

+1
source

Source: https://habr.com/ru/post/1237034/

More articles:


All Articles