Problem

With limit set to 1, I'm getting a SQL error when trying to login when an existing session exists.

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'drupal.s.timestamp' which is not in SELECT list; this is incompatible with DISTINCT: SELECT DISTINCT s.sid AS sid FROM {sessions} s WHERE (s.uid = :db_condition_placeholder_0) ORDER BY timestamp ASC LIMIT 1 OFFSET 0; Array ( [:db_condition_placeholder_0] => 5 ) in Drupal\session_limit\Services\SessionLimit->_onSessionCollision__DropOldest() (line 272 of [...]/modules/contrib/session_limit/src/Services/SessionLimit.php).
Drupal\Core\Database\Statement->execute(Array, Array) (Line: 610)
Drupal\Core\Database\Connection->query('SELECT DISTINCT s.sid AS sid
FROM 
{sessions} s
WHERE  (s.uid = :db_condition_placeholder_0) 
ORDER BY timestamp ASC
LIMIT 1 OFFSET 0', Array, Array) (Line: 81)
Drupal\Core\Database\Driver\mysql\Connection->query('SELECT DISTINCT s.sid AS sid
FROM 
{sessions} s
WHERE  (s.uid = :db_condition_placeholder_0) 
ORDER BY timestamp ASC
LIMIT 1 OFFSET 0', Array, Array) (Line: 493)
Drupal\Core\Database\Query\Select->execute() (Line: 272)
Drupal\session_limit\Services\SessionLimit->_onSessionCollision__DropOldest(Object) (Line: 213)
Drupal\session_limit\Services\SessionLimit->onSessionCollision(Object, 'session_limit.collision', Object) (Line: 111)
Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher->dispatch('session_limit.collision', Object) (Line: 143)
Drupal\session_limit\Services\SessionLimit->onKernelRequest(Object, 'kernel.request', Object) (Line: 111)
Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher->dispatch('kernel.request', Object) (Line: 125)
Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object, 1) (Line: 64)
Symfony\Component\HttpKernel\HttpKernel->handle(Object, 1, 1) (Line: 57)
Drupal\Core\StackMiddleware\Session->handle(Object, 1, 1) (Line: 47)
Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object, 1, 1) (Line: 98)
Drupal\page_cache\StackMiddleware\PageCache->pass(Object, 1, 1) (Line: 77)
Drupal\page_cache\StackMiddleware\PageCache->handle(Object, 1, 1) (Line: 50)
Drupal\ban\BanMiddleware->handle(Object, 1, 1) (Line: 47)
Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object, 1, 1) (Line: 50)
Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object, 1, 1) (Line: 23)
Stack\StackedHttpKernel->handle(Object, 1, 1) (Line: 628)
Drupal\Core\DrupalKernel->handle(Object) (Line: 19)

Proposed resolution

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

cashwilliams created an issue. See original summary.

cashwilliams’s picture

I'm not sure if just adding the timestamp to the fields list is the correct fix or not.

nicrodgers’s picture

I haven't been able to replicate this. Is is still an issue?

shrop’s picture

Description

This is still an issue, but doesn't appear if you have already used this patch from issue 2787013.

I tested the patch that @cashwilliams posted in this issue with a clean 8.x-1.x branch and it does resolve the PHP error.

Testing Instructions

  1. Checkout session_limit 8.x-1.x
  2. Enable session_limit
  3. Configure session_limit with "Default maximum number of active sessions" set to 1 and "Automatically drop the oldest sessions."
  4. Open two separate browser sessions
  5. Login to browser session 1 with a non-admin test user
  6. Login to browser session 2 with the same non-admin test user
  7. In browser session 1, navigate to another route and you will see the reported error in Drupal Watchdog

Next Steps

  • Additional review as needed
  • Commit patch to 8.x-1.x
shrop’s picture

Status: Active » Reviewed & tested by the community
jurgenhaas’s picture

For the records, I was able to reproduce this problem but only on MySQL server version 5.7.19 on Ubuntu 16.04. The exact same installation inside a docker container with MariaDB version 10.1.21 this problem does not occur.

The patch above looks great and it does solve the problem with my installation on MySQL as well.

darksnow’s picture

Status: Reviewed & tested by the community » Closed (duplicate)

Since the other patch fixes the issue, I'm closing this as a duplicate of issue 2787013

nonom’s picture

The patch is not enough for 8.5, you need to replace

 $result = $this->database->select('sessions', 's')
        ->distinct()
        + ->fields('s', array('sid','timestamp'))
        - ->fields('s', array('sid'))
        ->condition('s.uid', $event->getAccount()->id())
        ->orderBy('timestamp', 'ASC')
        ->range(0, $limit)
        ->execute();

Credits to Chechu

khaled.zaidan’s picture

Status: Closed (duplicate) » Active

Re-opening as the issue is still happening, and the patch in the other ticket doesn't solve this.

I'm on the latest release of the module (8-beta1).

Error:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'csp_uat.s.timestamp' which is not in SELECT list; this is incompatible with DISTINCT: SELECT DISTINCT s.sid AS sid FROM {sessions} s WHERE s.uid = :db_condition_placeholder_0 ORDER BY timestamp ASC LIMIT 1 OFFSET 0; Array ( [:db_condition_placeholder_0] => 14 ) in Drupal\session_limit\Services\SessionLimit->_onSessionCollision__DropOldest() (line 272 of [redacted]/www/web/modules/contrib/session_limit/src/Services/SessionLimit.php).
idiaz.roncero’s picture

+1.

I'm having the same issue on D8 8.6.2 and Session Limit 8.x-1.0-beta1

johnennew’s picture

Committed fix to 8.x-1.x branch

johnennew’s picture

Status: Active » Fixed

This is fixed.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.