New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Incompatibility with Mysql 5.7 #8121

Closed
gerard-kanters opened this Issue Apr 6, 2015 · 18 comments

Comments

Projects
None yet
@gerard-kanters
Contributor

gerard-kanters commented Apr 6, 2015

Exception #1428314231: exception 'DatabaseException' with message 'Expression #1 of ORDER BY clause is not in SELECT list, references column 'elgg.ps.value' which is not in SELECT list;

This is caused by the default mysql mode in 5.7 which is ONLY_FULL_GROUP_BY

Elgg database is not compatble with that. Turning that mode off, will allow you to continue.

@Srokap

This comment has been minimized.

Show comment
Hide comment
@Srokap

Srokap Apr 6, 2015

Contributor

Can't you include full stack trace? Would be interested in if bad query params originate from core or 3rd party code. That's also something to take care of when allowing to use postgres. It's much more strict about proper aggregations vs selects than usual MySQL.

Contributor

Srokap commented Apr 6, 2015

Can't you include full stack trace? Would be interested in if bad query params originate from core or 3rd party code. That's also something to take care of when allowing to use postgres. It's much more strict about proper aggregations vs selects than usual MySQL.

@gerard-kanters

This comment has been minimized.

Show comment
Hide comment
@gerard-kanters

gerard-kanters Apr 6, 2015

Contributor

Sure I can : It is core. I tested it without any third party plugins.

Exception #1428314231: exception 'DatabaseException' with message 'Expression #1 of ORDER BY clause is not in SELECT list, references column 'elgg.ps.value' which is not in SELECT list; this is incompatible with DISTINCT\n\n QUERY: SELECT DISTINCT e., plugin_oe., r.id FROM elgg_entities e JOIN elgg_private_settings ps on ps.entity_guid = e.guid JOIN elgg_objects_entity plugin_oe on plugin_oe.guid = e.guid JOIN elgg_entity_relationships r on r.guid_one = e.guid WHERE ps.name = 'elgg:internal:priority' AND (r.relationship = 'active_plugin' AND r.guid_two = '1') AND ((e.type = 'object' AND e.subtype IN (2))) AND (e.site_guid IN (1)) AND ( (1 = 1) and e.enabled='yes') ORDER BY CAST(ps.value as unsigned), e.guid' in /var/www/elgg/engine/lib/database.php:274\nStack trace:\n#0 /var/www/elgg/engine/lib/database.php(416): execute_query('SELECT DISTINCT...', Resource id #4)\n#1 /var/www/elgg/engine/lib/database.php(362): elgg_query_runner('SELECT DISTINCT...', '', false)\n#2 /var/www/elgg/engine/lib/entities.php(1118): get_data('SELECT DISTINCT...')\n#3 /var/www/elgg/engine/lib/entities.php(1062): _elgg_fetch_entities_from_sql('SELECT DISTINCT...', NULL)\n#4 /var/www/elgg/engine/lib/metadata.php(450): elgg_get_entities(Array)\n#5 /var/www/elgg/engine/lib/relationships.php(307): elgg_get_entities_from_metadata(Array)\n#6 /var/www/elgg/engine/lib/plugins.php(414): elgg_get_entities_from_relationship(Array)\n#7 /var/www/elgg/engine/lib/plugins.php(341): elgg_get_plugins('active')\n#8 /var/www/elgg/engine/start.php(102): elgg_load_plugins()\n#9 /var/www/elgg/index.php(12): require_once('/var/www/elgg...')\n#10 {main}

Contributor

gerard-kanters commented Apr 6, 2015

Sure I can : It is core. I tested it without any third party plugins.

Exception #1428314231: exception 'DatabaseException' with message 'Expression #1 of ORDER BY clause is not in SELECT list, references column 'elgg.ps.value' which is not in SELECT list; this is incompatible with DISTINCT\n\n QUERY: SELECT DISTINCT e., plugin_oe., r.id FROM elgg_entities e JOIN elgg_private_settings ps on ps.entity_guid = e.guid JOIN elgg_objects_entity plugin_oe on plugin_oe.guid = e.guid JOIN elgg_entity_relationships r on r.guid_one = e.guid WHERE ps.name = 'elgg:internal:priority' AND (r.relationship = 'active_plugin' AND r.guid_two = '1') AND ((e.type = 'object' AND e.subtype IN (2))) AND (e.site_guid IN (1)) AND ( (1 = 1) and e.enabled='yes') ORDER BY CAST(ps.value as unsigned), e.guid' in /var/www/elgg/engine/lib/database.php:274\nStack trace:\n#0 /var/www/elgg/engine/lib/database.php(416): execute_query('SELECT DISTINCT...', Resource id #4)\n#1 /var/www/elgg/engine/lib/database.php(362): elgg_query_runner('SELECT DISTINCT...', '', false)\n#2 /var/www/elgg/engine/lib/entities.php(1118): get_data('SELECT DISTINCT...')\n#3 /var/www/elgg/engine/lib/entities.php(1062): _elgg_fetch_entities_from_sql('SELECT DISTINCT...', NULL)\n#4 /var/www/elgg/engine/lib/metadata.php(450): elgg_get_entities(Array)\n#5 /var/www/elgg/engine/lib/relationships.php(307): elgg_get_entities_from_metadata(Array)\n#6 /var/www/elgg/engine/lib/plugins.php(414): elgg_get_entities_from_relationship(Array)\n#7 /var/www/elgg/engine/lib/plugins.php(341): elgg_get_plugins('active')\n#8 /var/www/elgg/engine/start.php(102): elgg_load_plugins()\n#9 /var/www/elgg/index.php(12): require_once('/var/www/elgg...')\n#10 {main}

@gerard-kanters

This comment has been minimized.

Show comment
Hide comment
@gerard-kanters

gerard-kanters Apr 6, 2015

Contributor

The full stack trace on a 1.10.4 instance looks like this:

Exception #1428343845: exception 'DatabaseException' with message 'Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.n_table.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n\n QUERY:

SELECT DISTINCT  n_table.*, e.guid, COUNT(*) AS cnt
FROM elgg_annotations n_table
JOIN elgg_entities e ON n_table.entity_guid = e.guid
JOIN elgg_metastrings msn on n_table.name_id = msn.id
WHERE  (e.guid IN (50,49,47,46))
AND  ((msn.string IN ('likes'))
AND ((1 = 1)
AND (n_table.enabled = 'yes')))
AND ((1 = 1) AND (e.enabled = 'yes'))
GROUP BY e.guid
ORDER BY n_table.time_created ASC, n_table.id ASC, n_table.id
LIMIT 0, 10

' in /var/www/test/engine/classes/Elgg/Database.php:405\nStack trace:\n#0 /var/www/test/engine/classes/Elgg/Database.php(349): Elgg\Database->executeQuery('SELECT DISTINCT...', Resource id #1)\n#1 /var/www/test/engine/classes/Elgg/Database.php(175): Elgg\Database->getResults('SELECT DISTINCT...', false, false)\n#2 /var/www/test/engine/lib/database.php(51): Elgg\Database->getData('SELECT DISTINCT...', false)\n#3 /var/www/test/engine/lib/metastrings.php(324): get_data('SELECT DISTINCT...', false)\n#4 /var/www/test/engine/classes/Elgg/Database/Annotations.php(220): _elgg_get_metastring_based_objects(Array)\n#5 /var/www/test/engine/lib/annotations.php(114): Elgg\Database\Annotations->find(Array)\n#6 /var/www/test/mod/likes/classes/Elgg/Likes/Preloader.php(44): elgg_get_annotations(Array)\n#7 /var/www/test/mod/likes/classes/Elgg/Likes/Preloader.php(30): Elgg\Likes\Preloader->preloadCountsFromQuery(Array)\n#8 /var/www/test/mod/likes/views/default/likes/before_lists.php(15): Elgg\Likes\Preloader->preloadForList(Array)\n#9 /var/www/test/engine/classes/Elgg/ViewsService.php(364): include('/var/www/test/m...')\n#10 /var/www/test/engine/classes/Elgg/ViewsService.php(299): Elgg\ViewsService->renderViewFile('likes/before_li...', Array, 'default', true)\n#11 /var/www/test/engine/lib/views.php(355): Elgg\ViewsService->renderView('page/components...', Array, false, '')\n#12 /var/www/test/engine/lib/river.php(530): elgg_view('page/components...', Array)\n#13 /var/www/test/pages/river.php(55): elgg_list_river(Array)\n#14 /var/www/test/engine/lib/river.php(740): require_once('/var/www/test/p...')\n#15 [internal function]: _elgg_river_page_handler(Array, 'activity')\n#16 /var/www/test/engine/classes/Elgg/Router.php(80): call_user_func('_elgg_river_pag...', Array, 'activity')\n#17 /var/www/test/index.php(60): Elgg\Router->route(Object(Elgg\Http\Request))\n#18 {main}
[Mon Apr 06 20:10:45 2015] [error] [client 192.168.2.101] sending email to: gkanters@netcare.nl

Contributor

gerard-kanters commented Apr 6, 2015

The full stack trace on a 1.10.4 instance looks like this:

Exception #1428343845: exception 'DatabaseException' with message 'Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.n_table.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n\n QUERY:

SELECT DISTINCT  n_table.*, e.guid, COUNT(*) AS cnt
FROM elgg_annotations n_table
JOIN elgg_entities e ON n_table.entity_guid = e.guid
JOIN elgg_metastrings msn on n_table.name_id = msn.id
WHERE  (e.guid IN (50,49,47,46))
AND  ((msn.string IN ('likes'))
AND ((1 = 1)
AND (n_table.enabled = 'yes')))
AND ((1 = 1) AND (e.enabled = 'yes'))
GROUP BY e.guid
ORDER BY n_table.time_created ASC, n_table.id ASC, n_table.id
LIMIT 0, 10

' in /var/www/test/engine/classes/Elgg/Database.php:405\nStack trace:\n#0 /var/www/test/engine/classes/Elgg/Database.php(349): Elgg\Database->executeQuery('SELECT DISTINCT...', Resource id #1)\n#1 /var/www/test/engine/classes/Elgg/Database.php(175): Elgg\Database->getResults('SELECT DISTINCT...', false, false)\n#2 /var/www/test/engine/lib/database.php(51): Elgg\Database->getData('SELECT DISTINCT...', false)\n#3 /var/www/test/engine/lib/metastrings.php(324): get_data('SELECT DISTINCT...', false)\n#4 /var/www/test/engine/classes/Elgg/Database/Annotations.php(220): _elgg_get_metastring_based_objects(Array)\n#5 /var/www/test/engine/lib/annotations.php(114): Elgg\Database\Annotations->find(Array)\n#6 /var/www/test/mod/likes/classes/Elgg/Likes/Preloader.php(44): elgg_get_annotations(Array)\n#7 /var/www/test/mod/likes/classes/Elgg/Likes/Preloader.php(30): Elgg\Likes\Preloader->preloadCountsFromQuery(Array)\n#8 /var/www/test/mod/likes/views/default/likes/before_lists.php(15): Elgg\Likes\Preloader->preloadForList(Array)\n#9 /var/www/test/engine/classes/Elgg/ViewsService.php(364): include('/var/www/test/m...')\n#10 /var/www/test/engine/classes/Elgg/ViewsService.php(299): Elgg\ViewsService->renderViewFile('likes/before_li...', Array, 'default', true)\n#11 /var/www/test/engine/lib/views.php(355): Elgg\ViewsService->renderView('page/components...', Array, false, '')\n#12 /var/www/test/engine/lib/river.php(530): elgg_view('page/components...', Array)\n#13 /var/www/test/pages/river.php(55): elgg_list_river(Array)\n#14 /var/www/test/engine/lib/river.php(740): require_once('/var/www/test/p...')\n#15 [internal function]: _elgg_river_page_handler(Array, 'activity')\n#16 /var/www/test/engine/classes/Elgg/Router.php(80): call_user_func('_elgg_river_pag...', Array, 'activity')\n#17 /var/www/test/index.php(60): Elgg\Router->route(Object(Elgg\Http\Request))\n#18 {main}
[Mon Apr 06 20:10:45 2015] [error] [client 192.168.2.101] sending email to: gkanters@netcare.nl

@Srokap Srokap added the important label Apr 6, 2015

@Srokap

This comment has been minimized.

Show comment
Hide comment
@Srokap

Srokap Apr 6, 2015

Contributor

Yeah, I didn't think that n_table will bite us before we get to postgresql compatibility. Perhaps it's better this way.

@Elgg/owners What would you say for a solution that checks for use of n_table from the core parameters? Should we support folks that use n_table in custom where clauses? Code to detect that might be pretty awful. I'd prefer to treat this join as private api.

Contributor

Srokap commented Apr 6, 2015

Yeah, I didn't think that n_table will bite us before we get to postgresql compatibility. Perhaps it's better this way.

@Elgg/owners What would you say for a solution that checks for use of n_table from the core parameters? Should we support folks that use n_table in custom where clauses? Code to detect that might be pretty awful. I'd prefer to treat this join as private api.

@beck24

This comment has been minimized.

Show comment
Hide comment
@beck24

beck24 Apr 6, 2015

Member

I've used n_table custom where clauses a lot...

Member

beck24 commented Apr 6, 2015

I've used n_table custom where clauses a lot...

@Srokap

This comment has been minimized.

Show comment
Hide comment
@Srokap

Srokap Apr 6, 2015

Contributor

Do you at least use it with n_table. prefix?

Contributor

Srokap commented Apr 6, 2015

Do you at least use it with n_table. prefix?

@beck24

This comment has been minimized.

Show comment
Hide comment
@beck24

beck24 Apr 6, 2015

Member

oh yes, I always prefix

Member

beck24 commented Apr 6, 2015

oh yes, I always prefix

@Srokap

This comment has been minimized.

Show comment
Hide comment
@Srokap

Srokap Apr 7, 2015

Contributor

Ok, so I imagine we could check:

  • joins
  • wheres
  • group by
  • order by
  • selects

For existence of n_table.FIELD (with escaping, whitechars etc.) and only than add the n_table join.

In addition to usual parameters that might force n_table usage.

That wouldn't be enough to guarantee that aggregations would be fine, but would address usual n_table issue that also forces us to use DISTINCT. I'm not sure yet if we could try to make reliable algorithm for determining aggregation requirements. Theoretically why not, but not eager to go into that.

Contributor

Srokap commented Apr 7, 2015

Ok, so I imagine we could check:

  • joins
  • wheres
  • group by
  • order by
  • selects

For existence of n_table.FIELD (with escaping, whitechars etc.) and only than add the n_table join.

In addition to usual parameters that might force n_table usage.

That wouldn't be enough to guarantee that aggregations would be fine, but would address usual n_table issue that also forces us to use DISTINCT. I'm not sure yet if we could try to make reliable algorithm for determining aggregation requirements. Theoretically why not, but not eager to go into that.

@jeabakker

This comment has been minimized.

Show comment
Hide comment
@jeabakker

jeabakker Apr 10, 2015

Member

related use case

$options = array(
  'type' => 'user',
  'joins' => array('join users_entity ue on e.guid = ue.guid')
  'order_by' => 'ue.name'
);

same applies for groups, site and objects

Member

jeabakker commented Apr 10, 2015

related use case

$options = array(
  'type' => 'user',
  'joins' => array('join users_entity ue on e.guid = ue.guid')
  'order_by' => 'ue.name'
);

same applies for groups, site and objects

@bjm88

This comment has been minimized.

Show comment
Hide comment
@bjm88

