30 Apr
2021
30 Apr
'21
6:37 p.m.
After I lost a hard drive on my server, I've been reconstructing from
backups, and decided this is a good time to finally migrate away from
1.3.25 to 1.8.11, which I normally wouldn't dream of right before needed
to do taxes, but needs must. After much trial and error, I've got the
server up and talking to Chrome through PSGI. I have gotten as far as
setup.pl, and tried migrating one of my company databases. The
migration crashed with:
"Failed to execute pre-migration check no_null_ac_amounts at
/usr/local/ledgersmb/tools/../lib/LedgerSMB/Scripts/setup.pm line 669."
"dbversion 1.5.30, company"
"Status: 500 Internal server error (PSGI.pm run_new)"
The starman log has a possibly relevant error : Failed to execute
bin/lsmb-request.pl (): Can't locate PatternLayout.pm in @INC (you may
need to install the PatternLayout module) (@INC contains:
/usr/local/ledgersmb/tools/../lib /usr/local/ledgersmb/tools/.. lib
/usr/lib/perl5/site_perl/5.26.0/x86_64-linux-thread-multi
/usr/lib/perl5/site_perl/5.26.0
/usr/lib/perl5/vendor_perl/5.26.0/x86_64-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.26.0
/usr/lib/perl5/5.26.0/x86_64-linux-thread-multi /usr/lib/perl5/5.26.0)
at /usr/lib/perl5/site_perl/5.26.0/Log/Log4perl/Util.pm line 51.
...propagated at /usr/local/ledgersmb/tools/../lib/LedgerSMB.pm
line 517.
cpan reports that PatternLayout.pm is up to date.
(/usr/lib/perl5/site_perl/5.26.0/Log/Log4perl/Layout/PatternLayout.pm)
PERL5LIB points to /usr/lib/perl5/site-perl/5.26.0/ Grasping at
straws, I added a path to /usr/local/ledgersmb/ with no change
I'm running Postgresql 13.2, Perl 5.26.0, and Apache 2.4.27 with reverse
proxy to starman running on port 5762. I have a "pg_dumpall" backup of
my databases, so I can experiment, but at the moment I'm stumped.
Victor
30 Apr
30 Apr
8:20 p.m.
Hi Victor!
Welcome to our list! I hope we can help you get past your problems quickly.
On Fri, Apr 30, 2021 at 9:34 PM Victor Wren <vwren(a)ponyhome.com> wrote:
After I lost a hard drive on my server, I've been
reconstructing from
backups,
Good that you've got far enough to be able to reconstruct the databases
themselves!
and decided this is a good time to finally migrate
away from
1.3.25 to 1.8.11, which I normally wouldn't dream of right before needed
to do taxes, but needs must. After much trial and error, I've got the
server up and talking to Chrome through PSGI.
That's a good basis to build on.
I have gotten as far as
setup.pl, and tried migrating one of my company databases. The
migration crashed with:
"Failed to execute pre-migration check no_null_ac_amounts at
/usr/local/ledgersmb/tools/../lib/LedgerSMB/Scripts/setup.pm line 669."
"dbversion 1.5.30, company"
"Status: 500 Internal server error (PSGI.pm run_new)"
That error points to this line:
https://github.com/ledgersmb/LedgerSMB/blob/1.8/lib/LedgerSMB/Upgrade_Tests…
which says:
'There are NULL values in the amounts column of your
source database. Please either find professional help to migrate your
database, or delete the offending rows through PgAdmin III or psql'),
What it proposes is that you check the data in the acc_trans table for NULL
values in the 'amount' column. A way to get rid of it (if it's not
referenced from anywhere) would be to run the query "DELETE FROM acc_trans
WHERE amount IS NULL". By "not being referenced from anywhere", I mean
that
the rows in acc_trans can themselves reference "invoice" rows or be
referenced by "payment_link" rows or "cr_report_line" rows (off the
top of
my head) and probably others. When those lines refer to these rows, either
the referencing rows are meaningless (likely this is the case for "invoice"
rows). Or they need to be replaced by a functional equivalent (this is
likely the case for "cr_report_line" rows). Links are supposed to be on the
"entry_id" field in the "acc_trans" table.
I'm puzzled that you receive this internal server error instead of the
message shown above. I've rewritten a large part of the upgrade logic for
1.8 with the exact purpose to solve problems like these I was seeing in
earlier upgrades.
The starman log has a possibly relevant error : Failed to execute
bin/lsmb-request.pl (): Can't locate
PatternLayout.pm in @INC (you may
need to install the PatternLayout module) (@INC contains:
/usr/local/ledgersmb/tools/../lib /usr/local/ledgersmb/tools/.. lib
/usr/lib/perl5/site_perl/5.26.0/x86_64-linux-thread-multi
/usr/lib/perl5/site_perl/5.26.0
/usr/lib/perl5/vendor_perl/5.26.0/x86_64-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.26.0
/usr/lib/perl5/5.26.0/x86_64-linux-thread-multi /usr/lib/perl5/5.26.0)
at /usr/lib/perl5/site_perl/5.26.0/Log/Log4perl/Util.pm line 51.
...propagated at /usr/local/ledgersmb/tools/../lib/LedgerSMB.pm
line 517.
cpan reports that PatternLayout.pm is up to date.
(/usr/lib/perl5/site_perl/5.26.0/Log/Log4perl/Layout/PatternLayout.pm)
What happens when you
$ perl -MLog::Log4perl::Layout::PatternLayout -E 'say "ok"'
?
It should say "ok", but I doubt it does.
However, I don't think it's all that important for the problem at hand.
PERL5LIB points to /usr/lib/perl5/site-perl/5.26.0/
Grasping at
straws, I added a path to /usr/local/ledgersmb/ with no change
I'm running Postgresql 13.2, Perl 5.26.0, and Apache 2.4.27 with reverse
proxy to starman running on port 5762. I have a "pg_dumpall" backup of
my databases, so I can experiment, but at the moment I'm stumped.
Hope the above helps a bit!
Regards,
--
Bye,
Erik.
http://efficito.com -- Hosted accounting and ERP.
Robust and Flexible. No vendor lock-in.
11:20 p.m.
Hi, and thanks for the informative reply!
What happens when you
$ perl -MLog::Log4perl::Layout::PatternLayout -E 'say "ok"'
Says "ok" so it (apparently?) doesn't think that PatternLayout.pm is
actually missing.
I forgot to mention that I WAS able to create a new "demo" business, so
that much is working, at least,
despite some creative launching (right now I'm manually launching
starman-ledgersmb until I get around to
writing a SYSV init script for LFS) It's a shame mod_perl didn't come
out for 2.4 sooner, but what can you do. Still,
good to dust off the cobwebs from my server building skills.
What it proposes is that you check the data in the
acc_trans table for
NULL values in the 'amount' column. A way to get rid of it (if it's
not referenced from anywhere) would be to run the query "DELETE FROM
acc_trans WHERE amount IS NULL". By "not being referenced from
anywhere", I mean that the rows in acc_trans can themselves reference
"invoice" rows or be referenced by "payment_link" rows or
"cr_report_line" rows (off the top of my head) and probably others.
When those lines refer to these rows, either the referencing rows are
meaningless (likely this is the case for "invoice" rows). Or they need
to be replaced by a functional equivalent (this is likely the case for
"cr_report_line" rows). Links are supposed to be on the "entry_id"
field in the "acc_trans" table.
Doing "SELECT FROM acc_trans WHERE amount IS NULL" returns zero rows (on
both databases).
The "entry_id" column of table "acc_trans" is just sequential numbers,
as far as I can tell. Maybe I misunderstand.
"payment_links" and "cr_report_line" are both empty tables in that
database.
Hope the above helps a bit!
It gives me something to test, at least. I was concerned that the
database I was trying to convert might be too old (it's from a defunct
business, but I'm 99% certain I've pulled it up in 1.3.x), so I tried
one that I used most recently before upgrading, and got exactly the same
result.
—Victor
1 May
1 May
10:56 a.m.
Hi Victor,
On Sat, May 1, 2021 at 1:20 AM Victor Wren <vwren(a)ponyhome.com> wrote:
Good. That simplifies the repairs we're looking at!
> Hope the above helps a bit!
> It gives me something to test, at least. I
was concerned that the
> database I was trying to convert might be too old (it's from a defunct
> business, but I'm 99% certain I've pulled it up in 1.3.x),
so I tried one that I used most recently before
upgrading, and got exactly
> the same result.
Any database that was dumped from LedgerSMB 1.2, is supposed to be
supported for the upgrade process. Due to lack of real life data, however,
this goal is hard to achieve. Regularly, databases show up with migration
issues (we're less likely to hear from successful migrations,
unfortunately); by solving the migration issues that *are* reported to us,
I hope to end up with a migration process that allows most people to
migrate their databases without problems.
BTW, migration from some versions of SQL Ledger are also supported, but
there's even less data available to us to test the correctness of those,
making it much harder to make any guarantees about correctness.
Regards,
--
Bye,
Erik.
http://efficito.com -- Hosted accounting and ERP.
Robust and Flexible. No vendor lock-in.
$ perl -MLog::Log4perl::Layout::PatternLayout -E
'say "ok"'
Says "ok" so it (apparently?) doesn't think that PatternLayout.pm is
actually missing.
Yes, that's what it means indeed. Did you run this with a PERL5LIB
environment variable set? If so, we should make sure the same setting is
available in the environment in which the Starman process runs.
I forgot to mention that I WAS able to create a new
"demo" business, so
that much is working, at least,
despite some creative launching (right now I'm manually launching
starman-ledgersmb until I get around to
writing a SYSV init script for LFS) It's a shame mod_perl didn't come out
for 2.4 sooner, but what can you do. Still,
good to dust off the cobwebs from my server building skills.
Since you were able to create a new business, this means you're set up
pretty well and that the problem you're experiencing is likely "just"
related to your data.
BTW, there's a CentOS/RH-based SysV init script here:
https://github.com/ledgersmb/LedgerSMB/tree/master/doc/conf/sysvinit
hopefully that helps to (quickly) build one for LFS. I'd be happy to
include such an script in our repository for use by others or even just for
reference.
What it proposes is that you check the data in the
acc_trans table for
NULL values in the 'amount' column. A way to get rid of it (if it's not
referenced from anywhere) would be to run the query "DELETE FROM acc_trans
WHERE amount IS NULL". By "not being referenced from anywhere", I mean
that
the rows in acc_trans can themselves reference "invoice" rows or be
referenced by "payment_link" rows or "cr_report_line" rows (off the
top of
my head) and probably others. When those lines refer to these rows, either
the referencing rows are meaningless (likely this is the case for "invoice"
rows). Or they need to be replaced by a functional equivalent (this is
likely the case for "cr_report_line" rows). Links are supposed to be on the
"entry_id" field in the "acc_trans" table.
Doing "SELECT FROM acc_trans WHERE amount IS NULL" returns zero rows (on
both databases).
The "entry_id" column of table "acc_trans" is just sequential
numbers, as
far as I can tell. Maybe I misunderstand.
That's interesting. If there are no rows where "amount" is null in
acc_trans, then why does this query fail:
select trans_id, chart_id, transdate from acc_trans WHERE amount IS NULL
?
"payment_links" and "cr_report_line" are both empty tables in that
database.
3:58 p.m.
Says "ok" so it (apparently?)
doesn't think that PatternLayout.pm
is actually missing.
Yes, that's what it means indeed. Did you run this with a PERL5LIB
environment variable set? If so, we should make sure the same setting
is available in the environment in which the Starman process runs.
I set the variable in the global "profile" file, so it's available to
all users. What it should contain is a better question. Right now it
contains the path to my Perl repository and the path to the root
directory of ledgersmb
(/usr/lib/perl5/site_perl/5.26.0/:/usr/local/ledgersmb/)
BTW, there's a CentOS/RH-based SysV init script
here:
https://github.com/ledgersmb/LedgerSMB/tree/master/doc/conf/sysvinit
<https://github.com/ledgersmb/LedgerSMB/tree/master/doc/conf/sysvinit>
hopefully that helps to (quickly) build one for LFS. I'd be happy to
include such an script in our repository for use by others or even
just for reference.
The LFS boot scripts are much more "bare bones" but this will be an
excellent starting point, thanks!
The "entry_id" column of table "acc_trans" is just sequential
numbers, as far as I can tell. Maybe I misunderstand.
That's interesting. If there are no rows where "amount" is null in
acc_trans, then why does this query fail:
select trans_id, chart_id, transdate from acc_trans WHERE amount IS NULL
This /may /be simpler than it looks. Trying to use it directly from
psql, I'm running into an access problem.
vwren$ psql -h localhost -U lsmb_dbadmin -d sql_ledger_weld
Password for user lsmb_dbadmin:
psql (13.2)
Type "help" for help.
sql_ledger_weld=> select trans_id, chart_id, transdate from acc_trans
WHERE amount IS NULL;
ERROR: permission denied for table acc_trans
The current owner of the database is the legacy "ledgersmb" user.
So why does this appear to be working when I give the user/password
credentials in setup.pl? It can read the database and determine the
version of it, and offer to update it.
Let me try changing the ownership of the database (REASSIGN OWNED) and
see of that fixes anything. ...
Nope, not that simple. Still fails at the same spot in setup.pl even
with all objects owned by lsmb_dbadmin, even though the command I tried
above now works with all objects owned by lsmb_dbadmin. Looks like
ownership doesn't make a difference. Darn it.
root# psql -h localhost -U lsmb_dbadmin -d sql_ledger_weld
Password for user lsmb_dbadmin:
psql (13.2)
Type "help" for help.
sql_ledger_weld=> select trans_id, chart_id, transdate from acc_trans
WHERE amount IS NULL;
trans_id | chart_id | transdate
----------+----------+-----------
(0 rows)
Any database that was dumped from LedgerSMB 1.2, is
supposed to be
supported for the upgrade process. Due to lack of real life data,
however, this goal is hard to achieve. Regularly, databases show up
with migration issues (we're less likely to hear from successful
migrations, unfortunately); by solving the migration issues that *are*
reported to us, I hope to end up with a migration process that allows
most people to migrate their databases without problems.
The one I provided to you started out life as an SQL_Ledger database,
was converted for 1.2 and then for 1.3. "setup.pl" identifies it as a
1.3 database.
—Victor
5 May
5 May
8:23 p.m.
Hi All,
BTW, there's a CentOS/RH-based SysV init script here:
https://github.com/ledgersmb/LedgerSMB/tree/master/doc/conf/sysvinit
hopefully that helps to (quickly) build one for LFS. I'd be happy to
include such an script in our repository for use by others or even just for
reference.
The LFS boot scripts are much more "bare bones" but this will be an
excellent starting point, thanks!
The "entry_id" column of table "acc_trans" is just sequential
numbers, as
For the information of the mailing list: This discussion didn't peter out
without a solution; instead, Victor and I exchanged some screenshots of the
problems he was looking at, privately. With his help, I was able to create
the 1.8.12 release, which has allowed him to migrate (as far as I know) the
books of two companies.
--
Bye,
Erik.
http://efficito.com -- Hosted accounting and ERP.
Robust and Flexible. No vendor lock-in.
far as I can tell. Maybe I misunderstand.
That's interesting. If there are no rows where "amount" is null in
acc_trans, then why does this query fail:
select trans_id, chart_id, transdate from acc_trans WHERE amount IS NULL
This *may *be simpler than it looks. Trying to use it directly from
psql, I'm running into an access problem.
1 May
1 May
11:25 a.m.
Hi,
On Sat, May 1, 2021 at 1:20 AM Victor Wren <vwren(a)ponyhome.com> wrote:
I forgot to mention that I WAS able to create a new
"demo" business, so
that much is working, at least,
despite some creative launching (right now I'm manually launching
starman-ledgersmb until I get around to
writing a SYSV init script for LFS) It's a shame mod_perl didn't come out
for 2.4 sooner, but what can you do.
Just for reference, there's a PSGI / mod_perl handler available from CPAN:
https://metacpan.org/pod/Plack::Handler::Apache2
Regards,
--
Bye,
Erik.
http://efficito.com -- Hosted accounting and ERP.
Robust and Flexible. No vendor lock-in.
689
days inactive
694
days old
6 comments
2 participants
participants (2)
-
Erik Huelsmann -
Victor Wren