View Issue Details

IDProjectCategoryView StatusLast Update
1681RackTablesdefaultpublic2017-02-09 12:04
Reporterhclima Assigned To 
PrioritynormalSeveritycrashReproducibilityalways
Status acknowledgedResolutionreopened 
PlatformLinuxOSCentOSOS Version6
Product Version0.20.10 
Summary1681: usePreparedSelectBlade query fails when run against MySQL 5.7.10
DescriptionUpgraded database from 5.1 to 5.5, then 5.5 to 5.6, then 5.6 to 5.7.10. At the end of the day, one of my IP ranges throws a PDO exception when viewed:

Partial stack trace (proprietary info if I go any further down)

Pdo exception: PDOException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'container_id' in 'where clause' (42S22)

at file /usr/share/RackTables/wwwroot/inc/database.php, line 3979

/usr/share/RackTables/wwwroot/inc/database.php:3979 execute(Array
(
    [0] => object
    [1] => 7
)
)
/usr/share/RackTables/wwwroot/inc/database.php:590 usePreparedSelectBlade('SELECT tag_id, TagStorage.user as tag_user, UNIX_TIMESTAMP(TagStorage.date) AS tag_time, RackObject.id, RackObject.name, RackObject.label, RackObject.asset_no, RackObject.objtype_id, (SELECT MIN(rack_id) FROM RackSpace WHERE object_id = RackObject.id) as rack_id, (SELECT MIN(parent_entity_id) FROM EntityLink WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'rack') as rack_id_2, (SELECT MIN(parent_entity_id) FROM EntityLink WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'object') as container_id, (SELECT name FROM RackObject WHERE id = container_id) as container_name, (SELECT objtype_id FROM RackObject WHERE id = container_id) as container_objtype_id, RackObject.has_problems, RackObject.comment, (SELECT COUNT(*) FROM Port WHERE object_id = RackObject.id) as nports, (SELECT domain_id FROM VLANSwitch WHERE object_id = id LIMIT 1) as 8021q_domain_id, (SELECT template_id FROM VLANSwitch WHERE object_id = id LIMIT 1) as 8021q_template_id FROM RackObject LEFT JOIN TagStorage on entity_realm = ? and entity_id = RackObject.id WHERE RackObject.id = ? ORDER BY tag_id', Array
(
    [0] => object
    [1] => 7
)
)
/usr/share/RackTables/wwwroot/inc/interface-lib.php:1066 spotEntity('object', '7')
/usr/share/RackTables/wwwroot/inc/interface.php:2939 makeIPAllocLink('
*#?', Array
(
    [type] => regular
    [name] => snmp
    [object_id] => 7
    [object_name] => erg
)
, 1)
/usr/share/RackTables/wwwroot/inc/interface.php:2850 renderIPv4NetworkAddresses(Array
(


It looks like the container_id subselect isn't visible to subsequent subselects in the query. I suspect some semantic changed between MySQL 5.1 and MySQL 5.7.10 that affects that visibility. Query gets chopped off in the PDO exception, so I can't just feed it into MySQL and start taking it apart to see what's happening. I can pull the full query once I enable the general query log, but I've got other priorities right now.
TagsNo tags attached.

Relationships

related to 1635 closedinfrastation Pdo exception: PDOException 

Activities

adoom42

adoom42

2015-12-09 00:06

administrator   ~0003445

I'm unable to reproduce this issue. Please provide more details and ideally specific steps to reproduce.

Until then, this issue will be considered "on hold pending user data".
mikemol

mikemol

2015-12-10 16:23

reporter   ~0003447

a GET request (while logged into MantisBT) to http://f.q.d.n/racktables/index.php?page=ipv4net&id=3

is the URI I used to trigger the the exception. Short of a full database dump, I really don't know what more I can offer you. We're about to replace this particular MySQL 5.7 instance with a Percona 5.6 instance. In the mean time, maybe try your unit test suite against MySQL 5.7, if you haven't?
mikemol

mikemol

2015-12-10 20:56

reporter   ~0003449

Full query:

SELECT 
    tag_id,
    TagStorage.user AS tag_user,
    UNIX_TIMESTAMP(TagStorage.date) AS tag_time,
    RackObject.id,
    RackObject.name,
    RackObject.label,
    RackObject.asset_no,
    RackObject.objtype_id,
    (SELECT 
            MIN(rack_id)
        FROM
            RackSpace
        WHERE
            object_id = RackObject.id) AS rack_id,
    (SELECT 
            MIN(parent_entity_id)
        FROM
            EntityLink
        WHERE
            child_entity_type = 'object'
                AND child_entity_id = RackObject.id
                AND parent_entity_type = 'rack') AS rack_id_2,
    (SELECT 
            MIN(parent_entity_id)
        FROM
            EntityLink
        WHERE
            child_entity_type = 'object'
                AND child_entity_id = RackObject.id
                AND parent_entity_type = 'object') AS container_id,
    (SELECT 
            name
        FROM
            RackObject
        WHERE
            id = container_id) AS container_name,
    (SELECT 
            objtype_id
        FROM
            RackObject
        WHERE
            id = container_id) AS container_objtype_id,
    RackObject.has_problems,
    RackObject.comment,
    (SELECT 
            COUNT(*)
        FROM
            Port
        WHERE
            object_id = RackObject.id) AS nports,
    (SELECT 
            domain_id
        FROM
            VLANSwitch
        WHERE
            object_id = id
        LIMIT 1) AS 8021q_domain_id,
    (SELECT 
            template_id
        FROM
            VLANSwitch
        WHERE
            object_id = id
        LIMIT 1) AS 8021q_template_id
FROM
    RackObject
        LEFT JOIN
    TagStorage ON entity_realm = 'object'
        AND entity_id = RackObject.id
WHERE
    RackObject.id = '7'
ORDER BY tag_id



When run against Percona 5.6, this works fine. When run against MySQL 5.7 with the same dataset, this fails.
Axenow

Axenow

2016-01-19 15:44

reporter   ~0003451

I can confirm, that this mistake is appeared only in MySQL 5.7.X
infrastation

infrastation

2016-05-19 17:09

administrator   ~0003453

This probably has to do with MySQL SQL mode changes in versions 5.7.x.
racktabler

racktabler

2016-06-02 15:15

reporter   ~0003455

No issues so far with MySQL 5.7.12.

I just removed "NO_ZERO_DATE" from 5.7 default modes, so RT install works

file my.cnf

[mysqld]
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
infrastation

infrastation

2016-06-02 15:50

administrator   ~0003457

If you try to click a row one of the queries will fail because of ONLY_FULL_GROUP_BY. I would like to avoid managing the MySQL server if possible.
racktabler

racktabler

2016-06-02 16:40

reporter   ~0003459

I clicked some rows but all looks fine.
I also can execute the full query in mysql without errors. (Empty Set of course)
If i change RackObject.id i get some rows.
infrastation

infrastation

2016-06-04 00:27

administrator   ~0003461

A user on the mailing list has experienced the issue I meant.
infrastation

infrastation

2016-06-14 17:48

administrator   ~0003463

The solution to bug 1635 may have worked around this bug too, it would be great if anybody could check and update. I have not seen this bug reproducing.
infrastation

infrastation

2016-06-22 16:16

administrator   ~0003465

Could anybody test if the original issue still reproduces with the change mentioned in the previous comment applied? Thank you.
infrastation

infrastation

2016-06-29 18:35

administrator   ~0003467

This query does not fail on my MySQL server version 5.7.12. To work on this bug it is necessary to see how to reproduce it.
infrastation

infrastation

2016-08-10 23:11

administrator   ~0003469

If anybody can provide exact instructions how to reproduce this issue, please reopen.
hclima

hclima

2016-09-02 18:05

reporter   ~0003471

Last edited: 2016-09-02 18:19

Hi.

Here what I got:

* OS: Windows 2008 R2 (I know that Windows is not supported, but for this diagnostic I think it's not the root cause)
* WAMP Server 3.0.0
  - MySQL: 5.7.9
  - PHP: 5.6.16
  - Apache: 2.4.17
* Rack Tables 10.11

Installation successfully done and I was able to create Locations, Rows and Racks.
Now whenever I try to create an object I got the following output:

#-------------- Start Output
Pdo exception: PDOException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'container_id' in 'where clause' (42S22)
at file C:\wamp64\apps\RackTables-0.20.11\wwwroot\inc\database.php, line 4058

C:\wamp64\apps\RackTables-0.20.11\wwwroot\inc\database.php:4058 execute(Array
(
    [0] => object
    [1] => 115
)
)
C:\wamp64\apps\RackTables-0.20.11\wwwroot\inc\database.php:587 usePreparedSelectBlade('SELECT tag_id, TagStorage.user as tag_user, UNIX_TIMESTAMP(TagStorage.date) AS tag_time, RackObject.id, RackObject.name, RackObject.label, RackObject.asset_no, RackObject.objtype_id, (SELECT MIN(rack_id) FROM RackSpace WHERE object_id = RackObject.id) as rack_id, (SELECT MIN(parent_entity_id) FROM EntityLink WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'rack') as rack_id_2, (SELECT MIN(parent_entity_id) FROM EntityLink WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'object') as container_id, (SELECT name FROM RackObject WHERE id = container_id) as container_name, (SELECT objtype_id FROM RackObject WHERE id = container_id) as container_objtype_id, RackObject.has_problems, RackObject.comment, (SELECT COUNT(*) FROM Port WHERE object_id = RackObject.id) as nports, (SELECT domain_id FROM VLANSwitch WHERE object_id = id LIMIT 1) as 8021q_domain_id, (SELECT template_id FROM VLANSwitch WHERE object_id = id LIMIT 1) as 8021q_template_id FROM RackObject LEFT JOIN TagStorage on entity_realm = ? and entity_id = RackObject.id WHERE RackObject.id = ?', Array
(
    [0] => object
    [1] => 115
)
)
C:\wamp64\apps\RackTables-0.20.11\wwwroot\inc\database.php:4138 spotEntity('object', '115')
C:\wamp64\apps\RackTables-0.20.11\wwwroot\inc\database.php:960 executeAutoPorts('115')
C:\wamp64\apps\RackTables-0.20.11\wwwroot\inc\ophandlers.php:1413 commitAddObject('Test', 'Test', '4', '', Array
(
)
)
addMultipleObjects()
C:\wamp64\apps\RackTables-0.20.11\wwwroot\index.php:231 call_user_func('addMultipleObjects')
Error info:
Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'container_id' in 'where clause'
)
Parameters:
GET
module redirect
page depot
tab addmore
op addObjects

POST
0_object_type_id 4
0_object_name Test
0_object_label Test
0_object_asset_no
1_object_type_id 0
1_object_name
1_object_label
1_object_asset_no
2_object_type_id 0
2_object_name
2_object_label
2_object_asset_no
3_object_type_id 0
3_object_name
3_object_label
3_object_asset_no
4_object_type_id 0
4_object_name
4_object_label
4_object_asset_no
5_object_type_id 0
5_object_name
5_object_label
5_object_asset_no
6_object_type_id 0
6_object_name
6_object_label
6_object_asset_no
7_object_type_id 0
7_object_name
7_object_label
7_object_asset_no
got_fast_data Go!

COOKIE
PHPSESSID lshlrl9tgsih59f709nehn2pc7
MYSAPSSO2 AjExMDAgAA9wb3J0YWw6SjAyMjQ0NDWIABNiYXNpY2F1dGhlbnRpY2F0aW9uAQAISjAyMjQ0NDUCAAMwMDADAANGUDMEAAwyMDE…

#-------------- End Output

After digging a bit on the output I got that it fails in the select part where it needs to use the "container_id" result which is NULL because in the table EntityLink there's no row with parent_entity_type = 'object'.

#-------------- Start Code
SELECT
  tag_id,
  TagStorage.user as tag_user,
  UNIX_TIMESTAMP(TagStorage.date) AS tag_time,
  RackObject.id,
  RackObject.name,
  RackObject.label,
  RackObject.asset_no,
  RackObject.objtype_id,
  (SELECT MIN(rack_id) FROM RackSpace WHERE object_id = RackObject.id) as rack_id,
  (SELECT MIN(parent_entity_id)
    FROM EntityLink
    WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'rack') as rack_id_2,
  (SELECT MIN(parent_entity_id)
    FROM EntityLink
    WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'object') as container_id, <<----
  (SELECT name FROM RackObject WHERE id = container_id) as container_name,
  (SELECT objtype_id FROM RackObject WHERE id = container_id) as container_objtype_id, <<----
  RackObject.has_problems,
  RackObject.comment,
  (SELECT COUNT(*) FROM Port WHERE object_id = RackObject.id) as nports,
  (SELECT domain_id FROM VLANSwitch WHERE object_id = id LIMIT 1) as 8021q_domain_id,
  (SELECT template_id FROM VLANSwitch WHERE object_id = id LIMIT 1) as 8021q_template_id
FROM RackObject
LEFT JOIN TagStorage on entity_realm = ? and entity_id = RackObject.id WHERE RackObject.id = ?

#-------------- End Code

Can the issue be on the content of the table EntityLink or in the SQL code which don't take into account when the "container_id" value is NULL?

hclima

hclima

2016-09-02 18:17

reporter   ~0003473

Also, after apply the fix of bug 1635 I got the same error message.

#----Before

$drvoptions = array
(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'set names "utf8"',
);

#----

#---- After
$drvoptions = array
(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
// Cancel two specific SQL mode options that RackTables has been non-compliant
// with but which used to be off by default until MySQL 5.7. As soon as
// respective SQL queries and table columns become compliant with those options
// stop changing @@SQL_MODE but still keep SET NAMES in place.
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES "utf8", @@SQL_MODE = REPLACE(REPLACE(@@SQL_MODE, "NO_ZERO_DATE", ""), "ONLY_FULL_GROUP_BY", "")',
);

#----
hclima

hclima

2016-09-02 18:37

reporter   ~0003475

Another thing, even with the error message the object information is registred in the table "object" and I need to delete it to be able to reuse the Object tab on RackTables.

Issue History

Date Modified Username Field Change
2016-09-02 18:04 hclima New Issue
2016-09-02 18:04 hclima Issue generated from: 1547
2016-09-02 18:05 hclima Note Added: 0003471
2016-09-02 18:05 hclima Status closed => feedback
2016-09-02 18:05 hclima Resolution unable to reproduce => reopened
2016-09-02 18:17 hclima Note Added: 0003473
2016-09-02 18:17 hclima Status feedback => new
2016-09-02 18:18 hclima Note Edited: 0003471
2016-09-02 18:19 hclima Note Edited: 0003471
2016-09-02 18:37 hclima Note Added: 0003475
2017-02-09 12:04 infrastation Status new => acknowledged