Problem/Motivation

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-9.html shows that MySQL 5.7.9 was the first generally available release of MySQL 5.7

Unfortunately core has at least two fatal errors with it:

#2616282: error creating migrate_map table with mysql 5.7.9
#2388139: Installation error MYSQL Primary key is null with MySQL 5.7 on block_content_revision

Proposed resolution

Fix them, and add a MySQL 5.7 environment to DrupalCI.

If this issue isn't fixed in time for 8.0.0, we should add a note to the release notes and drupal.org/requirements pointing out the incompatibility.

Remaining tasks

User interface changes

API changes

Data model changes

Issue fork drupal-2616488

Command icon Show commands

Start within a Git clone of the project using the version control instructions.

Or, if you do not have SSH keys set up on git.drupalcode.org:

About issue forks
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

catch created an issue. See original summary.

steinmb’s picture

steinmb CreditAttribution: steinmb as a volunteer commented

From my understanding goes this also for MariaDB 10.1.x https://mariadb.com/kb/en/mariadb/system-variable-differences-between-ma...

We might also have a D7 problem. My server just plainly refused to start on 10.1.8 in homebrew failing with: 

2015-11-16 21:07:17 140735136096256 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 44708035878
2015-11-16 21:07:17 140735136096256 [Note] InnoDB: 128 rollback segment(s) are active.
2015-11-16 21:07:17 140735136096256 [Note] InnoDB: Waiting for purge to start
2015-11-16 21:07:17 7000008b1000  InnoDB: Assertion failure in thread 123145311424512 in file btr0cur.cc line 5341
InnoDB: Failing assertion: rb_ctx != RB_NONE
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
151116 21:07:17 [ERROR] mysqld got signal 6

Worked OK when I stepped back to 10.0.x.

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch commented
Title: [meta] MySQL 5.7 support » [meta] MySQL 5.7 / MariaDb 10.0.* support

Updating issue title, 10.1.8 was the first stable for 10.1, and October 17th.

steinmb’s picture

steinmb CreditAttribution: steinmb as a volunteer commented
Title: [meta] MySQL 5.7 / MariaDb 10.0.* support » [meta] MySQL 5.7 / MariaDb 10.1.* support

Think It should be 10.1

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch commented

Yes, yes it should.

It looks like the block / contact errors might not be 5.7 incompatibilities in core - marked both 'needs more info'.

If that's the case, then only {migrate_map} is incompatible, and given migrate is experimental that wouldn't in itself be an issue.

I opened #2616724: Warn when trying to create a database table with a NOT NULL => FALSE primary key to validate this in PHP, which would help contrib/custom database tables be 5.7 compatible.

If it's really only migrate_map that's actually a problem in core, then we can probably say 'not yet fully tested on MySQL 5.7' as opposed to 'does not install on 5.7' which would be a less bad d.o/requirements/release notes note.

alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
CreditAttribution: alexpott at Chapter Three commented

#2388139: Installation error MYSQL Primary key is null with MySQL 5.7 on block_content_revision has been fixed.

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch commented
Priority: Critical » Major

Committed #2616282: error creating migrate_map table with mysql 5.7.9 .

Leaving this open for tracking the DrupalCI environment, and possibly doing our own validation in the database layer, but as far as we know we're compatible now - so moving to major.

Could still use a d.o/requirements update to indicate we don't test on MySQL 5.7 yet.

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch commented
Category: Bug report » Task
Status: Active » Postponed
Issue tags: +8.0.1 target

Tagging 8.0.1 target, but also postponing on #2616490: MySQL 5.7 environment - hopefully nothing to do here, and we can just close this once we get a clean 5.7 run on DrupalCI.

joelpittet’s picture

joelpittet
Primary language English
Location Vancouver
CreditAttribution: joelpittet as a volunteer commented
Component: base system » database system
Category: Task » Plan
Status: Postponed » Active

It's a meta it doesn't need to be postponed, just needs it's children finished up.

Version: 8.0.x-dev » 8.1.x-dev

Drupal 8.0.6 was released on April 6 and is the final bugfix release for the Drupal 8.0.x series. Drupal 8.0.x will not receive any further development aside from security fixes. Drupal 8.1.0-rc1 is now available and sites should prepare to update to 8.1.0.

Bug reports should be targeted against the 8.1.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.1.x-dev » 8.2.x-dev

Drupal 8.1.9 was released on September 7 and is the final bugfix release for the Drupal 8.1.x series. Drupal 8.1.x will not receive any further development aside from security fixes. Drupal 8.2.0-rc1 is now available and sites should prepare to upgrade to 8.2.0.

