[GeoNetwork-devel] Unable to set up GeoNetwork+MySQL (InnoDB) on Windows XP

[Angley, W. [USA] <ang...@ba...>]

Hi GeoNetwork team,

On Windows XP SP3, I installed MySQL 5.5.9 Community Edition and GeoNetwork 2.6.3.  In MySQL, I created a GeoNetwork user and a corresponding database (with the InnoDB storage manager), and granted the user all the necessary permissions.  I then updated the web/geonetwork/WEB-INF/config.xml  file to connect to the MySQL instance, and to not connect to the McKoi instance.

When I started the GeoNetwork server for the first time, it threw an exception as it was preparing the database for first use:

2011-02-28 15:39:11,188 ERROR [jeeves.engine] - Raised exception while starting appl handler. Skipped.
2011-02-28 15:39:11,204 ERROR [jeeves.engine] -    Handler   : org.fao.geonet.Geonetwork
2011-02-28 15:39:11,204 ERROR [jeeves.engine] -    Exception : com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Cannot truncate a table referenced in a foreign key constraint (`geonetwork`.`metadatacateg`, CONSTRAINT `metadatacateg_ibfk_1` FOREIGN KEY (`metadataId`) REFERENCES `geonetwork`.`metadata` (`id`))

I did some work to investigate this on my own.  I tried reproducing it on other databases and found that the issue seems to only occur with MySQL; it did not occur under McKoi, Oracle or PostgreSQL.  I also checked the GeoNetwork Trac and could not find a ticket that matched this issue.

Doing a Google search on the error message led to a ticket on the MySQL issue tracker: http://bugs.mysql.com/bug.php?id=58788.  The ticket indicates that the behavior of TRUNCATE TABLE under InnoDB changed in v5.5.7 of MySQL, and that some scripts that used to work no longer will.  If this behavior change is the root cause of the issue I'm having, the MySQL migration scripts that come with GeoNetwork will probably need to be updated.

I have the geonetwork.log file that contains the error excerpted above, and can provide it upon request.  If you need anything else from me, or if I should report this elsewhere (perhaps on the GeoNetwork Trac?), please let me know.

Thanks,
-- Will

William Angley III
Sr. Consultant
Booz Allen Hamilton
575 Herndon Parkway
Herndon, VA 20170
Tel. (703) 377-9104
ang...@ba...<mailto:ang...@ba...>