2

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 |
  • Alas, nothing jumps out as being the cause. – Rick James Jul 29 '16 at 22:15
  • What happens if you try and set it to null first? – Sir Swears-a-lot Jul 31 '16 at 9:35
  • Check the single quotes for unusual characters and remove quotes around customer_id if possible. – Sir Swears-a-lot Jul 31 '16 at 9:39
  • Nope, NULL has the same problem: mysql> update customer set customer_name = NULL where customer_id = 15484; ERROR 1406 (22001): Data too long for column 'customer_name' at row 1 – RyanH Jul 31 '16 at 23:13
2

EDIT:

To change the mode

This can be done in two ways:

Open your "my.ini" file within the MySQL installation directory, and look for the text "sql-mode". Find:

Code:

Set the SQL mode to strict

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Replace with:

Code:

Set the SQL mode to strict

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Or

You can run an SQL query within your database management tool, such as phpMyAdmin: Code:

SET @@global.sql_mode= '';

From post:https://stackoverflow.com/questions/15949038/error-code-1406-data-too-long-for-column-mysql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.