[F] SQL error in usergroups management in ACP [C-Michael83]

[vxl]

Hi folks,

I just installed a fresh MyBB 1.4.2 and included German language files, now, when in the ACP, selecting the usergroups management, I end up with an SQL error and a collapsing style - this happens on no other page except this.

Some data in hope that it could be of any help to you:

MyBB version: 1.4.2
PHP version: 5.2.6-3
SQL engine: PostgreSQL 8.3.3
Browser: Mozilla Firefox 3.0.1

The corresponding URL/parameter is:
myforumurl/admin/index.php?module=user/groups

This it what it looks like (please excuse the fact that I use German language):

- collapsed template (zoomed out a bit to show the collapsed template itself)
- SQL error message

In the second screenshot "ungültige Eingabesyntax für ganze Zahl" would probably mean "invalid syntax for integer value" (as far as I interprete it).

Any help would be very appreciated, thanks a lot!

Regards,
vxl

[Ryan Gordon]

looks like g.gid != '' is the issue. Should be intval'ed to show up like g.gid != '0'

[vxl]

Hello Ryan,

thanks for your answer!

I now edited the original file

/admin/modules/user/groups.php

on lines 1174 and 1182 and changed the code from

WHERE g.gid != '' GROUP BY g.gid

to

WHERE g.gid != '0' GROUP BY g.gid

Well, it just perfect now, thank you for the quick support!

---

Since it does not directly belong to the issue before, I open a second reply post:

Klicking on the user management in the ACP, I got another error:

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
22P02 - ERROR: invalid input value for integer: »«
Query:
SELECT * FROM mybb_adminviews WHERE type='user' AND (vid='' OR uid=0) ORDER BY uid DESC

Corresponding URL: /admin/index.php?module=user

This query appears on 3 different lines in the users.php in the admin/modules folder, namely lines 1321, 1586 and 1709:

$default_view = fetch_default_view("user");
$query = $db->simple_select("adminviews", "*", "type='user' AND (vid='{$default_view}' OR uid=0)", array("order_by" => "uid", "order_dir" => "desc"));
$admin_view = $db->fetch_array($query);

For some reason (actually I'm not that much into SQL or PHP in general), $default_view seems to remain empty or give anything which corrupts the query itself, resulting in the error above.
Although I don't think of it as a good solution, I worked around this by changing the query into this:

$query = $db->simple_select("adminviews", "*", "type='user' AND uid=0", array("order_by" => "uid", "order_dir" => "desc"));

Could you somehow comment on this, might it be a PostgreSQL syntax issue?


Apart from that, I am also experiencing another SQL error when trying to optimize the database:

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
42601 - ERROR: syntax error at »OPTIMIZE« LINE 1: OPTIMIZE TABLE mybb_adminlog ^
Query:
OPTIMIZE TABLE mybb_adminlog

Corresponding URL: /admin/index.php?module=tools/optimizedb
Actually, the error is the same, regardless which table I choose - only the table's name changes according to my choice.

And this when trying to create a new user account:

SQL Error:
42P01 - ERROR: Relation »mybb_userfields_ufid_seq« does not exist
Query:
SELECT currval('mybb_userfields_ufid_seq') AS last_value

URL: /admin/index.php?module=user/users&action=add

Unfortunately, I have no idea how to solve this... I'm rather getting the impression that something isn't right with the files, since other forums have absolutely no problems at all...

[Yumi]

I don't have PostgreSQL to test, but for the optimize table issue, can you try this?
Open inc/db_pgsql.php, find OPTIMIZE TABLE and replace it with VACUUM

Thanks!


Oh, and I can't find how you could produce your last error... :/

[Yumi]

Thank you for your bug report.

This bug has been fixed in our internal code repository. Please note that the problem will not be fixed here until these forums are updated.

With regards,
MyBB Group

[vxl]

Hi again,

thank you for your answer, I tried the replacement of "OPTIMIZE TABLE" with "VACUUM" and ended up with the error mentioned above, so this doesn't seem to make any change over here at all... :/

The last error occurs only if I manually want to add a new user account, it doesn't happen when a user registers himself using the registration form.

Best regards,
vxl

[Ryan Gordon]

vxl which postgreSQL version are you running? And is it under a particular mode?

[vxl]

Hi Ryan,

I'm running PostgreSQL 8.3.3 just as mentioned in my first post.
It's a Debian standard installation, no special modes or modifications.