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


Dev-Bookmarks Logo

Bookmarks.dev - Open source Bookmark Manager for Developers & Co. See the How To guide and use our Add to Bookmarks.dev bookmarklet or chrome extension to help you get started. Share your favorites with the community and they will be published 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 www.bookmarks.dev. 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 Bookmarks.dev 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(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 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:

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.

Subscribe to our newsletter for more code resources and news

Adrian Matei

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

Angular material expansion panel and accordion example

Presents angular material expansion panel and accordion in action at http://www.bookmarks.dev with source code and notes Continue reading