MySQL Stored Proc. / Unique Key conflict

Question

I am using a stored procedure to insert and update some scraped data into a table. The logic is pretty simple.

check if a matching value exists in the table.
if yes - update the odds.
if no - insert the data.

I am using the following source code:

DECLARE match_id bigint;
SET match_id = -1;
SELECT id INTO match_id FROM scrape_initial_horses sih
  WHERE  `time` = event_time 
  AND `track_name` =  event_track
  and `date` = event_date
  and `participant_name` = participant_name
  and `bookmaker_name` = provider
  and `betting_type` = offer_type LIMIT 1;

# Here I am placing a debug statements showing me the raw data and match_id.

IF match_id = -1 THEN 
  insert into `scrape_initial_horses` (`time`, `track_name`, `date`, `participant_name`, `odds`, `bookmaker_name`, `betting_type`)
  values (event_time, event_track, event_date, participant_name, offer_odds, provider, offer_type);
ELSE 
  UPDATE scrape_initial_horses sih SET sih.odds = offer_odds where id = match_id;
end IF;
END

I further have a unique key on the table that covers the columns: time, track_name, date, participant_name, bookmaker_name, betting_type.

The issue I have is that the match_Id regularly seems to match a record with a different participant id, so an incorrect row is updated instead of a new row being inserted. I have double and treble checked that the unique key is in place. Prior to setting this up as 3 queries I had a single query of the style

Insert into ... on duplicate key update ....

This had exactly the same effect. What am I missing?

I have also switched between InnoDB and MyISAM tables, recreated the table. I am running MySQL 5.7 on a Centos 6 server. My understanding is that the match_id variable should only be accessible within the thread running the stored procedure, but either that is not the case or I am making some other incorrect assumptions about uniqueness of the data tables.

score 1 · Answer 1 · answered May 29 '16 at 11:28

I suspect what you are missing is that within a MySQL stored program, a local variable takes precedence over a column name.

If participant_name is both the name of a column, and a local variable name, then this:

   and `participant_name` = particpant_name

Will perform a comparison of the local variable to the local variable. The value from the column will not be referenced.

That's just a guess, because the code posted in the question is insufficient for us to make that determination. (We don't see any local variables declared, nor do we see the table definition.)

Best practice is to qualify column references with the tablename or a table alias. Also, consider naming procedure and local variables so that the names don't collide with column names.

   SELECT sih.`id` INTO match_id
     FROM `scrape_initial_horses` sih
    WHERE sih.`time`             = v_event_time 
      AND sih.`track_name`       = v_event_track
      AND sih.`date`             = v_event_date
      AND sih.`participant_name` = v_participant_name
      AND sih.`bookmaker_name`   = v_provider
      AND sih.`betting_type`     = v_offer_type
    LIMIT 1;

MySQL Stored Proc. / Unique Key conflict

1 Answers1