File field submissions point to incorrect file after D6 to D7 upgrade

This David, one of the developers who works with Aaron, the OP. Adding some info to this issue for others who might have to deal with this problem.

In 2010 Drupal core switched from using files table to new file_managed table. Also created was the file_usage table which appears to be a registry of which modules are using which uploaded files. Webform modules switched to this new backend scheme in early 2013 but did not provide update code in webform.install to migrate the files from old table to new or insert rows into the file_usage table.

Webform module maps webforms to submitted files via webform_submitted_data table, with column data containing the foreign key file id of the corresponding row in files table. This query displays the rows in old files table that belong to webforms.

	SELECT DISTINCT wsd.nid, wsd.sid, f.*
	FROM `webform_submitted_data` AS wsd
		INNER JOIN `webform_component` AS wc
			ON wsd.nid = wc.nid
			AND wsd.cid = wc.cid
		INNER JOIN `files` AS f
			ON wsd.data = f.fid
	WHERE wc.type = 'file';

To see related rows in the file_managed table, change the 2nd INNER JOIN to reference `file_managed` table. For a database that has just been upgraded from 6.x to 7.x, this query will not return any rows.

After a Drupal 6.x to 7.x migration, file mappings break due to the following reasons:

• The related rows in files table have not been migrated to file_managed
• The webform_submitted_data.fid column still contains foreign key values for old files table, which the Webform module code now interprets to be foreign key values for the file_managed table. Thus webforms will map to completely unrelated rows in the file_managed table.
• No rows were created in the file_usage table to reflect Webform module's usage of the uploaded files.

Luckily the files table is left untouched after migration so it is possible to repair the mappings. The steps for doing so:

• Migrate rows from files table to file_managed table. Be aware that the file_managed.uri column is defined with a unique index. During migration the values that populate this column come from files.filepath, which does not have a unique index defined. In certain cases the old Webform code allowed for duplicates in this column. In our case this happened when the same user uploaded the same file on the same form more than once. These cases have to be dealt with manually.
• Insert rows in file_usage table for each migrated row.

Attached is an edited version of the ad-hoc script I wrote to do this for our site. (.txt extension is due to restriction against attaching files named *.php). I removed some stuff to preserve confidentiality but it should still work as is. Note that function alter_duplicate_filepaths was written to be specific to our website and will not apply as a general case. Before attempting migration it should be determined if there are any duplicate filepath values in files table. If so, ad hoc transformation can be performed in this function. If no duplicates are found, invocation of alter_duplicate_filepaths can be commented out.

Links of interest

Webform module code switches from file_managed to uri column is defined :
http://drupalcode.org/project/webform.git/blob/cf4a11d385bcdcd18ae4f3429...

...but does not provide migration update:
http://drupalcode.org/project/webform.git/blob/cf4a11d385bcdcd18ae4f3429...

This appeared to work well for me when I migrated 900 submissions form Webform 6 to Webform 7.x-3.19.

Thanks.

It would be nice if this can be made as contributed module or get into webform update script as it will help end users (The files are not converted in 7.x-3.19 also).

The script worked fine for me with one exception. The filemime and filesize fields are reversed when written to the file_managed table. After running the script, the filemime field contained the file size, and the filesize field contained 0 because a string was being written to an int field.

I've attached a modified version of the script with the columns ordered properly.

Just discovered this issue today. I still need to test the script that was posted here, but something like it definitely is needed for the webform module. For all intents and purposes the upgrade path has a major failure if all of the files are mis-referenced. I tested this on a few dozen sites and I'm getting the same result posted here, completely borked submissions if they contained files.

I think this needs some more people to review this solution and if it's working as well as it does for the creator of the script then it should get committed to the webform module ASAP!

Seems to be a migration issue, not directly affecting webform itself. Also, Version 7.x-3.x is receiving critical bug fixes only.