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:12]
Michael Z
[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.