Data too long for column MYSQL Error

I've been pouring over threads regarding this issue, but ours seems unique in that it's not an issue with the query but the row itself; or at least I couldn't find a similar topic addressing how to fix the row.

A simple query like this:

> update customer set customer_name = 'Health Net of CA' where customer_id = '15484'; 

Results in:

ERROR 1406 (22001): Data too long for column 'customer_name' at row 1 

Checking the character length:

mysql> select char_length(customer_name) from customer where customer_id = '15484'; +----------------------------+ | char_length(customer_name) | +----------------------------+ |                         54 | +----------------------------+ 1 row in set (0.00 sec) 

Describe shows:

 | customer_name      | varchar(255) | YES  |     | NULL    |                | 

This database was populated using an import. I'm fairly sure strict mode was on but I didn't handle the import myself so I can't say for certain. This table has 39 columns, and most are Varchar(50) or tinyint so it shouldn't be an issue with the row being too big.

Any suggestions on fixing these bad rows?

Update:

SHOW CREATE TABLE customer; | customer | CREATE TABLE `customer` (   `customer_id` int(11) NOT NULL AUTO_INCREMENT,   `customer_name` varchar(255) DEFAULT NULL,   `customer_code` varchar(12) DEFAULT NULL,   `customer_type` text,   `bill_type` text,   `attention` varchar(100) DEFAULT NULL COMMENT 'Attention to who we deal biz with',   `address_id` int(11) DEFAULT NULL,   `b_bad_debt` tinyint(1) DEFAULT '0',   `b_fee_approval` tinyint(1) DEFAULT NULL COMMENT 'boolean flag for Fee Approval 1=set, 0=unset',   `approval_amount` decimal(5,2) DEFAULT NULL,   `notification` varchar(45) DEFAULT NULL COMMENT 'notified customer by email / fax or ftp',   `b_tax_exempt` tinyint(1) DEFAULT '0' COMMENT 'Tax Exempt Flag',   `sales_tax_number` varchar(20) DEFAULT NULL COMMENT 'sales tax/ permit no.',   `b_prepay` tinyint(1) DEFAULT NULL,   `create_date` datetime DEFAULT NULL,   `last_updated` timestamp NULL DEFAULT NULL,   `active` tinyint(4) DEFAULT '1',   `created_by` varchar(45) DEFAULT NULL,   `updated_by` varchar(45) DEFAULT NULL,   `email` varchar(45) DEFAULT NULL,   `email_extra` mediumtext COMMENT 'extra emails as per creation',   `state_tax_code` varchar(2) DEFAULT NULL COMMENT 'this is as CA if CA state and refer to TAX table',   `email_verified_by` varchar(45) DEFAULT 'NA',   `fax_verified_by` varchar(45) DEFAULT 'NA',   `b_always_send` tinyint(1) DEFAULT '0' COMMENT 'there is customer that we need always send',   `b_project_customer` tinyint(1) DEFAULT '0',   `b_exception_list` tinyint(1) DEFAULT '0',   `b_has_inslist` tinyint(1) DEFAULT '0',   `customer_passwrd` varchar(255) DEFAULT NULL,   `delivery_opt` varchar(45) DEFAULT NULL,   `max_fax_pages` int(11) DEFAULT NULL,   `phone` varchar(20) DEFAULT NULL,   `fax` varchar(20) DEFAULT NULL,   `phone_extra` mediumtext,   `phone_second` varchar(20) DEFAULT NULL,   `b_multi_suites` tinyint(1) DEFAULT '0',   `suite_list` varchar(255) DEFAULT NULL,   `b_portal_download` tinyint(1) DEFAULT '0',   `b_no_download` tinyint(1) DEFAULT '0',   PRIMARY KEY (`customer_id`),   KEY `customer_idx` (`customer_code`,`customer_name`) ) ENGINE=InnoDB AUTO_INCREMENT=18870 DEFAULT CHARSET=utf8 | 

Replay

Category: mysql Time: 2016-07-29 Views: 3

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.196 (s). 12 q(s)