cursor() raise errors.OperationalError("MySQL Connection not available.") OperationalError: MySQL Connection not available

Question

import requests
import time
import csv
import ast
import sys
import mysql.connector

config = {
'user': 'root',
'password': 'password',
'host': '127.0.0.1',
'port': '3306',
'database': 'dbname',
'raise_on_warnings': True,}

cnx = mysql.connector.connect(config)    
cursor = cnx.cursor()

Running gives:

Traceback (most recent call last):
  File "/home/ubuntu/scrapers/xrp2.py", line 17, in <module>
    cursor = cnx.cursor()
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 1383, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
OperationalError: MySQL Connection not available.

Does anyone know how to fix this? Other forums have had similar errors and fixed the problem by not having too many cursors open, but this is the first call to cursor(), so I'm not sure why it's unavailable. Do I need to close MySQL from the Ubuntu terminal?

My config file works fine connecting via Sequel Pro's SSH.

SOLVED: Put the configuration into the .connect(statement) instead of as a dictionary.

import requests
import mysql.connector

cnx = mysql.connector.connect(user ='root', password= 'p', host = '127.0.0.1',port='3306', database='coindb')

cursor = cnx.cursor()

Tong

Are you sure you your config has the right values? – Lachellelaches

To follow up on the comment from @kartikg3, have you verified that you have the right hostname, port, username, password, etc.? – Foresail

Usually, you solve your own questions by creating an answer and marking it as solved. – Arrhythmia

I am somehow still getting the error as raised here, #76366100 any clue what might be wrong? – Splitting

II have tried with and without the with statements, same result – Splitting

Answer 1

This error will happen if your connection is already closed. In a Try-Except-Else block, it turns out Else is always executed if there is no error caught by the Except.

Therefore, this code was closing my connection immediately:

def mysql_get_mydb():
    '''Takes no args, and returns a connection to MYDB via MYSQL.'''
    
    creds = fixed_data.MYSQL_ENDPOINTS

    try:
        cnx = connector.connect(user='MYDB',
                              password='open_sesame',
                              host=creds['prod']['MYDB'][0],
                                port=3306,
                              database='MYDB')
    except connector.Error as err:
        if err.errno == connector.errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)
    # the else will happen if there was no error!
    else:
        cnx.close()

    return cnx

When I tried doing z = mysql_get_mydb() and y = z.cursor() an error is raised by y = z.cursor(). This is the exact error you've listed. You can also test this by opening a connection, closing it, then trying to define a cursor on it. Hopefully, this comment helps someone. The fix here is that the last else should contain return cnx (and the cnx.close() should be removed)

Jocasta

I guess everyone just copy/pastes the examples on mysql connector tutorial, without putting much though about its logic and just assumes the error raised, is the print(err), not a connection closed, but trying to use cursor. It's dumb but everyone does it. Zombie programers ;p – Stubble

Answer 2

Try adding this line: cnx.reconnect() before you use the cursor.

Celt

Yep yep! That solved my problem. – Ahn

that's actually a hack solution im not proud of. A cleaner way way is to use with block for connection variable – Celt

@Celt but what if a same connection is object being used across methods and process? with will not work then – Scrivener

@Celt I tried this, the reconnect many 20+ connections for me, why is that? – Scrivener

@Celt I am already using with block but running within a loop, after a certain period of time it gives the error – Scrivener

@Scrivener try putting the loop inside with block – Celt

@Vasily802, I am already using a with block and I am still losing the connection. – Blaspheme

Answer 3

A more elegant solution than yours:

import requests
import time
import csv
import ast
import sys
import mysql.connector

config = {
'user': 'root',
'password': 'password',
'host': '127.0.0.1',
'port': '3306',
'database': 'dbname',
'raise_on_warnings': True,}

cnx = mysql.connector.connect(**config)    
cursor = cnx.cursor()

Arrhythmia

Answer 4

This error will happen if your connection is already closed, maybe after TIMEOUT in MySQL Server is finished. (default timeout is 28,​800 seconds ~= 8 hours)

So, another solution is increase the timeout connection in MySQL Server with this command:

SET GLOBAL wait_timeout = <seconds>;

Hope it's helpful!

Actinolite

after the server restart that value will be changed to default, it would be better to use my.cnf / ini; Windows use ini file and Linux use cnf file, file to change the value in seconds ` wait_timeout = <seconds>` . this value then will be persisted over default all the time. – Kopans

Answer 5

Alluding to to Vasily's comment, here's the code using a 'with' statement:

config = {
'user': 'root',
'password': 'password',
...
}

with mysql.connector.connect(**config) as cnx:
    cursor = cnx.cursor()
    ...

The 'with' statement creates a new connection for each request. Once this block of code is executed, the connection is automatically closed. For new requests, a fresh connection is used, such that the closure of previous connection doesn't affect the requests that come after it.

Do note that this could result in slower performance, since a new connection is being created for every new request.

Dissuade

Answer 6

dont make seprate dictionary as "config" just put it inside the cnx statement(it will work for the python 3):

cnx = mysql.connector.connect('user': 'root',
'password': 'password',
'host': '127.0.0.1',
'port': '3306',
'database': 'dbname',
'raise_on_warnings': True) 

Underlie