CMS Made Simple Forums

Posted: **Sun Apr 27, 2008 4:04 pm**

Hi alby,

I installed everything new with the given Versions in your mini HowTo!
But after that, I still can't see any forums in the Frontend or in the Backend. Register with a new created user (in the Backend) was quite OK!
Looking in the log of Postgres I saw this message

Code: Select all

2008-04-27 17:59:21 [25447] ERROR:  invalid input syntax for integer: "f"
2008-04-27 18:00:12 [3911] ERROR:  invalid input syntax for integer: "f"
2008-04-27 18:00:25 [3750] ERROR:  invalid input syntax for integer: "f"
2008-04-27 18:00:30 [3911] ERROR:  column "f.name" must appear in the GROUP BY clause or be used in an aggregate function

Maybe something wrong with an SQL-Statement? How could I help to get more information?

Regards,
Zappodrom

Posted: **Sun Apr 27, 2008 4:06 pm**

I forgot to say, that there are also no FEUs visible in the Backend!
Maybe that helps searching for the error...

Posted: **Sun Apr 27, 2008 4:21 pm**

zappodrom wrote: Maybe something wrong with an SQL-Statement? How could I help to get more information?

Enable debug in config.php and look for complete sql query with error

Alby

Posted: **Sun Apr 27, 2008 6:51 pm**

Ok, this appears in the Postgres-Log with active Debug-Mode:

Code: Select all

2008-04-27 20:47:53 [16767] ERROR:  column "a.username" must appear in the GROUP BY clause or be used in an aggregate function
2008-04-27 20:47:53 [16767] ERROR:  column "a.username" must appear in the GROUP BY clause or be used in an aggregate function
2008-04-27 20:47:54 [16767] ERROR:  column "a.username" must appear in the GROUP BY clause or be used in an aggregate function
2008-04-27 20:47:54 [16767] ERROR:  LIMIT #,# syntax is not supported
HINT:  Use separate LIMIT and OFFSET clauses.

Not much more like above but maybe it helps. Is there any other chance to get more information?

Zappodrom

Posted: **Sun Apr 27, 2008 7:09 pm**

Ohh, sorry! I just saw the output at the bottom of the single Pages in the Back-End. *boing*

Here's the output on FEU I thought it's responsible for the problem:

Code: Select all

Debug: (6,357621) - (12446620)

<br>
(postgres7): SELECT A.*,count(D.userid) as loggedin FROM module_feusers_users A LEFT OUTER JOIN module_feusers_loggedin D
                  ON A.id = D.userid,module_feusers_belongs C WHERE  A.id = C.userid AND  groupid = '2' GROUP BY  A.id<br>
 Error (ERROR:  column "a.username" must appear in the GROUP BY clause or be used in an aggregate function): ERROR:  column "a.username" must appear in the GROUP BY clause or be used in an aggregate function<br>

And this one:

Code: Select all

Debug: (6,66833) - (12565916)

<br>
(postgres7): SELECT A.*,count(D.userid) as loggedin FROM module_feusers_users A LEFT OUTER JOIN module_feusers_loggedin D
                  ON A.id = D.userid GROUP BY  A.id ORDER BY username LIMIT 100<br>
 Error (ERROR:  column "a.username" must appear in the GROUP BY clause or be used in an aggregate function): ERROR:  column "a.username" must appear in the GROUP BY clause or be used in an aggregate function<br>

Probably this is also important:

Code: Select all

Debug: (7,116064) - (12660608)

<br>
(postgres7): SELECT A.*,B.username 
             FROM module_feusers_history A, 
                  module_feusers_users B
            WHERE A.userid = B.id ORDER BY refdate DESC LIMIT 0,10<br>
 Error (ERROR:  LIMIT #,# syntax is not supported
HINT:  Use separate LIMIT and OFFSET clauses.): ERROR:  LIMIT #,# syntax is not supported
HINT:  Use separate LIMIT and OFFSET clauses.<br>

