[racktables-users] Re: query failed on upgrade to 0.17.6

On Mon, 2 Nov 2009 16:48:46 +0100 Frank Altpeter wrote:

> Hi there,
> 
> on 2009-11-02 at 16:33:54 CET, To racktables-users wrote:
> > And, another question about the new "show only ports near the object"
> > feature... I have a rack, where two AC power units are mounted, each with
> > one free port. When I try to link the unlinked AC power port of a machine,
> > I only get the free port of the first device, but the one free port of the
> > second device doesn't show up here. Am I missing something or is there a
> > minor bug in the backend logic?
> 
> This one just cleared itself. There was a dead entry in the Link database,
> where porta was the port_id of the missing port, which was displayed as
> free port, because the port_id for portb didn't exist anymore.
> 
> This brings up the question... how do I check if there are more such
> entries in the Link database?

SELECT * FROM Link WHERE porta NOT IN (SELECT id FROM Port) OR portb
NOT IN (SELECT id FROM Port);


> 
> 
> 
> I mean this:
> 
> mysql> select * from Link where porta = 640;
> +-------+-------+
> | porta | portb |
> +-------+-------+
> |   640 |   681 | 
> +-------+-------+
> 1 row in set (0.00 sec)
> 
> 
> mysql> select * from Port where id in ( 681, 640 );
> +-----+-----------+------+--------+------+-----------+---------------------+-------+
> | id  | object_id | name | iif_id | type | l2address | reservation_comment | 
> label |
> +-----+-----------+------+--------+------+-----------+---------------------+-------+
> | 640 |       150 | e 6  |      1 | 1322 | NULL      | NULL                | 
> 6     | 
> +-----+-----------+------+--------+------+-----------+---------------------+-------+
> 1 row in set (0.00 sec)
> 
> 
> Looks for my like there was once an object with a linked port, which has
> been deleted but without cascaded deleting the Link entry ...

The matter is, InnoDB should make this impossible:

mysql> show create table Link;
[...]
  CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`),
  CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`)

Could you check, that the table is InnoDB and has these constraints?

-- 
    Denis Ovsienko

Other related posts: