I'm updated Drupal 7.10 to 7.12 with SQLite database, and error message:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 19 PRIMARY KEY must be unique: UPDATE {sequences} SET value = GREATEST(value, :existing_id) + 1; Array ( [:existing_id] => 0 ) in db_next_id()

Update is not success.

CommentFileSizeAuthor
#25 0001-test-for-1425794-SQLite-exception-primary-key-must-b.patch1.37 KBgeek-merlin
#25 0001-fixed-1425794-SQLite-exception-primary-key-must-be-u.patch2.27 KBgeek-merlin
#25 0001-D7-backport-of-1425794-SQLite-exception-primary-key-.patch2.22 KBgeek-merlin
#22 0001-D7-backport-of-1425794-SQLite-exception-primary-key-.patch2.23 KBgeek-merlin
#21 0001-test-for-1425794-SQLite-exception-primary-key-must-b.patch1.37 KBgeek-merlin
#21 0001-fixed-1425794-SQLite-exception-primary-key-must-be-u.patch2.28 KBgeek-merlin
#20 0001-fixed-1425794-SQLite-exception-primary-key-must-be-u.patch2.27 KBgeek-merlin
#18 0001-1425794-test-case-SQLite-exception-primary-key-must-.patch1.36 KBgeek-merlin
#9 1425794-9-simplify-sqlite-greatest-more.patch728 byteslotyrin
#6 1425794-6-simplify-sqlite-greatest.patch693 byteslotyrin
#3 1425794-3-sqlite-greatest-unset-value-not-array.patch491 byteslotyrin
#2 sequences_dump.sql_.txt125 bytesnevergone

Issue fork drupal-1425794

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

chx’s picture

chx CreditAttribution: chx commented
Title: SQLite update fail: 7.10 -> 7.12 » SQLite GREATEST is broken
Version: 7.12 » 8.x-dev
Component: database update system » database system
Priority: Normal » Critical

http://api.drupal.org/api/drupal/includes--database--sqlite--database.in...

shouldn't unset($args); be unset($args[$k]);???? Clearly this loop is about nuking NULLs.

nevergone’s picture

nevergone CreditAttribution: nevergone commented
FileSize
sequences_dump.sql_.txt125 bytes

Drupal 7.10 SQLite sequences dump

lotyrin’s picture

lotyrin CreditAttribution: lotyrin commented
FileSize
1425794-3-sqlite-greatest-unset-value-not-array.patch491 bytes

I'm with chx in #1. I can't imagine that loop is acting as intended there.

I don't have an SQLite install to run tests against, but here's a patch.

lotyrin’s picture

lotyrin CreditAttribution: lotyrin commented
Status: Active » Needs review
chx’s picture

chx CreditAttribution: chx commented
Priority: Critical » Normal
Status: Needs review » Needs work

Actually Damien said that if any of the arguments of GREATEST is NULL the result is NULL which makes sense -- if you compare any ways anything to NULL the result will be NULL. So, 1, 2, NULL, compare 2 to NULL is NULL, then 1 to NULL, NULL. See. So the loop actually is correct.

lotyrin’s picture

lotyrin CreditAttribution: lotyrin commented
FileSize
1425794-6-simplify-sqlite-greatest.patch693 bytes

Hmm. That is the SQL GREATEST behavior. But, doing it as implemented spits out a notice:

PHP Notice: Undefined variable: args in /home/ubuntu/drupal/core/includes/database/sqlite/database.inc on line 165

because $args got unset. Also, the way it flows makes this behavior seem like an error rather than intended. I'm changing this to an early return and adding a comment. This change also simplifies the logic.

Probably doesn't fix the issue here though, so leaving as needs work.

chx’s picture

chx CreditAttribution: chx commented

Comment -- good idea. Simplification -- even better idea. What about

return array_search($args, NULL) === FALSE ? max($args) : NULL;
Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented

The search needs to be strict:

return array_search($args, NULL, TRUE) === FALSE ? max($args) : NULL;

That said, in_array() would be a tad more self-documenting:

return !in_array(NULL, $args, TRUE) ? max($args) : NULL;
lotyrin’s picture

lotyrin CreditAttribution: lotyrin commented
FileSize
1425794-9-simplify-sqlite-greatest-more.patch728 bytes

Implemented second suggestion from #8, as well as decided that less verbose code means more verbose comment.

lotyrin’s picture

lotyrin CreditAttribution: lotyrin commented
Title: SQLite GREATEST is broken » SQLite upgrade breaks
Version: 8.x-dev » 7.x-dev
Component: database system » database update system
Assigned: Unassigned » lotyrin
Priority: Normal » Major

Going to see if I can reproduce and solve the actual issue here, since the ambiguous code this patch fixes doesn't seem to be the cause.

marcingy’s picture

marcingy CreditAttribution: marcingy commented
Version: 7.x-dev » 8.x-dev
Priority: Major » Normal

patch needs to be against d8 and as per #7 this should be normal.

marcingy’s picture

marcingy CreditAttribution: marcingy commented

-

marcingy’s picture

marcingy CreditAttribution: marcingy commented

-

lotyrin’s picture

lotyrin CreditAttribution: lotyrin commented
Status: Needs work » Postponed (maintainer needs more info)

nevergone, do you have a minimal case which reproduces this? I've done the following:

  • Install Apache and PHP (5.3.6) with sqlite + PDO
  • Install Drush (4.4)
  • Check Drupal 7.10 out from git
  • Run drush site-install.
  • Verify the site is up and working.
  • Check Drupal 7.12 out from git
  • Run drush updatedb
  • Update completes successfully

