Integrity constraint [error] violation: 1062 Duplicate entry 'Filtered HTML' for key 'name' [#1847366]

I sucessfully upgraded Drupal core from D6 to D7, however when I try to install the standard distribution profile using drush I get the following errors:

The following extensions will be enabled: standard, rdf, overlay, toolbar, shortcut, search, dblog, file, image, dashboard, contextual
Do you really want to continue? (y/n): y
WD php: PDOException: SQLSTATE[23000]: Integrity constraint          [error]
violation: 1062 Duplicate entry 'Filtered HTML' for key 'name':
INSERT INTO {filter_format} (format, name, cache, status, weight)
VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1,
:db_insert_placeholder_2, :db_insert_placeholder_3,
:db_insert_placeholder_4); Array
(
    [:db_insert_placeholder_0] => filtered_html
    [:db_insert_placeholder_1] => Filtered HTML
    [:db_insert_placeholder_2] => 1
    [:db_insert_placeholder_3] => 1
    [:db_insert_placeholder_4] => 0
)
 in filter_format_save() (line 210 of
/mnt/drupal-data/www-test/modules/filter/filter.module).
Cannot modify header information - headers already sent by (output   [warning]
started at /var/www/drush/includes/output.inc:37) bootstrap.inc:1239
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry &#039;Filtered HTML&#039; for key &#039;name&#039;: INSERT INTO {filter_format} (format, name, cache, status, weight) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4); Array
(
    [:db_insert_placeholder_0] => filtered_html
    [:db_insert_placeholder_1] => Filtered HTML
    [:db_insert_placeholder_2] => 1
    [:db_insert_placeholder_3] => 1
    [:db_insert_placeholder_4] => 0
)
 in filter_format_save() (line 210 of /mnt/drupal-data/www-test/modules/filter/filter.module).
Drush command terminated abnormally due to an unrecoverable error.   [error]

Everything seems to work fine despite these errors, but I'm not sure if it could affect anything later. I'm curious if there is anything I can do so that it won't occur when I attempt to upgrade my other sites to Drupal 7?

Comments

Comment #1

frazras CreditAttribution: frazras commented 30 January 2013 at 17:00

I'm not sure what causes this but the fix I used was to delete the previous entries:

echo "DELETE FROM filter_format WHERE name = 'Filtered HTML'" | drush sqlc && drush cc all

You could rename them if you really needed them.

Comment #2

axle_foley00 CreditAttribution: axle_foley00 commented 30 January 2013 at 22:26

Does that affect any content that may have had the filter format that you deleted?

Comment #3

frazras CreditAttribution: frazras commented 30 January 2013 at 22:36

Good question!
The actual rendering seemed unchanged but upon edit I get this
http://cl.ly/image/1R0N0i3r3k25/Screen%20Shot%202013-01-30%20at%205.32.3...
the filter selection becomes a required field.

Comment #4

Jon Pugh

he/him

English

Newburgh, NY

CreditAttribution: Jon Pugh commented 1 February 2013 at 17:20

First, using the query in #1 could cause big problems, unless you are sure the new input filter is ready to go to replace it.

Also the conflict is in the "format" (machine name) column, so you should delete based on that since users can rename the "name" column.

I think the problem here is that we already have an input filter named "filtered_html"...

You can't update the machine name of an input filter through the UI.

You cannot update the "format" column in the {filter_format} table because that name matches all of your content tables.

I am afraid the only solution would be to run something like
UPDATE filter_format SET format = 'filtered_html_old', name='Filtered HTML (OLD)' WHERE format = 'filtered_html';
AND
UPDATE filter SET format = 'filtered_html_old' WHERE format = 'filtered_html';

So that you would have your old Filter format to play with. Then you would enable the feature module with the new filtered_html, and ALL OF YOUR CONTENT will be configured to use that new filter format, so you would just have to make sure it works for you.

There's no easy way to update all of your fields tables format columns, since you pick your column names.

Comment #5

Jon Pugh

he/him

English

Newburgh, NY

CreditAttribution: Jon Pugh commented 1 February 2013 at 17:25

oh I am sorry, i didn't realize this was from an upgrade... a similar error was happening for us when installing panopoly on an existing site.

You cannot run drupal install on an existing site, so I don't know how you are doing that!

Comment #6

axle_foley00 CreditAttribution: axle_foley00 commented 12 March 2013 at 05:50

@Jon Pugh what feature module are you referring to? And is it that you are saying we would need to manually update all fields for all content to recognize the new filtered_html or full_html formats?

Comment #7

helmo CreditAttribution: helmo commented 19 June 2013 at 13:25

Version:

7.17

» 7.x-dev

I noticed the same problem after a D6 -> D7 upgrade. In D6 I had a custom install profile, which has no D7 counterpart.

This might be related: #1170362: Install profile is disabled for lots of different reasons and core doesn't allow for that
Especially the 'work-around for current Drupal 7 users:' from the issue summary...

Comment #8

ShaneOnABike CreditAttribution: ShaneOnABike commented 31 July 2013 at 18:01

Actually this happened to me when I moved my site from one server to another. Interesting I _think_ this is happening b/c some enabled features actually exported this, and while it wasn't a problem in the past having had to rebuild the registery probably caused the double inserts? Wouldn't the repair in the code be to use update instead of insert?