« Why I like Tomcat... | Main | Sun's Guerilla Canni... »
20060301 Wednesday March 01, 2006

DbUnit Tip: Turn off foreign key constraints when importing into MySQL One of the issues I've had with using DbUnit is getting tables to load in the proper order from XML. The XML datasets I use to load table data are flat and don't really have any notion of foreign keys and such. Therefore, when you get into a situation where tables have a circular reference, using DbUnit can be a real bitch. I ran into this situation yesterday.

Luckily, I was able to figure out a solution thanks to the help of Mark Matthews. Just add "sessionVariables=FOREIGN_KEY_CHECKS=0" to your JDBC URL. Here's how the "db-load" target in AppFuse looks with this in place:

    <target name="db-load" depends="prepare"
        description="Loads the database with sample data">
        <property name="operation" value="CLEAN_INSERT"/>
        <property name="file" value="metadata/sql/sample-data.xml"/>
        <dbunit driver="${database.driver_class}"
            supportBatchStatement="false" 
            url="${database.url}&amp;sessionVariables=FOREIGN_KEY_CHECKS=0"
            userid="${database.username}" password="${database.password}">
            <operation type="${operation}" src="${file}" format="xml" transaction="true"/>
        </dbunit>

    </target>

Does your preferred database have a similar mechanism for turning off foreign key checks using the connection URL? Posted in Java at Mar 01 2006, 04:16:48 PM MST 15 Comments

Comments:

Use DDLUtils instead of DBUnit directly. It has a lot of improvements and better ANT tasks.

Posted by Ahmed Mohombe on March 02, 2006 at 06:02 AM MST #

Last time I used dbUnit was a couple of years ago, against Oracle 9. Things may have changed since then. Our solution was a bit more convoluted than we wanted. On an Oracle jdbc connection, open a Statement and execute "SET CONSTRAINTS ALL DEFERRED", then constraint checking is delayed until the final commit. Close the Statement, keep the Connection and use it as the connection for the data load/drop. To prevent pasting that around, we wrote our own "DeferConstraintsCheckinOperation" that extends dbunit's DatabaseOperation, and wrapped our dbunit operations with our operation.

Posted by Paul Devine on March 02, 2006 at 02:57 PM MST #

You can do something similar to the Oracle thing in PostgreSQL, see here . You need to explicitly mark the FKC's as deferrable, I dunno if that's the way Oracle does it.

I just had a John McEnroe moment (ie "you cannot be serious") reading about the MySQL behaviour. From what I can see, the implication is that the checking is completely turned off, not just delayed until the end of the transaction. What happens if the data's bad? It will happily insert your bad data. This seems unspeakably evil to me, although it's generally in line with the MySQL way of doing things. Have I misread?

Posted by Tom Dunstan on March 06, 2006 at 01:27 AM MST #

I do not think transaction is supported in DBUnit 2.1. To use this you may need to upgrade to DBUnit-dev which is version 2.2 I guess.

Posted by Thomas Ramapuram on March 07, 2006 at 10:46 PM MST #

Thomas - you are correct about transaction="true", I'm using a nightly build of DbUnit.

Posted by Matt Raible on March 08, 2006 at 07:12 AM MST #

Paul - does "SET CONSTRAINTS ALL DEFERRED" on Oracle delay all constraints or only the deferrable ones?

Posted by Vahan Harput on March 12, 2006 at 12:47 PM MST #

I hadn't seen an example of passing query string params in the jdbc url. (Now I've got a research/learning topic for the afternoon.) The example above didn't initially work for me, since I didn't already have a ? in my url. (I bet your database.url property already has params, so it already had one.) So, here's what config ended up looking like:

       <dbunit driver="${database.driver_class}"
           supportBatchStatement="false"
           url="${database.url}?&sessionVariables=FOREIGN_KEY_CHECKS=0"
           userid="${database.username}" password="${database.password}">
           <operation type="${operation}" src="${file}" format="xml" transaction="true"/>
       </dbunit>

Posted by Trenton Lipscomb on April 03, 2006 at 12:05 PM MDT #

