async def realTestPamAuth(self):
db = self.db.copy()
import os
db['password'] = os.environ.get('PASSWORD')
cur = self.connections[0].cursor()
try:
await cur.execute('show grants for ' + TestAuthentication.osuser + '@localhost')
grants = (await cur.fetchone())[0]
await cur.execute('drop user ' + TestAuthentication.osuser + '@localhost')
except trio_mysql.OperationalError as e:
# assuming the user doesn't exist which is ok too
self.assertEqual(1045, e.args[0])
grants = None
async with TempUser(cur, TestAuthentication.osuser + '@localhost',
self.databases[0]['db'], 'pam', os.environ.get('PAMSERVICE')) as u:
try:
c = trio_mysql.connect(user=TestAuthentication.osuser, **db)
await c.connect()
db['password'] = '******'
with self.assertRaises(trio_mysql.err.OperationalError):
cc = trio_mysql.connect(user=TestAuthentication.osuser,
auth_plugin_map={b'mysql_cleartext_password': TestAuthentication.DefectiveHandler},
**self.db)
await cc.connect()
except trio_mysql.OperationalError as e:
self.assertEqual(1045, e.args[0])
# we had 'bad guess at password' work with pam. Well at least we get a permission denied here
with self.assertRaises(trio_mysql.err.OperationalError):
cc = trio_mysql.connect(user=TestAuthentication.osuser,
auth_plugin_map={b'mysql_cleartext_password': TestAuthentication.DefectiveHandler},
**self.db)
await cc.connect()
if grants:
# recreate the user
await cur.execute(grants)
async def realTestDialogAuthTwoQuestions(self):
TestAuthentication.Dialog.fail=False
TestAuthentication.Dialog.m = {b'Password, please:': b'notverysecret',
b'Are you sure ?': b'yes, of course'}
async with TempUser(self.connections[0].cursor(), 'trio_mysql_2q@localhost',
self.databases[0]['db'], 'two_questions', 'notverysecret') as u:
with self.assertRaises(trio_mysql.err.OperationalError):
c = trio_mysql.connect(user='******', **self.db)
await c.connect()
c = trio_mysql.connect(user='******', auth_plugin_map={b'dialog': TestAuthentication.Dialog}, **self.db)
await c.connect()
await c.aclose()
async def test_caching_sha2_password_ssl():
async with trio_mysql.connect(user="******",
password=pass_caching_sha2,
host=host,
port=port,
ssl=ssl):
pass
# Fast path of caching sha2
async with trio_mysql.connect(user="******",
password=pass_caching_sha2,
host=host,
port=port,
ssl=None) as con:
await con.query("FLUSH PRIVILEGES")
async def connect(self, **params):
p = self.databases[0].copy()
p.update(params)
conn = trio_mysql.connect(**p)
await conn.connect()
self.connections.append(conn)
return conn
async def test_sha256_password_ssl():
async with trio_mysql.connect(user="******",
password=pass_sha256,
host=host,
port=port,
ssl=ssl):
pass
async def test_sha256_password():
async with trio_mysql.connect(user="******",
password="******",
host=host,
port=port,
ssl=None):
pass
async def test_utf8mb4(self, set_me_up):
await set_me_up(self)
"""This test requires MySQL >= 5.5"""
arg = self.databases[0].copy()
arg['charset'] = 'utf8mb4'
conn = trio_mysql.connect(**arg)
await conn.connect()
await conn.aclose()
async def test_no_delay_warning(self, set_me_up):
await set_me_up(self)
current_db = self.databases[0].copy()
current_db['no_delay'] = True
with self.assertWarns(DeprecationWarning) as cm:
conn = trio_mysql.connect(**current_db)
await conn.connect()
await conn.aclose()
async def test_read_default_group(self, set_me_up):
await set_me_up(self)
conn = trio_mysql.connect(read_default_group='client',
**self.databases[0])
self.assertFalse(conn.open)
await conn.connect()
self.assertTrue(conn.open)
await conn.aclose()
async def realtestSocketAuth(self):
async with TempUser(self.connections[0].cursor(),
TestAuthentication.osuser + '@localhost',
self.databases[0]['db'],
self.socket_plugin_name) as u:
c = trio_mysql.connect(user=TestAuthentication.osuser, **self.db)
await c.connect()
await c.aclose()
async def realTestDialogAuthThreeAttempts(self):
TestAuthentication.Dialog.m = {b'Password, please:': b'stillnotverysecret'}
TestAuthentication.Dialog.fail=True # fail just once. We've got three attempts after all
async with TempUser(self.connections[0].cursor(), 'trio_mysql_3a@localhost',
self.databases[0]['db'], 'three_attempts', 'stillnotverysecret') as u:
c = trio_mysql.connect(user='******', auth_plugin_map={b'dialog': TestAuthentication.Dialog}, **self.db)
await c.connect()
await c.aclose()
c = trio_mysql.connect(user='******', auth_plugin_map={b'dialog': TestAuthentication.DialogHandler}, **self.db)
await c.connect()
await c.aclose()
with self.assertRaises(trio_mysql.err.OperationalError):
c = trio_mysql.connect(user='******', auth_plugin_map={b'dialog': object}, **self.db)
with self.assertRaises(trio_mysql.err.OperationalError):
c = trio_mysql.connect(user='******', auth_plugin_map={b'dialog': TestAuthentication.DefectiveHandler}, **self.db)
await c.connect()
await c.aclose()
with self.assertRaises(trio_mysql.err.OperationalError):
c = trio_mysql.connect(user='******', auth_plugin_map={b'notdialogplugin': TestAuthentication.Dialog}, **self.db)
await c.connect()
await c.aclose()
TestAuthentication.Dialog.m = {b'Password, please:': b'I do not know'}
with self.assertRaises(trio_mysql.err.OperationalError):
c = trio_mysql.connect(user='******', auth_plugin_map={b'dialog': TestAuthentication.Dialog}, **self.db)
await c.connect()
await c.aclose()
TestAuthentication.Dialog.m = {b'Password, please:': None}
with self.assertRaises(trio_mysql.err.OperationalError):
c = trio_mysql.connect(user='******', auth_plugin_map={b'dialog': TestAuthentication.Dialog}, **self.db)
await c.connect()
await c.aclose()
async def test_init_command(self, set_me_up):
await set_me_up(self)
conn = trio_mysql.connect(
#init_command='SELECT "bar"; SELECT "baz"',
init_command='SELECT "bar"',
**self.databases[0])
await conn.connect()
c = conn.cursor()
await c.execute('select "foobar";')
self.assertEqual(('foobar', ), await c.fetchone())
await conn.aclose()
with self.assertRaises(trio_mysql.err.Error):
await conn.ping(reconnect=False)
def __init__(self, zone: str, delete_unknowns: bool,
sql_cfg: ConfigExtractor, logger: "Logger") -> None: # {{{
self.logger = logger
self.delete_unknowns = delete_unknowns
self.domain_name = '.' + zone.lstrip('.')
sqlcfg: Dict[str,
object] = dict(cursorclass=trio_mysql.cursors.DictCursor)
sqlcfg.update(sql_cfg._config)
try:
self.conn = trio_mysql.connect(**sqlcfg)
except Exception as e:
raise ConfigError("Error configuring connection to mysql: %s" %
(e, ))
async def testMySQLOldPasswordAuth(self, set_me_up):
await set_me_up(self)
if self.mysql_server_is(self.connections[0], (5, 7, 0)):
raise base.SkipTest('Old passwords aren\'t supported in 5.7')
# trio_mysql.err.OperationalError: (1045, "Access denied for user 'old_pass_user'@'localhost' (using password: YES)")
# from login in MySQL-5.6
if self.mysql_server_is(self.connections[0], (5, 6, 0)):
raise base.SkipTest('Old passwords don\'t authenticate in 5.6')
db = self.db.copy()
db['password'] = "******"
async with self.connections[0] as c:
# deprecated in 5.6
if sys.version_info[0:2] >= (3, 2) and self.mysql_server_is(
self.connections[0], (5, 6, 0)):
with self.assertWarns(trio_mysql.err.Warning) as cm:
await c.execute("SELECT OLD_PASSWORD('%s')" %
db['password'])
else:
await c.execute("SELECT OLD_PASSWORD('%s')" % db['password'])
v = (await c.fetchone())[0]
self.assertEqual(v, '2a01785203b08770')
# only works in MariaDB and MySQL-5.6 - can't separate out by version
#if self.mysql_server_is(self.connections[0], (5, 5, 0)):
# with TempUser(c, 'old_pass_user@localhost',
# self.databases[0]['db'], 'mysql_old_password', '2a01785203b08770') as u:
# c = trio_mysql.connect(user='******', **db)
# await c.connect()
# cur = c.cursor()
# await cur.execute("SELECT VERSION()")
await c.execute("SELECT @@secure_auth")
secure_auth_setting = (await c.fetchone())[0]
await c.execute('set old_passwords=1')
# trio_mysql.err.Warning: 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead
if sys.version_info[0:2] >= (3, 2) and self.mysql_server_is(
self.connections[0], (5, 6, 0)):
with self.assertWarns(trio_mysql.err.Warning) as cm:
await c.execute('set global secure_auth=0')
else:
await c.execute('set global secure_auth=0')
async with TempUser(c,
'old_pass_user@localhost',
self.databases[0]['db'],
password=db['password']) as u:
cc = trio_mysql.connect(user='******', **db)
await cc.connect()
cur = cc.cursor()
await cur.execute("SELECT VERSION()")
await cc.aclose()
await c.execute('set global secure_auth=%r' % secure_auth_setting)
async def test_context(self, set_me_up):
await set_me_up(self)
c = trio_mysql.connect(**self.databases[0])
await c.connect()
with self.assertRaises(ValueError):
async with c as cur:
await cur.execute('create table if not exists test ( a int )')
await c.begin()
await cur.execute('insert into test values ((1))')
raise ValueError('pseudo abort')
await c.commit() # never executed
await c.aclose()
c = trio_mysql.connect(**self.databases[0])
await c.connect()
async with c as cur:
await cur.execute('select count(*) from test')
self.assertEqual(0, (await cur.fetchone())[0])
await cur.execute('insert into test values ((1))')
await c.commit() # otherwise we may deadlock
async with c as cur:
await cur.execute('select count(*) from test')
self.assertEqual(1, (await cur.fetchone())[0])
await cur.execute('drop table test')
async def main():
async with trio_mysql.connect(host='localhost',
port=3306,
user='******',
passwd='',
db='mysql') as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT Host,User FROM user")
print(cur.description)
print()
async for row in cur:
print(row)
async def testAuthSHA256(self, set_me_up):
await set_me_up(self)
c = self.connections[0].cursor()
async with TempUser(c, 'test_sha256@localhost',
self.databases[0]['db'], 'sha256_password') as u:
if self.mysql_server_is(self.connections[0], (5, 7, 0)):
await c.execute("SET PASSWORD FOR 'test_sha256'@'localhost' ='Sh@256Pa33'")
else:
await c.execute('SET old_passwords = 2')
await c.execute("SET PASSWORD FOR 'test_sha256'@'localhost' = PASSWORD('Sh@256Pa33')")
db = self.db.copy()
db['password'] = "******"
# not implemented yet so thows error
with self.assertRaises(trio_mysql.err.OperationalError):
c = trio_mysql.connect(user='******', **db)
await c.connect()
def get_db_connection() -> trio_mysql.Connection:
"""
Convenience function for DB connection.
Returns:
MySQL Connection
"""
return trio_mysql.connect(
host="localhost",
user=CONFIG["db"]["user"],
password=CONFIG["db"]["password"],
db=CONFIG["db"]["db"],
charset="utf8mb4",
# cursorclass=trio_mysql.cursors.DictCursor,
)
async def test_defer_connect(self, set_me_up):
await set_me_up(self)
for db in self.databases:
d = db.copy()
try:
sock = trio.socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)
await sock.connect(d['unix_socket'])
sock = trio.SocketStream(sock)
except KeyError:
sock = await trio.open_tcp_stream \
(d.get('host', 'localhost'), d.get('port', 3306))
for k in ['unix_socket', 'host', 'port']:
try:
del d[k]
except KeyError:
pass
c = trio_mysql.connect(**d)
self.assertFalse(c.open)
await c.connect(sock)
await c.aclose()
await sock.aclose()
async def test_caching_sha2_no_password():
async with trio_mysql.connect(user="******",
host=host,
port=port,
ssl=ssl):
pass
async def test_sha256_no_passowrd_ssl():
async with trio_mysql.connect(user="******",
host=host,
port=port,
ssl=ssl):
pass
async def _get_plugins(db):
async with trio_mysql.connect(**db) as conn:
async with conn.cursor() as curs:
await curs.execute("SHOW PLUGINS")
res = await curs.fetchall()
return res
async def setUp(self):
self.connections = []
for params in self.databases:
conn = trio_mysql.connect(**params)
await conn.connect()
self.connections.append(conn)
async def main():
q = asks.Session(connections=3)
cl = trio.CapacityLimiter(5)
cff = open('cfg.yaml', 'r+')
cfg = yaml.safe_load(cff)
db_ = DB.connect(**cfg['mysql'],
cursorclass=trio_mysql.cursors.SSDictCursor,
init_command="set time_zone='+00:00';")
async with trio.open_nursery() as N:
async with db_ as db:
async with db.cursor() as c:
await c.execute("select * from data_type where series is NULL")
async for r in c:
raise RuntimeError("not assigned", r)
await c.execute(
"select id,series,tags,zero from data_type where series != '-'"
)
async for r in c:
tags = str2tags(r['tags'])
known[r['id']] = (r['series'], tags)
if r['zero'] is not None:
special[r['id']] = [r['zero'], None]
di = []
async def one(dt):
async with connect(N,
host="stats.work.smurf.noris.de",
delta=True) as s:
for v in known.values():
s.preload(v[0], v[1])
await s.flush_dict()
series, tags = known[dt]
async with cl:
ts = await get_min_ts(q, series, tags)
if ts < 0:
ts = t = None
sp = special.get(dt, None)
nn = 0
n = 0
while True:
nn += n
n = 0
async with db.cursor() as c:
ats = ""
if ts:
ats = "and timestamp > from_unixtime(%s)" % (
ts, )
await c.execute(
"select id, timestamp as t, unix_timestamp(timestamp) as ts, value from data_log where data_type = %s %s order by timestamp limit 10000"
% (dt, ats))
async for r in c:
e = Entry(value=r['value'],
mode=DS.gauge,
series=series,
tags=tags,
time=r['ts'])
if sp:
if sp[0] > 0:
if sp[1] is not None:
lts = sp[0] + sp[1]
if lts < r['ts']:
ec = copy(e)
ec.value = 0
ec.time = lts
await s.put(ec)
sp[1] = r['ts']
else:
e.mode = DS.counter
await s.put(e)
n += 1
t = r['t']
ts = r['ts']
cfg['at'] = {'t': t, 'ts': ts, 'dt': dt}
if not n:
break
print(dt, *known[dt], nn)
async def one_(x):
try:
await one(x)
except Exception as exc:
print_exc()
if len(sys.argv) < 2:
for k in known.keys():
await one_(k)
else:
for k in sys.argv[1:]:
await one_(int(k))
N.cancel_scope.cancel()
async def test_set_charset(self, set_me_up):
await set_me_up(self)
c = trio_mysql.connect(**self.databases[0])
await c.connect()
await c.set_charset('utf8')
await c.aclose()
