Driver Error on local setup for test, only in forums

[wimg]

I've set up a copy of our large board (5.7 GB) on a local Windows 7 machine for testing purposes, with a Test Install license.

Everything appears to be working fine, but one: whenever I try to display a forum, or message in a forum, I get the dreaded "Driver Error" message, and returning to the index does work, fortunately.

Literally everything else does work fine, Members, Calendar, Downloads, Blogs, Tracker, Gallery, you name it, it works just fine. ACP also works just fine.

I have tried everything by now, tweaking my.ini, php.ini, htppd.conf, nothing, other than that occasionally the ACP stopped working as well, due to MySQL connection problems when I set buffers too big here and there.

Currently I am using Apache 2.2.24, MySQL 5.5.31, PHP 5.3.25 and IonCube.

I reckon that the large number of posts (2.6 million +) is causing this to happen, but I don't know what I could do to optimize the set-up in such a way that it works for Forums as well, short of clipping the last few million posts :)..

Thank you in advance for any help or tips.

Warm regards, Wim

[Mark H]

What's the SQL error from the log in your ./cache folder? That's first thing to find out.

Post the error here and I'm sure someone will have an idea or two. :)

[wimg]

Thanks Mark, that was great - never looked at the SQL error files before :smile:.

Here it is:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date: Thu, 13 Jun 2013 13:57:47 +0000
Error: 1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
IP Address: 127.0.0.1 - /forum/index.php?/forum/22-xxxxxxxxxx/
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mySQL query error: SELECT t.*,xxx.* FROM ibf_topics t LEFT JOIN ibf_core_tags_cache xxx ON ( xxx.tag_cache_key=MD5(CONCAT('forums',';','topics',';',t.tid)) ) WHERE t.forum_id=22 AND t.pinned IN (0,1) AND ( t.approved=1 ) ORDER BY t.pinned DESC, t.last_post DESC LIMIT 0,20

As a result I also found a fix. It looks like the MD5() function returns a utf8 character set, and after changing the characterset and collation of the ibf_core_tags_cache to utf8 and utf8-general-ci it now works.

I am wondering a little bit now as to why this works on the live server, where this table has a latin1 character set?

Is this maybe caused by the sql-mode directive on my local testbed?

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

The weird thing is that the local testbed for my other site, which is way smaller (about 80 MB currently) generates no problems whatsoever under the same conditions.

Anybody here has an idea as to why?

Warm regards, Wim

[wimg]

I found that there was a problem with the other site as well, after cleaning browser cache etc.

It also appeared that there was some interesting updating history in the database, occasionally with 3 different collation sequences in tables, and generally 4 different collation sequences used for tables, so I decided to convert everything to utf8 / utf8_general_ci, to make things consistent again.

The converter offered by Invision couldn't cope with it, so I decided to convert everything by hand eventually, which took me 3 days to do :smile:. In the end I was left with three blog posts which where still giving (driver) errors - looks like it had a different collation sequence to the rest from the start, so those I just deleted, and now everything is working just fine.

It appears that for some reason, what worked in 3.1.4 in this regard, or generally with 3.1.x, didn't anymore with 3.4.4. I would like therefore to give a warning to anyone planning to move up from 3.1.x: do make sure your character set and collation-sequence is the same throughout the entire database, and if possible, make sure it is utf8 and utf8_general_ci, with en eye to the future.

It'll save you from a lot of time wasted, now and later.

@Mark: thanks again for your reply, because that set me off in exactly the right direction!

Warm regards, Wim