_bbsearch_wordlist indexes error on upgrade

[Eestlane]

On /install.php
While executing query "ALTER TABLE cms_bbsearch_wordlist CHANGE word_id word_id INT UNSIGNED NOT NULL AUTO_INCREMENT NOT NULL DEFAULT NULL"

the following error occured: Incorrect table definition; there can be only one auto column and it must be defined as a key

In: /home/xxx/public_html/includes/classes/installer.php on line: 115

[Eestlane]

I tried to upgrade from 9.1.2.1 btw.

[Eestlane]

Mhm, maybe it's because most of the words have id 0 in my database. Maybe I have done something wrong in past.

[Eestlane]

Well, I got it installed by dropping the modified database failed install made.

Then reuploading the backup.

Then ran this code:
DELETE FROM cms_bbsearch_wordlist where word_id = 0

Then made new PRIMARY key from word_id

Thats all! All these steps were required btw.

[Eestlane]

Well, only problem is that I won't have a lot of results from forum search now.

[rlgura]

Nobody posts on your forums anyway!

[Eestlane]

You are just envious, that you can't speak Estonian.

[layingback]

Have same problem on upgrade to 9.2.1. This is on (a copy of) an old db that started on PHP-Nuke and has been through CPG 8 and 9 - up to 9.0.6.1.

Code::

On .../install.php While executing query "ALTER TABLE nuke_bbsearch_wordlist DROP INDEX word_id" the following error occured: Incorrect table definition; there can be only one auto column and it must be defined as a key In: ..../public_html/includes/classes/installer.php on line: 125

Guess I could try similar to Eestlane, but I have to keep all the search words ...

[Phoenix]

That appears to be a slightly different issue - I would wait until investigated.

It may well be that MySQL4 is the issue.

[DJ Maze]

ALTER TABLE cms_bbsearch_wordlist CHANGE word_id word_id INT UNSIGNED NOT NULL AUTO_INCREMENT NOT NULL DEFAULT NULL

Looks like a wrong query.

[NanoCaiordo]

Yes query is totally wrong but also there is one more thing. word_id is a AUTO_INCREMENT field and it has a PRIMARY or an UNIQUE key depends the history of his site. Both field and indexes does not allow to have duplicate values, but Eestlane got all of them 0 which make me think that field and/or indexes were heavily modified by something.

For layingback's issue there it shouldn't be any problem if the the field is AUTO_INCREMENT and the PRIMARY key exists and is UNIQUE (which it has to be). If the PRIMARY key doesn't exists then i can justify the error and tune up the order on how the queries are executed. Otherwise its something to do with MySQL 4.

[NanoCaiordo]

I've manually run few tests in mysql 4.1.22 and mysql 5.0.22 and they both returned the same results, its not a MySQL issue unless it does happen in 4.1.21 only (not even reported in bugs.mysql).

It seems that you (layingback) are missing the PRIMARY KEY which should be already there. Please run the following query and let us know:

Code::

SHOW INDEXES FROM nuke_bbsearch_wordlist

[Eestlane]

I think I'll need (to make) something similar to phpBB's Rebuild Search to rebuild my search values, as I had them messed up already before.

[Phoenix]

It certainly did appear that you had a previous problem, complicated by the upgrade.

dragonflycms.org/Forum...=6612.html - it's old now, and needs to be adapted to DF, but then again, our forum db structure hasn't changed much either. Just ensure your basic table structures are correct to start with.

[Eestlane]

Thanks, Phoenix, will try.