Python MySQLdb.OperationalError() Examples

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 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 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 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 _connect(self, db_username, db_password, db_name, connection_type,
      db_host = None, db_port = None, db_ssl_cert_path = None,
      db_unix_socket = None, ssh_host = None, ssh_port = None,
      ssh_username = None, ssh_key = None, **kwargs):
    # pylint: disable = R0913, R0914, W0613
    # R0913: Unfortunately, this function needs this many arguments
    # R0914: Need local vars due to arguments
    # W0613: Need **kwargs because dictionary is passed indiscriminately in
    import MySQLdb
    """
    Helper function for connecting to a database driver.
    """
    connArgs = { 'user':   db_username
               , 'passwd': db_password
               , 'db':     db_name }
    if connection_type == 'direct':
      connArgs.update({ 'host': db_host
                      , 'port': db_port })

      if db_ssl_cert_path:
        connArgs['ssl'] = { 'ca': db_ssl_cert_path }
    elif connection_type == 'ssh':
      connArgs.update({ 'host':        'localhost'
                      , 'unix_socket': createSshUnixSocket( db_unix_socket
                                                          , ssh_username
                                                          , ssh_host
                                                          , ssh_port
                                                          , ssh_key )})
    else:
      raise ValueError( "sql.connection.SqlConn._connect"
                      , "No connection method for data source" )
    try:
      db = MySQLdb.connect(**connArgs)
      db.autocommit(True)
      return db
    except MySQLdb.OperationalError as err:
      raise DsConnError('OperationalError: {msg}'.format(msg = err.args[1])) 

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 

def fetch_mysql_master_stats(conn):
	try:
		result = mysql_query(conn, 'SHOW BINARY LOGS')
	except MySQLdb.OperationalError:
		return {}

	stats = {
		'binary_log_space': 0,
	}

	for row in result.fetchall():
		if 'File_size' in row and row['File_size'] > 0:
			stats['binary_log_space'] += int(row['File_size'])

	return stats 

def fetch_mysql_response_times(conn):
	response_times = {}
	try:
		result = mysql_query(conn, """
			SELECT *
			FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME
			WHERE `time` != 'TOO LONG'
			ORDER BY `time`
		""")
	except MySQLdb.OperationalError:
		return {}

	for i in range(1, 14):
		row = result.fetchone()

		# fill in missing rows with zeros
		if not row:
			row = { 'count': 0, 'total': 0 }

		row = {key.lower(): val for key, val in row.items()}

		response_times[i] = {
			'time':  float(row['time']),
			'count': int(row['count']),
			'total': round(float(row['total']) * 1000000, 0),
		}

	return response_times 

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 testdb(request):
    if 'MySQLdb' not in sys.modules:
        pytest.skip("requires MySQLdb to run")
        return None
    dbuser,dbpwd = request.config.getoption("--dbuser").split(':')
    try:
        sql = db.connect('localhost',dbuser,dbpwd,'')
    except db.OperationalError:
        pytest.skip("requires mysql running + admin user/pwd to run")
        return None
    cur = sql.cursor()
    cur.execute("set sql_notes=0;")
    cur.execute("show databases like 'unittest';")
    if cur.rowcount > 0:
        print "\nClearing test db"
        cur.execute("drop database unittest;")
    sqlsrc = open('/usr/local/share/sqlchain/docs/sqlchain.sql').read()
    sqlcode = ''
    for k,v in [('{dbeng}','Memory'),('{dbname}','unittest'),('{dbpwd}',dbpwd),('{dbuser}',dbuser)]:
        sqlsrc = sqlsrc.replace(k, v)
    for line in sqlsrc.splitlines():
        if line != '' and line[:2] != '--':
            sqlcode += line
    for stmnt in sqlcode.split(';'):
        if stmnt:
            cur.execute(stmnt)
    return cur 

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 _connect(self, db_host, db_port, db_username, db_password, db_name, **kwargs):
    """
    Overrides _connect method in SqlConn for the simpler connection offered by
    the PostgreSQL adapter.

    Args:
      db_host: String for data base host.
      db_port: String for data base port.
      db_username: String for data base user.
      db_password: String for data base password corresponding to given user.
      db_name: String for table schema that is to be used.

    Returns:
      If the connection is successful, the active database connection is returned.

    Raises:
      DsConnError: Thrown in case of an error during connection.
    """
    connArgs = { 'host':     db_host
               , 'port':     int(db_port)
               , 'user':     db_username
               , 'password': db_password
               , 'dbname':   db_name
               }
    try:
      import psycopg2
      db            = psycopg2.connect(**connArgs)
      db.autocommit = True
      return db
    except psycopg2.OperationalError as err:
      raise DsConnError('OperationalError: ' + str(err.message))


  ### Convenience functions for common queries 

def _connect(self, db_username, db_password, db_name, connection_type,
      db_host = None, db_port = None, db_ssl_cert_path = None,
      db_unix_socket = None, ssh_host = None, ssh_port = None,
      ssh_username = None, ssh_key = None, **kwargs):
    # pylint: disable = R0913, R0914, W0613
    # R0913: Unfortunately, this function needs this many arguments
    # R0914: Need local vars due to arguments
    # W0613: Need **kwargs because dictionary is passed indiscriminately in
    import MySQLdb
    """
    Helper function for connecting to a database driver.
    """
    connArgs = { 'user':   db_username
               , 'passwd': db_password
               , 'db':     db_name }
    if connection_type == 'direct':
      connArgs.update({ 'host': db_host
                      , 'port': db_port })

      if db_ssl_cert_path:
        connArgs['ssl'] = { 'ca': db_ssl_cert_path }
    elif connection_type == 'ssh':
      connArgs.update({ 'host':        'localhost'
                      , 'unix_socket': createSshUnixSocket( db_unix_socket
                                                          , ssh_username
                                                          , ssh_host
                                                          , ssh_port
                                                          , ssh_key )})
    else:
      raise ValueError( "sql.connection.SqlConn._connect"
                      , "No connection method for data source" )
    try:
      db = MySQLdb.connect(**connArgs)
      db.autocommit(True)
      return db
    except MySQLdb.OperationalError as err:
      raise DsConnError('OperationalError: {msg}'.format(msg = err.args[1])) 

def sqlFunc(func):
    def wrapper(*args, **kwargs):
        try:
            return func(*args, **kwargs)
        except OperationalError as e:
            if 'MySQL server has gone away' in str(e):
                sql_operator.connect()
                return func(*args, **kwargs)
            print e
    return wrapper 

def execute(query, params=None):
        # type: (str, Sequence[Any]) -> Sequence[Sequence[Any]]
        '''
        Executes a given SQL query with some possible params.

        Args:
            query (str): The query to perform.
            params (Tuple[str]): Optional parameters to pass to the query.
        Returns:
            Tuple[Tuple[str]]: The output from the SQL query.
        '''
        if params is None:
            params = ()
        try:
            SQLEngine._cursor.execute(query, params)
            rows = SQLEngine._cursor.fetchall()
            SQLEngine._conn.commit()
        except (AttributeError, MySQLdb.OperationalError):
            # Recover from lost connection
            logging.warn('Recovering from lost MySQL connection.')
            SQLEngine._create_engine(SQLEngine._host,
                                     SQLEngine._user,
                                     SQLEngine._passwd,
                                     SQLEngine._db)
            return SQLEngine.execute(query, params)
        except MySQLdb.Error as e:
            try:
                raise SQLEngineException('MySQL error [{0}]: {1}'.format(e.args[0], e.args[1]))
            except IndexError:
                raise SQLEngineException('MySQL error: {0}'.format(e))
        return rows 

def query(self, db_name=None, sql='', limit_num=0, close_conn=True, **kwargs):
        """返回 ResultSet """
        result_set = ResultSet(full_sql=sql)
        max_execution_time = kwargs.get('max_execution_time', 0)
        cursorclass = kwargs.get('cursorclass') or MySQLdb.cursors.Cursor
        try:
            conn = self.get_connection(db_name=db_name)
            conn.autocommit(True)
            cursor = conn.cursor(cursorclass)
            try:
                cursor.execute(f"set session max_execution_time={max_execution_time};")
            except MySQLdb.OperationalError:
                pass
            effect_row = cursor.execute(sql)
            if int(limit_num) > 0:
                rows = cursor.fetchmany(size=int(limit_num))
            else:
                rows = cursor.fetchall()
            fields = cursor.description

            result_set.column_list = [i[0] for i in fields] if fields else []
            result_set.rows = rows
            result_set.affected_rows = effect_row
        except Exception as e:
            logger.warning(f"MySQL语句执行报错，语句：{sql}，错误信息{traceback.format_exc()}")
            result_set.error = str(e)
        finally:
            if close_conn:
                self.close()
        return result_set 

def connect(self, reconnect = False):
		from config import config
		try:
			self.conn = MySQLdb.connect(host=config['db_host'], user=config['db_username'], passwd=config['db_password'], db=config['db_name'])
			self.conn.autocommit(True)
		except (AttributeError, MySQLdb.OperationalError) as e:
			if not reconnect:
				raise
			# else ignore and return 

def query(self, q, p = []):
		with self.db_lock:
			try:
				cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
				cursor.execute(q, p)
				self.conn.commit()
				return cursor
			except (AttributeError, MySQLdb.OperationalError) as e:
				import time
				print 'database: encountered error: ' + str(e) + "; reconnecting in five seconds..."
				time.sleep(5)
				print 'database: reconnecting'
				self.connect()
				return self.query(q, p) 

def main():
    """ Trojan Manager main function
    This function can only be executed when
    this file is not being imported.
    """
    # Create database controller connection
    try:
        trojan_db = TrojanDatabase('127.0.0.1', 'trojan', 'thisisthetrojandbpassword', 'trojan', 'users')
    except (MySQLdb.OperationalError) as e:
        Avalon.error('Error establishing connection to MySQL/MariaDB')
        Avalon.error('Please check your settings')
        traceback.print_exc()
        exit(1)

    # Begin command interpreting
    try:
        if sys.argv[1].lower() == 'interactive' or sys.argv[1].lower() == 'int':
            print_legal_info()
            # Set command completer
            completer = ShellCompleter(COMMANDS)
            readline.set_completer(completer.complete)
            readline.parse_and_bind('tab: complete')
            # Launch interactive trojan shell
            prompt = '{}[trojan]> {}'.format(Avalon.FM.BD, Avalon.FM.RST)
            while True:
                command_interpreter(trojan_db, [''] + input(prompt).split(' '))
        else:
            # Return to shell with command return value
            exit(command_interpreter(trojan_db, sys.argv[0:]))
    except IndexError:
        Avalon.warning('No commands specified')
        exit(0)
    except (KeyboardInterrupt, EOFError):
        Avalon.warning('Exiting')
        exit(0)
    except Exception:
        Avalon.error('Exception caught')
        traceback.print_exc()
        exit(1) 

def execute_with_retry(self, operations, *args, **kwds):
        max_retries = 5
        for retry in range(max_retries):
            try:
                with self.conn.cursor() as cursor:
                    return operations(cursor, *args, **kwds)
            except MySQLdb.OperationalError:
                if retry == max_retries - 1:
                    raise
                else:
                    self._sleep(2 ** retry)
                    self._do_connect()
            else:
                break 

def _test_db_conn(self):
        import MySQLdb
        try:
            MySQLdb.connect(host=self.db_host, port=int(self.db_port),
                            user=self.db_user, passwd=self.db_pass, db=self.db)
            color_print('连接数据库成功', 'green')
            return True
        except MySQLdb.OperationalError, e:
            color_print('数据库连接失败 %s' % e, 'red')
            return False 

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 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()