[Eestlane]

It worked for me with a bit modified code like this:

PHP:

<?php

require_once('includes/cmsinit.inc');
require_once(BASEDIR.'includes/classes/security.php');
require_once(BASEDIR.'includes/classes/cpg_member.php');

global $db, $prefix;
define('IN_PHPBB', true);

define('SEARCH_WORD_TABLE', $prefix.'_bbsearch_wordlist');
define('SEARCH_MATCH_TABLE', $prefix.'_bbsearch_wordmatch');
include('includes/phpBB/functions_search.php');

$total_topics = 'SELECT SUM(forum_topics) AS topic_total, SUM(forum_posts) AS post_total FROM '.$prefix.'_bbforums';

$result = $db->sql_query('SELECT post_id, post_text, post_subject from '.$prefix.'_bbposts_text order by post_id');

while($row = $db->sql_fetchrow($result))  {
  $total_topics++;
  add_search_words('global', $row['post_id'], $row['post_text'], $row['post_subject']);
}

echo 'Finished - please delete this file';
?>

I also checked for example a word from my before upgrade to 9.2.1 backup with word_id 0 and it really has a proper id now!

Nice, it worked!

[layingback]

SHOW INDEXES FROM nuke_bbsearch_wordlist

results from new db post (failed) upgrade:

Code:

Full Texts
Table 	Non_unique 	Key_name 	Seq_in_index 	Column_name 	Collation 	Cardinality 	Sub_part 	Packed 	Null 	Index_type 	Comment
nuke_bbsearch_wordlist 	0 	PRIMARY 	1 	word_text 	A 	6150 	NULL 	NULL 	  	BTREE 	 
nuke_bbsearch_wordlist 	1 	word_id 	1 	word_id 	A 	NULL 	NULL 	NULL 	  	BTREE 	 

The update was being done on localhost with MySQL5 and PHP5. Site normally runs on MySQL5.0.45-community (recent upgrade from MySQL4 by ISP on 12/27/07) and PHP4.4.7.

SQL file had to go through a collation update on localhost to be able to run installer at all.

SHOW INDEXES FROM nuke_bbsearch_wordlist

from original file pre collation update and pre install/upgrade attempt gives same results:

Code:

Table 	Non_unique 	Key_name 	Seq_in_index 	Column_name 	Collation 	Cardinality 	Sub_part 	Packed 	Null 	Index_type 	Comment
nuke_bbsearch_wordlist 	0 	PRIMARY 	1 	word_text 	A 	6150 	NULL 	NULL 	  	BTREE 	 
nuke_bbsearch_wordlist 	1 	word_id 	1 	word_id 	A 	NULL 	NULL 	NULL 	  	BTREE 	 

[BrokenCrust]

I am getting the same error (on a MySQL 5.0 database) for a 9.0.6.1. upgrade. (php 5.2).

Code:

On /install.php
While executing query "ALTER TABLE bc_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: C:\web\html\includes\classes\installer.php on line: 125

Code:

Table  	Non_unique  	Key_name  	Seq_in_index  	Column_name  	Collation  	Cardinality  	Sub_part  	Packed  	Null  	Index_type  	Comment
bc_bbsearch_wordlist 	0 	PRIMARY 	1 	word_text 	A 	6179 				BTREE 	
bc_bbsearch_wordlist 	1 	word_id 	1 	word_id 	A 	6179 				BTREE 	

[ConViCt]

This is also happening to me:

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: /usr/local/psa/home/vhosts/xxxx/httpdocs/includes/classes/installer.php on line: 125

Ran the query and got this:

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
nuke_bbsearch_wordlist 0 PRIMARY 1 word_text A 91883 NULL NULL BTREE
nuke_bbsearch_wordlist 1 word_id 1 word_id A 91883 NULL NULL BTREE


MySQL 4.1.11 and PHP Version 4.4.8

[NanoCaiordo]

I see why you have this issue, on 2006/01/26 was made a change in the indexes and the installer will be ready to handle such case within 24 hours.

Any version prior 9.1.0.1 were the installer never been there will have the same error, new 9.1+ or any upgrade to 9.1+ up will be just fine.

[Eestlane]

Will the installer recreate wordlist then during the upgrade process?

[NanoCaiordo]

The whole word list? like IDs and text?

[Eestlane]

Yep. I know it would be probably too heavy job for big forums.
However, if same problem exists like I had, that the word_id's were 0, then it would be a good investment for the future, I guess, to make that heavy job. Maybe splitted somehow, so if something fails it doesn't have to start over.

[NanoCaiordo]

Eestlane, you still haven't told us whats your forums tables history. This will help us to understand why you are experiencing such a strange issue.

[layingback]

Thanks Nano for the quick response!

[Eestlane]

Mhm, I started with 9.0.6.1, I guess.
Also maybe I have done some weird black magic with it previously, as I had some issues in past with upgrading because of double entries.
It might be mine problem specifically that I had word_id's with 0-s though, as if I understand correctly then the current error shows up even if the primary key isnt made right?

[NanoCaiordo]

PRIMARY KEYs are there, always been there and they will rebuilt properly - I only need to tune up the query execution order.

If you do have 0s in word id, then you do not have the an UNIQUE or PRIMARY index for word_id, or probably you forgot to add indexes back on your table.

Run and report here:
SHOW INDEXES FROM tablename;

[Eestlane]

Everything is fine now, but I didn't have indexes before. And I couldn't make them because of the same values of word_id's.

But, again, everything seems to be fine now.

[LankaBoard]

i didnt have any problem on upgrading from 9.1.2.1 to 9.2.1, but my mysql version is mysql5. my server can run both of versions mysql4 and mysql5. What is the best version for DF? if i change my server to another one thats only having mysql 4 databases, is it possible to import my old data base to new host (mysql4)or do i need to find a host which can run mysql5 databases?

[Eestlane]

Well, MySQL should be better for sure.