SOLVED Problem with posting and moderating posts w/ MySQL 5

[MomTo3Girls3Boys]

Anyone have any idea what might cause this error message:

---------------------------
Error getting users post stat

DEBUG MODE

SQL Error : 1054 Unknown column 'g.group_id' in 'on clause'

SELECT ug.user_id, g.group_id as g_id, u.user_posts, g.group_count, g.group_count_max, 16 as u_id FROM phpbb_groups g, phpbb_users u LEFT JOIN phpbb_user_group ug ON g.group_id=ug.group_id AND ug.user_id=16 WHERE u.user_id=16 AND g.group_single_user=0 AND g.group_count_enable=1 AND g.group_moderator<>16

Line : 356
File : modcp.php

---------------------------

I'm running 1.4.0 upgraded to the 2.1.20 phpbb version with the 103 security update. It was working fine on my home server, I uploaded it my dreamhost server and now I'm getting this error on posts and replies (but the posts and replies still show up) and when I try to delete posts (the posts don't get deleted). Any tips would be greatly appreciated!

I did change the domain name and root directory in the config panel to match the location of the board files on the dreamhost server. Is there something else I should have updated as well?

TIA for any help on sorting this out!

[Dragonsys]

when you uploaded the files, did you do the DB updates for 2.0.20 as well?

[MomTo3Girls3Boys]

I did an export of the database and imported it on the other server. I had done an update of the database on my home server before that.

[MomTo3Girls3Boys]

I doubled checked both versions of the database and they are identical. They is a group_id in the phpbb_groups table in both databases. I can run the mysql command in the phpMyAdmin on my home server and it runs fine, when I do it on dreamhost, I get that same error message. The only difference I can see between the two is that my home server is runing a much older version of mysql:

Home version: MySQL client version: 3.23.49
Dreamhost version: MySQL client version: 5.0.16

Could this be the root of my problem?

[MomTo3Girls3Boys]

I looked up changes in the syntax in the new MySQL 5 version from previous versions and a difference in the precedence of the join is causing this problem. The syntax needs to be changed to:

SELECT ug.user_id, g.group_id as g_id, u.user_posts, g.group_count, g.group_count_max FROM (phpbb_groups g, phpbb_users u) LEFT JOIN phpbb_user_group ug ON g.group_id=ug.group_id AND ug.user_id=2 WHERE u.user_id=2 AND g.group_single_user=0 AND g.group_count_enable=1 AND g.group_moderator<>2

Note addition of the parens between the FROM and the LEFT.

I don't have time right now to go figure out which files need to be modifed to fix this, but hope to get a fix done tonight or tomorrow.

Anyone else have this problem and need the file fixes? If so, I can post them once I make the changes.

[Dragonsys]

I doubled checked both versions of the database and they are identical. They is a group_id in the phpbb_groups table in both databases. I can run the mysql command in the phpMyAdmin on my home server and it runs fine, when I do it on dreamhost, I get that same error message. The only difference I can see between the two is that my home server is runing a much older version of mysql:

Home version: MySQL client version: 3.23.49
Dreamhost version: MySQL client version: 5.0.16

Could this be the root of my problem?

Probably is, there are a few issues with mySQL 5 and phpBB. It can work, but it can be a pain.

[MomTo3Girls3Boys]

Problem has been fixed!

Here's the changes for anyone else having problem with posting or moding posts with MySQL 5:

In file includes/functions_post.php:

FIND:

$sql = "SELECT ug.user_id, g.group_id as g_id, u.user_posts, g.group_count, g.group_count_max FROM " . GROUPS_TABLE . " g, ".USERS_TABLE." u


REPLACE WITH:

$sql = "SELECT ug.user_id, g.group_id as g_id, u.user_posts, g.group_count, g.group_count_max FROM (" . GROUPS_TABLE . " g, ".USERS_TABLE." u)


In file modcp.php:

FIND:

$sql = "SELECT ug.user_id, g.group_id as g_id, u.user_posts, g.group_count, g.group_count_max FROM " . GROUPS_TABLE . " g, ".USERS_TABLE." u


REPLACE WITH:

$sql = "SELECT ug.user_id, g.group_id as g_id, u.user_posts, g.group_count, g.group_count_max FROM (" . GROUPS_TABLE . " g, ".USERS_TABLE." u)

[Dragonsys]

thank you for sharing that! Is that the only change for mySQL 5, or does that just fix the modcp?

[SLY LS1]

I am running 1.3.2 and my hosting just upgraded to MySQL: 5.0.16

I was getting the same problem after I made a new post, after doing the fix below it now works perfect.

In file includes/functions_post.php:

FIND:

$sql = "SELECT ug.user_id, g.group_id as g_id, u.user_posts, g.group_count, g.group_count_max FROM " . GROUPS_TABLE . " g, ".USERS_TABLE." u


REPLACE WITH:

$sql = "SELECT ug.user_id, g.group_id as g_id, u.user_posts, g.group_count, g.group_count_max FROM (" . GROUPS_TABLE . " g, ".USERS_TABLE." u)

thanks

MomTo3Girls3Boys

[MomTo3Girls3Boys]

You welcome!

So far, these are the only changes I have found necessary to get it to work. If I find any other problems, I'll let you know!

If you find any other problems, please let me know.

[Dragonsys]

You welcome!

So far, these are the only changes I have found necessary to get it to work. If I find any other problems, I'll let you know!

If you find any other problems, please let me know.

it's a deal <img>

[SLY LS1]

Im also having a problem with the statistics page, not sure if it has anything to do with the MySQL 5 change

I get this on the statistics page

Warning: fopen(./modules/cache/templates/fisubice/content_values.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_values.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_statistical.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_statistical.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_bars.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_bars.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_bars.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_values.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_values.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_statistical.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_bars.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_bars.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/templates/fisubice/content_values.tpl.php): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 752

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 753

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 754

Warning: touch(): Utime failed: Operation not permitted in /var/virtual/web/w1487/html/portal/integramod/stats_mod/includes/template.php on line 756

Warning: fopen(./modules/cache/explain/e2.html): failed to open stream: Permission denied in /var/virtual/web/w1487/html/portal/integramod/statistics.php on line 237

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/statistics.php on line 238

Warning: fwrite(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/statistics.php on line 240

Warning: fclose(): supplied argument is not a valid stream resource in /var/virtual/web/w1487/html/portal/integramod/statistics.php on line 241

Page is here
http://www.vicstreetutes.com/portal/int ... istics.php

[Dragonsys]

CHMOD stats_mod/includes/template.php to 644, if that doesn't work, try making it 755 or 777

See if that helps.

[SLY LS1]

CHMOD stats_mod/includes/template.php to 644, if that doesn't work, try making it 755 or 777

See if that helps.

Hmmm tried them all but nothing changed.....

[Dragonsys]

check the CHMOD on your stats_mod/modules/cache directory, maybe the script is having problems writing the cache files.

[SLY LS1]

check the CHMOD on your stats_mod/modules/cache directory, maybe the script is having problems writing the cache files.

all solved

I posted it here

http://integramod.com/forum/viewtopic.php?p=7069#7069

but worked it out myself

cheers

[SLY LS1]

I found another 1 when new users register it gives a general error after clicking submit, you need to change

/profilcp/profilcp_register.php - about line 320

Code: Select all

$sql = "SELECT ug.user_id, g.group_id as g_id, g.group_name , u.user_posts, g.group_count FROM " . GROUPS_TABLE . " g, ".USERS_TABLE." u

to

Code: Select all

$sql = "SELECT ug.user_id, g.group_id as g_id, g.group_name , u.user_posts, g.group_count FROM (" . GROUPS_TABLE . " g, ".USERS_TABLE." u)

[Dragonsys]

/profilcp/profilcp_register.php

?? I don't have that file.

[Dragonsys]

Here is a full fix for the posting issue when using mySQL 5. There are a lot more edits then what's posted above (I think... <img> )
http://www.integramod.com/forum/viewtop ... =9041#9041

[SLY LS1]

You welcome!

So far, these are the only changes I have found necessary to get it to work. If I find any other problems, I'll let you know!

If you find any other problems, please let me know.

I have 1 here

http://integramod.com/forum/viewtopic.p ... ight=#9237