Error when I upgrade from 3.03 to 3.6.7PL1

[odl2000]

Hello,

It's ok until 3.5 Beta 1 Step 1
But in the step 2, I have this error :

Step 2) Miscellaneous table alterations 2/6

• Altering useractivation Table (1 of 2)
• Altering useractivation Table (2 of 2)
• Altering usergrouprequest Table (1 of 1)
• Altering postparsed Table (1 of 6)
• Altering postparsed Table (2 of 6)
• Altering postparsed Table (3 of 6)
• Altering postparsed Table (4 of 6)
• Altering postparsed Table (5 of 6)
• Altering postparsed Table (6 of 6)
• Altering customavatar Table (1 of 2)
• Altering customavatar Table (2 of 2)
• Altering customprofilepic Table (1 of 2)
• Altering customprofilepic Table (2 of 2)
• Altering user Table (1 of 1)
• Altering subscription Table (1 of 4)

Database error The Forum myforum database has encountered a problem. Please try the following:

• Load the page again by clicking the refresh button in your web browser.
• Open the MYDOMAIN home page, then try to open another page.
• Click the Back button to try another link.

The mydomain forum technical staff have been notified of the error, though you may contact them if the problem persists.
We apologise for any inconvenience.
Database error in vBulletin 3.0.15:

Invalid SQL:
ALTER TABLE subscription CHANGE cost cost MEDIUMTEXT;

MySQL Error : BLOB/TEXT column 'cost' used in key specification without a key length
Error Number : 1170
Date : Friday, June 22nd 2007 @ 09:10:04 PM
Script : http://www.mydomain.com/forum-update...0b1.php?step=2
Referrer : http://www.mydomain.com/forum-update...0b1.php?step=1
IP Address : 82.227.131.56
Username :
Classname : vB_Database


Have you an Iidea ?

Thanks
odl

[Steve Machol]

Fill out a support ticket at:

http://members.vbulletin.com/members...ontactform.php

Please include a complete description of the problem and be sure to include the login info to your Admin CP, phpMyAdmin and FTP in the 'Sensitive Data' field.

[odl2000]

Thank you for reply, but I can't give access to my forum.

The problem is with this SQL query :

ALTER  TABLE subscription CHANGE cost cost MEDIUMTEXT 

The error message is :

BLOB/TEXT column 'cost' used in key specification without a key length 

The structure of my sql table is

