Webform update fails on database update 7.x-4.10

See the related issue. It is caused by not completely uninstalling the webform_conditaionals module.

In the past, someone installed it but didn't uninstall it. It would probably be better to delete the table in the error and update again. Backup first.

When upgrading a Drupal 7.38 website from Webform 7.x-4.0-alpha10 to 7.x-4.10 I encountered a similar PDO error, indicating that: Base table or view not found: 1146 Table 'db.webform_conditional_actions' doesn't exist.

Upon further investigation, it was clear that even though the update seemed to work using drush, there were schema changes in the Webform tables that needed to be fixed.

I was able to deconstruct and reconstruct the tables with some clues from this post: https://www.drupal.org/node/2495731

This turned out to be pretty complex, so I am copying here in case it helps anyone. Now the original functions of my webforms are working again.

Essentially this is what worked:

1. dumped the data only from all the existing tables (created with the earlier version of webform 7.x-4.0-alpha10):
webform
webform_component
webform_conditional
webform_conditional_rules
webform_emails
webform_last_download
webform_roles
webform_submissions
webform_submitted_data

using mysql command line:
mysqldump -u USER -p --no-create-info DB webform > /path/to/backup//webform_data.sql

do this for EACH table

2. dumped the structure only for comparison one-by-one with the new schema.

using mysql command line:
mysqldump -u USER -p --no-data DB webform > /path/to/backup//webform_structure.sql

do this for EACH table

3. uninstall both webform and webform_validation (I never had webform_conditional installed!)
warning: this DELETES ALL content and tables!

drush pmi webform_validation
drush pm-uninstall webform_validation

drush pmi webform
drush pm-uninstall webform

drush cc all

3.1 check mysql (all webform tables should now be deleted)

3.2. re-install the latest webform
drush en webform

3.3 check mysql --- conditional actions table now exists

3.4 re-install webform_validation
drush en webform_validation

4. compare the dumped table structures to the newly installed table structures in mysql one at a time: they are not the same!

4.1 webform table: serial column is in the wrong position
ALTER TABLE webform MODIFY `next_serial` int(10) unsigned NOT NULL DEFAULT '1' AFTER preview_excluded_components;

4.2 webform table: confidential is in the new version, not the old, move it to the end of the table
ALTER TABLE webform MODIFY `confidential` tinyint(4) NOT NULL DEFAULT '0' AFTER next_serial;

4.3 webform data: copy the dumped data file for editing
cp /path/to/backup//webform_data.sql /path/to/backup//webform_data_FIX.sql

4.4 need to fix because there is a blank column position at the end of each INSERT statement in the .sql file, so edit the VALUES section of the FIX.sql file. I copied just the VALUES section into an editor and did find / replace:
Add the default integer ,0 at end of each insert statement: REPLACE ), WITH ,0), and then ADD ,0 at the very of the VALUES section for the last entry, so it ends with ,0);

4.5 compare the structure of the new table with the data, then load it if the schemas match
mysql -u USER -p DB < /path/to/backup//webform_data.sql

5. webform_component table: structure is the same, restore the data into the new table
mysql -u USER -p DB < /path/to/backup//webform_component_data.sql

6. fixing the webform_conditional table. see the info about what is missing: https://www.drupal.org/node/2495731

6.1 THREE FIELDS ARE MISSING in the newly installed table:
ALTER TABLE webform_conditional ADD `action` varchar(128) DEFAULT NULL AFTER andor;
ALTER TABLE webform_conditional ADD `target_type` varchar(128) DEFAULT NULL AFTER action;
ALTER TABLE webform_conditional ADD `target` varchar(128) DEFAULT NULL AFTER target_type;

6.2 table was empty, so no restore in my case

7. webform_conditional_rules table: empty table, definition the same. no ACTION taken.

8. webform_emails table: empty table, the NEW version has an additional field, status. no ACTION taken.

9. webform_last_download table: structure is the same, restore the data into the new table
mysql -u USER -p DB < /path/to/backup//webform_last_download_data.sql

10. webform_submitted_data table: structure is the same, restore the data into the new table
mysql -u USER -p DB < /path/to/backup//webform_submitted_data_data.sql

11. webform_roles table: structure is the same, restore the data into the new table
mysql -u USER -p DB < /path/to/backup//webform_roles_data.sql

12. webform_submissions: very different! difficult fix. need to adjust the dumped data.sql to match the new schema:
OLD SCHEMA
`sid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nid` int(10) unsigned NOT NULL DEFAULT '0',
`uid` int(10) unsigned NOT NULL DEFAULT '0',
`is_draft` tinyint(4) NOT NULL DEFAULT '0',
`submitted` int(11) NOT NULL DEFAULT '0',
`remote_addr` varchar(128) DEFAULT NULL,
`serial` int(10) unsigned NOT NULL,

