Upgrade from 1.3 to newer (1.4, 1.5, 3.x) with drush fails.

I have a modified 1.3. I download 1.4 with drush, then ran 'drush updb' it ran 3 WBM (7008, 7009, 7010) updates successfully.

I ran into this same isssue. Re-running updb from drush again seems to run the updates. I'm not sure what's causing them to fail first, but succeed on the second run.

Same as redndahead, I saw the errors but overcame them with the brute force of a second attempt at running database updates.

I am having this issue also. I tried running drush up and drush updatedb a second time, and the error still persists.

redndahead & hylid - Am I missing something you tried to complete the update?

Thanks!

If someone that can reproduce this were to actually figure out what's going on, we could come up with a fix. Otherwise, there's not much we can do.

I have the same issue here which i use drush to update workbench moderation module:

1. run drush drush -l example.com pm-update --no-core workbench_moderation
2. get the error message

Do you really want to continue with the update process? (y/n): y
Project workbench_moderation was updated successfully. Installed version is now 7.x-1.4.
Backups were saved into the directory /Users/AG/drush-backups/20150225052255/modules/workbench_moderation.              [ok]
WD php: PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.id' in 'field list': SELECT t.id AS id, t.name AS name, [error]
t.from_name AS from_name, t.to_name AS to_name
FROM 
{workbench_moderation_transitions} t
INNER JOIN {workbench_moderation_states} workbench_moderation_states ON t.from_name = workbench_moderation_states.name
INNER JOIN {workbench_moderation_states} workbench_moderation_states_2 ON t.to_name = workbench_moderation_states_2.name
ORDER BY workbench_moderation_states.weight ASC, workbench_moderation_states_2.weight ASC; Array
(
)
 in workbench_moderation_transitions() (line 1523 of
/Users/AG/app/sites/all/modules/contrib/workbench_moderation/workbench_moderation.module).
Drush command terminated abnormally due to an unrecoverable error.                                                                      [error]
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.id' in 'field list': SELECT t.id AS id, t.name AS name, t.from_name AS from_name, t.to_name AS to_name
FROM 
{workbench_moderation_transitions} t
INNER JOIN {workbench_moderation_states} workbench_moderation_states ON t.from_name = workbench_moderation_states.name
INNER JOIN {workbench_moderation_states} workbench_moderation_states_2 ON t.to_name = workbench_moderation_states_2.name
ORDER BY workbench_moderation_states.weight ASC, workbench_moderation_states_2.weight ASC; Array
(
)
 in workbench_moderation_transitions() (line 1523 of /Users/AG/app/sites/all/modules/contrib/workbench_moderation/workbench_moderation.module).Backups were restored successfully.                                                                                                     [ok]

Is any idea?

Cheers

Andrew

I had the same issue (drush up fails) if you:

drush dl workbench_moderation
then:
drush updb

everything runs fine.

It seems the update script of workbench_moderation is using workbench_moderation before applying the updates. I think that there is a difference in how drush updates and the webui updates. But the error is consistent.

I just upgraded from 7.x-1.3 to 7.x-1.4 using “drush up workbench_moderation” and received the same error as the original post. What was interesting was that drush indicated there were “No database updates required” and then presented the error regarding the unknown columns. After some investigation I found that even though I’ve been actively using this module for the past year the schema_version was set to ‘-1’ in the system table which meant the module update hooks would never trigger. Since I wasn’t sure what the overall schema status/version was in the database I rolled back to 7.x-1.3 and manually set the schema_version to ‘0’ and ran “drush updb”. It ran all the update hooks though ‘7005’ successfully then failed on ‘7006’ when it tried to add a field and a primary key that already existed. I commented out those lines in the .install file and re-ran “drush updb”. All the update hooks for 7.x-1.3 successfully completed and the system schema_version showed ‘7007’. I ran “drush up workbench_moderation” again and this time it installed 7.x-1.4 and ran three more update hooks successfully. Schema_version now shows ‘7010’. I’m not sure why (or when) the schema_version wasn’t set correctly, but it appears to be the same situation on several other production servers I have that were also built on clean Drupal installs… In any case I just wanted to add my findings to the discussion.

