How to modify the size of a column in a Mysql database from command line
(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 repos on Github ⭐🙏
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.codever.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
keycloakin-u keycloakis the username- the last
keycloakis 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.
