Scaffolded Blog i18n example. Post contents lost?

[Andrew Myers]

I've been trying to build a scaffold site based on the i18n blog
example in [1] but I seem to be missing something. I would like to
eventually have something that I can use as an online log for my
thesis work so 'blog' has been changed to 'thesis' (I think)
everywhere.

The problem I'm having is content in the posts are lost and replaced
with what looks suspiciously like a hash value of some kind. For
example, if I log in and got to the root site 'localhost:3000/thesis'
and add a new post with title 'foobarbaz' and content 'hello world' I
get a post with the right title but the content is
'\x68656c6c6f20776f726c64'.

I'm using postgresql because I would like to eventually host on Heroku
(I promise I haven't forgotten the wiki entry about that). If I
connect to the db with psql the content in the database is also wrong
but the log messages from the yesod devel server have the correct form
fields:

POST /thesis
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/
*;q=0.8
POST [("_nonce","S0qX0dfYcy"),("f2","foobarbaz"),("f3","hello world")]

All my code is on github here [2], I would be happy to post code
snippets but I'm not sure what would be helpful. I basically copied
the i18n example, split it into separate files, and then changed as
little as possible to make the project build. I'm using all the yesod
stuff off Hackage right now.

Thanks in advance, Yesod is awesome :)

[1] http://www.yesodweb.com/book/example-blog
[2] https://github.com/asmyers/amyers

[Aaron Hodgen]

Well, '\x68656c6c6f20776f726c64' is 'hello world' hex-escaped.

This is just a stab in the dark, but can you check the type of this column? If it is a binary type you might need to change your postgresql config:
http://www.postgresql.org/docs/9.1/static/datatype-binary.html

Regards,
Aaron

[Michael Snoyman]

This is a bug we've seen in the past, and thought was resolved. The bug resides either in postgresql-libpq, postgresql-simple, or persistent-postgresql. Can you let us know your: OS, version of Postgresql, and version of the libpq library?

Michael

[Andrew Myers]

@Michael
Here's my set up. I'm open if you have code or ideas you want me to
try out. Thanks for the help guys.

OS: Up to date Archlinux
ghc 7.4.1

postgresql-libpq == 0.7
persistent-postgresql == 0.8.1.2
persistent-postgresql == 0.0.4

➤ postgres --version
postgres (PostgreSQL) 9.1.2

@Aaron nice catch on the hex escaped hello world, I wouldn't have
caught that. Doesn't look like the table column is binary though,
it's type 'character varying'. All the locale stuff seems to be
correctly set to UTF8 as far as I can tell. At least, that's what's
set in /etc/postgres/data/postgres.conf and what is displayed via
'show all;' in psql.

amyers=# select table_name, column_name, data_type from
information_schema.columns where table_name = 'entry';
table_name | column_name | data_type
------------+-------------+-----------------------------
entry | content | character varying
entry | posted | timestamp without time zone
entry | title | character varying
entry | id | integer
(4 rows)

[Michael Snoyman]

I was also able to reproduce the issue at some point. Felipe and Leon:
I'm not nearly as familiar with the code base involved here as you two
are, do either of you have any ideas on how to resolve this?

Michael

[Felipe Almeida Lessa]

On Fri, Feb 24, 2012 at 8:34 AM, Michael Snoyman <mic...@snoyman.com> wrote:
> I was also able to reproduce the issue at some point. Felipe and Leon:
> I'm not nearly as familiar with the code base involved here as you two
> are, do either of you have any ideas on how to resolve this?

AFAIK, this issue should be solved unless your PostgreSQL server is
ill-configured, see [1]. So it could be an issue with
postgresql-simple or postgresql-libpq, but only Leon is qualified to
say that =).

[1] https://github.com/yesodweb/yesod/issues/243#issuecomment-3709375

--
Felipe.

[Andrew Myers]

Okay, When I get home this evening I'll try
ALTER ROLE "HerokuIssues" SET bytea_output TO 'escape';
from the comment you linked to.

I'll also check my libpq version again. I just realized I only
checked the version of the package for Haskell bindings to it.

Andrew

On Feb 24, 5:42 am, Felipe Almeida Lessa <felipe.le...@gmail.com>
wrote:

[Andrew Myers]

So I tried the bytea_output and no luck. I deleted the database and
let yesod recreate it on startup with the setting to 'escape' and
still get the hex escaped binary. I think my next step will be to
install the libraries Michael mentioned with debug stuff and see how
far I can get that way.

[Andrew Myers]

