Open-AudIT

I'm receiving the following error when trying to import the MySQL database:

[code][quote]$ mysql -u root -p OAv2 < OAv2_mysql.sql
Enter password:
ERROR 1005 (HY000) at line 250: Can't create table 'OAv2.oa_device_col' (errno: 150)[/quote][/code]

Relevant lines of the file:

[code] 247 -- table structure for table `oa_device_columns`
248 --
249 DROP TABLE IF EXISTS `oa_device_col`;
250 CREATE TABLE `oa_device_col` (
251 `col_id` int(10) unsigned NOT NULL auto_increment,
252 `dev_id` int(10) unsigned default NULL,
253 `col_table` varchar(100) NOT NULL default '',
254 `col_column` varchar(100) NOT NULL default '',
255 `col_type` varchar(100) NOT NULL default '',
256 `col_order` int(10) unsigned default NULL,
257 PRIMARY KEY (`col_id`),
258 KEY `dev_id` (`dev_id`),
259 CONSTRAINT `oa_dev_col_dev_id` FOREIGN KEY (`dev_id`) REFERENCES `oa_device` (`dev_id`) ON DELETE CASCADE
260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;[/code]


I picked up the latest code using:

[code]$ bzr branch lp:oav2
$ bzr revno
36[/code]


... and have followed the installation instructions almost precisely - with two exceptions:

1 - I am using the path in all lower case, so "/usr/oav2" instead of the documented "/usr/OAv2" (although that doesn't seem relevant to the issue)
2 - I did add privileges for an oav2 user in MySQL (again, doesn't seem relevant to the error though):

[code]mysql> CREATE DATABASE OAv2;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE USER 'oav2'@'localhost' IDENTIFIED BY '********';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON oav2.* TO 'oav2'@'localhost';
Query OK, 0 rows affected (0.00 sec)[/code]


Further information about the test box:

[code]$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 12.10
Release: 12.10
Codename: quantal

$ dpkg -l | grep mysql
ii libdbd-mysql-perl 4.021-1 amd64 Perl5 database interface to the MySQL database
ii libmysqlclient18:amd64 5.5.28-0ubuntu0.12.10.1 amd64 MySQL database client library
ii mysql-client-5.5 5.5.28-0ubuntu0.12.10.1 amd64 MySQL database client binaries
ii mysql-client-core-5.5 5.5.28-0ubuntu0.12.10.1 amd64 MySQL database core client binaries
ii mysql-common 5.5.28-0ubuntu0.12.10.1 all MySQL database common files, e.g. /etc/mysql/my.cnf
ii mysql-server 5.5.28-0ubuntu0.12.10.1 all MySQL database server (metapackage depending on the latest version)
ii mysql-server-5.5 5.5.28-0ubuntu0.12.10.1 amd64 MySQL database server binaries and system database setup
ii mysql-server-core-5.5 5.5.28-0ubuntu0.12.10.1 amd64 MySQL database server binaries
ii php5-mysql 5.4.6-1ubuntu1.1 amd64 MySQL module for php5[/code]


I searched online for the error and got into some heavy MySQL documentation about primary keys and what-not. I'm not a DBA so don't want to go breaking this database.


Any help would be appreciated. If any further information is needed, I am happy to provide.

I don't know in what state Mark keeps the public source repository. It might be better to use one of his "release" dumps.

Having said that it looks like the failure is because of data type mismatches in the foreign key.

[code]
CREATE TABLE `oa_device` (
`dev_id` int(10) NOT NULL auto_increment,
[/code]

[code]
CREATE TABLE `oa_device_col` (
`dev_id` int(10) unsigned default NULL,
[/code]

Should be something like:

[code]
CREATE TABLE `oa_device_col` (
`dev_id` int(10) NOT NULL default 0,[/code]

I've left the non-relevant stuff out above. I'm not a dba either so your mileage may vary.

Yep - JPA looks right.
I've been uploading quite a ptahces of late - including converting to using CodeIgniter 2.1.3.
I'd hold off using the Bazaar repo at the moment as I haven't finished testing it yet.
Give me a few days and it should be in a buildable state again

_________________
Support and Development hours available from [url=https://opmantek.com]Opmantek[/url].
Please consider a purchase to help make Open-AudIT better for everyone.