 |
Do you mean 0.19.10, which was released in October 2011? |
 |
nope... this specifically happens when i try to upgrade to any version 0.9.11 or greater. this stage always fails with same message. |
|
|
basically, I inherited an old install of Racktables that was left at version 0.19.9. I had no trouble getting it to 0.19.10... but as soon as I hit 0.19.11, sh!t hits the fan. |
|
|
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; |
|
|
Hey thanks for your help, here is what the database returned for that query: 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 | +-----------+---------+------------+ | 42 | 2 | 1506 | | 42 | 3 | 1506 | | 42 | 4 | 1506 | | 42 | 26 | 1506 | | 43 | 2 | 1506 | | 43 | 3 | 1506 | | 43 | 4 | 1506 | | 43 | 26 | 1506 | | 44 | 2 | 1506 | | 44 | 3 | 1506 | | 44 | 4 | 1506 | | 44 | 26 | 1506 | | 45 | 2 | 1506 | | 45 | 3 | 1506 | | 45 | 4 | 1506 | | 45 | 26 | 1506 | | 46 | 2 | 1506 | | 46 | 3 | 1506 | | 46 | 4 | 1506 | | 46 | 26 | 1506 | | 47 | 2 | 1506 | | 47 | 3 | 1506 | | 47 | 4 | 1506 | | 47 | 26 | 1506 | | 48 | 2 | 1506 | | 48 | 3 | 1506 | | 48 | 4 | 1506 | | 48 | 26 | 1506 | | 49 | 2 | 1506 | | 49 | 3 | 1506 | | 49 | 4 | 1506 | | 49 | 26 | 1506 | | 284 | 3 | 1506 | +-----------+---------+------------+ 33 rows in set (0.02 sec) Not sure exactly what I can do with it, but will await your reply. |
|
|
Thank you. 1506 stands for "VM Resource Pool", which is not associated with the attributes below by default:+----+--------+------------+ | id | type | name | +----+--------+------------+ | 2 | dict | HW type | | 3 | string | FQDN | | 4 | dict | SW type | | 26 | dict | Hypervisor | +----+--------+------------+ Please run the following queries: INSERT INTO AttributeMap (objtype_id, attr_id) VALUES (1506, 3); SELECT AV.object_id, AV.attr_id, RO.objtype_id, D.chapter_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 LEFT JOIN Dictionary as D ON AV.uint_value = D.dict_key WHERE AM.attr_id IS NULL; |
|
|
ok here is what I have now: mysql> INSERT INTO AttributeMap (objtype_id, attr_id) VALUES (1506, 3); Query OK, 1 row affected (0.01 sec) mysql> SELECT AV.object_id, AV.attr_id, RO.objtype_id, D.chapter_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 -> LEFT JOIN Dictionary as D ON AV.uint_value = D.dict_key -> WHERE AM.attr_id IS NULL; +-----------+---------+------------+------------+ | object_id | attr_id | objtype_id | chapter_id | +-----------+---------+------------+------------+ | 42 | 2 | 1506 | 11 | | 42 | 26 | 1506 | 29 | | 42 | 4 | 1506 | 13 | | 43 | 2 | 1506 | 11 | | 43 | 26 | 1506 | 29 | | 43 | 4 | 1506 | 13 | | 44 | 2 | 1506 | 11 | | 44 | 26 | 1506 | 29 | | 44 | 4 | 1506 | 13 | | 45 | 2 | 1506 | 11 | | 45 | 26 | 1506 | 29 | | 45 | 4 | 1506 | 13 | | 46 | 2 | 1506 | 11 | | 46 | 26 | 1506 | 29 | | 46 | 4 | 1506 | 13 | | 47 | 2 | 1506 | 11 | | 47 | 26 | 1506 | 29 | | 47 | 4 | 1506 | 13 | | 48 | 2 | 1506 | 11 | | 48 | 26 | 1506 | 29 | | 48 | 4 | 1506 | 13 | | 49 | 2 | 1506 | 11 | | 49 | 26 | 1506 | 29 | | 49 | 4 | 1506 | 13 | +-----------+---------+------------+------------+ 24 rows in set (0.02 sec) |
|
|
do I need to do more or am I able to continue on upgrading past 0.19.11? |
|
|
INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (1506, 2, 11), (1506, 4, 13), (1506, 26, 29); After that the upgrade to 0.19.11 should produce no errors because the schema will be consistent. That said, please check the 8 objects above. They did have the following attributes, which normally are not enabled for their current type: * "HW type" - "server models" * "SW type" - "server OS type" * "Hypervisor" - "Yes/No" * FQDN It is likely that someone in the past had changed their object type from "server" to "VM Resource Pool", which does not normally have these attributes. Older versions of RackTables did not have the guards to prevent this. The INSERT statements you have received add the rules to resolve the inconsistency, but it is only you who can tell what the values mean and if the objects are still relevant. The values may have no sense. If you decide to delete these attribute values or even objects it would be best to remove the four attribute map values added for "VM Resource Pool". Please do not be surprised if upgrading to subsequent versions results in other issues. It is several years of code and schema changes, and not every corner case is always covered, although in most cases things just work. |
|
|
I went ahead and did the following: re-ran the original query that failed on the upgrade from 0.19.10 --> 0.19.11: mysql> ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`); Query OK, 2476 rows affected (0.26 sec) Records: 2476 Duplicates: 0 Warnings: 0 Then proceeded to upgrade to 0.20.0 by doing the following: 1) rm -rf racktables dir on webserver 2) unzip 0.20.0 tarball into racktables dir on webserver 3) copy back secret.php file to wwwroot/inc/. 4) run the upgrade script via browser and confirm the change 5) upgrade said all upgrade steps completed "OK" between 0.19.11 --> 0.20.0 No errors. When I go into the Racktables application, I can see the main page and can click on various things and most everything seems to be working but when I click "rackspace" I get this response in the browser: Pdo exception: PDOException SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Row ORDER BY location_name, name' at line 1 (42000) at file /racktables/wwwroot/inc/database.php, line 3536 /racktables/wwwroot/inc/database.php:3536 execute(Array ( ) ) /racktables/wwwroot/inc/database.php:270 usePreparedSelectBlade('SELECT * FROM Row ORDER BY location_name, name') /racktables/wwwroot/inc/interface.php:344 getAllRows() /racktables/wwwroot/index.php:32 renderRackspace(NULL) Error info: Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Row ORDER BY location_name, name' at line 1 ) Parameters: GET page rackspace POST COOKIE PHPSESSID kmq1ucgnljn6983arjoq87vu4p Any ideas? |
|
|
environment: mysql version: 8.0.22 PHP version: PHP 7.2.22 (cli) (built: Aug 30 2019 00:11:51) ( NTS ) Copyright (c) 1997-2018 The PHP Group |
|
|
The "row" error is an effect of MySQL 8, which was addressed in RackTables 0.21.4 about a year ago (see 1911). To use RackTables before upgrading to the latest stable release you can try switching to MySQL 5.7, although there is a chance it is too new for 0.20.x as well. You may have better experience with MariaDB or MySQL 5.5 or whatever else that was running this RackTables instance before the upgrade. Also PHP 7.2 may be not good for old RackTables. Let me suggest you sort the 8 odd objects as discussed above and then continue upgrading, making sure there are full backups of the database and the files. |
|
|
Has this problem been resolved? |
|
|
Closing due to lack of feedback. |
- Anonymous
