 |
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". |
 |
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? |
|
|
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_idWhen run against Percona 5.6, this works fine. When run against MySQL 5.7 with the same dataset, this fails. |
 |
I can confirm, that this mistake is appeared only in MySQL 5.7.X |
 |
This probably has to do with MySQL SQL mode changes in versions 5.7.x. |
 |
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" |
|
|
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. |
|
|
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. |
|
|
A user on the mailing list has experienced the issue I meant. |
|
|
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. |
|
|
Could anybody test if the original issue still reproduces with the change mentioned in the previous comment applied? Thank you. |
|
|
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. |
|
|
If anybody can provide exact instructions how to reproduce this issue, please reopen. |
 |
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? |
|
|
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", "")', ); #---- |
|
|
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. |
- Anonymous
