Missing Invoice

[John Ralls]

On Dec 2, 2013, at 7:19 AM, Derek Atkins <warlord at MIT.EDU> wrote:

> 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.
> 
> 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.
> 

If there's no way to delete an invoice, then the SQL backend can't delete it either: The only way to make a SQL DELETE statement is to free an instance and mark it dirty inside of a BeginEdit/CommitEdit block for that instance, which would also result in its being deleted from the XML backend.

So far, though, we don't know that the invoice *was* deleted; it's possible that it just had its name changed.

To find the guid of the invoice, run
  select * from slots where obj_guid is "<transaction guid>" and name like "gncInvoice%";

The resulting row's name will be either gncInvoice or gncInvoice/invoice-guid. In the latter case the second guid in the row is the invoice; in the former case you need to run 
  select * from slots where obj_guid is "<result second guid>" and name like "%invoice-guid";
and that row's second guid will be the invoice. Run
  select * from invoices where guid is "<invoice guid>";
  select * from entries where invoice is "<invoice guid>";
to make absolutely sure that it's been deleted.

If it really has been deleted your best bet is to copy another invoice, changing only the guid to match the missing one and the name so you can find it easily. You need rows in both the invoices and entries tables. I suggest that you first "save as" your database to SQLite3 and test on that so that you don't risk any more damage to your MySQL database.

Regards,
John Ralls