Does anyone have a working Heroku patch to the standard scaffold
site? The one I did that's now in the procfile doesn't work any more
and I'm having trouble reconciling the changes. My thinking was to
push it to Heroku and see what happened. If the problem didn't occur
then my local postgresql db is probably the cause, otherwise I keep
looking for a bug.

[Andrew Myers]

Looks likes libpq and postgres match they're both 9.1.2. 
This morning updating the Heroku code was a lot simpler than it was at 3:00 AM.  I'm updating my build VM (local clib etc is too new) and then I'll try that out.

On Sat, Feb 25, 2012 at 1:54 PM, Leon Smith <leon.p...@gmail.com> wrote:

Well, that is the bytea escaping for "hello world".    Last time this issue came up, the problem was that that the libpq library from PostgreSQL 8 was being used to interact with a PostgreSQL 9 database.   So what version of libpq are you using?

Leon

[Greg Weber]

On Sat, Feb 25, 2012 at 11:31 AM, Andrew Myers <asm...@gmail.com> wrote:
> Looks likes libpq and postgres match they're both 9.1.2.
> This morning updating the Heroku code was a lot simpler than it was at 3:00
> AM.  I'm updating my build VM (local clib etc is too new) and then I'll try
> that out.
>

Please help us keep the Heroku documentation up to date by sending a patch.

[Andrew Myers]

Thats for the help Leon, I really appreciate it.  I'm still learning my way around Postgres :)
I turned on logging locally and if I understand the logs correctly it looks like the insert command is already an escaped hex string.  Here's what I see when I post an entry:

LOG:  duration: 0.031 ms  statement: BEGIN
LOG:  duration: 0.297 ms  statement: INSERT INTO "entry"("title","posted","content") VALUES('logging test','2012-02-25 20:47:42.67409+00','\x74657374207769746820706f737467726573206c6f6767696e67') RETURNING id
LOG:  duration: 19.254 ms  statement: COMMIT
LOG:  duration: 0.030 ms  statement: BEGIN
LOG:  duration: 0.129 ms  statement: SELECT "title","posted","content" FROM "entry" WHERE id=14
LOG:  duration: 0.069 ms  statement: SELECT "id","entry","posted","user","name","text" FROM "comment" ORDER BY "posted"
LOG:  duration: 0.023 ms  statement: COMMIT
LOG:  duration: 0.014 ms  statement: BEGIN
LOG:  duration: 0.070 ms  statement: SELECT "email" FROM "user" WHERE id=1
LOG:  duration: 0.014 ms  statement: COMMIT

If I post a comment everything works correctly.  Maybe this is a clue to what's going on?

LOG:  duration: 0.049 ms  statement: BEGIN
LOG:  duration: 22.115 ms  statement: INSERT INTO "comment"("entry","posted","user","name","text") VALUES(14,'2012-02-25 20:49:23.579735+00',1,'adsfasdf','comment') RETURNING id
LOG:  duration: 15.845 ms  statement: COMMIT
LOG:  duration: 0.035 ms  statement: BEGIN
LOG:  duration: 0.187 ms  statement: SELECT "title","posted","content" FROM "entry" WHERE id=14
LOG:  duration: 0.342 ms  statement: SELECT "id","entry","posted","user","name","text" FROM "comment" ORDER BY "posted"
LOG:  duration: 0.019 ms  statement: COMMIT
LOG:  duration: 0.013 ms  statement: BEGIN
LOG:  duration: 0.104 ms  statement: SELECT "email" FROM "user" WHERE id=1
LOG:  duration: 0.012 ms  statement: COMMIT

This is something I should have posted before.  The models for Entry and comment look like this:
Entry
   title Text
   posted UTCTime
   content Html
   deriving
Comment
   entry EntryId
   posted UTCTime
   user UserId
   name Text
   text Textarea

so the only type of field that's not working is the Html one, all other text fields work correctly.

Thanks,
Andrew

On Sat, Feb 25, 2012 at 3:07 PM, Leon Smith <leon.p...@gmail.com> wrote:

Well,  my suggestion is to turn on statement logging in PostgreSQL,   by setting log_min_duration_statement = 0 in your postgresql.conf   (In debian and ubuntu this is located in /etc/postgresql/,   in Redhat and CentOS this is located in /var/lib/pgsql)   Then "service postgresql reload",  insert a story,  fetch a story,  and then look in the postgresql logs for what the SQL statements look like.

I'm suspicious that something is getting converted from say,  bytea to text,  which doesn't really do what you want:

psql=> select 'hello world'::text;
    text    
-------------
 hello world
(1 row)

psql=> select ('hello world'::text)::bytea;
          bytea          
--------------------------
 \x68656c6c6f20776f726c64
(1 row)

psql=> select (('hello world'::text)::bytea)::text;
           text          
--------------------------
 \x68656c6c6f20776f726c64
(1 row)

Best,
Leon

[Andrew Myers]

Okay, after my last email I switched the Entry type to use Textarea for the content instead of Html and it works fine.  This is fine for my use case and I'll just continue with that.  If it's something you want to investigate more I'm happy to help though.
Thanks,
Andrew

[Andrew Myers]

I will as soon as I get a chance to test it on Heroku :)

[Bryan Richter]

On Sat, Feb 25, 2012 at 12:52, Andrew Myers <asm...@gmail.com> wrote:
> Thats for the help Leon, I really appreciate it.  I'm still learning my way
> around Postgres :)
> I turned on logging locally and if I understand the logs correctly it looks
> like the insert command is already an escaped hex string.

I just reproduced this. I have an Html field that used to work fine
but now gets written to the database in hex:

2012-02-25 15:41:02 PST LOG: duration: 2.100 ms statement: INSERT
INTO "post"("content","family","author","created")
VALUES('\x42617a2e',1,1,'2012-02-25 23:41:02.04804+00') RETURNING id

This happened while in the midst of migrating to the latest
Yesod/Persistent libraries. Unfortunately I don't have a complete
history of what libraries changed (lesson for next migration: use a
brand new virthualenv), but the differences in my .cabal from the last
known-good configuration are:

- , yesod >= 0.9 && < 0.10
- , yesod-core >= 0.9.3 && < 0.10
- , yesod-form >= 0.3.3 && < 0.4
+ , yesod >= 0.10 && < 0.11
+ , yesod-core >= 0.10 && < 0.11
+ , yesod-form >= 0.4 && < 0.5

...

- , persistent >= 0.6.2 && < 0.7
- , persistent-postgresql >= 0.6 && < 0.7
+ , persistent >= 0.8 && < 0.9
+ , persistent-postgresql >= 0.8 && < 0.9

persistent and yesod were both freshly installed from github.

[Greg Weber]

Hi Leon,

Another user is seeing this issue. This time immediately after
upgrading persistent to the new version dependent on your driver. So
it seems that there is a solution lurking here that HDBC new about.

Thanks,
Greg Weber

[Leon Smith]

Yes, there are three questions I'd like answered about your original model,  which may or may not satisfy my curiousity:

1.  What's the type of the "content" column?   Is it a bytea?   You can find this out by connecting to your database using psql and typing at "\d entry" the prompt.

2.  What's the postgresql-simple type being used to serialize the column?  My guess is that it's (Binary ByteString).

3.  What's the postgresql-simple type being used to deserialize this column?   Is it (Binary ByteString), or something else?

Best,
Leon

[Felipe Almeida Lessa]

I guess Html's PersistField instance is wrong. It says that it's a
SqlString but then goes ahead and uses PersistByteString [1].
Thoughts?

[1] http://hackage.haskell.org/packages/archive/persistent/0.8.0/doc/html/src/Database-Persist-Store.html#line-249

--
Felipe.

[Leon Smith]

Oh I don't doubt it's a real, widespread issue.  I do doubt that this is a postgresql-simple/postgresql-libpq issue, and I currently believe it to be a persistent/persistent-postgresql issue.   After all, I use both bytea columns and text columns in my own code without issue.     And I don't know much about persistent.   

So what I'm trying to do is rule out my code,  and hopefully in the process make it obvious what needs to be fixed in persistent/persistent-postgresql.   I don't expect this to be a "deep" bug,  but rather some sort of simple mistake.   

 So I really do need the answer to three questions:

1.   What is the type of the database column used to store an HTML field?

2.   What is the Haskell type of the parameter used to serialize the HTML field?   (This almost certainly is (Database.PostgreSQL.Simple.Types.Binary ByteString))

