|Summary:||akonadi postgresql backend runs incorrect alter table query|
|Product:||Akonadi||Reporter:||Vincent de Phily <moltonel>|
|Component:||general||Assignee:||kdepim bugs <kdepim-bugs>|
|Latest Commit:||Version Fixed In:||1.11.0|
Description Vincent de Phily 2013-12-02 12:42:17 UTC
Akonadi pg backend runs erroneous "alter table" queries at startup. I tries to convert a colum to text, but it is already of type text. The pg function convert_from() requires a bytea as first argument. Maybe it worked with older versions of postgres, which had an implicit text to bytea cast. Incidentally, it is strange that akonadi would need to run any "alter table" query on a freshly-created database. Reproducible: Always Steps to Reproduce: 1. start postgres 9.2 server 2. createdb akonadi 3. configure pg backend using akondiconsole 4. restart akonadi server 5. click "test..." in akonadiconsole Actual Results: Current Akonadi server error log found: DBUpdater: query error: "ERROR: function convert_from(text, unknown) does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts. (42883) QPSQL: Unable to create query" " " Query was: "ALTER TABLE CollectionTable ALTER remoteId TYPE text USING convert_from(remoteId,'utf8');" Target version was: 24 Mandatory: false DBUpdater: query error: "ERROR: current transaction is aborted, commands ignored until end of transaction block .... Failed to commit transaction for database update. Expected Results: No error. Using PG 9.2, akonadi 1.10.3, qtsql 4.8.5
Comment 2 Daniel Vrátil 2013-12-03 13:14:45 UTC
Please open Akonadi console, go to "DB Browser", select "schemaversiontable", click "Refresh" and change the value in the field to 24. Freshly created database is created with revision 23, and Akonadi then tries to upgrade it to 24, by migrating from BYTEA to TEXT, which fails, because the database has already been created with TEXT.
Comment 3 Daniel Vrátil 2013-12-03 13:19:20 UTC
The schema version mismatch between dbupdate.xml and akonadidb.xml has been fixed in 1.11.0.
Comment 4 Vincent de Phily 2013-12-03 15:11:05 UTC
Confirming that manually changing the version to 24 fixes the issue. Thanks for quick workaround and fix.