I'm trying to integrate DBUnit into some unit tests programmatically and I'm running into a similar issue. I do not want to turn off foreign keys, which is easy enough to deal w/ if your tables are always clean at the start of the test. But ideally, each test should not have to worry about the db state. For example if I have test XXX execute, inserting into tables A, B, and C. C contains a FK to A. Test YYY tries to insert a new row into C, but in the process of a clean_insert, cannot delete all rows b/c of the constraint. I feel like turning off this constraint checking is ok w/ strict data insertion, but if the same connection is used to commit objects during tests, this constraint should not be toggled off. Does anyone have a good solution for leaving FK constraints intact, but also utilizing DBUnit's ease of data insertion? I've considered rolling back every test's data, but thought there might be a more elegant solution.

Posted by Tom on May 24, 2006 at 10:53 AM MDT #

if you're not into using a nightly of DBUnit, I posted a blog entry with an alternative solution for MySQL etc.

http://blogs.sourceallies.com/roller/page/joedeveloper?entry=dbunit_ant_task_and_foreign

There's a bit of code, but it will compile against the latest release of DBUnit and allow you to execute a script as part of the ant task. In said script you can utilize the connection that is being used to disable the FKChecking. It was the only way I could get things to work as the above listed method gave me all sorts of fits.

The extension shown also allows other options for use when using DBUnit for pre and post scripting. more concise than a bunch of sql targets IMHO.

Posted by Brian Greene on July 13, 2006 at 09:47 AM MDT #

To Tom:

I also use DbUnit (2.1) programatically. What I've found works (and works pretty well for us) is subclassing DatabaseTestCase and using that for all our MySQL database test cases.

In my getConnection() method, I simply turn off foreign key checking using "SET FOREIGN_KEY_CHECKS=0". This lets DbUnit load my test data with no hiccups.

However, since a) that's a session variable, and b) the actual tests get their own connections directly from the datasource (I use Spring JdbcDaoSupport), then the actual DAO operations execute with foreign key constraint checks on - thus making my tests more robust and in line with the production environment.

Posted by Alistair Israel on September 05, 2006 at 11:20 PM MDT #

How to SET FOREIGN_KEY_CHECKS=0 in getConnection() method
Is there a way to include this in Java code rather then specify in the Ant task.
Is there an alternate solution for SQLServer2005?

Following is the code I'm using to load the data into DB from an XML dataset. How do I set the FOREIGN_KEY_CHECKS=0 in the code here ?? 

// Get the database connection
Connection jdbcConnection = dataSource.getConnection();

DatabaseConfig dbConfig = new DatabaseConfig();   
dbConfig.setFeature(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES,true);

IDatabaseConnection connection = new databaseConnection(jdbcConnection,schema);
    try
        {
            //initialize your dataset here
            IDataSet dataSet = new FlatXmlDataSet(new File(dataSetName));

            // This operation decorates an insert operation and disables the MS SQL Server automatic
            // identifier generation (IDENTITY) during its execution
            InsertIdentityOperation.CLEAN_INSERT.execute(connection, dataSet);
 
            return 1;
        }  
        catch (Exception ex)
        {
            ex.printStackTrace();
            throw new ServiceUnavailableException();
        }
        finally {
            if (connection != null) connection.close();
        }

Posted by sageer on November 07, 2006 at 07:43 PM MST #

Can someone post some example code showing exactly how to disable foreign key checking in the getConnection() method, as mentioned by Alistair? Will this work for HSQLDB, or is it specific to MySQL?

I have tried appending "&sessionVariables=FOREIGN_KEY_CHECKS=0" to my JDBC URL, with no effect, and Alistair's approach sounds better anyway.

Thanks in advance.

--James

Posted by James Adams on December 27, 2006 at 12:02 AM MST #

James, Use "?sessionVariables=FOREIGN_KEY_CHECKS=0" on your DriverManager.getConnection() call, i.e. ? in place of your ampersand. Robert

Posted by Robert Jewett on February 27, 2008 at 08:25 PM MST #

does anyone have an idea how to do this with maven2? i have an oracle database and can´t get that problem under control so far.

Posted by niko on March 03, 2008 at 06:48 AM MST #

This worked for me. I added this to the dbunit-maven-plugin in pom.xml <url>${jdbc.url}&amp;sessionVariables=FOREIGN_KEY_CHECKS=0</url>

Posted by Paddy Daly on May 19, 2008 at 10:27 AM MDT #

Post a Comment:
  • HTML Syntax: Allowed