NEW SCHEMA
| sid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| nid | int(10) unsigned | NO | MUL | 0 | |
| serial | int(10) unsigned | NO | | NULL | |
| uid | int(10) unsigned | NO | | 0 | |
| is_draft | tinyint(4) | NO | | 0 | |
| highest_valid_page | smallint(6) | NO | | 0 | |
| submitted | int(11) | NO | | 0 | |
| completed | int(11) | NO | | 0 | |
| modified | int(11) | NO | | 0 | |
| remote_addr | varchar(128) | YES | | NULL |

STEPS for FIXING COLS in DATA
A. open the data section of INSERT INTO `webform_submissions` VALUES [section];
copy this to WORD and replace the value breaks with paragraph
replace ),( with )^p(
save as .txt file and open in calc for editing using comma delimiters as column breaks

B. first strip ending ) from serial, which will no longer be the last column on each row
C. insert col after NID and paste in the SERIAL values without the ending parenthesis )
D. delete the old SERIAL col at the end

E. insert blank col after is_draft for HIGHEST_VALUE_PAGE fill with value = 0
F. insert blank col after submitted for COMPLETED fill with value = 0
G. insert blank col after completed for MODIFIED fill with value = 0

H. edit the column called remote_addr to add the closing parenthesis )
replace '^p with ')^p
I. paste the new col after the old version of remote_addr
J. if values are good, delete the old version that lacks the closing )

K. save to .txt, with comma delimiters, then open in text editor and change the line endings back to comma

breaks in WORD
replace )^p( with ),(
L. save back to text. copy the value section back into the original location within INSERT INTO

`webform_submissions` VALUES [section];

now the .sql should load the correct values into the correct columns

12.1 check the data.sql schema to make sure it matches the webform_submissions table before loading
12.2 restore the content
mysql -u USER -p DB < /path/to/backup//webform_submissions_data.sql

@mlex -- this sounds like a completely different issue. You are getting an error about a missing table, not about a table that exists but is missing a column.

It would have been helpful to know where you received this error (line number, and if possible stack trace). Recreating all the updates by hand is certainly not recommended.

hi Dan,
As I mentioned, I just upgraded from an older version of Webform [7.x-4.0-alpha10] to [7.x-4.10].

The webforms and all their admin info were simply no longer visible on the website. However, the data was still fine in the original mysql tables.

Really, I didn't want to spend an entire day reconstructing all the tables and their structures... but the upgrade changed the schema and did not install the table that it expected... Without losing ALL my existing data, (which is what happens when uninstalling the module), what else could I do? Since this is a departmental website at a University, I went to the trouble of patching it all together to save all the info created with the webforms.

Also, as I mentioned, the other post I found indicates that three columns need to be added to webform_conditional table. This turned out to be exactly correct. Basically, it all works now.

It's a great module! I hope that future upgrades won't go like this...

Its the part about the update not adjusting the schema that concerns me. It certainly should have.

Update 7402 creates webform_conditional and webform_conditional_rules.

Update 7423 creates webform_conditional_actions from data in webform_conditional and drops the unneeded columns from webform_conditional.

There are changes that update functions make beyond the schema changes. I assume that you read the code in webform.install and performed any of these manually as well.

Good luck. I would have lingering concerns that there is latent damage to the database after manually performing all those changes.

Hi Dan, thanks again for following up on this!

Basically, I would like to follow the standard update procedure. However, as you can see from my post, when I uninstall the module, then re-install it (a) the tables are deleted, and (b) the new tables no longer match the content that I backed up.

So what can I do?

I checked the webform.install file, but only in terms of checking the order and existence of the fields in the tables.

I thought that if I made my data conform to the tables EXACTLY as they are created by the re-install (in other words according to the webform.install) that I should be okay?

Any further advice or warnings will be helpful!

Dan,

The webform.install [7.x-4.10] creation of webform_conditional table only contains four fields: nid, rgid, andor, weight.

The module failed for me unless I followed the instructions here:
https://www.drupal.org/node/2495731

Are you sure that the module does not need the three additional fields in webform_conditional table ?
`action` varchar(128) DEFAULT NULL AFTER andor;
`target_type` varchar(128) DEFAULT NULL AFTER action;
`target` varchar(128) DEFAULT NULL AFTER target_type;

Once I added these it all works again.

THANKS!

Re #9: The standard update process is to leave the module enabled, install the new code base, and visit update.php or execute drush updatedb. Uninstalling will definitely result in a loss of data.

Without reading every update, and without knowing which hook_update_N functions would be executed for the version you started with, I can't say for sure what else the update functions do. In general, they do things other than adjust the schema and data.

Re #10, those columns are now in webform_conditional_action. The data from webform_conditional is moved to the action table by update 7423 and those columns are removed from the webform_conditional table. This was done to support multiple actions per conditional.

Hi Dan,
Thanks again. Re#9, yes, that is how I updated the module. But the webforms were no longer found. Possibly something went wrong, but at that point, the schema of the tables in mysql no longer matched the tables that webform.install created, and there was no webform_conditional_actions table.

So, I guess it was a freak accident or something. Hopefully, by reinstalling from scratch and loading the previous table info into the new schema I haven't broken anything...

Thanks for all the support on Webforms.