mysql error number: 1020

[SpankMe]

Recently upgraded MySQL/PHP from 4.1.1-alpha/4.3.2 to 4.1.5-gamma/5.0.2 and now users are occasionally getting:

Database error in vBulletin 3.0.0:
Invalid SQL: 
		SELECT *
		FROM session
		WHERE sessionhash = 'cda575f4ffd9e72fba515eff6713f08d'
		AND lastactivity > 1097600682
		AND host = '134.251.247.115'
		AND idhash = '5cd00773571849da36151c0974c0b63a'
mysql error: Record has changed since last read in table 'session'
mysql error number: 1020

When they refresh, the error disappears and the page loads ok.
I have tried doing repair/Optimize and clearing the session table with no joy. I also tried going back to 4.1.1-alpha, but get errors when trying to import the dB back but I’d rather move forward than backward.

I am running Solaris 9 and vBulletin 3.0.0

and kudos to the vBulletin team for their ability to show restraint. While searching the forums to see if others had posted this error I couldn’t believe the number of threads posted about the MySQL “connect failed” error. I would have started shouting abuse at these people for not searching first long ago.

[Steve Machol]

Honestly, I don't recommend running Gamma versions in a public environment and PHP 5 has not been recommend for vB yet. It's hard to say whether this is from MySQL or PHP though. Perhaps one of the more knowledge people around here will be able to tell.

[George L]

I'd just downgrade to PHP 4.3.9 as those versions are still in development and may not be fully compatible with current vB3.x version, I would also try updating to vB 3.0.3 first too

[jrx]

Yesterday I upgraded to MySQL 4.1.7 from 4.0.21, and after months of getting no error e-mails whatsoever, I just received an e-mail containing this exact same message:

Database error in vBulletin 3.0.0 Beta 7:

Invalid SQL: 
SELECT *
FROM session
WHERE sessionhash = '32e49834fd041bf6e2e7dfd5d8645e33'
AND lastactivity > 1098869530
AND host = '24.125.143.xxx'
AND idhash = '07a90e97d7711ff25b6a95d5a937e19b'

mysql error: Record has changed since last read in table 'session'

mysql error number: 1020

PHP version is 4.3.9; I've never used 5.x.

[Steve Machol]

The first thing you should do is upgrade to vB 3.0.3. We do not provide support for beta versions.

[jrx]

The first thing you should do is upgrade to vB 3.0.3. We do not provide support for beta versions.

Yeah, I understand, but it's easier said than done due to the style changes...

Looks like some sort of obscure MySQL bug, though, doesn't it? The query it's complaining about appears to be perfectly valid, and executes fine if I run it manually.

I guess I may just go back to MySQL 4.0.21 if this gets to be a problem...


Update: Just noticed that a vBulletin 3.0.3 user is getting the same error on the same query.

[SpankMe]

I went back to 4.1.2-alpha and the problem is gone. I need mysql 4.1 because I need to use sub-queries.

I found another problem with going to 4.1.5-gamma. When I do an export/import, any attachments made before the upgrade get corrupted. I had to change storage type to filesystem to fix this.

[Ralf vd Enden]

I've upgraded a few days ago to MySQL 4.1.7 and I've also received several emails with the 'Record has changed since..' message. I'm running PHP 4.3.9 and using VBulletin 3.0.3. It always concerns the vb3_session table.

[ZippySLC]

Why not find out why it's not working with new version of mySQL and fix the problem, instead of just telling people to downgrade their mySQL version?

Would be a great fix, especially for those who don't control what versions get loaded on by their host.

[conqsoft]

Why not find out why it's not working with new version of mySQL and fix the problem, instead of just telling people to downgrade their mySQL version?

Would be a great fix, especially for those who don't control what versions get loaded on by their host.

If your host is using Alpha or Gamma versions of mySQL on their production servers, you might want to look for another host...

[Ralf vd Enden]

4.1.7 is now the production version of MySQL, so it's no longer gamma.

I'm trying to figure out how to fix this. Will know more tomorrow when I've had some users login and check my forum.

[ZippySLC]

If your host is using Alpha or Gamma versions of mySQL on their production servers, you might want to look for another host...

My host is using 4.1.7 and STILL have the same problems as I did with the Gamma version.

And come on - Gamma mySQL is really NOT unstable.

[Steve Machol]

I believe Scott is installing 4.1.7 on his development server to test things out.

[Ralf vd Enden]

It appears adding the following before each SELECT query that accesses TABLE_PREFIX + 'session' did the trick although I'm not 100% sure yet.
The file to change is includes/sessions.php

$DB_site->query_unbuffered("FLUSH TABLE " . TABLE_PREFIX . "session");

[Ralf vd Enden]

The errors no longer occur on my box. If anyone needs my sessions.php lemme know. I'll attach it.