Bug 252120

Summary: Akonadi fails to start with postgres backend. Possibly due to wrong escape '\\ANSWERED'
Product: [Frameworks and Libraries] Akonadi Reporter: Esben Mose Hansen <kde>
Component: generalAssignee: kdepim bugs <kdepim-bugs>
Status: RESOLVED FIXED    
Severity: crash CC: asturm, david, matlads, rdieter, vmikhailikov
Priority: NOR    
Version: 4.5   
Target Milestone: ---   
Platform: Unlisted Binaries   
OS: Linux   
Latest Commit: Version Fixed In: 1.10.3

Description Esben Mose Hansen 2010-09-23 12:05:54 UTC
Version:           4.5 (using KDE 4.5.1) 
OS:                Linux

Akonadi stopped working with postgres backend. I get this when I drop akonadi

Reproducible: Always

Steps to Reproduce:
just set up postgresql backend and run akonadictl start. If not the first time, drop the akonadi database first.

Actual Results:  
[akonadiserver] "INSERT INTO FlagTable (name) VALUES ('has_attachment')"
[akonadiserver] "INSERT INTO FlagTable (name) VALUES ('spam')"
[akonadiserver] "INSERT INTO FlagTable (name) VALUES ('\\ANSWERED')"
[akonadiserver] WARNING:  nonstandard use of \\ in a string literal
[akonadiserver] LINE 1: INSERT INTO FlagTable (name) VALUES ('\\ANSWERED')
[akonadiserver] ^
[akonadiserver] HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
[akonadiserver] "Unable to add initial data to table 'FlagTable'.
[akonadiserver] Query error: 'ERROR:  invalid input syntax for type bytea
[akonadiserver] LINE 1: INSERT INTO FlagTable (name) VALUES ('\\ANSWERED')
[akonadiserver] ^
[akonadiserver] QPSQL: Unable to create query'
[akonadiserver] Query was: INSERT INTO FlagTable (name) VALUES ('\\ANSWERED')"
[akonadiserver] Unable to initialize database.
[akonadiserver] "[
[akonadiserver] 0: akonadiserver(_Z11akBacktracev+0x39) [0x40b679]
[akonadiserver] 1: akonadiserver() [0x40bba2]
[akonadiserver] 2: /lib/libc.so.6(+0x321e0) [0x7fe616fd41e0]
[akonadiserver] 3: /lib/libc.so.6(gsignal+0x35) [0x7fe616fd4165]
[akonadiserver] 4: /lib/libc.so.6(abort+0x180) [0x7fe616fd6f70]
[akonadiserver] 5: /usr/lib/libQtCore.so.4(_Z17qt_message_output9QtMsgTypePKc+0x6f) [0x7fe6181b6c7f]
[akonadiserver] 6: akonadiserver(_ZN15FileDebugStream9writeDataEPKcx+0xa8) [0x40cc98]
[akonadiserver] 7: /usr/lib/libQtCore.so.4(_ZN9QIODevice5writeEPKcx+0x77) [0x7fe618245017]
[akonadiserver] 8: /usr/lib/libQtCore.so.4(+0x11d969) [0x7fe618252969]
[akonadiserver] 9: /usr/lib/libQtCore.so.4(_ZN11QTextStreamD1Ev+0x39) [0x7fe618253b69]
[akonadiserver] 10: /usr/lib/libakonadiprivate.so.1(_ZN7Akonadi13AkonadiServerC1EP7QObject+0x6c1) [0x7fe6186297c1]
[akonadiserver] 11: /usr/lib/libakonadiprivate.so.1(_ZN7Akonadi13AkonadiServer8instanceEv+0x4a) [0x7fe61862ab7a]
[akonadiserver] 12: akonadiserver(main+0x404) [0x4064e4]
[akonadiserver] 13: /lib/libc.so.6(__libc_start_main+0xfd) [0x7fe616fc0c4d]
[akonadiserver] 14: akonadiserver() [0x405fe9]
[

Later on it loops a few times trying to alter the database but failing since the columns are already there.

Expected Results:  
Well, starting would be nice.
Comment 1 Tobias Koenig 2010-11-07 20:24:11 UTC
*** Bug 254926 has been marked as a duplicate of this bug. ***
Comment 2 David Blewett 2010-12-01 04:59:06 UTC
This can be fixed by enabling the "standard_conforming_strings" option in your postgresql.conf file.

http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

"If the configuration parameter standard_conforming_strings is off, then PostgreSQL recognizes backslash escapes in both regular and escape string constants. This is for backward compatibility with the historical behavior, where backslash escapes were always recognized. Although standard_conforming_strings currently defaults to off, the default will change to on in a future release for improved standards compliance. Applications are therefore encouraged to migrate away from using backslash escapes. If you need to use a backslash escape to represent a special character, write the string constant with an E to be sure it will be handled the same way in future releases.

In addition to standard_conforming_strings, the configuration parameters escape_string_warning and backslash_quote govern treatment of backslashes in string constants."
Comment 3 David Blewett 2010-12-01 05:09:37 UTC
Unfortunately, you can't start Akonadi back up later on then however. It seems that Akonadi is not detecting the created tables correctly. PostgreSQL automatically folds unquoted table/column identifiers to lowercase. So, SchemaVersionTable is actually created as schemaversiontable. If you want the table to be created with mixed case you have to use "SchemaVersionTable" in the CREATE TABLE command.

http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

"Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)"
Comment 4 Christophe Giboudeaux 2011-03-09 10:55:56 UTC
Works as expected since ages.

Note that the external DB usage is not supported and supposes that the server is configured correctly before starting Akonadi.
Comment 5 Martin Atukunda 2013-07-17 04:07:18 UTC
Futher to what David said, I would to add that if 'standard_conforming_strings' doesn't fix it for you, then consider changing the type of the column from bytea to text e.g:

ALTER TABLE flagTable ALTER COLUMN name TYPE text;

This worked for me.
Comment 6 Christophe Giboudeaux 2013-07-17 04:52:47 UTC
Note to the Postgres backend users: Do not update Qt to 4.8.5. This release has a nasty regression that would prevent you from using Akonadi.

Read the comments there for the details:  https://bugreports.qt-project.org/browse/QTBUG-30076

If possible, delay your Qt update or try to downgrade to 4.8.4 if it's too late
Comment 7 Rex Dieter 2013-07-17 14:09:46 UTC
setting as upstream (Qt) bug
Comment 8 Christophe Giboudeaux 2013-07-17 15:58:18 UTC
no, the upstream bug appeared after this bug.
Comment 9 Daniel Vrátil 2013-10-04 12:18:42 UTC
Git commit 0a348cc98a458227bb28d89de584b61e31e1e9a8 by Dan Vrátil, on behalf of Cédric Villemain.
Committed on 04/10/2013 at 12:07.
Pushed by dvratil into branch '1.10'.

Map QString to text instead of Bytea with PostgreSQL backend

It's what suggested by QTSql.

It also happens to fix the recent issues raising around '\SEEN' errors
in SQL queries. It was visible because QTSql somewhat improve the postgresql
driver, and so... akonadi revealed its own bug.

REVIEW: 112976
Related: bug 322931
FIXED-IN: 1.10.3

M  +0    -7    server/src/storage/dbinitializer_p.cpp
M  +14   -0    server/src/storage/dbupdate.xml

http://commits.kde.org/akonadi/0a348cc98a458227bb28d89de584b61e31e1e9a8