MySQL CONCAT_WS Error – Data Too Long Issue

concaterrorsMySQLmysql-5.5

I've got a simple table with several columns and I'm trying to merge two of them into one. I'm working with MySQL 5.5 and I can reproduce the error one a specific data set by running the following (anonymized) query through MySQL Workbench:

UPDATE person SET a = CONCAT_WS('\n', a, b)

It clearly produces the following response message:

Error Code: 1406. Data too long for column 'a' at row 34

Because both a and b are VARCHAR(200) the response is fair, but I would expect and hope that since it produced an error, no changes would be commited. However, apparently it does. I'm not able to reproduce it on a smaller dummy dataset. I am using auto commit.

Am I simply wrong in my assumption that erronous queries don't change any values? Could it be some feature of either auto commit, or other setup of my MySQL server?

Please ask if any obvious detail is missing from my specification.

Best Answer

I would expect and hope that since it produced an error, no changes would be commited

The reason is in Strict SQL mode.

When error (instead of warning) occures, only the rest of rows is ignored, rows already updated do not ROLLBACKed.

See Strict SQL Mode:

For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.

what do they mean by "single-row statements"?

It is a statement which affects strictly one row. By WHERE conditions and/or by LIMIT borders. If warning converted to error occures, the row in update is not altered.

both a and b are VARCHAR(200)

This means that concatenated value can be up to 402 symbols. So use 

UPDATE person 
SET a = LEFT(CONCAT_WS('\n', a, b), 200); -- truncate if too long

or 

UPDATE person 
SET a = CONCAT_WS('\n', a, b) 
WHERE LENGTH(CONCAT_WS('\n', a, b)) <= 200; -- do not update if too long
Related Question