News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

SMF 2.0 RC2 on PostreSQL bugs

Started by MegaByte, November 29, 2009, 03:05:58 PM

Previous topic - Next topic

MegaByte

Okay, I managed to upgrade my installation using the changes provided by TheStupidOne.  Here are errors that I'm still getting:

---

I can't see "New replies to your posts."

http://www.nintendoworldreport.com/forums/index.php?action=unreadreplies
The database value you're trying to insert does not exist: sort
Function: UnreadTopics
File: /web/forums/Sources/Recent.php
Line: 1076

---

Search is failing.

http://www.nintendoworldreport.com/forums/index.php?action=search2
2: preg_replace() [<a href='function.preg-replace'>function.preg-replace</a>]: No ending delimiter '~' found
File: /web/forums/Sources/DbSearch-postgresql.php
Line: 89

---

Error in Who's Online

http://www.nintendoworldreport.com/forums/index.php?action=who
Database Error: ERROR: argument of AND must be type boolean, not type integer
LINE 5: WHERE (IFNULL(mem.show_online, 1) = 1) AND 1
File: /web/forums/Sources/Who.php
Line: 160

---

Guest theme index undefined.

http://www.nintendoworldreport.com/forums/index.php?
8: Undefined index: theme_guests
File: /web/forums/Sources/Load.php
Line: 1340

MegaByte

#1
I fixed the New replies bug with the following changes:

Recent.php line 1085
                                SELECT DISTINCT t.id_topic, ' . $_REQUEST['sort'] . '

Subs-Db-postgresql line 303
                        '~t.id_topic~' => 't.id_topic',

There might be a better cross-db solution, but that made it work for me on Postgresql. 

---

For search, there seems to be a mistake on line 68 of DbSearch-postgresql.php with a missing ~i

---

The Who's Online error is just another problem where SMF tries to use integers as boolean, which is not accepted by PostgreSQL.  Boolean casts fix the problem.

Norv

I cannot confirm any of these issues on a RC2 public new install on Postgresql 8.4.

Can you please tell, what version of Postgresql are you using? What package of SMF did you use to upgrade?
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

MegaByte

#3
PostgreSQL 8.4.1
SMF 2.0 RC2 Large Upgrade package extracted on top of SMF 2.0 RC1.

How did you even manage a new install?  Did you apply the patches from TheStupidOne?  And then how do you specify PostgreSQL as the database?

---

I reset the default theme settings and that seemed to get rid of the undefined theme index error.

Norv

I didn't apply any patch. Please note however, that I installed anew, you upgraded a previous installation, as I understand from your report.

Here's the simple story of it:
I ran install. It asked me from the beginning whether I want to use a MySQL or Postgres database, as it said there are multiple choices on my server. Indeed, I know my php had the right modules, no surprise there.
So, I chose Postgres. I chose UTF8, as well, and I unchecked the "compression" checkbox. (I always do, to be able to feed the output to a debugger if case may be). I entered database credentials.
I made sure I already have an empty database created in Postgres, with that name, accessible to that user. UTF8 database.
Then I pressed 'continue'.

Strangely enough, first time, I got an error (it complained that my Postgresql version does not know UTF8, which is false), but I hit back and after a while, ran install.php again, and this time it worked fully.

In any case, on this RC2 installation, all the scenarios reported in your original post do not apply.

It might be worth trying to re-upload all files from the upgrade package, even without re-running the upgrade script itself. It might be that you are getting some of these errors because of not-updated files. Worth checking, to be sure, IMHO.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

MegaByte

#5
Ah, I wasn't able to do a fresh install before, but now I see that's because the installer can't create a database.  Of course, it doesn't tell me this, it just says that it cannot connect with the given settings.  I was unable to complete a fresh install.  On the last step, I ended up with:

Notice: Undefined index: theme_guests in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1340

Notice: Undefined index: permissions in /web/webapps/ROOT/forums-test/Sources/Security.php on line 832

Warning: in_array() [function.in-array]: Wrong datatype for second argument in /web/webapps/ROOT/forums-test/Sources/Security.php on line 832

Notice: Undefined index: mod_cache in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1527

Notice: Undefined index: username in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1528

Notice: Undefined index: language in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1529

Notice: Undefined index: email in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1530

Notice: Undefined index: ignoreusers in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1531

Notice: Undefined index: name in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1534

Notice: Undefined index: smiley_set in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1539

Notice: Undefined index: smiley_sets_known in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1539

