You are not logged in. Please login or register.
PunBB Forums → PunBB 1.2 bug reports → can't update
Hi,
I've got a problem
When i try to update, i've got the following message:
File: /home/oyre/www/forum/update.php
Line: 166
PunBB reported: Unable to alter DB structure.
Database reported: Can't DROP 'forumsearch_results_ident_idx'. Check that column/key exists (Errno: 1091)
Help me please.
Ludo,
Check if your database has an index called forumsearch_results_ident_idx on the search_results table.
I do not have any search_results table as the update script has begun to execute. It has certainly erased the search_results table.
I also tell you that I have no dump of my DB. I assume it.
So, I can't answer your question.
Ludo,
I deleted DROP 'forumsearch_results_ident_idx' from the update script.
But i got another problem:
Can't DROP 'forumsubscriptions_user_id_idx'. Check that column/key exists (Errno: 1091)
I also deleted this line. The update is made but see what I got. The problem comes from every idx files.
It's a very light punbb...
what's up?
Ludo,
The update script first renames the search_results table to search_cache. It then tries to drop the index search_results_ident_idx and create a new index search_cache_ident_idx. I have no idea why it fails for you. I would like to see a structure dump of your database though. Preferably before you ran the upgrade script.
no problem.
Can I send you by mail the former dump I got? It's about 3 Mo
Ludo,
I also want to explain that:
I've got several forums using punbb.
I haven't got any dump for the first I tried to upgraded and which doesn't work at the moment. (http://www.oyre.net/forum)
I Try to upgrade the second and I have exactly the same error. But I obviously got a dump for this second. Do you want the dump before I started to upgrade or after I started upgrading? at which mail adress can I send you the dump?
Ludo,
I've understood what you want.
So this is the structure of the db before upgrading:
Structure de la table `forumbans`
--
CREATE TABLE `forumbans` (
`id` int(10) unsigned NOT NULL auto_increment,
`username` varchar(200) default NULL,
`ip` varchar(255) default NULL,
`email` varchar(50) default NULL,
`message` varchar(255) default NULL,
`expire` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Structure de la table `forumcategories`
--
CREATE TABLE `forumcategories` (
`id` int(10) unsigned NOT NULL auto_increment,
`cat_name` varchar(80) NOT NULL default 'New Category',
`disp_position` int(10) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
-- --------------------------------------------------------
--
-- Structure de la table `forumcensoring`
--
CREATE TABLE `forumcensoring` (
`id` int(10) unsigned NOT NULL auto_increment,
`search_for` varchar(60) NOT NULL default '',
`replace_with` varchar(60) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
-- --------------------------------------------------------
--
-- Structure de la table `forumconfig`
--
CREATE TABLE `forumconfig` (
`conf_name` varchar(255) NOT NULL default '',
`conf_value` text,
PRIMARY KEY (`conf_name`)
) TYPE=MyISAM;
-- --------------------------------------------------------
--
-- Structure de la table `forumforums`
--
CREATE TABLE `forumforums` (
`id` int(10) unsigned NOT NULL auto_increment,
`forum_name` varchar(80) NOT NULL default 'New forum',
`forum_desc` text,
`moderators` text,
`num_topics` mediumint(8) unsigned NOT NULL default '0',
`num_posts` mediumint(8) unsigned NOT NULL default '0',
`last_post` int(10) unsigned default NULL,
`last_post_id` int(10) unsigned default NULL,
`last_poster` varchar(200) default NULL,
`closed` tinyint(1) NOT NULL default '0',
`admmod_only` tinyint(1) NOT NULL default '0',
`disp_position` int(10) NOT NULL default '0',
`cat_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=16 ;
-- --------------------------------------------------------
--
-- Structure de la table `forumonline`
--
CREATE TABLE `forumonline` (
`user_id` int(10) unsigned NOT NULL default '0',
`ident` varchar(200) NOT NULL default '',
`logged` int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;
-- --------------------------------------------------------
--
-- Structure de la table `forumposts`
--
CREATE TABLE `forumposts` (
`id` int(10) unsigned NOT NULL auto_increment,
`poster` varchar(200) NOT NULL default '',
`poster_id` int(10) unsigned NOT NULL default '1',
`poster_ip` varchar(15) default NULL,
`poster_email` varchar(50) default NULL,
`message` text NOT NULL,
`smilies` tinyint(1) NOT NULL default '1',
`posted` int(10) unsigned NOT NULL default '0',
`edited` int(10) unsigned default NULL,
`edited_by` varchar(200) default NULL,
`topic_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1062 ;
-- --------------------------------------------------------
--
-- Structure de la table `forumranks`
--
CREATE TABLE `forumranks` (
`id` int(10) unsigned NOT NULL auto_increment,
`rank` varchar(50) NOT NULL default '',
`min_posts` mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
-- --------------------------------------------------------
--
-- Structure de la table `forumreports`
--
CREATE TABLE `forumreports` (
`id` int(10) unsigned NOT NULL auto_increment,
`post_id` int(10) unsigned NOT NULL default '0',
`topic_id` int(10) unsigned NOT NULL default '0',
`forum_id` int(10) unsigned NOT NULL default '0',
`reported_by` int(10) unsigned NOT NULL default '0',
`created` int(10) unsigned NOT NULL default '0',
`message` text NOT NULL,
`zapped` int(10) unsigned default NULL,
`zapped_by` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
-- --------------------------------------------------------
--
-- Structure de la table `forumsearch_matches`
--
CREATE TABLE `forumsearch_matches` (
`post_id` int(10) unsigned NOT NULL default '0',
`word_id` int(10) unsigned NOT NULL default '0',
`subject_match` tinyint(1) NOT NULL default '0'
) TYPE=MyISAM;
-- --------------------------------------------------------
--
-- Structure de la table `forumsearch_results`
--
CREATE TABLE `forumsearch_results` (
`id` int(10) unsigned NOT NULL default '0',
`ident` varchar(200) NOT NULL default '',
`search_data` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;
-- --------------------------------------------------------
--
-- Structure de la table `forumsearch_words`
--
CREATE TABLE `forumsearch_words` (
`id` int(10) unsigned NOT NULL auto_increment,
`word` varchar(20) binary NOT NULL default '',
PRIMARY KEY (`word`),
KEY `forumsearch_words_id_idx` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=7158 ;
-- --------------------------------------------------------
--
-- Structure de la table `forumsubscriptions`
--
CREATE TABLE `forumsubscriptions` (
`user_id` int(10) unsigned NOT NULL default '0',
`topic_id` int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;
-- --------------------------------------------------------
--
-- Structure de la table `forumtopics`
--
CREATE TABLE `forumtopics` (
`id` int(10) unsigned NOT NULL auto_increment,
`poster` varchar(200) NOT NULL default '',
`subject` varchar(255) NOT NULL default '',
`posted` int(10) unsigned NOT NULL default '0',
`last_post` int(10) unsigned NOT NULL default '0',
`last_post_id` int(10) unsigned NOT NULL default '0',
`last_poster` varchar(200) default NULL,
`num_views` mediumint(8) unsigned NOT NULL default '0',
`num_replies` mediumint(8) unsigned NOT NULL default '0',
`closed` tinyint(1) NOT NULL default '0',
`sticky` tinyint(1) NOT NULL default '0',
`moved_to` int(10) unsigned default NULL,
`forum_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=151 ;
-- --------------------------------------------------------
--
-- Structure de la table `forumusers`
--
CREATE TABLE `forumusers` (
`id` int(10) unsigned NOT NULL auto_increment,
`username` varchar(200) NOT NULL default '',
`password` varchar(40) NOT NULL default '',
`email` varchar(50) NOT NULL default '',
`title` varchar(50) default NULL,
`realname` varchar(40) default NULL,
`url` varchar(100) default NULL,
`icq` varchar(12) default NULL,
`msn` varchar(50) default NULL,
`aim` varchar(20) default NULL,
`yahoo` varchar(20) default NULL,
`location` varchar(30) default NULL,
`use_avatar` tinyint(1) NOT NULL default '0',
`signature` text,
`disp_topics` tinyint(3) unsigned default NULL,
`disp_posts` tinyint(3) unsigned default NULL,
`email_setting` tinyint(1) NOT NULL default '1',
`save_pass` tinyint(1) NOT NULL default '1',
`notify_with_post` tinyint(1) NOT NULL default '0',
`smilies` tinyint(1) NOT NULL default '1',
`show_img` tinyint(1) NOT NULL default '1',
`show_avatars` tinyint(1) NOT NULL default '1',
`show_sig` tinyint(1) NOT NULL default '1',
`link_to_new_win` tinyint(1) NOT NULL default '1',
`timezone` tinyint(2) NOT NULL default '0',
`style` varchar(25) NOT NULL default 'Oxygen',
`num_posts` int(10) unsigned NOT NULL default '0',
`status` tinyint(1) NOT NULL default '-1',
`last_post` int(10) unsigned default NULL,
`registered` int(10) unsigned NOT NULL default '0',
`last_visit` int(10) unsigned NOT NULL default '0',
`last_action` int(10) unsigned NOT NULL default '0',
`admin_note` varchar(30) default NULL,
`activate_string` varchar(50) default NULL,
`activate_key` varchar(8) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=141 ;It has no link but I also want to tell you that though I'm subscribed to this topic, I do not receive any notification when an answer is posted.
Ludo
There is definately something with that dump. There are several indexes missing from the tables. E.g. 'forumposts' should have two indexes: forumposts_topic_id_idx and forumposts_poster_id_idx.
the forum worked good without theses missing indexes in 1.1.5
Yes, it works, but it would have been a lot slower than with the indexes in place. Especially with a large number of posts/topics/users.
I've got 3 forums using punbb 1.1.5
For every forums it's the same problem.
Was there a problem with the installation of 1.1.5
I really don't understand.
Are these forums fresh 1.1.* installs or have they been updated from 1.0 earlier?
they are fresh installed. They were installed in 1.1.4 and upgraded to 1.1.5
did you have the PM mod installed?
I've never installed any mode.
The only modification I made were to personalise header and footer
I'm really sorry Ludo, but for some reason, your databases are missing some indexes. Just for fun, install a fresh 1.1.5 install, save a structure dump of it and then try to upgrade it to 1.2. If it fails, post the structure dump here.
and can i install new punbb to empty DB and new folder and than import users and topics from old version to it? (sorry for my english 
)
I'm afraid not. What you can do is install a fresh 1.1.5 into a new db and then compare this database with the old one and make the changes manually. It's not something I would recommend unless you know a little about MySQL.
thank you
Just for fun, install a fresh 1.1.5 install, save a structure dump of it and then try to upgrade it to 1.2. If it fails, post the structure dump here.
Ok Rickard,
Just for fun as you say (even if it does not make me laugh 
) I've installed 1.1.5 on two different webhosters.
Then, I tried to update to 1.2.
Guess! I've got exactly the same problem:
File: /home/oyre/www/pun/update.php
Line: 166
PunBB reported: Unable to alter DB structure.
Database reported: Can't DROP 'puntestsearch_results_ident_idx'. Check that column/key exists (Errno: 1091)I'm really sad not to be able to update the 1.2 I've waited for so long time.
As you asked me, this is the dump before updating.
Have you got an idea?
# Structure de la table 'puntestbans'
#
CREATE TABLE puntestbans (
id int(10) unsigned NOT NULL auto_increment,
username varchar(200),
ip varchar(255),
email varchar(50),
message varchar(255),
expire int(10) unsigned,
PRIMARY KEY (id)
);
# --------------------------------------------------------
#
# Structure de la table 'puntestcategories'
#
CREATE TABLE puntestcategories (
id int(10) unsigned NOT NULL auto_increment,
cat_name varchar(80) DEFAULT 'New Category' NOT NULL,
disp_position int(10) DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
# --------------------------------------------------------
#
# Structure de la table 'puntestcensoring'
#
CREATE TABLE puntestcensoring (
id int(10) unsigned NOT NULL auto_increment,
search_for varchar(60) NOT NULL,
replace_with varchar(60) NOT NULL,
PRIMARY KEY (id)
);
# --------------------------------------------------------
#
# Structure de la table 'puntestconfig'
#
CREATE TABLE puntestconfig (
conf_name varchar(255) NOT NULL,
conf_value text,
PRIMARY KEY (conf_name)
);
# --------------------------------------------------------
#
# Structure de la table 'puntestforums'
#
CREATE TABLE puntestforums (
id int(10) unsigned NOT NULL auto_increment,
forum_name varchar(80) DEFAULT 'New forum' NOT NULL,
forum_desc text,
moderators text,
num_topics mediumint(8) unsigned DEFAULT '0' NOT NULL,
num_posts mediumint(8) unsigned DEFAULT '0' NOT NULL,
last_post int(10) unsigned,
last_post_id int(10) unsigned,
last_poster varchar(200),
closed tinyint(1) DEFAULT '0' NOT NULL,
admmod_only tinyint(1) DEFAULT '0' NOT NULL,
disp_position int(10) DEFAULT '0' NOT NULL,
cat_id int(10) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
# --------------------------------------------------------
#
# Structure de la table 'puntestonline'
#
CREATE TABLE puntestonline (
user_id int(10) unsigned DEFAULT '0' NOT NULL,
ident varchar(200) NOT NULL,
logged int(10) unsigned DEFAULT '0' NOT NULL
);
# --------------------------------------------------------
#
# Structure de la table 'puntestposts'
#
CREATE TABLE puntestposts (
id int(10) unsigned NOT NULL auto_increment,
poster varchar(200) NOT NULL,
poster_id int(10) unsigned DEFAULT '1' NOT NULL,
poster_ip varchar(15),
poster_email varchar(50),
message text NOT NULL,
smilies tinyint(1) DEFAULT '1' NOT NULL,
posted int(10) unsigned DEFAULT '0' NOT NULL,
edited int(10) unsigned,
edited_by varchar(200),
topic_id int(10) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
# --------------------------------------------------------
#
# Structure de la table 'puntestranks'
#
CREATE TABLE puntestranks (
id int(10) unsigned NOT NULL auto_increment,
rank varchar(50) NOT NULL,
min_posts mediumint(8) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
# --------------------------------------------------------
#
# Structure de la table 'puntestreports'
#
CREATE TABLE puntestreports (
id int(10) unsigned NOT NULL auto_increment,
post_id int(10) unsigned DEFAULT '0' NOT NULL,
topic_id int(10) unsigned DEFAULT '0' NOT NULL,
forum_id int(10) unsigned DEFAULT '0' NOT NULL,
reported_by int(10) unsigned DEFAULT '0' NOT NULL,
created int(10) unsigned DEFAULT '0' NOT NULL,
message text NOT NULL,
zapped int(10) unsigned,
zapped_by int(10) unsigned,
PRIMARY KEY (id)
);
# --------------------------------------------------------
#
# Structure de la table 'puntestsearch_matches'
#
CREATE TABLE puntestsearch_matches (
post_id int(10) unsigned DEFAULT '0' NOT NULL,
word_id int(10) unsigned DEFAULT '0' NOT NULL,
subject_match tinyint(1) DEFAULT '0' NOT NULL
);
# --------------------------------------------------------
#
# Structure de la table 'puntestsearch_results'
#
CREATE TABLE puntestsearch_results (
id int(10) unsigned DEFAULT '0' NOT NULL,
ident varchar(200) NOT NULL,
search_data text NOT NULL,
PRIMARY KEY (id)
);
# --------------------------------------------------------
#
# Structure de la table 'puntestsearch_words'
#
CREATE TABLE puntestsearch_words (
id int(10) unsigned NOT NULL auto_increment,
word varchar(20) binary NOT NULL,
PRIMARY KEY (word),
KEY puntestsearch_words_id_idx (id)
);
# --------------------------------------------------------
#
# Structure de la table 'puntestsubscriptions'
#
CREATE TABLE puntestsubscriptions (
user_id int(10) unsigned DEFAULT '0' NOT NULL,
topic_id int(10) unsigned DEFAULT '0' NOT NULL
);
# --------------------------------------------------------
#
# Structure de la table 'puntesttopics'
#
CREATE TABLE puntesttopics (
id int(10) unsigned NOT NULL auto_increment,
poster varchar(200) NOT NULL,
subject varchar(255) NOT NULL,
posted int(10) unsigned DEFAULT '0' NOT NULL,
last_post int(10) unsigned DEFAULT '0' NOT NULL,
last_post_id int(10) unsigned DEFAULT '0' NOT NULL,
last_poster varchar(200),
num_views mediumint(8) unsigned DEFAULT '0' NOT NULL,
num_replies mediumint(8) unsigned DEFAULT '0' NOT NULL,
closed tinyint(1) DEFAULT '0' NOT NULL,
sticky tinyint(1) DEFAULT '0' NOT NULL,
moved_to int(10) unsigned,
forum_id int(10) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
# --------------------------------------------------------
#
# Structure de la table 'puntestusers'
#
CREATE TABLE puntestusers (
id int(10) unsigned NOT NULL auto_increment,
username varchar(200) NOT NULL,
password varchar(40) NOT NULL,
email varchar(50) NOT NULL,
title varchar(50),
realname varchar(40),
url varchar(100),
icq varchar(12),
msn varchar(50),
aim varchar(20),
yahoo varchar(20),
location varchar(30),
use_avatar tinyint(1) DEFAULT '0' NOT NULL,
signature text,
disp_topics tinyint(3) unsigned,
disp_posts tinyint(3) unsigned,
email_setting tinyint(1) DEFAULT '1' NOT NULL,
save_pass tinyint(1) DEFAULT '1' NOT NULL,
notify_with_post tinyint(1) DEFAULT '0' NOT NULL,
smilies tinyint(1) DEFAULT '1' NOT NULL,
show_img tinyint(1) DEFAULT '1' NOT NULL,
show_avatars tinyint(1) DEFAULT '1' NOT NULL,
show_sig tinyint(1) DEFAULT '1' NOT NULL,
link_to_new_win tinyint(1) DEFAULT '1' NOT NULL,
timezone tinyint(2) DEFAULT '0' NOT NULL,
style varchar(25) DEFAULT 'Oxygen' NOT NULL,
num_posts int(10) unsigned DEFAULT '0' NOT NULL,
status tinyint(1) DEFAULT '-1' NOT NULL,
last_post int(10) unsigned,
registered int(10) unsigned DEFAULT '0' NOT NULL,
last_visit int(10) unsigned DEFAULT '0' NOT NULL,
last_action int(10) unsigned DEFAULT '0' NOT NULL,
admin_note varchar(30),
activate_string varchar(50),
activate_key varchar(8),
PRIMARY KEY (id)
);Very odd indeed. The dump you posted above does not contain any indexes. How did you create the dump? Through phpMyAdmin?
Would it be possible for me to have a look at it? If so, e-mail me.
Don't worry. We'll work it out somehow.
Very odd. When you install PunBB 1.1.5, does the install finish without any errors?
Yes. I haven't got any errors on the first DB.
I was thinking about the problem this night and about what you told me. You said phpmyadmin was a very old version on the first DB I gave you access.
I replied you that the problem was the same on a recent version of phpmyadmin on the second DB I gave you access.
I've forgotten to tell you that punbb 1.1.5 which works on the second DB was originally installed on an old version and then migrated on a recent version of phpmyadmin.
I hope you understand what I mean. I mean that the problem may have something to do with the old version.
To be sure, I installed 1.1.5 on the second DB (which has a recent version of phpmyadmin). I try to update it. I had another problem:
An error was encountered
File: /home/ju37853/pun/update.php
Line: 224
PunBB reported: Unable to add group
Database reported: Table 'ju37853.puntestgroups' doesn't exist (Errno: 1146)Any idea?
thanksfully,
Ludo
no help?
So, more simply, is it possible to convert 1.1.5 (missing _idx) to 1.2?
Are missing indexes so important? Why can 1.1.5 work without this indexes? Can 1.2?
Ludo,
Yes, you can run it without indexes. They just speed up the process of finding rows. If a database can use an index it doesn't have to search through each row in the table. Very similar to an index in a book.
I really have no idea why the hell all your installations get messed up. I'm not saying it's your fault. I just don't understand what's happening. I can help you add the 1.2 indexes manually, but it'll have to wait. I'm just too busy right now.
PunBB Forums → PunBB 1.2 bug reports → can't update
Powered by PunBB, supported by Informer Technologies, Inc.