Bug 328325 - akonadi postgresql backend runs incorrect alter table query
Summary: akonadi postgresql backend runs incorrect alter table query
Status: RESOLVED FIXED
Alias: None
Product: Akonadi
Classification: Frameworks and Libraries
Component: general (show other bugs)
Version: 1.10.3
Platform: Gentoo Packages Linux
: NOR normal
Target Milestone: ---
Assignee: kdepim bugs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-12-02 12:42 UTC by Vincent de Phily
Modified: 2013-12-03 15:11 UTC (History)
1 user (show)

See Also:
Latest Commit:
Version Fixed In: 1.11.0


Attachments
error log (3.71 KB, text/plain)
2013-12-02 13:01 UTC, Vincent de Phily
Details

Note You need to log in before you can comment on or make changes to this bug.
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 1 Vincent de Phily 2013-12-02 13:01:06 UTC
Created attachment 83877 [details]
error log
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.