View Issue Details

IDProjectCategoryView StatusLast Update
1909RackTablesdefaultpublic2019-06-19 16:38
ReporterdavokazakiAssigned To 
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version0.21.0 
Target Version0.21.4Fixed in Version0.21.4 
Summary1909: Pdo exception: PDOException
DescriptionI 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?
TagsNo tags attached.

Relationships

has duplicate 1911 closed MySQL 8 support 

Activities

infrastation

infrastation

2019-05-24 17:32

administrator   ~0003935

Could you upgrade to version 0.21.2 and check if the problem still persists?
davokazaki

davokazaki

2019-05-28 11:41

reporter   ~0003937

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?
infrastation

infrastation

2019-05-28 13:35

administrator   ~0003939

Oh, I see now. How did you install MySQL 8?
davokazaki

davokazaki

2019-05-30 08:54

reporter   ~0003941

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).
davokazaki

davokazaki

2019-05-30 09:07

reporter   ~0003943

the error in error_log is:

Invalid argument supplied for foreach() in /var/www/html/gestirack/inc/functions.php on line 1647
infrastation

infrastation

2019-05-30 14:20

administrator   ~0003945

Is your goal to fix the upstream?
davokazaki

davokazaki

2019-05-30 16:15

reporter   ~0003947

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.
infrastation

infrastation

2019-05-30 16:20

administrator   ~0003949

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.
davokazaki

davokazaki

2019-05-30 16:47

reporter   ~0003951

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.
infrastation

infrastation

2019-06-01 12:24

administrator   ~0003953

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.
davokazaki

davokazaki

2019-06-03 15:10

reporter   ~0003955

Hello.

the issue comes with i upgrade more than 20.11 version, whith 20.11 version racktables works fine.
infrastation

infrastation

2019-06-03 16:08

administrator   ~0003957

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.
davokazaki

davokazaki

2019-06-04 13:53

reporter   ~0003959

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
davokazaki

davokazaki

2019-06-04 14:12

reporter   ~0003961

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.
infrastation

infrastation

2019-06-04 15:59

administrator   ~0003963

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;
davokazaki

davokazaki

2019-06-04 16:17

reporter   ~0003965

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
infrastation

infrastation

2019-06-04 16:56

administrator   ~0003967

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;
davokazaki

davokazaki

2019-06-04 17:16

reporter   ~0003969

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)
infrastation

infrastation

2019-06-04 17:49

administrator   ~0003971

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
davokazaki

davokazaki

2019-06-05 09:06

reporter   ~0003973

Finally works with the last delete, thank you :D.

Related Changesets

RackTables: master fc92cbc3

2019-06-19 16:22:12

arkadi


Committer: infrastation Details Diff
amend SQL to work on MySQL 8 (Mantis#1909)

ROW is a MySQL 8 keyword.
Affected Issues
1909
mod - ChangeLog Diff File
mod - wwwroot/inc/database.php Diff File

Issue History

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 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