[Forum] db error after update from 3.8.5 to 4.0.7

[jluerken]

Hi,

after updating the board from 3.8.5 to 4.0.7 I get the following db error when I try to read threads or create new forums

Database error in vBulletin 4.0.7:

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 vb305_package AS package
			LEFT JOIN vb305_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 vb305_contenttype AS contenttype
			INNER JOIN vb305_package AS package ON package.packageid = contenttype.packageid
			LEFT JOIN vb305_product AS product ON product.productid = package.productid
			WHERE product.active = 1
			OR package.productid = 'vbulletin'  );

MySQL Error   : Table 'db123.vb305_contenttype' doesn't exist
Error Number  : 1146
Request Date  : Tuesday, October 5th 2010 @ 02:00:37 PM
Error Date    : Tuesday, October 5th 2010 @ 02:00:37 PM
Script        : http://subdomain.tld.com/forum/admincp/forum.php?do=update
Referrer      : http://subdomain.tld.com/forum/admincp/forum.php?do=add
Classname     : vB_Database
MySQL Version : 5.1.49-1~bpo50+1-log

Repair/optimize tables did not work and if I check the db manually there is no table called
vb305_contenttype.

How can I fix this?

Kind regards
jluerken

[jluerken]

Ok there is a strange DB error with this database. I created a new one and restored the dump. No the table is there and it works.
What I see is that old tables have collation utf8-general_ci and newly created tables from the upgrade got latin1_german2_ci?
I think this is related to a server move in the past.
I want to fix this now and checked the new servers mysql config.
All new databases get latin1_german2_ci.

Can I change now all old tables which have ut8-general_ci to latin1_german2_ci without any problems and how?

[Wayne Luke]

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.