Page 1 of 2
[Solved] 'No entries to print' problem (postgres 8.3)
Posted: Thu Feb 28, 2008 3:42 pm
by sasek
Hi
I've installed Serendipity on my server with apache, mod_rewrite an PostgreSQL. Everything work just fine, but when I click on the title of the entry I get 'No entries to print' message instead of the entry view. I tried switching mod_rewrite off, but it didn't help. How can I fix it?
SQL log entry looks like this:
Code: Select all
ERROR: operator does not exist: integer ~~ unknown at character 1559
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT: SELECT e.id,
e.title,
e.timestamp,
e.body,
e.comments,
e.trackbacks,
e.extended,
e.exflag,
e.authorid,
e.isdraft,
e.allow_comments,
e.last_modified,
e.moderate_comments,
a.realname AS author,
a.username AS loginname,
a.email
FROM
serendipity_entries e
LEFT JOIN serendipity_authors a
ON e.authorid = a.authorid
LEFT JOIN serendipity_entrycat ec
ON e.id = ec.entryid LEFT JOIN serendipity_category c
ON ec.categoryid = c.categoryid LEFT JOIN serendipity_authorgroups AS acl_a
ON acl_a.authorid = 2
LEFT JOIN serendipity_access AS acl_acc
ON ( acl_acc.artifact_mode = 'read'
AND acl_acc.artifact_type = 'category'
AND acl_acc.artifact_id = c.categoryid
)
WHERE
e.id LIKE '9'
AND e.isdraft = 'false' AND e.timestamp <= 1204208700 AND (
c.categoryid IS NULL
OR ( acl_acc.groupid = acl_a.groupid OR acl_acc.groupid = 0)
OR ( acl_acc.artifact_id IS NULL
)
)
LIMIT 1
Re: 'No entries to print' problem
Posted: Thu Feb 28, 2008 4:24 pm
by garvinhicking
Hi!
Hm, is this maybe an oddity of your PGSQL setup? It sounds as if it cannot execute one of the WHERE operators. But which one, can't PGSQL tell us that?
does it work if you replace in the query
with
if it does, I wonder why other people can use the same query on pgsql, I bet it's a config option of pgsql whether to enforce strict or unstrict type casting?
Regards,
Garvin
Posted: Thu Feb 28, 2008 4:30 pm
by judebert
That's an unhelpful error message. I don't see "integer", "~~", or "unknown" anywhere in that SQL statement.
When I massage the statement into a single line, character 1559 can be a blank, or the middle of "acl_acc.artifact_type", depending on what I do with newlines. That's the character right after the . on acl_acc.artifact_type.
Does serendipity_access exist in your database? Is it populated?
I'm just guessing until someone more knowledgeable arrives to help.
Posted: Thu Feb 28, 2008 4:31 pm
by judebert
And of course, by the time I do all that work, somebody more knowledgeable has answered. Stick with Garvin, disregard my post.
Posted: Thu Feb 28, 2008 5:06 pm
by sasek
Garvin: I think, that's the problem. How can I change type casting i pg? Can't find the option...
Posted: Fri Feb 29, 2008 10:03 am
by garvinhicking
Hi!
sasek wrote:Garvin: I think, that's the problem. How can I change type casting i pg? Can't find the option...
What do you mean? Did you try the SQL?
I don't know anything about pgsql.
Regards,
Garvin
Posted: Fri Feb 29, 2008 12:28 pm
by sasek
I did. It works with '=', but I have no idea how to change type casting method. There's nothing about it in pg's docs. Maybe changing the query might help. I'll try to figure it out.
I've found the same problem when searching entries via calendar.
Do you know, where this query is generated?
Posted: Fri Feb 29, 2008 3:09 pm
by garvinhicking
Hi!
Hm, there are more often parts in the s9y code where "LIKE 'integer'" is used. This works for all past pstgresql users. Maybe something changed in pgsql just recently so that it no longer works?
Using my 7.4 installation at home, the 'LIKE'-query doesn't make a problem, it'S the debian default pgsql installation from some time ago.
Regards
Garvin
Posted: Fri Feb 29, 2008 3:12 pm
by sasek
I'm using postgres 8.3 and it doesn't work
And I can't locate place, where the query is built... I'll stay in toutch.
Posted: Fri Feb 29, 2008 3:29 pm
by sasek
Done
You have to change 'LIKE' in line 521 of include/functions_entries.inc.php to '=' and it does work. Meanwhile i've done some tests on previous versions of postgres (default ubuntu package). The "LIKE int" have worked until ver 8.1. Thanks for your help
Posted: Fri Feb 29, 2008 3:43 pm
by garvinhicking
Hi!
Sigh. I bet there are more places in the s9y code where a LIKE syntax is used. Maybe you can find out how to re-enable the LIKE int syntax in PGSQL 8.1+? I wonder what the reasoning was to remove that behaviour, many cross-DB PHP applications will get problems witht that.
Regards,Garvin
Posted: Fri Feb 29, 2008 4:06 pm
by judebert
I found some online information about pgsql and type conversions.
First off, the error message makes much more sense if you know that "~~" is equivalent to "LIKE". So it's complaining that there is no operator "LIKE" that can be applied to integers.
The manual states that type conversion will occur automatically. It specifically covers numeric-to-string conversion for operators. I don't know why your installation isn't doing it.
At least one site claims LIKE can't be used on integers at all.
Here's how to cast the integer to a string in SQL.
Posted: Sat Mar 01, 2008 11:11 am
by griffinn
Just upgraded from PostgreSQL 8.2 to 8.3, using Debian defaults. I was burnt by this too.
From the
8.3 release notes:
Non-character data types are no longer automatically cast to TEXT
Arrgh. Anyway, the SQL-compliant way to keep the LIKE operator and also have typecasting happen would be to change:
to:
The CAST operator works in MySQL as far back as 3.23 and in PostgreSQL as far back as 7.4.
Posted: Sun Mar 02, 2008 12:16 pm
by garvinhicking
Hi!
Sadly CAST does not work in SQLite, I believe? Also, there are dozens of places where s9y uses LIKE uniquely, because it can either attach to TEXT or INT columns, so this would meen a massive codebase scan to find all occurences.
I'd rather appreciate that PGSQL would perform like it did before, which is compatible to MySQL and SQLite parsing
Why doesn't every other project pay close attention to backwards compatibility if needed, like we do?
Regards,
Garvin
Posted: Sun Mar 02, 2008 7:00 pm
by griffinn
garvinhicking wrote:Sadly CAST does not work in SQLite, I believe?
Not sure if it does anything useful in SQLite, but thankfully at least it is accepted, according to:
http://www.sqlite.org/lang_expr.html
Combing through the
PostgreSQL archives, it seems they have been mulling over whether to remove implicit casting to text for months (presumably out of concerns for backward compatibility), and then decided to "bite the bullet" and do it. They don't seem to have provided any easy means (e.g. a config value) to revert to the old behaviour.
An ugly hack using
CREATE CAST might coerce PostgreSQL 8.3 into doing implicit casts to text again, but it just looks too invasive.