Professional Web Applications Themes

auto_increment to get default value, then adjust afterwards? - MySQL

Hi, I'm setting up a database for incidents that users report for our software. I want to group incidents together that are caused by the same bug in our program, and for that matter I've got a hash function that aims to return the same value for incidents that should be in the same group. So ideally I'd like to say: CREATE TABLE incidents (incidentId INT AUTO_INCREMENT NOT NULL PRIMARY KEY, hash VARCHAR(250), ...additional columns...) CREATE TABLE groups (hash VARCHAR(250) PRIMARY KEY, groupId INT AUTO_INCREMENT NOT NULL) with the incidentIds ranging from 1 to # incidents and groupId between 1 ...

  1. #1

    Default auto_increment to get default value, then adjust afterwards?

    Hi,

    I'm setting up a database for incidents that users report for our
    software. I want to group incidents together that are caused by the
    same bug in our program, and for that matter I've got a hash function
    that aims to return the same value for incidents that should be in the
    same group.

    So ideally I'd like to say:

    CREATE TABLE incidents
    (incidentId INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    hash VARCHAR(250),
    ...additional columns...)

    CREATE TABLE groups
    (hash VARCHAR(250) PRIMARY KEY,
    groupId INT AUTO_INCREMENT NOT NULL)

    with the incidentIds ranging from 1 to # incidents and groupId between
    1 and #groups.

    However this doesn't work because AUTO_INCREMENT only works for columns
    that are a key. And if I make groupId the key, then i can't change the
    values afterwards because as a key, the groupId would have to be
    unique. However the hash function isn't perfect, and sometimes issues
    will have different hashes although they are the same. So I'd like to
    merge two groups - i.e. map two different hashes to the same groupId.

    If i SELECT MAX(groupId) FROM groups, then between that and me
    executing the INSERT, some other process may access the same table and
    get the same value - a race condition.

    Is there any way I can solve this problem without transactions (doesn't
    look like there is good transaction support in MySQL in the first place
    :( )?

    I also thought of something like

    CREATE TABLE groups (hash VARCHAR(250) PRIMARY KEY, groupId INT)

    and have a table counters that contains the maximum group Id.

    CREATE TABLE counters (counter CHAR(20), value INT)

    Then I could say:

    UPDATE counters SET counter = LAST_INSERT_ID(counter+1) WHERE
    name='maxGroupId';

    INSERT INTO groups SET hash='foo', groupId=LAST_INSERT_ID();

    This would make sure that the same groupId isn't assigned twice
    initially, however when the INSERT fails, I have already incremented
    the counter, so the groupIds wouldn't be consecutive. In fact, the
    groupIds would simply become the incidentId of the first incident in
    that group.

    Also, I can't select from a table in the same statement as i insert, so

    INSERT INTO groups SET hash='foobar', groupId=(SELECT MAX(1+groupId
    FROM groups));

    won't work.

    Any ideas? I kinda think this must be a classic problem with a standard
    answer. However I couldn't find any using google.

    Thanks heaps in advance for any help!

    Tobias

    Tobias Guest

  2. #2

    Default Re: auto_increment to get default value, then adjust afterwards?

    By the way, one way of solving this problem might be if there was a
    function that mapped a value to its index in the ordered list of all
    values in that column.

    So if for each incident hash I could store a reference to the smallest
    incident ID that is in the same incident group, then I would have a
    table

    hash | smallestIncidentIdInGroup
    foo 2
    bar 23
    baz 42

    If I could calculate a value groupId for each row as follows:

    hash | smallestIncidentIdInGroup | groupId
    foo 2 1
    bar 23 2
    baz 42 3

    where the 1,2,3 are just the indices of the rows after ordering by
    smallestIncidentIdInGroup, and are automatically calculated from the
    other data (e.g. as a view) then that would be very helpful. Is this
    possible with MySQL?

    Cheers,

    Tobias

    Tobias Guest

  3. #3

    Default Re: auto_increment to get default value, then adjust afterwards?

    Actually, I just found out that an auto_increment column doesn't
    actually have to be a key (although MySQL's error message when an
    auto_increment column is no index states so! - I consider this a bug in
    MySQL's error message).

    So I can just say:

    CREATE TABLE groups (hash CHAR(30) PRIMARY KEY, groupId INT
    AUTO_INCREMENT NOT NULL, INDEX(groupId));

    and then adjust the groupId afterwards as much as I like :)

    Yay!

    Tobias

    Tobias Guest

Similar Threads

  1. Solved how to adjust the css in dreamweaver
    By soukphadeth in forum Brainstorming Area
    Replies: 0
    Last Post: April 21st, 09:27 AM
  2. Replies: 4
    Last Post: October 11th, 11:30 PM
  3. How to personalise an auto_increment reference field ?
    By Baptiste Pillot in forum MySQL
    Replies: 11
    Last Post: June 16th, 03:31 PM
  4. How can I adjust....
    By Gabriel Sanchez in forum Adobe Photoshop Elements
    Replies: 2
    Last Post: August 1st, 01:33 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139