| 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: | |
| 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 | |
[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>
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.