1406 Data too long for column 'prefix' at row 1

Good Day
I have similar issue as reported by another user however mine is in step 6 and seems to be related to “prefix”. I removed all prefixes longer than 4 characters in M1 via Admin, however I am still getting the error below. Is there a table I should check as well?
2020/07/02 19:23:20 [error] [ub_data_migration] CDbCommand failed to execute the SQL statement: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column ‘prefix’ at row 1. The SQL statement executed was: UPDATE `customer_address_entity` SET `entity_id`=:yp0, `increment_id`=:yp1, `parent_id`=:yp2, `created_at`=:yp3, `updated_at`=:yp4, `is_active`=:yp5, `city`=:yp6, `company`=:yp7, `country_id`=:yp8, `fax`=:yp9, `firstname`=:yp10, `lastname`=:yp11, `middlename`=:yp12, `postcode`=:yp13, `prefix`=:yp14, `region`=:yp15, `region_id`=:yp16, `street`=:yp17, `suffix`=:yp18, `telephone`=:yp19, `vat_id`=:yp20, `vat_is_valid`=:yp21, `vat_request_date`=:yp22, `vat_request_id`=:yp23, `vat_request_success`=:yp24 WHERE `customer_address_entity`.`entity_id`=’5539′

4 answers

Profile photo of Mall Staff 184060.00 $tone July 3, 2020

Hi Stuart,

1406 Data too long for column ‘prefix’ at row 1. The SQL statement executed was: UPDATE `customer_address_entity` SET

That was because your M1 database had at least one data record which had the bad data in the value of the customer_address_entity.prefix field. Max length allowed in the string value of that field in M2 is 40 characters: https://prnt.sc/tb1x8o
You can run the following SQL query in your M1 database to look for that data record:

Select * From customer_address_entity Where length(prefix) > 40;

And then, you have to re-update the value of the ‘prefix’ field on all found data records to reduce the length of the string value.

Once done, you can continue with the data migration in the step you mentioned.
PS. Since our team is off during the weekend, we’re sorry for a certain delay in response if you reach out to us during this time. 


Profile photo of Stuart Roberts 30.00 $tone July 3, 2020

OK I managed to trace the ‘prefix’ fields causing the error with:
Select * From customer_address_entity_varchar Where length(VALUE) > 40 ORDER BY `entity_id` ASC
Thanks for you assistance.

Profile photo of ubdev Staff 98350.00 $tone July 6, 2020

Hi Stuart, 
We’re sorry for getting back to you this late as we’ve just been back from our weekend. 
Yes, please move forward with the migration process at your end. In case you come across any other issue, please let us know here.
Ubertheme team


Please login or Register to Submit Answer

Written By