CREATE TABLE subscription (
  subscriptionid smallint(5) unsigned NOT NULL auto_increment,
  title varchar(100) NOT NULL default '',
  description varchar(250) NOT NULL default '',
  cost varchar(255) NOT NULL default '',
  length varchar(10) NOT NULL default '',
  units char(1) NOT NULL default '',
  forums mediumtext NOT NULL,
  nusergroupid smallint(6) NOT NULL default '0',
  membergroupids varchar(255) NOT NULL default '',
  active smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (subscriptionid),
  KEY active (active),
  KEY cost (cost),
  KEY description (description),
  KEY length (length),
  KEY membergroupids (membergroupids),
  KEY nusergroupid (nusergroupid),
  KEY subscriptionid (subscriptionid),
  KEY title (title),
  KEY units (units)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

The server configuration is :

MySQL version 5.0.21
Apache version 2.0.58
PHP Version 5.1.4

With this information in the phpinfo :

 System
Linux ks34060.kimsufi.com 2.6.18.1-grsec-xxxx-grs-ipv4-64 #1 SMP Mon Oct 30 22:05:25 CET 2006 x86_64  Build Date Jun 12 2006 10:51:25  

Configure Command
'./configure' '--prefix=/usr/local/php5' '--with-config-file-path=/usr/local/lib/php5' '--with-pear=/usr/share/php5' '--with-dbase' '--with-filepro' '--with-xml' '--enable-exif' '--enable-ftp' '--with-db' '--enable-bcmath' '--enable-calendar' '--with-gd' '--enable-gd-native-ttf' '--with-freetype-dir' '--with-gettext' '--with-zlib-dir' '--enable-trans-sid' '--with-imap' '--with-kerberos' '--with-imap-ssl' '--with-openssl' '--enable-sysvsem' '--enable-sysvshm' '--with-dom' '--with-mcrypt' '--with-iconv' '--enable-mbstring=all' '--enable-mbregex' '--with-png-dir=/usr' '--with-jpeg-dir=/usr' '--with-mysql=/usr' '--with-mime-magic=/usr/share/misc/file/magic.mime' '--enable-cgi'

Server API
CGI

Virtual Directory Support
disabled

Configuration File (php.ini) Path
/usr/local/lib64/php5/php.ini

PHP API
20041225

PHP Extension
20050922

Zend Extension
220051025

Debug Build
no

Thread Safety
disabled

Zend Memory Manager
enabled

IPv6 Support
enabled

Registered PHP
Streams php, file, http, ftp, compress.zlib, https, ftps

Registered Stream Socket Transports
tcp, udp, unix, udg, ssl, sslv3, sslv2, tls

Registered Stream Filters
string.rot13, string.toupper, string.tolower, string.strip_tags, convert.*, consumed, convert.iconv.*, zlib.* 

[odl2000]

It need a size, so I replace :

KEY cost (cost) 

by

KEY cost (cost(30)) 

But I have the same problem on step 3

Invalid SQL:
ALTER TABLE administrator CHANGE navprefs navprefs MEDIUMTEXT;

MySQL Error : BLOB/TEXT column 'navprefs' used in key specification without a key length
Error Number : 1170

Could you just said me if size = 30 is ok for a key of MEDIUMTEXT ?

[odl2000]

So I replace :

CREATE TABLE administrator (
  userid int(10) unsigned NOT NULL default '0',
  adminpermissions int(10) unsigned NOT NULL default '0',
  navprefs mediumtext,
  cssprefs varchar(250) NOT NULL default '',
  notes mediumtext NOT NULL,
  languageid smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (userid),
  KEY adminpermissions (adminpermissions),
  KEY cssprefs (cssprefs),
  KEY userid (userid),
  KEY navprefs (navprefs)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

by

CREATE TABLE administrator (
  userid int(10) unsigned NOT NULL default '0',
  adminpermissions int(10) unsigned NOT NULL default '0',
  navprefs mediumtext,
  cssprefs varchar(250) NOT NULL default '',
  notes mediumtext NOT NULL,
  languageid smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (userid),
  KEY adminpermissions (adminpermissions),
  KEY cssprefs (cssprefs),
  KEY userid (userid),
  KEY navprefs (navprefs(30))
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

KEY navprefs (navprefs(30))

[odl2000]

3.5.0 beta 2

Invalid SQL:
ALTER TABLE forum CHANGE description description TEXT;

MySQL Error : BLOB/TEXT column 'description' used in key specification without a key length
Error Number : 1170

CREATE TABLE forum (
  forumid smallint(5) unsigned NOT NULL auto_increment,
  styleid smallint(5) unsigned NOT NULL default '0',
  title char(100) NOT NULL default '',
  description char(250) NOT NULL default '',
  displayorder smallint(6) NOT NULL default '0',
  replycount int(10) unsigned NOT NULL default '0',
  lastpost int(11) NOT NULL default '0',
  lastposter varchar(100) NOT NULL default '',
  threadcount mediumint(8) unsigned NOT NULL default '0',
  daysprune smallint(6) NOT NULL default '0',
  newpostemail char(250) NOT NULL default '',
  newthreademail char(250) NOT NULL default '',
  parentid smallint(6) NOT NULL default '0',
  parentlist char(250) NOT NULL default '',
  lastthread char(250) NOT NULL default '',
  lastthreadid int(10) unsigned NOT NULL default '0',
  `password` char(50) NOT NULL default '',
  link char(200) NOT NULL default '',
  lasticonid smallint(6) NOT NULL default '0',
  options int(10) unsigned NOT NULL default '0',
  childlist char(250) NOT NULL default '',
  title_clean varchar(100) NOT NULL,
  description_clean text,
  PRIMARY KEY  (forumid),
  KEY childlist (childlist),
  KEY daysprune (daysprune),
  KEY displayorder (displayorder),
  KEY forumid (forumid),
  KEY lasticonid (lasticonid),
  KEY lastpost (lastpost),
  KEY lastposter (lastposter),
  KEY lastthread (lastthread),
  KEY lastthreadid (lastthreadid),
  KEY link (link),
  KEY newpostemail (newpostemail),
  KEY newthreademail (newthreademail),
  KEY options (options),
  KEY parentid (parentid),
  KEY parentlist (parentlist),
  KEY `password` (`password`),
  KEY replycount (replycount),
  KEY styleid (styleid),
  KEY threadcount (threadcount),
  KEY title (title),
  KEY description (description)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

I replace :

  KEY description (description) 

by

  KEY description (description(30)) 

[odl2000]

3.6.0 beta 1

Step 3

Database error in vBulletin 3.5.5:

Invalid SQL:
ALTER TABLE forum CHANGE childlist childlist TEXT;

MySQL Error : BLOB/TEXT column 'childlist' used in key specification without a key length
Error Number : 1170

In :

CREATE TABLE forum (
  forumid smallint(5) unsigned NOT NULL auto_increment,
  styleid smallint(5) unsigned NOT NULL default '0',
  title char(100) NOT NULL default '',
  description text,
  displayorder smallint(6) NOT NULL default '0',
  replycount int(10) unsigned NOT NULL default '0',
  lastpost int(11) NOT NULL default '0',
  lastposter varchar(100) NOT NULL default '',
  threadcount mediumint(8) unsigned NOT NULL default '0',
  daysprune smallint(6) NOT NULL default '0',
  newpostemail char(250) NOT NULL default '',
  newthreademail char(250) NOT NULL default '',
  parentid smallint(6) NOT NULL default '0',
  parentlist char(250) NOT NULL default '',
  lastthread char(250) NOT NULL default '',
  lastthreadid int(10) unsigned NOT NULL default '0',
  `password` char(50) NOT NULL default '',
  link char(200) NOT NULL default '',
  lasticonid smallint(6) NOT NULL default '0',
  options int(10) unsigned NOT NULL default '0',
  childlist char(250) NOT NULL default '',
  title_clean varchar(100) NOT NULL,
  description_clean text,
  PRIMARY KEY  (forumid),
  KEY daysprune (daysprune),
  KEY displayorder (displayorder),
  KEY forumid (forumid),
  KEY lasticonid (lasticonid),
  KEY lastpost (lastpost),
  KEY lastposter (lastposter),
  KEY lastthread (lastthread),
  KEY lastthreadid (lastthreadid),
  KEY link (link),
  KEY newpostemail (newpostemail),
  KEY newthreademail (newthreademail),
  KEY options (options),
  KEY parentid (parentid),
  KEY parentlist (parentlist),
  KEY `password` (`password`),
  KEY replycount (replycount),
  KEY styleid (styleid),
  KEY threadcount (threadcount),
  KEY title (title),
  KEY description (description),
  KEY childlist (childlist(30))
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

I replace :

  KEY description (description), 

by

  KEY description (description(30)), 

[odl2000]

3.6.0 beta 1

Step 7

Database error in vBulletin 3.5.5:

Invalid SQL:
ALTER TABLE post_parsed RENAME postparsed;

MySQL Error : Table 'postparsed' already exists
Error Number : 1050

So I delete postparsed table and I refresh.

[Wayne Luke]

The problem is that you have indexes on a lot extra fields. This is not standard. IT would be easier to simply drop the invalid indexes since they are most likely slowing down your forum as it is.

The postparsed is most likely from repeated running the upgrade scripts and making manual database alterations at the same time. You have a lot of problems with your current database structure.

[odl2000]

3.6.6

step 1

Database error in vBulletin 3.6.5:

Invalid SQL:
ALTER TABLE calendar CHANGE neweventemail neweventemail TEXT;

MySQL Error : BLOB/TEXT column 'neweventemail' used in key specification without a key length
Error Number : 1170

In :

CREATE TABLE calendar (
  calendarid int(10) unsigned NOT NULL auto_increment,
  title varchar(255) NOT NULL default '',
  description varchar(100) NOT NULL default '',
  displayorder smallint(6) NOT NULL default '0',
  neweventemail varchar(255) NOT NULL default '',
  moderatenew smallint(6) NOT NULL default '0',
  startofweek smallint(6) NOT NULL default '0',
  options int(10) unsigned NOT NULL default '0',
  cutoff smallint(5) unsigned NOT NULL default '0',
  eventcount smallint(5) unsigned NOT NULL default '0',
  birthdaycount smallint(5) unsigned NOT NULL default '0',
  startyear smallint(5) unsigned NOT NULL default '2000',
  endyear smallint(5) unsigned NOT NULL default '2006',
  holidays int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (calendarid),
  KEY displayorder (displayorder),
  KEY birthdaycount (birthdaycount),
  KEY calendarid (calendarid),
  KEY cutoff (cutoff),
  KEY description (description),
  KEY endyear (endyear),
  KEY eventcount (eventcount),
  KEY holidays (holidays),
  KEY moderatenew (moderatenew),
  KEY options (options),
  KEY startofweek (startofweek),
  KEY startyear (startyear),
  KEY title (title),
  KEY neweventemail (neweventemail)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

I replace :

  KEY neweventemail (neweventemail) 

by

  KEY neweventemail (neweventemail(30)) 

IDEM with forum table

replace

  KEY newpostemail (newpostemail) 

by

  KEY newpostemail (newpostemail(30)) 

and

 KEY newthreademail (newthreademail) 

by

 KEY newthreademail (newthreademail(30)) 

[odl2000]

Just could you just said me if size = 30 is ok for a key of MEDIUMTEXT ?

[odl2000]

Thank you,

I think I have many indexes, because, my database forum was very big and with lot of visitor online.

So long time ago, the admin of the server did this.

[Wayne Luke]

Just could you just said me if size = 30 is ok for a key of MEDIUMTEXT ?

You shouldn't have any indexes that require a size on Mediumtext fields. That is the issue. I would say they don't need any indexes on them at all because we're not going to use them in a where clause except for fulltext searching and those indexes only need to be on a few fields.

Having indexes on these fields will only slow down writes and degrade the performance of your site.

So long time ago, the admin of the server did this.

He didn't know what he was doing.

[odl2000]

Thank you I will remove them.