Missing Invoice

[Roman]

Hi John,

John Ralls <jralls <at> ceridwen.us> writes:

> 
> 
> 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.
>
I don't understand your statement. I can access the database in MySQL
directly and delete any record. But I assume you are telling me I can't
delete it via a GnuCash interface.
 
> So far, though, we don't know that the invoice *was* deleted; it's
possible that it just had its name changed.
>
My database of invoices is not extensive. I can account for every one of
them in the MySQL database except this one item which is no longer there.
 
> 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.
>
I actually did a fair bit of research and came up with the same procedure.
However, the slots table only had one relevant record with 'name'='title'
and 'string_val' referencing the invoice. No references to the
 
> 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.
>
And I also came to the same conclusion. I copied another very similar
invoice, gave it an arbitrary guid (since there was no reference available
to the actual guid of the missing invoice) and added the record. Then I ran
GnuCash and found the reconstructed invoice. Now I could unpost it and
remove the offending transaction within GnuCash.

Although use of MySQL is a separate issue, I am curious why you would offer
it if you believe it is not safe to make use of it. The problem arose in the
past few days while I was editing within GnuCash. Has GnuCash run into other
issues associated with MySQL? I have never had a corruption problem with
MySQL data or its server engine.

Thank you for your helpful and very detailed suggestions.

Roman
 
> Regards,
> John Ralls
> 
> _______________________________________________
> gnucash-user mailing list
> gnucash-user <at> gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
> 
>