How to modify the size of a column in MySql from command line

This blog post presents the steps required to connect to the MySql database from the command line and modify the size of a column in a table. The example is based on the MySql database that is backing Keycloak to run www.bookmarks.dev. For a more detailed setup you can see the wiki article Keycloak MySQL Setup

This all began with an error thrown by the Chrome extension - Save to Bookmarks.dev - I was trying to select a very long text to add the bookmark's description. The error in the Keycloak logs was the following:

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'DETAILS_JSON' at row 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3971)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
    at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
    ... 91 more

Fortunately in the this case the error in the logs is clear enough - the column's size seemed too short to handle the bigger selection.

So let's dig in to correct the problem.

Connect to the database

First connect to the database

mysql -u keycloak -p keycloak 

where

• keycloak in -u keycloak is the username
• the last keycloak is the name of the database

You will be asked for the user's password (-p).

Find the right table

The column name DETAILS_JSON was mentioned in logs. To find out which table this columns belongs to I listed the
columns of all tables from the keycloak database:

select * from information_schema.columns
where table_schema = 'keycloak'
order by table_name,ordinal_position;

The result was not easy to interpret - I got a pretty long lists of columns, mainly because Keycloak uses lots of tables. So I needed a way
to grep the result. Apparently you can grep in mysql shell by issuing the following command

pager grep DETAILS_JSON;

and then run the above command again:

mysql> select * from information_schema.columns where table_schema = 'keycloak' order by table_name,ordinal_position;
| def           | keycloak     | EVENT_ENTITY                  | DETAILS_JSON                 |                3 | NULL                        | YES         | varchar   |                     5550 |                   5550 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | varchar(5550) |            |       | select,insert,update,references |                |                       |
519 rows in set (0.00 sec)

I could now clearly identify the table as EVENT_ENTITY

Change the column's size

First we need to disable the grep/pager to be able to see anything else which does not contain the DETAILS_JSON text in it.
You can do that by issuing the following command:

mysql> nopager;

Then display the columns of the EVENT_ENTITY table to make sure the searched column is there:

mysql> show columns from EVENT_ENTITY;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| ID           | varchar(36)   | NO   | PRI | NULL    |       |
| CLIENT_ID    | varchar(255)  | YES  |     | NULL    |       |
| DETAILS_JSON | varchar(2550) | YES  |     | NULL    |       |
| ERROR        | varchar(255)  | YES  |     | NULL    |       |
| IP_ADDRESS   | varchar(255)  | YES  |     | NULL    |       |
| REALM_ID     | varchar(255)  | YES  |     | NULL    |       |
| SESSION_ID   | varchar(255)  | YES  |     | NULL    |       |
| EVENT_TIME   | bigint(20)    | YES  |     | NULL    |       |
| TYPE         | varchar(255)  | YES  |     | NULL    |       |
| USER_ID      | varchar(255)  | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

We can see now it has a size of 2550 characters. We'll just more than double that by altering the table:

mysql> ALTER TABLE EVENT_ENTITY MODIFY DETAILS_JSON VARCHAR(5550) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Run the above command again to make sure the column has now the new size.

Conclusion

This post serves me as a reminder on how to connect to a MySql database and modify a column.