Admin ? Trouble

[kuyenmotdivad]

Hi

I noticed a problem in my admin panel I dont know if you allready know about it as a bug. But if I click "vBulletin Options > Show all Settings" Then click and of the "?" for help it puts this.

There seems to have been a problem with the Forums database.
                Please try again by clicking the Refresh button in your web browser.
             An E-Mail has been dispatched to our Technical Staff, whom you can also contact if the problem persists.
             We apologise for any inconvenience.

Database error in vBulletin 3.5.4:

Invalid SQL:

        SELECT *, LENGTH(action) AS length
        FROM bb_adminhelp
        WHERE script = 'options' AND
            (action = '' OR FIND_IN_SET('options', action))
            AND
            optionname = 'bbclosedreason' AND
            displayorder <> 0
        ORDER BY displayorder;

MySQL Error  : Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'find_in_set'
Error Number : 1267
Date         : Friday, April 21st 2006 @ 07:33:59 PM
Script       : http://dragonflyresources.net/forum/admincp/help.php?s=&do=answer&page=options&pageaction=options&option=bbclosedreason
Referrer     : http://dragonflyresources.net/forum/admincp/options.php?do=options&dogroup=%5Ball%5D
IP Address   : xx.xx.xx.xx
Username     : DaveTomneyUK
Classname    : vb_database 

[Steve Machol]

Which version of MySQL are you running? This was a bug in some earlier versions.

[kuyenmotdivad]

MySQL : 4.1.15-Debian_1-log (client: 5.0.20)
PHP : 4.4.2-1+b1

[Steve Machol]

Hmmm...that MySQL bug was supposed to have been fixed in 4.1.12. I don't know whaat elase would cause this other than a bug in MySQL. I'll ask the Devs if they have any ideas.

[Mike Sullivan]

Your database is using latin1, but your connection is using UTF-8.

Find this section in your config.php:

	//	****** MySQLI OPTIONS *****
	// When using MySQL 4.1+, MySQLi should be used to connect to the database.
	// If you need to set the default connection charset because your database
	// is using a charset other than latin1, you can set the charset here.
	// If you don't set the charset to be the same as your database, you
	// may receive collation errors.  Ignore this setting unless you are sure
	// you need to use it.
// $config['Mysqli']['charset'] = 'utf8';

You need to replace the last line with:

$config['Mysqli']['charset'] = 'latin1_swedish_ci';

(I believe that is correct -- if you get an error, try just 'latin1'.)

[kuyenmotdivad]

Still just the same error, How would I change my database from latin to utf8 general like my tables?

[Freddie Bingham]

You need to use:

$config['Mysqli']['charset'] = 'utf8';

Make sure that you remove the two slashes that are in front of that line by default.

[kuyenmotdivad]

Still the same but if I run it on my own PC it works fine I think there is something wrong with my hosts server and I reported it to them. As it wont let me change the collection for the database or the tables one is latin1_swedish_ci and the other is utf8_general_ci

[Freddie Bingham]

Have you changed

$config['Database']['dbtype'] = 'mysql';

to:

$config['Database']['dbtype'] = 'mysqli';

It isn't possible to receive the same error if you have.

Your tables needs to be using the same charset as your database, otherwise you will have problems in the future when vBulletin adds new tables. Submit a support ticket if you can get it figured out.

[kuyenmotdivad]

The database uses latin1_swedish_ci and the tables use utf8_general_ci but I have no idea how to change the table collations back to latin1_swedish_ci as I am based in the UK (england) thats why I changed the tables from latin1_swedish_ci to utf8_general_ci.

If I change it to $config['Database']['dbtype'] = 'mysqli'; I get a blank page.

[Freddie Bingham]

You can change the charset from within phpMyAdmin, which you should have on your hosting account.

[kuyenmotdivad]

Still wont work maybe because I dont know what I am doing, If I change the collation to general it still comes up with the same error.

[Freddie Bingham]

You have to change the charset back to latin1 and collation swedish_ci or you have to get the options in config.php working. If you want to get the options working and stay with UTF8 for your tables then submit a support ticket.

If you want to stay with UTF8 then in addition to getting the options in config.php working, you will also need to change your database's default charset to UTF8 as well so that all future tables are created as UTF and not Latin1.

[Sergio68]

You can change the charset from within phpMyAdmin, which you should have on your hosting account.

I have the same error on my forum, I just upgraded to 3.6 beta 2, admin panel :

Database error in vBulletin 3.6.0 Beta 2:

Invalid SQL:

	SELECT adminmessage.adminmessageid
	FROM adminmessage AS adminmessage
	INNER JOIN adminlog AS adminlog ON (adminlog.script = adminmessage.script AND adminlog.action = adminmessage.action)
	WHERE adminmessage.status = 'undone'
		AND adminmessage.script <> ''
		AND adminlog.dateline > adminmessage.dateline
	GROUP BY adminmessage.adminmessageid;

MySQL Error  : Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
Error Number : 1267
Date         : Sunday, June 18th 2006 @ 02:28:44 AM
Script       : http://www.vbulletin.it/forums/admincp/index.php?do=home
Referrer     : http://www.vbulletin.it/forums/admincp/index.php?do=nav
IP Address   : 81.174.32.193
Username     : Sergio
Classname    : vb_database

But both adminlog and adminmessage are set to latin1. Most of the rest is utf8.
Should I alter all tables (a lot) to latin1 ? I can do easily with mysqladministrator.
Any corruption risk?

[OmaniMan]

I got exactly same problem

http://www.vbulletin.com/forum/bugs36.php?do=view&bugid=367