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:
- I start by building out a complex set of content types on my local machine.
- I copy the database over to a staging site periodically so I have a stable copy and so other people can do other things.
- I forget that I added some fields on my localhost.
- 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.)
- I then see that my field is missing - doh.
- 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:
- Backup_migrate doesn't drop all the tables when you restore a database so the
field_revision_field_zzz tables are not removed.
- 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_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
- drop all the tables on your other site(s)
- go to install.php and create a blank drupal site
- enable backup_migrate
- now when you restore your clean db, you won't have all that baggage
- 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.