A string residing in a mysql column is failing to get inserted in an exact same column on redshift stating DDL length error

Question

A string that goes into Mysql string column varchar(255) should ideally go to a redshift column of varchar(255) is my understanding. (utf8 charset)

I am stuck at a use case where this is failing.

I have a sample string of length 255 character,

'Test data: Around 23:43 IST on 12-10-20, a ABC event was announced due to seller contact number found missing from abc. The required field is showing empty with message on screen “Bad Response/err1 9 error in the comput”, and also unable to activate signs' => length 255

Mysql

CREATE TABLE `test_varchar255` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`token` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


insert into test_varchar255 values (1, 'Test data: Around 23:43 IST on 
12-10-20, a ABC event was announced due to seller contact number found 
missing from abc. The required field is showing empty with message on 
screen “Bad Response/err1 9 error in the comput”, and also unable to 
activate signs')

Insert Succeded

Redshift

CREATE TABLE test_varchar255 (
id                   BIGINT DEFAULT NULL encode mostly32,
string_01            VARCHAR(255) DEFAULT NULL ENCODE LZO
)


 insert into test_varchar255 values (1, 'Test data: Around 23:43 IST on 
12-10-20, a ABC event was announced due to seller contact number found 
missing from abc. The required field is showing empty with message on 
screen “Bad Response/err1 9 error in the comput”, and also unable to 
activate signs')

ERROR: value too long for type character varying(255) Query failed PostgreSQL said: value too long for type character varying(255)

Trying to figure out the exact issue, any help?

Answer 1

varchar(255) means that you have a character string of variable length, having 255 as the maximum length. However, it is important to note that if there is an encoding, which uses 3 bytes for a character (like utf8), then you have 256 * 3 = 768 bytes. If your other column encodes characters into two bytes, then it can store a maximum of 256 * 2 = 512 bytes, hence, you try to store a string of the size of 768 bytes into a place where it has a maximum size of 512 bytes. So, the thing you experienced is only the symptom.

The effective problem's nature depends on your actual situation. If you need to show characters, which cannot be represented by LZO, then change the encoding of the target table. If there are no such characters, then make sure your string is well represented in LZO before you try to store it.