I'll be trying other things (creating content types, enabling more core modules), but so far I'm unable to reproduce.

lotyrin’s picture

lotyrin CreditAttribution: lotyrin commented
Assigned: lotyrin » Unassigned

Nothing to go on here, unassigning.

geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin commented

"hint": i have had good "success" seeing this error when enabling modules.

geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin commented
Title: SQLite upgrade breaks » SQLite exception "primary key must be unique" when sequences table erroneously contains more than one row

OK, hunted this down, it's all about db_next_id() and the sequences table (which contains ony one unique row "value")
look at #2! in our case the sequences table contains 2 entries (which i think should normally never happen).
of course sqlite update will freak out when next_id() sais:

update sequences set value=123;

as it is told to update *all* rows to the same value.

inserting "on conflict replace" here should do the trick.

geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin commented
FileSize
0001-1425794-test-case-SQLite-exception-primary-key-must-.patch1.36 KB

so let's feed the bot with a test case that should fail.

geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin commented
Status: Postponed (maintainer needs more info) » Needs review
geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin commented
FileSize
0001-fixed-1425794-SQLite-exception-primary-key-must-be-u.patch2.27 KB

and the fix that hopefully works

geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin commented
FileSize
0001-fixed-1425794-SQLite-exception-primary-key-must-be-u.patch2.28 KB
0001-test-for-1425794-SQLite-exception-primary-key-must-b.patch1.37 KB

upsala, forgot to change method name, so here's the stuff.

geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin commented
FileSize
0001-D7-backport-of-1425794-SQLite-exception-primary-key-.patch2.23 KB

and - in case this all works - here's the straight d7 backport.

lotyrin’s picture

lotyrin CreditAttribution: lotyrin commented

Won't on conflict replace mean that we've gotten the same value twice (once when the duplicate entry was created, once when we get and resolve the conflict)?

Is it unavoidable that the duplicate sequence entry gets created on SQLite? Should we try to prevent that from happening somehow?

Other than that this seems good, we'll see what the bots say.

It (rightfully) doesn't include the changes from my patch, that should become a separate issue.

geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin commented
Status: Needs review » Needs work

what? i must have been lacking sleep when first testing the sql...

hold on and sorry folks and bot for all that noise.

geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin commented
Status: Needs work » Needs review
FileSize
0001-D7-backport-of-1425794-SQLite-exception-primary-key-.patch2.22 KB
0001-fixed-1425794-SQLite-exception-primary-key-must-be-u.patch2.27 KB
0001-test-for-1425794-SQLite-exception-primary-key-must-b.patch1.37 KB

i hope this is the last iteration. going to sleep now.

@lotyrin: tested this manually, also see here: http://www.sqlite.org/lang_conflict.html

EDIT: also here some historical findings where the code comes from:
* implemented generically by chx in #356074-94: Provide a sequences API
* moved to sqlite (the generic implementation was dropped) by Josh Waihi in #633678-47: Make sequence API work on non-MySQL databases

Status: Needs review » Needs work

The last submitted patch, 0001-D7-backport-of-1425794-SQLite-exception-primary-key-.patch, failed testing.

geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin commented

to interpret this:
* d7 patch MUST fail
the other patches must be tested in a sqlite environment
the tests in my d7 environment do what they should (fail unpatched, pass patched)
as soon as i can i will set up a d8 sqlite environment and run tests locally.

geek-merlin’s picture

geek-merlin
they/omg
Primary language German
Location Freiburg, Germany
CreditAttribution: geek-merlin at Clever Systems, MachbarMacher, BinDrin, DruConsult commented
Issue summary: View changes
Status: Needs work » Needs review

axel.rutz queued 25: 0001-fixed-1425794-SQLite-exception-primary-key-must-be-u.patch for re-testing.

axel.rutz queued 25: 0001-test-for-1425794-SQLite-exception-primary-key-must-b.patch for re-testing.

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.

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.

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.8.x-dev
Status: Needs review » Needs work
Issue tags: +Needs reroll

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

Drupal 8.8.0-alpha1 will be released the week of October 14th, 2019, which means new developments and disruptive changes should now be targeted against the 8.9.x-dev branch. (Any changes to 8.9.x will also be committed to 9.0.x in preparation for Drupal 9’s release, but some changes like significant feature additions will be deferred to 9.1.x.). For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

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.

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.

mradcliffe’s picture

mradcliffe
he/him
Primary language English
CreditAttribution: mradcliffe at Kosada commented
Issue tags: +Needs issue summary update

Tagging because the issue summary references Drupal 7, but we want to apply the fix to Drupal 9 first.

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

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.

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: Needs work » Postponed (maintainer needs more info)
Issue tags: +Bug Smash Initiative

Is this still a problem?

There has been no work on this issue for 8 years. It was pointed out 4 years ago that a re-roll was needed and 2 years ago an issue summary was requested. There has been no reply to those comments.

Since we need more information to move forward with this issue, I am setting the status to Postponed (maintainer needs more info). If we don't receive additional information to help with the issue, it may be closed after three months.

Thanks!

daffie’s picture

daffie CreditAttribution: daffie commented

I do not think this is still a problem. The sequences table has been deprecated in #2665216: Deprecate Drupal\Core\Database\Connection::nextId() and the {sequences} table and schema.

quietone’s picture

quietone CreditAttribution: quietone at PreviousNext commented
Status: Postponed (maintainer needs more info) » Closed (outdated)

@daffie, thank you!

Based on #49 I am closing this as outdated.