View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
1833RackTablesdefaultpublic2018-07-20 09:122018-07-23 12:30
Reporterigloo Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionnot fixable 
Product Version0.20.14 
Summary1833: Upgrade.php doesn't create FOREIGN KEY for CachedPNV
Descriptionupgrade.php doesnt create FOREIGN KEY for CachedPNV while upgrade from 0.19.11 to 0.20.14
Steps To ReproduceUpgrade from 0.19.11 to 0.20.14

MariaDB [racktables_db]> select * from information_schema.table_constraints where TABLE_SCHEMA='racktables_db' and TABLE_NAME='CachedPNV' \G;
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: racktables_db
   CONSTRAINT_NAME: PRIMARY
      TABLE_SCHEMA: racktables_db
        TABLE_NAME: CachedPNV
   CONSTRAINT_TYPE: PRIMARY KEY
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: racktables_db
   CONSTRAINT_NAME: port_id
      TABLE_SCHEMA: racktables_db
        TABLE_NAME: CachedPNV
   CONSTRAINT_TYPE: UNIQUE
2 rows in set (0.00 sec)



~/racktables/src/RackTables-0.20.14/wwwroot/inc]# grep CachedPNV upgrade.php
            $query[] = "ALTER TABLE `CachedPNV` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
Additional Information0.20.0
~/racktables/src/racktables-RackTables-0.20.0/wwwroot/inc]# grep CachedPNV upgrade.php
CREATE TABLE `CachedPNV` (
  CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE

Full query:
$query[] = "
CREATE TABLE `CachedPNV` (
  `object_id` int(10) unsigned NOT NULL,
  `port_name` char(255) NOT NULL,
  `vlan_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
  UNIQUE KEY `port_id` (`object_id`,`port_name`),
  CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON
) ENGINE=InnoDB
";


0.20.14
~/racktables/src/RackTables-0.20.14/wwwroot/inc]# grep CachedPNV upgrade.php
            $query[] = "ALTER TABLE `CachedPNV` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";

0.21.1
~/racktables/src/RackTables-0.21.1/wwwroot/inc]# grep CachedPNV upgrade.php
            $query[] = "ALTER TABLE `CachedPNV` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci"


TagsNo tags attached.

Relationships

has duplicate 1835 closed Upgrade.php doesn't create FOREIGN KEY for CachedPNV 

Activities

infrastation

infrastation

2018-07-20 09:38

administrator   ~0003805

Last edited: 2018-07-20 09:39

 CachedPNV-FK-compound was never added to CachedPNV, the table was originally created with the FK (RackTables 0.18.0, May 2010). In older RackTables versions that supported upgrading from even older RackTables versions the upgrader created CachedPNV with the FK too. Those statements are based on the git history.

This way, what you see in the source code is correct. What you see in the live database structure (no FK in the table) is incorrect, but it is unlikely caused by RackTables installer or upgrader (you are welcome to prove the opposite). There may be other FKs missing from this database, the integrity report (first appeared in RackTables 0.20.7) will flag those for you.
igloo

igloo

2018-07-20 10:21

reporter   ~0003807

Ok, i got it.
Seems like the first RT setup was done with Mysql ENGINE=MyISAM. I don't know how it happened, i see the isInnoDBSupported() exist in 0.19.11...

Thank you!
infrastation

infrastation

2018-07-23 12:30

administrator   ~0003809

isInnoDBSupported() is called from the installer and the upgrader, but not from the normal interface code. What probably happened is this RackTables was installed on an InnoDB server and then the SQL dump was restored on a MyISAM server, or a similar engine switch after the installation.

Issue History

Date Modified Username Field Change
2018-07-20 09:12 igloo New Issue
2018-07-20 09:38 infrastation Note Added: 0003805
2018-07-20 09:39 infrastation Note Edited: 0003805
2018-07-20 09:39 infrastation Note Edited: 0003805
2018-07-20 09:40 infrastation Relationship added has duplicate 1835
2018-07-20 10:21 igloo Note Added: 0003807
2018-07-20 10:22 igloo Status new => closed
2018-07-20 10:22 igloo Resolution open => not fixable
2018-07-23 12:30 infrastation Note Added: 0003809