Bug #41686 PROCESSLIST : Data too long for column 'STATE'
Submitted: 22 Dec 2008 23:19 Modified: 23 Dec 2008 23:31
Reporter: Serdar S. Kacar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.30, 6.1 bzr OS:Any (Solaris 10u6, Linux)
Assigned to: CPU Architecture:Any

[22 Dec 2008 23:19] Serdar S. Kacar
Description:
On Solaris platform, if one tries to log current process info in a trigger, this error pops.

my.cnf related variables :
default-character-set=utf8
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Clients are 
- mysql command line client on localhost
- MySQL Query browser on a remote Windows machine

How to repeat:
DROP TABLE IF EXISTS `test`.`dbgproc`;
CREATE TABLE  `test`.`dbgproc` (
  `LBL` varchar(32) NOT NULL DEFAULT '',
  `UPDDT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ID` bigint(4) NOT NULL DEFAULT '0',
  `USER` varchar(16) NOT NULL DEFAULT '',
  `HOST` varchar(64) NOT NULL DEFAULT '',
  `DB` varchar(64) DEFAULT NULL,
  `COMMAND` varchar(16) NOT NULL DEFAULT '',
  `TIME` bigint(7) NOT NULL DEFAULT '0',
  `STATE` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`LBL`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`a`;
CREATE TABLE  `test`.`a` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `VL` int(10) unsigned NOT NULL,
  `X` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

delimiter $$

DROP TRIGGER IF EXISTS test.`a_AFTER_INSERT` $$
CREATE TRIGGER test.`a_AFTER_INSERT` AFTER INSERT ON test.`a`
  FOR EACH ROW
    REPLACE dbgproc SELECT  'test.a' AS LBL,  NOW(),  A.`ID`,  A.`USER`,  A.`HOST`,  A.`DB`,  A.`COMMAND`,  A.`TIME`,  A.`STATE`
    FROM information_schema.`PROCESSLIST` A WHERE A.`ID` = CONNECTION_ID();
$$

delimiter ;

INSERT INTO test.a VALUES (NULL, 2, 2);

Strangely, error pops even STATE field is not refenced. E.g. try " '' AS `STATE`" instead of " A.`STATE` ".

Besides REPLACE, CREATE ... SELECT cause the same error.

Work around is first storing into user variables :
delimiter $$
DROP TRIGGER IF EXISTS test.`a_AFTER_INSERT` $$
CREATE TRIGGER test.`a_AFTER_INSERT` AFTER INSERT ON test.`a`
  FOR EACH ROW
  BEGIN
  SELECT  A.`ID`, A.`USER`, A.`HOST`, A.`DB`, A.`COMMAND`, A.`TIME`, A.`STATE`
    INTO @ID, @USER, @HOST, @DB, @COMMAND, @TIME, @STATE
    FROM information_schema.`PROCESSLIST` A WHERE A.`ID` = CONNECTION_ID();
  REPLACE dbgproc SELECT 'test.a' AS LBL, NOW(),
    @ID, @USER, @HOST, @DB, @COMMAND, @TIME, @STATE;
  END;
$$
delimiter ;

Note: This bug is not repeatable in windows platform.
[23 Dec 2008 9:29] Sveta Smirnova
Thank you for the report.

I can not repeat this error on Solaris platform. Please paste exact error message you get and indicate hardware (SPARC or x86) you run Solaris on.
[23 Dec 2008 10:53] Serdar S. Kacar
configuration file

Attachment: my.cnf (text/plain), 2.55 KiB.

[23 Dec 2008 10:57] Serdar S. Kacar
Solaris hardware is X86.

bash-3.00# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS `test`.`dbgproc`;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE  `test`.`dbgproc` (
    ->   `LBL` varchar(32) NOT NULL DEFAULT '',
    ->   `UPDDT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   `ID` bigint(4) NOT NULL DEFAULT '0',
    ->   `USER` varchar(16) NOT NULL DEFAULT '',
    ->   `HOST` varchar(64) NOT NULL DEFAULT '',
    ->   `DB` varchar(64) DEFAULT NULL,
    ->   `COMMAND` varchar(16) NOT NULL DEFAULT '',
    ->   `TIME` bigint(7) NOT NULL DEFAULT '0',
    ->   `STATE` varchar(64) DEFAULT NULL,
    ->   PRIMARY KEY (`LBL`)
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> DROP TABLE IF EXISTS `test`.`a`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE  `test`.`a` (
    ->   `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `VL` int(10) unsigned NOT NULL,
    ->   `X` int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> delimiter $$
mysql>
mysql> DROP TRIGGER IF EXISTS test.`a_AFTER_INSERT` $$
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TRIGGER test.`a_AFTER_INSERT` AFTER INSERT ON test.`a`
    ->   FOR EACH ROW
    ->     REPLACE dbgproc SELECT  'test.a' AS LBL,  NOW(),  A.`ID`,
    ->       A.`USER`,  A.`HOST`,  A.`DB`,  A.`COMMAND`,  A.`TIME`,  A.`STATE`
    ->       FROM information_schema.`PROCESSLIST` A
    ->       WHERE A.`ID` = CONNECTION_ID();
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> INSERT INTO test.a VALUES (NULL, 2, 2);
ERROR 1406 (22001): Data too long for column 'STATE' at row 1
[23 Dec 2008 10:59] Sveta Smirnova
Thank you for the feedback.

Could you also send output of SELECT * FROM information_schema.`PROCESSLIST` where id=CONNECTION_ID();, so we can be sure there is nothing unusual in it.
[23 Dec 2008 11:04] Serdar S. Kacar
Here is the strange version where there is no explicit reference to column STATE :

bash-3.00# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS `test`.`dbgproc`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE  `test`.`dbgproc` (
    ->   `LBL` varchar(32) NOT NULL DEFAULT '',
    ->   `UPDDT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   `ID` bigint(4) NOT NULL DEFAULT '0',
    ->   `USER` varchar(16) NOT NULL DEFAULT '',
    ->   `HOST` varchar(64) NOT NULL DEFAULT '',
    ->   `DB` varchar(64) DEFAULT NULL,
    ->   `COMMAND` varchar(16) NOT NULL DEFAULT '',
    ->   `TIME` bigint(7) NOT NULL DEFAULT '0',
    ->   `STATE` varchar(64) DEFAULT NULL,
    ->   PRIMARY KEY (`LBL`)
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> DROP TABLE IF EXISTS `test`.`a`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE  `test`.`a` (
    ->   `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `VL` int(10) unsigned NOT NULL,
    ->   `X` int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> delimiter $$
mysql>
mysql> DROP TRIGGER IF EXISTS test.`a_AFTER_INSERT` $$
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TRIGGER test.`a_AFTER_INSERT` AFTER INSERT ON test.`a`
    ->   FOR EACH ROW
    ->     REPLACE dbgproc SELECT  'test.a' AS LBL,  NOW(),  A.`ID`,
    ->       A.`USER`,  A.`HOST`,  A.`DB`,  A.`COMMAND`,  A.`TIME`,
    ->       '' AS DUMMYSTATE -- we do not refer to column A.`STATE` in this case ..
    ->       FROM information_schema.`PROCESSLIST` A
    ->       WHERE A.`ID` = CONNECTION_ID();
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> INSERT INTO test.a VALUES (NULL, 2, 2);
ERROR 1406 (22001): Data too long for column 'STATE' at row 1
[23 Dec 2008 11:09] Serdar S. Kacar
mysql> SELECT * FROM information_schema.`PROCESSLIST` A        WHERE A.`ID` = CONNECTION_ID() \G
*************************** 1. row ***************************
     ID: 9
   USER: root
   HOST: localhost
     DB: test
COMMAND: Query
   TIME: 0
  STATE: executing
   INFO: SELECT * FROM information_schema.`PROCESSLIST` A        WHERE A.`ID` = CONNECTION_ID()
1 row in set (0.00 sec)

mysql>
[23 Dec 2008 23:31] Sveta Smirnova
Thank you for the report.

Verified as described with version 6.0. With version 5.1 bug is not repeatable for me.

Cause is replication. Strange if replace --source include/master-slave.inc with --source include/have_log_bin.inc in the test file error is not repeatable.
[26 Jan 2010 13:45] Nancy Boyle
I'm having the same problem with importing a csv file. It has an error on a column I'm trying to import. If I remove that column, then it picks another column. But, I don't see what the problem is. Here is my code. The error is at the very end after I created the table and imported the csv file (ERROR 1366 (HY000): Incorrect integer value: '' for column 'listID' at row 1). Any help with this would be much appreciated. I'm a graphic designer trying to learn this and I'm running out of time. 

Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.42-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| fortasample             |
| mysql                   |
| ne_vineyardsinfo        |
| newengland_vineyardinfo |
| sampleemployee          |
| test                    |
+-------------------------+
7 rows in set (0.00 sec)

mysql> use ne_vineyardsinfo;
Database changed
mysql> drop table if exists vininfo_data;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE vininfo_data
    -> (
    -> vin_id int unsigned not null auto_increment primary key,
    -> listID integer,
    -> featurelink char(50),
    -> photolink char(50),
    -> reviewlink char(50),
    -> maplink char(50),
    -> triplink char(50),
    -> sitelink char(50),
    -> vineyard char(50),
    -> town char(30),
    -> state varchar(2),
    -> zip int(11),
    -> phone char(30),
    -> fax char(60),
    -> email varchar(60),
    -> contact_fname char(50),
    -> contact_lname char(50)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> describe vininfo_data;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| vin_id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| listID        | int(11)          | YES  |     | NULL    |                |
| featurelink   | char(50)         | YES  |     | NULL    |                |
| photolink     | char(50)         | YES  |     | NULL    |                |
| reviewlink    | char(50)         | YES  |     | NULL    |                |
| maplink       | char(50)         | YES  |     | NULL    |                |
| triplink      | char(50)         | YES  |     | NULL    |                |
| sitelink      | char(50)         | YES  |     | NULL    |                |
| vineyard      | char(50)         | YES  |     | NULL    |                |
| town          | char(30)         | YES  |     | NULL    |                |
| state         | varchar(2)       | YES  |     | NULL    |                |
| zip           | int(11)          | YES  |     | NULL    |                |
| phone         | char(30)         | YES  |     | NULL    |                |
| fax           | char(60)         | YES  |     | NULL    |                |
| email         | varchar(60)      | YES  |     | NULL    |                |
| contact_fname | char(50)         | YES  |     | NULL    |                |
| contact_lname | char(50)         | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
17 rows in set (0.05 sec)

mysql> LOAD DATA INFILE 'vineyardInfoFileRev.csv'
    -> INTO TABLE vininfo_data
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> (listID,featurelink,photolink,reviewlink,maplink,triplink,sitelink,vineya
rd,town,state,zip,phone,fax,email,contact_fname,contact_lname);
ERROR 1366 (HY000): Incorrect integer value: '' for column 'listID' at row 1
mysql>