Failure to save as postgresql database

Thomas Baumgart thb at net-bembel.de
Tue Jan 5 13:22:04 GMT 2021


On Dienstag, 5. Januar 2021 01:58:33 CET Jack wrote:

> On 2021.01.01 13:57, Jack wrote:
> > On 2020.12.31 19:02, Jack wrote:
> >> In looking at https://bugs.kde.org/show_bug.cgi?id=430163 I got both  
> >> mariadb and postgresql set up.  I can save to mariadb (mysql) but  
> >> not to postgresql.  The error I get is:
> >> 
> >> -----
> >> Cannot save to current database: Error in function void  
> >> MyMoneyStorageSqlPrivate::deleteKeyValuePairs(const QString&, const  
> >> QVariantList&) : deleting kvp for STORAGE
> >> Driver = QPSQL, Host = localhost, User = jack, Database = kmm
> >> Driver Error:
> >> Database Error No -1:
> >> Text:
> >> Error type 0
> >> Executed: DELETE FROM kmmKeyValuePairs WHERE kvpType = ? AND kvpId =  
> >> ?;
> >> Query error No 42601: ERROR:  syntax error at or near "("
> >> LINE 1: EXECUTE  ('STORAGE', '')
> >>                  ^
> >> (42601) QPSQL: Unable to create query
> >> Error type 2  
> >> /var/tmp/portage/app-office/kmymoney-5.1.9999/work/kmymoney-5.1.9999/kmymoney/plugins/sql/mymoneystoragesql_p.h:1927
> >> -----
> >> 
> >> In that call, kvpType is "STORAGE" and idList contains one empty  
> >> string.  I have no idea where the question marks in the "Executed"  
> >> line come from.  I see no obvious errors in the qSql code, other  
> >> than perhaps wondering if the "" is getting correctly bound to  
> >> :kvpID but I know I'm grabbing at straws here.
> >> 
> >> My other question is where LINE 1: EXECUTE('STORAGE','') comes  
> >> from.  Those would be the proper two values for the single execution  
> >> of the DELETE statement, but that seems to have come from much  
> >> deeper in the libraries, although I don't know if it's still within  
> >> QSql orin actual Postgresql code.
> >> 
> >> Any suggestions on how to further troubleshoot will be appreciated,  
> >> although I know my next effort will be to run under gdb.
> > With a hint from TonyB, I'm beginning to think there might be a  
> > problem within QSql and its Postgres innards.  The syntax error in  
> > "EXECUTE  ('STORAGE', '')" appears to be the lack of a name for the  
> > prepared statement.  I'll have to track down how it creates the  
> > actual psql PREPARE statement from the query.prepare call.
>
> I've used a debugger to trace the call to QSqlResult::execBatch to  
> qtsql translating it to exec() in the postgresql specific code.   
> Extracting just part of the preparation of that statement:  'stmt =  
> QStringLiteral("EXECUTE %1 (%2)").arg(d->preparedStmtId, params);'.  In  
> the call that fails, %1 is blank.  I traced the prepare code to about  
> that level, and saw a preparedStmtId of qpsqlpstmt_de.  (I thought I  
> had seen one with a number after the underscore, but am not certain.)   
> It seems that by the time it gets to the exec, that value has  
> disappeared.  I traced through an earlier call in the process, where  
> that value (qpsqlpstmt_f1) is still present for the exec.  I'd really  
> love any way to debug this better without spending hours stepping  
> through every nested line of code.  A part of the problem is that it is  
> difficult for me to get gdb to print out values of qt constructs in a  
> useful way.  I found a gdb function to print QString's, which has  
> helped a great deal, but any additional pointer oh better debuggin of  
> QT code would be appreciated.

The output for gdb is covered in https://invent.kde.org/office/kmymoney/-/issues/12

I scanned the Qt source code and noticed, that the numerical suffix to
qpsqlpstmt_ is to base 16 so hexadecimal. DE and F1 are perfect numbers then.

This happens in qMakePreparedStmtId(). Since the stmtId in the failing
case is empty, it must have been cleared or never set. The member variable
is set when a call to QPSQLResult::prepare() returns true. If it returns
false, the stmtId is cleared.

The other spot where d->preparedStmtId is cleared is in QPSQLResultPrivate::deallocatePreparedStmt()
which is called in the QPSQLResult::dtor or QPSQLResult::prepare() in case
you want to prepare a new command when one still exists.

So it seems to me, that exec is called in the wrong spot. To track this
down (After all F1 identifies 241 iterations) I would derive a new result
object from QPSQLResult or QSqlResult and override prepare() and exec() to keep track 
of the calls and their return code. Then use this object to add specific
debugging code/output.

I think I have done something similar for MySQL to track down a DB problem in
the past. Yes, it's in

kmymoney/kmymoney/plugins/sql/kmmsqlquery.[h,cpp]

and can be turned on using ENABLE_SQLTRACER. You may use it to add
a KMMSqlResult object. The #define at the end
of kmmsqlquery.h and the #undef at the beginning of kmmsqlquery.cpp
do the trick of using the identical (KMyMoney) source code and
switch the stuff with cmake or ccmake (ENABLE_SQLTRACER).

> Question:  In the process of trying to save to a postgres db,  
> deleteKeyvaluePairs() seems to be called for STORAGE three times.   
> Should I just assume this is not necessarily by design, but a side  
> effect of multiple calls to higher level routines when saving as to the  
> sql back end?  I'm not sure it matters - but an apparently identical  
> call to deleteKeyValuePairs where type is 'STORAGE' and id is '' works  
> twice, and then fails - apparently some value is getting deleted,  
> although I have no idea where.

See above to track it down. Hope that helps. 

-- 

Regards

Thomas Baumgart

https://www.signal.org/       Signal, the better WhatsApp
-------------------------------------------------------------
I don't think any of us need any help in this regard, getting
rid of money is probably the single most userfriendly interface
since breast-feeding... unfortunately. -- A.J. Venter
-------------------------------------------------------------
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 868 bytes
Desc: This is a digitally signed message part.
URL: <http://mail.kde.org/pipermail/kmymoney-devel/attachments/20210105/109325e3/attachment.sig>


More information about the KMyMoney-devel mailing list