Upgrading from 3.6.7 to 3.8, problem on one of the steps

[Swede]

I received an error in the upgrade script on my test system that I wasn't expecting. Any ideas why I received it at this stage? I can't find this statement in the file it says it's in. Shouldn't this be "ENGINE = MYISAM" instead? I can't find where this statement is at.

Database error in vBulletin 3.7.1:
Invalid SQL:
    ALTER TABLE groupmessage
    TYPE = MYISAM;
MySQL Error   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right 
syntax to use near 'TYPE = MYISAM' at line 2
Error Number  : 1064
Request Date  : Sunday, February 27th 2011 @ 10:43:03 AM
Error Date    : Sunday, February 27th 2011 @ 10:43:03 AM
Script        : [URL]http://www.internaltest1.com/forums/install/upgrade_380a2.php?step=3[/URL]
Referrer      : [URL]http://www.internaltest1.com/forums/install/upgrade_380a2.php?step=2[/URL]
IP Address    : 192.168.1.106
Username      : 
Classname     : vB_Database
MySQL Version : 5.5.9

[Steve Machol]

SQL Syntax errors are almost always related to modified code. To troubleshoot this, first reupload all the original vB non-image files (except install.php). Make sure you upload these in ASCII format and overwrite the ones on the server. Also be sure to upload the admincp files to whichever directory you have set in your config.php file.

The try again. Make sure the version you are upgrading to appear in the uppoer right-hand corner of the page.

[Swede]

I've modified very little on my site. Mostly added graphics and edited the templates for how the site looks. I added the vbAd Manager and that was about it. I just did a search through *.PHP files on the site and can't find this statement except in two of the upgrade scripts; upgrade_352.php and upgrade_300B3.php. Can't figure out where else this might be coming from. I may just wipe out all of the current *.php files and just load those from the 3.8 update and see if that helps. I may have modified something a long time ago and just don't remember.

I don't dare try this on the live site until I can get this resolved.

[Swede]

Ok... I've just deleted every PHP file on the site except the images and the cpstyles directories. Copied all the files from the 3.8 install and it's doing the exact same thing when running the install.php at the same spot -- no change. Is it possible this statement is in a database table someplace? That's where I'm headed off to check next as it doesn't appear to be in any of the files.

This is the last successful items listed before the error:

[B][U]Step 3) Group Discussion Conversions[/U][/B][LIST][*]Updating groupmessage table[*]Altering index on groupmessage[*]Altering groupmessage Table (1 of 1)[*]Creating index for groupmessage[*]Creating fulltext index for groupmessage[/LIST]

[Swede]

Do I need to switch to an old version of MySQL to make this work? I noticed up on my web that the version running there is 5.0.19 and I'm running 5.5.9 on my test sever. I've also tried 5.2 and 5.3 versions of PHP to see if that made a difference but no go. I can't find the problem anywhere at the moment. I've purchased the 4.x suite version of vBulletin but according to the documentation I need to bring my current version up to 3.8 before jumping to the new stuff.

[Trevor Hannant]

I think the version of MySQL you're running can't handle the TYPE=. If you check the upgrade file you're having the error on (upgrade_380a2.php) and change TYPE= to ENGINE= then re-run the upgrade at that step, does this resolve the problem?

[AlphaSoftware]

We are having this exact same problem on the exact same step. Did you solve the problem?

There is no "TYPE =" in upgrade_380a2.php to change. I was able to isolate the error as coming from the following block at line 372:

$upgrade->add_index(
            sprintf($upgrade_phrases['upgrade_380a2.php']['fulltext_index_on_x'], TABLE_PREFIX . 'groupmessage'),
            'groupmessage',
            'gm_ft',
            array('title', 'pagetext'),
            'fulltext'
        );

What I can't find is where the actual alter is being done. I searched everywhere under install and changed every occurance of TYPE to ENGINE, even though most of them were conditionalized on MYSQL_VERSION, with no change in the error.

[Trevor Hannant]

That's not the same error - the OP relates to a change of the table type - yours, if you've picked out the correct part of the code, relates to Indexing.

Please start your own thread with the relevant error message included also.

[Swede]

That's pretty much what I was running into. The TYPE= that was in the error doesn't exist in the file the error was coming from. I searched all of the files for that and couldn't find it. In the end I had to revert to an old version of mySQL and that fixed it. I believe it was 5.0. The upgrade does not work with the latest version of mySQL or since they dropped support for it in mySQL.

[Paul M]

The installer checks the mysql version and uses TYPE if its older than 4.0.18, or ENGINE otherwise.

[AlphaSoftware]

That's not the same error - the OP relates to a change of the table type - yours, if you've picked out the correct part of the code, relates to Indexing.

Please start your own thread with the relevant error message included also.

This is the exact error I am having and in the exact same place that I am having it. I can post it again, but it looks 100% identical to the OP's.

While the code I posted is in fact code to add an index, it is trying to add a fulltext index to what is currently an InnoDB table. InnoDB does not support fulltext and there must be some logic somewhere that recognizes this and attempts to change the table type, but I have been unable to locate it.

The installer checks the mysql version and uses TYPE if its older than 4.0.18, or ENGINE otherwise.

That's true in most cases, but I found a few places where it does not. Unfortunately, I have not yet found the one causing the error in my current situation.

[Jimbot]

This appears to be a bug. I concur with AlphaSoftware that it is true in most cases that it checks the mysql version and uses ENGINE where appropriate, but not in all cases. For this issue in particular, it is obviously still using "TYPE" instead of "ENGINE".

I found the issue in .\includes\class_dbalter.php in the function convert_table_type($type), line 443. I resolved this by adding the following line at the top of the function:

$enginetype = (version_compare(MYSQL_VERSION, '4.0.18', '<')) ? 'TYPE' : 'ENGINE';

Then I changed

$this->sql = "
ALTER TABLE " . TABLE_PREFIX . $this->db->escape_string($this->table_name) . "
TYPE = " . $this->db->escape_string(strtoupper($type));


to:

$this->sql = "
ALTER TABLE " . TABLE_PREFIX . $this->db->escape_string($this->table_name) . "
" . $enginetype . " = " . $this->db->escape_string(strtoupper($type));

Making this change fixed the problem, and allowed me to proceed with the install.

It's a shame that VBulletin support wasn't able to help with this.

[Paul M]

This is not a bug.

The minimum supported MySQL version for vB4 is 4.1.0, therefore no check is needed (4.1 onwards supports ENGINE).

JFYI, vBulletin support are not developers.

[AlphaSoftware]

The installer checks the mysql version and uses TYPE if its older than 4.0.18, or ENGINE otherwise.

The minimum supported MySQL version for vB4 is 4.1.0, therefore no check is needed (4.1 onwards supports ENGINE).

So which is it? At the risk of sounding unnecessarily argumentative, it seems at though you are just trying to dismiss this. Jimbot has done the legwork and found the exact change to be made (thank you Jimbot!) so it should be a minimal amount of work to verify his change and pull it into the VB distribution. I spent many wasted hours trying to upgrade with no indication that an upgrade from < 4.1.0 would not work. In fact it seems to be just the opposite of that, as VB jumps through all sorts of hoops to get us 95% of the way there, and falls short here. Incorporating Jimbot's work would be a nice improvement to VB.

[Paul M]

So which is it?

Since the first post was made over four months ago, I think you can figure out which it is.

Just to be clear, what I posted in April is out of date, and superceeded by what I posted on Monday.

(It was probably out of date in April as well).