Hi

This issue seems to be this. Once the 7.x-1.4 update has been downloaded by Drush, but before the database updates are executed, workbench_moderation_action_info() is called (workbench_moderation.module, line 2280). This calls workbench_moderation_transitions() on line 2290. workbench_moderation_transitions() fetches all the workbench_moderation_transitions records from the database with a query that contains the four column names 'id', 'name', 'from_name', and 'to_name'. Obviously, because the 7009 update hasn't been performed yet, the 'id' and 'name' columns don't exist yet.

The reason that workbench_moderation_action_info() is called before the database updates are executed is because Drush clears the cache after downloading the new module files. This causes actions_synchronize() to be called, which leads inevitably to workbench_moderation_action_info().

Catching the PDOException that is thrown on line 1523 of workbench_moderation.module might be a good way to fix the issue.

Edit: Just to be clear, this is what happens when using the command `drush up workbench_moderation`

I have the exact same findings as mjohnstone42: drush updatedb reports no updates, but produces the PDO errors. Schema version in system table is set to -1. Manually changing the schema version to 0 does work until it reaches update 7006 where it hits the duplicate key issue. Manually changing the schema version to 7006 does proceed without further errors, but I'm not sure if I manually edit the live site schema directly from -1 to 7006 will cause any necessary updates to be skipped, and manually editing the schema twice between updates seems a bit too hacky.

Unlike asennus1, using drush dl does not fix the issue, nor does running updatedb more than once.

**EDIT** There's a missing hook_update in the workbench_moderation.install (this affects people that upgrade from previous versions)
**EDIT**
**EDIT**someone forgot that if you installed from a previous version you won't get the newly added "id" and "name" columns that were added to workbench_moderation_transition schema definition on first install. Those that upgrade won't get this unless someone creates a proper hook_update to deal with the upgrade. **EDIT**

I'm getting PDO errors because the latest code thinks my schema has the "id" and "name" column.
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.id' in 'field list':

IMHO a fix for this would involve a hook_update that checks to see if the schema has this already , otherwise add it.

I came up with a workaround:
Change the last line of code in this snippet from workbench_moderation.module:

BEFORE:

