Error in creating Participant in MySQL

[phax]

          Hi Philip,

a very similar problem:

SMP server version 7.1.1 with SQL backend:
On JDBC settings: amended after checking your post above - no luck:

In logs:

Database user must have full access to database schema.
All database tables were created during server startup:

Originally posted by @skizub in #273 (comment)

[phax]

@skizup lets take this separately.

Can you please show me the "cause" of the SQLException you shown in the logs? Seems some DB specific problem...

[skizub]

Philip, yes sure and thanks for moving issue to the right place.

As all logging available for JDBC is enabled, you can see all SELECTs on the screenshot attached to the original message. For INSERT statement where it seems to be failing to insert into 'smp_service_group' SQL statement is not available so I cannot try it manually to see why process fails. On the other hand it seems to be inserting into 'smp_audit' table ok:

Yes, forgot to mention - we are using Maria DB v10.5.8, where we moved all our MySQL schemas long time ago without any issues (at least for basic set SQL commands like SELECT, INSERT, UPDATE, DROP).

[phax]

@skizub In your original post you added this image:

I would need to know the further details of the stack trace in that image (the "caused by..." stuff). Can you please post it here. Thanks

[skizub]

sure

[phax]

The problem is, that the creation of the Service Group in the SML did not work. Please make sure you selected the correct "SML" in the "SMP Settings". Should be "SMK" for test and "SML" for production.

[skizub]

Thank you.
Initial SMP settings were following:

When I changed it :

registration started to work.

problem was resolved.
Thanks for a prompt feedback as usual.

[phax]

Well yes it works now, but also means: Your participants don't end up in the SML and therefore they are not disoverable by other Peppol participants.

Are you using Peppol certificates? If so, Test or Production?

[skizub]

That's correct - I just tried to retrieve one of the service groups registered via PEPPOL Practical portal - getting an error saying that service group is not registered. We are using TEST PEPPOL certificates.

[phax]

@skizub okay, that is weird. The initial setting is the correct one:

Are you using an outbound proxy server for connections to the Internet?

[skizub]

@phax , I'm not aware of any proxy server used at our end, but I'll try to dig out more details from networking team and get post an update.

[phax]

Please check if your configuration contains any of the items mentioned at https://github.com/phax/phoss-smp/wiki/Configuration#httpproxy - the UI should also give an indicator.

But the underlying problem is clearly that you can reach the SML. Maybe it was just a temportary hickup and a retry will work??

[skizub]

@phax, thank you - I'll double-check configuration.

Also it doesn't look like a temporary issue, I just tried to set two "SML connection..." fields to 'true' and got same error back. When I set them back to 'false', registration works, but only in local database SML is not being updated. Except proxy option, cannot it be a firewall blocking outgoing connections?

[phax]

Yes, it might also be some firewall issue - but they usually prevent connection and don't respond with an HTTP error code. You may try to use the "debug" log level to see the transmission and the headers returned - maybe that helps to see the source of the error.

[skizub]

@phax,
I enabled DEBUG mode.

Proxy Initialization (from application.properties)

Service Group registration(beginning before producing exceptions):

If I can read logs correctly it uses direct connection.

[phax]

Yes thanks - it uses direct connection.
Can you please import your Peppol Test SMP certificate into your browser and access https://acc.edelivery.tech.ec.europa.eu/edelivery-sml/ - it should show a page like this:

Or do you also get an HTTP 404 when trying to do so?

[skizub]

@phax, tried to access SML from browser, which I'm using to configure SMP backend. Here is a reply I got:

Tried to use wget from the server, where backend is running:
wget https://acc.edelivery.tech.ec.europa.eu/edelivery-sml/

Also certificates are looking good in SMP frontend:

[phax]

Hmm, this is very weird. I am afraid I can't help you any further without looking at the system. Please reach out to me via email (firstname@lastname.com) and lets see if we can have a Zoom session or so...

[phax]

We are trying to resolve the topic out of band. It seems to have been a network level configuration issue...

[skizub]

@phax , many thanks for your help. All works perfectly now.

Just as an nice to have point for future versions: is it possible to mention that given exception could be linked to the networking issue?

What it says currently somehow leads user to check database...

[phax]

Well yes, the base exception is this:

which "clearly" indicates a miscommunication with the SML server (even though a very odd one).

Also the second level exception indicates that:

However I agree, that the error on the UI should highlight that better