I have a lot of nodes that need to get their input format changed - I could do that by hand, but then I won't be done before Christmas 2014.

Where is Drupal storing that information? How can I change the input format in a split second, with an SQL query?


I'm going to preface this answer by saying doing this en-masse presents a potential security risk, especially if you are changing the format to a more lenient set of filters. Text formats modify field output during display, not during save. So for example, any previously-escaped HTML or PHP submitted to a field will straight up render/run if you accidentally or intentionally set the filter to Full HTML or PHP code.

It's for this reason that Drupal doesn't automatically update all existing nodes when you change a text format. The behavior of text formats in similar scenarios is still an open issue.

So again: beware, there be dragons.

With that said, each field stores the text as a column named field_foo_format, where field_foo is the machine name of the field. You'll need to update that column on the tables field_revision_field_foo and field_data_field_foo.

The value for the column is a machine name defined as the column format in the filter_format table. So, updating all the fields would be a matter of a query like:

UPDATE field_revision_foo SET field_foo_format = 'new_format';
UPDATE field_data_foo SET field_foo_format = 'new_format';

for each field that needs changing.

You can determine new_format value here: http://YOURSITE.com/admin/config/content/formats - configure link - number or string in URL is your new_format. Clear cache after updating.

| improve this answer | |
  • 1
    Good answer. You should also do field_cache_clear(); after changes in field_data_... and field_revision_... tables – milkovsky Sep 12 '14 at 10:19

Try this way, by making a loop for all nodes of certain type:

$node = node_load(nid);
$node->body[$node->language][0]['format'] = 'full_html'; // plain_text
| improve this answer | |

Just ran into the same situation as Morten did here, with a D6 => D7 upgrade that apparently didn't finish the input formats.

Took a cruder approach than the answers already here, and wrote a module which ran through the DB schema and updated all the columns which contain the string 'format', replacing the D6 format values (1, 2, 3) with D7 machine names (filtered_html, full_html, plain_text).


Hardcoded to support the mapping of

1 => filtered_html, 
2 => full_html,
3 => plain_text,

Also might try to rewrite fields which are named 'format' (eg "date_format", but if you have a date format with value '2', that's your problem).

| improve this answer | |

For me, the following worked:

update `field_revision_body` set `body_format` = 'new_body_forma' WHERE `bundle` = 'node_type'
update `field_data_body` set `body_format` = 'new_body_forma' WHERE `bundle` = 'node_type'

Of course you must change the new_body_forma and the node_type

| improve this answer | |
  • This worked like a charm, but i had to clear the caches to see the effect. Thank you. – shasi kanth Mar 7 '18 at 14:16

You can use the following code, if you have the entity.module installed.

// I'm using node_save($node); 
$wrapper = entity_metadata_wrapper('node', $node->nid); 
$wrapper->body->set(array('value' => body_text, 'format'=>'full_html'));
| improve this answer | |

Presumably you will want to know which fields need updating, possibly to do some logging or checks on the data. To do this, get all table and column names that contain a _format column:

select distinct TABLE_NAME, column_name
from information_schema.columns
where TABLE_SCHEMA = 'my_drupal_database_name' and column_name like '%_format';

Armed with this data you can build separate queries from those values. Check the output first; you may need to remove some entries that do not pertain to content/revisions. I recommend using a regex-capable editor to build the queries. I turned the data into a big select [...] union statement and then ran update queries against it.

Using this approach saved me quite some time when I needed to update thousands of nodes/revisions. Remember to clear the field cache (NOT covered by drush cc all!):


Or with drush:

drush sqlq "truncate table cache_field;"

Also removing text filter

If you are also retiring a text filter, afterwards you will need to change the default text format for CTs that had fields that used it. If you don't do this, your users will get permission denied messages within the fields that used the old_format. I did this query to find the culprits:

select * from field_config_instance where `data` LIKE '%old_format%';

To make the changes, I found it easier to use the interface to visit each field settings page and press Save (the data is stored as longblob and was unwieldy to search and replace due to the better formats module data injections). Even fields that had Text processing set to Plain text contained the old_format! For the fields that have Text processing set to Filtered text (user selects text format), you will additionally need to select a new default value and press Save.

You should clear the filter cache after removing a filter (again, not covered by drush cc all!):

cache_clear_all('*', 'cache_filter', TRUE);

Or with drush:

drush sqlq "truncate table cache_filter;"
| improve this answer | |
update field_revision_body set body_format = 'full_html' WHERE bundle IN ('book','page');
update field_data_body set body_format = 'full_html' WHERE bundle IN ('book','page');

did the trick for me. Don't forget to clear caches

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.