View Issue Details

IDProjectCategoryView StatusLast Update
1635RackTablesdefaultpublic2017-02-06 08:06
Reporterbforpc Assigned Toinfrastation  
PriorityimmediateSeveritycrashReproducibilityalways
Status closedResolutionfixed 
Platformlinux OSubuntu OS Version16.04
Product Version0.20.11 
Target Version0.20.12Fixed in Version0.20.12 
Summary1635: Pdo exception: PDOException
DescriptionPdo exception: PDOException after klicking on a new created row
Steps To ReproduceNew clean installation, after creating an location and a row, klicking on the row, I get the Pdo exception: PDOException

Additional InformationPdo exception: PDOException

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Location.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (42000)
at file /usr/local/share/RackTables/wwwroot/inc/database.php, line 4058

/usr/local/share/RackTables/wwwroot/inc/database.php:4058 execute(Array
(
    [0] => 2
)
)
/usr/local/share/RackTables/wwwroot/inc/database.php:287 usePreparedSelectBlade('SELECT Row.id AS id, Row.name AS name, COUNT(Rack.id) AS count, IF(ISNULL(SUM(Rack.height)),0,SUM(Rack.height)) AS sum, Location.id AS location_id, Location.name AS location FROM Row LEFT JOIN Rack ON Rack.row_id = Row.id LEFT OUTER JOIN Location ON Row.location_id = Location.id WHERE Row.id = ? GROUP BY Row.id', Array
(
    [0] => 2
)
)
/usr/local/share/RackTables/wwwroot/inc/interface.php:682 getRowInfo('2')
/usr/local/share/RackTables/wwwroot/index.php:35 renderRow('2')
Error info:

Array
(
    [0] => 42000
    [1] => 1055
    [2] => Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Location.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
)
Parameters:

GET

page row
row_id 2
POST

COOKIE
TagsNo tags attached.

Relationships

has duplicate 1699 closed Clicking on row causes exception 
has duplicate 1725 closed Creating racks in locations fails due to MYSQL strict settings in Version 5.7 
related to 1547 closed usePreparedSelectBlade query fails when run against MySQL 5.7.10 
related to 1681 acknowledged usePreparedSelectBlade query fails when run against MySQL 5.7.10 

Activities

infrastation

infrastation

2016-06-14 17:35

administrator   ~0003231

The next stable release (0.20.12) should have this issue fixed. For an immediate solution you may want to apply this change:

--- a/wwwroot/inc/pre-init.php
+++ b/wwwroot/inc/pre-init.php
@@ -58,7 +58,11 @@ function connectDB()
        $drvoptions = array
        (
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
- PDO::MYSQL_ATTR_INIT_COMMAND => 'set names "utf8"',
+ // 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", "")',
        );
        if (isset ($pdo_bufsize))
                $drvoptions[PDO::MYSQL_ATTR_MAX_BUFFER_SIZE] = $pdo_bufsize;

Issue History

Date Modified Username Field Change
2016-06-03 12:52 bforpc New Issue
2016-06-04 00:29 infrastation Relationship added related to 1547
2016-06-10 11:08 infrastation Status new => acknowledged
2016-06-14 17:35 infrastation Note Added: 0003231
2016-06-14 17:35 infrastation Assigned To => infrastation
2016-06-14 17:35 infrastation Status acknowledged => closed
2016-06-14 17:35 infrastation Resolution open => fixed
2016-06-14 17:35 infrastation Fixed in Version => 0.20.12
2016-06-14 17:35 infrastation Target Version => 0.20.12
2016-10-26 22:32 infrastation Relationship added has duplicate 1699
2017-02-06 08:06 adoom42 Relationship added has duplicate 1725