Menu
▾
▴
sql-ledger-users
|
From: Jason K. <ja...@no...> - 2006-12-30 00:06:51
|
Hello. I am currently trying to fix some problems I am having with SL 2.4.11 on Postgresql 8.1.3 (migrated to another machine with 8.2.0 while I try and fix it): 1) When I try to print an invoice containing an assembly, I get the following error: DBD::Pg::st execute failed: ERROR: column a.oid does not exist LINE 8: ORDER BY a.oid ^ Error! SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup, p.partnumber AS sku FROM assembly a JOIN parts p ON (a.parts_id = p.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) WHERE a.bom = '1' AND a.id = '10151' ORDER BY a.oid ERROR: column a.oid does not exist LINE 8: ORDER BY a.oid ^ 2) When I create a new inventory item, duplicate inventory items are sometimes created (and when I add or remove stock of such, it is duplicated as well). 3) When I create a new invoice, (very) old inventory items and payments are sometimes shown on the new invoice along with what I have entered, after the invoice is posted. I have been reading the SQL-Ledger FAQ, the SQL-Ledger manual, the Postgresql FAQ and Online Documentation and Googling the internet in general in an attempt to determine how to fix my SL installation. I've especially been paying attention to any information regarding OID's. I have also been learning how to do basic querying in Postgresql. I have looked at the assembly table and it has the following columns: id | parts_id | qty | bom | adj Can I add the missing a.oid column to the table? If so, how do I populate it with the correct values? In the FAQ, it states: If you already built a dataset without this change (referring to creating the orderitems, inventory and assembly tables WITH OIDS) you will get an error when you try to save a PO or assembly. "ERROR: column "oid" does not exist" Drop the tables and rebuild WITH OIDS. I attempted this (even though I can create and save an assembly without a problem -- I just can't print an invoice that contains an assembly) by using the COPY TO command to send the contents of each table to file, dropping each table, creating each table as per the FAQ and then using the COPY FROM command to repopulate the tables. This did not help with my first problem. I will be testing to see if the second and third issues are still present or not. I believe this started (at least the assembly-related issues) when I attempted to upgrade to SL 2.6.19 a few months ago. I'm pretty sure I didn't lock the database prior to untarring the upgrade file (and fixing the file permissions). The database was upgraded and I ended up with an unusable mess! Since I did not have the time to play around, I restored a backup of the SQL-Ledger directory and restored a backup of the database that I had made just prior. Unfortunately, I don't recall changing the DROP SEQUENCE entries to SELECT SETVAL entries as per the FAQ. Since I don't use assemblies very often, I didn't notice anything wrong until recently. The second and third problems that I listed above didn't show up until much later. If anyone has any wisdom to share, I would be most appreciative!! Thanks! Jason Kay. |
|
From: Jason K. <ja...@no...> - 2006-12-30 18:47:37
|
> On Fri, 29 Dec 2006, Jason Kay wrote: > > > > I have been reading the SQL-Ledger FAQ, the SQL-Ledger manual, the > > I have no record that you ever purchased the manual from DWS. > I did. Invoice J041205 dated April 12, 2005. I did not realize that I had used a different e-mail address when I purchased it. Sorry for the confusion. Jason Kay. |
|
From: Dieter S. <dsi...@sq...> - 2006-12-30 19:15:39
|
found it.
You have to edit the backup and adjust the tables as per FAQ and add WITH
OIDS before you restore. You also have to add a "SELECT SETVAL('sequence',
sequencenumber);" after the "DROP SEQUENCE" lines and remove the "DROP
SEQUENCE" lines.
--
Dieter Simader http://www.sql-ledger.com Tel: (780) 472-8161
DWS Systems Inc. Accounting Software Fax: (780) 478-5281
============== On a clear disk you can seek forever ================
On Sat, 30 Dec 2006, Jason Kay wrote:
> > On Fri, 29 Dec 2006, Jason Kay wrote:
> > >
> > > I have been reading the SQL-Ledger FAQ, the SQL-Ledger manual, the
> >
> > I have no record that you ever purchased the manual from DWS.
> >
>
> I did. Invoice J041205 dated April 12, 2005. I did not realize that I
> had used a different e-mail address when I purchased it. Sorry for the
> confusion.
>
> Jason Kay.
|
|
From: Jason K. <ja...@no...> - 2007-01-01 23:02:58
|
> You have to edit the backup and adjust the tables as per FAQ and add
> WITH OIDS before you restore. You also have to add a "SELECT
> SETVAL('sequence', sequencenumber);" after the "DROP SEQUENCE" lines
> and remove the "DROP SEQUENCE" lines.
Thank you Dieter. I did this and it has fixed the problem I had with
printing assemblies. I made a new invoice just now, and when I ran a
report for outstanding invoices for the client, two invoices were
displayed, with the same invoice number, date, total, etc. When I
opened one of them, I noticed that an old inventory item and payment
were added to the invoice along with what I had entered.
When I was editing the backup file, I had no trouble adding WITH OIDS to
the correct locations, but when I was replacing the DROP SEQUENCE --
Pg-tables.sqlentries with SELECT SETVAL entries I only had a
'sequencenumber' for SEQUENCE id. The invoiceid and orderitemsid
SEQUENCES each did not have one:
***Snip from backup file:
...
DROP SEQUENCE id ;
DROP SEQUENCE invoiceid ;
DROP SEQUENCE orderitemsid ;
--
...
...
-- Pg-tables.sql
CREATE SEQUENCE id start 10000;
SELECT nextval ('id');
CREATE SEQUENCE invoiceid;
SELECT nextval ('invoiceid');
CREATE SEQUENCE orderitemsid MAXVALUE 100000 CYCLE;
SELECT nextval ('orderitemsid');
...
***End Snip from backup file.
I put the following just after the DROP SEQUENCE entries, then deleted
the DROP SEQUENCE entries:
SELECT SETVAL('id',10000);
SELECT SETVAL('invoiceid');
SELECT SETVAL('orderitemsid');
(So, the SELECT SETVAL entries are still above the '-- Pg-tables.sql'
line.) Is the lack of a 'sequencenumber' for the invoiceid and
orderitemsid SEQUENCES the cause of this problem? If so, how do I
determine what values to put in?
Jason Kay.
|
|
From: Dieter S. <dsi...@sq...> - 2007-01-03 03:04:36
|
Do not edit Pg-tables.sql, it has nothing to do with a backup. Edit the backup file only. -- Dieter Simader http://www.sql-ledger.com Tel: (780) 472-8161 DWS Systems Inc. Accounting Software Fax: (780) 478-5281 ============== On a clear disk you can seek forever ================ On Mon, 1 Jan 2007, Jason Kay wrote: > > You have to edit the backup and adjust the tables as per FAQ and add > > WITH OIDS before you restore. You also have to add a "SELECT > > SETVAL('sequence', sequencenumber);" after the "DROP SEQUENCE" lines > > and remove the "DROP SEQUENCE" lines. > > Thank you Dieter. I did this and it has fixed the problem I had with > printing assemblies. I made a new invoice just now, and when I ran a > report for outstanding invoices for the client, two invoices were > displayed, with the same invoice number, date, total, etc. When I > opened one of them, I noticed that an old inventory item and payment > were added to the invoice along with what I had entered. > > When I was editing the backup file, I had no trouble adding WITH OIDS to > the correct locations, but when I was replacing the DROP SEQUENCE -- > Pg-tables.sqlentries with SELECT SETVAL entries I only had a > 'sequencenumber' for SEQUENCE id. The invoiceid and orderitemsid > SEQUENCES each did not have one: > > ***Snip from backup file: > ... > DROP SEQUENCE id ; > DROP SEQUENCE invoiceid ; > DROP SEQUENCE orderitemsid ; > -- > ... > > ... > -- Pg-tables.sql > CREATE SEQUENCE id start 10000; > SELECT nextval ('id'); > CREATE SEQUENCE invoiceid; > SELECT nextval ('invoiceid'); > CREATE SEQUENCE orderitemsid MAXVALUE 100000 CYCLE; > SELECT nextval ('orderitemsid'); > ... > ***End Snip from backup file. > > I put the following just after the DROP SEQUENCE entries, then deleted > the DROP SEQUENCE entries: > > SELECT SETVAL('id',10000); > SELECT SETVAL('invoiceid'); > SELECT SETVAL('orderitemsid'); > > (So, the SELECT SETVAL entries are still above the '-- Pg-tables.sql' > line.) Is the lack of a 'sequencenumber' for the invoiceid and > orderitemsid SEQUENCES the cause of this problem? If so, how do I > determine what values to put in? > > > Jason Kay. > > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share your > opinions on IT & business topics through brief surveys - and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users > |
|
From: Jason K. <ja...@no...> - 2007-01-03 22:42:49
|
Okay, good. That's what I did. I'm going to look at some of my older backups (from before I messed everything up) to see if I can find some values for the invoiceid and orderitemsid sequences and see what that does. Thanks, Jason Kay. On 1/2/07, Dieter Simader <dsi...@sq...> wrote: > > Do not edit Pg-tables.sql, it has nothing to do with a backup. Edit the > backup file only. > > -- > Dieter Simader http://www.sql-ledger.com Tel: (780) 472-8161 > DWS Systems Inc. Accounting Software Fax: (780) 478-5281 > ============== On a clear disk you can seek forever ================ > |
|
From: Jason K. <jas...@gm...> - 2007-01-06 06:03:45
|
Well, that didn't help. There aren't any sequence number values for the invoiceid or orderitemsid in my older backups. I did find some values for them in a backup I had made using pg_dumpall quite a long time ago. I thought I would try putting them in my recent backup file and restoring it anyway, but I still get old payments and such showing up on new invoices. Anybody have any suggestions? Thanks, Jason Kay. ---------- Forwarded message ---------- From: Jason Kay <ja...@no...> Date: Wed, 3 Jan 2007 14:42:47 -0800 Subject: Re: [SL] Various Problems SL 2.4.11/PGSQL 8.1.3 To: sql...@li... Okay, good. That's what I did. I'm going to look at some of my older backups (from before I messed everything up) to see if I can find some values for the invoiceid and orderitemsid sequences and see what that does. Thanks, Jason Kay. |
|
From: Dieter S. <dsi...@sq...> - 2007-01-06 18:30:09
|
On Fri, 5 Jan 2007, Jason Kay wrote: > Well, that didn't help. There aren't any sequence number values for > the invoiceid or orderitemsid in my older backups. I did find some > values for them in a backup I had made using pg_dumpall quite a long > time ago. I thought I would try putting them in my recent backup file > and restoring it anyway, but I still get old payments and such showing > up on new invoices. Trust me, there is a value in the backup file. This is from a v2.4 backup search for 'DROP SEQUENCE' and you'll see the starting numbers. DROP SEQUENCE id ; CREATE SEQUENCE id START 17515; -- DROP SEQUENCE invoiceid ; CREATE SEQUENCE invoiceid START 17006; -- DROP SEQUENCE orderitemsid ; CREATE SEQUENCE orderitemsid START 475; -- Dieter Simader http://www.sql-ledger.com Tel: (780) 472-8161 DWS Systems Inc. Accounting Software Fax: (780) 478-5281 ============== On a clear disk you can seek forever ================ |
|
From: Jason K. <ja...@no...> - 2007-01-07 01:07:11
|
Yes, you are absolutely, 100% correct! Good old grep had no trouble at all finding it. Thank you! I edited the backup file _correctly_ this time and restored. I made a couple of invoices which had previously caused me grief and they were fine. The first new part I entered was fine. Every subsequent part I enter 'doubles itself' and sets an arbitrary value for stock for just one of them. Since I have been using an improperly restored SL since early October, I think I have created some very large problems! I compared the sequence numbers from previous backups and it looks like they should be continually increasing: Backup from Jan 29/06: id -> 11716 invoiceid -> 2056 orderitemsid -> 6 Backup from May 22/06: id -> 12078 invoiceid -> 2620 orderitemsid -> 10 (last backup before I wrecked things) Backup from Oct 7/06: id -> 12445 invoiceid -> 3141 orderitemsid -> 11 Backup from Dec 30/06: id -> 10151 invoiceid -> 207 orderitemsid -> 2 Uh-oh! I'll bet my improper restoration in October was using id, invoiceid and orderitemsid values of 10000, 0 and 0 (respectively). (Does that mean I've overwritten 360 other 'transactions'?) I can see one way to fix this (although I don't like the thought of it): restore the backup from October 7th and re-enter everything that was entered since then. Could I use the Oct 7th id, invoiceid and orderitemsid values to restore my Dec 30th backup, expecting to have no further trouble, and knowing that some damage has been done already that I'll fix as time permits? Thanks, Jason Kay. On 1/6/07, Dieter Simader <dsi...@sq...> wrote: > > Trust me, there is a value in the backup file. <snipped! > Dieter Simader |
|
From: Dieter S. <dsi...@sq...> - 2006-12-30 04:11:26
|
On Fri, 29 Dec 2006, Jason Kay wrote: > > I have been reading the SQL-Ledger FAQ, the SQL-Ledger manual, the I have no record that you ever purchased the manual from DWS. -- Dieter Simader http://www.sql-ledger.com Tel: (780) 472-8161 DWS Systems Inc. Accounting Software Fax: (780) 478-5281 ============== On a clear disk you can seek forever ================ |
|
From: Adrian B. <bla...@gm...> - 2006-12-30 05:04:53
|
Suggestion: Go to your backup of your SQL-Ledger data. copy. untar the copy if required. Grep for "orderitems" and locate "CREATE TABLE orderitems ( ....." as in the second item of the FAQ. Check it has the same format as in the FAQ and hand edit if necessary. Do the same for inventory and assembly. Also check your data that is the same as in the first item of the FAQ. Now restore using this modified data. It is important that you know how to backup, restore and drop a database in Postgres. It may be worth purchasing the O'Reilly book. A small price for valuable data. Adrian On 12/30/06, Jason Kay <ja...@no...> wrote: > Hello. I am currently trying to fix some problems I am having with SL > 2.4.11 on Postgresql 8.1.3 (migrated to another machine with 8.2.0 while > I try and fix it): > > 1) When I try to print an invoice containing an assembly, I get the > following error: > > DBD::Pg::st execute failed: ERROR: column a.oid does not exist > LINE 8: ORDER BY a.oid > ^ > Error! > SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup, > p.partnumber AS sku FROM assembly a JOIN parts p ON (a.parts_id = p.id) > LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) WHERE a.bom = '1' > AND a.id = '10151' ORDER BY a.oid ERROR: column a.oid does not exist > LINE 8: ORDER BY a.oid ^ > > 2) When I create a new inventory item, duplicate inventory items are > sometimes created (and when I add or remove stock of such, it is > duplicated as well). > > 3) When I create a new invoice, (very) old inventory items and payments > are sometimes shown on the new invoice along with what I have entered, > after the invoice is posted. > > I have been reading the SQL-Ledger FAQ, the SQL-Ledger manual, the > Postgresql FAQ and Online Documentation and Googling the internet in > general in an attempt to determine how to fix my SL installation. I've > especially been paying attention to any information regarding OID's. I > have also been learning how to do basic querying in Postgresql. > > I have looked at the assembly table and it has the following columns: > id | parts_id | qty | bom | adj > Can I add the missing a.oid column to the table? If so, how do I > populate it with the correct values? > > In the FAQ, it states: > > If you already built a dataset without this change (referring to > creating the orderitems, inventory and assembly tables WITH OIDS) you > will get an error when you try to save a PO or assembly. > > "ERROR: column "oid" does not exist" > > Drop the tables and rebuild WITH OIDS. > > I attempted this (even though I can create and save an assembly without > a problem -- I just can't print an invoice that contains an assembly) by > using the COPY TO command to send the contents of each table to file, > dropping each table, creating each table as per the FAQ and then using > the COPY FROM command to repopulate the tables. This did not help with > my first problem. I will be testing to see if the second and third > issues are still present or not. > > I believe this started (at least the assembly-related issues) when I > attempted to upgrade to SL 2.6.19 a few months ago. I'm pretty sure I > didn't lock the database prior to untarring the upgrade file (and fixing > the file permissions). The database was upgraded and I ended up with an > unusable mess! Since I did not have the time to play around, I restored > a backup of the SQL-Ledger directory and restored a backup of the > database that I had made just prior. Unfortunately, I don't recall > changing the DROP SEQUENCE entries to SELECT SETVAL entries as per the > FAQ. Since I don't use assemblies very often, I didn't notice anything > wrong until recently. The second and third problems that I listed above > didn't show up until much later. > > If anyone has any wisdom to share, I would be most appreciative!! Thanks! > > Jason Kay. > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share your > opinions on IT & business topics through brief surveys - and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users > |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X