Mysql connection reset by peer

Question
1

Hi all, finally i got my HA privecyidea setup working with a mysql galera cluster master-master, but if i leave my admin user logged for some minutes, it will be locked and when i try to login again i got for the first time:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, ‘Lost connection to MySQL server during query ([Errno 104] Connection reset by peer)’)
[SQL: SELECT config.Key AS config_Key, config.Value AS config_Value, config.Type AS config_Type, config.Description AS config_Description
FROM config
WHERE config.Key = %(Key_1)s
LIMIT %(param_1)s]
[parameters: {‘Key_1’: ‘timestamp’, ‘param_1’: 1}]
(Background on this error at: Error Messages — SQLAlchemy 1.3 Documentation)

and i can’t login, entering again the password it will let me enter in

Is there a way to fix it ?

I searched and it seems that the problem can be sqalchemy timeout, so a fix could be using a pool_pre_ping and a pool_recycle but i don’t know where to put these.

I tried to add it to the

SQLALCHEMY_DATABASE_URI

after the dbname but it didn’t work, i got Internal Server Error

thanks in advance
Claudio

2

Someone has some ideas why it happens

i lowered PI_AUDIT_POOL_SIZE = 1 but it still persists

When a user enter in lock screen, if he try to put the password to login, the first attempt give an internal server error (Comnection reset by peer) while the second attempt goes perfect and authenticate the user.

I’m talking about an user that connect to privacyidea WebUi interface

Thanks for any suggestion.
Claudio

3

A connection reset by peer usually meens, that the timeout of the SQL server is lower than the one of the SQL client/SQLAlchemy.
Check to adapt your timeouts, when which partner closes the connection.
The client should be shorter.

4

for now seems i solved adding pool_pre_ping: True to

SQLALCHEMY_ENGINE_OPTIONS = { “pool_pre_ping” : True }

in mysql i have:
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800

i will try to reset PI_AUDIT_POOL_SIZE = 20 and i will see what happens, anyway now ti seems to work pretty well.

Can i ask you why privacyidea-apache2 install a mysql-server locally ?

For now i stopped and disabled mysql, but i can’t remove it else will be removed also privacyidea, mysql-client and all the installed packages needed for privacyidea.
We use an external mysql galera cluster, so we don’t need mysql locally, is there an easy way to install privacyidea (for apache2) without the mysql-server ?

Another question, we have 500 pages of documentation, but not a single example about variables, or default value or which values are possible, also some example of using all the 2FA method, i know it is big task to do, but a lot of things are here on the forum, an easy copy and paste in the documentation.

Same for the HA, it tells what to do, but a list of all files or keys to be copied on all the privacyidea servers would be better and this will avoid future questions about the same topic.

5

with PI_AUDIT_POOL_SIZE <> 1 the connection reset by peer appears again

so for now i will use that

if you have hints on how modify client timeout, let me know

thanks
Claudio

6

No also with PI_AUDIT_POOL_SIZE =1 the connection reset by peer occurs

i have to understand how to fix this problem.

Claudio

7

I added poo_recycle = 600 in the ENGINE_OPTIONS

SQLALCHEMY_ENGINE_OPTIONS = { “pool_pre_ping” : True, “pool_recycle” : 600 }

i hope this will fix the problems

Claudio

8

Nothing i still have the connection lost problem,

any hints to how to resolve it ?

how can i set a lower timeout in the SQLALCHEMY_ENGINE_OPTIONS or in the mysql client configuration ?

Thanks for any advice
Claudio