PDOException on Update DB 7416

What database? MySQL?

What is strange is that the line number is in the update manager. The transaction(s) in webform_update_7416 actually commit when the function returns and the transaction variables go out of scope.

You might upgrading again, but this time remove the db_transaction statements and step through the code to see if one of the SQL statements is not working.

You also might look at the tables to see if there is any problem with what the update routine is doing.

I have the same issue with update.php:

The following updates returned messages

webform module

Update #7416
Failed: PDOException: There is no active transaction en webform_update_7416() (línea 963 de /includes/update.inc).

My database is MariaDB: 5.5.38-MariaDB

I think we are in the land of SQL platform incompatibility.

The update routine for the serial numbers intermixes DDL and data access queries. I suspect that some platforms automatically commit the open transaction when a DDL statement is encountered. Then when the transaction variable goes out of scope, the transaction commits, but the transaction has already been committed.

@Liam Morland, @benjaminarthurt, @jasaldivara -- Please try the patch and report back your results.

Just adding to this to let you know that I had the same problem, and the patch and dropping the index fixed it for me. So thanks!

My database is:
mysql Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.2

As another datapoint, another server, with:
mysql Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (x86_64) using readline 6.2

updated cleanly the first time. This problem did not manifest. Not sure if that's signficant, but just letting you know.

UPDATED TO ADD: I was wrong about the second server, it did exhibit the problem as well. Sorry for the confusion.

Committed to 7.x-4.x and 8.x.

I suspect that some platforms automatically commit the open transaction when a DDL statement is encountered.

Yes, that is what MySQL does. That is why I wrote the update function with two transactions. The way you have re-written it is good for MySQL, however, it means that Postgres users don't get the benefit of having the entire update happen as a transaction, so they are not protected if the server crashes part way through the update. Is there a way to open a transaction at the start of webform_update_7416() only if the user is using Postgres?

@Liam -- I'm not sure how a crash here is worse than a crash at any of the thousands of places where transactions aren't used in Drupal. I also suspect that the DDL statement are extremely unlikely to crash, right? And the user should have a backup before updating anyhow, right?

I don't think trying to do RDBMS-specific hacks is a good idea. It leads us into incompatibilities like the one we had.

The (possibly very lengthy) update of the data is protected.

I'm not sure how a crash here is worse than a crash at any of the thousands of places where transactions aren't used in Drupal.

Lack of transactions is a big problem in Drupal, but the only way to solve that is to start using them.

I also suspect that the DDL statement are extremely unlikely to crash, right?

The power failure or corrupted RAM isn't going to wait until the DDL update is done.

And the user should have a backup before updating anyhow, right?

Yes, but restoring is a hassle and presumes that the user noticed what the problem was.

I don't think trying to do RDBMS-specific hacks is a good idea. It leads us into incompatibilities like the one we had.

I understand you reluctance, but I don't think this introduces much complexity.

I have made a follow-up issue: #2316711: Use transactions for DDL in Postgres.

In looking at the code, I cannot find a place where the database could fail and the update routine could not be repeated.

It only adds the fields to the tables if they aren't there already.

If the data transaction fails, all the individual updates will be rolled back, but so what? They they will just be recreated the next time through.

If the index can't be created, it can be created the next time.

Is there a possible failure mode I'm not seeing?

In this case, it would be safe to re-run webform_update_7416(). If the failure occurred just before the last call to db_change_field(), it would not be obvious that there was something missing. I come from the perspective that transactions are cheap and it is preferable to make changes as much as possible be all or nothing. As well, I don't like having Postgres users held back by the fact that Drupal has to maintain MySQL compatibility.

Anyway, my suggestion for fixing this is in #2316711: Use transactions for DDL in Postgres.

If the failure happens right before the last db_change_field, the update will re-run. Updates aren't marked as completed until the return without raising an exception.

I don't see the benefit of the transaction and I see the cost of introducing RDBMS dependencies. I would prefer to not have another issue that is essentially this one, so I'm going to mark that one as a duplicate. However, should quicksketch feel differently (or we change our minds), here's a direct like to the patch in that issue:
https://www.drupal.org/files/issues/webform-db_transactins-2316711-1.patch

