Running LedgerSMB 1.5 with a schema other than public
Hi,
In my current installation of LedgerSMB, the tables are in a schema other than public, because I installed it in a database where there are other data in schema public. (The reason I did that is that I want to link tables in schema public and LedgerSMB tables, but I have not done it yet.) I could make it work relatively painlessly with LedgerSMB 1.3 by setting db_namespace in ledgersmb.conf. Recently (that is, very late!) I wanted to upgrade to LedgerSMB 1.5.24 (not 1.6 because my server is still under Debian stretch). I think I could upgrade the database correctly with setup.pl, but when running login.pl it will not work because, according to Postgresql's logs, it insists on trying to call the stored procedure public.setting_get, which does not exist. Although I do not know perl, I tried to read the code to understand what is going on. If I understood well, stored procedures are called by calling directly or indirectly the perl function call_procedure in module PGObject, which sets the schema to public when it is not specified. However, module LedgerSMB also contains a function call_procedure which correctly sets the schema, so the problem seems to be that sometimes PGObject's call_procedure is called directly instead of module LedgerSMB's. I do not understand why, because I do not know what constructs like $self->call_procedure or __PACKAGE__->call_procedure do, but anyway this is very annoying for me, because it means that LedgerSMB 1.5 just will not work with db_namespace set to anything else than public. Was this problem known? Is it corrected in a later version?
Regards.
Hi Nicolas,
Thanks for writing about your problem!
In my current installation of LedgerSMB, the tables are in a schema other
than public, because I installed it in a database where there are other data in schema public. (The reason I did that is that I want to link tables in schema public and LedgerSMB tables, but I have not done it yet.)
No problem. Your setup *should* be supported. And I remember helping you when you first set up your database with the non-public schema.
I could make it work relatively painlessly with LedgerSMB 1.3 by setting db_namespace in ledgersmb.conf.
Actually, you should still be able to do that. However, I think the section in which the key resides has changed. Does your ledgersmb.conf have this:
[database] db_namespace = alternativeschema
? That is, is your db_namespace in the "database" section of the config file?
Recently (that is, very late!) I wanted to upgrade to LedgerSMB 1.5.24 (not 1.6 because my server is still under Debian stretch). I think I could upgrade the database correctly with setup.pl, but when running login.pl it will not work because, according to Postgresql's logs, it insists on trying to call the stored procedure public.setting_get, which does not exist.
Thanks for this info. I think I found that the module which calls the 'setting_get' (lib/LedgerSMB/PGOld.pm) to be lacking the code to set up the db_namespace. I'm now trying to figure out what the best way is to pass the configuration value to the database call component.
Although I do not know perl, I tried to read the code to understand what is going on. If I understood well, stored procedures are called by calling directly or indirectly the perl function call_procedure in module PGObject, which sets the schema to public when it is not specified. However, module LedgerSMB also contains a function call_procedure which correctly sets the schema, so the problem seems to be that sometimes PGObject's call_procedure is called directly instead of module LedgerSMB's.
That's a completely correct assessment! The component that's missing to pass the argument is lib/LedgerSMB/PGOld.pm.
I do not understand why, because I do not know what constructs like $self->call_procedure or __PACKAGE__->call_procedure do, but anyway this is very annoying for me, because it means that LedgerSMB 1.5 just will not work with db_namespace set to anything else than public. Was this problem known? Is it corrected in a later version?
Is it a known problem? No, I wasn't aware of it. (And by extension, it's not fixed in any currently released versions.)
I want to help you by getting this fixed - in a 1.5 release. However, the last two weeks, I've had very little time to work on this project and my family duties due to a major release at $work. Would it be ok for you if we (you and I) were able to work out the complete and correct solution over the coming weeks? (Then, I'd expect you to have a working solution before the end of august.) Or is this currently blocking your $getting_work_done ?
Regards,
Hi Nicolas,
On Sun, Jul 28, 2019 at 9:22 PM Erik Huelsmann ehuels@gmail.com wrote:
Recently (that is, very late!) I wanted to upgrade to LedgerSMB 1.5.24 (not 1.6 because my server is still under Debian stretch). I think I could upgrade the database correctly with setup.pl, but when running login.pl it will not work because, according to Postgresql's logs, it insists on trying to call the stored procedure public.setting_get, which does not exist.
Thanks for this info. I think I found that the module which calls the 'setting_get' (lib/LedgerSMB/PGOld.pm) to be lacking the code to set up the db_namespace. I'm now trying to figure out what the best way is to pass the configuration value to the database call component.
I've created a change to the code base which is at least a step in the right direction. Could you check that it works when you place these files on your server:
https://github.com/ledgersmb/LedgerSMB/blob/8f5782ee9fe2e6159f42b652fd77c331... https://github.com/ledgersmb/LedgerSMB/blob/8f5782ee9fe2e6159f42b652fd77c331...
?
There should be files on your system with the names "lib/LedgerSMB/PGOld.pm" and "lib/LedgerSMB/PGObject.pm". Make a backup of those files before you do anything, so you can restore your system to what it was. Then, download the two files above and replace the files you just backed up. Then restart your server processes (LedgerSMB service) and try to log in after that.
If you still have problems, please let me know which ones and I'll work to resolve those too. If you need help applying the fix, don't hesitate to mail or come to IRC/Matrix for help.
Regards,
Erik Huelsmann a écrit :
I've created a change to the code base which is at least a step in the right direction. Could you check that it works when you place these files on your server:
https://github.com/ledgersmb/LedgerSMB/blob/ 8f5782ee9fe2e6159f42b652fd77c331dd25a737/lib/LedgerSMB/PGOld.pm https://github.com/ledgersmb/LedgerSMB/blob/ 8f5782ee9fe2e6159f42b652fd77c331dd25a737/lib/LedgerSMB/PGObject.pm
?
Hi,
It seems to work, thank you ! I found other places where schema public explicitely appears : * In sql/changes/1.5/open_forms_callers.sql, there are three requests explicitely mentioning table « public.open_forms ». This prevents the complete upgrade of the tables from 1.3 to 1.5. I patched the file by suppressing « public. » and it works. * In sql/modules/Roles.sql, there is a request « GRANT ALL ON SCHEMA public TO public; ».
Regards.
On Tue, Aug 13, 2019 at 9:02 PM Nicolas Couchoud < nicolas.couchoud__ledgersmb@normalesup.org> wrote:
Erik Huelsmann a écrit :
I've created a change to the code base which is at least a step in the
right
direction. Could you check that it works when you place these files on
your
server:
https://github.com/ledgersmb/LedgerSMB/blob/ 8f5782ee9fe2e6159f42b652fd77c331dd25a737/lib/LedgerSMB/PGOld.pm https://github.com/ledgersmb/LedgerSMB/blob/ 8f5782ee9fe2e6159f42b652fd77c331dd25a737/lib/LedgerSMB/PGObject.pm
?
Hi,
It seems to work, thank you !
Great! Thanks for the confirmation. I can make the suggested adjustments and merge the branch to make a new release.
I found other places where schema public explicitely appears :
- In sql/changes/1.5/open_forms_callers.sql, there are three requests
explicitely mentioning table « public.open_forms ». This prevents the complete upgrade of the tables from 1.3 to 1.5. I patched the file by suppressing « public. » and it works.
Hmm. Ok. In order to prevent future updates from going breaking on your database, I need a bit of information from you: In your database, there's a table called "db_patches". This table has a 'sha' and a 'path' column. I need the content of the 'sha' column for each of the files you patched (indicated in the 'path' column). Could you send me the sha of that file? I want to see if I can recreate the same SHA. (or that I need to send you a new sha for that column...)
- In sql/modules/Roles.sql, there is a request « GRANT ALL ON SCHEMA
public TO public; ».
I'll update that one too.
Thanks again!
Regards,
Erik Huelsmann a écrit :
On Tue, Aug 13, 2019 at 9:02 PM Nicolas Couchoud < nicolas.couchoud__ledgersmb@normalesup.org> wrote:
I found other places where schema public explicitely appears : * In sql/changes/1.5/open_forms_callers.sql, there are three requests explicitely mentioning table « public.open_forms ». This prevents the complete upgrade of the tables from 1.3 to 1.5. I patched the file by suppressing « public. » and it works.Hmm. Ok. In order to prevent future updates from going breaking on your database, I need a bit of information from you: In your database, there's a table called "db_patches". This table has a 'sha' and a 'path' column. I need the content of the 'sha' column for each of the files you patched (indicated in the 'path' column). Could you send me the sha of that file? I want to see if I can recreate the same SHA. (or that I need to send you a new sha for that column...)
The only file I patched in direcftory sql/changes is sql/changes/1.5/open_forms_callers.sql . The corresponding sha column in table db_patches is ke63Vj9L87jBNBhn3SgzIAtT2WQ3GBjowM24pRtt4H1a13PmUd3hW9T+VQCsPUF2UDXWs9pMIPbOEE/l7kNROA In my patched file I added a comment in French so it may be difficult for you to obtain the same sha.
Regards.
Hi Nicolas,
On Sun, Aug 18, 2019 at 3:24 PM Nicolas Couchoud < nicolas.couchoud__ledgersmb@normalesup.org> wrote:
[ snip ]
I need the content of the 'sha' column for each of the files you patched (indicated in the 'path' column). Could you send me the sha of that file? I want to see if I can recreate the same SHA. (or that I need to send you a new sha for that column...)
The only file I patched in direcftory sql/changes is sql/changes/1.5/open_forms_callers.sql . The corresponding sha column in table db_patches is
ke63Vj9L87jBNBhn3SgzIAtT2WQ3GBjowM24pRtt4H1a13PmUd3hW9T+VQCsPUF2UDXWs9pMIPbOEE/l7kNROA In my patched file I added a comment in French so it may be difficult for you to obtain the same sha.
Thanks for sending the SHA, I was just working on completing the change and the builders are running to verify the commits.
We thought beforehand that it would be hard to maintain the exact same content of the files over time -- either because of space-reformatting or because we (or someone else--like you) would feel or even have the need to add comments. So: as it happens, the SHA of the file I prepared in the commit that's being verified *matches* yours!
Regards,
participants (2)
-
Erik Huelsmann -
Nicolas Couchoud