Changing Custom Field Types in Redmine

At Brick Street, we use Redmine to manage engineering and product management. We host it ourselves on an AWS instance because we're cheap and because we want to integrate it with our other line-of-business systems. We recently upgraded from Redmine 2.1 to 3.3. In Redmine 2.1, we had some custom fields on Issue records that held text to include in our release notes. (We have a program that scans subversion logs for Issues, and then generates release notes by pulling the Redmine text for the issues fixed in the branch.)

In Redmine 2.1, these custom fields are 'text' type, which shows up in the Redmine UI as a single line (an HTML input element). In Redmine 3.3, they added a 'Long Text' type which shows up in the UI as a resizable multiline control (an HTML textarea element).

We wanted to convert the 'text' custom fields to 'long text'. So I played with our preproduction instance and reverse engineered the schema: The custom field definitions are in the CUSTOM_FIELDS table:

MySQL> desc custom_fields;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| type            | varchar(30)  | NO   |     |         |                |
| name            | varchar(30)  | NO   |     |         |                |
| field_format    | varchar(30)  | NO   |     |         |                |
| possible_values | mediumtext   | YES  |     | NULL    |                |
| regexp          | varchar(255) | YES  |     |         |                |
| min_length      | int(11)      | YES  |     | NULL    |                |
| max_length      | int(11)      | YES  |     | NULL    |                |
| is_required     | tinyint(1)   | NO   |     | 0       |                |
| is_for_all      | tinyint(1)   | NO   |     | 0       |                |
| is_filter       | tinyint(1)   | NO   |     | 0       |                |
| position        | int(11)      | YES  |     | NULL    |                |
| searchable      | tinyint(1)   | YES  |     | 0       |                |
| default_value   | mediumtext   | YES  |     | NULL    |                |
| editable        | tinyint(1)   | YES  |     | 1       |                |
| visible         | tinyint(1)   | NO   |     | 1       |                |
| multiple        | tinyint(1)   | YES  |     | 0       |                |
| format_store    | text         | YES  |     | NULL    |                |
| description     | text         | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)

The field_format value for text fields is 'string'. The field_format value for long text fields is 'text'.

The actual values of the custom fields are stored in the CUSTOM_VALUES table:

MySQL>; desc custom_values;
+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| id              | int(11)     | NO   | PRI | NULL    | auto_increment |
| customized_type | varchar(30) | NO   | MUL |         |                |
| customized_id   | int(11)     | NO   |     | 0       |                |
| custom_field_id | int(11)     | NO   | MUL | 0       |                |
| value           | mediumtext  | YES  |     | NULL    |                |
+-----------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

The customized_id field is a foreign key to the record in the ISSUES table. The custom_field_id field is a foreign key to the CUSTOM_FIELDS record. The value field stores the custom field value. This query will show you all the custom field values for Issue 2001:

select * from custom_values where customized_id = 2001;

So it turns out that you can change the type of a custom field value by updating the custom_field_id field of the CUSTOM_VALUES record. In our Redmine, we changed the text fields to long text by following this procedure:

1. Define a new long text field in the UI.
2. Look in the CUSTOM_FIELDS table to get the id of the text field (e.g. id 5) and the new long text field (e.g. id 15).
3. Run this update query to change types of all the custom field values:
   

   update CUSTOM_VALUES set custom_field_id = 15 where custom_field_id = 5;
   

   
   
4. Delete the custom field definition of the old field (id 5) in the UI.

This approach works great for Text custom fields. List custom fields are harder because the custom field definition includes a list of possible values (in the CUSTOM_FIELDS.possible_values). We have a number of multiselect fields that contain lists of release version. My next task is to convert these to the new Version custom field type.