Change a string column into a textarea column

Owned by svitlana (Deactivated)
Last updated: Dec 21, 2018
2 min read



Introduction 

Hi,

I got a column 'description' and it's current type is 'string'.
I need to provide more space for users to enter addtional data, and therefore I would like to convert it from 'string' into 'textarea',
but when I change the configuration I'm getting an error.

How can I convert the type ?

 John.

 

Step-by-step guide

Although the TGE addon is creating table and columns automatically, it is not guaranteed that it can alter the configuration of the table in the database. The addon will do its best, but depending on the existing data the database server will probably refuse to change the type of the column to prevent data loss. So changing the type of the column in the grid configuration could work, but we do not advise to do that as results of that operation could vary depending on the database vendor and the existing data.

There is a safe and predictable way to change the column type, but it requires some additional "manual" actions and has to be performed very accurately to prevent data loss. This short guide deals with migrating a column from string to textarea, but that same approach can be used for other column types migrations.

 

  1. In your grid configuration, add a new column with type=textarea - with another id. This will add a new column to the database table.

  2. Compose and run SQL query(ies) which would transfer the data from the original string column to the new column. Check that it worked correctly.

If you need the name of the column to be the same after the type migration (for example, there are other applications that use that table and that exact column and renaming it is not an option), the scenario is more complicated

  1. Lets say you have a column 'description' in your grid and you want to change its type in grid configuration from 'string' to 'textarea'

  2. In your grid configuration, add a new column with type=textarea - with another id, say 'description_new'. This will add a new column 'description_new' to the database table.

  3. Compose and run SQL query(ies) which would transfer the data from the original string column 'description' to the new column 'description_new'. Check that it worked correctly.

  4. Make sure no one is using the TGE add-on at the moment as we are going to change the grid configuration and do some manual schema update operations in the database directly. If anyone is editing grid content at that moment - the data could be lost.

  5. Update grid configuration:

    1. Remove the new column 'description_new'

    2. Update the definition of the column description. Set the type to 'textarea'

  6. Go to the database and update the table definition

    1. Drop the column 'description' (we already moved the data to the new column 'description_new' which has the right column type)

    2. Rename the column 'description_new' to 'description'

  7. Check that it works as expected. Now you can let others use TGE

  8. Enjoy





Alternative step-by-step guide

Provided by one of the TGE users.



Workaround to change size of columns from string to text area:

  1. Modify the idalko table grid field configuration for desired fields from string to textarea.

  2. Bring jira app offline (shutdown) and then alter corresponding database table field datatypes from varchar(255) to TEXT.

  3. Restart jira

  4. Existing data will be retained and newly created issues will accept multiline text.