Missing Invoice

[John Ralls]

On Dec 2, 2013, at 11:17 AM, Roman <romankal at verizon.net> wrote:

> Hi Derek,
> 
> Derek Atkins <warlord <at> MIT.EDU> writes:
> 
>> 
>> Hi,
>> 
>> Roman <romankal <at> verizon.net> writes:
>> 
>>> I run GnuCash 2.4.13 under Windows 7-x64 using MySQL as the database
>>> backend. I created an Invoice having an Invoice ID 'ID01', and posted it
>>> into an account (receivable). I now want to remove that posted entry and
>>> replace it with a different modified Invoice. In order to do so I must
>>> unpost the Invoice which will remove the entry.
>>> 
>>> However, on running the 'Find Invoice' script, I get a list of ALL my known
>>> invoices except 'ID01'. And on checking the MySQL database 'invoices' table
>>> I see that this invoice is not present! Thus, I cannot remove that entry.
>>> But the entry for this transaction (posting the invoice) can be found in the
>>> 'transactions' table. (By the way, that invoice did exist until recently.)
>>> 
>>> How should I proceed? Based on developer recommendations, I have made no
>>> modifications or deletions directly via MySQL. Furthermore, there apparently
>>> is no intent to provide Invoice removal options. Is it safe to remove the
>>> record in table 'transactions' referencing Invoice 'ID01'? Or can I add a
>>> record to the 'invoices' table with the proper guid? Where would I find that
>>> guid?
>>> 
>>> Is there a set of actions that I can perform on the MySQL backend to remove
>>> this unwanted invoice transaction for a missing invoice?
>> 
>> First, why are you using MySQL?  Most likely you hit a data loss bug
>> somewhere and you've now run into a bigger issue where you have
>> inconsistent data.  Alas, I don't know a good way to correct that.
>> GnuCash (rightfully!) stops you from deleting an Invoice Transaction,
>> but if you have no Invoice to unpost then the transaction is stuck.
>> 
> 
> I am using MySQL because I have the server running on my computer and
> because GnuCash has offered it as a back-end. Furthermore, I am more
> familiar with it than with XML. And I am more comfortable with Perl scripts
> that can generate reports from MySQL data than with Scheme programming or
> Lisp. (However I can somewhat understand your view since Scheme was
> developed at MIT.)
> 
>> Considering there is no way to delete an invoice, I'm curious how you
>> got into this state in the first place.  Like I said, most likely a data
>> corruption bug in the SQL backend -- which is why for YEARS we have been
>> telling people not to use SQL for real data.  Now you know why.
>> 
> 
> Thanks for the comments - however, I was hoping for a better response.
> Actually one can delete an Invoice if one is willing to work directly with
> the MySQL database. But great care must be taken in doing so. With this in
> mind, can you tell me whether ALL user data is stored in MySQL or whether
> there is another, perhaps hidden, resource. In other words, are the XML file
> and MySQL equivalent data bases?


There are stores of UI state, preferences, and some other general settings in ~/.gnucash and ~/.gconf. That has no data relevant to your invoice problem; everything that is stored is stored in the selected backend, the MySQL database in your case. When you run “file save as”, everything does get stored, even to a SQL backend. The problem is that in normal operation, the SQL backend stores every time “commit edit” is called, and it stores only the object on which commit edit is called and only the parts that are marked as having been changed (“dirty”), where the XML backend just uses the “dirty” flag to indicate that *something* needs saving, and saves everything in memory. For many years developers relied on that and were careless about marking things dirty or wrapping state changes in BeginEdit/CommitEdit. We’re still trying to track down all of those skipped spots.

That’s unlikely to explain how your invoice got deleted, if it did. I proposed some queries in my previous reply and I’m looking forward to hearing what you find out.

Regards,
John Ralls