Notice: Undefined index: smiley_sets_default in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1539

Notice: Undefined index: session_var in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1547

Notice: Undefined index: session_value in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1548

Notice: Undefined index: htmlspecialchars in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1550

Fatal error: Function name must be a string in /web/webapps/ROOT/forums-test/Sources/Load.php on line 1550


and the trying to load the forums leads to:

The database value you're trying to insert does not exist: value

If it matters, I'm using
PHP 5.2.10-2ubuntu6.3 with Suhosin-Patch 0.9.7
php5-pgsql 5.2.10.dfsg.1-2ubuntu6.3
PostgreSQL 8.4.1-1
Apache 2.2.12-1ubuntu2.1


I checked the files of the fresh install and they still have the errors that I mentioned in the files, so at least some of the errors I saw should be present in a fresh install.

Norv

#6
Quote from: MegaByte on December 01, 2009, 12:29:04 AM
Ah, I wasn't able to do a fresh install before, but now I see that's because the installer can't create a database.  Of course, it doesn't tell me this, it just says that it cannot connect with the given settings. 
Yes, I fully agree it should say that it cannot create the database, if it cannot create the database. I'll keep this in mind.

So, on a fresh RC2 public installation, you get all those at the end?
Please tell, UTF-8 or not?

Quote from: MegaByte on December 01, 2009, 12:29:04 AM
and the trying to load the forums leads to:

The database value you're trying to insert does not exist: value
Is there anything else to this message, like where it's trying to insert something?
Eventually, since this is a local test forum, if I understand correctly, it would be very appreciated if you could make an edit useful to debug the problem, in Subs-Db-postgresql.php:
Code (find) Select

// Here's where the variables are injected to the query.
$insertRows = array();
foreach ($data as $dataRow)
$insertRows[] = smf_db_quote($insertData, array_combine($indexed_columns, $dataRow), $connection);

Code (replace) Select

// Here's where the variables are injected to the query.
echo "indexed_columns: <br>";
var_dump($indexed_columns);
echo "<br>";
$insertRows = array();
foreach ($data as $dataRow){
echo "dataRow: <br>";
var_dump($dataRow);
echo "<br>";
$insertRows[] = smf_db_quote($insertData, array_combine($indexed_columns, $dataRow), $connection);
}

Please don't use that in a production setup. :)

Quote from: MegaByte on December 01, 2009, 12:29:04 AM
If it matters, I'm using
PHP 5.2.10-2ubuntu6.3 with Suhosin-Patch 0.9.7
php5-pgsql 5.2.10.dfsg.1-2ubuntu6.3
PostgreSQL 8.4.1-1
Apache 2.2.12-1ubuntu2.1
Thank you, it might matter, any information at this point is useful.
Anything special in the settings of suhosin or postgres? Any log you could check to see if some operation was forbidden?

Quote from: MegaByte on December 01, 2009, 12:29:04 AM
I checked the files of the fresh install and they still have the errors that I mentioned in the files, so at least some of the errors I saw should be present in a fresh install.
I don't have, for example, an error in Who.php. Were you looking at index.php?action=who as an admin or moderator, or as a guest/member?
If I look at index.php?action=who as an admin, the relevant query being executed (Who.php line 160) is:

      SELECT COUNT(*)
      FROM smf_log_online AS lo
         LEFT JOIN smf_members AS mem ON (lo.id_member = mem.id_member)
      WHERE 1

As you can see, the condition you mention, that would perhaps make it fail because of boolean/integer incompatibility, is not there.
However, as I see in code, that condition seems to be added when the user might not have moderation rights. Yet, seeing index.php?action=who as a normal member still doesn't fail for me (I'm not sure what is the entire query being executed in this case, though, but I'll find out that too).
ETA: the query executed for a normal member/guest is:

SELECT COUNT(*)
FROM {db_prefix}log_online AS lo
LEFT JOIN {db_prefix}members AS mem ON (lo.id_member = mem.id_member)
WHERE (IFNULL(mem.show_online, 1) = 1)

(where {db_prefix} is to be replaced by your tables prefix).
As you can see, there is no "AND 1" here, that would make it fail...
Were you using some filter perhaps? Like, 'only members' or 'only guests'?

On yet another note, if possible, please tell if you have a table smf_settings, and how many records does it have. Does it have a variable "theme_guests"?
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

MegaByte

