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?
Doing this en-masse could present 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. 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.
For this reason, 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. (See Text formats should throw an error and refuse to process if a plugin goes missing, which is still open on May 31, 2021).
With that said, each field stores the text field_machine_name_format, where field_machine_name is the field machine name. That column should be updated field_revision_field_machine_name and field_data_field_machine_name tables.
The value for the column is a machine name defined as the format column in the filter_format table. Updating all the fields would be a matter of running a query like the following, for each field that needs changing.
UPDATE field_revision_foo SET field_foo_format = 'new_format';
UPDATE field_data_foo SET field_foo_format = 'new_format';
You can determine the value of new_format value from /admin/config/content/formats, from the Configure link.
field_cache_clear(); after changes in field_data_... and field_revision_... tables
Commented
Sep 12, 2014 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
node_save($node);
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).
https://gist.github.com/xurizaemon/9824872
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).
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
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!):
field_cache_clear();
Or with drush:
drush sqlq "truncate table cache_field;"
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;"
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'));
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
