View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
1651 | RackTables | default | public | 2016-06-30 23:03 | 2016-08-01 17:14 |
Reporter | mhilgers | Assigned To | infrastation | ||
Priority | normal | Severity | major | Reproducibility | always |
Status | closed | Resolution | fixed | ||
OS | CentOS | OS Version | 6.7 | ||
Summary | 1651: attempting to upgrade DB from 0.19.14 to 0.20.0 | ||||
Description | I am in the process of upgraded our Racktables DB and app to the current version. I have been somewhat successful thus far via this ticket: https://bugs.racktables.org/view.php?id=1649 However I am now upgrading from 0.19.14 to 0.20.0 and I have received the below query errors. INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (27,'uint','Height, units') -- Duplicate entry '27' for key 'PRIMARY' INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (29,'uint','Sort order') -- Duplicate entry '29' for key 'PRIMARY' In the pasted ticket link, we altered local data re-ran the upgrade with success. However in this case I don't know which piece of data to amend. Can you guide me? Thanks again! | ||||
Steps To Reproduce | Install version 0.20.0 create DB dump in 0.19.14 DB run upgrade see errors. | ||||
Tags | No tags attached. | ||||
mysql> SELECT * FROM Attribute WHERE id < 10000; | |
mysql> SELECT * FROM Attribute WHERE id < 10000; +------+--------+-----------------------------+ | id | type | name | +------+--------+-----------------------------+ | 1 | string | OEM S/N 1 | | 2 | dict | HW type | | 3 | string | FQDN | | 4 | dict | SW type | | 5 | string | SW version | | 6 | uint | number of ports | | 7 | float | max. current, Ampers | | 8 | float | power load, percents | | 13 | float | max power, Watts | | 14 | string | contact person | | 16 | uint | flash memory, MB | | 17 | uint | RAM, GB | | 18 | uint | CPU, MHz | | 20 | string | OEM S/N 2 | | 21 | date | support contract expiration | | 22 | date | HW warranty expiration | | 24 | date | SW warranty expiration | | 25 | string | UUID | | 26 | uint | disk size, GB | | 27 | uint | CPU count (sockets) | | 28 | uint | vCPU count | | 29 | uint | disk count | | 30 | uint | disk speed, RPM | | 31 | dict | RAID Level(s) | | 32 | uint | disk2 size, GB | | 33 | uint | disk3 size, GB | | 34 | uint | FusionIO card size, GB | | 36 | uint | CPU cores per socket | | 9999 | string | base MAC address | +------+--------+-----------------------------+ 29 rows in set (0.00 sec) |
|
The following queries should fix this problem for this and subsequent upgrades: SET FOREIGN_KEY_CHECKS=0; UPDATE Attribute SET id = id + 10000 WHERE id IN(30, 31, 32, 33, 34, 36); UPDATE AttributeMap SET attr_id = attr_id + 10000 WHERE attr_id IN(30, 31, 32, 33, 34, 36); UPDATE AttributeValue SET attr_id = attr_id + 10000 WHERE attr_id IN(30, 31, 32, 33, 34, 36); SET FOREIGN_KEY_CHECKS=1; Please try and let me know if any errors remain. |
|
I ran the above queries on the 0.19.14 DB and then ran the upgrade to 0.20.0 but the errors remain.. The following queries failed: INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (27,'uint','Height, units') -- Duplicate entry '27' for key 'PRIMARY' INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (29,'uint','Sort order') -- Duplicate entry '29' for key 'PRIMARY' |
|
My bad, looks like it does only half of the job. Let me review once again. | |
Please find the correct query below (can be run after or instead of the previous). SET FOREIGN_KEY_CHECKS=0; UPDATE Attribute SET id = id + 10000 WHERE id IN(26, 27, 28, 29, 30, 31, 32, 33, 34, 36); UPDATE AttributeMap SET attr_id = attr_id + 10000 WHERE attr_id IN(26, 27, 28, 29, 30, 31, 32, 33, 34, 36); UPDATE AttributeValue SET attr_id = attr_id + 10000 WHERE attr_id IN(26, 27, 28, 29, 30, 31, 32, 33, 34, 36); SET FOREIGN_KEY_CHECKS=1; |
|
I reset my upgrade test system, installed the 0.20.0 version, dumped in the 0.19.14 DB, ran the above query on it, proceeded with the upgrade and have an output that looks like this Upgrade status Current status Data version: 0.19.14 Code version: 0.20.0 Upgrade path 0.19.14 ? 0.20.0 Executing batch '0.20.0' done Executing batch 'dictionary' done Summary Upgrade complete, it is Ok to enter the system. However, after I enter the system and click the IPv4 Space link, I see this error in the webpage: Missing record Object 'object'#'1444' does not exist This is the same error I saw initially when I ran the upgrade from 0.19.12 to 0.19.13 This is the first time I've seen an Good output as the upgrade result but then could not see the IPv4 Space Data. |
|
Could you upgrade this temporary copy to 0.20.11 and post the result here? Some issues in old versions had been addressed by subsequent releases. | |
Shall I go straight to 0.20.11 or upgrade to 0.20.10 *then* to 0.20.11 ? | |
Straight to 0.20.11, it will handle the intermediate steps by itself. | |
I ran the upgrade to 0.20.11 and have good upgrade results but I still get this error when clicking on the IPv4 Space link: Object 'object'#'1444' does not exist |
|
Are there any error messages in "Data integrity" under Reports under Main page? | |
Yes. I should have looked here sooner. ====== TagStorage: Missing Parents (1) Tag Parent Type Parent ID Network invalid 0 Missing Foreign Keys (1) Table Key AttributeValue AttributeValue-FK-map |
|
ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`); SELECT TS.*, TT.tag FROM TagStorage TS LEFT JOIN TagTree TT ON TS.tag_id = TT.id LEFT JOIN File ON TS.entity_id = File.id WHERE TS.entity_realm = 'file' AND File.id IS NULL; SELECT TS.*, TT.tag FROM TagStorage TS LEFT JOIN TagTree TT ON TS.tag_id = TT.id LEFT JOIN IPv4Network ON TS.entity_id = IPv4Network.id WHERE TS.entity_realm = 'ipv4net' AND IPv4Network.id IS NULL; SELECT TS.*, TT.tag FROM TagStorage TS LEFT JOIN TagTree TT ON TS.tag_id = TT.id LEFT JOIN IPv4RSPool ON TS.entity_id = IPv4RSPool.id WHERE TS.entity_realm = 'ipv4rspool' AND IPv4RSPool.id IS NULL; SELECT TS.*, TT.tag FROM TagStorage TS LEFT JOIN TagTree TT ON TS.tag_id = TT.id LEFT JOIN IPv4VS ON TS.entity_id = IPv4VS.id WHERE TS.entity_realm = 'ipv4vs' AND IPv4VS.id IS NULL; SELECT TS.*, TT.tag FROM TagStorage TS LEFT JOIN TagTree TT ON TS.tag_id = TT.id LEFT JOIN IPv6Network ON TS.entity_id = IPv6Network.id WHERE TS.entity_realm = 'ipv6net' AND IPv6Network.id IS NULL; SELECT TS.*, TT.tag FROM TagStorage TS LEFT JOIN TagTree TT ON TS.tag_id = TT.id LEFT JOIN VS ON TS.entity_id = VS.id WHERE TS.entity_realm = 'ipvs' AND VS.id IS NULL; SELECT TS.*, TT.tag FROM TagStorage TS LEFT JOIN TagTree TT ON TS.tag_id = TT.id LEFT JOIN Location ON TS.entity_id = Location.id WHERE TS.entity_realm = 'location' AND Location.id IS NULL; SELECT TS.*, TT.tag FROM TagStorage TS LEFT JOIN TagTree TT ON TS.tag_id = TT.id LEFT JOIN RackObject ON TS.entity_id = RackObject.id WHERE TS.entity_realm = 'object' AND RackObject.id IS NULL; SELECT TS.*, TT.tag FROM TagStorage TS LEFT JOIN TagTree TT ON TS.tag_id = TT.id LEFT JOIN Rack ON TS.entity_id = Rack.id WHERE TS.entity_realm = 'rack' AND Rack.id IS NULL; SELECT TS.*, TT.tag FROM TagStorage TS LEFT JOIN TagTree TT ON TS.tag_id = TT.id LEFT JOIN UserAccount ON TS.entity_id = UserAccount.user_id WHERE TS.entity_realm = 'user' AND UserAccount.user_id IS NULL; SELECT TS.*, TT.tag FROM TagStorage TS LEFT JOIN TagTree TT ON TS.tag_id = TT.id LEFT JOIN VLANSwitchTemplate ON TS.entity_id = VLANSwitchTemplate.id WHERE TS.entity_realm = 'vst' AND VLANSwitchTemplate.id IS NULL; |
|
mysql> ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY -> (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`rtables`.<result 2 when explaining filename '#sql-1bee_4e8'>, CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`)) |
|
all SELECT queries produced empty sets | |
SELECT AV.object_id, AV.attr_id, RO.objtype_id FROM AttributeValue AS AV INNER JOIN RackObject AS RO ON AV.object_id = RO.id LEFT JOIN AttributeMap AS AM ON AV.attr_id = AM.attr_id AND RO.objtype_id = AM.objtype_id WHERE AM.attr_id IS NULL; |
|
mysql> SELECT AV.object_id, AV.attr_id, RO.objtype_id FROM -> AttributeValue AS AV INNER JOIN RackObject AS RO ON AV.object_id = RO.id -> LEFT JOIN AttributeMap AS AM ON AV.attr_id = AM.attr_id AND RO.objtype_id = AM.objtype_id -> WHERE AM.attr_id IS NULL; +-----------+---------+------------+ | object_id | attr_id | objtype_id | +-----------+---------+------------+ | 86 | 2 | 11 | +-----------+---------+------------+ 1 row in set (0.05 sec) |
|
DELETE FROM AttributeValue WHERE object_id = 86 AND attr_id = 2; ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`); SHOW CREATE TABLE AttributeValue; SELECT * FROM TagStorage WHERE entity_id = 0; |
|
mysql> DELETE FROM AttributeValue WHERE object_id = 86 AND attr_id = 2; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 48 Current database: rtables Query OK, 1 row affected (0.07 sec) mysql> mysql> ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`); Query OK, 14919 rows affected (0.57 sec) Records: 14919 Duplicates: 0 Warnings: 0 mysql> mysql> SHOW CREATE TABLE AttributeValue; +----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | AttributeValue | CREATE TABLE `AttributeValue` ( `object_id` int(10) unsigned NOT NULL, `object_tid` int(10) unsigned NOT NULL DEFAULT '0', `attr_id` int(10) unsigned NOT NULL, `string_value` char(255) COLLATE utf8_unicode_ci DEFAULT NULL, `uint_value` int(10) unsigned DEFAULT NULL, `float_value` float DEFAULT NULL, PRIMARY KEY (`object_id`,`attr_id`), KEY `attr_id-uint_value` (`attr_id`,`uint_value`), KEY `attr_id-string_value` (`attr_id`,`string_value`(12)), KEY `id-tid` (`object_id`,`object_tid`), KEY `object_tid-attr_id` (`object_tid`,`attr_id`), CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`), CONSTRAINT `AttributeValue-FK-object` FOREIGN KEY (`object_id`, `object_tid`) REFERENCES `Object` (`id`, `objtype_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT * FROM TagStorage WHERE entity_id = 0; +--------------+-----------+--------+-------------------+------+------+ | entity_realm | entity_id | tag_id | tag_is_assignable | user | date | +--------------+-----------+--------+-------------------+------+------+ | file | 0 | 110 | yes | NULL | NULL | +--------------+-----------+--------+-------------------+------+------+ 1 row in set (0.00 sec) mysql> |
|
I still this when I click the IPv4 Space link: Object 'object'#'1444' does not exist And this on the Data Integrity page: TagStorage: Missing Parents (1) Tag Parent Type Parent ID Network invalid 0 |
|
I will be on vacation until July 11. I will pick this back up on July 12. | |
It turns out that "invalid" string was a bug of its own, the next release will not have it. The following query will clear the integrity report: DELETE FROM TagStorage WHERE entity_id = 0; I will need time to understand where the "object does not exist" message comes from so enjoy your vacation! |
|
Has there been any progress on this while I was out? Thanks! |
|
"Object 'object'#'1444' does not exist" -- is this a message bar at the page top or a standalone page? | |
Object 'object'#'1444' does not exist message bar at page top. Not a stand alone page. |
|
When that message appears you land on the main page instead of the page you have clicked, right? | |
yes. the main page, with all the links like IPv4 Space and Reports, with message bar on top giving the object error | |
Could you clean the integrity report as described earlier, apply the following temporary change to the source code, trigger the error and paste the diagnostics output here? --- a/wwwroot/inc/exceptions.php +++ b/wwwroot/inc/exceptions.php @@ -415,7 +415,9 @@ function printGenericException($e) function printException($e) { - if ($e instanceof RackTablesError) + if ($e instanceof EntityNotFoundException) + printGenericException ($e); + elseif ($e instanceof RackTablesError) $e->dispatch(); elseif ($e instanceof PDOException) printPDOException($e); |
|
Instead of applying the change above feel free to set the following in secret.php: $debug_mode = TRUE; |
|
I've cleaned the integrity report and added $debug_mode = TRUE; to secret.php. The error output is a bit large: (moved to attachment -- D.O.) |
|
I am setting this issue to private to avoid disclosing your internal information. | |
OK, now the issue is readable again. Back to the original problem, let me have some time to understand how it breaks. | |
Hello, I just wanted to check in and see if there has been any progress on this? Thanks! |
|
I was busy with other work recently, will update as soon as I have new results. | |
Hello, just a reminder that we are hoping this is making some progress | |
Thank you for waiting. I have spent some time studying the backtrace and found that there seems to be an IPv4 address allocation to an object that does not exist, though it is not clear how exactly it can happen as a foreign key would not allow such a row in the table. Could you post the output of the following? SELECT ip, object_id, type FROM IPv4Allocation WHERE object_id = 1444 OR ip = 1208056161; SELECT objtype_id FROM Object WHERE id = 1444; |
|
Here is the requested output. Thanks for keeping on this! mysql> SELECT ip, object_id, type FROM IPv4Allocation WHERE object_id = 1444 OR ip = -> 1208056161; +------------+-----------+--------+ | ip | object_id | type | +------------+-----------+--------+ | 1208056161 | 1444 | router | +------------+-----------+--------+ 1 row in set (0.01 sec) mysql> SELECT objtype_id FROM Object WHERE id = 1444; +------------+ | objtype_id | +------------+ | 1562 | +------------+ 1 row in set (0.00 sec) |
|
This makes it more clear as 1562 stands for a location, which is stored as an object at the database level but does not make a valid object at the PHP code level. RackTables releases before 0.20.9 had an unintended "feature" of allowing to change an object into a location (see bug 1339). The query below should make the router a normal object again: UPDATE Object SET objtype_id = (SELECT objtype_id FROM ObjectHistory WHERE id = 1444 AND objtype_id NOT IN (1560, 1561, 1562) ORDER BY ctime DESC LIMIT 1) WHERE id = 1444; This should resolve this exception completely. |
|
This query worked. The 1444 error has disappeared and we are running a test instance at version 0.20.11 with no errors! Thanks so much for seeing this difficult issue through! This ticket can be closed. |
|
Cheers. This issue does not have your debug data anymore and is now public again for other people to consider. Closing. | |
Date Modified | Username | Field | Change |
---|---|---|---|
2016-06-30 23:03 | mhilgers | New Issue | |
2016-06-30 23:41 | infrastation | Note Added: 0003295 | |
2016-07-01 00:36 | mhilgers | Note Added: 0003297 | |
2016-07-01 17:53 | infrastation | Note Added: 0003299 | |
2016-07-01 17:53 | infrastation | Assigned To | => infrastation |
2016-07-01 17:53 | infrastation | Status | new => assigned |
2016-07-01 19:31 | mhilgers | Note Added: 0003301 | |
2016-07-02 02:32 | infrastation | Note Added: 0003303 | |
2016-07-04 14:27 | infrastation | Note Added: 0003311 | |
2016-07-05 17:52 | mhilgers | Note Added: 0003317 | |
2016-07-05 18:28 | infrastation | Note Added: 0003319 | |
2016-07-05 18:30 | mhilgers | Note Added: 0003321 | |
2016-07-05 18:49 | infrastation | Note Added: 0003323 | |
2016-07-05 19:10 | mhilgers | Note Added: 0003325 | |
2016-07-06 00:08 | infrastation | Note Added: 0003327 | |
2016-07-06 00:51 | mhilgers | Note Added: 0003329 | |
2016-07-06 14:04 | infrastation | Note Added: 0003331 | |
2016-07-06 23:29 | mhilgers | Note Added: 0003333 | |
2016-07-06 23:38 | mhilgers | Note Added: 0003335 | |
2016-07-07 00:05 | infrastation | Note Added: 0003337 | |
2016-07-07 05:23 | mhilgers | Note Added: 0003339 | |
2016-07-07 08:24 | infrastation | Note Added: 0003341 | |
2016-07-07 15:34 | infrastation | Status | assigned => feedback |
2016-07-07 16:39 | mhilgers | Note Added: 0003345 | |
2016-07-07 16:39 | mhilgers | Status | feedback => assigned |
2016-07-07 16:43 | mhilgers | Note Added: 0003347 | |
2016-07-07 16:43 | mhilgers | Note Added: 0003349 | |
2016-07-07 17:16 | infrastation | Note Added: 0003351 | |
2016-07-12 19:45 | mhilgers | Note Added: 0003355 | |
2016-07-12 23:27 | infrastation | Note Added: 0003357 | |
2016-07-13 02:16 | mhilgers | Note Added: 0003359 | |
2016-07-13 13:23 | infrastation | Note Added: 0003365 | |
2016-07-13 23:02 | mhilgers | Note Added: 0003367 | |
2016-07-14 11:24 | infrastation | Note Added: 0003371 | |
2016-07-14 11:25 | infrastation | Summary | attmepting to upgrade DB from 0.19.14 to 0.20.0 => attempting to upgrade DB from 0.19.14 to 0.20.0 |
2016-07-15 12:16 | infrastation | Note Added: 0003375 | |
2016-07-18 18:43 | mhilgers | Note Added: 0003381 | |
2016-07-18 19:35 | infrastation | Note Edited: 0003381 | |
2016-07-18 19:36 | infrastation | File Added: racktables_bug_1651_debug_output.txt | |
2016-07-18 19:38 | infrastation | Note Added: 0003383 | |
2016-07-18 19:38 | infrastation | View Status | public => private |
2016-07-18 19:43 | infrastation | Note Added: 0003385 | |
2016-07-23 00:05 | mhilgers | Note Added: 0003389 | |
2016-07-23 07:44 | infrastation | Note Added: 0003391 | |
2016-07-28 21:02 | mhilgers | Note Added: 0003397 | |
2016-07-29 23:43 | infrastation | Note Added: 0003407 | |
2016-07-30 00:09 | mhilgers | Note Added: 0003409 | |
2016-07-30 16:03 | infrastation | Note Added: 0003411 | |
2016-08-01 16:51 | mhilgers | Note Added: 0003415 | |
2016-08-01 17:12 | infrastation | File Deleted: racktables_bug_1651_debug_output.txt | |
2016-08-01 17:14 | infrastation | Note Added: 0003417 | |
2016-08-01 17:14 | infrastation | Status | assigned => closed |
2016-08-01 17:14 | infrastation | Resolution | open => fixed |
2016-08-01 17:14 | infrastation | View Status | private => public |