Bug reports should be targeted against the 8.2.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.6 was released on February 1, 2017 and is the final full bugfix release for the Drupal 8.2.x series. Drupal 8.2.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.3.0 on April 5, 2017. (Drupal 8.3.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.3.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

pwolanin’s picture

pwolanin CreditAttribution: pwolanin as a volunteer and at SciShield commented

It seems on MysSQL 5.7 it's possible to create a view that fatals on SQL when you add a group by and an order by and the field used for ordering is not in the grouping.

Example error:

Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Buildings[buildings]: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'circle_test.node_field_data.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT node__field_building_id.field_building_id_value AS node__field_building_id_field_building_id_value, node_field_data_node__og_audience.title AS node_field_data_node__og_audience_title, MIN(node_field_data.nid) AS nid, MIN(node_field_data_node__og_audience.nid) AS node_field_data_node__og_audience_nid FROM {node_field_data} node_field_data LEFT JOIN {node__og_audience} node__og_audience ON node_field_data.nid = node__og_audience.entity_id AND (node__og_audience.deleted = :views_join_condition_0 AND node__og_audience.langcode = node_field_data.langcode) LEFT JOIN {node_field_data} node_field_data_node__og_audience ON node__og_audience.og_audience_target_id = node_field_data_node__og_audience.nid AND node_field_data_node__og_audience.type = :views_join_condition_2 LEFT JOIN {node__field_building_id} node__field_building_id ON node_field_data.nid = node__field_building_id.entity_id AND (node__field_building_id.deleted = :views_join_condition_3 AND node__field_building_id.langcode = node_field_data.langcode) LEFT JOIN {node__field_building_usage} node__field_building_usage ON node_field_data.nid = node__field_building_usage.entity_id AND (node__field_building_usage.deleted = :views_join_condition_5 AND node__field_building_usage.langcode = node_field_data.langcode) WHERE (node_field_data.status = :db_condition_placeholder_7) AND (node_field_data.type IN (:db_condition_placeholder_8)) GROUP BY node__field_building_id_field_building_id_value, node_field_data_node__og_audience_title ORDER BY node_field_data.title ASC LIMIT 25 OFFSET 0; Array ( [:db_condition_placeholder_7] => 1 [:db_condition_placeholder_8] => building [:views_join_condition_0] => 0 [:views_join_condition_2] => campus [:views_join_condition_3] => 0 [:views_join_condition_5] => 0 ) in Drupal\views\Plugin\views\query\Sql->execute() (line 1488 of core/modules/views/src/Plugin/views/query/Sql.php).

Note we are using the patch from #2662548: Support Plugins for Views Aggregate to make it a little easer to do grouping

possible pgsql driver already has some code to try to handle this - mradcliff points me to:

https://api.drupal.org/api/drupal/core!lib!Drupal!Core!Database!Driver!p...

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.6 was released on August 2, 2017 and is the final full bugfix release for the Drupal 8.3.x series. Drupal 8.3.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.4.0 on October 4, 2017. (Drupal 8.4.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.4.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.4 was released on January 3, 2018 and is the final full bugfix release for the Drupal 8.4.x series. Drupal 8.4.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.5.0 on March 7, 2018. (Drupal 8.5.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.5.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Mixologic’s picture

Mixologic
He/Him/His
Primary language English
Location Portland, OR
CreditAttribution: Mixologic at Drupal Association commented

I added the mysql 5.7 environment as an option to test with to drupalci today. Seems to be failing a couple of tests:

https://www.drupal.org/pift-ci-job/1011706

I would really really love it if we could get this passing, and use it for the default mysql environment from now on. 5.7 is pretty common, and also, there is a bug in mysql that impacts one of the kernel tests that makes it take 5 min on 5.5, vs .2 sec on 5.7.

The 5.7 +php 7.1 tests seem to run in about 38 minutes, which is another 11 minutes of time saved off of our test runs if we could get there.

tacituseu’s picture

tacituseu CreditAttribution: tacituseu commented
Related issues: +#2394069: Enforce NOT NULL on columns with primary key, +#2616724: Warn when trying to create a database table with a NOT NULL => FALSE primary key
pounard’s picture

pounard
Primary language French
CreditAttribution: pounard commented

MySQL 8 testing would also be a nice to have. In a few projects, we had SQL queries exploding after testing them on MySQL 8.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.6 was released on August 1, 2018 and is the final bugfix release for the Drupal 8.5.x series. Drupal 8.5.x will not receive any further development aside from security fixes. Sites should prepare to update to 8.6.0 on September 5, 2018. (Drupal 8.6.0-rc1 is available for testing.)

Bug reports should be targeted against the 8.6.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

andypost’s picture

andypost
he/him
Primary language Russian
CreditAttribution: andypost as a volunteer and at Skilld commented
Version: 8.6.x-dev » 8.9.x-dev

Guess it should be about mysql8-kinds

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch at Third and Grove commented

We have a MySQL 8 testing environment already, does this need to be open still?

Version: 8.9.x-dev » 9.1.x-dev

Drupal 8.9.0-beta1 was released on March 20, 2020. 8.9.x is the final, long-term support (LTS) minor release of Drupal 8, which means new developments and disruptive changes should now be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

joseph.olstad’s picture

joseph.olstad
Primary language French
CreditAttribution: joseph.olstad commented

Got hit with this issue today

MySQL 5.7.30

Steps to reproduce, very easy:

Drupal 8.8.8

vanilla install
add a bunch of exposed filters to /admin/content view
click advanced options in /admin/content view
select query settings, enable 'distinct' checkmark

error message:

ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'd8vanilla.node_field_data.changed' which is not in SELECT list; this is incompatible with DISTINCT

WORKAROUND:

install MySQL or MariaDB 5.6 and use 5.6 instead

OR add configuration as described above in previous comments.

joseph.olstad’s picture

joseph.olstad
Primary language French
CreditAttribution: joseph.olstad commented

likely need to expand test coverage to handle 'distinct' query option for the /admin/content view.

Version: 9.1.x-dev » 9.2.x-dev

Drupal 9.1.0-alpha1 will be released the week of October 19, 2020, which means new developments and disruptive changes should now be targeted for the 9.2.x-dev branch. For more information see the Drupal 9 minor version schedule and the Allowed changes during the Drupal 9 release cycle.

weseze’s picture

weseze CreditAttribution: weseze as a volunteer commented

I can confirm what @joseph.olstad has documented. I noticed the same sort of issue after a MySQL update from 5.6.x to 5.7.x, 100 D8 sites broken...

Seems to me that both Drupal 8 and 9 (the most recent releases) are not compatible with MySQL 5.7.

joseph.olstad’s picture

joseph.olstad
Primary language French
CreditAttribution: joseph.olstad commented

Ironically I also ran into a similar issue with Postgres 11 when using the 'distinct' query option after upgrading to Drupal 9.0.12 from Drupal 8.9.x

disabling the 'distinct' query option fixed it for Postgres 11 . With that said, ya MySQL 5.7 seems like an odd ball implementation of MySQL. I haven't noticed this issue with MariaDB.

there's a workaround for MySQL 5.7 which is to modify the settings.php with some strange options but I would recommend against it and recommend using MySQL 8 or MariaDB instead of this version of MySQL.

Version: 9.2.x-dev » 9.3.x-dev

Drupal 9.2.0-alpha1 will be released the week of May 3, 2021, which means new developments and disruptive changes should now be targeted for the 9.3.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.0-rc1 was released on November 26, 2021, which means new developments and disruptive changes should now be targeted for the 9.4.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.0-alpha1 was released on May 6, 2022, which means new developments and disruptive changes should now be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 10.1.x-dev

Drupal 9.5.0-beta2 and Drupal 10.0.0-beta2 were released on September 29, 2022, which means new developments and disruptive changes should now be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

quietone’s picture

quietone CreditAttribution: quietone at PreviousNext commented
Issue tags: -D8 upgrade path +D9 upgrade path

Changing tag

Version: 10.1.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch, which currently accepts only minor-version allowed changes. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

quietone’s picture

quietone CreditAttribution: quietone at PreviousNext commented
Status: Active » Closed (outdated)

We do have the testing environments asked for in the Issue Summary. I think it is time to close this.

lcatlett made their first commit to this issue’s fork.

lcatlett changed the visibility of the branch 2616488-meta-mysql-5.7 to hidden.

joseph.olstad’s picture

joseph.olstad
Primary language French
CreditAttribution: joseph.olstad commented

There's a somewhat related patch available that could inspire fixes for ERROR 3065 (HY000): Expression #1 of ORDER BY clause

#3221622: Unable to sort by current moderation state. Column not found

If the query uses DISTINCT we need to add column to the query.

See patch for ideas.

https://www.drupal.org/files/issues/2022-04-06/content_moderation-fix-Mo...