Upgrade Error: 3.8.4 pl2 to 4.0.1 Suite - mysql error

[Rik Brown]

Done. -- Rik

ps: And thank you mustang02.

[Mornagest]

Hello, Got the same problem... I'm actually trying to upgrade a copy of my forum from 3.8.5 to 4.0.2, but I encountered some problems while running the upgrade script. I duplicated my database via ssh, gave the mysql permissions to the new database, I created a new mysql-user with the appropriated rights. I also duplicated my live forum (and edited the config.php file to point the forum towards the appropriate database), so I have a completely independant-forum to test the migration. And, after uploading the v4.0.2 files, I began to run the script. At the second step, I got this error :

Database error in vBulletin 3.8.5: Invalid SQL: CREATE TABLE vb_contenttype ( contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT, class VARBINARY(50) NOT NULL, packageid INT UNSIGNED NOT NULL, canplace ENUM('0','1') DEFAULT '0', cansearch ENUM('0','1') DEFAULT '0', cantag ENUM('0','1') DEFAULT '0', canattach ENUM('0','1') DEFAULT '0', isaggregator ENUM('0','1') NOT NULL DEFAULT '0', PRIMARY KEY (contenttypeid), UNIQUE KEY package (packageid, class) ) ENGINE=InnoDB; MySQL Error : Can't create table './testupgradev4/vb_contenttype.frm' (errno: 121) Error Number : 1005 Request Date : Sunday, March 14th 2010 @ 04:30:31 PM Error Date : Sunday, March 14th 2010 @ 04:30:31 PM
Script : http://www.mysite.fr/testupgradev4/i...0a1.php?step=2
Referrer : http://www.mysite.fr/testupgradev4/i...0a1.php?step=1

IP Address : Username : Classname : vB_Database MySQL Version : 5.0.32-Debian_7etch4-log

Can I do something but waiting for an update ? Thanks ! Edit : sorry for the layout, I cannot preserve it...

[Rik Brown]

