Update #7017 to 2.10 or later throws error during updatedb [#2918573]

Update #14: This may be a Drupal core bug regarding MySQL >= 5.7 https://www.drupal.org/project/drupal/issues/3056593
Update #12: This looks like an issue with Mysql 5.7 and Drupal 7. My workaround is to downgrade my database version to 5.5.

Original post:
I am trying to upgrade a fairly old version of Workflow to the latest version and I've run into a number of issues.

I decided to upgrade version by version to pinpoint them. When I ran the db update for 2.3 I got the following warnings pop up.

$:/var/www/public$ drush updatedb
 Workflow  7011  Add database fields to make Workflow exportable.   Ignore the 'User warning'.
Do you wish to run all pending updates? (y/n): y
Performed update: workflow_update_7011                                                                                                                                                           [ok]
class_implements(): Class WorkflowTransitionController does not exist and could not be loaded entity.module:728                                                                                  [warning]
in_array() expects parameter 2 to be array, boolean given entity.module:728                                                                                                                      [warning]
class_implements(): Class WorkflowTransitionController does not exist and could not be loaded entity.module:728                                                                                  [warning]
in_array() expects parameter 2 to be array, boolean given entity.module:728                                                                                                                      [warning]
Missing database columns for the exportable entity Workflow as defined by entity_exportable_schema_fields(). Update the according module and run update.php! entity.module:872                   [warning]
'all' cache was cleared.                                                                                                                                                                         [success]
Finished performing updates.                                                                                                                                                                     [ok]

Seeing as they were warnings I pressed on, upgrading individually through versions 2.4, 2.5 until I hit an error at 2.6

$:/var/www/public$ drush updatedb
 Workflow  7016  Add an index to {workflow_node_history}.nid.
 Workflow  7017  Add a primary key to {workflow_scheduled_transition}.
Do you wish to run all pending updates? (y/n): y
Performed update: workflow_update_7016                                                                                                                                                           [ok]
SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead                                               [error]
Performed update: workflow_update_7017                                                                                                                                                           [ok]
include_once(/var/www/public/sites/all/modules/workflow/includes/Entity/WorkflowTransitionController.php): failed to open stream: No such file or directory bootstrap.inc:3494       [warning]
include_once(): Failed opening '/var/www/public/sites/all/modules/workflow/includes/Entity/WorkflowTransitionController.php' for inclusion (include_path='.:/usr/share/php')         [warning]
bootstrap.inc:3494
class_implements(): Class WorkflowTransitionController does not exist and could not be loaded entity.module:728                                                                                  [warning]
in_array() expects parameter 2 to be array, boolean given entity.module:728                                                                                                                      [warning]
include_once(/var/www/public/sites/all/modules/workflow/includes/Entity/WorkflowTransitionController.php): failed to open stream: No such file or directory bootstrap.inc:3469       [warning]
include_once(): Failed opening '/var/www/public/sites/all/modules/workflow/includes/Entity/WorkflowTransitionController.php' for inclusion (include_path='.:/usr/share/php')         [warning]
bootstrap.inc:3469
class_implements(): Class WorkflowTransitionController does not exist and could not be loaded entity.module:728                                                                                  [warning]
in_array() expects parameter 2 to be array, boolean given entity.module:728                                                                                                                      [warning]
'all' cache was cleared.                                                                                                                                                                         [success]
Finished performing updates.

The same warnings as 2.3 have popped up along with the PRIMARY KEY error, which I've been unable to find any other reference to in the Workflow module.

Could anyone point me in the right direction as to how to fix these issues?

Thanks in advance.

Comments

Comment #1

25 October 2017 at 01:59

chris_gooley created an issue. See original summary.

Comment #2

johnv

Dutch

CreditAttribution: johnv commented 26 October 2017 at 10:36

Thank for the detailed report.
The functions alre located in workflow.install.inc.
I always use www.mysite.com/update.php to upgrade. This also clears all caches before/after the db upgrade.

Do you experience any problems? an extra flush caches shoudl to the trick.

The following error:
Failed opening '/var/www/public/sites/all/modules/workflow/includes/Entity/WorkflowTransitionController.php'
May happen because this class is moved from one place to another. There is a known problem with moving classes around.

The following error:
SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

I have updated the update function 2017 in dev. You may try jumping from 2.5 to dev version.
* db_change_field('workflow_scheduled_transition', 'tid', 'tid',
* array('type' => 'serial', 'not null' => TRUE),
* array('primary key' => array('tid')));

