def test_skip_cleanup(self):
payload = CopyPayload()
sql = 'CREATE TABLE abc (ID int)'
database = 'db'
payload._old_table = Mock()
payload._old_table.name = 'abc'
# add some drop table entry pretending we've done some work
payload._cleanup_payload = CleanupPayload(db=database)
payload._cleanup_payload.add_drop_table_entry(
database, constant.DELTA_TABLE_PREFIX + 'abc')
payload._cleanup_payload.add_drop_table_entry(
database, constant.NEW_TABLE_PREFIX + 'abc')
payload._cleanup_payload.cleanup = Mock()
# If we don't skip cleanup, then we should have 2 tables to clean up
payload.skip_cleanup_after_kill = False
with self.assertRaises(OSCError) as err_context:
payload.init_connection = Mock(
side_effect=MySQLdb.OperationalError(2006,
'MySQL has gone away'))
payload.run_ddl(database, sql)
self.assertEqual(len(payload._cleanup_payload.to_drop), 2)
self.assertEqual(err_context.exception.err_key, 'GENERIC_MYSQL_ERROR')
# If we are skipping cleanup, then there's nothing to cleanup
payload.skip_cleanup_after_kill = True
with self.assertRaises(OSCError) as err_context:
payload.init_connection = Mock(
side_effect=MySQLdb.OperationalError(2006,
'MySQL has gone away'))
payload.run_ddl(database, sql)
# There should be no cleanup entry at all if we skip the table cleanup
self.assertEqual(payload._cleanup_payload.to_drop, [])
self.assertEqual(err_context.exception.err_key, 'GENERIC_MYSQL_ERROR')
def test_set_innodb_tmpdir(self):
"""
Make sure set_innodb_tmpdir will catch and only catch 1231 error
"""
payload = CopyPayload()
table_obj = parse_create(
" CREATE TABLE a "
"( ID int primary key ) ")
payload._old_table = table_obj
payload._new_table = table_obj
payload.replay_changes = Mock()
payload.execute_sql = Mock(
side_effect=MySQLdb.OperationalError(1231, 'abc'))
# Call the function make sure it catch the 1231 error
payload.set_innodb_tmpdir('mock/path')
# Call the function make sure it will still raise anything other than
# 1231
with self.assertRaises(MySQLdb.OperationalError) as err_context:
payload.execute_sql = Mock(
side_effect=MySQLdb.OperationalError(1111, 'abc'))
payload.set_innodb_tmpdir('mock/path')
self.assertEqual(err_context.exception.args[0], 1111)
def _runTransientErrorRetryTest(self, numErrors):
for errorCode in _ALL_RETRIABLE_ERROR_CODES:
clientSideEffects = [sqlalchemy.exc.OperationalError(
orig=MySQLdb.OperationalError(errorCode),
statement="err", params=None)] \
* numErrors + [DEFAULT]
serverSideEffects = [sqlalchemy.exc.InternalError(
orig=MySQLdb.InternalError(errorCode),
statement="err", params=None)] \
* numErrors + [DEFAULT]
if 3000 > errorCode >= 2000:
# The error is client side. Return one operationalError, then pass
with patch.object(Engine,
"execute",
spec_set=Engine.execute,
side_effect=clientSideEffects) \
as mockExecute:
retryOnTransientErrors(mockExecute)(Mock())
self.assertEqual(mockExecute.call_count, numErrors + 1)
elif errorCode >= 1000:
# The error is server side. Return one internalError, then pass
with patch.object(Engine,
"execute",
spec_set=Engine.execute,
side_effect=serverSideEffects) \
as mockExecute:
retryOnTransientErrors(mockExecute)(Mock())
self.assertEqual(mockExecute.call_count, numErrors + 1)
else:
self.fail("Error code is neither client nor server: %s" % errorCode)
def test_set_rocksdb_bulk_load(self):
payload = CopyPayload()
table_obj = parse_create(
" CREATE TABLE a "
"( ID int primary key ) ENGINE=ROCKSDB")
payload._old_table = table_obj
payload._new_table = table_obj
payload.execute_sql = Mock()
payload.change_rocksdb_bulk_load()
self.assertTrue(payload.execute_sql.called)
table_obj = parse_create(
" CREATE TABLE a "
"( ID int primary key ) ENGINE=ROCKSDB")
new_table_obj = parse_create(
" CREATE TABLE a "
"( ID int, id2 int, "
"primary key (ID,id2)) ENGINE=ROCKSDB")
payload._old_table = table_obj
payload._new_table = new_table_obj
payload.execute_sql = Mock()
payload.change_rocksdb_bulk_load()
self.assertFalse(payload.execute_sql.called)
table_obj = parse_create(
" CREATE TABLE a "
"( ID int primary key ) ENGINE=ROCKSDB")
payload._old_table = table_obj
payload._new_table = table_obj
payload.execute_sql = Mock(
side_effect=MySQLdb.OperationalError(1193, 'abc'))
payload.change_rocksdb_bulk_load()
def setUp(self):
db_lib.MySQLSocketConnection.query = MagicMock(
side_effect=MySQLdb.OperationalError(2013, "transaction failed")
)
self._payload = DirectPayload()
self._payload.read_ddl_files = MagicMock()
super(DirectPayloadSQLFailed, self).setUp()
def setUp(self):
db_lib.MySQLSocketConnection = MagicMock(
side_effect=MySQLdb.OperationalError(2013, "mock unconnectable")
)
self._payload = DirectPayload()
self._payload.read_ddl_files = MagicMock()
super(DirectPayloadDeadMySQL, self).setUp()
def getData(db_obj, table, column="*", conditions=''):
"""
从数据库中获取数据
:param db_obj: 目标数据库对象
:param table: 来源表
:param column: select的列
:param conditions: 筛选条件
:return: 返回查询的数据 (dictionary形式)
"""
try:
db = MySQLdb.connect(db_obj.host_ip,
db_obj.username,
db_obj.password,
db_obj.database,
charset='utf8')
try:
cursor = db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("""select %s from %s%s;""" %
(column, table, conditions))
print("""select %s from %s%s;""" % (column, table, conditions))
result = cursor.fetchall()
return result
except MySQLdb.ProgrammingError as e:
raise MySQLdb.ProgrammingError("ERROR 数据库执行语句出错:" + str(e))
#raise MySQLdb.ProgrammingError("ERROR 数据库执行语句出错:" + e)
finally:
# 一定关闭数据库连接
db.close()
except MySQLdb.OperationalError as e:
raise MySQLdb.OperationalError("ERROR 数据库连接不上:" + str(e))
def sysbench(cmd, threads):
"""Execute linux cmd sysbench"""
cmd = cmd.format(
MYSQL_HOST=MYSQL_HOST,
MYSQL_USER=MYSQL_USER,
MYSQL_PASSWORD=MYSQL_PASSWORD,
TEST_DIR=TEST_DIR,
THREADS=threads
)
result = subprocess.run(cmd, shell=True, stdout=subprocess.PIPE)
if result.returncode:
printv("error")
if "FATAL: error 1040: Too many connections" in str(result.stdout):
raise MySQLdb.OperationalError(1040, "Too many connections")
else:
printv("---- cmd")
printv(cmd.strip())
printv("---- return code")
printv(result.returncode)
printv("---- stdout")
printv(result.stdout)
printv("---- stderr")
printv(result.stderr)
printv("----")
return result.stdout
def test_skip_cleanup(self):
payload = CopyPayload()
sql = "CREATE TABLE abc (ID int)"
database = "db"
payload._old_table = Mock()
payload._old_table.name = "abc"
payload._new_table = Mock()
payload._new_table.name = "abc"
payload.outfile_dir = "/path/to/file/dump"
payload.outfile_suffix_end = 2
# add some drop table entry pretending we've done some work
payload._cleanup_payload = CleanupPayload(db=database)
payload._cleanup_payload.add_drop_table_entry(
database, constant.DELTA_TABLE_PREFIX + "abc"
)
payload._cleanup_payload.add_drop_table_entry(
database, constant.NEW_TABLE_PREFIX + "abc"
)
payload._cleanup_payload.cleanup = Mock()
for suffix in range(1, payload.outfile_suffix_end + 1):
payload._cleanup_payload.add_file_entry(payload.outfile + "." + str(suffix))
# If we don't skip cleanup, then we should have 2 tables to clean up
payload.skip_cleanup_after_kill = False
with self.assertRaises(OSCError) as err_context:
payload.init_connection = Mock(
side_effect=MySQLdb.OperationalError(2006, "MySQL has gone away")
)
payload.run_ddl(database, sql)
self.assertEqual(len(payload._cleanup_payload.to_drop), 2)
self.assertEqual(
len(payload._cleanup_payload.files_to_clean), payload.outfile_suffix_end
)
self.assertEqual(err_context.exception.err_key, "GENERIC_MYSQL_ERROR")
# If we are skipping cleanup, then there's nothing to cleanup
payload.skip_cleanup_after_kill = True
with self.assertRaises(OSCError) as err_context:
payload.init_connection = Mock(
side_effect=MySQLdb.OperationalError(2006, "MySQL has gone away")
)
payload.run_ddl(database, sql)
# There should be no cleanup entry at all if we skip the table cleanup
self.assertEqual(payload._cleanup_payload.to_drop, [])
self.assertEqual(len(payload._cleanup_payload.files_to_clean), 0)
self.assertEqual(err_context.exception.err_key, "GENERIC_MYSQL_ERROR")
def execute(cur, stmt):
ROW_COUNT_ERROR = 18446744073709551615
logging.debug("Executing %s" % stmt)
cur.execute(stmt)
if cur.rowcount < 0 or cur.rowcount == ROW_COUNT_ERROR:
raise MySQLdb.OperationalError(MySQLdb.constants.CR.CONNECTION_ERROR,
"Possible connection error, rowcount is %d"
% cur.rowcount)
def test_mysql_command_operational_error(self, db_cursor_mock,
setup_mysql_loop_mock):
db_cursor_mock.execute.side_effect = MySQLdb.OperationalError()
setup_mysql_loop_mock.side_effect = exceptions.DbError()
self.assertRaises(exceptions.DbError, self.db.mysql_command, 'execute',
'test_sql', True, True, 'test_arg')
db_cursor_mock.execute.assert_called_once_with('test_sql',
('test_arg', ))
def execute(cur, stmt):
ROW_COUNT_ERROR = 18446744073709551615L
logging.debug("Executing %s" % stmt)
cur.execute(stmt)
if cur.rowcount < 0 or cur.rowcount == ROW_COUNT_ERROR:
# PyMySQL CR codes has CR prefixes while MySQLdb doesn't
raise MySQLdb.OperationalError(MySQLdb.constants.CR.CR_CONNECTION_ERROR,
"Possible connection error, rowcount is %d"
% cur.rowcount)
def test_query_error(self):
connect_mock = MagicMock()
with patch.object(mysql, '_connect', connect_mock):
with patch.dict(mysql.__salt__, {'config.option': MagicMock()}):
side_effect = MySQLdb.OperationalError(9999, 'Something Went Wrong')
with patch.object(mysql, '_execute', MagicMock(side_effect=side_effect)):
mysql.query('testdb', 'SELECT * FROM testdb')
self.assertIn('mysql.error', mysql.__context__)
expected = 'MySQL Error 9999: Something Went Wrong'
self.assertEqual(mysql.__context__['mysql.error'], expected)
def testIsRetryable(self):
self.assertFalse(mysql._IsRetryable(Exception("Some general error.")))
self.assertFalse(
mysql._IsRetryable(
MySQLdb.OperationalError(
1416, "Cannot get geometry object from data...")))
self.assertTrue(
mysql._IsRetryable(
MySQLdb.OperationalError(
1205, "Lock wait timeout exceeded; try restarting...")))
self.assertTrue(
mysql._IsRetryable(
MySQLdb.OperationalError(
1213,
"Deadlock found when trying to get lock; try restarting...")))
self.assertTrue(
mysql._IsRetryable(
MySQLdb.OperationalError(
1637, "Too many active concurrent transactions")))
def RaisePermanentError(connection):
# Wrap methods of the connection so we can check whether they get
# called later.
real_rollback_fn = connection.rollback
real_close_fn = connection.close
connection.rollback = mock.Mock(wraps=real_rollback_fn)
connection.close = mock.Mock(wraps=real_close_fn)
connections.append(connection)
raise MySQLdb.OperationalError(mysql_conn_errors.NOT_IMPLEMENTED,
expected_error_msg)
def RaiseServerGoneError(connection):
# Wrap methods of the connection so we can check whether they get
# called later.
real_rollback_fn = connection.rollback
real_close_fn = connection.close
connection.rollback = mock.Mock(wraps=real_rollback_fn)
connection.close = mock.Mock(wraps=real_close_fn)
connections.append(connection)
raise MySQLdb.OperationalError(mysql_conn_errors.SERVER_GONE_ERROR,
expected_error_msg)
def test_file_exists(self):
payload = self.payload_setup()
with self.assertRaises(OSCError) as err_context:
payload.execute_sql = Mock(
side_effect=MySQLdb.OperationalError(1086, 'abc'))
payload.select_full_table_into_outfile()
self.assertEqual(err_context.exception.err_key, 'FILE_ALREADY_EXIST')
with self.assertRaises(OSCError) as err_context:
payload.execute_sql = Mock(
side_effect=MySQLdb.OperationalError(1086, 'abc'))
payload.select_chunk_into_outfile('path/to/outfile', False)
self.assertEqual(err_context.exception.err_key, 'FILE_ALREADY_EXIST')
# Any mysql error other than 1086 should surface
with self.assertRaises(MySQLdb.OperationalError) as err_context:
payload.execute_sql = Mock(
side_effect=MySQLdb.OperationalError(1111, 'abc'))
payload.select_chunk_into_outfile('path/to/outfile', False)
self.assertEqual(err_context.exception.args[0], 1111)
def execute(self, query=None, params=None):
try:
cursor = self._thread[self.thread_id][self.name]['cursor']
result = execute(cursor, query, params)
self._thread[self.thread_id][self.name]['uncommited'] = True
return result
except MySQLdb.OperationalError as e:
if self._ping is False:
log.error("mysql error, attempt to re-initialize (%s)" % (e))
del self._thread[self.thread_id][self.name]
self.initialize()
return execute(self, query, params)
else:
raise MySQLdb.OperationalError(e)
def testNonTransientError(self):
# Pass MySQLdb error constant for CANT_CREATE_TABLE
errorCode = 1005
#The error is client side. Return an operationalError
with patch.object(Engine,
"execute",
spec_set=Engine.execute,
side_effect=[sqlalchemy.exc.OperationalError(
orig=MySQLdb.OperationalError(errorCode),
statement="err", params=None)])\
as mockExecute:
self.assertRaises(sqlalchemy.exc.OperationalError,
retryOnTransientErrors(mockExecute))
def unlock(self):
try:
cursor = self._thread[self.thread_id][self.name]['cursor']
query = "UNLOCK TABLES"
result = execute(cursor, query)
return result
except MySQLdb.OperationalError as e:
if self._ping is False:
log.error("mysql error, attempt to re-initialize (%s)" % (e))
del self._thread[self.thread_id][self.name]
self.initialize()
return unlock(self, table)
else:
raise MySQLdb.OperationalError(e)
def execute_from_queue(self, query, params, retry_count=0):
cursor = self._conn.cursor()
try:
cursor.execute(query, params)
self._conn.commit()
cursor.close()
except MySQLdb.OperationalError, e:
# The db has gone down
cursor.close()
if retry_count > 20:
# this is going to fail out of initialization or initiate emergency braking
raise MySQLdb.OperationalError(e)
self.execute_from_queue(query, params, retry_count + 1)
def __connect(self):
"""Makes a connection to the database"""
gc.collect(
) # runs the python garbage collector to close and possible open but unused MySQL connections
try:
self.__con = mdb.connect(host=self.__hostname,
user=self.__user,
passwd=self.__password,
db=self.__dbname,
use_unicode=True,
charset="utf8")
except mdb.Error, e:
raise mdb.OperationalError("Database Error: %d: %s" %
(e.args[0], e.args[1]))
def testConfigureDatastore_ConnectionRetry(self, getpass_mock, connect_mock):
# Mock user-inputs for MySQL prompts.
self.input_mock.side_effect = [
"", # MySQL hostname (the default is localhost).
"1234", # MySQL port
"grr-test-db", # GRR db name.
"grr-test-user", # GRR db user.
"n" # Exit config initialization after retries are depleted.
]
getpass_mock.return_value = "grr-test-password" # DB password for GRR.
connect_mock.side_effect = MySQLdb.OperationalError(
mysql_conn_errors.CONNECTION_ERROR, "Fake connection error.")
config = grr_config.CONFIG.CopyConfig()
with self.assertRaises(config_updater_util.ConfigInitError):
config_updater_util.ConfigureDatastore(config)
self.assertEqual(connect_mock.call_count, 2)
def execute(self, *args, **kwargs):
try:
result = self._cursor.execute(*args, **kwargs)
except MySQLdb.ProgrammingError as e:
raise MySQLdb.ProgrammingError(
e.args[0], e.args[1] +
'.\nSTATEMENT: {}'.format(self._cursor._last_executed))
except MySQLdb.OperationalError as e:
# Sometimes a MySQL session times out. In this case, we wish to reconnect, and reissue the query.
if e[0] == 2006:
self.connect()
result = self._cursor.execute(*args, **kwargs)
else:
raise MySQLdb.OperationalError(
e.args[0], e.args[1] +
'.\nSTATEMENT: {}'.format(self._cursor._last_executed))
return result
def execute(self, sql, args=None):
try:
result = self.cursor.execute(sql, args=args)
except MySQLdb.OperationalError as e:
#捕获超时异常
err_args = e.args
code = err_args[0]
if code in (2006, 2013):
#print("超时重连 %s"%code)
#重连
mysql_conn = self.get_mysql_conn()
self.cursor = mysql_conn.cursor(**self.kwargs)
self.conn = mysql_conn
return self.execute(sql, args)
else:
raise MySQLdb.OperationalError(*err_args)
return result
def lock(self, table, write=True):
try:
if write is True:
lock = "WRITE"
else:
lock = "READ"
cursor = self._thread[self.thread_id][self.name]['cursor']
query = "LOCK TABLES %s %s" % (table, lock)
result = execute(cursor, query)
return result
except MySQLdb.OperationalError as e:
if self._ping is False:
log.error("mysql error, attempt to re-initialize (%s)" % (e))
del self._thread[self.thread_id][self.name]
self.initialize()
return lock(self, table, write)
else:
raise MySQLdb.OperationalError(e)
def read_topics_from_db(conn, start_date):
"""
Read unchecked topics from database, return list of topics
param start_date(str): YYYY-MM-DD
"""
select_topic = """
SELECT DISTINCT title FROM topicinfo
WHERE theme LIKE '新浪微博_热门话题%'
AND STR_TO_DATE(createdate, "%Y-%m-%d %H:%i:%s") > '{}'
ORDER BY STR_TO_DATE(createdate, "%Y-%m-%d %H:%i:%s")
""".format(start_date)
cursor = conn.cursor()
# read search keywords from table topicinfo
cursor.execute(select_topic) # filter by date: >_< , include >, exclude <
topicinfo_res = cursor.fetchall()
import ipdb; ipdb.set_trace()
raise mdb.OperationalError(1000, "You bastard")
for res in topicinfo_res:
yield res[0]
def create_table(self, reset=False):
if reset:
self.execute("DROP TABLE IF EXISTS {}".format(self.tablename))
sql = """CREATE TABLE {} (
cache_key VARCHAR(255) primary key not null,
cache_value JSON default NULL,
date_created DATETIME default NULL,
version int(11) default 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci""".format(
self.tablename)
try:
self.execute(sql)
self._create_triggers()
return True
except mdb.OperationalError as error:
if error.args[0] == 1050:
# if table already exists, we're fine.
return True
raise mdb.OperationalError(error.args)
def testMeaningfulErrorWhenNotEnoughPermissionsToOverrideGlobalVariable(
self):
def SetMaxAllowedPacket(conn):
with contextlib.closing(conn.cursor()) as cursor:
mysql._SetGlobalVariable("max_allowed_packet", 20 << 10,
cursor)
self.db.delegate._RunInTransaction(SetMaxAllowedPacket)
# MaxAllowedPacketSettingTooLowError will be raised since
# _SetGlobalVariable call will fail (via the mock). This way
# we mimick the situation when _SetGlobalVariable fails due to
# the lack of permissions.
with mock.patch.object(mysql,
"_SetGlobalVariable",
side_effect=MySQLdb.OperationalError(
"SUPER privileges required")):
with self.assertRaises(mysql.MaxAllowedPacketSettingTooLowError):
mysql.MysqlDB(host=self._testdb.hostname(),
port=self._testdb.port(),
user=self._testdb.username(),
password=self._testdb.password(),
database=self._testdb.dbname())
def db_error(self, *args, **kwargs):
raise mysql.OperationalError("You can't do this in read-only mode.")
