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?