Problem/Motivation

Part of #2616488: [meta] MySQL 5.7 / MariaDb 10.1.* support.

MySQL < 5.7 silently corrects tables to have a NOT NULL primary key if it's specified as NOT NULL => FALSE.

5.7 throws an error.

We should consider throwing a helpful exception ourselves before attempting to create the table, so that people write code compatible with older versions of MySQL.

Proposed resolution

Check the primary key and ensure all columns have NOT NULL definitions.

Remaining tasks

User interface changes

API changes

Data model changes

CommentFileSizeAuthor
#20 interdiff-20.txt3.84 KBamateescu
#20 2616724-20.patch14.9 KBamateescu
#19 interdiff-19.txt647 bytesamateescu
#19 2616724-19.patch12.27 KBamateescu
#14 2616724-14-pass.patch11.91 KBamateescu
#14 2616724-14-fail.patch7.46 KBamateescu
#13 interdiff-2616724-12-13.txt3.31 KBdaffie
#13 2616724-13.patch4.45 KBdaffie
#12 interdiff-2616724-11-12.txt989 bytesdaffie
#12 2616724-12.patch1.42 KBdaffie
#11 2616724-11.patch761 bytesdaffie

Comments

catch created an issue. See original summary.

catch’s picture

catch
he/him
Primary language English
commented
Title: Warn when trying to create a database table with a NULL primary key » Warn when trying to create a database table with a NOT NULL => FALSE primary key
effulgentsia’s picture

effulgentsia commented
Parent issue: » #2616488: [meta] MySQL 5.7 / MariaDb 10.1.* support

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.

daffie’s picture

daffie commented
Related issues: +#1728688: Make serial SQL types consistent

Most if not all primary keys are of the serial type. Does anybody know if there are primary keys that are not of the serial type?

chx’s picture

chx commented

Edited: deleted. This was solely an api.drupal.org link but I have confidence in the good developers of Drupal core that they would find a way to get insulted even by that so I deleted it.

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.

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.

daffie’s picture

daffie commented
Version: 8.5.x-dev » 8.6.x-dev
Status: Active » Needs review
StatusFileSize
new 2616724-11.patch761 bytes

This patch should fix the testbot fail for the new MySQL 5.7 environment.

daffie’s picture

daffie commented
StatusFileSize
new 2616724-12.patch1.42 KB
new interdiff-2616724-11-12.txt989 bytes

Second try.

daffie’s picture

daffie commented
StatusFileSize
new 2616724-13.patch4.45 KB
new interdiff-2616724-12-13.txt3.31 KB

Third try.

amateescu’s picture

amateescu commented
StatusFileSize
new 2616724-14-fail.patch7.46 KB
new 2616724-14-pass.patch11.91 KB

I've been working on this as well from the angle of the initial scope, which is to throw our own db schema exception when a user tries to define a primary key without 'not null' => TRUE.

The "fail" patch shows the new exception being thrown, and the "pass" one includes the fixes from #13 :)

daffie’s picture

daffie commented
Status: Needs review » Needs work
Issue tags: +Needs tests

@amateescu: I like your idea with the added method to make sure that every primary key does have a allows null value. What I miss is tests where the thrown exception is caught.

amateescu’s picture

amateescu commented
Status: Needs work » Needs review
Issue tags: -Needs tests

The new exception is not meant to be caught by default, it's simply there to provide a friendlier message to module authors :)

The last submitted patch, 14: 2616724-14-fail.patch, failed testing. View results

daffie’s picture

daffie commented
Status: Needs review » Needs work
amateescu’s picture

amateescu commented
Status: Needs work » Needs review
StatusFileSize
new 2616724-19.patch12.27 KB
new interdiff-19.txt647 bytes

This should fix the sqlite driver by making its schema introspection return the proper 'not null' status of a field if it's part of the primary key.

amateescu’s picture

amateescu commented
StatusFileSize
new 2616724-20.patch14.9 KB
new interdiff-20.txt3.84 KB

@daffie, sorry, I didn't understand your request from #15 at first read :/ Here's full test coverage for the exception, which already caught a bug in the pgsql implementation.

daffie’s picture

daffie commented
Status: Needs review » Reviewed & tested by the community

You have made the same changes as I did to the SchemaTest class to fix the problem for MySQL 5.7.
I like your idea for throwing exception when a primary key field does not have the is not null option set.
You have added the by me requested tests.
I all looks good now to me.
For me it is RTBC.

Great work @amateescu.

  • catch committed 72cd973 on 8.6.x 
    Issue #2616724 by amateescu, daffie: Warn when trying to create a...
catch’s picture

catch
he/him
Primary language English
commented
Status: Reviewed & tested by the community » Fixed

Committed 72cd973 and pushed to 8.6.x. Thanks!

  • alexpott committed 5a7dc82 on 8.6.x 
    Issue #2616724 follow-up by alexpott: Warn when trying to create a...

Status: Fixed » Closed (fixed)

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