View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
1909 | RackTables | default | public | 2019-05-24 14:17 | 2019-11-11 21:22 |
Reporter | davokazaki | Assigned To | |||
Priority | normal | Severity | major | Reproducibility | always |
Status | closed | Resolution | fixed | ||
Product Version | 0.21.0 | ||||
Target Version | 0.21.4 | Fixed in Version | 0.21.4 | ||
Summary | 1909: Pdo exception: PDOException | ||||
Description | I just recently migrated from machine version 0.20.0, but when I get inside racktables I get this error. 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 /var/www/html/gestirack/inc/database.php, line 3536 /var/www/html/gestirack/inc/database.php:3536 execute(Array ( ) ) /var/www/html/gestirack/inc/database.php:270 usePreparedSelectBlade('SELECT * FROM Row ORDER BY location_name, name') /var/www/html/gestirack/inc/interface.php:344 getAllRows() renderRackspace(NULL) /var/www/html/gestirack/index.php:32 call_user_func('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 dc55h6igu7l3di6nod4gei2cr5 Should I update to the new version to be solved? | ||||
Tags | No tags attached. | ||||
Could you upgrade to version 0.21.2 and check if the problem still persists? | |
I just upgrade but i got the same error, the problem comes when racktables does select * from row since row is a mysql keyword from version 8.0.2, is there any solution for this? |
|
Oh, I see now. How did you install MySQL 8? | |
at the end I have fixed it, I have replaced the select from Row by Select from `Row` in the database.php file and now works fine, but I have one more problem, when I go to reports and I click on a list of objects I get the following error. Internal error Argument 'input' of value '(array)' is invalid (ID column missing). |
|
the error in error_log is: Invalid argument supplied for foreach() in /var/www/html/gestirack/inc/functions.php on line 1647 |
|
Is your goal to fix the upstream? | |
for the last mistake that I have commented I do not have much idea about what I can do, the only thing i see is in reports there is a blank record. |
|
It will be much easier to solve the problem if I can reproduce the bug myself. Which will be much easier to do if you tell how you had set up the server that runs RackTables. | |
I have this configuration on the server: MYSQL 8.0.16 PHP 5.5.38 S.o: RHEL 7 i migrate 20.0 to 21.2 When i go reports and click a number of object with one tag, or click a tag in objects the error is: Argument 'input' of value '(array)' is invalid (ID column missing) Invalid argument supplied for foreach() in /var/www/html/gestirack/inc/functions.php on line 1647 Thanks. |
|
I took time to look into database.php and functions.php as they appear in releases 0.21.0 and 0.21.2, and could not correlate the source code with the error messages you have provided. It is easy to see why the server would reject the SQL query due to a new keyword, but RackTables does not make this particular query in this particular place of database.php as far as I can see (if you show the actual diff of the changes you made it may be easier to see). It would probably happen in a couple _other_ functions in database.php, but before fixing that I would like to have a reliable way to reproduce the issue locally. For the PHP exception to happen in reindexById() in functions.php the function argument has to be incorrectly formed, which may also emit the PHP warning just before in sortEntityTags(), but the warning would mean that either $taglist is malformed/undefined, or an explicit tag recorded for the object in the TagStorage SQL table has an invalid ID, which is impossible unless the foreign key is broken. A likely cause of this issue is that this installation of RackTables mixes PHP files from different versions and/or has local modifications. I recommend checking the PHP files one by one to tell if the source code on your server is identical to what is in the tar.gz. |
|
Hello. the issue comes with i upgrade more than 20.11 version, whith 20.11 version racktables works fine. |
|
Alright, it looks like it will take some time to troubleshoot this problem. Meanwhile please try to provide the additional information I had requested in the earlier comments. | |
if help here is how the tables are created Table: TagTree Create Table: CREATE TABLE `TagTree` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned DEFAULT NULL, `is_assignable` enum('yes','no') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'yes', `tag` char(255) COLLATE utf8_unicode_ci DEFAULT NULL, `color` mediumint(8) unsigned DEFAULT NULL, `description` char(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tag` (`tag`), KEY `TagTree-K-parent_id` (`parent_id`), KEY `id-is_assignable` (`id`,`is_assignable`), CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (`parent_id`) REFERENCES `TagTree` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci show create table TagStorage \G; *************************** 1. row *************************** Table: TagStorage Create Table: CREATE TABLE `TagStorage` ( `entity_realm` enum('file','ipv4net','ipv4rspool','ipv4vs','ipvs','ipv6net','location','object','rack','user','vst') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'object', `entity_id` int(10) unsigned NOT NULL, `tag_id` int(10) unsigned NOT NULL DEFAULT '0', `tag_is_assignable` enum('yes','no') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'yes', `user` char(64) COLLATE utf8_unicode_ci DEFAULT NULL, `date` datetime DEFAULT NULL, UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`), KEY `entity_id` (`entity_id`), KEY `TagStorage-FK-tag_id` (`tag_id`), KEY `tag_id-tag_is_assignable` (`tag_id`,`tag_is_assignable`), CONSTRAINT `TagStorage-FK-TagTree` FOREIGN KEY (`tag_id`, `tag_is_assignable`) REFERENCES `TagTree` (`id`, `is_assignable`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
|
i have this error to: Notice: Undefined offset: 0 in /var/www/html/pruebasgesti/inc/database.php on line 496 Notice: Undefined offset: 0 in /var/www/html/pruebasgesti/inc/database.php on line 497 Notice: Undefined offset: 0 in /var/www/html/pruebasgesti/inc/database.php on line 498 Notice: Undefined offset: 0 in /var/www/html/pruebasgesti/inc/database.php on line 499 seems like the files of racktables are fine. |
|
Both tables look fine, including the foreign key. But this is not consistent with the 4 warnings in the most recent comment, where the single table SELECT returns tag_id as either 0 or NULL. What do the queries below return? SELECT @FOREIGN_KEY_CHECKS, @@FOREIGN_KEY_CHECKS; SELECT * FROM TagStorage WHERE tag_id IS NULL OR tag_id = 0; |
|
These are the results mysql> SELECT @FOREIGN_KEY_CHECKS, @@FOREIGN_KEY_CHECKS; +---------------------+----------------------+ | @FOREIGN_KEY_CHECKS | @@FOREIGN_KEY_CHECKS | +---------------------+----------------------+ | NULL | 1 | +---------------------+----------------------+ entity_realm | entity_id | tag_id | tag_is_assignable | user | date | +--------------+-----------+--------+-------------------+------+------+ | object | 200 | 0 | yes | NULL | NULL | | object | 204 | 0 | yes | NULL | NULL | | object | 205 | 0 | yes | NULL | NULL | | object | 206 | 0 | yes | NULL | NULL | | object | 219 | 0 | yes | NULL | NULL | | object | 227 | 0 | yes | NULL | NULL | | object | 232 | 0 | yes | NULL | NULL | | object | 270 | 0 | yes | NULL | NULL | | object | 286 | 0 | yes | NULL | NULL | | object | 297 | 0 | yes | NULL | NULL | | object | 298 | 0 | yes | NULL | NULL | ..................................................................................................... ..................................................................................................... 73 rows in set |
|
What does the following return? BEGIN; DELETE FROM TagStorage WHERE entity_realm = 'object' AND entity_id = 200 AND tag_id = 0; INSERT INTO TagStorage (entity_realm, entity_id, tag_id) VALUES ('object', 200, 0); ROLLBACK; |
|
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM TagStorage WHERE entity_realm = 'object' AND entity_id = 200 AND tag_id = 0; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO TagStorage (entity_realm, entity_id, tag_id) VALUES ('object', 200, 0); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`racktables_pruebas`.`TagStorage`, CONSTRAINT `TagStorage-FK-TagTree` FOREIGN KEY (`tag_id`, `tag_is_assignable`) REFERENCES `TagTree` (`id`, `is_assignable`)) mysql> ROLLBACK; Query OK, 0 rows affected (0.03 sec) |
|
This proves the foreign key is at least partially working, so the stray rows could be in the table because the foreign key checks were switched off for some length of time, and somebody deleted some tags during that time. Or because ON DELETE CASCADE in the foreign key did not work right (I cannot provide a simple query to test that right now). There may be other referential integrity problems. Although I do not see the exact reason why this issue was not visible before the RackTables upgrade, I do not see why the invalid rows should remain in the database. Please make a backup of the database and then delete the invalid rows with: DELETE FROM TagStorage WHERE tag_id IS NULL OR tag_id = 0 |
|
Finally works with the last delete, thank you :D. | |
Date Modified | Username | Field | Change |
---|---|---|---|
2019-05-24 14:17 | davokazaki | New Issue | |
2019-05-24 17:32 | infrastation | Note Added: 0003935 | |
2019-05-28 11:41 | davokazaki | Note Added: 0003937 | |
2019-05-28 13:35 | infrastation | Note Added: 0003939 | |
2019-05-30 08:54 | davokazaki | Note Added: 0003941 | |
2019-05-30 09:07 | davokazaki | Note Added: 0003943 | |
2019-05-30 14:20 | infrastation | Note Added: 0003945 | |
2019-05-30 16:15 | davokazaki | Note Added: 0003947 | |
2019-05-30 16:20 | infrastation | Note Added: 0003949 | |
2019-05-30 16:47 | davokazaki | Note Added: 0003951 | |
2019-06-01 12:24 | infrastation | Note Added: 0003953 | |
2019-06-03 15:10 | davokazaki | Note Added: 0003955 | |
2019-06-03 16:08 | infrastation | Note Added: 0003957 | |
2019-06-04 13:53 | davokazaki | Note Added: 0003959 | |
2019-06-04 14:12 | davokazaki | Note Added: 0003961 | |
2019-06-04 15:59 | infrastation | Note Added: 0003963 | |
2019-06-04 16:17 | davokazaki | Note Added: 0003965 | |
2019-06-04 16:56 | infrastation | Note Added: 0003967 | |
2019-06-04 17:16 | davokazaki | Note Added: 0003969 | |
2019-06-04 17:49 | infrastation | Note Added: 0003971 | |
2019-06-05 09:06 | davokazaki | Note Added: 0003973 | |
2019-06-19 14:02 | infrastation | Relationship added | has duplicate 1911 |
2019-06-19 16:31 | infrastation | Source_changeset_attached | => RackTables master fc92cbc3 |
2019-06-19 16:38 | infrastation | Status | new => closed |
2019-06-19 16:38 | infrastation | Resolution | open => fixed |
2019-06-19 16:38 | infrastation | Fixed in Version | => 0.21.4 |
2019-06-19 16:38 | infrastation | Target Version | => 0.21.4 |
2019-11-11 21:21 | infrastation | Relationship added | has duplicate 1927 |
2019-11-11 21:22 | infrastation | Relationship added | has duplicate 1941 |