A appreciate your work on this and all the other issues. We just disagree on this (very minor) point, and I'm happy to abide by whatever quicksketch wants.

I also had the same issue as #5 after applying 7.x-4.x-dev.
After dropping the nid_serial index from the database, I was able to update the database without error.

I had the same experience as ardscard mentioned in comment #15
Deleting the nid_serial index helped me.

Got the same error everyone else is getting, and did as instructed in #5 - applied the patch and dropped the nid_serial index from the webform_submissions table, and then it worked.

One question, though: are there any negative effects to be expected from dropping that index? Do I need to restore it now that the update is complete, or is it OK to leave it off?

I think what is happening that is the update actually run properly, but it wasn't recorded as having happened because of the "no active transaction" exception. Everything in the update can safely run again except for adding the index. I suppose the update hook could check for the existence of the index before it tries to add it.

@Liam- I bet you're right. And "else" clause on the the test for the field could remove any index. Patches welcome, although this is a transitory issue. As soon as everyone who used the RC that was "broken" get this resolved, it will never happen again.

Using Drush, ran into slightly different output here - this issue didn't come up when searching issue queue, so adding the output we saw error for future searchers. Resolved by moving from 7.x-4.0-rc5 to 7.x-4.x-dev.

$ drush -y updb
 Webform  7416  Add columns for serial numbered submissions. Add serial numbers to existing submissions.
 Webform  7417  Change webform_component.name to text to allow for longer labels.
Do you wish to run all pending updates? (y/n): y
Segmentation fault
Illegal string offset 'site' backend.inc:1013                                                                                                                                 [warning]
The command could not be executed successfully (returned: Segmentation fault                                                                                                  [error]
, code: 139)
'all' cache was cleared.                                                                                                                                                      [success]
Finished performing updates.

Webform 7.x-4.0-beta3 to update Webform 7.x-4.0-rc5
After applying the webform-serial_update_exception-2315339-4.patch

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'nid_serial': ALTER TABLE {webform_submissions} CHANGE `serial` `serial` INT unsigned NOT NULL COMMENT 'The serial number of this submission.', ADD UNIQUE KEY `nid_serial` (`nid`, `serial`); Array ( ) в функции db_change_field() (строка 3020 в файле /includes/database/database.inc).

@#22 That is the problem I describe in #19. Delete the nid_serial index and re-run the update.

Edit: Ignore this.

With this patch, the update will only add the index if it does not already exist.

@DD 85 - please install the patch from #25 and report if you can run the update without trouble. If so, then I will commit this as soon as possible.

I have solved the problem without the patch.

Perhaps the reason was the module Views. He was in the directory /sites/all/modules, but was not used and was off.

I did the following:
1. Returned backup.
2. Included Views.
3. Update to version webform-7.x-4.0-rc1. UPDATE WAS SUCCESSFUL!
4. Update to version webform-7.x-4.0-rc5.

After these steps, the error
Failed: PDOException: There is no active transaction in webform_update_7416 () (line 963 of /includes/update.inc)
eliminated!

@DanChadwick patch #25 by Liam is safe to commit and it fixes #22.
I've had the same issue and excluding index does the trick
thank you guys for those update fixes

@DanChadwick patch #4 and #25 successful applied and fix issue.

The following updates returned messageswebform module
Update #7416
Columns for serial numbered submissions successfully added. Serial numbers added to existing submissions.

Applying Patches #4 and #25 also fixed this issue for me in a production environment.

I was running a slightly older dev build of webform and #7416 was causing a segmentation fault when I tried to run it. I updated to the latest dev build and the segmentation fault went away, but it complained that the 'nid_serial' key was a duplicate. This patch resolved that issue - marking as RTBC.

I applied patches #4 and #25, it fixed this issue for me.

Patches #4 and #25 work for us also

#25 committed to 7.x-4.x and 8.x. Thank you.

Testbot error.