I wonder why there is after every SQL-Query a visible . Maybe an error too? To know what I mean please take a look at the full output:
http://armadillo.ffis.de/~cw/cmsms-debug-feu.txt

Thanks!
Zappodrom

Posted: **Sun Apr 27, 2008 8:37 pm**

zappodrom wrote: Ohh, sorry! I just saw the output at the bottom of the single Pages in the Back-End. *boing*

Here's the output on FEU I thought it's responsible for the problem:

Code: Select all

Debug: (6,357621) - (12446620)

<br>
(postgres7): SELECT A.*,count(D.userid) as loggedin FROM module_feusers_users A LEFT OUTER JOIN module_feusers_loggedin D
                  ON A.id = D.userid,module_feusers_belongs C WHERE  A.id = C.userid AND  groupid = '2' GROUP BY  A.id<br>
 Error (ERROR:  column "a.username" must appear in the GROUP BY clause or be used in an aggregate function): ERROR:  column "a.username" must appear in the GROUP BY clause or be used in an aggregate function<br>

I don't have PostgreSQL, but try to edit FrontEndUsers.api.php (about #788) and substitute:

Code: Select all

    $q = "SELECT A.*,count(D.userid) as loggedin FROM ".cms_db_prefix()."module_feusers_users A"; 
		$group[] = " A.id";

with:

Code: Select all

    $q = "SELECT A.*,count(D.userid) as loggedin FROM ".cms_db_prefix()."module_feusers_users A"; 
		$group[] = " A.id, A.username, A.password, A.createdate, A.expires";

Alby

Posted: **Mon Apr 28, 2008 1:29 pm**

Ok, alby

that's an solution for the Userlist but here is the next error message. Maybe you know the correct statement:

Code: Select all

Debug: (3,140743) - (14053180)

<br>
(postgres7): SELECT A.*,B.username 
             FROM module_feusers_history A, 
                  module_feusers_users B
            WHERE A.userid = B.id ORDER BY refdate DESC LIMIT 0,10<br>
 Error (ERROR:  LIMIT #,# syntax is not supported
HINT:  Use separate LIMIT and OFFSET clauses.): ERROR:  LIMIT #,# syntax is not supported
HINT:  Use separate LIMIT and OFFSET clauses.<br>

Regards,
Zappodrom

Posted: **Mon Apr 28, 2008 2:04 pm**

zappodrom wrote: that's an solution for the Userlist but here is the next error message. Maybe you know the correct statement:

