database update problem v1.10-to-1.11

[Stefan]

neighbor table cannot be created:

ERROR 1005 (HY000): Can't create table 'netdb.neighbor' (errno: 150)

mysql> describe switchstatus;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| switch | varchar(100) | NO | PRI | NULL | |
| port | varchar(30) | NO | PRI | NULL | |
| vlan | varchar(15) | YES | MUL | NULL | |
| status | varchar(20) | NO | | NULL | |
| speed | varchar(30) | YES | | NULL | |
| duplex | varchar(20) | YES | | NULL | |
| description | varchar(150) | YES | | NULL | |
| lastseen | datetime | NO | | NULL | |
| lastup | datetime | YES | | NULL | |
| p_minutes | int(11) | YES | | NULL | |
| p_uptime | varchar(55) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> CREATE TABLE neighbor (
-> switch varchar(100) NOT NULL,
-> port varchar(30) NOT NULL,
-> n_ip VARCHAR(32),
-> n_host VARCHAR(100),
-> n_desc VARCHAR(2000),
-> n_model VARCHAR(100),
-> n_port VARCHAR(30),
-> n_protocol varchar(10),
-> n_lastseen datetime NOT NULL,
-> CONSTRAINT FOREIGN KEY ( switch, port ) REFERENCES switchstatus ( switch, port ) ON DELETE CASCADE ON UPDATE CASCADE,
-> PRIMARY KEY ( switch, port )
-> ) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'netdb.neighbor' (errno: 150)

Server version: 5.1.57-community MySQL

MySQL error code 150: Foreign key constraint is incorrectly formed

Is there any Problem with foreign keys or did I miss something in eaelier updates?

Any ideas?

Many thanks in advance!

Regards,

Stefan

[Stefan]

I could solve the issue by dropping and re-creating the switchstatus table.

Regards,

Stefan

[Jonathan Yantis]

Stefan,

What was the first version of NetDB you installed? Is it possible you were running on an older database version in the past and never upgraded to v1.9 of the database? Older version of the code won't complain about that issue. I have a feeling we could be running in to an issue like that, since everyone else has been able to upgrade versions so far. In the newer versions, I have added the version to the database itself to help with these issues, but that was done in later versions than you are on.

Whatever you do, backup the database with the netdbctl . It's possible you could drop the switchstatus and recreate it, since that data is recreated all the time and no data loss could happen. I would make sure you ran the v1.9 to v1.10 upgrade script though. That's why it's important to backup the database as is, and then perhaps try to run the older upgrade script.

Let me know how that goes, I don't you'll have a problem running the old script. Back it up in case though.

Jonathan