3.   What is the Haskell type of the parameter used to deserialize the HTML field?   (I should be able to deduce this information from the answer to #1,  as retrieving a bytea column as a ByteString or retrieving a text column as a (Binary ByteString) is a run-time error,  but I'm curious nonetheless.) 

Best,
Leon

[Michael Snoyman]

Felipe is right: the issue is the mismatch between PersistByteString
and SqlString. I've corrected this (with a test case) in [1]. I
believe this wasn't an issue with HDBC since it didn't handle binary
data at all. Thanks Leon for the help in debugging this (and for
giving us a library that properly handles binary data).

Andrew: would it be possible to pull the code from Github and try
building with it?

Michael

[1] https://github.com/yesodweb/persistent/commit/2b16863171686dd680599d861ae37a84d0916820

[Bryan Richter]

This works for me. Thanks guys!

[Andrew Myers]

The anwer to #1 is 'character varying'

amyers=# \d entry
                                    Table "public.entry"
 Column  |            Type             |                     Modifiers                     
---------+-----------------------------+----------------------------------------------------
 id      | integer                     | not null default nextval('entry_id_seq'::regclass)
 title   | character varying           | not null
 posted  | timestamp without time zone | not null
 content | character varying           | not null


How would I go about finding the answers to #2 and #3?

Andrew

[Leon Smith]

Well, I'm pretty sure I know the answer to #2 and #3,   based on your answer to #1 and what you've posted from the postgresql log.    #2 is almost certainly a (Binary ByteString), because of the escaping from the INSERT statement,  and #3 is almost certainly *not* a (Binary ByteString),  which would lead to a run-time error.    So my curiosity is satisfied.

The short story is that the persistent declaration for HTML was wrong,  but it sounds like the mistake didn't matter before now.  Michael has posted a fix.

It would be nice to have better end-to-end typechecking in postgresql-simple that would catch these sorts of errors earlier.   Also, it sounds like persistent isn't as a robust an abstraction as one might want,  but c'est la vie. 

Best,
Leon

[Felipe Almeida Lessa]

On Sun, Feb 26, 2012 at 1:13 PM, Leon Smith <leon.p...@gmail.com> wrote:
> It would be nice to have better end-to-end typechecking in postgresql-simple
> that would catch these sorts of errors earlier.

What I would like is to have ByteString always be treated as Binary
ByteString by postgresql-simple. =)

Cheers,

--
Felipe.

[Andrew Myers]

Okay, Thanks for the help :)

[Andrew Myers]

oops, missed this email yesterday. I see Bryan confirmed it but I'll
pull it also and try it.

On Feb 26, 12:22 am, Michael Snoyman <mich...@snoyman.com> wrote:
> Felipe is right: the issue is the mismatch between PersistByteString
> and SqlString. I've corrected this (with a test case) in [1]. I
> believe this wasn't an issue with HDBC since it didn't handle binary
> data at all. Thanks Leon for the help in debugging this (and for
> giving us a library that properly handles binary data).
>
> Andrew: would it be possible to pull the code from Github and try
> building with it?
>
> Michael
>

> [1]https://github.com/yesodweb/persistent/commit/2b16863171686dd680599d8...

>
>
>
>
>
>
>
> On Sun, Feb 26, 2012 at 4:06 AM, Leon Smith <leon.p.sm...@gmail.com> wrote:
> > Oh I don't doubt it's a real, widespread issue.  I do doubt that this is a
> > postgresql-simple/postgresql-libpq issue, and I currently believe it to be a
> > persistent/persistent-postgresql issue.   After all, I use both bytea
> > columns and text columns in my own code without issue.     And I don't know
> > much about persistent.
>
> > So what I'm trying to do is rule out my code,  and hopefully in the process
> > make it obvious what needs to be fixed in
> > persistent/persistent-postgresql.   I don't expect this to be a "deep" bug,
> > but rather some sort of simple mistake.
>
> >  So I really do need the answer to three questions:
>
> > 1.   What is the type of the database column used to store an HTML field?
>
> > 2.   What is the Haskell type of the parameter used to serialize the HTML
> > field?   (This almost certainly is (Database.PostgreSQL.Simple.Types.Binary
> > ByteString))
>
> > 3.   What is the Haskell type of the parameter used to deserialize the HTML
> > field?   (I should be able to deduce this information from the answer to
> > #1,  as retrieving a bytea column as a ByteString or retrieving a text
> > column as a (Binary ByteString) is a run-time error,  but I'm curious
> > nonetheless.)
>
> > Best,
> > Leon
>

[Andrew Myers]

Also works for me :)

On Feb 26, 12:22 am, Michael Snoyman <mich...@snoyman.com> wrote:
> Felipe is right: the issue is the mismatch between PersistByteString
> and SqlString. I've corrected this (with a test case) in [1]. I
> believe this wasn't an issue with HDBC since it didn't handle binary
> data at all. Thanks Leon for the help in debugging this (and for
> giving us a library that properly handles binary data).
>
> Andrew: would it be possible to pull the code from Github and try
> building with it?
>
> Michael
>

