Bug 328325

Summary: akonadi postgresql backend runs incorrect alter table query
Product: Akonadi Reporter: Vincent de Phily <moltonel>
Component: generalAssignee: kdepim bugs <kdepim-bugs>
Severity: normal CC: dvratil
Priority: NOR    
Version: 1.10.3   
Target Milestone: ---   
Platform: Gentoo Packages   
OS: Linux   
Latest Commit: Version Fixed In: 1.11.0
Attachments: error log

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.