Python MySQLdb.OperationalError() Examples

def buildDatabase( databaseName ):

   username = raw_input( "Enter MySQL user name: " )
   password = getpass.getpass( "Enter user password: " )

   print "Creating database %s:" % databaseName

   # retrieve database description from file
   print "\tRetrieving database definition:",

   try:
      databaseDefinition = retrieveDatabaseDefinition( databaseName )
   except TypeError:
      sys.exit( "ERROR\nThe database definition in %s.def is invalid" % databaseName )
   else:
      print "DONE"

   # get a cursor for MySQL
   print "\tConnecting to MySQL:",
   try:
      cursor = MySQLdb.connect( user = username, passwd = password ).cursor()
   except MySQLdb.OperationalError, error:
      sys.exit( "ERROR\nCould not connect to MySQL (%s)" % error ) 

def execute_with_reconnect(self, sql: str, args: Optional[List[ValidSqlArgumentDescription]] = None, fetch_rows: Optional[bool] = False) -> Tuple[int, List[ValidSqlArgumentDescription]]:
        result = None
        # Attempt to execute the query and reconnect 3 times, then give up
        for _ in range(3):
            try:
                p = perf.start()
                n = self.cursor.execute(sql, args)
                perf.check(p, 'slow_query', (f'```{sql}```', f'```{args}```'), 'mysql')
                if fetch_rows:
                    rows = self.cursor.fetchall()
                    result = (n, rows)
                else:
                    result = (n, [])
                break
            except OperationalError as e:
                if 'MySQL server has gone away' in str(e):
                    print('MySQL server has gone away: trying to reconnect')
                    self.connect()
                else:
                    # raise any other exception
                    raise e
        else:
            # all attempts failed
            raise DatabaseException('Failed to execute `{sql}` with `{args}`. MySQL has gone away and it was not possible to reconnect in 3 attemps'.format(sql=sql, args=args))
        return result 

def setup_audit_plugin(instance):
    """ Install the audit log plugin.  Similarly to semisync, we may
        or may not use it, but we need to make sure that it's available.
        Audit plugin is available on 5.5 and above, which are all the
        versions we support.

        Args:
        instance - A hostaddr object
    """
    return 
    # this is intentional; we may re-enable the audit log at some 
    # later date, but for now, we just exit.

    conn = connect_mysql(instance)
    cursor = conn.cursor()

    try:
        cursor.execute("INSTALL PLUGIN audit_log SONAME 'audit_log.so'")
    except MySQLdb.OperationalError as detail:
        (error_code, msg) = detail.args
        # plugin already loaded, nothing to do.
        if error_code != MYSQL_ERROR_FUNCTION_EXISTS:
            raise
    conn.close() 

def query(self, sql, use_dict=True, retry=0):

        if retry < 0:
            retry = 0

        retry = int(retry)

        # the first attempt does not count as 'retry'
        for i in range(retry + 1):

            try:
                with self() as conn:
                    return conn_query(conn, sql, use_dict=use_dict)

            except MySQLdb.OperationalError as e:
                if len(e.args) > 0 and e[0] in retriable_err:
                    logger.info(
                        repr(e) + " conn_query error {sql}".format(sql=sql))
                    continue
                else:
                    raise
        else:
            raise 

def _purge_bitmaps_to(lsn):
    _say("Purging bitmap files to LSN: %s" % lsn)

    if not db_connect():
        _error("Failed to connect to server, unable to purge bitmaps.")
        _exit_code(XB_EXIT_BITMAP_PURGE_FAIL)
        return False

    try:
        cur = xb_mysqldb.cursor(MySQLdb.cursors.DictCursor)
        cur.execute("PURGE CHANGED_PAGE_BITMAPS BEFORE %s" % lsn)
    except MySQLdb.OperationalError, e:
        _error("Got MySQL error %d, \"%s\" at execute" % (e.args[0], e.args[1]))
        _error("Failed to purge bitmaps!")
        _exit_code(XB_EXIT_BITMAP_PURGE_FAIL)
        return False 