> [1]https://github.com/yesodweb/persistent/commit/2b16863171686dd680599d8...

>
>
>
>
>
>
>
> On Sun, Feb 26, 2012 at 4:06 AM, Leon Smith <leon.p.sm...@gmail.com> wrote:
> > Oh I don't doubt it's a real, widespread issue.  I do doubt that this is a
> > postgresql-simple/postgresql-libpq issue, and I currently believe it to be a
> > persistent/persistent-postgresql issue.   After all, I use both bytea
> > columns and text columns in my own code without issue.     And I don't know
> > much about persistent.
>
> > So what I'm trying to do is rule out my code,  and hopefully in the process
> > make it obvious what needs to be fixed in
> > persistent/persistent-postgresql.   I don't expect this to be a "deep" bug,
> > but rather some sort of simple mistake.
>
> >  So I really do need the answer to three questions:
>
> > 1.   What is the type of the database column used to store an HTML field?
>
> > 2.   What is the Haskell type of the parameter used to serialize the HTML
> > field?   (This almost certainly is (Database.PostgreSQL.Simple.Types.Binary
> > ByteString))
>
> > 3.   What is the Haskell type of the parameter used to deserialize the HTML
> > field?   (I should be able to deduce this information from the answer to
> > #1,  as retrieving a bytea column as a ByteString or retrieving a text
> > column as a (Binary ByteString) is a run-time error,  but I'm curious
> > nonetheless.)
>
> > Best,
> > Leon
>

[Darrin Thompson]

Sorry I'm a bit late to the party.

So Persistent maps ByteString to varchar for Postgres? I noticed that for MongoDB Persistent maps ByteString to BSON string instead of a BSON binary. Disregard if I misunderstood the previous posts but from what I can tell we're all still working around this by escaping in our handlers and PersistentEntity instances.

I believe all the supported databases have proper binary support that we just aren't using. I don't store binaries anymore so I'm not affected, but I doubt we will want to release a Yesod 1.0 until persistent can handle binaries correctly. The correct solution is for Persistent Postgres is to create the column with whatever Postgres has for potentially long blobs. It might even be called blob. For MongoDB, ByteStrings need to be wrapped in MongoDB's BinaryObject or something I'm too lazy to look up at the moment when creating or updating documents.

I think we're suffering from databases arbitrarily truncating at nulls or other data they don't perceive as valid text.

--
Darrin

[Greg Weber]

I updated MongoDB to save bytestrings as binary.

[Felipe Almeida Lessa]

On Thu, Mar 1, 2012 at 7:10 PM, Darrin Thompson <darr...@gmail.com> wrote:
> So Persistent maps ByteString to varchar for Postgres?

Nope, it uses blobs. Html's definition was asking for SqlString,
though. That was the bug here.

Cheers,

--
Felipe.

[Bryan Richter]

On Thu, Mar 1, 2012 at 17:13, Felipe Almeida Lessa
<felipe...@gmail.com> wrote:
> On Thu, Mar 1, 2012 at 7:10 PM, Darrin Thompson <darr...@gmail.com> wrote:
>> So Persistent maps ByteString to varchar for Postgres?
>
> Nope, it uses blobs.

Just so I'm understanding correctly, is it true that you can store a
blob in a column of type varchar? Or is Html being converted to
something other than ByteString before heading out to the database?
Because my Html field is, in fact, stored in a varchar column with the
latest Persistent.

[Darrin Thompson]

My experience with Postgres and MongoDB was that if the blob contained, for example, what appears to be ascii text with a null byte somewhere in the middle, the database will silently truncate the blob, probably at the null byte. So unless your blobs are pure ascii... :-)

--
Darrin

[Felipe Almeida Lessa]

On Thu, Mar 1, 2012 at 11:34 PM, Bryan Richter <bryan....@gmail.com> wrote:
> Just so I'm understanding correctly, is it true that you can store a
> blob in a column of type varchar? Or is Html being converted to
> something other than ByteString before heading out to the database?
> Because my Html field is, in fact, stored in a varchar column with the
> latest Persistent.

Html still uses SqlString and still uses VARCHAR. However, it doesn't
try to convert itself to ByteString anymore, and instead uses Text.
The other way of fixing this bug was to change the format of the
column, but that wouldn't be backwards compatible AFAICT.

Cheers,

--
Felipe.