Error on changing field type: "There is data for this field in the database. The field settings can no longer be changed."

By leepowers on 9 Feb 2011 at 02:07 UTC

There is data for this field in the database. The field settings can no longer be changed.

This is in Drupal 7, stock install, in a custom content type. Trying to convert from an Integer to Float. I know the appropriate MySQL statement to make the change to the column:

alter table field_data_field_bathrooms modify column field_bathrooms_value double;

That will give me the database storage I want.

But, when I try to enter a decimal/float value, Drupal gives me the following error:

Only numbers are allowed in Bathrooms.

So, what's the missing piece? How do I get Drupal to accept decimals for this custom field?

Comments

You have to make another field

wranvaud commented 10 September 2011 at 21:31

Once you start entering a variable of a certain type into a database you cannot change the variable type any more because the database structure should be modified.
I would say you could create a new field in admin/structure/types/manage/*content type*/fields
and this time you say it's a float. Then, well, you can erase the old field and start over with the new one.
I just had this issue and that's how I solved it.

Updates

Alex Andrascu commented 17 October 2011 at 16:18

Subscribe. There has to be a way to edit a field settings after node creation. It was in D6.

Subscribe. Totally agree with

my-family commented 30 October 2011 at 13:54

Subscribe. Totally agree with Alex.

Confusing

greggmarshall

Aurora/Denver, Colorado

commented 18 November 2011 at 17:45

I just ran into this new limitation and have to agree it is a huge problem. I don't know any client that can definitively guarantee the allowed value list won't change while the site is being built. There has to be some way to work around this. Edited to add I realize now that the message doesn't prohibit adding new allowed values. But it is confusing until you just try it anyway.

wow...

pauln600 commented 16 April 2012 at 16:02

I totally agree that not being able to add to the allowed values list is a really serious problem. Had I known about this bug I would never have moved to Drupal 7.

Paul N.

Well, to be fair, Drupal is

earwax commented 16 April 2012 at 16:14

Well, to be fair, Drupal is trying to protect you from ruining the integrity of your keys and values. I just wish they would provide you with a confirmation message, something like, "Are you sure you want to change the current values?".

As I mentioned down below in another response, it is possible to change the values by tricking Drupal 7. I have done this a few times. Yes, it is annoying but maybe this will be fixed in Drupal 9

understandable, but...

pauln600 commented 16 April 2012 at 18:30

With all due respect, I would hope it would be fixed in Drupal 7 - it's a very serious limitation, and IMHO it simply rules out Drupal 7 as a candidate for a lot of clients who would want to maintain option lists themselves without resorting to hacking the database. It's kind of hard to believe that's the only way to do it...

Paul N.

Huh.... You CAN add "allowed values" for option lists

LoMo commented 16 April 2012 at 20:33

Huh.... You CAN add "allowed values", in my experience, you can even change the displayed values. What you don't want to do—and what Drupal helps prevent you from doing—is changing a field in a way that would be incompatible with the data that might already be stored in the field, i.e. making potentially destructive changes. If the changes can be made programmatically, (e.g. you have a text field that you want to change to a number field—integer or decimal—and all content stored in that field is numeric) you could add the new field and loop through the existing content—there are a number of ways this could be done—converting what's in the old field and storing it in the new field (without modifying the old field). Then, if everything went well, you could just delete the old field and set up the content type to display your new field. Of course, if it's a site you are still developing and it has no real content in it yet, you can always just delete and replace the field, so it helps to think things through very well before you have to hassle with modifying data. Also, if before launching a site, you have setup scripts that import data from CSV files or something, you could change the storage type for the field at some point and quite likely not have to modify the setup script (much or at all).

Just to prove this to myself, I just did this on a deployed Drupal 6 site, added three new "allowed values" to a text field with a limited range of allowed values and also set the field to be a select list (instead of a plain text field). This was a change I was planning to make, anyway; so I'm glad I was right and that it was not hard to make the change.

See you at the Drupalcon!

I just did this on a deployed

earwax commented 16 April 2012 at 22:29

LoMo:

I just did this on a deployed Drupal 6 site ...

Now try it on Drupal 7.

Doh! If it won't work in D7,

LoMo commented 16 April 2012 at 22:39

Doh! If it won't work in D7, I hadn't noticed yet, but for this site, when I do finally upgrade it, I guess I'll move the select list to work off a taxonomy rather than "allowed values" text-based select list. Generally, taxonomies have a lot more power, anyway, and certainly have a whole set of new features in D7.

In any case, some of the changes that I was allowed to do just now (and could have made, but carefully chose not to) could totally have broken things, so I understand the decision if this particular kind of change was restricted in D7. OTOH, adding new terms to an options list should be no problem, so adding a module that allows that could only be a GoodThing™. ;-)

See you at the Drupalcon!

LoMo: You are correct that if

earwax commented 17 April 2012 at 16:04

LoMo:

You are correct that if you use a taxonomy list in Drupal 7, then there is no problem adding, modifying, or deleting values in a list. Unfortunately, sometimes it is overkill and a taxonomy is not always needed. But yes, it is a work around. Either way, it is what it is and we just do what we have to do to make this work.

Right. Sometimes it's overkill...

LoMo commented 17 April 2012 at 23:59

And sometimes you have already created it in a way that is not very changeable (I now understand) in D7, even if it would not damage any data. So I agree that a custom module to help modify values without causing damage is a GoodThing™. ;-) Let's think about how to best implement such a utility module.

See you at the Drupalcon!

Lomo: So I agree that a

earwax commented 18 April 2012 at 16:41

Lomo:

So I agree that a custom module to help modify values without causing damage is a GoodThing

This is what is already built into Drupal 7, hence all our complaints about not being able to make the changes. Drupal 7 is trying to protect us from shooting ourselves in the foot, and it is somewhat valid. I was suggesting we do the opposite - create a module that would ALLOW damage to potentially be done. This might sound stupid, but sometimes we need to do this, as people in here are posting. A good example of "use at your own risk" is the Drupal 7 module, http://drupal.org/project/allow_all_file_extensions, that allows ANY types of file extensions to be uploaded. So I think it definitely is possible to submit this as a module and have the mods approve it.

I'm thinking, still attempt to prevent destruction

LoMo commented 19 April 2012 at 01:33

Right. What I'm thinking is not to totally prevent making changes if they *might* be destructive, but to help with adding changes that would likely not cause any issue (e.g. adding additional values) and warn against making changes (e.g. removing values) which would likely cause issues, possibly also providing a method to correct any data that would be affected by a “destructive” change.

See you at the Drupalcon!

Pauln600: I completely agree

earwax commented 16 April 2012 at 20:35

Pauln600: I completely agree with you, and I do not think it will be "fixed" in Drupal 7 because this is what the core developers deemed as important (to them). Maybe we can write a module to just override this message and whatever it is doing to block the changes.

As I mention to many people, Drupal gets you about 80% of the way. The other 20% is all frustration and ripping your hair out.

But almost any reasonable

LoMo commented 16 April 2012 at 20:50

But almost any reasonable change CAN be made—certainly Paul's assertion that users want to maintain option lists (and can't) does not make sense to me. You can add or remove options to/from a select list without issue, for example. A module to help with other changes to a field might be useful, but would be probably a bit "techy" for most users who wouldn't be savvy enough to make the changes to the database directly. OTOH, it's nice to have an admin interface for these kinds of things so you don't have to go to phpMyAdmin or whatever.

I suggest being careful though. The message and the changes Drupal prevents you from making are often not trivial matters. The module that helps work around this should be able to identify whether the data really would be compromised (in many cases it would not), then allow you to make your changes.

See you at the Drupalcon!

strange...

pauln600 commented 25 April 2012 at 14:09

Just coming back to this - now it seems to allow changes to the option lists, except for those that compromise keys already in use... which is very strange, as my first attempts were, according to my memory, blocked for any change. So I'm rather surprised by this - either my memory is at fault or there's some indeterminacy in the process. It does complain if you try to modify keys in use, which I agree makes sense - if you do that, you need to work out any inconsistencies at the database level.

Paul

Agree 100%

marcelomg commented 2 February 2012 at 18:40

Please help us find a solution for this.

The "fix"

earwax commented 29 February 2012 at 01:30

The way I fixed this was by going into the table of the particular field in the MySQL Drupal DB and deleting all values in the table.
Another way, which might work is to:
1) Backup the table for that particular field
2) delete everything from that table
3) fix your values/options in Drupal for that field (Drupal will think that no values have been entered yet)
4) reimport your table with values

The "fix" worked, with an extra step

sstacks commented 28 September 2012 at 05:07

These steps worked, but in addition before importing (step 4), I had to alter the sql file from the table backup to remove a column I was no longer using.

In this case the column was the "Repeating" option on Date a date field. I decided to remove the Repeat functionality.

Shane

I just want to also comment

LasVegasFIT commented 17 January 2017 at 19:35

I just want to also comment on this as the working fix that I had to perform on a production site where we had case managers listed on Drupal 7.

I had to export the list in phpmyadmin from inside the specific table, empty the table from the phpmyadmin>tables>empty option, then go inside drupal 7 under content types > manage fields > go to the specific field , and use the manual entry field , delete and readd what list I wanted, then import the .sql file with the backup table I exported earlier.

After that I tested and everything was working while leaving the prior intact. Thanks for this post, it was the final answer that helped me overcome this problem. I wish they had a module to simplify this instead of having to do a major workaround like this.

Bit of a hack

yul63 commented 13 November 2012 at 06:30

I wanted to change the vocabulary of a taxonomy field and got the "There is data for this field in the database. The field settings can no longer be changed." message.

It's possible to do it by commenting out

  $has_data = field_has_data($field);
  if ($has_data) {
    $form['field']['#description'] = '<div class="messages error">' . t('There is data for this field in the database. The field settings can no longer be changed.') . '</div>' . $form['field']['#description'];
  }

in /modules/field_ui/field_ui.admin.inc. You can remove the comments after making the change to the proprieties of your field.

It worked in this context but I don't know if it might have unforeseen effects. I suggest to back up a site before trying this.

Sandbox module to fix this

ericmulder1980 commented 1 June 2013 at 09:53

https://drupal.org/sandbox/ericmulder1980/2000476

It currently has some issues with Features buth otherwise works fine. The proposed hack from yul63 is something that gets kittens killed.

I'd rather hear it from you than from someone with a sane mind.

Small code snippet to change

japicoder commented 16 July 2013 at 11:31

Small code snippet to change a float field to decimal field. Probably a bit tricky for someone but it works pretty nice.

  // Change type of field 'field_campaign_budget_value' to DECIMAL
  db_query("ALTER TABLE {field_data_field_campaign_budget} CHANGE `field_campaign_budget_value` `field_campaign_budget_value` DECIMAL(10,2)");
  db_query("ALTER TABLE {field_revision_field_campaign_budget} CHANGE `field_campaign_budget_value` `field_campaign_budget_value` DECIMAL(10,2)");

  // Update Fields configuration
  db_query("UPDATE {field_config} SET type='number_decimal' WHERE field_name='field_campaign_budget'");

Just you need to add this code in a module_update_70XX hook function.

I managed in a different way

vishnujayadhevan commented 12 February 2016 at 06:08

I create a list having 20 items. After creating 100+ new nodes of the content type, I had to make some change in one item. So I did the following

Create a backup of the corresponding field table
Delete the content of the table
Modify the content type field by manage field option
Again import the backups to the field table
Update the name by using SQL query

I changed the node__field table as well as node__revision_field table. Both store the field values. So I Confused and did that. Any way its work for me and thanks @ earwax

One Method Could Be To Export and Re-Import the Data

Steve Polito Design commented 13 February 2016 at 16:47

Create a new field that satisfies your needs.
Export all the data from the original field that you no longer can edit.
Import all the data from the original field to the new field.
Delete the original field.

I wrote a tutorial explaining how to do this.

I wrote a description for how

greggles

he/him

English

Denver, Colorado, USA

commented 11 October 2016 at 15:55

I wrote a description for how to do this via some custom queries/code over on Drupal.StackExchange.com.

--
Morris Animal Foundation

Convert Content

shoreshotweb commented 20 January 2018 at 17:24

You can also create a new Content Type with the fields the way you want them. Install Node Convert and use this tool to convert your content from one type to the other. (In Drupal 8, the tool is called Convert Nodes.)