SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Bizarre, inexplicable error message

    Hi All,

    I have a problem with a table in my database.

    The SQL for creating the table is:

    Code SQL:
    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:

    Code SQL:
    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:

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i did not get the same error message, although that might have something to do with the fact that my table is empty
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    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.
    Attached Files Attached Files

  4. #4
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •