0

I have a script that connects to a MySQL DB via pyMySQL.

It works like a charm when I execute it manually from the console, but gives this output when I run this cronjob:

@reboot sudo python3 /var/www/html/ls/src/AppBundle/Command/crawl.py true > /tmp/listener.log 2>&1

Result:

Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 890, in connect
(self.host, self.port), self.connect_timeout)
 File "/usr/lib/python3.5/socket.py", line 711, in create_connection
raise err
File "/usr/lib/python3.5/socket.py", line 702, in create_connection
sock.connect(sa)
ConnectionRefusedError: [Errno 111] Connection refused

Why is that? I followed all of this https://stackoverflow.com/a/15684341/1092632 w/out any success.

Any hint appreciated!

Edit

Things I tried:

connect = pymysql.connect(host=constants.HOST,
                          user=constants.USERNAME,
                          passwd=constants.PASSWORD,
                          db=constants.DATABASE,
                          charset='utf8mb4',
                          port=constants.PORT,
                          unix_socket=constants.SOCKET,
                          cursorclass=pymysql.cursors.DictCursor)

constants.py

# MySQL #
HOST = '127.0.0.1' // Localhost, 127.0.0.1 and public IP of Server (having bind to 0.0.0.0
USERNAME = 'admin'
PASSWORD = 'XXX'
DATABASE = 'ls_base'
PORT = '3306' // With and without ''
SOCKET = '/var/run/mysqld/mysqld.sock' // File exists
Community
  • 1
  • 1
PrimuS
  • 2,505
  • 6
  • 33
  • 66
  • I'd guess your client is trying to connect to the wrong socket or port, see http://stackoverflow.com/questions/6885164/pymysql-cant-connect-to-mysql-on-localhost – Bill Karwin Jan 23 '17 at 23:15
  • Neither adding `unix_socket='/var/run/mysqld/mysqld.sock',` (file exists) nor specifically adding the right port seems to do the trick. The socket changes the Error to `[Errno 2] No such file or directory)` though. – PrimuS Jan 23 '17 at 23:31
  • What hostname are you using to connect? Using "localhost" will invoke the socket file connection, using "127.0.0.1" will invoke a network connection. – Bill Karwin Jan 24 '17 at 00:04

2 Answers2

1

This problem could be either caused by 1. server configuration or by your 2. python code.

for point 2, to be sure to exclude your code from the error, try this:

import MySQLdb
def dbconnect():
    try:
        db = MySQLdb.connect(
            host='localhost',
            user='root',
            passwd='XXX',
            db='myDB'
        )
    except Exception as e:
        sys.exit(e)
    return db
print dbconnect()

this code runs whit cron on a STOCK, RHEL server:

* * * * * root /var/www/html/myApp/stopClock/stopClock.py

If this does not work, and you get the same error, the problem is your server cron config:

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/var/www/html/myApp

This is what I use. and of course the Shebang in my first line of code:

#!/usr/bin/env python
Roy Holzem
  • 860
  • 13
  • 25
  • It does work, says `<_mysql.connection open to 'localhost' at 2b4b438>`. Currently I use a daemon that starts the script upon login, which suprisingly works but is actually not really what I want... So the conclusion is that it should be sth with the code, but why does it work when called manually though? – PrimuS Jan 24 '17 at 11:24
  • Nice so you are one step closer to resolution, can you pastebin your code, especially the start, and the part where you connect to MySQL? – Roy Holzem Jan 25 '17 at 07:24
  • Also check your var/log/messages for errors on the modules you launch with cron for something like : `Jan 23 09:47:07 AppRoot abrt: detected unhandled Python exception in '/var/www/html/myApp/cleaner.py'` – Roy Holzem Jan 25 '17 at 07:26
1

I too had errno 111 when my python3 code connected to my localhost database from a cron @reboot execution. The python3 code would run fine when executing from shell. After much debugging I found out that the database connection was being made too soon. I added this to my code:

import subprocess

while subprocess.Popen('service mysqld status', shell=True, stdout=subprocess.PIPE).stdout.read().decode('utf-8').find('Active: active') == -1:
     sleep(1)
#once mysql is ready it will break out of while loop and continue to
connection = pymysql.connect(host='localhost'............the rest of your code

Hope this helps.

helper object
  • 11
  • 1