Contrib.social

Updating to Drupal 9.3 fails when sql_require_primary_key MySQL system variable is ON.

Open on Drupal.org →
Created on 21 December 2021, almost 3 years ago
Updated 22 November 2023, 12 months ago

Problem/Motivation

When updating to Drupal 9.3 an error occurs when the MySQL system variable "sql_require_primary_key" is set to ON, leaving the database in an undesired state (hence the reason I've set priority to "Critical").

The error is triggered by user_update_9301:

[error] SQLSTATE[HY000]: General error: 3750 Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.: ALTER TABLE "users" DROP PRIMARY KEY;

>  [error]  Update failed: user_update_9301 
 [error]  Update aborted by: user_update_9301 
 [error]  Finished performing updates.

Some managed database services require this to avoid replication issues.

Steps to reproduce

The error occurs when applying updates to the database. Trying to update a Drupal website to 9.3 with the MySQL system variable "sql_require_primary_key" set to ON triggers the error.

Proposed resolution

In user_update_9301: dropping the old primary key and adding the new one needs to happen in one statement.

Remaining tasks

Patch needs review.

Data model changes

No changes to the model are required, only the way schema changes are executed in user_update_9301 needs to be altered.

Release notes snippet

Fixed an issue where an error is triggered executing update user_update_9301 when updating to Drupal 9.3 on a MySQL database with the system variable "sql_require_primary_key" set to "ON".

🐛 Bug report
Status

Fixed

Version

9.3

Component
User system  →

Last updated 2 days ago

Created by

🇧🇪Belgium Bart Vanhoutte

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • Comment 12 months ago →
    🇦🇺Australia taggartj

    This is still an issue in Drupal 10 please see 

    /**
 * Adds a prime key id so I can finish early today. 
 */
function MYMODULE_update_8002() {
  // Add a prime key for views base.
  $spec = [
    'type' => 'serial',
    'unsigned' => TRUE,
    'not null' => TRUE,
  ];
  $db = \Drupal::database();
  $schema = $db->schema();

  if ($schema->fieldExists('some_custom_table', 'id')) {
    $schema->dropField('some_custom_table', 'id');
  }
   
  // THESE HERE Don't work as they should 
  //$schema->addField('some_custom_table', 'id', $spec, ['id']);
  //$schema->addPrimaryKey('some_custom_table', ['id']);

  // Resulting to hackery. 
  $table = 'some_custom_table';
  $col = 'id';
  $db->query('ALTER TABLE {' . $table . '} ADD {'. $col .'} INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY ({'. $col .'})');
}
  • Comment about 1 month ago →
    🇨🇦Canada Shane Birley

    Unsure if bumping this one is the way to go but I have run into this issue during a Drupal 8 > 9.5.11 upgrade.

    Running MariaDB 10.11.7.

    Reviewing previous patches but they appear to already be in play.

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024