Edit action.userhistory.php and comment this row (#249):

//$query1 .= " LIMIT $startelement,$input_pagelimit";

go to #307 and substitute:

Code: Select all

$dbresult = $db->Execute( $query1, $parms );

with:

Code: Select all

$dbresult = $db->SelectLimit( $query1,$input_pagelimit,$startelement );

Edit function.admin_exportuserhistory.php (#97) and substitute:

Code: Select all

    $query = $qbase . " LIMIT $start,$num";
    $dbresult = $db->Execute( $query, $parms );

with:

Code: Select all

    $dbresult = $db->SelectLimit( $query,$num,$start );

Have you other sql errors?

Alby

Posted: **Mon Apr 28, 2008 7:10 pm**

alby wrote:
Have you other sql errors?

Alby

Yes, sorry

FEU seems to be ok, but FMS still makes Problems. Here's the error message:

Code: Select all

Debug: (2,242736) - (11849568)

<br>
(postgres7): SELECT f.*, COUNT(t.id) AS topics FROM module_forum_forums AS f
  LEFT JOIN module_forum_topics AS t ON t.forum_id = f.id
  GROUP BY f.id<br>
 Error (ERROR:  column "f.name" must appear in the GROUP BY clause or be used in an aggregate function): ERROR:  column "f.name" must appear in the GROUP BY clause or be used in an aggregate function<br>

The Postgresql-Server-Log print something like this:

Code: Select all

2008-04-28 21:07:29 [1397] ERROR:  invalid input syntax for integer: "f"

Maybe it's in conjunction with the above error message?

Regards
Zappodrom

Posted: **Mon Apr 28, 2008 7:16 pm**

zappodrom wrote:
alby wrote:
Have you other sql errors?

Alby
Yes, sorry
FEU seems to be ok, but FMS still makes Problems. Here's the error message:
Code: Select all
Debug: (2,242736) - (11849568)

<br>
(postgres7): SELECT f.*, COUNT(t.id) AS topics FROM module_forum_forums AS f
  LEFT JOIN module_forum_topics AS t ON t.forum_id = f.id
  GROUP BY f.id<br>
 Error (ERROR:  column "f.name" must appear in the GROUP BY clause or be used in an aggregate function): ERROR:  column "f.name" must appear in the GROUP BY clause or be used in an aggregate function<br>
The Postgresql-Server-Log print something like this:
Code: Select all
2008-04-28 21:07:29 [1397] ERROR:  invalid input syntax for integer: "f"
Maybe it's in conjunction with the above error message?

No, the problem is that PostgreSQL want ALL fields of table in GROUP BY.
The syntax should be:
SELECT f.*, COUNT(t.id) AS topics FROM module_forum_forums AS f
LEFT JOIN module_forum_topics AS t ON t.forum_id = f.id
GROUP BY f.id, f.name, f.description, f.position

For full support PostgreSQL we should revisit all queries with GROUP and LIMIT .... $:-\$

Alby

Posted: **Mon Apr 28, 2008 7:27 pm**

alby wrote:
No, the problem is that PostgreSQL want ALL fields of table in GROUP BY.
The syntax should be:
SELECT f.*, COUNT(t.id) AS topics FROM module_forum_forums AS f
LEFT JOIN module_forum_topics AS t ON t.forum_id = f.id
GROUP BY f.id, f.name, f.description, f.position

For full support PostgreSQL we should revisit all queries with GROUP and LIMIT .... $:-\$

Alby

Uhm, for the whole CMSMS or only for FMS?
How could I help?

Regards
zappodrom

Posted: **Mon Apr 28, 2008 7:33 pm**

zappodrom wrote:
alby wrote:
No, the problem is that PostgreSQL want ALL fields of table in GROUP BY.
The syntax should be:
SELECT f.*, COUNT(t.id) AS topics FROM module_forum_forums AS f
LEFT JOIN module_forum_topics AS t ON t.forum_id = f.id
GROUP BY f.id, f.name, f.description, f.position

For full support PostgreSQL we should revisit all queries with GROUP and LIMIT .... $:-\$

Alby
Uhm, for the whole CMSMS or only for FMS?
How could I help?

Yes, un BIG debug

After I post in tracker for core/modules

Alby

Posted: **Mon Apr 28, 2008 7:42 pm**

alby wrote:
Yes, un BIG debug

After I post in tracker for core/modules

Alby

Holla, that's much work, isn't it?
So, I need the forum in some time and it's a pleasure to me to give something back to CMSMS. Please tell me how / where could I help?
You can also mail me via [chw ed ffis dot de]

Regards
Zappodrom

Posted: **Mon Apr 28, 2008 8:06 pm**

zappodrom wrote:
alby wrote:
Yes, un BIG debug

After I post in tracker for core/modules

Alby
Holla, that's much work, isn't it?
So, I need the forum in some time and it's a pleasure to me to give something back to CMSMS. Please tell me how / where could I help?

Tomorrow I check FMS module and I say you which files you must edit after other modules, you must test this patchs and report

Alby

Posted: **Mon Apr 28, 2008 8:14 pm**

alby wrote:
Tomorrow I check FMS module and I say you which files you must edit after other modules, you must test this patchs and report

Alby

No problem.

Perhaps I'm on vacation for two days. So don't worry for a delay of my answers.

Btw, thanks for your help so far!

Regards
Zappodrom

CMS Made Simple Forums

Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development

Re: Forum module development