| Bug #53913 | Unknown column error for existing column | ||
|---|---|---|---|
| Submitted: | 22 May 2010 15:12 | Modified: | 22 May 2010 16:52 |
| Reporter: | Michael Z | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.1.45-1 | OS: | Linux (Debian/squeeze) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | unknown existing column, unkown column | ||
[22 May 2010 15:33]
Valeriy Kravchuk
This problem is not repeatable with recent 5.1.48 from bzr for me (on Mac OS X, but still):
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.48-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `p_geradeFormDachfenster` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `vorgang` int(10) unsigned NOT NULL,
-> `anzahl` tinyint(3) unsigned NOT NULL,
-> `pid` smallint(6) NOT NULL,
-> `produktklassifizierung` varchar(10) COLLATE latin1_german1_ci NOT NULL,
-> `produktbezeichnung` varchar(50) COLLATE latin1_german1_ci NOT NULL,
-> `typenbezeichnung` varchar(7) COLLATE latin1_german1_ci NOT NULL,
-> `typennummer` varchar(5) COLLATE latin1_german1_ci NOT NULL,
-> `farbe1` int(10) unsigned NOT NULL,
-> `anzZwischenstopps` smallint(6) DEFAULT NULL,
-> `profilfarbe` varchar(15) COLLATE latin1_german1_ci NOT NULL,
-> `einkaufspreis` decimal(5,2) unsigned NOT NULL,
-> `verkaufspreis` decimal(5,2) unsigned NOT NULL,
-> `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> `updated` timestamp NULL DEFAULT NULL,
-> `deleted` tinyint(1) NOT NULL,
-> `ersteller` varchar(20) COLLATE latin1_german1_ci NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `p_geradeFormDachfenster_fk_vorgang` (`vorgang`),
-> KEY `p_geradeFormDachfenster_fk_pidPklassePbez`
-> (`pid`,`produktklassifizierung`,`produktbezeichnung`),
-> KEY `p_geradeFormDachfenster_fk_farbe1` (`farbe1`),
-> KEY `p_geradeFormDachfenster_fk_typenbezeichnung` (`pid`,`typenbezeichnung`),
-> KEY `p_geradeFormDachfenster_fk_typennummer` (`pid`,`typennummer`),
-> KEY `p_geradeFormDachfenster_fk_profilfarbe` (`pid`,`profilfarbe`),
-> KEY `p_geradeFormDachfenster_fk_ersteller` (`ersteller`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
Query OK, 0 rows affected (0.42 sec)
mysql> select * from p_geradeFormDachfenster where id=1;
Empty set (0.01 sec)
mysql> UPDATE `p_geradeFormDachfenster` SET
-> `vorgang`=3471,`anzahl`=1,`typenbezeichnung`='GGL',`typennummer`='M04',`farbe1`=618,`anzZwischenstopps`=NULL,`profilfarbe`='silber',`verkaufspreis`=35.01,`pid`='16',`produktklassifizierung`='gerade',`produktbezeichnung`='DFR-HE',`updated`=NOW()
-> WHERE `id`=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select `typenbezeichnung` from `p_geradeFormDachfenster`;
Empty set (0.00 sec)
I had not inserted any data int ote table, but I do not think this is essential.
So, I have several suggestions/requests for you:
1. Please, check if the same simplified test case above still fails for you.
2. If it fails, please, send your my.cnf file content and the results of:
show variables like 'char%';
show variables like 'coll%';
from the same database. In my case I have:
mysql> show variables like 'char%';
+--------------------------+---------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /Users/openxs/dbs/5.1/share/mysql/charsets/ |
+--------------------------+---------------------------------------------+
8 rows in set (0.00 sec)
mysql> show variables like 'coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
3. Try to upgrade to 5.1.47 (MySQL binaries, if possible) and check if it is affected by the same problem.
[22 May 2010 16:52]
Michael Z
Crap, I'm very sorry for wasting your time. I had that hunch I missed something...now I finally had a revelation. The table posted in the bug report actually has a trigger for backing up changed rows, including time of change and responsible user. The "problematic" table has been created only recently in order to store data for a new product recently incorporated in our portfolio. The trigger for keeping track of changes evidently started out as a copy of a trigger for another table. And that copy hasn't been adapted correctly for the new table. It attempted to insert the changed row into the wrong/old table (which doesn't have the "typenbezeichnung" column). So the error message I got makes perfect sense...it was just misleading at first glance. Again, I'm very sorry I wasted your time. I hope you didn't spent too much of it. I have the urge to print this bug report and shove it up the responsible developers ass. Guess I'd better calm down, since he is also my employer :-) Sorry.
Description: Some things first: - I searched the Bug-DB but but could not find a similar error. There are many bugs regarding unknown existing columns, but the conditions under which those errors occured where very different in my opinion. - This is the latest version available on Debian/squeeze as of now. - I did not test with other operating systems - The error first occurred with version 5.0.51a-24+lenny3 (I use that version, because the production system also uses 5.0 and employs many SPs and Triggers) - Upgrade to 5.1.45-1 did not fix the issue - I did not even check for workarounds because I won't be ably to employ any either way - This bug might therefore even qualify as critical, but I'll leave that up to you. Same with the Category, because I have no idea what actually causes the problem. ###################################################### To begin with: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.1.45-1 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \s -------------- mysql Ver 14.14 Distrib 5.1.45, for debian-linux-gnu (i486) using readline 6.1 Connection id: 6 Current database: prefix_user-name Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.45-1 (Debian) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 37 sec Threads: 2 Questions: 458 Slow queries: 0 Opens: 453 Flush tables: 2 Open tables: 64 Queries per second avg: 12.378 -------------- The current database is actually different from "prefix_user-name" but it follows the same schema, for example "usr_miguel-sanchez". I cannot disclose the actual name. The table in question is defined as follows (Output from SHOW CREATE TABLE): CREATE TABLE `p_geradeFormDachfenster` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `vorgang` int(10) unsigned NOT NULL, `anzahl` tinyint(3) unsigned NOT NULL, `pid` smallint(6) NOT NULL, `produktklassifizierung` varchar(10) COLLATE latin1_german1_ci NOT NULL, `produktbezeichnung` varchar(50) COLLATE latin1_german1_ci NOT NULL, `typenbezeichnung` varchar(7) COLLATE latin1_german1_ci NOT NULL, `typennummer` varchar(5) COLLATE latin1_german1_ci NOT NULL, `farbe1` int(10) unsigned NOT NULL, `anzZwischenstopps` smallint(6) DEFAULT NULL, `profilfarbe` varchar(15) COLLATE latin1_german1_ci NOT NULL, `einkaufspreis` decimal(5,2) unsigned NOT NULL, `verkaufspreis` decimal(5,2) unsigned NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated` timestamp NULL DEFAULT NULL, `deleted` tinyint(1) NOT NULL, `ersteller` varchar(20) COLLATE latin1_german1_ci NOT NULL, PRIMARY KEY (`id`), KEY `p_geradeFormDachfenster_fk_vorgang` (`vorgang`), KEY `p_geradeFormDachfenster_fk_pidPklassePbez` (`pid`,`produktklassifizierung`,`produktbezeichnung`), KEY `p_geradeFormDachfenster_fk_farbe1` (`farbe1`), KEY `p_geradeFormDachfenster_fk_typenbezeichnung` (`pid`,`typenbezeichnung`), KEY `p_geradeFormDachfenster_fk_typennummer` (`pid`,`typennummer`), KEY `p_geradeFormDachfenster_fk_profilfarbe` (`pid`,`profilfarbe`), KEY `p_geradeFormDachfenster_fk_ersteller` (`ersteller`), CONSTRAINT `p_geradeFormDachfenster_fk_ersteller` FOREIGN KEY (`ersteller`) REFERENCES `benutzer` (`benutzername`) ON UPDATE CASCADE, CONSTRAINT `p_geradeFormDachfenster_fk_farbe1` FOREIGN KEY (`farbe1`) REFERENCES `farbe` (`id`) ON UPDATE CASCADE, CONSTRAINT `p_geradeFormDachfenster_fk_pidPklassePbez` FOREIGN KEY (`pid`, `produktklassifizierung`, `produktbezeichnung`) REFERENCES `produkt_tabelle_zuordnung` (`pid`, `produktklassifizierung`, `produktbezeichnung`), CONSTRAINT `p_geradeFormDachfenster_fk_profilfarbe` FOREIGN KEY (`pid`, `profilfarbe`) REFERENCES `attr_profilfarbe` (`pid`, `bezeichnung`), CONSTRAINT `p_geradeFormDachfenster_fk_typenbezeichnung` FOREIGN KEY (`pid`, `typenbezeichnung`) REFERENCES `attr_typenbezeichnung` (`pid`, `bezeichnung`), CONSTRAINT `p_geradeFormDachfenster_fk_typennummer` FOREIGN KEY (`pid`, `typennummer`) REFERENCES `attr_typennummer` (`pid`, `bezeichnung`), CONSTRAINT `p_geradeFormDachfenster_fk_vorgang` FOREIGN KEY (`vorgang`) REFERENCES `vorgang` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci; The table contains a row as follows: mysql> select * from p_geradeFormDachfenster where id=1; +----+---------+--------+-----+------------------------+--------------------+------------------+-------------+--------+-------------------+-------------+---------------+---------------+---------------------+---------+---------+-----------+ | id | vorgang | anzahl | pid | produktklassifizierung | produktbezeichnung | typenbezeichnung | typennummer | farbe1 | anzZwischenstopps | profilfarbe | einkaufspreis | verkaufspreis | created | updated | deleted | ersteller | +----+---------+--------+-----+------------------------+--------------------+------------------+-------------+--------+-------------------+-------------+---------------+---------------+---------------------+---------+---------+-----------+ | 1 | 3471 | 1 | 16 | gerade | DFR-HE | GGL | 102 | 618 | NULL | silber | 0.00 | 35.01 | 2010-05-22 15:02:25 | NULL | 0 | dj | +----+---------+--------+-----+------------------------+--------------------+------------------+-------------+--------+-------------------+-------------+---------------+---------------+---------------------+---------+---------+-----------+ 1 row in set (0.00 sec) I try to execute this statement: UPDATE `p_geradeFormDachfenster` SET `vorgang`=3471,`anzahl`=1,`typenbezeichnung`='GGL',`typennummer`='M04',`farbe1`=618,`anzZwischenstopps`=NULL,`profilfarbe`='silber',`verkaufspreis`=35.01,`pid`='16',`produktklassifizierung`='gerade',`produktbezeichnung`='DFR-HE',`updated`=NOW() WHERE `id`=1; The error I get is: ERROR 1054 (42S22): Unknown column 'typenbezeichnung' in 'field list' Now I try: mysql> select `typenbezeichnung` from `p_geradeFormDachfenster`; +------------------+ | typenbezeichnung | +------------------+ | GGL | | GGL | | GGL | | GGL | | GGL | | GGL | | GGL | +------------------+ 7 rows in set (0.00 sec) And finally, the most f***ed up (...for lack of a better term, sorry...) thing is: I remove the "`typenbezeichnung`='GGL'," part from the update statement, and I get the same error: mysql> UPDATE `p_geradeFormDachfenster` SET `vorgang`=3471,`anzahl`=1,`typennummer`='M04',`farbe1`=618,`anzZwischenstopps`=NULL,`profilfarbe`='silber',`verkaufspreis`=35.01,`pid`='16',`produktklassifizierung`='gerade',`produktbezeichnung`='DFR-HE',`updated`=NOW() WHERE `id`=1 -> ; ERROR 1054 (42S22): Unknown column 'typenbezeichnung' in 'field list' Restarting the server, flushing all sorts of "stuff" (query cache, privileges, tables) didn't help. I'm out of ideas, please help. :-( How to repeat: I dropped all foreign keys and the problem persists. So I suggest that you try to create the table from the description omitting the foreign key constraints. Suggested fix: You tell me... :-)