Problem/Motivation

From #3081144: Database primary keys can exceed maximum integer storeable (has actually occurred, in watchdog) becomes it clear that there is a bug with a PostgreSQL database that it fails when changing a serial field to a big serial field.

Proposed resolution

Fix the bug and add testing for changing a regular serial field to one that uses a bit regular field and add a fix for database driver for PostgreSQL.

Remaining tasks

Write patch.
Review patch.
Commit patch.

User interface changes

None

API changes

None

Data model changes

None

Release notes snippet

TBD

Issue fork drupal-3191391

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:

Comments

daffie created an issue. See original summary.

daffie’s picture

I have set the priority to critical, because it is blocking #3081144: Database primary keys can exceed maximum integer storeable (has actually occurred, in watchdog) and that issue is critical.

daffie’s picture

Issue tags: +Bug Smash Initiative

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

Drupal 8 is end-of-life as of November 17, 2021. There will not be further changes made to Drupal 8. Bugfixes are now made to the 9.3.x and higher branches only. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.2.x-dev » 9.3.x-dev
stephencamilo’s picture

Status: Active » Closed (won't fix)
hestenet’s picture

Status: Closed (won't fix) » Active

Reset issue status.

quietone’s picture

Issue tags: +blocker

Adding tag.

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

Drupal 9.3.15 was released on June 1st, 2022 and is the final full bugfix release for the Drupal 9.3.x series. Drupal 9.3.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.4.x-dev branch from now on, and new development or disruptive changes should 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.

xjm’s picture

xjm’s picture

The IS says there's a bug, but not what the bug is.

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

larowlan’s picture

larowlan’s picture

Version: 9.4.x-dev » 10.1.x-dev
Status: Active » Needs review
StatusFileSize
new1.1 KB
new2.15 KB

We discussed this in the bug smash meeting today - here's an untested stab at a test and some code

larowlan’s picture

Status: Needs review » Needs work

Probably need some asserts in that test 🤦‍♂️ serves me right for trying to write a patch and run a meeting.

_utsavsharma’s picture

StatusFileSize
new1000 bytes
new2.15 KB

FIxed CCF for 10.1.x of #14.
Please review.

larowlan’s picture

Status: Needs work » Needs review
StatusFileSize
new1.29 KB
new1.56 KB
new2.61 KB

Attempt to fix the test

andypost’s picture

Came from #3327520: Postgres error 500 on paths when supplied entity ID gte 2147483647 and lte 9223372036854776832 where I see that when serial column value passed as string a query will fail

needs-review-queue-bot’s picture

Status: Needs review » Needs work
StatusFileSize
new1013 bytes

The Needs Review Queue Bot tested this issue. It either no longer applies to Drupal core, or fails the Drupal core commit checks. Therefore, this issue status is now "Needs work".

Apart from a re-roll or rebase, this issue may need more work to address feedback in the issue or MR comments. To progress an issue, incorporate this feedback as part of the process of updating the issue. This helps other contributors to know what is outstanding.

Consult the Drupal Contributor Guide to find step-by-step guides for working with issues.

daffie’s picture

I think that we for PostgreSQL first need to remove the sequence, than change the field from an integer to a big integer field. After that is done can a new sequence be created. This can be done in the method changeField(). In that method we are already doing the same with constraints on the field. Just before removing the contraints on the field remove the sequence.

arantxio’s picture

Status: Needs work » Needs review
StatusFileSize
new3.99 KB
new1.89 KB

I've been working on this, and created a patch that seems to the pass all tests.

daffie’s picture

Status: Needs review » Needs work
  1. +++ b/core/modules/pgsql/src/Driver/Database/pgsql/Schema.php
    @@ -893,12 +893,11 @@ public function changeField($table, $field, $field_new, $spec, $new_keys = []) {
    +      'serial'=> 'int',
    

    Add a space between 'serial' and =>.

  2. +++ b/core/modules/pgsql/src/Driver/Database/pgsql/Schema.php
    @@ -1062,6 +1069,26 @@ public function extensionExists($name): bool {
    +  public function getSequenceName(string $table, string $column): ?string {
    

    This method does not have to be public. Just make it a protected method.

  3. +++ b/core/tests/Drupal/KernelTests/Core/Database/DriverSpecificSchemaTestBase.php
    @@ -1308,4 +1308,35 @@ public function testReservedKeywordsForNaming(): void {
    +    $this->schema->changeField($table_name, 'id', 'id', $new_specification, ['primary key' => ['id']]);
    

    We need to check that after we change the field to be a big serial field, that can store a big integer value. After we have tested that we should insert another row in the table and the value of the big serial should be the old big integer value +1.

arantxio’s picture

Status: Needs work » Needs review
StatusFileSize
new4.96 KB
new2.99 KB

Added the requested changes from @daffie in #22.

arantxio’s picture

StatusFileSize
new4.97 KB

My bad, didn't check the styling.

Status: Needs review » Needs work

The last submitted patch, 24: 3191391-24.patch, failed testing. View results

daffie’s picture

The testbot for MySQL returns an syntax error:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'A text field'' at line 3: CREATE TABLE "test49347608too_small_id" (
"id" INT NOT NULL auto_increment COMMENT 'Primary Key: Unique ID.',
"text" VARCHAR CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL COMMENT 'A text field',
PRIMARY KEY ("id")
arantxio’s picture

Status: Needs work » Needs review
StatusFileSize
new4.96 KB

I've changed the varchar_acsii type to text, which seems to be working on my end.

arantxio’s picture

StatusFileSize
new4.19 KB
new2.63 KB

Changed the test with some help from @daffie, this should be a bit better to understand.

daffie’s picture

Issue summary: View changes
Status: Needs review » Reviewed & tested by the community
Issue tags: -Needs issue summary update

All code changes look good to me.
All my remarks have been addressed.
I have updated the IS.
For me it is RTBC.

larowlan’s picture

+++ b/core/modules/pgsql/src/Driver/Database/pgsql/Schema.php
@@ -910,6 +909,14 @@ public function changeField($table, $field, $field_new, $spec, $new_keys = []) {
+      // We need to add CASCADE otherwise we cannot drop the sequence because
+      // the table depends on it.

Just for my understanding (I've not used postgres for about 15 years) can you elaborate on the type of objects that this will remove in Drupal?

Does the table gets removed? We don't do foreign keys so I assume it doesn't spider out to other tables.

daffie’s picture

@larowlan: The cascade option is necessary, because a sequence in PostgreSQL can have dependencies behind it like a constraint that the value cannot be lower then zero. When you delete the sequence, the constraint also needs to be deleted. The alternative is to delete the constraint first and then delete the sequence. The last option is more work and more that can go wrong.
The text in the comment is about the sequence is preventing the altering of the table. We should change the word "drop" to "alter" in the comment. Hopefully this can be done on commit.

larowlan’s picture

Thanks daffie that makes sense and resolves my concern, I'll look at this tomorrow morning

larowlan’s picture

We're in commit freeze so I will wait for that to finish before committing, but this is top of my list

  • larowlan committed 251a9465 on 10.0.x
    Issue #3191391 by Arantxio, larowlan, _utsavsharma, daffie: Schema::...

  • larowlan committed 6367bb87 on 10.1.x
    Issue #3191391 by Arantxio, larowlan, _utsavsharma, daffie: Schema::...
larowlan’s picture

Title: Schema::changeField() has bug when changing regular serial field to big serial field » [Needs backport] Schema::changeField() has bug when changing regular serial field to big serial field
Version: 10.1.x-dev » 9.5.x-dev
Status: Reviewed & tested by the community » Patch (to be ported)

Committed to 10.1.x and 10.0.x.

Needs reroll for backport to 9.5.x, the test changes don't apply.

dagmar’s picture

Status: Patch (to be ported) » Needs review
StatusFileSize
new4.14 KB

Rerolled for Drupal 9.5.x.

core/tests/Drupal/KernelTests/Core/Database/DriverSpecificSchemaTestBase.php has a lot of new lines in D10 than D9.5. I hope this doesn't break anything...

daffie’s picture

Status: Needs review » Needs work

The testbot is failing for PostgreSQL.

arantxio’s picture

Status: Needs work » Needs review
StatusFileSize
new4.15 KB

Thanks @dagmar for the reroll. The function getPrefix, does not exist in D9.5, because this function replaces tablePrefix(). So in this case for Drupal 9.5 we need to revert back to using tablePrefix().

I could not create a interdiff unfortunately, but I only changed the function getPrefix to tablePrefix($table) in pgsql/Schema / getSequenceName()

alexpott’s picture

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

This issue relies on code from #3257201: Create the new method Drupal\Core\Database\Connection::getPrefix() and deprecate Drupal\Core\Database\Connection::prefixTable($table) so we have to revert it from 10.0.x as that issue is only in 10.1.x.

  • alexpott committed 58268fcb on 10.0.x
    Revert "Issue #3191391 by Arantxio, larowlan, _utsavsharma, daffie:...
daffie’s picture

Title: [Needs backport] Schema::changeField() has bug when changing regular serial field to big serial field » Schema::changeField() has bug when changing regular serial field to big serial field
Status: Needs review » Fixed

No backport needed any more

alexpott’s picture

On 10.0.x prior to the revert if you run core/tests/Drupal/KernelTests/Core/Entity/Sql/SqlContentEntityStorageSchemaTest.php you will get an error like:

Error : Call to undefined method Drupal\pgsql\Driver\Database\pgsql\Connection::getPrefix()
 /Volumes/dev/sites/drupal8alt.dev/core/modules/pgsql/src/Driver/Database/pgsql/Schema.php:1086
alexpott’s picture

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

Status: Fixed » Closed (fixed)

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