DROP CONSTRAINT failing in update

Had the same problem and I agree with the fix, here is a patch:

--- date_api.install	Mon Jun  8 15:42:25 2009
+++ date_api.install.new	Wed Jul 29 20:20:31 2009
@@ -491,8 +491,8 @@
   }
   // Rename existing table and index.
   else {
+    db_drop_unique_key($ret, 'date_format', 'format');
     db_rename_table($ret, 'date_format', 'date_formats');
-    db_drop_unique_key($ret, 'date_formats', 'format');
     db_add_unique_key($ret, 'date_formats', 'formats', array('format', 'type'));
   }
 

Marked as duplicate:
http://drupal.org/node/535270

I agree that the other issue is different - not a duplicate. This issue was marked as "minor" originally so I'm putting it back (is that correct? - are there any other side effects that result from this, or is just an error on the screen?).

By the way, I couldn't reproduce this on MySQL, and from the format of the above queries it looks like they came from Postgres. Maybe this bug is specific to Postgres.... The proposed fix looks like it makes sense - can you submit it as a patch?

Attached hawk259's patch as a file.

I can confirm this fixes the problem on Postgres for me as well.

Oh, and although the effect is somewhat minor, it's not just an error, the end result is that there are two unique constraints on date_formats for (format, type), one called date_formats_format_key and date_format_format_key. This may have slight performance implications (double-indexed) and may lead to trouble with future updates that assume that there's only the one constraint.

Here's what the Postgres table definition looked like after the failure:

drupal-mins=> \d date_formats
                                 Table "public.date_formats"
 Column |          Type          |                         Modifiers
--------+------------------------+------------------------------------------------------------
 dfid   | integer                | not null default nextval('date_format_dfid_seq'::regclass)
 format | character varying(100) | not null
 type   | character varying(200) | not null
 locked | smallint               | not null default 0
Indexes:
    "date_format_pkey" PRIMARY KEY, btree (dfid)
    "date_format_format_key" UNIQUE, btree (format, type)
    "date_formats_formats_key" UNIQUE, btree (format, type)
Check constraints:
    "date_format_dfid_check" CHECK (dfid >= 0)

At this point I think it may have happened to enough people that a future update hook should search for a constraint named date_format_format_key and remove it if it's there, just to be safe.

This is definitely not a problem for MYSQL, I have done numerous updates with no problem in MYSQL. And I also checked whether MYSQL ends up with duplicate keys and it does not, so that issue also does not seem to affect MYSQL.

I will add the above patch, but am not sure about what a patch to look for duplicate keys might look like, since it should not make any changes in MYSQL, which ends up correctly configured. If someone provides a patch for that and confirms it does not break anything in MYSQL I will commit it.

I don't believe MySQL does named constraints, so it's a NOOP on MySQL I guess.

I'm not sure that Drupal provides a way to detect whether a constraint exists, so you might just have to blindly drop it in an update hook and if it fails, ignore it. You could do something like:

...
$drop_res = db_drop_unique_key($ret, 'date_format', 'format');
if ($drop_res['success']) {
  $ret[] = $drop_res;
}
...
return $ret;

So it only shows up in the update log if it was successful.

Sorry that last code was nonsense, better:

...
$drop_res = array();
db_drop_unique_key($drop_res, 'date_format', 'format');
if ($drop_res[0]['success']) {
  $ret[] = $drop_res[0];
}
...
return $ret;

Or something along those lines.