Quote from: Norv on December 01, 2009, 07:12:44 AM
So, on a fresh RC2 public installation, you get all those at the end?
Please tell, UTF-8 or not?
Yes, on a fresh install.  I wasn't able to specify UTF-8 because it claimed that I was running a version < 7.4 and wouldn't continue until I turned the option off.

Quote from: Norv on December 01, 2009, 07:12:44 AM
Is there anything else to this message, like where it's trying to insert something?
Eventually, since this is a local test forum, if I understand correctly, it would be very appreciated if you could make an edit useful to debug the problem, in Subs-Db-postgresql.php:
Code (find) Select

// Here's where the variables are injected to the query.
$insertRows = array();
foreach ($data as $dataRow)
$insertRows[] = smf_db_quote($insertData, array_combine($indexed_columns, $dataRow), $connection);

Code (replace) Select

// Here's where the variables are injected to the query.
echo "indexed_columns: <br>";
var_dump($indexed_columns);
echo "<br>";
$insertRows = array();
foreach ($data as $dataRow){
echo "dataRow: <br>";
var_dump($dataRow);
echo "<br>";
$insertRows[] = smf_db_quote($insertData, array_combine($indexed_columns, $dataRow), $connection);
}

Please don't use that in a production setup. :)
It was a completely black page except for that error.  Here's what the debug code output:
indexed_columns:
array(9) {
  • => string(9) "id_member" [1]=> string(8) "log_time" [2]=> string(2) "ip" [3]=> string(3) "url" [4]=> string(7) "message" [5]=> string(7) "session" [6]=> string(10) "error_type" [7]=> string(4) "file" [8]=> string(4) "line" }
    dataRow:
    array(9) {
  • => int(0) [1]=> int(1259712843) [2]=> string(0) "" [3]=> string(48) "?http://www.nintendoworldreport.com/forums-test/" [4]=> string(94) "The database value you're trying to insert does not exist: value
    Function: updateSettings" [5]=> string(0) "" [6]=> string(8) "critical" [7]=> string(46) "/web/webapps/ROOT/forums-test/Sources/Subs.php" [8]=> int(611) }
    Quote from: Norv on December 01, 2009, 07:12:44 AM
    Thank you, it might matter, any information at this point is useful.
    Anything special in the settings of suhosin or postgres? Any log you could check to see if some operation was forbidden?
    They're stock settings, and I don't see anything forbidden.

    Quote from: Norv on December 01, 2009, 07:12:44 AM
    I don't have, for example, an error in Who.php. Were you looking at index.php?action=who as an admin or moderator, or as a guest/member?
    If I look at index.php?action=who as an admin, the relevant query being executed (Who.php line 160) is:

          SELECT COUNT(*)
          FROM smf_log_online AS lo
             LEFT JOIN smf_members AS mem ON (lo.id_member = mem.id_member)
          WHERE 1

    As you can see, the condition you mention, that would perhaps make it fail because of boolean/integer incompatibility, is not there.
    Actually, it is there.  "1" is not considered boolean by PostgreSQL and makes the query fail.  I stuck a ::boolean cast at the end to fix it since PostgreSQL does know how to convert it, it just doesn't do it by default, at least on my install.

    Quote from: Norv on December 01, 2009, 07:12:44 AM
    On yet another note, if possible, please tell if you have a table smf_settings, and how many records does it have. Does it have a variable "theme_guests"?
    theme_guests does exist.  There are 167 settings on the new install.

Norv

#8
Quote from: MegaByte on December 01, 2009, 07:31:18 PM
Quote from: Norv on December 01, 2009, 07:12:44 AM
I don't have, for example, an error in Who.php. Were you looking at index.php?action=who as an admin or moderator, or as a guest/member?
If I look at index.php?action=who as an admin, the relevant query being executed (Who.php line 160) is:

      SELECT COUNT(*)
      FROM smf_log_online AS lo
         LEFT JOIN smf_members AS mem ON (lo.id_member = mem.id_member)
      WHERE 1

As you can see, the condition you mention, that would perhaps make it fail because of boolean/integer incompatibility, is not there.
Actually, it is there.  "1" is not considered boolean by PostgreSQL and makes the query fail.  I stuck a ::boolean cast at the end to fix it since PostgreSQL does know how to convert it, it just doesn't do it by default, at least on my install.

Not really. This "WHERE 1" works for me, while your query (in the OP) contains "WHERE (IFNULL(mem.show_online, 1) = 1) AND 1".
In theory, a database system could implement a failsafe "WHERE 1" (for compatibility with other systems) while not accepting other usages of integers as booleans. (I'm not saying this is the case).
Apparently, a query containing "WHERE 1" works for me on Postgresql 8.4.1 - Debian Squeeze.

ETA: not knowing Postgresql, I cannot tell at this point if it has some setting controlling whether to be nice to integers trying to pass as booleans, that could also explain why one statement works for me and another similar statement doesn't work for you.
What is obvious however, is that it's not the same query being executed: I am unable to replicate on my SMF the sending to Postgresql of the exact query you get an error at.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

Norv

To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

MegaByte

#10
There were a bunch of boolean cast bugs in the previous version, for example see http://www.simplemachines.org/community/index.php?topic=293059.0  I'm not sure why your install does the implicit cast by default and mine doesn't.  There is a way to change the setting, but since you can't guarantee users have this set, it would be better to just make the query compatible.  http://www.chrisspen.com/blog/enabling-implicit-cast-from-integer-to-boolean-in-postgresql.html  There were three places where I added a cast (basically everywhere $conditions was being referenced and I also did the IFNULL explicitly, but you could also probably rewrite the queries to say "(stuff) = 1" like is mentioned in bug 3118.

As for the modification you referenced, that was one of the things I had to do to make my upgrade installation work.

I forgot to mention that the big list of errors that I got with the new install, I also got with the upgrade install, but it still seemed to function once I had the modifications in place.

Norv

That the raw SQL code used for Postgresql should not assume that integers will be taken lightly as booleans is not under question. As said, what I would like to understand is why I get a different query being executed. I will come back on this, if I have any news.

With the modification in Subs-Db-Postgresql.php, you still get errors? Could you please tell which ones?
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

MegaByte

Given the obvious bugs in the code, the first three should still have problems, but not under every circumstance.  I still have the search error -- it doesn't show up when you do a search, just in the erorr log.  It's a very simple fix.  I'm still looking for the others.  It's kind of hard to test everything without data in there already, but I'm also getting two new errors on the new install:

http://www.nintendoworldreport.com/forums-test/index.php?action=admin;area=logs;sa=errorlog;desc8: Undefined index: latestRealNameFile: /web/webapps/ROOT/forums-test/Sources/Subs.php
Line: 3209
http://www.nintendoworldreport.com/forums-test/index.php?action=admin;area=logs;sa=errorlog;desc8: Undefined index: latestMemberFile: /web/webapps/ROOT/forums-test/Sources/Subs.php
Line: 3209



Norv

#13
I will look into them. It looks like some were solved already in the current svn. For new installation, can you please consider also the patch here: http://dev.simplemachines.org/mantis/view.php?id=4062, it might help.
One way or the other, the blocking issue of 'replace' error will hopefully be fixed for the next RC.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

MegaByte

The first bug I reported is still there in SMF 2.0 RC3.  The others appear to be fixed.

MegaByte

#15
Actually, the third one is also not fixed.  It doesn't always appear depending on $conditions

Norv

Noted, thank you! I'll keep these in mind until I can check too (currently I'm unable to) or document them.
I understand (please correct me if wrong) that the unread replies issue is happening simply when clicking on unread replies under Curve, and the Who issue is happening for some members / guests, and eventually not for others?
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

Oldiesmann

! Trying to view unread replies caused a database error in PostgreSQL (Recent.php, Subs-Db-postgresql.php) Revision 9819

To fix:

Sources/Recent.php

Find
Code (Line 1075) Select
'limit' => $context['topics_per_page'],

Add after that
'sort' => $_REQUEST['sort'],

Subs-Db-postgresql.php

Find
Code (Line 303) Select
'~t.id_topic~' => 't.id_topic, {raw:sort}',

Replace
'~SELECT\\s+DISTINCT\\s+t.id_topic~' => 'SELECT t.id_topic, {raw:sort}',

! Trying to view Who's Online with a parameter of "show=all" caused a database error in PostgreSQL (Who.php) Revision 9820

To fix that one:

Sources/Who.php

Find
Code (Line 90) Select
'all' => '1',

Replace
'all' => '1=1',

You've verified that the remaining bugs in this topic are no longer an issue, correct?
Michael Eshom
Christian Metal Fans

MegaByte

Yeah, I haven't seen them come up in any logs.

Oldiesmann

Ok. Going to mark this one as solved and will go take a look at the others you've posted :)
Michael Eshom
Christian Metal Fans

Advertisement: