CiviCRM Core issueshttps://lab.civicrm.org/dev/core/-/issues2023-02-02T05:03:32Zhttps://lab.civicrm.org/dev/core/-/issues/1538CRM_Export_BAO_ExporTest regularly failing on MySQL 82023-02-02T05:03:32ZseamusleeCRM_Export_BAO_ExporTest regularly failing on MySQL 8Overview
----------------------------------------
When running the CRM_Export_BAO_ExportTest it is regularly failing MySQL 8
Reproduction steps
----------------------------------------
1. Install MySQL 8.
1. Create Buildkit environment...Overview
----------------------------------------
When running the CRM_Export_BAO_ExportTest it is regularly failing MySQL 8
Reproduction steps
----------------------------------------
1. Install MySQL 8.
1. Create Buildkit environment.
1. Run the CRM_Export_BAO_ExportTest class.
Current behaviour
----------------------------------------
It appears that this query
```
SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.sort_name as `sort_name`, contact_a.display_name as `display_name`, civicrm_address.id as address_id, contact_a.do_not_email as `do_not_email`, contact_a.do_not_phone as `do_not_phone`, contact_a.do_not_mail as `do_not_mail`, contact_a.do_not_sms as `do_not_sms`, contact_a.do_not_trade as `do_not_trade`, contact_a.is_opt_out as `is_opt_out`, contact_a.legal_identifier as `legal_identifier`, contact_a.external_identifier as `external_identifier`, contact_a.nick_name as `nick_name`, contact_a.legal_name as `legal_name`, contact_a.image_URL as `image_URL`, contact_a.preferred_communication_method as `preferred_communication_method`, contact_a.preferred_language as `preferred_language`, contact_a.preferred_mail_format as `preferred_mail_format`, contact_a.hash as `hash`, contact_a.source as `contact_source`, contact_a.first_name as `first_name`, contact_a.middle_name as `middle_name`, contact_a.last_name as `last_name`, contact_a.prefix_id as `prefix_id`, contact_a.suffix_id as `suffix_id`, contact_a.formal_title as `formal_title`, contact_a.communication_style_id as `communication_style_id`, contact_a.email_greeting_id as email_greeting_id, contact_a.postal_greeting_id as postal_greeting_id, contact_a.addressee_id as addressee_id, contact_a.job_title as `job_title`, contact_a.gender_id as `gender_id`, contact_a.birth_date as `birth_date`, contact_a.is_deceased as `is_deceased`, contact_a.deceased_date as `deceased_date`, contact_a.household_name as `household_name`, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, contact_a.sic_code as `sic_code`, contact_a.user_unique_id as `user_unique_id`, contact_a.employer_id as `current_employer_id`, contact_a.is_deleted as `contact_is_deleted`, contact_a.created_date as `created_date`, contact_a.modified_date as `modified_date`, contact_a.addressee_display as addressee_display, contact_a.addressee_custom as addressee_custom, contact_a.email_greeting_display as email_greeting_display, contact_a.email_greeting_custom as email_greeting_custom, contact_a.postal_greeting_display as postal_greeting_display, contact_a.postal_greeting_custom as postal_greeting_custom, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer, civicrm_location_type.id as location_type_id, civicrm_location_type.name as `location_type`, civicrm_address.street_address as `street_address`, civicrm_address.street_number as `street_number`, civicrm_address.street_number_suffix as `street_number_suffix`, civicrm_address.street_name as `street_name`, civicrm_address.street_unit as `street_unit`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.supplemental_address_3 as `supplemental_address_3`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, civicrm_address.manual_geo_code as `manual_geo_code`, civicrm_address.name as `address_name`, civicrm_address.master_id as `master_id`, civicrm_address.county_id as county_id, civicrm_address.state_province_id as state_province_id, civicrm_address.country_id as country_id, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as `phone_type_id`, civicrm_phone.phone as `phone`, civicrm_phone.phone_ext as `phone_ext`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_email.is_bulkmail as `is_bulkmail`, civicrm_email.signature_text as `signature_text`, civicrm_email.signature_html as `signature_html`, civicrm_im.id as im_id, civicrm_im.provider_id as `im_provider`, civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_openid.id as openid_id, civicrm_openid.openid as `openid`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`, civicrm_website.id as website_id, civicrm_website.url as `url`,
CONCAT_WS(',',
GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
)
as `groups`, GROUP_CONCAT(DISTINCT(civicrm_tag.name)) as tags, GROUP_CONCAT(DISTINCT(civicrm_note.note)) as notes FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id ) LEFT JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id ) LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id ) LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id ) LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id ) LEFT JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id ) LEFT JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id LEFT JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id LEFT JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id LEFT JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND civicrm_entity_tag.entity_id = contact_a.id ) LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id ) LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id LEFT JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id LEFT JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id WHERE (contact_a.is_deleted = 0) AND contact_a.is_deleted != 1 AND contact_a.id IN (3,4,5) GROUP BY contact_a.id
```
gives a different result when running on MySQL 8 to MySQL 5. 7 in that it gets the address_id 2 not address_id 1 for the first contact.
ping @eileenhttps://lab.civicrm.org/dev/core/-/issues/1480Ubuntu 19.10 MySQL 8 and 'grouping' keyword in OptionGroup.php2020-01-02T00:07:08Zthoni56Ubuntu 19.10 MySQL 8 and 'grouping' keyword in OptionGroup.phpCiviCRM 5.19.3
Joomla 3.9.11
Ubuntu 19.10
Updating Ubuntu from 19.04 to 19.10 will pull in MySQL v8. It is a known issue (https://lab.civicrm.org/dev/core/issues/392) that `grouping` now became a reserved word which caused some problems...CiviCRM 5.19.3
Joomla 3.9.11
Ubuntu 19.10
Updating Ubuntu from 19.04 to 19.10 will pull in MySQL v8. It is a known issue (https://lab.civicrm.org/dev/core/issues/392) that `grouping` now became a reserved word which caused some problems.
I found an instance of this in `OptionGroup.php`. On line 124 (https://lab.civicrm.org/dev/core/blob/master/CRM/Core/OptionGroup.php#L124) :
SELECT v.{$labelColumnName} as {$labelColumnName} ,v.{$keyColumnName} as value, v.grouping as grouping
This caused our whole site to `DB Error syntax error` since the first page is an event listing.
I did not have the time to figure out if there actually was some further use of that identifier, since the full query is built in run-time. Seems like an easy fix anyway.
Just single-quoting the grouping-word on that line brought it back for us. And a quick test seems to indicate that the site works ok.
I'm volunteering to staying on MySQL 8 for now to flesh out any further issues. Worst case, I'll downgrade MySQL.5.22.0seamusleeseamusleehttps://lab.civicrm.org/dev/core/-/issues/1250CiviDiscount override the amount regardless the number of terms for membership2021-09-14T13:49:28Zagileware_pengyiCiviDiscount override the amount regardless the number of terms for membershipSimilar to #860 , the amount is overridden regardless of the terms. But the expired date is right.
## Step to reproduce:
- create a discount code for membership
- create or renew membership with credit card
- set the terms to two or mor...Similar to #860 , the amount is overridden regardless of the terms. But the expired date is right.
## Step to reproduce:
- create a discount code for membership
- create or renew membership with credit card
- set the terms to two or more
- submit and check the created contribution and membership
For example, the membership fee is $100 per term. We submit the form with 3 terms. The discount is 25% off.
## Expected results:
- contribution amount is adjusted correctly: 100 * 3 * 75% = 225
- membership expired date matches the terms: 3 terms
## Actual results:
- contribution amount is one term price adjusted by the discount: 75
- membership expired date matches the terms: 3 terms
Agileware ref: CIVICRM-1307https://lab.civicrm.org/dev/core/-/issues/1145Document upgrading to MySQL82020-05-15T01:39:39ZJoeMurrayDocument upgrading to MySQL8We should document how upgraders will have to deal with hassle of changes to MySQL user password library (https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password) even though this doesn't...We should document how upgraders will have to deal with hassle of changes to MySQL user password library (https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password) even though this doesn't affect our code.JoeMurrayJoeMurrayhttps://lab.civicrm.org/dev/core/-/issues/1144Support MySQL8 in test infrastructure2020-01-08T15:38:31ZJoeMurraySupport MySQL8 in test infrastructureThe testing infrastructure will be enhanced to support testing of versions of dependencies that are on the edge of support (see https://lab.civicrm.org/dev/core/issues/1142).
Setting up testing for MySQL is the focus of this issue.
In...The testing infrastructure will be enhanced to support testing of versions of dependencies that are on the edge of support (see https://lab.civicrm.org/dev/core/issues/1142).
Setting up testing for MySQL is the focus of this issue.
In particular, We may need to adjust some of our test infrastructure for PHP <7.1.16 or PHP 7.2 less than 7.2.4 as per instructions at http://php.net/manual/en/ref.pdo-mysql.php .Monish DebMonish Debhttps://lab.civicrm.org/dev/core/-/issues/1142Upgrade test infrastructure to support 'edge' versions of dependencies like M...2020-01-08T01:52:11ZJoeMurrayUpgrade test infrastructure to support 'edge' versions of dependencies like MySQL8@totten wrote:
> we currently check releases using a matrix with `min` and `max` columns
>
> if we expect civicrm to be compatible already (and/or that updates will be quick and straight-forward), then we can use KISS approach and just...@totten wrote:
> we currently check releases using a matrix with `min` and `max` columns
>
> if we expect civicrm to be compatible already (and/or that updates will be quick and straight-forward), then we can use KISS approach and just bump up max (`mysql57` => `mysql80`)
>
> otoh, if the process is going to take longer, then we should add a new column (`min`, `max`, `edge`)
>
> (note: adding an `edge` column could also be useful wrt to phasing-in support for `php73`)
>
> ... it would mean that reports of errors for infrastructure that is on the edge of getting support are not treated as blockers, and are identifiable as edge errors
Once an edge technology dependency like MySQL 8 or PHP 7.3 are deemed ready to be declared supported, the max column will change, and the edge column will change to NULL or perhaps the next higher version if people are prepared to start work on that.
This issue is focused on adding edge support to the matrix through a new column, and everything that entails in the build infrastructure and error reporting.
In order to do that, MySQL8 happens to be the first edge version to be incorporated. Issues specific to getting MySQL8.0 testing working on infrastructure will be managed at https://lab.civicrm.org/dev/core/issues/1144. Issues regarding MySQL8 compatibility are being managed via the meta issue https://lab.civicrm.org/dev/core/issues/392.
PHP73 is expected to be the next edge dependency.5.23.0tottentottenhttps://lab.civicrm.org/dev/core/-/issues/1141Remove deprecated SQL_CALC_FOUND_ROWS2022-11-30T05:03:30ZJoeMurrayRemove deprecated SQL_CALC_FOUND_ROWSMySQL 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 r...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 ./*
```php
./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
```
>>>Monish DebMonish Debhttps://lab.civicrm.org/dev/core/-/issues/392Support MySQL 8.0 now that it is GA2021-04-01T01:52:03ZJoeMurraySupport MySQL 8.0 now that it is GA# Overview
MySQL 8.0 went General Availability on April 19, 2018. CiviCRM's aim is to support the latest GA for required infrastructure (MySQL, PHP) as soon as feasible.
In reviewing removed features that cause incompatibilities (htt...# Overview
MySQL 8.0 went General Availability on April 19, 2018. CiviCRM's aim is to support the latest GA for required infrastructure (MySQL, PHP) as soon as feasible.
In reviewing removed features that cause incompatibilities (https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html) I noticed a couple of issues that definitely require changes, one that might cause syntax errors that are easily fixed when found, and a fourth that we may want to change to improve our functionality and get current.
This is a meta issue for tracking what's needed for MySQL8. I have added a MySQL8 flag to lab for the moment to help in tracking. We can delete that flag after this issue is deemed complete.
# Related issue: Upgrade test infrastructure to enable testing against a version on the edge of being officially supported: https://lab.civicrm.org/dev/core/issues/1142
# Issue: Field Names now Reserved Words https://lab.civicrm.org/dev/core/issues/1143
# Testing issue: dependence on PHP version for MySQL authentication ~~https://lab.civicrm.org/dev/core/issues/1144~~
# Document dealing with changes to MySQL user password library https://lab.civicrm.org/dev/core/issues/1145
# Possible issue: Sort order on GROUP BY clauses
~~I don't think we have any code that tries to specify sort order on GROUP BY fields, but if so we will need to change to add an ORDER BY clause with the sort order. I think we should just try running tests on MySQL 8.0 and using it manually to determine locations in code with this issue, and play whack-a-mole on anything missed.~~
# Nice to have: Upgrade our text fields to support all utf8 characters https://lab.civicrm.org/dev/core/issues/339