I would suggest reporting it in the bug tracker (click on the link in message #12 above). The more reports the more chance the bug can get fixed for others.

I completed the installation by taking the steps I indicated above (message #13) and have been running 4.0.2 without problems since.

-- Rik

[Mornagest]

Okay, I will make an attempt tonight.

Thanks, Rik ! (I also posted in the bug tracker )

[Yves R.]

I'm marked this as confirmed only for devs checking if this is a vBulletin bug or MySQL problem (as I don't know how to reproduce it - I don't have any large database to check). See the bug report to see what is.

[fabioski]

2) The next problem is at Step 12 where it adds post and thread table indexes. It runs for about 20 minutes and stops (I see via the unix top command almost nil mysql activity so I assume its either completed or hung). I move onto Step 13 and the process continues.

I have the same problem. How did you fix it?
Thanks.

[Rik Brown]

I backed up the script containing Step 12 (indexing) and then modified the script.

Scan it for "== 12" which should take you to Step 12. About 13 lines below that is this line:

if ($postcount['postcount'] < 1200000)

I changed that to:

if ($postcount['postcount'] = 1200000)

In other words, unless you have exactly 1200000 posts, it will execute the alternate "else" code there (I'm sure there are better ways to do this, but this was simple).

After the installation was complete up through 4.0.2, I built the search index bypassed in step 12:
admincp -> Maintenance -> Update Counters -> Rebuild Search Index

Hope that works for you. -- Rik

ps: If you have a large board, indexing can take a long time. It took us about 2 days for 800,000 posts on a dedicated mysql server. That's why it may have choked (or appear to) on Step 12.

[StevenTN]

I encountered the same problem myself in having to do a repeat upgrade from 3.8.4 to 4.0.4 on my test site (after having done a previous upgrade to 4.0.3, then screwed it up while working with 4.0.4). The problem is neither MySQL nor vBulletin, and can actually be caused by human error, particularly deleting a database or InnoDB table, since MySQL stores info on InnoDB tables in special transaction tables. One common way of doing it is deleting the table within the server's filesystem instead of doing a drop database or drop table within MySQL.

Normally errno 121 (Error 1005) indicates a filesystem problem of some sort. However, I could create a MyISAM table with the name contenttype with no problem, and another InnoDB table with no problem. Just when I did one of contenttype with type InnoDB, then it'd fail.

Basically, to resolve it, you're going to have to trick the InnoDB engine into deleting it, which is normally a tricky proposition, but this is the easiest way to do so...

To fix it with vBulletin, here's what you need to do...

For this, we'll assume the problem database is vb-problem

1. Create a new, fresh vBulletin 4.x installation on a different database, call it vb-fresh, and make sure you have any table prefix names matching what's in your problem database.
2. Preferably with a tool such as phpMyAdmin, drop the vb-problem table, then copy the entire vb-fresh database (structure and data) to a new database that has the same name (of vb-problem). Believe it or not, this part works.
3. Once done (again with phpMyAdmin), properly drop both the vb-fresh and vb-problem databases
4. Restore your 3.8.x backup of the vb-problem database. Be sure that you repair and optimize your tables (at least 2-3 times).
5. Proceed with your 4.0.x upgrade.

Because of my hatred of InnoDB tables, I'm going to try converting mine to MyISAM, since they do tend to run faster.

FYI: The forums we operate have about 757,000 posts with over 43,000 threads and 12,000 members. Following those steps helped solve my problem.

[Sasha~CPF]

Still having this same problem with trying to upgrade from 3.8.4 to 4.0.7. Any new advise? I have not tried the "fix" in the previous post. Is there any plan to try and fix this in future versions?

[Trevor Hannant]

What problem are you havign exactly as I see more than one in this thread...

If you're receiving a database update error, then please post the full error here (if the same) or open a new thread for your issue (if different).

[Sasha~CPF]

Sorry... the Step 2 problem with creating the contenttype table:

Database error in vBulletin 3.8.4:
Invalid SQL:
CREATE TABLE vb3_contenttype (
   contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT,
   class VARBINARY(50) NOT NULL,
   packageid INT UNSIGNED NOT NULL,
   canplace ENUM('0','1') DEFAULT  '0',
   cansearch ENUM('0','1') DEFAULT '0',
   cantag ENUM('0','1') DEFAULT '0',
   canattach ENUM('0','1') DEFAULT '0',
   isaggregator ENUM('0','1') NOT NULL DEFAULT '0',
   PRIMARY KEY (contenttypeid),
   UNIQUE KEY package (packageid, class)
  ) ENGINE=InnoDB;
MySQL Error   : Can't create table './mysite/vb3_contenttype.frm' (errno: 121)
Error Number  : 1005
Request Date  : Tuesday, October 19th 2010 @ 09:14:14 PM
Error Date    : Tuesday, October 19th 2010 @ 09:14:14 PM
Script        : [URL]http://www.mysite/install/upgrade_400a1.php?step=2[/URL]
Referrer      : [URL]http://www.mysite/install/upgrade_400a1.php?step=1[/URL]
IP Address    : My IP
Username      : 
Classname     : vB_Database
MySQL Version : 5.0.45

[Trevor Hannant]

This is a server error code:

OS error code 121: Remote I/O error

I'm afraid you will need to contact your host and ask them to investigate and fix this.

[Sasha~CPF]

I guess I'm a bit confused. Why was it not considered a server error code previously in post #s 1 & 17? Why were those reports handled as bugs and I am told to go talk to my host and tell them to fix it? Yes... confused just a little bit.

This is a server error code:

OS error code 121: Remote I/O error

I'm afraid you will need to contact your host and ask them to investigate and fix this.

[aa7bq]

Same problem here. The easy fix (for me) was to use MyIsam instead of Innodb. The change is easy.

In the file: install/functions_installupgrade.php

find the function get_high_concurrency_table_engine($db)

Just add this line at the top of the function:


return 'MyISAM';

This way, InnoDB is never chosen.

Once I did this, all of the problems described in this thread went away.

[kontrabass]

So, over a year has passed since this bug was reported (the 121 error), and it's still marked in the tracker as a MAJOR bug that is confirmed, and is still "unassigned". Should I wait another year?

Upgrading via console:

Upgrading to 3.8.0
----------------------------------
Upgrade Complete

Upgrading to 4.0.0 Alpha 1
----------------------------------
Step 1 - Altering reputation Table (1 of 4)
Step 2 - Altering reputation Table (2 of 4)
Step 3 - Altering reputation Table (3 of 4)
Step 4 - Altering reputation Table (4 of 4)
Step 5 - Altering subscribegroup Table (1 of 1)
Step 6 - Altering thread Table (1 of 1)
Step 7 - Altering reputation Table (1 of 2)
Step 8 - Altering reputation Table (2 of 2)
Step 9 - Altering subscribegroup Table (1 of 1)
Step 10 - Altering forum Table (1 of 1)
Step 11 - Altering thread Table (1 of 2)
Step 12 - Altering thread Table (2 of 2)
Step 13 - Altering tachyforumpost Table (1 of 1)
Step 14 - Altering tachythreadpost Table (1 of 1)
Step 15 - Altering style Table (1 of 1)
Step 16 - Altering template Table (1 of 1)
Step 17 - Creating templatemerge table
Step 18 - Updating adminmessage table
Step 19 - Updating adminmessage table
Step 20 - Updating adminmessage table
Step 21 - Altering tag Table (1 of 1)
Step 22 - Creating index canonicaltagid on tag
Step 23 - Updating phrasetype table

----------------------------------
Error on rename of './forum4test/#sql-1350_97606' to './forum4test/tag' (errno: 121)
----------------------------------
ALTER TABLE tag ENGINE=InnoDB