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

Dev-Bookmarks Logo

(P) - Open source Bookmarks and Code Snippets Manager for Developers & Co. See our How To guides to help you get started. Public Bookmarks Repo on Github - Star

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 For a setup of the environment you can see the wiki article Keycloak MySQL Setup

This all started with an error thrown by the Chrome extension - Save to when selecting 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(
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(
	at com.mysql.jdbc.MysqlIO.sendCommand(
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(
	at com.mysql.jdbc.ConnectionImpl.execSQL(
	at com.mysql.jdbc.PreparedStatement.executeInternal(
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(
	at com.mysql.jdbc.PreparedStatement.executeUpdate(
	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(
	... 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


  • 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 highly uninterpretable - I got a pretty long lists of columns because Keycloak uses lots of tables. So I needed a way to grep the result. You can do that 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)

We can 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:

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.


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

Subscribe to our newsletter for more code resources and news

Adrian Matei

Adrian Matei
Life force expressing itself as a coding capable human being

Pass data through window object history in Angular navigation

Code snippet showing how to add and retrieve data from window object history in Angular Continue reading