REFERENTIAL_INTEGRITY FALSE and creating tables

[Patrick Wright]

Hi 

This is with H2 1.3.172.

I am trying to import an existing MySQL database schema from a legacy project into an in-memory H2 instance, for unit-testing purposes. 

Unfortunately, the MySQL database schema is taken from a database dump, and the tables are not listed in dependency order vis-a-vis foreign key constraints. This causes errors when I try to load the schema - as constraints are sometimes declared before the parent table has been created. I had thought, from the documentation, that SET REFERENTIAL_INTEGRITY FALSE these constraints would not be checked, but it doesn't appear to work (at least when creating tables).

Below is a simple example that shows the problem. Note that the second table, test_2, depends on the third table in the schema, test_1. Applying this script results in

Table "TEST_1" not found;

It would be great to have a workaround for this, as otherwise I need to manually massage the dump file to reorder the tables (and then maintain that massaged version forever).

Thanks!

Patrick

SET REFERENTIAL_INTEGRITY FALSE;

DROP TABLE IF EXISTS `test_3`;

CREATE TABLE `test_3` (

  `id` bigint(20) NOT NULL,

  PRIMARY KEY (`id`),

) ;

DROP TABLE IF EXISTS `test_2`;

CREATE TABLE `test_2` (

  `id` bigint(20) NOT NULL,

  `test_1_id` bigint(20) NOT NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `FK_TEST_1_ID` FOREIGN KEY (`test_1_id`) REFERENCES `test_1` (`id`)

) ;

DROP TABLE IF EXISTS `test_1`;

CREATE TABLE `test_1` (

  `id` bigint(20) NOT NULL,

  PRIMARY KEY (`id`)

) ;

[Noel Grandin]

Sorry, there isn't much we can do about that. 

How would we maintain the integrity of our internal data structures if we allowed constraints to be created that referenced non existent tables? 

The way I would do it is to use the h2 console to cut chunks out if the script and run them, gradually whittling the script down until you had the whole thing captured. 

Then you use our script dumping tool to get a decent schema dump. 

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Patrick Wright]

Hi Noel

in MySQL itself, it is possible to skip constraint checks on create tables (apparently) via

SET FOREIGN_KEY_CHECKS = 0;

(http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_foreign_key_checks, "Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships.")

How that is actually implemented in the MySQL server, and whether that's at all possible in H2, is another question.

In practical terms, my problem is that I am not the owner of this schema, so if I create a cleaned-up version of the database dump, with tables in dependency order, then I have to maintain that across changes to the original schema. I'm already doing some simple pre-processing to remove MySQL syntax that H2 appears to not accept (things like the ENGINE=). But with tables that's a more time-consuming problem to solve as it involves re-ordering chunks of the script.

If this behavior is not supported in H2, I'll see if I can get separate dumps for tables without constraints, and for the constraints alone.

Thanks again for the clarification.

Patrick

[Noel Grandin]

Hi

We have a similar setting for skipping actually doing the constraint checking, but what we __don't__ allow is setting up constraints that reference non-existent tables.

Note that we already support a lot of MySQL-specific syntax, like the "ENGINE=" stuff, which we just ignore.

You could try getting a schema dump where the constraints are specified out-of-line, that might make it easier to apply.

Regards, Noel.

[Patrick Wright]

Hi Noel,

thanks for clarifying what SET REFERENTIAL_INTEGRITY FALSE does in H2.

Note that we already support a lot of MySQL-specific syntax, like the "ENGINE=" stuff, which we just ignore.

There are some part of the CREATE TABLE syntax that are not skipped, which is what I was referring to; I will open a low-priority ticket to track these. For example

CREATE TABLE x....) ENGINE=INNODB;

is accepted, but

CREATE TABLE x....) ENGINE=INNODB DEFAULT CHARSET=latin1;

is rejected. Similarly, C-style (/**/) comments are also rejected. These appear in some dumps to specify MySQL-specific database settings, such as in 

/*!40101 SET character_set_client = @saved_cs_client */;

That said, I've been able to use H2 successfully for unit-testing on previous projects that targeted MySQL. Apart from minor problems in SQL syntax the support was fantastic and I have been recommending H2 to my colleagues for some time now. It's a great piece of work. I'm really not writing to complain, but to understand what the limits of the MySQL mode are.

 

You could try getting a schema dump where the constraints are specified out-of-line, that might make it easier to apply.

This is what I will try next.

Best,

Patrick

[Thomas Mueller]

Hi,

> C-style (/**/) comments are also rejected

Actually, H2 supports them (meaning, it is threaded as a comment and ignored). Example:

    select * from test /* comment */

Regards,

Thomas

--

[Patrick Wright]

Ah, thanks for verifying that. It turns out that mysqldump had generated in-comment directives like

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

which (as you can see) are ended with a semicolon. It appears that H2 will ignore the comment, but if the trailing semicolon is present, the parser will abort.

Syntax error in SQL statement "                                          ;[*] "; SQL statement:

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; [42000-172]

I had taken this to mean that type of comment was not supported.

Thanks,

Patrick

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rrQmOzRbWgU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

OK I see. I will try to change the parser to support it. MySQL itself throws an exception for both an empty statement and a statement with semicolon, but works with a comment and semicolon. PostgreSQL supports all three cases. I will try to change H2 to match the PostgreSQL behavior. This should also solve this problem.