Duplicate entry '689-192-23-0' for key 'PRIMARY' query: INSERT INTO webform_submitted_data (nid, sid, cid, no, data) VALUES (689, 192, 23, '0', '') in /sites/all/modules/webform/includes/webform.submissions.inc on line 110.

This is on a multistep webform and appears when going from one page to the next with saved data.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

fuzzyjared’s picture

UPDATE:

This happens whenever a multipage webform is saved. The webform initially was configured to have both the save as draft and the automatically save option selected. If I turn off the automatically save option and just use save draft then the webform will present the error when saving the data. This makes sense per the error but this appears to be new to the 3.11 webform as we didn't see this previously with the 3.9 version.

The information appears to still save appropriately with fields that have no change and fields that have changed.

I am not sure of the best way to fix this. The webform is continually doing an insert and not an update query apparently?

quicksketch’s picture

I can't reproduce this problem. Save as draft and automatically saving as draft between pages seem to work fine on my install of 3.11. Does this problem happen if you create a new Webform node and try to submit/save draft on it? it looks like you're correct that it's inserting instead of updating (or actually I believe it is supposed to simply DELETE the old submission first, then INSERT it again, so what's really missing here is the DELETE). However I don't know why it's not working as expected on your site, it doesn't seem to be a general problem.

jnettik’s picture

I can confirm this error after updating to the latest version of webform. I'm not using either of the draft options on the form, but this is a multi-page webform and the error shows up after saving.

Everything seems to work fine aside from that. Notifications send and submission gets saved to the database.

J Gladish’s picture

I have the same problem. (Now Using Webform 6.x-3.11 on Drupal 6-22). Originally built form in Webform 6.7 (no problem), once I updated to 6.9, I started to get these:

11 lines starting with:
user warning: Duplicate entry '27-223-1-0' for key 'PRIMARY' query: INSERT INTO drp_webform_submitted_data (nid, sid, cid, no, data) VALUES (27, 223, 1, '0', '') in /home7/----/public_html/sites/all/modules/webform/includes/webform.submissions.inc on line 110.

ending with:
user warning: Duplicate entry '27-223-12-0' for key 'PRIMARY' query: INSERT INTO drp_webform_submitted_data (nid, sid, cid, no, data) VALUES (27, 223, 12, '0', '') in /home7/-----/public_html/sites/all/modules/webform/includes/webform.submissions.inc on line 110.

I am using a Text field and then 10 Selects (4 options each, with Customize keys (Advanced) checked and numbered 0 to 3), a Page Break and then 11 more Selects on the second page. I am also running several other surveys, most using Select arrays and multiple pages (no problem with those). I deleted the original form and recreated it using 6-11, same errors.

fuzzyjared’s picture

I have not checked if this happens on new forms created with 6.11, but I have confirmed the issue on forms created with 6.9 before we update to 6.11 for bug and security updates.

longbaugh’s picture

I'm having the same problem with a multiple-page webform (v6.x-3.11). I dropped the primary key on the webform_submitted_data (and added an auto-incremented id with a primary key and added a key on nid,sid,cid, and no).

I've avoided the error and the table seems to be populating. The results and analysis field seems to be error free. Am I going to run into any problems down the road?

dmidkiff’s picture

Having the same problem here with 3.9. Just updated to 3.11 and no change. Multiple page webform. Single page forms work fine.

quicksketch’s picture

Status: Active » Postponed (maintainer needs more info)

I need more information on how to reproduce this problem.

HansKuiters’s picture

Here is more info. I had the same problem with two components, a textfield and a textarea. I first recreated the textfield and removed the old. On submitting all the pages only the textarea gave an error. The error for the textfield was gone. So before doing the same trick for the textarea I saved the 'extra' field in the database column 'webform_component'. Then deleted it and recreated the textarea. All errors gone.

Below the 'extra' fields from the database. I hope it helps.
Old, cid 18:

a:5: {s:13:"title_display";s:4:"none";s:9:"resizable";i:1;s:8:"disabled";i:0;s:21:"conditional_component";s:2:"10";s:20:"conditional_operator";s:1:"=";}

New, cid 86

a:10:{s:13:"title_display";s:4:"none";s:9:"resizable";i:1;s:8:"disabled";i:0;s:21:"conditional_component";s:2:"33";s:20:"conditional_operator";s:1:"=";s:4:"cols";s:0:"";s:4:"rows";s:0:"";s:11:"description";s:0:"";s:10:"attributes";a:0:{}s:18:"conditional_values";s:0:"";}
quicksketch’s picture

Thanks for the info capono, but what I was really looking for was steps to reproduce this (if at all possible) starting from the installation of Webform. It's hard for me to fix problems if they don't actually occur on my machine.

HansKuiters’s picture

No problem. Two webmasters created the form at different time and places, so no way to figure out which steps we took.

To the others I say: recreate the components, it might fix the problem for now.

gheter’s picture

Perhaps I'm just being a n00b but column "nid" is set to be Unique, yet this field seems to be populated by the node number, which won't be unique. I'm not sure why the error only displays on multi page forms and not on single page forms. Seems duplicate submissions are being executed. If Unique is removed from nid you can see the duplicates in the table after submission. I'm running 6.16 core and webform 6.x-3.14. I get this error every time I cerate a webform and insert page breaks into it...
Thanks, Gabe

gheter’s picture

And if it's of any interest, I get an additional error for every page break I add to the form.
-Gabe

quicksketch’s picture

Perhaps I'm just being a n00b but column "nid" is set to be Unique, yet this field seems to be populated by the node number, which won't be unique.

Webform should not be setting NID to unique. The combination of "nid", "sid", "cid", and "no" form the unique key for the webform_submitted_data table, not just the NID itself.

I'm running 6.16 core and webform 6.x-3.14. I get this error every time I cerate a webform and insert page breaks into it...

It sounds like you'd be getting a different error than the one described here (though similar), because adding a component shouldn't make any data in the webform_submitted_data table. It'd probably give an error for the "webform_components" table, but it doesn't use NID as the unique key either (or at least it shouldn't).

gheter’s picture

Oh, I see, yes nid, sid, cid and no are all set to be unique. I have two Page Breaks in my form. Here is what I get after submitting.

" user warning: Duplicate entry '234-8-1-0' for key 'PRIMARY' query: INSERT INTO webform_submitted_data (nid, sid, cid, no, data) VALUES (234, 8, 1, '0', '') in /sites/all/modules/webform/includes/webform.submissions.inc on line 110.
user warning: Duplicate entry '234-8-2-0' for key 'PRIMARY' query: INSERT INTO webform_submitted_data (nid, sid, cid, no, data) VALUES (234, 8, 2, '0', '') in /sites/all/modules/webform/includes/webform.submissions.inc on line 110."

If you remove the Primary Key from nid, sid, cid and no and then submit the form, the error goes away but you will see empty inserts for as many Page Breaks that are in the form. In my form '234-8-1-0' and '234-8-2-0' are the fields for First Name and Last Name. The form does insert information for First and Last name but there are also duplicate entries for those same nid, sid, cid with no data in them. The email that then gets generated pulls the empty fields from the duplicate entries, so you don't see any information for those fields.

Can you test by removing nid, sid, cid and no from Primary Key and then submitt a form and see if you get duplicate entries?

gheter’s picture

OK, this seems to have fixed it for me. In the webform includes folder there is a file named webform.submissions.inc, around line 108 is the INSERT statement. I changed it from this:
foreach ($submission->data as $cid => $values) {
foreach ($values['value'] as $delta => $value) {

db_query("INSERT INTO {webform_submitted_data} (nid, sid, cid, no, data) VALUES (%d, %d, %d, '%s', '%s')", $node->nid, $submission->sid, $cid, $delta, $value);

}
}
To this:
foreach ($submission->data as $cid => $values) {
foreach ($values['value'] as $delta => $value) {
if($value != ""){
db_query("INSERT INTO {webform_submitted_data} (nid, sid, cid, no, data) VALUES (%d, %d, %d, '%s', '%s')", $node->nid, $submission->sid, $cid, $delta, $value);
}
}
}
Basically just checking to see if there is a value in the variable being passed and inserted into the data column in the table. Because for some reason Page Breaks are trying to insert duplicate rows into the table but the data field is always empty. So far after a bit of testing this seems to have solved my issue

rutiolma’s picture

I had this problem when I used webform pagebreak and webform_ajax module. The problem in this case is that when we submit the form, $cid come out as a string and on INSERT is converted to 0.
The good thing is that $cid also appears as numeric so as a temporary solution I added around line 108

  foreach ($submission->data as $cid => $values) {
  	if(is_numeric($cid)){
		  foreach ($values['value'] as $delta => $value) {

to confirm that $cid is numeric and isn't converted to 0, causing primary key warning.

quicksketch’s picture

Category: bug » support
Status: Postponed (maintainer needs more info) » Closed (fixed)

Sounds like this is fixed either by newer versions of the module or it was caused by user-error (hard to say). In any case we haven't had any confirming reports in months.

John Franklin’s picture

Version: 6.x-3.11 » 6.x-3.17
Status: Closed (fixed) » Active

Sorry. It's back. I'm using 6.x-3.17 and getting it repeatably on a multipage form with Webform Conditional (Same Page Conditionals) enabled.

user warning: Duplicate entry '2-4-25-0' for key 'PRIMARY' query: INSERT INTO webform_submitted_data (nid, sid, cid, no, data) VALUES (2, 4, 25, '0', '') in /home/vmadmin/docroot/example.org/includes/common.inc on line 3576.
user warning: Duplicate entry '2-4-27-0' for key 'PRIMARY' query: INSERT INTO webform_submitted_data (nid, sid, cid, no, data) VALUES (2, 4, 27, '0', '') in /home/vmadmin/docroot/example.org/includes/common.inc on line 3576.
user warning: Duplicate entry '2-4-28-0' for key 'PRIMARY' query: INSERT INTO webform_submitted_data (nid, sid, cid, no, data) VALUES (2, 4, 28, '0', '') in /home/vmadmin/docroot/example.org/includes/common.inc on line 3576.

Now, the interesting thing is when I put a dpm($submission) in webform.submissions.inc around line 107, I see array entries in $submission->data with keys 25, 27 and 28 that have the correct values AND array entries with keys 25b, 27b and 28c. (Not a typo, that's, bee, bee and sea. No, the site has nothing to do with the BBC.) Those extra keys are all array('value' => array(NULL)).

Each of those three components are select, non-multiple (radio buttons) with blank default values.

I'm going over the code to try to figure out where such keys could be generated.

quicksketch’s picture

Status: Active » Postponed (maintainer needs more info)

@ John Franklin: I'll need instructions on how to reproduce this problem from a fresh install of Drupal, as I still have never seen it myself.

John Franklin’s picture

Create a site with Drupal 6.25 and Webform 3.17.
Create a webform:

Title: Test Webform
Form Settings => Advanced Settings: Save drafts between pages

Create components listed below with default settings unless otherwise stated:

  • Textfield: "First Component"
  • Textfield: "Second Component."
  • Select options: "1b" (Mandatory: yes ; options: 1 => First, 2=> Second)

In Page 2, create two textfields, each with conditionals triggered by the Select Option:

  • Pagebreak: Page 2
  • Textfield: "2a" Conditional rule: 1b is one of 1
  • Textfield "2b" Conditional rule: 1b is one of 2

FInish up with a Page 3:

  • Pagebreak: Page 3
  • Textfield: "Third Page Textfield"

Try to get through the form. Advancing to page 2, you should get:

user warning: Duplicate entry '1-1-1-0' for key 'PRIMARY' query: INSERT INTO webform_submitted_data (nid, sid, cid, no, data) VALUES (1, 1, 1, '0', '') in /home/drupal/drupal-6.25/includes/common.inc on line 3576.

I think I know what's going on. From the error message, you'd think the CID=1 "First Component" textfield would would be the problem component, but it's not. The problem is Webform is trying to save CID="1b" (the select options) using only the numeric part.

The array $form_state['values']['submitted'] goes from being form_key-indexed to cid-indexed to messed up in webform_client_form_pages(). webform_client_form_pages() calls _webform_client_form_submit_process(), which expects a form_key-indexed array and processes it as such, the line calling _webform_client_form_submit_flatten() then converts it to a cid-indexed array, and then _webform_client_form_submit_process() attempts to process it again and messes it up. Enable devel and apply the attached "webform_dpm.patch" to watch it happen.

I'm not sure if the answer is to leave $form['values']['submitted'] form_key-indexed, or if the answer is to not flatten (and reindex) it until after both processing steps have been called. How much of the rest of webform expects it to be cid-indexed? Do any webform add-on modules expect it to be cid-indexed in hooks?

To save you a little time, the attached gzip is a "drush sql-dump" of a bare-bones site with the above webform already built. Use admin/admin to log in.

John Franklin’s picture

Status: Postponed (maintainer needs more info) » Active

Switch back to active.

John Franklin’s picture

Category: support » bug

Were you able to duplicate the issue with this data?

vernond’s picture

Category: bug » support
Priority: Major » Normal

@John Franklin - The problem lies in the Field key you specified for the select options on the first page. The machine readable key may not have a numeric digit as the first character, it should be alpha (a..z) or underscore (_). Changing the field key to b1 or _1b instead of 1b solves the issue.

Programming language parsers (such as PHP and SQL) assume that a user-supplied name starting with a digit means that a number is being expressed, which will cause problems such as you have experienced.

John Franklin’s picture

@quicksketch, what do you think?

quicksketch’s picture

Category: support » bug

@vernond: This is definitely a bug at some level. It would be best if we could figure out how to make Webform respect numeric keys. We should be able to do this with some kind of checking. MySQL and PHP both don't think that "1" === "1a", but they do think that (int) "1" === (int) "1a". We probably have some incorrect type casting going on that is causing this.

If we can't solve the comparison problem, we should prevent the user from using keys that start with numbers, but that seems like an avoidable situation.

vernond’s picture

@quicksketch & @John Franklin : Indeed. I should refrain from answering issues late at night as I see that I omitted an entire paragraph echoing your suggestion of implementing a first character alpha only rule. Type casting is a pain when user-supplied names start with a digit, which is why programming language lexers/parsers do not allow it. Be that as it may, I'll take a look at this lot over the weekend.

John Franklin’s picture

I don't think the names-start-with-a-letter rule is the right answer. That would only mask the problem, not fix it. I say that because the problem isn't that the form_keys are malformed, but that the module reindexes the $form_state['values']['submitted'] array and then gets confused, treating CIDs as form_keys the second time it calls _webform_client_form_submit_process() in line 2187.

I think it comes down to choosing what $form_state['values']['submitted'] should use for its array keys and not changing it.

[Edit: corrected the line number. -jf]

vernond’s picture

I'm still in discussion mode on this one. My contention is that we should enforce the name-starts-with-alpha rule.

A "machine name" is so called because it should be a name that will be correctly interpreted as a name by a machine (which is part of what I failed to express adequately in #24).

In the Drupalverse we have situations where machine names are used to create a function name, such as in theme overrides, where a name commencing with a digit will WSOD. This kind of rule carries through to other contexts and standards also, such as CSS class names, where the first character may not be a numeric digit.

I do not think it's a great idea to ignore, or overthrow, a broad-context standard ruling as is being suggested.

quicksketch’s picture

machine names are used to create a function name, such as in theme overrides, where a name commencing with a digit will WSOD. This kind of rule carries through to other contexts and standards also, such as CSS class names, where the first character may not be a numeric digit.

Hm, good points @vernond. I hadn't considered those technical limitations. Though in the case of CSS, numbers have always been supported by browsers as the first character, it just wasn't part of the HTML 4.01 spec. HTML5 allows use of numbers (as well as all UTF8 characters) anywhere in the classname apparently. http://mathiasbynens.be/notes/html5-id-class

John Franklin’s picture

machine names are used to create a function name, such as in theme overrides, where a name commencing with a digit will WSOD. This kind of rule carries through to other contexts and standards also, such as CSS class names, where the first character may not be a numeric digit.

I think the naming rules is a separate issue which deserves its own ticket. The existing ability to start a form_key with a number combined with PHP's weak type enforcement brought this issue to light, but the root cause of this issue is a flaw in the code logic, specifically:

_webform_client_form_submit_process() expects a form_key indexed array, but is called with a CID indexed array in line 2187 of webform.module (6.x-3.17) and form_keys != CIDs.

vernond’s picture

Non-numeric first character seems to be the standard. See the regexes employed by core:

From D7's core field module's field.crud.inc:

  // Field name cannot contain invalid characters.
  if (!preg_match('/^[_a-z]+[_a-z0-9]*$/', $field['field_name'])) {
    throw new FieldException('Attempt to create a field with invalid characters. Only lowercase alphanumeric characters and underscores are allowed, and only lowercase letters and underscore are allowed as the first character');
  }

and D6's menu module's menu.admin.inc:

  if (preg_match('/[^a-z0-9-]/', $item['menu_name'])) {
    form_set_error('menu_name', t('The menu name may only consist of lowercase letters, numbers, and hyphens.'));
  }

We should be excising the tumour (i.e. numeric first character) instead of the headache (i.e. duplicate key error). Machine names should not have a numeric first character. Period. Enforcing the correct naming standard solves the reported issue and ensures zero potential incompatibilities going forward. The Webform code you highlight, quite correctly, does not expect, and should not be expected to expect, a numeric first character in a machine name.

vernond’s picture

Attached patches for D6 and D7 force the rule of non-numeric first character in machine key name.

@quicksketch - Before I commit these changes, please answer me this:
A consequence of this commit is that any sites currently using the "incorrect" standard (i.e. having a numeral as first character) are going to experience validation issues when editing existing components. There may/will also be knock-on effects in terms of Webform tokens (e.g. email templates) and in custom coding where the machine key name is being used to manipulate component values and so forth. My gut feel is that there shouldn't be too much backlash as we've not had very many reports of this particular issue. What say you - can we bang it in and hold thumbs, or do we need to go through some notification process?

[edit] I must add that I do not find the possible argument of "we've gotten away with it for so long that it is better to continue with the existing methodology" at all compelling. It is better to do it right.

John Franklin’s picture

Vernond, I think you've completely missed the point I made.

The first time the function _webform_client_form_submit_process() is called, $form_values contains:

... (Array, 3 elements)
     first_component (String, 8 characters ) Answer 1
     second_compnoent (String, 8 characters ) Answer 2
     radio_1 (String, 1 characters ) 2

The second time it is called, $form_values contains:

... (Array, 3 elements)
     1 (String, 8 characters ) Answer 1
     2 (String, 8 characters ) Answer 2
     3 (String, 1 characters ) 2

Please confirm for me that both of those arrays are appropriate arrays to pass to _webform_client_form_submit_process(), and if not how your patch ensures the arrays are correct.

vernond’s picture

@John Franklin - Your point was well made and very thoroughly described in #19. Where we differ is in the conclusions drawn from available evidence. I say that you are describing a symptom, whereas you are saying that you're describing a cause. My point is that the root cause is failure to follow convention in the construction of machine names.

Are you able to re-create the issue with a machine name that commences with an alphabetic or underscore character?

John Franklin’s picture

If the bug is defined as violating the preconditions of the _webform_client_form_submit_process() function, namely that the $form_values argument must be form_key indexed, then no, the patch in #33 does not correct the issue. _webform_client_form_submit_process() continues to be passed a CID indexed array even when all form_keys begin with a letter or underscore, as demonstrated in #34. (It just happens to ignore the bad data.)

Applying the attached patch also corrects the issue. The patch removes the call to _webform_client_form_submit_process() that violates the preconditions. Given that calling the function with bad data will, at best, do nothing, removing it does not break anything, and provides a (modest) performance improvement.

If you want to impose restrictions on the names of form_keys, go ahead. But don't tell me violating the precondition of the function is a symptom, not the cause, because it doesn't result in the error message that started this thread.

quicksketch’s picture

I would also like to see the allowing of numbers as the beginning of machine names. Other than the inconveniences of PHP's loose type-checking and type-casting, I don't think this is insurmountable as a technical problem.

The patch in #36 probably takes the wrong approach though, that code exists for a reason (which is documented in the code comments above it). I would expect that removing the processing would cause checkbox values to not update properly when navigating between multipage forms. Drupal's native handling of checkboxes isn't entirely accurate, especially around handling "0" values. That processing function is to help overcome these shortcomings.

vernond’s picture

I would also like to see the allowing of numbers as the beginning of machine names. Other than the inconveniences of PHP's loose type-checking and type-casting, I don't think this is insurmountable as a technical problem

Yup, it is not insurmountable. Yet it remains not a good thing for all the reasons I've mentioned here-and-there above.

quicksketch’s picture

Yet it remains not a good thing for all the reasons I've mentioned here-and-there above.

Right, I get that it's technically more troublesome. But we're programmers aren't we? :)

vernond’s picture

But we're programmers aren't we?

...which is precisely why I'd prefer us to treat a machine name as a machine name... :-)
Maybe it's just old-age and resistance to change, or something along them lines. Whatever, you're the boss.

quicksketch’s picture

The patch in #36 probably takes the wrong approach though, that code exists for a reason (which is documented in the code comments above it). I would expect that removing the processing would cause checkbox values to not update properly when navigating between multipage forms.

I may be entirely wrong. I removed the lines as recommended in the patch to try and see what sort of problem would result. Unfortunately I could find absolutely *no* problem with those lines removed, in either Drupal 6 or 7, when using checkboxes or radios, with 0, numeric, and alphanumeric keys. It seriously does not seem to have any effect at all (other than breaking keys that start with numbers, apparently).

This work-around for checkboxes and grid components apparently is legacy code from the 2.x release. The very first branch of 3.x included the call. However we completely revamped the control structure of Webform in #645388: Multistep forms: Save information after each step is completed and its followup in #1076090: Multistep form no longer works with conditional logic, after which we started calling the submit handlers for *all* components, whereas previously we only called it on select and grids. This "doubling-up" of calling the submit handlers is causing the keys to become lost.

Thus far looks like @John Franklin really hit this on the head. I'll do some more testing and see if I can find consequences yet.

quicksketch’s picture

Title: Duplicate Entry For Primary Key » PDO Error (Duplicate Entry For Primary Key) when using numerically-keyed components in multistep forms
Status: Active » Fixed

Looking at this further. I'm positive that John Franklin is exactly right. He already said it perfectly in #36. We're calling the same function twice (only 14 lines apart), once with the "right" parameters and then again with "wrong" parameters. The end result is that the function (most of the time) does nothing at all, unless you have numerically-keyed components, which causes some random component (based on its CID) to have its data modified. The whole thing is a screwball situation of leftover code.

I've removed the bogus code per #36 and committed it. All of which has the happy side-effect of making it so we can support numerically-keyed form components without having errors. :)

vernond’s picture

@John Franklin - I concur with your #34 in that I missed your point. Apparently I couldn't see the forest for all the trees in my face :-)

Automatically closed -- issue fixed for 2 weeks with no activity.