function workbench_moderation_transitions() {
  $transitions = &drupal_static(__FUNCTION__);
  if (!isset($transitions)) {
    $query = db_select('workbench_moderation_transitions', 't')
      ->fields('t', array('id', 'name', 'from_name', 'to_name'));

AFTER:

function workbench_moderation_transitions() {
  $transitions = &drupal_static(__FUNCTION__);
  if (!isset($transitions)) {
    $query = db_select('workbench_moderation_transitions', 't')
      ->fields('t', array('from_name', 'to_name'));

I am still testing, but when I removed the "id" and "name" field from the query I no longer get the PDO exception.

Still testing though, not sure if there's side-effects from this.

**EDIT**
to fix the problem, I manually set my schema_version of "workbench_moderation" back to 7007 so that workbench_moderation_update_7008 would run on the "updatedb", this gives the new 'id' and 'name' columns for those upgrading after the update runs 7008.

this situation could be related to a previous patch overlap on workbench_moderation_update_7008 , whether it was from a patch or from running a previous dev version that had a different workbench_moderation_update_7008 without the same contents and my schema was bumped up before getting the new one. Whatever the case,
here's the sql I used:
update system set schema_version = 7007 where name like 'workbench_moderation';

curiously, things seemed to also work when I commented out the 'id' and 'name' field , but I don't feel the need to find out why at this point.

I'd be inclined to say that this is not a bug, but rather an issue related to running previous dev branches that did have bugs. This should more be classified as a support request. I believe there's enough information above for others to solve this problem in their environment if it should arise for others. Fresh installs are not affected. Various Workarounds are described above. For myself I had to adjust the schema_version to 7007.

@joseph.olstad: Thanks for the analysis!

Why has this been marked as fixed? The issue with `drush up workbench_moderation` is still there. Please see my comment #11 from 2 weeks ago.

Hi Freightdev , the most likely reason why 7009 won't run is because update 7008 has not run (as was the case for me). What version of workbench were you upgrading from? was it 1.3 or was it 1.3.x dev? If you were using 1.3 previously (not dev) , which patches were you using? (as the problem could have been caused by a previous patch you were using?) . Pay close attention to any code changes in the hook_update for 7008.

The workaround is to set your schema_version for workbench_moderation in the system table back to 7007 so that when you run the update , 7008 will insert the fields properly then from there 7009 will upgrade correctly.

Another workaround would be to do a full pm_uninstall and re-install workbench_moderation , however if you wanted to keep the workbench_moderation_history you could dump that table and maybe restore it but pay attention to the table schema to make sure it's the same as the re-installed.

For us we were using a dev version of 1.3.x with many patches. I used the workaround method as described and it worked.

Perhaps Colan can weigh into this, there could be a way to fix this in code and make everyone happy, but changing code in the .install file is something that needs to be in my opinion tested quickly, committed and flagged as a release version. Perhaps say a 1.5.x tagged release with code to accommodate upgrades for those who ran dev or patched code of the 7008 and 7009 hook updates that were changed by the time 1.4 was released. This would have to not break compatibility for those upgrading from 1.4 installs as well. It's possible to do this but easy to botch it as well. I've seen this type of hook update issue rarely but when it happens it was usually due to a hook update that was changed during dev commits without instead creating a new hook update with a higher number to fix the previous entry. If I was writing a foolproof fix for this type of issue I'd write some code to do a check on the current schema for the missing column or whatever the issue may be (in our case missing columns that were supposed to have been added during 7008 (but weren't likely because 7008 had multiple versions !gasp! haven't looked exactly but given the empiricle evidence...) then depending on the result of the check(if necessary), call 7008 and 7009 again .

clear as mud?

Hi Joseph

Thanks for the detailed reply.

I'm on 1.3 (not dev, no patches) which means that the last database update in workbench_moderation.install is 7007. This is also the schema_version in the system table.

Like I said in my previous comment, Drush clears the cache after downloading the new module version but before applying database updates, and that's why 7008 and 7009 haven't been executed at the time the exception is raised.

Is there a specific reason that the exception on line 1523 of workbench_moderation.module can't be caught in a try/catch block? You'll have to forgive me, I'm not familiar with the code base as a whole, so I don't know what the wider implications would be.

Michael

I had the same issue when upgrading from the previous version.

Those steps fixed for me, without completely re-installing the module :
1. Disabled the module directly from the db :
$ drush sql-query "UPDATE system SET status = 0 WHERE name = 'workbench_moderation';"
2. Updated the db :
$ drush updb -y
3. Just in case, cleared cache :
$ drush cc all
4. Re-enabled the module :
$ drush sql-query "UPDATE system SET status = 1 WHERE name = 'workbench_moderation';"

As @joseph.olstad recommended I did a backup of my table workbench_moderation_history, just in case the schema would change.

Luc

Here's a patch, this one will work if you're upgrading from for example 1.3 git hash a90378d with a big cocktail of patches as we are

basically the order of the hook updates got out of order somewhere between git hash a90378d with a "big cocktail of patches" and release 1.4. This patch will help for those that are upgrading from that git hash to > 1.4.

however I cannot guarantee that this will work with other git hashes of < 1.4

do not commit this patch either because it could affect those upgrading from earlier or later git hashes, I havent spent the time to look at where these hook installs got re-ordered.

In our case we were on schema version 7009 with the installation of our older version of workbench_moderation, however in > 1.4 schema 7009 from git hash a90378d with a "big cocktail of patches" we were using is actually in 7010 in our case I had to reorder these to get the upgrade to work without causing schema problems. It works in our case, treat this problem as a githash to githash case by case/patch by patch issue. Be sure to look at your own workbench_moderation.install file and compare it to what you're upgrading to before deciding on an action for solving this.

For me, when I try to run update 7009, the first thing that fails is the attempt to create the 'id' field:

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

Because that fails, the 'id' field doesn't get created, and I get:

exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.id' in 'field list''

I'm running MySQL 5.7.7-rc on PHP 5.5.20.

Attached is a patch that uses try/catch. Check the schema version you are running. If you are running < 7009 then it ask your to contact your administrator to run pending updates, else it throws the error.

For me, it threw the warning message 20+ times, but did not prevent the module from upgrading.

New patch that tested if transitions are entities.

Failed testbot because of #2528354: Update simpletest: change 'current' field to 'is_current' .

Remarking it as needs review.

Failed testbot because it was tagged 7.x-1.4. Tagging as 7.x-1.x-dev then I will re-queue test.

Found one problem with patch #24. drupal_get_install_schema is in the includes/install.inc. This file is not always loaded. The function is cache so, it works sometimes, even when the file is not needed.

New patch attached.

I upgraded to 1.4 from 1.3 with no patches and had the same issue. My schema_version was for some reason set to -1, so after running:

drush sqlq "update system set schema_version = 7007 where name = 'workbench_moderation';"
drush updb

The errors went away.

Correct me if I"m wrong, but schema should only be -1 if the module is not installed.

I also experienced this issue trying to update an old codebase. In my case webform_update_7420 wanted to run first, it does a simple "user_role_grant_permissions" and the workbench_moderation_permission() calls the workbench_moderation_transitions() which throws the PDO exception.

I'd like to express my interest in this also, I've been dealing with it daily since the release of 1.4...

My process is building a new site with drush make, site install, importing db via drush and rr, cc, updb, and I'm getting a heap of errors in that process.

I'd like to note as well that I've just tried the same process with 1.3, and there are absolutely no errors to report.

@fubarhouse, please try the @iStryker patch from comment #30.
download his patch and put it in the workbench_moderation (v1.4) module folder, then run the "patch" command from this folder as a dry run (to test), if you have it as follows:
patch --dry-run -p1 < upgrade_from_1x3_to_1x4_fails-2428371-30.patch

if the --dry-run doesnt give you errors then run the patch command again without --dry-run
patch -p1 < upgrade_from_1x3_to_1x4_fails-2428371-30.patch

Then once you have done this run "drush updatedb" (if you have drush installed, otherwise us the drupal GUI )

iStryker, your patch in #30 worked for me - thank you very much! :)

Good work iStryker

I am still getting this error when I try to upgrade from 1.3 to 1.4:

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 believe this error only occurs if you're using one of the newer releases of MySQL.

Setting this issue back to "needs work" since the title is "upgrade from 1.3 to 1.4 with drush fails", but I can create a new issue if the maintainer would prefer.

Agreed; let's account for this in the patch as well.

We've been holding off upgrading to 1.4.x until this patch gets committed.

I wonder if this patch still passes testing... I'll trigger another simpletest and see

I'm getting this error with the patch #30 after run drush updb:

[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 Mysql 5.7.13

I'm also getting the same error with patch #30 after running drush updb:

[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

with Mysql 5.7.16.

I applied patch from: https://www.drupal.org/node/2615496#comment-11195335 to drupal core and everything works fine for me.

Hope this helps someone !!

#43 is the only solution. MySQL 5.7 seems to have gotten stricter, and now Drupal must too.

The original problem (race condition between cache, updates, info hooks) can usually be avoided by disabling (not uninstalling!) the module, and then updating it, and then re-enabling it.

Unfortunately this issue is a bit of a mess, with several threads overriding each other, because there are several reasons that this situation might arise. I'm going to try to summarize just in case anyone still cares to fix this (it should still be an issue in 3.x) and I'll attach a patch of a single line change which seems to solve the actual issue for me.

This is a departure from the approach taken by iStryker in #23 and later in #24-#30:

#23: Exceptions in the db_select statement of workbench_moderation_transitions are caught and allowed with a warning.
#30: A more complex patch to determine if transitions are entities. This approach seems like overkill from my limited understanding, but it may be necessary for other reasons unknown to me.
#45: Instead of explicitly naming fields in the db_select statement, this patch just requests t.* from the workbench_moderation_transitions table. This seems to solve the upgrade problem.

I know that some people have recommended "workarounds" for this problem, but I feel that it should simply be fixed. Requiring administrators to disable a module, upgrade it and then re-enable it, as suggested in #44, is bad UX.

I also disagree with #38 and #39: since the issue of the primary key is one that affects Drupal core for which a fix is in progress in a core issue, workbench_moderation should not attempt to solve it. See #43.

Here is a patch for 7.x-3.x.

Patch looks great, and fixes the issue

RTBC patch 46 holds

Note: when using mysql 5.7.x or higher, you'll need to make sure to patch your core with patch #2 or #11 from #2615496: A serial/primary key field can not be added to an existing table for some databases

Otherwise you'll get:

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

Thanks for simplifying the solution. Simple is better. Patch 46 yay!

Thanks for your help on this issue. We had the same problem while running drush updb from 1.3 to 3.0 on Drupal 7.54 with MySQL 5.7. We installed the patch from #2615496: A serial/primary key field can not be added to an existing table for some databases and subsequently did not need patch #46. All seems to be working fine so far.

bump. Please commit this great multiyear effort!

In my environment, the is_current field already existed. Added an improvement to patch #45, which tests to see if the is_current field already exists and only renames the current field if necessary.

#51 is very elegant and I have tested it. Thanks!

patch #51 is all that is needed here.

joseph.olstad:

patch #51 is all that is needed here.

Can you advise if it is just applying the patch, do you need to run drush updb

I thought I was successful but further work on this still shows the error from the original post.

RTBC +

I found myself running into the following error once I ran updb or update.php after upgrading form 1.3 to 3.x-dev Using this core patch from this issue and the patches from this issue.

exception 'PDOException' with message 'SQLSTATE[42S22]: Column not  [error]
found: 1054 Unknown column 'm.is_current' in 'where clause'' in
/var/www/maa/web/includes/database/database.inc:2227
Stack trace:
#0 /var/www/maa/web/includes/database/database.inc(2227):
PDOStatement->execute(Array)
#1 /var/www/maa/web/includes/database/database.inc(697):
DatabaseStatementBase->execute(Array, Array)
#2 /var/www/maa/web/includes/database/select.inc(1280):
DatabaseConnection->query('SELECT m.*, r.t...', Array, Array)
#3
/var/www/maa/web/sites/all/modules/workbench_moderation/workbench_moderation.module(1236):
SelectQuery->execute()

In order to work around, I had to do the following, starting from the 7.x-1.3 state.

• Update the workbench_moderation module to 7.x-1.4.
• Apply the core patch and the patches 23 and 30 in order to run the updb (More specifically, to get update 7010, rename `current` to `is_current`.)
• Run /update.php through the gui.
• Install the latest workbench_moderation version and apply patch 51
• Run /update.php through the gui. (Not sure if this was necessary or if I could have used drush)

If anyone runs into this issue, I hope this helps.

Yes I confirm what was said in #56
we were actually using the aforementioned core patch #2615496-68: A serial/primary key field can not be added to an existing table for some databases in addition to #51 #2428371-51: Upgrade from 1.3 to newer (1.4, 1.5, 3.x) with drush fails.

not sure about patch 23 or #30 though, as far as I can recall we were just using #51 #2428371-51: Upgrade from 1.3 to newer (1.4, 1.5, 3.x) with drush fails. and no others aside from the core patch #2615496-68: A serial/primary key field can not be added to an existing table for some databases.

I can confirm that the issue is for mysql 5.7.x or higher - Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use[error]

If you are using lower version of mysql you will no longer have the issue.

IF you still want to use 5.7 or higher I will recommend patch given in #57