def job_runner(f):
    """Accepts any callable function and runs it

    Catches any exceptions and logs to Rollbar
    """
    result = None
    try:
        ensure_mysql_connection_usable()
        result = f()
    except MySQLdb.OperationalError as e:
        extra_data = {
            'caught_exception' : True,
            'attempt_reconnect' : True,
        }
        rollbar.report_exc_info(extra_data=extra_data)
        attempt_mysql_reconnect()
    except:
        rollbar.report_exc_info()
    finally:
        close_connection()
    return result 

def addAddress( self ):
      """Add address record to database"""

      if self.entries[ "LAST_NAME" ].get() != "" and \
         self.entries[ "FIRST_NAME"].get() != "":

         # create INSERT query command
         query = """INSERT INTO addresses (  
                 FIRST_NAME, LAST_NAME, ADDRESS, CITY,
                 STATE_PROVINCE, POSTAL_CODE, COUNTRY,
                 EMAIL_ADDRESS, HOME_PHONE, FAX_NUMBER
                 ) VALUES (""" + \
                 "'%s', " * 10 % \
                 ( self.entries[ "FIRST_NAME" ].get(),
                   self.entries[ "LAST_NAME" ].get(),
                   self.entries[ "ADDRESS" ].get(),
                   self.entries[ "CITY" ].get(),
                   self.entries[ "STATE_PROVINCE" ].get(),
                   self.entries[ "POSTAL_CODE" ].get(),
                   self.entries[ "COUNTRY" ].get(),
                   self.entries[ "EMAIL_ADDRESS" ].get(),
                   self.entries[ "HOME_PHONE" ].get(),
                   self.entries[ "FAX_NUMBER" ].get() )
         query = query[ :-2 ] + ")"

         # open connection, retrieve cursor and execute query
         try:      
            connection = MySQLdb.connect( db = "AddressBook" )
            cursor = connection.cursor()
            cursor.execute( query ) 
         except MySQLdb.OperationalError, message:
            errorMessage = "Error %d:\n%s" % \
               ( message[ 0 ], message[ 1 ] )
            showerror( "Error", errorMessage )
         else:
            cursor.close()
            connection.close()
	    self.clearContents() 

def findAddress( self ):
      """Query database for address record and display results"""

      if self.entries[ "LAST_NAME" ].get() != "":

         # create SELECT query
         query = "SELECT * FROM addresses " + \
                 "WHERE LAST_NAME = '" + \
                 self.entries[ "LAST_NAME" ].get() + "'"

         # open connection, retrieve cursor and execute query
         try:      
            connection = MySQLdb.connect( db = "AddressBook" )
            cursor = connection.cursor()
            cursor.execute( query )
         except MySQLdb.OperationalError, message:
            errorMessage = "Error %d:\n%s" % \
               ( message[ 0 ], message[ 1 ] )
            showerror( "Error", errorMessage )
            self.clearContents()
         else:   # process results
            results = cursor.fetchall()
            fields = cursor.description

            if not results:   # no results for this person
               showinfo( "Not found", "Nonexistent record" )
            else:             # display person's info. in GUI
               self.clearContents()

               # display results
               for i in range( len( fields ) ):

                  if fields[ i ][ 0 ] == "ID":
                     self.IDEntry.set( str( results[ 0 ][ i ] ) )
                  else:
                     self.entries[ fields[ i ][ 0 ] ].insert( 
                        INSERT, str( results[ 0 ][ i ] ) )
            
            cursor.close()
            connection.close() 

def updateAddress( self ):
      """Update address record in database"""

      if self.entries[ "ID" ].get() != "":

         # create UPDATE query command
         entryItems = self.entries.items()
         query = "UPDATE addresses SET"

         for key, value in entryItems:
            
            if key != "ID":
               query += " %s='%s'," % ( key, value.get() )

         query = query[ :-1 ] + " WHERE ID=" + self.IDEntry.get()

         # open connection, retrieve cursor and execute query
         try:      
            connection = MySQLdb.connect( db = "AddressBook" )
            cursor = connection.cursor()
            cursor.execute( query ) 
         except MySQLdb.OperationalError, message:
            errorMessage = "Error %d:\n%s" % \
               ( message[ 0 ], message[ 1 ] )
            showerror( "Error", errorMessage )
            self.clearContents()
         else:
            showinfo( "database updated", "Database Updated." )
            cursor.close()
            connection.close() 

def submitQuery( self ):
      """Execute user-entered query agains database"""

      # open connection, retrieve cursor and execute query
      try:      
         connection = MySQLdb.connect( db = "Books" )
         cursor = connection.cursor()
         cursor.execute( self.query.get() )
      except MySQLdb.OperationalError, message:
         errorMessage = "Error %d:\n%s" % \
            ( message[ 0 ], message[ 1 ] )
         showerror( "Error", errorMessage )
         return 

def process_exception(self, request, exception):
        import traceback
        exc_info = sys.exc_info()
        try:
            format = traceback.format_exc(exc_info[2])
        except:
            format = ''
        message = str(exception)
        if (isinstance(exception, IOError) and '_verify(ticket, service)' in format
            and ('Connection reset by peer' in message
                 or 'Name or service not known' in message
                 or 'Connection timed out' in message
                 or 'EOF occurred in violation of protocol' in message)):
            # CAS verification timeout
            return HttpError(request, status=500, title="CAS Error", error="Could not contact the CAS server to verify your credentials. Please try logging in again.")
        elif isinstance(exception, AssertionError) and "Django CAS middleware requires authentication middleware" in format:
            # CAS choke
            return HttpError(request, status=500, title="CAS Error", error="Could not contact the CAS server to verify your credentials. Please try logging in again.")
        elif isinstance(exception, EOFError) and "return request.POST.get('csrfmiddlewaretoken', '')" in format:
            # file upload EOF
            return HttpError(request, status=500, title="Upload Error", error="Upload seems to have not completed properly.")
        elif OperationalError is not None and isinstance(exception, OperationalError) and "Lost connection to MySQL server at 'reading initial communication packet'" in format:
            # lost main DB
            return HttpError(request, status=500, title="Database Error", error="Unable to connect to database.")
        elif OperationalError is not None and isinstance(exception, OperationalError) and "MySQL server has gone away" in format:
            # lost main DB
            return HttpError(request, status=500, title="Database Error", error="Unable to connect to database.")
        elif isinstance(exception, AssertionError) and "The Django CAS middleware requires authentication middleware" in format:
            # wacky authentication thing that means the database is missing, or something
            return HttpError(request, status=500, title="Database Error", error="Unable to connect to database.") 

def setup_semisync_plugins(instance):
    """ Install the semi-sync replication plugins.  We may or may
        not actually use them on any given replica set, but this
        ensures that we've got them.  Semi-sync exists on all versions
        of MySQL that we might support, but we'll never use it on 5.5.

        Args:
        instance - A hostaddr object
    """
    conn = connect_mysql(instance)
    cursor = conn.cursor()

    version = get_global_variables(instance)['version']
    if version[0:3] == '5.5':
        return

    try:
        cursor.execute("INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'")
    except MySQLdb.OperationalError as detail:
        (error_code, msg) = detail.args
        if error_code != MYSQL_ERROR_FUNCTION_EXISTS:
            raise
        # already loaded, no work to do

    try:
        cursor.execute("INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'")
    except MySQLdb.OperationalError as detail:
        (error_code, msg) = detail.args
        if error_code != MYSQL_ERROR_FUNCTION_EXISTS:
            raise 

def query(self, sql):
        """ Executes the given SQL statement and returns a sequence of rows. """
        assert self.cursor, "%s already closed?" % (self,)
        try:
            self.cursor.execute(sql)
        except MySQLdb.OperationalError, (errcode, msg):
            if errcode != 2006:  # "MySQL server has gone away"
                raise
            self._reconnect() 

def execute(self, query, args=None):
        try:
            # args is None means no string interpolation
            return self.cursor.execute(query, args)
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
            raise 

def executemany(self, query, args):
        try:
            return self.cursor.executemany(query, args)
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
            raise 

def execute(self, query, args=None):
        try:
            # args is None means no string interpolation
            return self.cursor.execute(query, args)
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                raise utils.IntegrityError(*tuple(e.args))
            raise 

def executemany(self, query, args):
        try:
            return self.cursor.executemany(query, args)
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                raise utils.IntegrityError(*tuple(e.args))
            raise 

def test_query_retry(self):

        pool = self.pool

        sql = (
            'set session wait_timeout=1;'
        )
        pool.query(sql)
        pool.query('show variables like "%timeout%";')

        with pool() as conn:
            time.sleep(2)
            with self.assertRaises(MySQLdb.OperationalError):
                print conn.query('show databases')

        # no error raise from above, thus a timed out conn has been left in
        # pool
        stat = pool('stat')
        dd('stat after timeout', stat)
        self.assertEqual(1, stat['create'], 'created 1 conn')

        # use previous conn, timed out and retry.
        pool.query('show databases', retry=1)

        stat = pool('stat')
        dd('stat after retry', stat)
        self.assertEqual(2, stat['create'], 'created another conn for retry') 

def test_affected_rows(self):
        self.assertEqual(
            self.conn.affected_rows(), 0, "Should return 0 before we do anything."
        )

    # def test_debug(self):
    # (FIXME) Only actually tests if you lack SUPER
    # self.assertRaises(MySQLdb.OperationalError,
    # self.conn.dump_debug_info) 

def do_pip(database, table):
    """Handle the parsing of the point in polygon request and return a polygon.

    This routes all the /pip/... requests to the handler.
    See http://flask.pocoo.org/docs/0.10/api/#flask.Flask.route

    Args:
      database: The name of the database to use, this is picked from the URL.
      table: The database table to query from, this is picked from the URL.
    Returns:
      A flask.Response object with the GeoJSON to be returned, or an error JSON.
    """
    lat = float(flask.request.args.get('lat', default=0.0))
    lng = float(flask.request.args.get('lng', default=0.0))
    select = flask.request.args.get('select', default='')
    try:
        pip = PointInPolygon(_INSTANCE, database, table)
    except MySQLdb.OperationalError as e:
        error = {'error': 'Database Error %s' % str(e)}
    return flask.Response(
            response=json.dumps(error),
            mimetype='application/json',
            status=500)

    polygon = pip.pip(lat, lng, select)
    if 'error' in polygon:
        return flask.Response(
            response=json.dumps(polygon),
            mimetype='application/json',
            status=500)
    else:
        return flask.Response(
            response=geojson.dumps(polygon, sort_keys=True),
            mimetype='application/json',
            status=200) 

def insert(self, query, args=None):
        server = self.connect_dbserver()
        cursor = server.cursor()

        try:
            cursor.execute(query, args)
            server.commit()
        except MySQLdb.OperationalError, e:
            self.sqlerror(e)
            self.insert(query, args) 

def select(self, query, args=None):
        results = None
        server = self.connect_dbserver()
        cursor = server.cursor(MySQLdb.cursors.DictCursor)

        try:
            cursor.execute(query, args)
            results = cursor.fetchall()
        except MySQLdb.OperationalError, e:
            self.sqlerror(e)
            results = self.select(query, args) 

def execute(self, query, args=None):
        try:
            # args is None means no string interpolation
            return self.cursor.execute(query, args)
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                raise utils.IntegrityError(*tuple(e.args))
            raise 

def executemany(self, query, args):
        try:
            return self.cursor.executemany(query, args)
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                raise utils.IntegrityError(*tuple(e.args))
            raise 

def execute(self, query, args=None):
        try:
            # args is None means no string interpolation
            return self.cursor.execute(query, args)
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
            raise 

def executemany(self, query, args):
        try:
            return self.cursor.executemany(query, args)
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
            raise 

def execute(self, query, args=None):
        try:
            return self.cursor.execute(query, args)
        except Database.IntegrityError as e:
            six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
            six.reraise(utils.DatabaseError, utils.DatabaseError(*tuple(e.args)), sys.exc_info()[2])
        except Database.DatabaseError as e:
            six.reraise(utils.DatabaseError, utils.DatabaseError(*tuple(e.args)), sys.exc_info()[2]) 

def executemany(self, query, args):
        try:
            return self.cursor.executemany(query, args)
        except Database.IntegrityError as e:
            six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
            six.reraise(utils.DatabaseError, utils.DatabaseError(*tuple(e.args)), sys.exc_info()[2])
        except Database.DatabaseError as e:
            six.reraise(utils.DatabaseError, utils.DatabaseError(*tuple(e.args)), sys.exc_info()[2]) 

def execute(self, query, args=None):
        try:
            # args is None means no string interpolation
            return self.cursor.execute(query, args)
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
            raise 

def executemany(self, query, args):
        try:
            return self.cursor.executemany(query, args)
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
            raise