Comment #3

26 October 2017 at 10:40

johnv committed a6ae712 on 7.x-2.x

Issue #2918573: Correct workflow_update_7017()

Comment #4

johnv

Dutch

CreditAttribution: johnv commented 26 October 2017 at 11:21

Title:	Upgrading older version to latest throws error during updatedb.	» Upgrading version 2.2 to 2.10 throws error during updatedb
Related issues:		+#2884417: Update #7017 from 2.5 to 2.10 fails with MySQL v5.7.18

The problem was already reported in #2884417: Update #7017 from 2.5 to 2.10 fails with MySQL v5.7.18.
It would have been better to fix it there.

Comment #5

brunodbo

he/him

CreditAttribution: brunodbo at Textillia commented 20 November 2017 at 18:39

Version:

7.x-2.2

» 7.x-2.11

Just tried upgrading from 2.5 to 2.11, and getting the same error while running drush updb:

SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead                [error]

Comment #6

Haemathemesis CreditAttribution: Haemathemesis commented 13 January 2018 at 16:47

I get the same error on a freshly migrated site. Tried latest stable 7.x-2.11 and 7.x-2.x dev version.

The following updates returned messages
workflow module
Update #7017

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead: ALTER TABLE {workflow_scheduled_transition} ADD `tid` INT NULL auto_increment DEFAULT NULL, ADD PRIMARY KEY (`tid`); Array ( ) db_add_field() function (/foo.bar/includes/database/database.inc 2900 sor).

Comment #7

johnv

Dutch

CreditAttribution: johnv commented 14 January 2018 at 08:04

What database type are you using?
Perhaps mine is different from yours. There is a difference in declaration: https://www.w3schools.com/sql/sql_primarykey.asp

Comment #8

johnv

Dutch

CreditAttribution: johnv commented 14 January 2018 at 11:33

FWIW,
The error should be in workflow_update_7017(), regarding the 'tid' field.

But according to the following page, that code is correct.
https://api.drupal.org/api/drupal/includes!database!database.inc/functio...

Comment #9

Haemathemesis CreditAttribution: Haemathemesis commented 14 January 2018 at 08:51

Thank you for your info. I use Mysql 5.6 and the update which not completed is 7017. It happens from workflow 7.x-2.6 version. I tried to update release by release but get the same error when i update to 7.x-2.6.

Comment #10

marthinal CreditAttribution: marthinal at Bluespark commented 7 February 2018 at 08:48

This issue is related to #2615496: A serial/primary key field can not be added to an existing table for some databases. I can reproduce with Mysql 5.7

Comment #11

Haemathemesis CreditAttribution: Haemathemesis commented 7 September 2018 at 08:00

Got the same error on another site.

Comment #12

chris_gooley CreditAttribution: chris_gooley commented 28 March 2019 at 00:32

I came around again to try and upgrade this module but have run into the same issue.

Attempting to upgrade to 2.6 shows the following error:
SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

Even after manually updating workflow_update_7017 to

function workflow_update_7017() {
  db_add_field('workflow_scheduled_transition', 'tid', array(
    'type' => 'serial', 
    'not null' => TRUE
    ), array(
    'primary key' => array('tid'),
  ));
  return t('Added stid primary key to {workflow_scheduled_transition}');
}

This looks like it must be an issue with Mysql 5.7 and Drupal 7. My workaround is to downgrade my database version to 5.5.

Comment #13

johnv

Dutch

CreditAttribution: johnv commented 28 March 2019 at 14:31

@chris_gooley, I suppose you have content (scheduled transitions) in this specific table?

Comment #14

johnv

Dutch

CreditAttribution: johnv commented 3 June 2019 at 19:12

Title:	Upgrading version 2.2 to 2.10 throws error during updatedb	» Update #7017 to 2.10 or later throws error during updatedb
Issue summary:	View changes

This may be a Drupal core bug regarding MySQL >= 5.7 https://www.drupal.org/project/drupal/issues/3056593

Comment #15

johnv

Dutch

CreditAttribution: johnv commented 3 June 2019 at 19:15

Comment from joshbrown81 : I think this could be related to the 5.7.3 release for MySQL ( https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html )

Columns in a PRIMARY KEY must be NOT NULL, but if declared explicitly as NULL produced no error. Now an error occurs. For example, a statement such as CREATE TABLE t (i INT NULL PRIMARY KEY) is rejected. The same occurs for similar ALTER TABLE statements.