bjm88 Dec 8, 2015

FYI I found this and had entered a similar ticket against Ruby on Rails.
rails/rails#22531

its related to MySQL 5.7 change, can't have order by that isn't in select for aggregations, group or distincts included.

http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html#mysql-nutshell-deprecations

bjm88 commented Dec 8, 2015

FYI I found this and had entered a similar ticket against Ruby on Rails.
rails/rails#22531

its related to MySQL 5.7 change, can't have order by that isn't in select for aggregations, group or distincts included.

http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html#mysql-nutshell-deprecations

@sdcuike

This comment has been minimized.

Show comment
Hide comment
@sdcuike

sdcuike Feb 23, 2016

Ye,mysql5.7 add new sql-mode ONLY_FULL_GROUP_BY,effect some sql.


To change the SQL mode at runtime, set the global or session sql_mode system variable using a SET statement:

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';
Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Each client can change its session sql_mode value at any time.

To determine the current global or session sql_mode value, use the following statements:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

sdcuike commented Feb 23, 2016

Ye,mysql5.7 add new sql-mode ONLY_FULL_GROUP_BY,effect some sql.


To change the SQL mode at runtime, set the global or session sql_mode system variable using a SET statement:

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';
Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Each client can change its session sql_mode value at any time.

To determine the current global or session sql_mode value, use the following statements:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

@lowfill

This comment has been minimized.

Show comment
Hide comment
@lowfill

lowfill Apr 27, 2016

Contributor

Thanks @sdcuike your fix help me.

As super user I used:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Contributor

lowfill commented Apr 27, 2016

Thanks @sdcuike your fix help me.

As super user I used:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

@n3edy

This comment has been minimized.

Show comment
Hide comment
@n3edy

n3edy May 16, 2016

Thank you, it works for me too. I entered the same query.

n3edy commented May 16, 2016

Thank you, it works for me too. I entered the same query.

@gabiudrescu

This comment has been minimized.

Show comment
Hide comment
@gabiudrescu

gabiudrescu May 25, 2016

@lowfill thanks for the tip - finally reached this after a couple of research.

any ideas how to make this persist over mysql restart?

gabiudrescu commented May 25, 2016

@lowfill thanks for the tip - finally reached this after a couple of research.

any ideas how to make this persist over mysql restart?

@w23ta0

This comment has been minimized.

Show comment
Hide comment
@w23ta0

w23ta0 commented Jun 16, 2016

@gabiudrescu

This comment has been minimized.

Show comment
Hide comment
@gabiudrescu

gabiudrescu Aug 18, 2016

if you want to solve this for good, from the infra point of view, you could use this: http://stackoverflow.com/questions/35670999/doctrine-querybuilder-order-by-clause-is-not-in-select-list

gabiudrescu commented Aug 18, 2016

if you want to solve this for good, from the infra point of view, you could use this: http://stackoverflow.com/questions/35670999/doctrine-querybuilder-order-by-clause-is-not-in-select-list

@mrclay

This comment has been minimized.

Show comment
Hide comment
@mrclay
Member

mrclay commented Aug 25, 2016

PR #10122

mrclay added a commit to mrclay/Elgg-leaf that referenced this issue Aug 25, 2016

fix(mysql): adds MySQL 5.7 compatibility
Removes `ONLY_FULL_GROUP_BY` from MySQL's session `sql_mode`, as it's
Incompatible with our queries.

Fixes #8121
@mrclay

This comment has been minimized.

Show comment
Hide comment
@mrclay

mrclay Aug 25, 2016

Member

Server-level fix (does not persist restart):
SET GLOBAL sql_mode=(SELECT REPLACE(@@GLOBAL.sql_mode, 'ONLY_FULL_GROUP_BY', ''));

Member

mrclay commented Aug 25, 2016

Server-level fix (does not persist restart):
SET GLOBAL sql_mode=(SELECT REPLACE(@@GLOBAL.sql_mode, 'ONLY_FULL_GROUP_BY', ''));

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment