Modify column length in MySql

Question

I'm issuing the following command:

ALTER TABLE `table_name` MODIFY `column_name` VARCHAR(1022)

At the moment it's VARCHAR(1024), however I'm getting this error:

Error Code: 1265 Data truncated for column mysql

I guess it's because some rows are longer than 1022 characters. When I add IGNORE to the command, the command finishes successfully but these rows still have 1024 characters, mysql did not remove the last 2 characters to make it 1022, is there any way to force this so that to ensure that all rows end up with the new column size?

This is my table:

CREATE TABLE `e_entity` (
`OID` int(11) NOT NULL AUTO_INCREMENT,
`E_E_OID` int(11) DEFAULT NULL,
`UNIQUE_IDX` int(11) NOT NULL,
`APP_OID` int(11) NOT NULL,
`META_OID` int(11) NOT NULL,
`STORE_DATE` datetime NOT NULL,
`REL_DISPLAY` varchar(1024) NOT NULL,
`SINDEX01` varchar(1024) NOT NULL,
`SINDEX02` varchar(1024) NOT NULL,
`SINDEX03` varchar(1024) NOT NULL,
`SINDEX04` varchar(1024) NOT NULL,
`SINDEX05` varchar(1024) NOT NULL,
`SINDEX06` varchar(1024) NOT NULL,
`SINDEX07` varchar(1024) NOT NULL,
`SINDEX08` varchar(1024) NOT NULL,
`SINDEX09` varchar(1024) NOT NULL,
`SINDEX10` varchar(1024) NOT NULL,
`NINDEX01` double NOT NULL,
`NINDEX02` double NOT NULL,
`NINDEX03` double NOT NULL,
`NINDEX04` double NOT NULL,
`NINDEX05` double NOT NULL,
`NINDEX06` double NOT NULL,
`NINDEX07` double NOT NULL,
`NINDEX08` double NOT NULL,
`NINDEX09` double NOT NULL,
`NINDEX10` double NOT NULL,
`DINDEX01` datetime NOT NULL,
`DINDEX02` datetime NOT NULL,
`DINDEX03` datetime NOT NULL,
`DINDEX04` datetime NOT NULL,
`DINDEX05` datetime NOT NULL,
`DINDEX06` datetime NOT NULL,
`DINDEX07` datetime NOT NULL,
`DINDEX08` datetime NOT NULL,
`DINDEX09` datetime NOT NULL,
`DINDEX10` datetime NOT NULL,
`FREETEXT` mediumtext NOT NULL,
`UID` int(11) DEFAULT NULL,
PRIMARY KEY (`OID`),
KEY `App_Parent` (`META_OID`),
KEY `RelDisplay` (`REL_DISPLAY`),
KEY `sindex01` (`META_OID`,`SINDEX01`(64)),
KEY `sindex02` (`META_OID`,`SINDEX02`(64)),
KEY `sindex03` (`META_OID`,`SINDEX03`(64)),
KEY `sindex04` (`META_OID`,`SINDEX04`(64)),
KEY `sindex05` (`META_OID`,`SINDEX05`(64)),
KEY `sindex06` (`META_OID`,`SINDEX06`(64)),
KEY `sindex07` (`META_OID`,`SINDEX07`(64)),
KEY `sindex08` (`META_OID`,`SINDEX08`(64)),
KEY `sindex09` (`META_OID`,`SINDEX09`(64)),
KEY `sindex10` (`META_OID`,`SINDEX10`(64)),
KEY `nindex01` (`META_OID`,`NINDEX01`),
KEY `nindex02` (`META_OID`,`NINDEX02`),
KEY `nindex03` (`META_OID`,`NINDEX03`),
KEY `nindex04` (`META_OID`,`NINDEX04`),
KEY `nindex05` (`META_OID`,`NINDEX05`),
KEY `dindex01` (`META_OID`,`DINDEX01`),
KEY `dindex02` (`META_OID`,`DINDEX02`),
KEY `dindex03` (`META_OID`,`DINDEX03`),
KEY `dindex04` (`META_OID`,`DINDEX04`),
KEY `dindex05` (`META_OID`,`DINDEX05`),
KEY `nindex06` (`META_OID`,`NINDEX06`),
KEY `nindex07` (`META_OID`,`NINDEX07`),
KEY `nindex08` (`META_OID`,`NINDEX08`),
KEY `nindex09` (`META_OID`,`NINDEX09`),
KEY `nindex10` (`META_OID`,`NINDEX10`),
KEY `dindex06` (`META_OID`,`DINDEX06`),
KEY `dindex07` (`META_OID`,`DINDEX07`),
KEY `dindex08` (`META_OID`,`DINDEX08`),
KEY `dindex09` (`META_OID`,`DINDEX09`),
KEY `dindex10` (`META_OID`,`DINDEX10`),
KEY `E_E_OID` (`E_E_OID`)
) ENGINE=InnoDB AUTO_INCREMENT=469158 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

You may try to trim values before altering column. `UDATE 'table_name' SET 'column_name'=LEFT('column_name', 1022)` — Aycan Yaşıt, Oct 19 '16 at 05:36

score 3 · Accepted Answer · answered Oct 19 '16 at 05:46

I guess that your MySQL server is running in SQL strict mode.

There is mention about effects of this mode on ALTER TABLE execution:

When you change a data type using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.

This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE

I would try to change SQL mode at runtime based on this (you may need SUPER privilege - not sure):

To change the SQL mode at runtime, set the global or session sql_mode system variable using a SET statement:

SET SESSION sql_mode = 'modes';

Then repeat your ALTER statement.

You mean `SET SESSION sql_mode = ''` ? – Rafael Oct 19 '16 at 06:36 — Rafael, Oct 19 '16 at 06:36

Modify column length in MySql

1 Answers1