search

hamidhtc / h2database

Automatically exported from code.google.com/p/h2database
0 stars 0 forks source link

H2 mode MySQL-foreigh key table references not found if the referenced tables are not being created prior of referencing them #489

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
Please send a question to the H2 Google Group or StackOverflow first,
and only then, once you are completely sure it is an issue, submit it here.
The reason is that only very few people actively monitor the issue tracker.

Before submitting a bug, please also check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1.Start h2 with MODE=MySQL 
2.CREATE TABLE `intents` (
  `project_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(128) NOT NULL,
  `destination_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`tag_id`),
  KEY `tags_unique` (`project_id`,`tag_name`),
  KEY `intents_destination_id_fk` (`destination_id`),
  CONSTRAINT `intents_project_id_fk` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON DELETE CASCADE,
) ENGINE=InnoDB CHARSET=utf8;

CREATE TABLE `projects` (
  `project_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` varchar(20) NOT NULL,
  `project_name` varchar(45) NOT NULL,
  `project_language` varchar(8) NOT NULL,
  `creationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `description` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`project_id`),
  UNIQUE KEY `company_id` (`company_id`,`project_name`),
  KEY `company` (`company_id`),
) ENGINE=InnoDB CHARSET=utf8;
-- commit all
COMMIT;

3.The following error will be returned: Caused by: 
org.h2.jdbc.JdbcSQLException: Table "PROJECTS" not found; SQL statement:
CREATE TABLE `intents` ( `project_id` int(11) NOT NULL, `tag_id` int(11) NOT 
NULL AUTO_INCREMENT, `tag_name` varchar(128) NOT NULL, `destination_id` int(11) 
DEFAULT NULL, PRIMARY KEY (`tag_id`), KEY `tags_unique` 
(`project_id`,`tag_name`), KEY `intents_destination_id_fk` (`destination_id`), 
CONSTRAINT `intents_project_id_fk` FOREIGN KEY (`project_id`) REFERENCES 
`projects` (`project_id`) ON DELETE CASCADE, ) ENGINE=InnoDB CHARSET=utf8 
[42102-172]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.schema.Schema.getTableOrView(Schema.java:419)
    at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:182)
    at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:70)
    at org.h2.command.ddl.CreateTable.update(CreateTable.java:169)
    at org.h2.command.CommandContainer.update(CommandContainer.java:79)
    at org.h2.command.Command.executeUpdate(Command.java:235)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:180)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:155)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:186)
    ... 51 more 

What is the expected output? What do you see instead?
The tables should be created 

What version of the product are you using? On what operating system, file
system, and virtual machine?
- H2 v1.3.172
- Windows 7 64bit
- java version "1.7.0_17"
Java(TM) SE Runtime Environment (build 1.7.0_17-b02)
Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

Do you know a workaround?

Create the project table before creating the intents table. Note that a MySQL 
instance will be able to execute successfully the script above 

What is your use case, meaning why do you need this feature?

How important/urgent is the problem for you?

very important. We need to decide if we will go with H2 mode MySQL or use a 
MySQL instance

Please provide any additional information below.

Original issue reported on code.google.com by queeq...@gmail.com on 12 Jul 2013 at 5:22

GoogleCodeExporter commented 9 years ago 
Just change the order of your create table statements, and it will work in both 
H2 and MySQL.

Original comment by noelgrandin on 15 Jul 2013 at 6:34

GoogleCodeExporter commented 9 years ago 
We are having the same problem. We dumped a MySQL schema to file and try to 
import it into H2. fails for that reason. Rearranging the tables by hand is not 
an option. Besides, it's impossible because we have circular dependencies. Yes, 
we could re-write them as ALTER TABLE statements, but it's supposed to be an 
automated process. Hope you can reconsider this and open it up for fixing.

Original comment by jan.christopher.vogt@gmail.com on 7 Jul 2014 at 11:48

GoogleCodeExporter commented 9 years ago 
I think it's better to use "alter table" statements, as this works with any 
database. I believe your solution only really works with MySQL, and no other 
database.

As a workaround could you run the script multiple times (ignoring errors), 
until you have all tables?

> We dumped a MySQL schema to file

I guess you used the MySQL tool to do that. Could you use a different tool, for 
example http://dbcopyplugin.sourceforge.net/ - but I'm not sure if there a 
command line tool for that

Original comment by thomas.t...@gmail.com on 7 Jul 2014 at 11:58