Mysql – Data too long for column MYSQL Error

errorsMySQLupdate

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 |

Best Answer

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

Related Question