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.

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...