Skip to content

GitLab

  • Projects
  • Groups
  • Snippets
  • Help
    • Loading...
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in
C
Core
  • Project overview
    • Project overview
    • Details
    • Activity
    • Releases
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
  • Issues 926
    • Issues 926
    • List
    • Boards
    • Labels
    • Service Desk
    • Milestones
  • Operations
    • Operations
    • Incidents
  • Analytics
    • Analytics
    • Repository
    • Value Stream
  • Wiki
    • Wiki
  • Snippets
    • Snippets
  • Members
    • Members
  • Collapse sidebar
  • Activity
  • Graph
  • Create a new issue
  • Commits
  • Issue Boards
  • Development
  • Core
  • Issues
  • #1141

Closed
Open
Opened Jul 22, 2019 by JoeMurray@JoeMurrayDeveloper

Remove deprecated SQL_CALC_FOUND_ROWS

MySQL has deprecated SQL_CALC_FOUND_ROWS https://dev.mysql.com/doc/refman/8.0/en/information-functions.html:

The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version. As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows.

We should refactor to remove it from core in the following places:

$ grep -R SQL_CALC_FOUND_ROWS ./*

./CRM/Core/BAO/CustomValueTable.php:      $query = "SELECT SQL_CALC_FOUND_ROWS id, " . implode(', ', $clauses) . " FROM $tableName WHERE entity_id = $entityID {$orderBy} {$limit}";
./CRM/Core/BAO/PrevNextCache.php:SELECT SQL_CALC_FOUND_ROWS {$selectString}
./CRM/Contribute/BAO/ContributionSoft.php:    SELECT SQL_CALC_FOUND_ROWS ccs.id, ccs.amount as amount,
./CRM/Case/BAO/Case.php:           SELECT SQL_CALC_FOUND_ROWS COUNT(ca.id) AS ismultiple,
./CRM/Report/Form/Contribute/Sybunt.php:      $getContacts = "SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from} {$this->_where} GROUP BY {$this->_aliases['civicrm_contact']}.id {$this->_limit}";
./CRM/Report/Form/Contribute/Lybunt.php:      SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from}
./CRM/Report/Form/Contribute/Lybunt.php:      $sql = str_replace('SQL_CALC_FOUND_ROWS', '', $sql);
./CRM/Report/Form/Contribute/Lybunt.php:      $sql = "SELECT SQL_CALC_FOUND_ROWS  * FROM ( $sql ) as inner_query {$this->_orderBy} $this->_limit";
./CRM/Report/Form/Contribute/History.php:    $getContacts = "SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid, SUM({$this->_aliases['civicrm_contribution']}.total_amount) as civicrm_contribution_total_amount_sum {$this->_from} {$this->_where} {$addWhere} GROUP BY {$this->_aliases['civicrm_contact']}.id {$this->_having} {$this->_limit}";
./CRM/Report/Form/Contribute/Detail.php:      return "SELECT SQL_CALC_FOUND_ROWS * FROM {$this->temporaryTables['civireport_contribution_detail_temp3']['name']} $this->_orderBy $this->_limit";
./CRM/Report/Form/Contribute/Detail.php:      $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
./CRM/Report/Form/Contribute/TopDonor.php:      $this->_select = preg_replace('/SELECT/', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select, 1);
./CRM/Report/Form.php:    if (stristr($this->_select, 'SQL_CALC_FOUND_ROWS')) {
./CRM/Report/Form.php:      $select = str_ireplace('SELECT SQL_CALC_FOUND_ROWS ', 'SELECT ', $this->_select);
./CRM/Report/Form.php:      $this->_select = preg_replace('/SELECT(\s+SQL_CALC_FOUND_ROWS)?\s+/i', 'SELECT SQL_CALC_FOUND_ROWS ', $this->_select);
./CRM/Report/Form.php:      $select = preg_replace('/SELECT(\s+SQL_CALC_FOUND_ROWS)?\s+/i', $select, $this->_select);
./tests/phpunit/api/v3/ReportTemplateTest.php:      SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as cid  FROM civicrm_contact contact_civireport    INNER JOIN civicrm_contribution contribution_civireport USE index (received_date) ON contribution_civireport.contact_id = contact_civireport.id
To upload designs, you'll need to enable LFS and have admin enable hashed storage. More information
Assignee
Assign to
None
Milestone
None
Assign milestone
Time tracking
None
Due date
None
Reference: dev/core#1141