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

Codever Logo

(P) Codever is an open source bookmarks and 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 (aka adixchen)

Adrian Matei (aka adixchen)
Life force expressing itself as a coding capable human being

Dynamically expand angular material panel when condition met

Dynamically expand angular material panel when condition met code snippet Continue reading

Copy to clipboard with angular material

Published on October 12, 2022