Bizarre, inexplicable error message

Databases
1

Hi All,

I have a problem with a table in my database.

The SQL for creating the table is:


CREATE TABLE `project` (
  `project_id` int(11) NOT NULL DEFAULT '0',
  `reference_id` varchar(255) DEFAULT NULL,
  `creation_date` date DEFAULT NULL,
  `lastupdate` datetime DEFAULT NULL,
  `profile_id` int(10) DEFAULT NULL,
  `publication_date` date DEFAULT NULL,
  `expiration_date` date DEFAULT NULL,
  `online` tinyint(1) DEFAULT '1',
  `name` varchar(255) NOT NULL DEFAULT '',
  `geo_id` int(10) NOT NULL DEFAULT '0',
  `geo_code` bigint(20) NOT NULL DEFAULT '0',
  `image_name` varchar(255) DEFAULT NULL,
  `my_first_time` datetime DEFAULT NULL,
  `organic_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `live_on_site` tinyint(1) DEFAULT '0',
  `live_expiration` date DEFAULT NULL,
  `live_publication` date DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `geolocation_lat` double(10,7) DEFAULT NULL,
  `geolocation_lon` double(10,7) DEFAULT NULL,
  `project_identifier` varchar(255) DEFAULT NULL,
  `harvester_checksum` varchar(32) DEFAULT NULL,
  `image_size` int(10) DEFAULT NULL,
  `alarm` varchar(255) DEFAULT NULL,
  `video_intercom` varchar(255) DEFAULT NULL,
  `video_surveilance` varchar(255) DEFAULT NULL,
  `security_service` varchar(255) DEFAULT NULL,
  `gatekeeper_concierge` varchar(255) DEFAULT NULL,
  `24h_medical_care` varchar(255) DEFAULT NULL,
  `smoke_detectors` varchar(255) DEFAULT NULL,
  `roll_shutters` varchar(255) DEFAULT NULL,
  `decorative_bars` varchar(255) DEFAULT NULL,
  `shutters` varchar(255) DEFAULT NULL,
  `security_lighting` varchar(255) DEFAULT NULL,
  `compound` tinyint(1) NOT NULL DEFAULT '0',
  `exterior_swimmingpool` varchar(255) NOT NULL DEFAULT '',
  `exterior_swimmingpool_type` varchar(255) DEFAULT NULL,
  `exterior_swimmingpool_size` int(10) DEFAULT NULL,
  `interior_swimmingpool` varchar(255) DEFAULT NULL,
  `interior_swimmingpool_type` varchar(255) DEFAULT NULL,
  `interior_swimmingpool_size` int(10) DEFAULT NULL,
  `jacuzzi` varchar(255) DEFAULT NULL,
  `jacuzzi_type` varchar(255) DEFAULT NULL,
  `sauna` varchar(255) DEFAULT NULL,
  `sauna_type` varchar(255) DEFAULT NULL,
  `tenniscourt` varchar(255) DEFAULT NULL,
  `tenniscourt_type` varchar(255) DEFAULT NULL,
  `gym` varchar(255) DEFAULT NULL,
  `gym_type` varchar(255) DEFAULT NULL,
  `bbq` varchar(255) DEFAULT NULL,
  `bbq_type` varchar(255) DEFAULT NULL,
  `exterior_bar` varchar(255) DEFAULT NULL,
  `exterior_bar_type` varchar(255) DEFAULT NULL,
  `interior_bar` varchar(255) DEFAULT NULL,
  `interior_bar_type` varchar(255) DEFAULT NULL,
  `tv_satellite` varchar(255) DEFAULT NULL,
  `amusement` varchar(255) NOT NULL DEFAULT '',
  `horeca` varchar(255) NOT NULL DEFAULT '',
  `closest_airport` int(10) DEFAULT NULL,
  `distance_to_airport` int(10) DEFAULT NULL,
  `distance_to_airport_unit` varchar(255) NOT NULL DEFAULT '',
  `distance_to_supermarket` int(10) DEFAULT NULL,
  `distance_to_supermarket_unit` varchar(255) NOT NULL DEFAULT '',
  `distance_to_center` int(10) DEFAULT NULL,
  `distance_to_center_unit` varchar(255) NOT NULL DEFAULT '',
  `distance_to_beach` int(10) DEFAULT NULL,
  `distance_to_beach_unit` varchar(255) NOT NULL DEFAULT '',
  `distance_to_harbour` int(10) DEFAULT NULL,
  `distance_to_harbour_unit` varchar(255) NOT NULL DEFAULT '',
  `distance_to_golfcourse` int(10) DEFAULT NULL,
  `distance_to_golfcourse_unit` varchar(255) NOT NULL DEFAULT '',
  `distance_to_tenniscourt` int(10) DEFAULT NULL,
  `distance_to_tenniscourt_unit` varchar(255) NOT NULL DEFAULT '',
  `distance_to_recreation_water` int(10) DEFAULT NULL,
  `distance_to_recreation_water_unit` varchar(255) NOT NULL DEFAULT '',
  `distance_to_hospital` int(10) DEFAULT NULL,
  `distance_to_hospital_unit` varchar(255) NOT NULL DEFAULT '',
  `distance_to_police` int(10) DEFAULT NULL,
  `distance_to_police_unit` varchar(255) NOT NULL DEFAULT '',
  `minimum_price` int(10) NOT NULL DEFAULT '0',
  `asking_pricefrom_generic` double(20,2) DEFAULT NULL,
  `asking_pricetill_generic` double(20,2) DEFAULT NULL,
  `asking_pricefrom` double(20,2) DEFAULT NULL,
  `asking_pricetill` double(20,2) DEFAULT NULL,
  `asking_pricetill_currency` varchar(10) DEFAULT NULL,
  `asking_pricefrom_currency` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`project_id`),
  UNIQUE KEY `project_idx_70` (`profile_id`,`project_identifier`),
  KEY `liveprojects` (`live_on_site`,`live_publication`,`live_expiration`),
  KEY `profileprojects` (`profile_id`,`organic_deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The query is:


UPDATE project SET lastupdate = lastupdate LIMIT 1;

(I know it’s a non-sensical query, but it’s just to generate the error)

The error message is:


Unknown column 'project' in 'field list' (#1054)

OK, the table might be called ‘project’, but there is no column ‘project’ in the table or in the query, so I do not understand why MySQL is complaining about it.

REPAIR commands etc do not help this problem…

Any ideas?

2

i did not get the same error message, although that might have something to do with the fact that my table is empty

3

Here’s a CSV (renamed to .txt) file with a single record in it.

I don’t think that will make much difference though. We don’t have this problem on our test environment, so I think there a corruption in the db somewhere.

The error is only displayed on UPDATE and DELETE commands. SELECTs and INSERTs don’t trigger the error.

4

Solved!

There was a trigger on the table, and the stored procedure it called had an incorrect field name in it.

How silly of me not to see that :slight_smile: