Professional Web Applications Themes

lost autoincrement and keys on several tables - MySQL

Greetings! Is there a silent specification change that can cause 'autoincrement' to be dropped for a table? I seem to have lost 'autoincrement' (and all the keys) on all 5 tables that use it, but I might have made a change to all 5 tables and not inserted anything into them so I didn't notice the change. table type MyISAM, mysql version 4.1.14-max I track changes to my tables in a text file, and this is the 'evolved' table deff. It works fine: show index is correct and autoincrement exists in describe: create table questions_temp ( content blob default "" ...

Sponsored Links
  1. #1

    Default lost autoincrement and keys on several tables

    Greetings!
    Is there a silent specification change that can cause 'autoincrement'
    to be dropped for a table? I seem to have lost 'autoincrement' (and
    all the keys) on all 5 tables that use it, but I might have made a
    change to all 5 tables and not inserted anything into them so I didn't
    notice the change. table type MyISAM, mysql version 4.1.14-max

    I track changes to my tables in a text file, and this is the 'evolved'
    table deff. It works fine: show index is correct and autoincrement
    exists in describe:

    create table questions_temp (
    content blob default "" NOT NULL,
    answer_type set ("single","multiple","ordered") default "single" NOT
    NULL,
    correct_answer CHAR(16) default "" NOT NULL,
    question_factor TINYINT default 1 NOT NULL,
    previous_question_id SMALLINT UNSIGNED default 0 NOT NULL,
    event_id SMALLINT UNSIGNED default 0 NOT NULL,
    question_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    KEY event_key (event_id),
    KEY question_key (question_id));


    However, show create shows this:
    questions | CREATE TABLE `questions` (
    `content` blob NOT NULL,
    `answer_type` set('single','multiple','ordered') NOT NULL default
    'single',
    `correct_answer` varchar(16) NOT NULL default '',
    `question_factor` tinyint(4) NOT NULL default '1',
    `previous_question_id` smallint(5) unsigned NOT NULL default '0',
    `event_id` smallint(5) unsigned NOT NULL default '0',
    `question_id` smallint(5) unsigned NOT NULL default '0'
    ) ENGINE=MyISAM DEFAULT CHT=latin1

    and describe shows this:
    +----------------------+------------------------------------+------
    +-----+---------+-------+
    | Field | Type | Null |
    Key | Default | Extra |
    +----------------------+------------------------------------+------
    +-----+---------+-------+
    | content | blob |
    | | | |
    | answer_type | set('single','multiple','ordered') |
    | | single | |
    | correct_answer | varchar(16) |
    | | | |
    | question_factor | tinyint(4) |
    | | 1 | |
    | previous_question_id | smallint(5) unsigned |
    | | 0 | |
    | event_id | smallint(5) unsigned |
    | | 0 | |
    | question_id | smallint(5) unsigned |
    | | 0 | |
    +----------------------+------------------------------------+------
    +-----+---------+-------+


    no keys, no autoincrement.

    I was able to fix the tables with:
    alter table questions modify column question_id SMALLINT UNSIGNED
    AUTO_INCREMENT NOT NULL KEY;
    (note the addition of "KEY' at the end)
    and
    alter table questions add index event_key (event_id);
    but I would like to know what I did so I don't do it again *wink*

    Thanx!

    Sponsored Links
    christopher@dailycrossword.com Guest

  2. #2

    Default Re: lost autoincrement and keys on several tables

    com wrote: 

    if the change you're talking about involved the engine type change, then
    you may have lost the attributes of the column. do you remember what
    type of change you made?
    lark Guest

  3. #3

    Default Re: lost autoincrement and keys on several tables

     

    no, never changed the engine. just column deffs, indexes, etc. never
    touched the autoincrement columns.

    christopher@dailycrossword.com Guest

  4. #4

    Default Re: lost autoincrement and keys on several tables

    com wrote: 
    >
    > no, never changed the engine. just column deffs, indexes, etc. never
    > touched the autoincrement columns.
    >[/ref]

    if you haven't changed the engine, then i can't answer your question! sorry!

    --
    lark -- net
    To reply to me directly, delete "despam".
    lark Guest

  5. #5

    Default Re: lost autoincrement and keys on several tables

    shameless bump

    christopher@dailycrossword.com Guest

Similar Threads

  1. ViewState + AutoIncrement -- Okay; Session + AutoIncrement -- Not okay
    By Gene Gorokhovsky in forum ASP.NET General
    Replies: 0
    Last Post: July 17th, 02:48 PM

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