[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: projects lost after importing 1.2.21 backups in 1.3.23
- Subject: Re: projects lost after importing 1.2.21 backups in 1.3.23
- From: ario <..hidden..>
- Date: Fri, 07 Dec 2012 21:57:19 +0000
On Fri, 2012-12-07 at 18:22 +0000, ario wrote:
> On Fri, 2012-12-07 at 03:34 -0800, Chris Travers wrote:
> >
> >
> > On Fri, Dec 7, 2012 at 2:07 AM, ario <..hidden..>
> > wrote:
> > Tried, I'll quote the result:
> >
> > <quote>
> > ERROR: relation "lsmb12.projects" does not exist
> > LINE 1: ..., parts_id, production, completed, credit_id FROM
> > lsmb12.pro...
> > ^
> > </quote>
> >
> > ario
> >
> >
> > On Fri, 2012-12-07 at 00:04 -0800, Chris Travers wrote:
> > >
> > >
> >
> > > On Thu, Dec 6, 2012 at 11:43 PM, ario
> > <..hidden..>
> > > wrote:
> > > Still trying to get things working.
> > > Re-installed brand new debian 6.0.6 and ledgersmb
> > 1.3.23.
> > > Imported the backup of a 1.2.21 database with loads
> > of
> > > projects.
> > > After that, only 2 projects show up.
> > > Strange number in System|Defaults|Job/Project
> > Number: 11
> > > Both databases (within the same cluster) give this
> > same
> > > number, although
> > > both have different number of projects >> 11.
> > >
> > > What's going on, and how can I get my projects back
> > in 1.3.23?
> > >
> > >
> > > Hi;
> > >
> > >
> > > I am trying to track this down. Can you try this for me:
> > >
> > >
> > > INSERT INTO project
> > > (id, projectnumber, description, startdate,
> > > enddate, parts_id, production, completed, credit_id)
> > > SELECT p.id, projectnumber, p.description, p.startdate,
> > p.enddate,
> > > parts_id, production, completed, credit_id
> > > FROM lsmb12.projects p
> > > JOIN lsmb12.customers c ON p.customer_id = c.id;
> >
> >
> >
> > Sorry, my bad, should be:
> >
> >
> > INSERT INTO project
> > (id, projectnumber, description, startdate, enddate, parts_id,
> > production, completed, credit_id)
> > SELECT p.id, projectnumber, p.description, p.startdate, p.enddate,
> > parts_id, production, completed, credit_id
> > FROM lsmb12.project p
> > JOIN lsmb12.customers c ON p.customer_id = c.id;
> >
> >
> > In other words, lsmb12.project instead of lsmb12.projects.
>
> And lsmb12.customer instead of lsmb12.customer, I realised after the
> same error appeared (no relationship exists) with respect to
> 'lsmb12.customers'.
>
> But still then, after removing the trailing 's' in both
> 'lsmb12.projects' and 'lsmb12.customers' it still gave an error:
>
> <quote>
> ERROR: duplicate key value violates unique constraint "project_pkey"
> DETAIL: Key (id)=(14395) already exists.
> dbname=#
> </quote>
>
> This (id)=(14395) seems to be the id of one of the (only) two projects
> that *did* make it into the list which I get from 'Projects|Reports|
> Search|Continue' (with all fields in the search window left blank) after
> running the upgrade function in 'Database Management Console' about
> three times in a row during the upgrade to 1.3.23. I found this out
> after executing:
>
> <quote>
> dbname=# SELECT * FROM project;
> id | projectnumber | description | startdate | enddate | parts_id |
> production | completed | credit_id
> 14395 | <projectnumber> | <empty> | 2011-04-29 | <empty> | <empty> |
> <empty> | 0 | 0 | 317
> 15799 | <projectnumber> | <descr> | 2011-11-26 | 2012-01-18 | <empty> |
> <empty> | 0 | 0 | 317
> (2 rows)
>
> dbname=#
> </quote>
>
> after consulting the postgresql 9.1 manual.
>
> Am I correct that we should try to apply your suggestion again, but not
> before we removed those two rows (as in 'TRUNCATE TABLE project;' ?).
>
>
> cheers,
> ario
What the heck, I just tried the following things:
<quote>
dbname=# TRUNCATE TABLE project;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "project_translation" references "project".
HINT: Truncate table "project_translation" at the same time, or use
TRUNCATE ... CASCADE.
dbname=# TRUNCATE TABLE project CASCADE;
NOTICE: truncate cascades to table "project_translation"
TRUNCATE TABLE
dbname=# INSERT INTO project (id, projectnumber, description, startdate,
enddate, parts_id, production, completed, credit_id) SELECT p.id,
projectnumber, p.description, p.startdate, p.enddate, parts_id,
production, completed, credit_id FROM lsmb12.project p JOIN
lsmb12.customer c ON p.customer_id = c.id;
INSERT 0 2
dbname=# SELECT * FROM project;
id | projectnumber | description | startdate | enddate | parts_id |
production | completed | credit_id
14395 | <projectnumber> | <empty> | 2011-04-29 | <empty> | | | 0 | 0
| 317
15799 | <projectnumber> | <descr> | 2011-11-26 | 2012-01-18 | | | 0 | 0
| 317
(2 rows)
dbname=#
</quote>
So we're back to where we were, only now without error when issuing your
suggested command.
Could it be that the 1.2's projects information has been lost due to
repetitive runs of setup.pl?
To test this hypothesis I will start all over with a virgin
lsmb1323/deb606 VM but will (try to) run setup.pl only once.
(30 minutes later:)
Ok, tried it, it doesn't work out, same 2 rows re-appear, not anyone more.
But if I list the original projects, I get 146 rows:
<quote>
Debian GNU?Linux 6.0 lsmb-squeeze tty1
lsmb-squeeze login: postgres
Password: ***
...
..hidden..;~$ psql dbname
dbname=# SELECT * FROM lsmb12.project;
(rows appear with numbers from 10195 to 19492.)
(146 rows)
dbname=#
</quote>
So the projects are still there, they're just not all properly imported (with 'not all' a slight misunderstatement :).
I had enough for now, will wait for further hints/instructions if possible.
cheers,
ario
> > Best Wishes,
> > Chris Travers
> >
> > >
> > >
> > > Let me know if this corrects your issue or if you get other
> > issues.
> > >
> > >
> > > Best Wishes,
> > > Chris Travers
> >
> > >
> > ------------------------------------------------------------------------------
> > > LogMeIn Rescue: Anywhere, Anytime Remote support for IT.
> > Free Trial
> > > Remotely access PCs and mobile devices and provide instant
> > support
> > > Improve your efficiency, and focus on delivering more
> > value-add services
> > > Discover what IT Professionals Know. Rescue delivers
> > > http://p.sf.net/sfu/logmein_12329d2d
> > > _______________________________________________
> > Ledger-smb-users mailing list
> > ..hidden..
> > https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
> >
> >
> >
> > ------------------------------------------------------------------------------
> > LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free
> > Trial
> > Remotely access PCs and mobile devices and provide instant
> > support
> > Improve your efficiency, and focus on delivering more
> > value-add services
> > Discover what IT Professionals Know. Rescue delivers
> > http://p.sf.net/sfu/logmein_12329d2d
> > _______________________________________________
> > Ledger-smb-users mailing list
> > ..hidden..
> > https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
> >
> >
> > ------------------------------------------------------------------------------
> > LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
> > Remotely access PCs and mobile devices and provide instant support
> > Improve your efficiency, and focus on delivering more value-add services
> > Discover what IT Professionals Know. Rescue delivers
> > http://p.sf.net/sfu/logmein_12329d2d
> > _______________________________________________ Ledger-smb-users mailing list ..hidden.. https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
>
>
>
> ------------------------------------------------------------------------------
> LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
> Remotely access PCs and mobile devices and provide instant support
> Improve your efficiency, and focus on delivering more value-add services
> Discover what IT Professionals Know. Rescue delivers
> http://p.sf.net/sfu/logmein_12329d2d
> _______________________________________________
> Ledger-smb-users mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users