IntegraMOD

Home of phpBB Integrated Modifications
http://integramod.com/forum/

SOLVED Problem with posting and moderating posts w/ MySQL 5

http://integramod.com/forum/viewtopic.php?f=17&t=670

Page 1 of 1

SOLVED Problem with posting and moderating posts w/ MySQL 5

PostPosted: Wed May 10, 2006 9:26 am
Author: 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!

Re: DEBUG ERROR - Error geting users post stat

PostPosted: Wed May 10, 2006 9:28 am
Author: Dragonsys
when you uploaded the files, did you do the DB updates for 2.0.20 as well?

PostPosted: Wed May 10, 2006 9:56 am
Author: 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.

PostPosted: Wed May 10, 2006 10:44 am
Author: 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?

PostPosted: Wed May 10, 2006 11:32 am
Author: 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.

PostPosted: Wed May 10, 2006 12:53 pm
Author: Dragonsys
"MomTo3Girls3Boys";p="5376" wrote: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.

PostPosted: Wed May 10, 2006 4:35 pm
Author: 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)

Re: SOLVED Problem with posting and moderating posts w/ MySQ

PostPosted: Thu May 11, 2006 5:51 am
Author: Dragonsys
thank you for sharing that! Is that the only change for mySQL 5, or does that just fix the modcp?

Re: SOLVED Problem with posting and moderating posts w/ MySQ

PostPosted: Thu May 11, 2006 6:51 am
Author: 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

PostPosted: Thu May 11, 2006 7:48 am
Author: 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.

PostPosted: Thu May 11, 2006 8:13 am
Author: Dragonsys
"MomTo3Girls3Boys";p="5507" wrote: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>

Re: SOLVED Problem with posting and moderating posts w/ MySQ

PostPosted: Mon May 15, 2006 4:06 am
Author: 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

Re: SOLVED Problem with posting and moderating posts w/ MySQ

PostPosted: Mon May 15, 2006 4:20 am
Author: Dragonsys
CHMOD stats_mod/includes/template.php to 644, if that doesn't work, try making it 755 or 777

See if that helps.

Re: SOLVED Problem with posting and moderating posts w/ MySQ

PostPosted: Mon May 15, 2006 4:31 pm
Author: SLY LS1
"Dragonsys";p="5943" wrote: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.....

Re: SOLVED Problem with posting and moderating posts w/ MySQ

PostPosted: Wed May 17, 2006 4:57 am
Author: Dragonsys
check the CHMOD on your stats_mod/modules/cache directory, maybe the script is having problems writing the cache files.

Re: SOLVED Problem with posting and moderating posts w/ MySQ

PostPosted: Tue May 23, 2006 7:23 pm
Author: SLY LS1
"Dragonsys";p="6193" wrote: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

Re: SOLVED Problem with posting and moderating posts w/ MySQ

PostPosted: Tue May 23, 2006 8:38 pm
Author: 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)

Re: SOLVED Problem with posting and moderating posts w/ MySQ

PostPosted: Wed May 24, 2006 6:01 am
Author: Dragonsys
"SLY LS1";p="7076" wrote:/profilcp/profilcp_register.php


?? I don't have that file.

Re: SOLVED Problem with posting and moderating posts w/ MySQ

PostPosted: Fri Jun 09, 2006 4:09 am
Author: 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

PostPosted: Sun Jun 11, 2006 10:35 pm
Author: SLY LS1
"MomTo3Girls3Boys";p="5507" wrote: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
All times are UTC - 8 hours [ DST ]
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/