Problem/Motivation

The update hook that was introduced in #3335488: Table entity string IDs should be 255 is causing a fatal database error:

SQLSTATE[22004]: Null value not allowed: 1138 Invalid use of NULL value: ALTER TABLE "entity_usage" CHANGE `target_id_string` `target_id_string` VARCHAR(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT 'The target ID, when the entity uses string IDs.'

This is happening because the target ID column used to allow NULL values prior to 2.0-alpha5. This was changed in #2980089: Not working with configuration reference, but no update hook was supplied to update the pre-existing rows that contain NULL values.

There has been an earlier attempt to update the column to use NOT NULL and this resulted in exactly the same error, ref. #2981394: Update failed: entity_usage_update_8203.

Steps to reproduce

  1. Install Entity Usage 2.0-alpha4.
  2. Recreate the entity usage statistics.
  3. You probably now have thousands of entries where the value NULL exists in the target_id_string column.
  4. Update to 2.0-beta8.

Result: the error above is thrown.

Proposed resolution

Update all entries from the table that have a NULL value for the target_id_string column before declaring it to be NOT NULL:

UPDATE entity_usage SET target_id_string = '' WHERE target_id_string IS NULL;

CommentFileSizeAuthor
#8 Upload fails on simply.com_.pdf210.63 KBuv516
#2 3340004-2.patch1 KBpfrenssen

Comments

pfrenssen created an issue. See original summary.

pfrenssen’s picture

pfrenssen
Location Sofia
commented
Status: Active » Needs review
StatusFileSize
new 3340004-2.patch1 KB

  • marcoscano committed b68beca1 on 8.x-2.x authored by pfrenssen 
    Issue #3340004 by pfrenssen: Fatal database error when updating to 2.0-...
marcoscano’s picture

marcoscano
Primary language Portuguese, Brazil
Location Barcelona, Spain
commented
Status: Needs review » Fixed

Thanks for filing the ticket and for the fix!
Committed and tagged beta10 with this fix, since it's better to avoid beta9 at this point.
https://www.drupal.org/project/entity_usage/releases/8.x-2.0-beta10

pfrenssen’s picture

pfrenssen
Location Sofia
commented

Thanks for the quick response!

Status: Fixed » Closed (fixed)

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

szato’s picture

szato commented

On entity_usage_update_8206() I got:

>  [notice] Update started: entity_usage_update_8206                                                                                                                                                   
>  [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 `entity_usage` DROP PRIMARY KEY;

I have to disable 'sql_require_primary_key' on session level by adding code to entity_usage_update_8206():
$database->query('SET SESSION sql_require_primary_key=0;');
before calling:

// Drop all indices involving columns we want to change.
uv516’s picture

uv516 commented
Version: 8.x-2.0-beta9 » 8.x-2.0-beta12
Priority: Normal » Critical
StatusFileSize
new Upload fails on simply.com_.pdf210.63 KB

I got the error as descriped in attached file. It handles about the field "tagert_id_string" which doesn't have a default value.
After a lot of searching I found the error and the solution:
In MySQL under entity_usage I inspected the table.
The field "target_id_string" is created with no NULL-value and no default value.
The field can not have at NULL-value, but it MUST have at default value.
I set (in) 0 as the default value and that solved my problem.

From the patch:

+  $database->update('entity_usage')
+    ->fields(['target_id_string' => ''])
+    ->condition('target_id_string', NULL, 'IS NULL')
+    ->execute();

I think that line 3 ('->condition('target_id_string', NULL, 'IS NULL')') should be different?