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 CHARSET=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!

christopher@dailycrossword.com wrote:
> 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 CHARSET=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!
>

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?

>
> 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?

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

christopher@dailycrossword.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?
>
> no, never changed the engine. just column deffs, indexes, etc. never
> touched the autoincrement columns.
>

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

--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

shameless bump