Mysql error on upgrade to 6.x-2.9

I haven't had a problem upgrading my local install of webform, but this seems like it could be a serious problem if other users start to experience it at well. I'd be very surprised if everything is working correctly on your webform installation. It's likely that you've lost all submitted data for the component whose update failed (1166990169). It looks like (if you included all the information) that the webform_submitted_data table was updated but the webform_component table was not for component 1166990169. This means that all the submitted data is now pointing to the wrong component, and will not be loaded when viewing results. What's particularly complexing is that this only occurred for one component rather than a lot of them.

If you have a database backup, I'd really suggest trying to run the update again and see if the problem happens twice. How many webforms and components have you created on your site? The new smallint database column should be more efficient, but has the very, very unlikely limit of capping an individual webform at 65535 components, which I sure hope isn't the problem you're experiencing ;)

Ah! I ran into the problem on my local install when upgrading my personal site to webform 2.x. Seems that this error was caused by some orphaned data being left in the webform_submitted_data table. The update script only updates webform data for webform nodes that currently exist. Because there was some orphaned data left from nodes that had been deleted, not all the keys were updated and so the database column couldn't be changed.

You're right, no data was lost, the column for 'cid' just wasn't changed to use the smallint size for efficiency. You can run this in your Execute PHP block using devel module and it will make the database change safely and delete the crufty data in your database.

  db_query('DELETE FROM {webform_submitted_data} WHERE nid NOT IN (SELECT nid FROM {node} WHERE type = "webform")');
  db_query("ALTER TABLE {webform_submitted_data} CHANGE cid cid smallint unsigned NOT NULL default '0'");

I've ensured this doesn't happen to other users upgrading from 1.x to 2.x by adding additional lines to webform_update_13():

  // Ensure crufty submission data that was not removed when webform nodes
  // were deleted is cleared out before doing key changes.
  $ret[] = update_sql('DELETE FROM {webform} WHERE nid NOT IN (SELECT nid FROM {node} WHERE type = "webform")');
  $ret[] = update_sql('DELETE FROM {webform_component} WHERE nid NOT IN (SELECT nid FROM {node} WHERE type = "webform")');
  $ret[] = update_sql('DELETE FROM {webform_submissions} WHERE nid NOT IN (SELECT nid FROM {node} WHERE type = "webform")');
  $ret[] = update_sql('DELETE FROM {webform_submitted_data} WHERE nid NOT IN (SELECT nid FROM {node} WHERE type = "webform")');

I'm not making another update to fix this one, so you'll need to actually run the php code I suggested above to finish updating your site. There's little effect if you *don't* run it, other than you'll continue to have crufty entries in your database and the smaller column size gives you speed improvements.

I also had this error on upgrading from 5.x-1.10 to 5.x-2.4:

Duplicate entry '906-3-65535-0' for key 1 query: ALTER TABLE webform_submitted_data CHANGE cid cid smallint unsigned NOT NULL default '0' in /var/www/html/drupal-5.14/includes/database.mysql.inc on line 174.

This occurred at update #13.

Looking at a copy of the database before the update, the values of cid in webform_submitted_data ranged from 1227779299 to 1231934312 and in webform_component, cid ranged from 1228334036 to 1231951014. These values are clearly much too big for a smallint unsigned, which has a max value of 65535, hence the error.

So the question is why our cids are these big numbers - which look like UNIX timestamps - whereas the upgrade script is expecting them to be in the range 0 to 65535.

Progress: I investigated further and found that update #13 had done things like:
UPDATE {webform_component} SET cid = 1 WHERE nid = 906 AND cid = 1228334036

so I guessed that it had just missed some. Checking the updated db revealed:
SELECT COUNT(*) FROM webform_submitted_data WHERE cid NOT IN (SELECT cid FROM webform_component)
==> 56 in updated db, similar number in pre-update db.

So it seems that this was just a slightly different case of crufty data. I ran:

DELETE FROM webform_submitted_data WHERE cid NOT IN (SELECT cid FROM webform_component)
and
ALTER TABLE webform_submitted_data CHANGE cid cid smallint unsigned NOT NULL default '0'
- which then succeeded.

Sorry for the noise, perhaps this will help someone else.

Dave J

Thanks so much davej - I tried #3 at first, but that didn't help, then I tried your solution, and that worked for me. Without looking too deeply into it, it seems to make more sense that the cids be checked against the webform_component table. May want to update the install file to reflect that, if it hasn't already.

user warning: Duplicate entry '3-1-65535-0' for key 1 query: ALTER TABLE webform_submitted_data CHANGE cid cid smallint unsigned NOT NULL default '0' in /home/iplakoo9/public_html/hambone/modules/webform/webform.install on line 617.

Hi there! Am also facing the same issue. How do we get to DELETE FROM & ALTER TABLE?

Thanks!

Leo