[v2.6] Upgrade from v2.5 Custom form sql error

[PoPoutdoor]

sql statement: ALTER TABLE form_field ADD UNIQUE ( field_name );
Error message: Duplicate entry 'BLANKDIV' for key 'field_name'

FYI: I got 2 'BLANKDIV' in field_name for click to open div

[rjmackay]

@PoPoutdoor I'm not sure if theres an easy fix for this in core, field_name should probably have been unique from the start. I suggest you rename you BLANKDIV fields to be unique then run the database upgrade again.

cc/ @aoduor any comments on this? It looks like adding the unique index on form_field.field_name is causing issues on upgrade since deployers could have added duplicate field names already.

[aoduor]

Ah, I meant to add form_id to that index. That way, you cant have the same field name within the same form. what do you think about removing the unique index for upgrades, but enforce through post validation, and then for those installing afresh, have it set from the get go. Because essentially, having two fields with the same name in the same form causes conflicts during custom form import via CSV and XML.

----- Reply message -----
From: "Robbie MacKay" notifications@github.com
To: "ushahidi/Ushahidi_Web" Ushahidi_Web@noreply.github.com
Cc: "Angela Oduor" angela@ushahidi.com
Subject: [Ushahidi_Web] [v2.6] Upgrade from v2.5 Custom form sql error (#922)
Date: Mon, Nov 5, 2012 00:14
@PoPoutdoor I'm not sure if theres an easy fix for this in core, field_name should probably have been unique from the start. I suggest you rename you BLANKDIV fields to be unique then run the database upgrade again.

cc/ @aoduor any comments on this? It looks like adding the unique index on form_field.field_name is causing issues on upgrade since deployers could have added duplicate field names already.

—

Reply to this email directly or view it on GitHub.

[rjmackay]

@aoduor removing from the upgrade is probably fine. Is it enforce by the validation when creating fields?
If they're just getting a horrible SQL error on trying to create a duplicate field that would be bad.

Agreed that this should probably include form_id in the index ...and this looks like another argument for us all doing code reviews ;)

[aoduor]

@rjmackay yeah, it's enforced during validation when creating fields already... Only need to go in and add the form_id condition as well.

Yup! we should probably have this argument during the tech call tomorrow :D!

[aoduor]

Hmmmm, come to think of it, removing the index for upgrades isn't the best solution. We need to think about those who've has successful upgrades already(e.g crowdmap users). We'll have lots of inconsistencies. I'd suggest returning the index to the sql upgrade files(for the sake of those who has successful upgrades) and then altering the field_name index in the form field table to accomodate the 'form_id' field. Then any deployer who gets the same issue as @PoPoutdoor should simply rename the form field_name and run the upgrade scripts again. Open to any other suggestions on fixing this @rjmackay and @kamaulynder

[PoPoutdoor]

I do comment the upgrade98-99.sql and let the upgrade passed.
I modified the duplicated entries and run 'ALTER TABLE form_field ADD UNIQUE ( field_name );', everything is working now.

For those unable to do manual database operations, a hotfix script to check duplicated field_name before upgrade.sql will be the short term solution.

If the hotfix script is reliable, it can do the field_name value renamed (suffix with '_n') .

BTW, the duplicated BLANKDIV value was assigned by the custom form code, may need code changes to avoid assigning further non-unique value.

[rjmackay]

Need to figure out what to include in #910

@aoduor I'm not sure the inconsistency matters much.. since its just an index, those who already have it added would just look the same as users who installed from scratch.
Upgrade scripts should never break with SQL errors.. since many of our users don't have the skills to debug that.

Can you follow up on @PoPoutdoor 's comment that BLANKDIV entrie were created by the custom form code?

Why did we need to force the field name to be unique in the first place? is this just to simplify custom forms CSV import?

[aoduor]

@rjmackay the inconsistency actually does cause some issues. For those who already had the index before, the unique field name would be enforced regardless of the form the field exists in, so each and every single form field would have to have a unique name. With the new index introduced, you can have two form fields with the same name, as long as they're not on the same form. Yeah, i'll have a look at the blankdiv entry issue mentioned by @PoPoutdoor.

Yup, the reason we we're forcing the field name to be unique is for ease of custom form responses upload via CSV/XML. Leaving as it was before would cause total chaos during import.

[aoduor]

• generally, also to avoid confusion when using custom forms. Doesn't really make sense to have multiple fields on the same form with the same name