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.
*** Bug 254926 has been marked as a duplicate of this bug. ***
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."
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.)"
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.
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.
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
setting as upstream (Qt) bug
no, the upstream bug appeared after this bug.
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