Drupal says "There was a problem creating field ZZZ: Table field_data_field_ZZZ already exists"

Question

I've run into this periodically: I'm working on a new site and go to add a field that I've created at some point in the past only to find out that it's not available. When I try to add a 'new' field (usually using the same title & machine name, because they pop up when I start typing), I get this message:

There was a problem creating field ZZZ: Table field_data_field_ZZZ already exists

There are several posts out there on this issue, but none of the solutions really tell the whole story.

Why did this happen and how do I fix it?

Answer 1

Problem

It's possible that something else could cause this, but it doesn't seem too likely.

The problem comes from using backup_migrate to recover or overwrite your database when there is some degree of disparity in the fields in the different databases. Here's one example of how things can get out of sync:

1. I start by building out a complex set of content types on my local machine.
2. I copy the database over to a staging site periodically so I have a stable copy and so other people can do other things.
3. I forget that I added some fields on my localhost.
4. I use backup_migrate to grab the latest copy of the database. (This is where things start to go wrong - it could happen without a dev/staging scenario too, if, for example, you roll-back to an earlier backup of your own database. There are probably many ways things can get out of wack like this.)
5. I then see that my field is missing - doh.
6. When I try to recreate the field I get that rotten message. You tell me it exists, but you won't let me add the rotten field??!?

What's going on

The problem is two-fold:

1. Backup_migrate doesn't drop all the tables when you restore a database so the field_data_field_zzz and field_revision_field_zzz tables are not removed.
2. However the field_config table and the field_config_instance tables are completely replaced. This is where Drupal keeps track of the fields that have been created, so now it has no idea that field_zzz ever existed.

The immediate solution:

Take a look at the known fields (the contents of field_config). Any fields that are not listed there need to be cleared out of the database, if you want to recreate them. Just drop the field_data.. and field_revision.. tables from the database. Now you can go about your business as usual.

..and the haunting:

The problem is that fixing this on your local machine does not fix the problem on other instances of your website. For instance, if you clean things up and push them up to a staging server, that server is not going to delete unused tables either. Then at some point in the future you'll grab a copy of that database and experience the pain all over again. You will google the same query, end up at this post again and call me rotten names for giving you a solution that failed.

The definitive solution:

Frankly, understanding the problem is the biggest step. The second biggest step is to make sure you fix all instances of your site. So once you have a nice clean version of your site, you'll want to

1. drop all the tables on your other site(s)
2. go to install.php and create a blank drupal site
3. enable backup_migrate
4. now when you restore your clean db, you won't have all that baggage
5. repeat on each site (we usually have multiple localhost/dev installs, then a staging and live site)

In reality, there are a lot of reasons this may not be feasible, but now you should know enough to create your own work-around.