[Suite] Can't upgrade from vb3 to vb4 4.1.8

[cauutcom]

Error
How to fix?
Help me, please!

[Zachery]

What does the bottom of that error message say?

[cauutcom]

Unexpected Text:
<?xml version="1.0" encoding="windows-1252"?>
<?xml version="1.0" encoding="windows-1252"?>
<errors>
<error><![CDATA[<p>Database Error</p>

Invalid SQL:

(SELECT 'package' AS classtype, package.packageid AS typeid, package.packageid AS packageid,
package.productid AS productid, if(package.productid = 'vbulletin', 1, product.active) AS enabled,
package.class AS class, -1 as isaggregator
FROM package AS package
LEFT JOIN product AS product
ON product.productid = package.productid
WHERE product.active = 1
OR package.productid = 'vbulletin'
)

UNION

(SELECT 'contenttype' AS classtype, contenttypeid AS typeid, contenttype.packageid AS packageid,
1, 1, contenttype.class AS class , contenttype.isaggregator
FROM contenttype AS contenttype
INNER JOIN package AS package ON package.packageid = contenttype.packageid
LEFT JOIN product AS product ON product.productid = package.productid
WHERE product.active = 1
OR package.productid = 'vbulletin' );

Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=']]></error>
<error_html><![CDATA[<p>Database error in vBulletin 3.8.2</p>
<p>Invalid SQL:

(SELECT 'package' AS classtype, package.packageid AS typeid, package.packageid AS packageid,
package.productid AS productid, if(package.productid = 'vbulletin', 1, product.active) AS enabled,
package.class AS class, -1 as isaggregator
FROM package AS package
LEFT JOIN product AS product
ON product.productid = package.productid
WHERE product.active = 1
OR package.productid = 'vbulletin'
)

UNION

(SELECT 'contenttype' AS classtype, contenttypeid AS typeid, contenttype.packageid AS packageid,
1, 1, contenttype.class AS class , contenttype.isaggregator
FROM contenttype AS contenttype
INNER JOIN package AS package ON package.packageid = contenttype.packageid
LEFT JOIN product AS product ON product.productid = package.productid
WHERE product.active = 1
OR package.productid = 'vbulletin' );<p>
<p>
<strong>MySQL Error</strong> : Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='<br />
<strong>Error Number</strong> : 1267<br />
<strong>Request Date</strong> : Sunday, May 15th 2011 @ 07:05:24 AM<br />
<strong>Error Date</strong> : Sunday, May 15th 2011 @ 07:05:24 AM<br />
<strong>Script</strong> : http://xxx.com/4rum/install/upgrade.php<br />
<strong>Referrer</strong> : http://xxx.com/4rum/install/upgrade.php<br />
<strong>Classname</strong> : vB_Database<br />
<strong>MySQL Version</strong> : 5.0.67-community<br />
</p>]]></error_html>
</errors>

(((

[TheNewOne]

your collation is wrong

[cauutcom]

How to fix it? Help me

[Trevor Hannant]

When vBulletin creates a new table in the upgrade process, it asks MySQL what the default collation for the database is and then uses that. Most MySQL installations default to latin1_swedish_ci. If you have changed collations for any reason on tables in the past then you should change the database to use that as the default to prevent this issue from happening in the future.

You will find more information in the MySQL manual here:

http://dev.mysql.com/doc/refman/5.1/en/charset.html

You will need to use phpMyAdmin and make sure that all tables and fields within them have the same collation. Make sure your database collations are appropriate and consistent throughout the entire database. You need to use program like phpmyadmin to view your collations:

http://www.phpmyadmin.net/home_page/index.php

When you click your database name in phpmyadmin it will list all tables, their collations, as well as the collation of the database itself at the bottom. And when you click the name of a table on the left it will list the collations of individual fields within that table.

1) To edit the collation of the database itself you need to click the database name on the left and then click "Operations" on the top.

2) To edit the collation of an individual table you need to click its name on the left and then click "Operations" on the top.

3) To edit the collation of an individual field within a table you need to click the table name on the left and then click the edit image (a little pencil icon) for that field.

For more information please see: http://www.vbulletin.com/forum/showt...ight=collation

Note: It is very very very important to make full database backups before proceeding if you are ever manually making any changes to your database, let it be issuing queries, or editing it via phpMyAdmin or any other interface. If done incorrectly, manually modifying the database can potentially cause irreversible damage to your database, and there is no way to perform a "partial restore" to restore just the part you may break.

[cauutcom]

how to change collation for multiple table at once, because there are many tables with different collation
Sorry i'm not good at english

[Trevor Hannant]

You would be best to do them one at a time...

[GavoTrav]

I have the exact same thing any help? I dont feel like changing the collation 1 by 1, if it has to be done I will but I'd like to know if it works first

[Zachery]

You should do it one by one.

[GavoTrav]

You should do it one by one.

If it does not work after doing this will I come back and post my own seperate thread? I Just thought it would be good to post in this thread seen as its nearly the same issue

[cauutcom]

after change all the collations into latin1_swedish_ci, but still error:

Unexpected Text:
<?xml version="1.0" encoding="windows-1252"?>
<?xml version="1.0" encoding="windows-1252"?>
<errors>
<error><=!=[=C=D=A=T=A=[<p>Database Error</p>

Invalid SQL:

(SELECT 'package' AS classtype, package.packageid AS typeid, package.packageid AS packageid,
package.productid AS productid, if(package.productid = 'vbulletin', 1, product.active) AS enabled,
package.class AS class, -1 as isaggregator
FROM package AS package
LEFT JOIN product AS product
ON product.productid = package.productid
WHERE product.active = 1
OR package.productid = 'vbulletin'
)

UNION

(SELECT 'contenttype' AS classtype, contenttypeid AS typeid, contenttype.packageid AS packageid,
1, 1, contenttype.class AS class , contenttype.isaggregator
FROM contenttype AS contenttype
INNER JOIN package AS package ON package.packageid = contenttype.packageid
LEFT JOIN product AS product ON product.productid = package.productid
WHERE product.active = 1
OR package.productid = 'vbulletin' );

Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=']=]=></error>
<error_html><=!=[=C=D=A=T=A=[<p>Database error in vBulletin 3.8.2</p>
<p>Invalid SQL:

(SELECT 'package' AS classtype, package.packageid AS typeid, package.packageid AS packageid,
package.productid AS productid, if(package.productid = 'vbulletin', 1, product.active) AS enabled,
package.class AS class, -1 as isaggregator
FROM package AS package
LEFT JOIN product AS product
ON product.productid = package.productid
WHERE product.active = 1
OR package.productid = 'vbulletin'
)

UNION

(SELECT 'contenttype' AS classtype, contenttypeid AS typeid, contenttype.packageid AS packageid,
1, 1, contenttype.class AS class , contenttype.isaggregator
FROM contenttype AS contenttype
INNER JOIN package AS package ON package.packageid = contenttype.packageid
LEFT JOIN product AS product ON product.productid = package.productid
WHERE product.active = 1
OR package.productid = 'vbulletin' );<p>
<p>
<strong>MySQL Error</strong> : Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='<br />
<strong>Error Number</strong> : 1267<br />
<strong>Request Date</strong> : Sunday, May 15th 2011 @ 07:05:24 AM<br />
<strong>Error Date</strong> : Sunday, May 15th 2011 @ 07:05:24 AM<br />
<strong>Script</strong> : http://xxx.com/4rum/install/upgrade.php<br />
<strong>Referrer</strong> : http://xxx.com/4rum/install/upgrade.php<br />
<strong>Classname</strong> : vB_Database<br />
<strong>MySQL Version</strong> : 5.0.67-community<br />
</p>]=]=></error_html>
</errors>

[Trevor Hannant]

You've changed the tables, yes - but have you changed the fields also?

You will need to use phpMyAdmin and make sure that all tables and fields within them have the same collation. Make sure your database collations are appropriate and consistent throughout the entire database.