Mysql.connector with systemd

Hello, my issue concerns importing mysql.connector in a python program. Actually, the program works fine when launched from a terminal, but I cannot start the same program from systemd at boot. It tells me :slight_smile:
mai 27 10:55:43 Raspi2 mesures_tension_mysql.py[337]: import mysql.connector as database
mai 27 10:55:43 Raspi2 mesures_tension_mysql.py[337]: ModuleNotFoundError: No module named ‘mysql’
I have added /usr/bin/python in the PYTHONPATH but I get the same error.
Probably the right mysql is in an other directory, but which one ? If I look for it, it answers with 16 files named mysql in different directories.
Thanks for your help.

How did you install mysql for python?
Did you install it from your systems package manager?

I’m guessing you are using raspberian so you would use apt to install a python mysql package.

For exmaple I found the pymysql package can be installed from python3-mysql:

sudo apt update
sudo apt install python3-pymysql
python
Python 3.11.2 (main, Mar 13 2023, 12:18:29) [GCC 12.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pymysql
>>> pymysql
<module 'pymysql' from '/usr/lib/python3/dist-packages/pymysql/__init__.py'>

If you are using a venv? Did you use the venv in the systemd service?

PYTHONPATH is for python code, not the python program, e.g. where your .py files are.

PATH is for the finding programs and /usr/bin will already be on the PATH.

What is in your systemd service unit?

FYI: post code and logs as preformatted text using the </> button in the :gear: menu.

1 Like

Thanks for your quick reply. I have installed mysql via apt, as follows :
sudo apt install mariadb-server php-mysql
and my python program works fine when launched from the terminal. So, I guess mysql is installed

Here is my systemd service : `nit]
Description=Lancement de mesures_tensions_msql.py

[Service]
Type=simple
ExecStart=/home/pi/python/Solaire/mesures_tension_mysql.py
user=pi
group=pi

[Install]
WantedBy=multi-user.target`

I wonder if the PATH is activated only after the program is started by systemd, but I cannot find a way to delay the start of the program.

A systemd script can have an Environ= line to specify environment variables.

1 Like

Related to what Chris said, Systemd doesn’t use the PATH environment variable from your login shell. It’s hard to link to a specific section of a man page, but I’ll quote:

For each command, the first argument must be either an absolute path to an executable or a simple file name without any slashes. If the command is not a full (absolute) path, it will be resolved to a full path using a fixed search path determined at compilation time. Searched directories include /usr/local/bin/ , /usr/bin/ , /bin/ on systems using split /usr/bin/ and /bin/ directories, and their sbin/ counterparts on systems using split bin/ and sbin/ . It is thus safe to use just the executable name in case of executables located in any of the “standard” directories, and an absolute path must be used in other cases. Hint: this search path may be queried using systemd-path search-binaries-default.

https://www.freedesktop.org/software/systemd/man/latest/systemd.service.html

It’s pretty odd that a library installed via apt isn’t being picked up with the systemd defaults, though. You should confirm that systemd is using the same Python environment as when you launch the script interactively. Since your script is being used directly in ExecStart and not as an argument to Python, I assume you’ve marked your script as executable. Does your script have a #! line at the top?

1 Like

I have tried a lot of things after reading your answer.
First, I have copied my program to /usr/bin and removed the path in the service and the result is still “mysql not found”.
Then, I have checked the version of python, writing a python program which gives the python version. It is 3.9.2 in both situations.
Do you think it could work better with a newer version of python ? I keep this version as it seems to be the standard for Bullseye and I cannot use Bookworms on the Raspberry, as RealVNC is not compatible.
All my scripts have a #! line at the top and are always defined as executable.
I have created a script that waits for 30 secondes and then lauch the program to leave time to path to be defined, and I have the same result.
Do you think it is possible to copy the “mysql” to /usr/bin or an other directory that systemd could find ?
Many thaks

You do not need to copy anything to /usr/bin just put appropriate settings into the systemd unit file.

I use /usr/local/bin for my personal scripts for example.
But I see no reason you cannot run from /home/pi

You could change the unit file like this to make things explicit:

[Unit]
Description=Lancement de mesures_tensions_msql.py

[Service]
Type=simple
# Set the current directory
WorkingDirectory=/home/pi/python/Solaire
# set PYTHONPATH as needed
Environment=PYTHONPATH=/home/pi/python/Solaire
# Be explicit about which python to use
ExecStart=/usr/bin/python3 /home/pi/python/Solaire/mesures_tension_mysql.py
user=pi
group=pi

[Install]
WantedBy=multi-user.target

I have tried your suggestion but the result is always the same.

Then, I have found this stackoverflow, to import from a defined path :

# some_file.py
import sys
# caution: path[0] is reserved for script path (or '' in REPL)
sys.path.insert(1, '/path/to/application/app/folder')

import file

It could be used if I could know the directory where is mysql. When I make a find -name mysql, I get 16 answers. Which one is the good one ?

Circling back a little. In addition to installing MySQL (MariaSB) like you mentioned here:

Your Python program needs the Python bindings for MySQL, which is what Barry was referring to here:

Did you install a Python MySQL library from apt, or from pip? I ask because if pymysql was installed with pip, it may have been installed into a virtual environment or perhaps more likely, your “user site packages” . In either case when the program is launched from systemd it may not be able to find that library.

When Python is running, sys.path holds a list of the directories it looks in for packages. You can create a small script to compare these paths when running from terminal vs. systemd. You can make a script file in the same location, permissions, owner etc. as your “real” script, but with just this to check the import locations:

import sys 
print(sys.path)

Then make a systemd unit that launches this little test script (type=oneshot should work, I think).

When you compare the output of running this script by hand vs with systemd, there should be a difference in what paths are printed.

1 Like

I have done it : installing pymysql from python3-mysql, but it was installed yet, so it is not the issue.
pi@Raspi2:~ $ python -m pip install mysql-connector-python
Looking in indexes: Simple index, piwheels - Simple index
Requirement already satisfied: mysql-connector-python in ./.local/lib/python3.9/site-packages (8.4.0)
but now, I have the right directory where it should look for mysql and I will declare it in my script.
I will keep youy informed.

I have tried your script !

import sys
print(sys.path)

and it gives a difference concerning /usr/bin/python which appears on command from the terminal and not via systemd.
Then, I tried to add this repertory by :slight_smile :

import sys
print(sys.path)
sys.path.insert(1, ‘/usr/bin/python’)
print(sys.path)

and I can check that the/usr/bin/pyrhon is added when I read the second time, but it cannot find the mysql module.

I think I will use a battery to protect this PC from mains breakdown, as they are quite frequent by there. The purpose was to avoid the battery if the script could be launched after each breakdown, but nevermind, I have to be realist and not fight with issues.

Many thanks for your help.

You are confusing the path used to find programs to run and the path used by python to file python modules.

sys.path tells you where python will look for python modules.
It will never have, or need to have, /usr/bin or /usr/bin/python on it.

Please show us here what you see from that code when you run it from a terminal and from the service.

I have written a little script to edit the python path and the result from python is :
python3 path.py
[‘/home/pi/python/Solaire’, ‘/home/pi/python/Solaire’, ‘/usr/bin/python’, ‘/usr/lib/python39.zip’, ‘/usr/lib/python3.9’, ‘/usr/lib/python3.9/lib-dynload’, ‘/home/pi/.local/lib/python3.9/site-packages’, ‘/usr/local/lib/python3.9/dist-packages’, ‘/usr/lib/python3/dist-packages’, ‘/usr/lib/python3.9/dist-packages’]
and, the same script from systemctl returns :

path.service
     Loaded: loaded (/etc/systemd/system/path.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Thu 2024-05-30 14:41:20 CEST; 5min ago
    Process: 5245 ExecStart=python3 /home/pi/python/Solaire/path.py (code=exited, status=0/SUCCESS)
   Main PID: 5245 (code=exited, status=0/SUCCESS)
        CPU: 271ms

mai 30 14:41:19 Raspi2 systemd[1]: Started path.service.
mai 30 14:41:20 Raspi2 python3[5245]: ['/home/pi/python/Solaire', '/usr/lib/python39.zip', '/usr/lib/python39.zip', '/usr/lib/python3.9', '/usr/lib/python3.9/lib-dynload', '/usr/local/lib/python3.9/dist-packages', '/usr/lib/python3/dist-packages', '/usr/lib/python3.9/dist-packages']
mai 30 14:41:20 Raspi2 systemd[1]: path.service: Succeeded.

You can check for the difference, as told in preceeding message

My instinct is that this one is causing the problem:

This is (I think) the directory pip installs packages into when run with the --user option, like:

pip install --user somepackagename

Perhaps Raspbian is configured to apply this option be default?

I wonder if adding this directory to the PYTHONPATH environment variable in your systemd service would help? Similar to:

Environment=“PYTHONPATH=/home/pi/.local/lib/python3.9/site-packages“

(Don’t copy/paste that, I wrote it on my phone and it has the wrong kind of quotation marks.)

Or add it to sys.path in your script as you have been doing.

Your instinct is right !!!
I have added this path using sys.path and it works. I realise that I had not seen this missing path. You seem to have got it at first glance. Very good.
Thanks for your help. I can know continue on my project with solar power.

I suppose I should mark the subject as solved, but I cannot find where to do that.

Many thanks

I am really sorry to say that I have been too quick !
The systemd init works a step further and I get a new issue.
I I test via systemctl enable, start and status, it works and I can read the values in the msql table.
But whenI boot the PC, the script sends three lines of error in the status :

mesures_tension.service - Lancement de mesures_tensions_msql.py
     Loaded: loaded (/etc/systemd/system/mesures_tension.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Fri 2024-05-31 13:14:36 CEST; 2min 25s ago
    Process: 335 ExecStart=python3 /home/pi/python/Solaire/mesures_tension_mysql.py (code=exited, status=1/FAILURE)
   Main PID: 335 (code=exited, status=1/FAILURE)
        CPU: 1.828s

mai 31 13:14:35 Raspi2 python3[335]:   File "/home/pi/.local/lib/python3.9/site-packages/mysql/connector/abstracts.py", line 1360, in connect
mai 31 13:14:35 Raspi2 python3[335]:     self._open_connection()
mai 31 13:14:35 Raspi2 python3[335]:   File "/home/pi/.local/lib/python3.9/site-packages/mysql/connector/connection.py", line 359, in _open_connection
mai 31 13:14:35 Raspi2 python3[335]:     self._socket.open_connection()
mai 31 13:14:35 Raspi2 python3[335]:   File "/home/pi/.local/lib/python3.9/site-packages/mysql/connector/network.py", line 760, in open_connection
mai 31 13:14:35 Raspi2 python3[335]:     raise InterfaceError(
mai 31 13:14:35 Raspi2 python3[335]: mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)
mai 31 13:14:36 Raspi2 systemd[1]: mesures_tension.service: Main process exited, code=exited, status=1/FAILURE
mai 31 13:14:36 Raspi2 systemd[1]: mesures_tension.service: Failed with result 'exit-code'.
mai 31 13:14:36 Raspi2 systemd[1]: mesures_tension.service: Consumed 1.828s CPU time.

So, the issues are in the scripts imported by mysql. It is not anymore a problem of path …
I cannot read all these lines but I have corrected the first error at line 1360 in python3.9/site-packages/mysql/connector/abstracts.py
by adding the auth_plugin in the connexion :

connection = database.connect(
	user="root",
	password="Rest3662",
	host="127.0.0.1",
	database="Philippe",
	auth_plugin="blablabla")

It allways works when I lauch it via systemctl, ans it bugs when I boot the PC, and now it is on line 380 and I cannot find why.

mesures_tension.service - Lancement de mesures_tensions_msql.py
     Loaded: loaded (/etc/systemd/system/mesures_tension.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Fri 2024-05-31 13:40:05 CEST; 6s ago
    Process: 1249 ExecStart=python3 /home/pi/python/Solaire/mesures_tension_mysql.py (code=exited, status=1/FAILURE)
   Main PID: 1249 (code=exited, status=1/FAILURE)
        CPU: 1.382s

mai 31 13:40:05 Raspi2 python3[1249]:   File "/home/pi/.local/lib/python3.9/site-packages/mysql/connector/protocol.py", line 380, in make_auth
mai 31 13:40:05 Raspi2 python3[1249]:     auth_response, auth_strategy = MySQLProtocol.auth_plugin_first_response(
mai 31 13:40:05 Raspi2 python3[1249]:   File "/home/pi/.local/lib/python3.9/site-packages/mysql/connector/protocol.py", line 265, in auth_plugin_firs>
mai 31 13:40:05 Raspi2 python3[1249]:     auth_strategy = get_auth_plugin(auth_plugin, auth_plugin_class)(
mai 31 13:40:05 Raspi2 python3[1249]:   File "/home/pi/.local/lib/python3.9/site-packages/mysql/connector/plugins/__init__.py", line 160, in get_auth>
mai 31 13:40:05 Raspi2 python3[1249]:     raise NotSupportedError(f"Authentication plugin '{plugin_name}' is not supported")
mai 31 13:40:05 Raspi2 python3[1249]: mysql.connector.errors.NotSupportedError: Authentication plugin 'Rest3662' is not supported
mai 31 13:40:05 Raspi2 systemd[1]: mesures_tension.service: Main process exited, code=exited, status=1/FAILURE
mai 31 13:40:05 Raspi2 systemd[1]: mesures_tension.service: Failed with result 'exit-code'.
mai 31 13:40:05 Raspi2 systemd[1]: mesures_tension.service: Consumed 1.382s CPU time.

Can you find the reason why it works launched from systemctl and not from boot ?
Thanks

I can guess! I think at boot, the MySQL service has not finished starting up at the time when the Python script tries to connect to it.

If that is the problem, you can solve it in the SystemD service file for your Python script by adding a dependency on the MySQL service in the [Unit] section.

[Unit]
Requires=mysql.service
After=mysql.service

This will tell systemd that when starting all services at boot, to not start the Python service until after it starts the MySQL service. (You can read about why to use both “Requires” and “After” here: https://serverfault.com/a/812589).

I have added the requires and after in my script.service and I have found that mysql service cannot start because mariadb is not started. Then, I have tried to start mariadb but it bugs too.

sudo systemctl status mysql
● mysql.service - MariaDB 10.5.23 database server
     Loaded: loaded (/etc/systemd/system/mysql.service; disabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Fri 2024-05-31 17:03:22 CEST; 55min ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 2062 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 2063 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 2065 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environm>
    Process: 2109 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
   Main PID: 2109 (code=exited, status=1/FAILURE)
     Status: "MariaDB server is down"
        CPU: 632ms

mai 31 17:03:22 Raspi2 mariadbd[2109]: 2024-05-31 17:03:22 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
mai 31 17:03:22 Raspi2 mariadbd[2109]: 2024-05-31 17:03:22 0 [Note] InnoDB: Starting shutdown...
mai 31 17:03:22 Raspi2 mariadbd[2109]: 2024-05-31 17:03:22 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
mai 31 17:03:22 Raspi2 mariadbd[2109]: 2024-05-31 17:03:22 0 [Note] Plugin 'FEEDBACK' is disabled.
mai 31 17:03:22 Raspi2 mariadbd[2109]: 2024-05-31 17:03:22 0 [ERROR] Could not open mysql.plugin table: "Unknown storage engine 'Aria'". Some plugins may be not loaded
mai 31 17:03:22 Raspi2 mariadbd[2109]: 2024-05-31 17:03:22 0 [ERROR] Failed to initialize plugins.
mai 31 17:03:22 Raspi2 mariadbd[2109]: 2024-05-31 17:03:22 0 [ERROR] Aborting
mai 31 17:03:22 Raspi2 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
mai 31 17:03:22 Raspi2 systemd[1]: mysql.service: Failed with result 'exit-code'.
mai 31 17:03:22 Raspi2 systemd[1]: Failed to start MariaDB 10.5.23 database server.
They can both start in "normal condition". The error messages from mariadb are not clear for me, but perhaps for you.

My goodness, that is a “deep down” error. :thinking:

On Debian systems when you install MariaDB, it creates aliases from “mysql” to “mariadb”, because MariaDB started as a fork of MySQL and and letting people continue to use “mysql” made it easier for people to migrate from one to the other. Therefore I believe mysql.service and mariadb.service are probably executing the same thing, if they were both added by the MariaDB package.

InnoDB failing is very strange, it is the default storage engine.

By this do you mean if you run this in your terminal:

sudo systemctl start mysql.service

Does it work normally then?

Before creating the Requires and After in your Python service, was MariaDB already configured to start at boot, and was that working?

I don’t know if this is the case, but it’s possible that MariaDB may have written other logs to files other than systemd/journalctl. Perhaps in /var/log. I would check, because finding more messages could be helpful.

For me, normally mode" means :

sudo mysql --user=root --password=blabla
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

and you are right : I ask for mysql and I read Mariadb, but nevermind, it works.

But the mysql.service doesnot work anymore :

sudo systemctl start mysql.service
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

you have the systemctl status in my preceeding mail, and the journalctl -xe tolds a lot of things :

juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [Note] InnoDB: Number of pools: 1
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [Note] InnoDB: Using generic crc32 instructions
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [Note] InnoDB: Using Linux native AIO
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [Note] InnoDB: Completed initialization of buffer pool
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [Note] InnoDB: Starting shutdown...
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [Note] Plugin 'FEEDBACK' is disabled.
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [ERROR] Could not open mysql.plugin table: "Unknown storage engine 'Aria'". Some plugins may be not loaded
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [ERROR] Failed to initialize plugins.
juin 01 14:39:33 Raspi2 mariadbd[5883]: 2024-06-01 14:39:33 0 [ERROR] Aborting
juin 01 14:39:33 Raspi2 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░ 
░░ An ExecStart= process belonging to unit mysql.service has exited.
░░ 
░░ The process' exit code is 'exited' and its exit status is 1.
juin 01 14:39:33 Raspi2 systemd[1]: mysql.service: Failed with result 'exit-code'.
░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░ 
░░ The unit mysql.service has entered the 'failed' state with result 'exit-code'.
juin 01 14:39:33 Raspi2 systemd[1]: Failed to start MariaDB 10.5.23 database server.
░░ Subject: L'unité (unit) mysql.service a échoué
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░ 
░░ L'unité (unit) mysql.service a échoué, avec le résultat failed.
juin 01 14:39:33 Raspi2 sudo[5808]: pam_unix(sudo:session): session closed for user root
~

So, Mariadb cannot start in this way.
I don’t understand everything in thes logs and I se that mysql try to start with “NO PASSWORD” while I have given one by deleting the initial user root with unknown password and have created an new user root with a known password.
Actually, I am an old programmer. I have begun with FORTRAN H, then basica and so on for years to finish with html, javascript, php and python. I am quite lost with the new way programming with a lot of dependancies which are not clear for me. I have never learned programming in school.
Thanks for your help