Hi,

Esbach, Brandon wrote:
> Hi all,
>
> I'm guessing this is a fairly common question, but I've drawn a blank so
> far with all workarounds possible.
> Here's the rough scenario:
> We have data streaming in constantly from various offsite locations.
> This comprises of several details: a session, a unit of that session,
> and a measurement of that unit.
> Data is captured and transferred on a timed process, so often data is
> repeated in the data transfer packets. This repeating is unavoidable as
> the software used to capture dumps this data for each measurement and
> each unit for the session it's working on.
>
> Due to the volume, a bulk update is done using "values()" with an insert
> statement.
>
> Unfortunately, often there are repeats of either session, unit, or
> measurement (as there could be new measurement or unit for the capture
> that needs to be associated to the session).
> The problem I've been experiencing is fairly straightforward (I hope): I
> have two, sometimes three columns in any given record that need to
> always be unique. This comprises an ID (the key column), and one
> (depending on the table, sometimes two) GUIDs which should be unique at
> all times for the entire table.
>
> I've tried setting the additional columns to be a primary key (which in
> turn sets them up to be "unique" when viewed under "Schema Indices" on
> the MySQL Administrator tool); however this does not give an error (or
> fail silently with "insert ignore") when I insert a duplicate - mySQL
> seems quite happy to add the duplicate record.
>
> At the moment, I'm running a process in the beginning which simply
> gathers all guids from the database and compares them as it runs through
> the data (then adds new ones as it runs).. This is hardly reliable, and
> also means starting the service would take several hours to gather the
> existing guids at current data levels... almost frightening to think,
> what will end up happening as the data expands.

It sounds like you need a separate primary key and unique index:

create table t (
id int not null,
guid char(32) not null,
unique key (guid),
primary key(id)
);

Then you can do REPLACE or IGNORE with the LOAD DATA INFILE. I can't
tell if you are actually using LOAD DATA INFILE or if your "bulk load"
is a big INSERT statement. If you're using an INSERT with multiple
VALUES() sections, you can also use ON DUPLICATE KEY UPDATE.

I agree the current strategy won't hold up well over time.

Baron