Replacement of tokens in SQL files
Hello,
I'm trying to upgrade an ancient LSMB (1.2.26) to a slightly less ancient one (1.3.47) and the upgrade is not going smoothly, as you might imagine.
This error is present in the database log:
2020-04-16 11:42:42 BST ERROR: role "lsmb_<?lsmb dbname ?>__part_edit" does not exist 2020-04-16 11:42:42 BST STATEMENT: GRANT "lsmb_testbooks__file_read" TO "lsmb_<?lsmb dbname ?>__part_edit";
which seems to relate to line 707 in sql/modules/Roles.sql:
GRANT "lsmb_<?lsmb dbname ?>__file_read" TO "lsmb_<?lsmb dbname ?>__part_edit";
So, it looks like the substitution of the second dbname token in that line is not happening.
Can you suggest any reason why that might be and how/where I should go about fixing it?
Thanks very much,
Pete
Hi Pete,
On Thu, Apr 16, 2020 at 2:48 PM Pete Houston ph1@openstrike.co.uk wrote:
Hello,
I'm trying to upgrade an ancient LSMB (1.2.26) to a slightly less ancient one (1.3.47) and the upgrade is not going smoothly, as you might imagine.
Upgrading from 1.2 to something newer than 1.3 isn't available, I think, but I'd like to urge you to pick something newer: 1.3 hasn't had any maintenance for *years* -- if you'd pick something not too recent, like 1.6, at least you'd be able to fall back on the community support for help and maintenance. With 1.3, no more fixes will be coming...
Is there anything we (the project) can help you with that would allow you to pick a newer version?
This error is present in the database log:
2020-04-16 11:42:42 BST ERROR: role "lsmb_<?lsmb dbname ?>__part_edit" does not exist 2020-04-16 11:42:42 BST STATEMENT: GRANT "lsmb_testbooks__file_read" TO "lsmb_<?lsmb dbname ?>__part_edit";
which seems to relate to line 707 in sql/modules/Roles.sql:
GRANT "lsmb_<?lsmb dbname ?>__file_read" TO "lsmb_<?lsmb dbname ?>__part_edit";
So, it looks like the substitution of the second dbname token in that line is not happening.
Can you suggest any reason why that might be and how/where I should go about fixing it?
The problem stems from this line: https://github.com/ledgersmb/LedgerSMB/blob/1.3/LedgerSMB/Database.pm#L674
There, the regex should have a 'g' at the end: s/.../.../g. With this "g", all occurrances in the string will be replaced instead of only the first one.
Thanks very much,
Pete
Openstrike - improving business through open source https://www.openstrike.co.uk/ or call 01722 770036 _______________________________________________ users mailing list -- users@lists.ledgersmb.org To unsubscribe send an email to users-leave@lists.ledgersmb.org
Regards,
Hi Erik,
Thanks for your reply.
On Thu, Apr 16, 2020 at 08:08:56PM +0200, Erik Huelsmann wrote:
On Thu, Apr 16, 2020 at 2:48 PM Pete Houston ph1@openstrike.co.uk wrote:
Hello,
I'm trying to upgrade an ancient LSMB (1.2.26) to a slightly less ancient one (1.3.47) and the upgrade is not going smoothly, as you might imagine.
Upgrading from 1.2 to something newer than 1.3 isn't available, I think, but I'd like to urge you to pick something newer: 1.3 hasn't had any maintenance for *years* -- if you'd pick something not too recent, like 1.6, at least you'd be able to fall back on the community support for help and maintenance. With 1.3, no more fixes will be coming...
Is there anything we (the project) can help you with that would allow you to pick a newer version?
The upgrade to 1.3.x is just a stepping stone to reach a supported version. I am going to have to go through a couple of iterations of upgrading LSMB then upgrading postgresql to inch my way up the ladder.
If there's a better way, do please suggest it!
So, it looks like the substitution of the second dbname token in that line is not happening.
Can you suggest any reason why that might be and how/where I should go about fixing it?
The problem stems from this line: https://github.com/ledgersmb/LedgerSMB/blob/1.3/LedgerSMB/Database.pm#L674
There, the regex should have a 'g' at the end: s/.../.../g. With this "g", all occurrances in the string will be replaced instead of only the first one.
That's it! It certainly looked like a missing /g or equivalent but I had no idea where to look for it. I've applied that fix now and all the GRANTS succeed. Thank you.
However, there are still a tonne of SQL errors, some of which are no doubt benign and a lot of which are just because the current transaction has been aborted. I do see a few foreign key constraint violations which look a bit concerning. The first such is this:
2020-04-17 11:06:52 BST ERROR: insert or update on table "menu_acl" violates foreign key constraint "menu_acl_node_id_fkey" 2020-04-17 11:06:52 BST DETAIL: Key (node_id)=(134) is not present in table "menu_node". 2020-04-17 11:06:52 BST STATEMENT: INSERT INTO menu_acl (node_id, acl_type, role_name) values (134, 'allow', 'lsmb_testbooks__backup');
... which might be fine. There is indeed no 134 in menu_node - there's nothing between 132 (Yearend) and 136 (Chart of Accounts) - but maybe I just removed whatever that was years ago.
It is followed by the slightly more worrying-looking
2020-04-17 11:06:52 BST ERROR: insert or update on table "account_link" violates foreign key constraint "account_link_description_fkey" 2020-04-17 11:06:52 BST DETAIL: Key (description)=(CT_tax) is not present in table "account_link_description". 2020-04-17 11:06:52 BST CONTEXT: SQL statement "INSERT INTO account_link (account_id, description) VALUES ( $1 , $2 )" PL/pgSQL function "account_save" line 60 at SQL statement 2020-04-17 11:06:52 BST STATEMENT: SELECT account_save(id, accno, description, category, gifi_accno, NULL, contra, CASE WHEN link like '%tax%' THEN true ELSE false END, string_to_array(link,':')) FROM lsmb12.chart WHERE charttype = 'A';
There is no CT_* of any sort in account_link_description. Is this something necessary?
Thanks again,
Pete
Hi Pete,
I'm trying to upgrade an ancient LSMB (1.2.26) to a slightly less
ancient one (1.3.47) and the upgrade is not going smoothly, as you
might
imagine.
Is there anything we (the project) can help you with that would allow you to pick a newer version?
The upgrade to 1.3.x is just a stepping stone to reach a supported version. I am going to have to go through a couple of iterations of upgrading LSMB then upgrading postgresql to inch my way up the ladder.
If there's a better way, do please suggest it!
Well, I'm short on time this weekend, but I've wanted to support migrations from 1.2 to the current versions (1.6 / 1.7) out of the box. Not knowing how many people are still on 1.2, at the time, it looked like a dumb goal to give a high priority. However, if there are (many) others on this mailing list on 1.2 as well, I'll be happy to work on that goal in the short term!
The problem stems from this line:
https://github.com/ledgersmb/LedgerSMB/blob/1.3/LedgerSMB/Database.pm#L674
There, the regex should have a 'g' at the end: s/.../.../g. With this
"g",
all occurrances in the string will be replaced instead of only the first one.
That's it! [...] Thank you.
No problem :-) You're welcome.
However, there are still a tonne of SQL errors, some of which are no doubt benign and a lot of which are just because the current transaction has been aborted. I do see a few foreign key constraint violations which look a bit concerning. The first such is this:
2020-04-17 11:06:52 BST ERROR: insert or update on table "menu_acl" violates foreign key constraint "menu_acl_node_id_fkey" 2020-04-17 11:06:52 BST DETAIL: Key (node_id)=(134) is not present in table "menu_node". 2020-04-17 11:06:52 BST STATEMENT: INSERT INTO menu_acl (node_id, acl_type, role_name) values (134, 'allow', 'lsmb_testbooks__backup');
... which might be fine. There is indeed no 134 in menu_node - there's nothing between 132 (Yearend) and 136 (Chart of Accounts) - but maybe I just removed whatever that was years ago.
The problem with the database scripts as we had them during the 1.3 release is that they aren't incremental. They'd simply be run on each upgrade again and again while at the same time Pg-database.sql as *also* updated. This causes lots of warnings and errors for which it is hard to estimate if they are problematic or not. Exactly because of that, we have used a different schema-upgrade strategy since 1.5, which tracks which updates have been applied to the schema. Pg-database.sql should be immutable. (Although, in all honesty, I *have* removed some elements from it, to reduce confusion, taking possible-non-existence into account in the schema-upgrade.)
It is followed by the slightly more worrying-looking
2020-04-17 11:06:52 BST ERROR: insert or update on table "account_link" violates foreign key constraint "account_link_description_fkey" 2020-04-17 11:06:52 BST DETAIL: Key (description)=(CT_tax) is not present in table "account_link_description". 2020-04-17 11:06:52 BST CONTEXT: SQL statement "INSERT INTO account_link (account_id, description) VALUES ( $1 , $2 )" PL/pgSQL function "account_save" line 60 at SQL statement 2020-04-17 11:06:52 BST STATEMENT: SELECT account_save(id, accno, description, category, gifi_accno, NULL, contra, CASE WHEN link like '%tax%' THEN true ELSE false END, string_to_array(link,':')) FROM lsmb12.chart WHERE charttype = 'A';
There is no CT_* of any sort in account_link_description. Is this something necessary?
I didn't know the answer to this: I hadn't seen CT_* before today. However, I looked up the 1.2 code and searched for it. It's not in the code base. It's in a few chart-of-accounts definition files, but that's all. I'm thinking your data has a longer history than LedgerSMB 1.2 and that these come from that line of history. Am I right?
Regards,
Hi Erik,
On Fri, Apr 17, 2020 at 07:56:30PM +0200, Erik Huelsmann wrote:
The upgrade to 1.3.x is just a stepping stone to reach a supported version. I am going to have to go through a couple of iterations of upgrading LSMB then upgrading postgresql to inch my way up the ladder.
If there's a better way, do please suggest it!
Well, I'm short on time this weekend, but I've wanted to support migrations from 1.2 to the current versions (1.6 / 1.7) out of the box. Not knowing how many people are still on 1.2, at the time, it looked like a dumb goal to give a high priority. However, if there are (many) others on this mailing list on 1.2 as well, I'll be happy to work on that goal in the short term!
I don't suppose there will be many still using it. We are only still using it because the upgrades to 1.3 in the past were so problematic that it was always put off. Inertia can be a powerful thing.
I didn't know the answer to this: I hadn't seen CT_* before today. However, I looked up the 1.2 code and searched for it. It's not in the code base. It's in a few chart-of-accounts definition files, but that's all. I'm thinking your data has a longer history than LedgerSMB 1.2 and that these come from that line of history. Am I right?
Yes, you are right. The data started off in SQL-Ledger and I *think* we moved to LedgerSMB at 1.1.x and then upgraded to 1.2.x but that's all so long ago that I cannot be sure. Digging through the existing 1.2.26 DB I have found the CT_tax references now. They are in the link field of the chart table for 3 VAT accounts. I expect that these can be ignored or even removed prior to running the upgrade.
The problem with all these database errors is that they abort the transaction and therefore it is unclear to me whether the rest of the transaction would have been important or not. Something is clearly going wrong with the upgrade as a whole as, for example, the ap table is completely empty after the upgrade. There are 185 errors listed in the db log file and trying to guess which ones are relevant makes the whole thing more difficult. I will persist.
Pete
Hi Pete,
[ ...]
Not knowing how many people are still on 1.2, at the time, it looked like a dumb goal to give a high priority. However, if there are (many) others on this mailing list on 1.2 as well, I'll be happy to work on that goal in the short term!
I don't suppose there will be many still using it. We are only still using it because the upgrades to 1.3 in the past were so problematic that it was always put off. Inertia can be a powerful thing.
True. Which is why I'm hoping that there are other users on the list who -if still on 1.2 or knowing others who still are- will speak up so we have some idea about the situation. On the other hand, I really feel that you're likely not alone with the pain you felt when trying to move to 1.3. If the same inertia happened for others, there might actually be quite some more users on 1.2!
[ ... ]
The problem with all these database errors is that they abort the
transaction and therefore it is unclear to me whether the rest of the transaction would have been important or not.
Yes. That's why we are currently running a very different procedure where there are scripts running to verify that the schema data *can* be migrated to the new version and - if not - which offer data-correction possibilities in-application. The nice thing is that if these scripts fail, we can analyse your specific problem and determine whether it likely has any impact on others. Then, we can include extra checks in a new release which then allow your migration to proceed just the way you expect. Additional benefit: the community as a whole will have a better migration experience.
Something is clearly going wrong with the upgrade as a whole as, for example, the ap table is completely empty after the upgrade. There are 185 errors listed in the db log file and trying to guess which ones are relevant makes the whole thing more difficult.
Feel free to send me the dump with errors [off-list] (and the file you're running which is causing them). I'll have a look and see if I can help you based on that.
I will persist.
Thanks! I hope you will help us learn from your experience and make it easier for whomever is still on 1.2...
Hi all,
On Sun, Apr 19, 2020 at 1:28 AM Erik Huelsmann ehuels@gmail.com wrote:
Something is clearly
going wrong with the upgrade as a whole as, for example, the ap table is completely empty after the upgrade. There are 185 errors listed in the db log file and trying to guess which ones are relevant makes the whole thing more difficult.
Feel free to send me the dump with errors [off-list] (and the file you're running which is causing them). I'll have a look and see if I can help you based on that.
Just to confirm: off-list, Pete and I are working on getting his company migrated. The results of the efforts are being taken back into the code base of the project. One of the results is a major refactoring of the upgrade process (the code) in setup.pl; the new way of coding highlighted discrepancies between upgrades from LedgerSMB 1.2 and SQL Ledger 2.8 and 3.0 -- a very nice result. Another result is that the upgrade code in 1.7 had a number of issues which break migration full stop. For 1.8, those will be resolved.
Regards,
Hi all,
Just to confirm: off-list, Pete and I are working on getting his company
migrated. The results of the efforts are being taken back into the code base of the project. One of the results is a major refactoring of the upgrade process (the code) in setup.pl; the new way of coding highlighted discrepancies between upgrades from LedgerSMB 1.2 and SQL Ledger 2.8 and 3.0 -- a very nice result. Another result is that the upgrade code in 1.7 had a number of issues which break migration full stop. For 1.8, those will be resolved.
A lot of back-and-forth off-list between Pete and myself as resulted in a new script being available for 1.8 which I have added to the repository on Friday as "bin/migrate-company". The script migrates LedgerSMB 1.2 and 1.3 databases to LedgerSMB 1.7+. Once the database migration for SQL Ledger 3.0 and 3.2 is done, that migration will be supported too.
The script has a number of rough edges and while Pete and I continue to refine it, it would benefit from broader testing. Please contact me (on- or off-list) if you want to migrate your 1.2 or 1.3 database to 1.7 too. I'll help run the tool (coaching through the steps - I don't need access to your database). The benefit I'll get from that is that it allows me to both finetune the tool and start writing the manual.
Regards, -- Bye,
Erik.
http://efficito.com -- Hosted accounting and ERP. Robust and Flexible. No vendor lock-in.
I realize that I should have sent the mail below with a new topic.
On Sun, May 17, 2020 at 8:10 PM Erik Huelsmann ehuels@gmail.com wrote:
Hi all,
Just to confirm: off-list, Pete and I are working on getting his company
migrated. The results of the efforts are being taken back into the code base of the project. One of the results is a major refactoring of the upgrade process (the code) in setup.pl; the new way of coding highlighted discrepancies between upgrades from LedgerSMB 1.2 and SQL Ledger 2.8 and 3.0 -- a very nice result. Another result is that the upgrade code in 1.7 had a number of issues which break migration full stop. For 1.8, those will be resolved.
A lot of back-and-forth off-list between Pete and myself as resulted in a new script being available for 1.8 which I have added to the repository on Friday as "bin/migrate-company". The script migrates LedgerSMB 1.2 and 1.3 databases to LedgerSMB 1.7+. Once the database migration for SQL Ledger 3.0 and 3.2 is done, that migration will be supported too.
The script has a number of rough edges and while Pete and I continue to refine it, it would benefit from broader testing. Please contact me (on- or off-list) if you want to migrate your 1.2 or 1.3 database to 1.7 too. I'll help run the tool (coaching through the steps - I don't need access to your database). The benefit I'll get from that is that it allows me to both finetune the tool and start writing the manual.
Hi Erik,
Seems you still managed to have the email part of the original thread ;-)
Regards David G
On 18/5/20 2:12 am, Erik Huelsmann wrote:
I realize that I should have sent the mail below with a new topic.
On Sun, May 17, 2020 at 8:10 PM Erik Huelsmann <ehuels@gmail.com mailto:ehuels@gmail.com> wrote:
Hi all, Just to confirm: off-list, Pete and I are working on getting his company migrated. The results of the efforts are being taken back into the code base of the project. One of the results is a major refactoring of the upgrade process (the code) in setup.pl <http://setup.pl>; the new way of coding highlighted discrepancies between upgrades from LedgerSMB 1.2 and SQL Ledger 2.8 and 3.0 -- a very nice result. Another result is that the upgrade code in 1.7 had a number of issues which break migration full stop. For 1.8, those will be resolved. A lot of back-and-forth off-list between Pete and myself as resulted in a new script being available for 1.8 which I have added to the repository on Friday as "bin/migrate-company". The script migrates LedgerSMB 1.2 and 1.3 databases to LedgerSMB 1.7+. Once the database migration for SQL Ledger 3.0 and 3.2 is done, that migration will be supported too. The script has a number of rough edges and while Pete and I continue to refine it, it would benefit from broader testing. Please contact me (on- or off-list) if you want to migrate your 1.2 or 1.3 database to 1.7 too. I'll help run the tool (coaching through the steps - I don't need access to your database). The benefit I'll get from that is that it allows me to both finetune the tool and start writing the manual.-- Bye,
Erik.
http://efficito.com http://efficito.com/ -- Hosted accounting and ERP. Robust and Flexible. No vendor lock-in.
users mailing list -- users@lists.ledgersmb.org To unsubscribe send an email to users-leave@lists.ledgersmb.org
participants (3)
-
David Godfrey -
Erik Huelsmann -
Pete Houston