def test_xfield_types(self):
cursor = self.connection.cursor()
fieldinfo = mariadb.fieldinfo()
cursor.execute(
"CREATE TEMPORARY TABLE test_xfield_types (a tinyint not null auto_increment primary "
"key, b smallint, c int, d bigint, e float, f decimal, g double, h char(10), i varchar(255), j blob, k json, index(b))"
)
info = cursor.description
self.assertEqual(info, None)
cursor.execute("SELECT * FROM test_xfield_types")
info = cursor.description
self.assertEqual(fieldinfo.type(info[0]), "TINY")
self.assertEqual(fieldinfo.type(info[1]), "SHORT")
self.assertEqual(fieldinfo.type(info[2]), "LONG")
self.assertEqual(fieldinfo.type(info[3]), "LONGLONG")
self.assertEqual(fieldinfo.type(info[4]), "FLOAT")
self.assertEqual(fieldinfo.type(info[5]), "NEWDECIMAL")
self.assertEqual(fieldinfo.type(info[6]), "DOUBLE")
self.assertEqual(fieldinfo.type(info[7]), "STRING")
self.assertEqual(fieldinfo.type(info[8]), "VAR_STRING")
self.assertEqual(fieldinfo.type(info[9]), "BLOB")
if not is_maxscale() and (self.connection.server_version_info >
(10, 5, 1) or is_mysql()):
self.assertEqual(fieldinfo.type(info[10]), "JSON")
else:
self.assertEqual(fieldinfo.type(info[10]), "BLOB")
self.assertEqual(fieldinfo.flag(info[0]),
"NOT_NULL | PRIMARY_KEY | AUTO_INCREMENT | NUMERIC")
self.assertEqual(fieldinfo.flag(info[1]), "PART_KEY | NUMERIC")
self.assertEqual(fieldinfo.flag(info[9]), "BLOB | BINARY")
del cursor
def test_conpy_15(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
cursor = self.connection.cursor()
cursor.execute(
"CREATE TEMPORARY TABLE test_conpy_15 (a int not null auto_increment primary key, b varchar(20))"
)
self.assertEqual(cursor.lastrowid, None)
cursor.execute("INSERT INTO test_conpy_15 VALUES (null, 'foo')")
self.assertEqual(cursor.lastrowid, 1)
cursor.execute("SELECT LAST_INSERT_ID()")
row = cursor.fetchone()
self.assertEqual(row[0], 1)
vals = [(3, "bar"), (4, "this")]
cursor.executemany("INSERT INTO test_conpy_15 VALUES (?,?)", vals)
self.assertEqual(cursor.lastrowid, 4)
# Bug MDEV-16847
# cursor.execute("SELECT LAST_INSERT_ID()")
# row= cursor.fetchone()
# self.assertEqual(row[0], 4)
# Bug MDEV-16593
# vals= [(None, "bar"), (None, "foo")]
# cursor.executemany("INSERT INTO t1 VALUES (?,?)", vals)
# self.assertEqual(cursor.lastrowid, 6)
del cursor
def test_change_user(self):
if is_skysql():
self.skipTest("SkySQL failure")
if self.connection.server_version < 100103:
self.skipTest("CREATE OR REPLACE USER not supported")
if is_maxscale():
self.skipTest("MAXSCALE doesn't get new user immediately")
if self.connection.server_name == "localhost":
curs = self.connection.cursor(buffered=True)
curs.execute(
"select * from information_schema.plugins where plugin_name='unix_socket' and plugin_status='ACTIVE'"
)
if curs.rowcount > 0:
del curs
self.skipTest("unix_socket is active")
del curs
default_conf = conf()
cursor = self.connection.cursor()
cursor.execute("drop user if exists foo")
if is_mysql() and self.connection.server_version < 80000:
cursor.execute("create user foo@'%'")
cursor.execute("GRANT ALL on `" + default_conf["database"] +
"`.* TO foo@'%' IDENTIFIED BY 'heyPassw-!µ20§rd'")
else:
cursor.execute(
"create user foo@'%' IDENTIFIED BY 'heyPassw-!µ20§rd'")
cursor.execute("GRANT ALL on `" + default_conf["database"] +
"`.* TO foo@'%'")
new_conn = create_connection()
new_conn.change_user("foo", "heyPassw-!µ20§rd", "")
self.assertEqual("foo", new_conn.user)
cursor.execute("drop user foo")
del new_conn
del cursor
def test_conpy61(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
con = create_connection()
if self.connection.server_version < server_indicator_version:
self.skipTest("Requires server version >= 10.2.6")
cursor = con.cursor()
cursor.execute(
"CREATE TEMPORARY TABLE ind1 (a int, b int default 2,c int)")
vals = [(1, 4, 3), (None, 2, 3)]
cursor.executemany("INSERT INTO ind1 VALUES (?,?,?)", vals)
cursor.execute("SELECT a, b, c FROM ind1")
row = cursor.fetchone()
self.assertEqual(row[0], 1)
row = cursor.fetchone()
self.assertEqual(row[0], None)
cursor.execute("DELETE FROM ind1")
vals = [(1, 4, 3), (INDICATOR.NULL, INDICATOR.DEFAULT, None)]
cursor.executemany("INSERT INTO ind1 VALUES (?,?,?)", vals)
cursor.execute("SELECT a, b, c FROM ind1")
row = cursor.fetchone()
self.assertEqual(row[0], 1)
row = cursor.fetchone()
self.assertEqual(row[0], None)
self.assertEqual(row[1], 2)
self.assertEqual(row[2], None)
del cursor
def test_reconnect(self):
if is_maxscale():
self.skipTest("MAXSCALE wrong thread id")
new_conn = create_connection()
conn1_id = new_conn.connection_id
self.connection.kill(conn1_id)
new_conn.reconnect()
conn2_id = new_conn.connection_id
self.assertFalse(conn1_id == conn2_id)
del new_conn
def test_compress(self):
default_conf= conf()
new_conn = mariadb.connect(**default_conf, compress=True)
cursor=new_conn.cursor()
cursor.execute("SHOW SESSION STATUS LIKE 'compression'")
row=cursor.fetchone()
if is_maxscale():
self.assertEqual(row[1], "OFF")
else:
self.assertEqual(row[1], "ON")
del cursor
del new_conn
def test_conpy_14(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
cursor = self.connection.cursor()
self.assertEqual(cursor.rowcount, -1)
cursor.execute(
"CREATE TEMPORARY TABLE test_conpy_14 (a int not null auto_increment primary key, b varchar(20))"
)
self.assertEqual(cursor.rowcount, 0)
cursor.execute("INSERT INTO test_conpy_14 VALUES (null, 'foo')")
self.assertEqual(cursor.rowcount, 1)
vals = [(3, "bar"), (4, "this")]
cursor.executemany("INSERT INTO test_conpy_14 VALUES (?,?)", vals)
self.assertEqual(cursor.rowcount, 2)
del cursor
def test_ping(self):
if is_maxscale():
self.skipTest("MAXSCALE wrong thread id")
conn = self.connection
cursor = conn.cursor()
oldid = conn.connection_id
try:
cursor.execute("KILL {id}".format(id=oldid))
except:
pass
conn.auto_reconnect = True
conn.ping()
self.assertNotEqual(oldid, conn.connection_id)
self.assertNotEqual(0, conn.connection_id)
def test_bulk_delete(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
cursor = self.connection.cursor()
cursor.execute(
"CREATE TEMPORARY TABLE bulk_delete (id int, name varchar(64), city varchar(64))"
)
params = [(1, u"Jack", u"Boston"), (2, u"Martin", u"Ohio"),
(3, u"James", u"Washington"), (4, u"Rasmus", u"Helsinki"),
(5, u"Andrey", u"Sofia")]
cursor.executemany("INSERT INTO bulk_delete VALUES (?,?,?)", params)
self.assertEqual(cursor.rowcount, 5)
params = [(1, ), (2, )]
cursor.executemany("DELETE FROM bulk_delete WHERE id=?", params)
self.assertEqual(cursor.rowcount, 2)
del cursor
def test_fetchmany(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
cursor = self.connection.cursor()
cursor.execute(
"CREATE TEMPORARY TABLE test_fetchmany (id int, name varchar(64), "
"city varchar(64))")
params = [(1, u"Jack", u"Boston"), (2, u"Martin", u"Ohio"),
(3, u"James", u"Washington"), (4, u"Rasmus", u"Helsinki"),
(5, u"Andrey", u"Sofia")]
cursor.executemany("INSERT INTO test_fetchmany VALUES (?,?,?)", params)
# test Errors
# a) if no select was executed
self.assertRaises(mariadb.Error, cursor.fetchall)
# b ) if cursor was not executed
del cursor
cursor = self.connection.cursor()
self.assertRaises(mariadb.Error, cursor.fetchall)
cursor.execute("SELECT id, name, city FROM test_fetchmany ORDER BY id")
self.assertEqual(0, cursor.rowcount)
row = cursor.fetchall()
self.assertEqual(row, params)
self.assertEqual(5, cursor.rowcount)
cursor.execute("SELECT id, name, city FROM test_fetchmany ORDER BY id")
self.assertEqual(0, cursor.rowcount)
row = cursor.fetchmany(1)
self.assertEqual(row, [params[0]])
self.assertEqual(1, cursor.rowcount)
row = cursor.fetchmany(2)
self.assertEqual(row, ([params[1], params[2]]))
self.assertEqual(3, cursor.rowcount)
cursor.arraysize = 1
row = cursor.fetchmany()
self.assertEqual(row, [params[3]])
self.assertEqual(4, cursor.rowcount)
cursor.arraysize = 2
row = cursor.fetchmany()
self.assertEqual(row, [params[4]])
self.assertEqual(5, cursor.rowcount)
del cursor
def test_executemany(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
con = self._connect()
try:
cur = con.cursor()
self.executeDDL1(cur)
largs = [("Cooper's", ), ("Boag's", )]
margs = [{'beer': "Cooper's"}, {'beer': "Boag's"}]
if self.driver.paramstyle == 'qmark':
cur.executemany(
'insert into %sbooze values (?)' % self.table_prefix,
largs)
elif self.driver.paramstyle == 'numeric':
cur.executemany(
'insert into %sbooze values (:1)' % self.table_prefix,
largs)
elif self.driver.paramstyle == 'named':
cur.executemany(
'insert into %sbooze values (:beer)' % self.table_prefix,
margs)
elif self.driver.paramstyle == 'format':
cur.executemany(
'insert into %sbooze values (%%s)' % self.table_prefix,
largs)
elif self.driver.paramstyle == 'pyformat':
cur.executemany(
'insert into %sbooze values (%%(beer)s)' %
(self.table_prefix), margs)
else:
self.fail('Unknown paramstyle')
self.assertTrue(
cur.rowcount in (-1, 2),
'insert using cursor.executemany set cursor.rowcount to '
'incorrect value %r' % cur.rowcount)
cur.execute('select name from %sbooze' % self.table_prefix)
res = cur.fetchall()
self.assertEqual(
len(res), 2,
'cursor.fetchall retrieved incorrect number of rows')
beers = [res[0][0], res[1][0]]
beers.sort()
self.assertEqual(beers[0], "Boag's", 'incorrect data retrieved')
self.assertEqual(beers[1], "Cooper's", 'incorrect data retrieved')
finally:
con.close()
def test_schema(self):
if self.connection.server_version < 100103:
self.skipTest("CREATE OR REPLACE SCHEMA not supported")
if self.connection.server_version < 100202:
self.skipTest("session tracking not supported")
if is_maxscale():
self.skipTest("MAXSCALE doesn't tell schema change for now")
default_conf = conf()
conn = self.connection
self.assertEqual(conn.database, default_conf["database"])
cursor = conn.cursor()
cursor.execute("CREATE OR REPLACE SCHEMA test1")
cursor.execute("USE test1")
self.assertEqual(conn.database, "test1")
conn.database = default_conf["database"]
self.assertEqual(conn.database, default_conf["database"])
def test_inserttuple(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
cursor = self.connection.cursor()
cursor.execute(
"CREATE TEMPORARY TABLE test_inserttuple (id int, name varchar(64), "
"city varchar(64))")
params = ((1, u"Jack", u"Boston"), (2, u"Martin", u"Ohio"),
(3, u"James", u"Washington"), (4, u"Rasmus", u"Helsinki"),
(5, u"Andrey", u"Sofia"))
cursor.executemany("INSERT INTO test_inserttuple VALUES (?,?,?)",
params)
cursor.execute("SELECT name FROM test_inserttuple ORDER BY id DESC")
row = cursor.fetchone()
self.assertEqual("Andrey", row[0])
del cursor
def test_format(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
cursor = self.connection.cursor()
cursor.execute(
"CREATE TEMPORARY TABLE pyformat (id int, name varchar(64), city varchar(64))"
)
params = [(1, u"Jack", u"Boston"), (2, u"Martin", u"Ohio"),
(3, u"James", u"Washington"), (4, u"Rasmus", u"Helsinki"),
(5, u"Andrey", u"Sofia")]
cursor.executemany("INSERT INTO pyformat VALUES (%s,%s,%s)", params)
self.assertEqual(cursor.rowcount, 5)
cursor.execute("commit")
cursor.execute("SELECT name FROM pyformat WHERE id=5")
row = cursor.fetchone()
self.assertEqual(row[0], "Andrey")
def test_update_bulk(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
cursor = self.connection.cursor()
cursor.execute(
"CREATE TEMPORARY TABLE test_update_bulk (a int primary key, b int)"
)
vals = [(i, ) for i in range(1000)]
cursor.executemany("INSERT INTO test_update_bulk VALUES (?, NULL)",
vals)
self.assertEqual(cursor.rowcount, 1000)
self.connection.autocommit = False
cursor.executemany("UPDATE test_update_bulk SET b=2 WHERE a=?", vals)
self.connection.commit()
self.assertEqual(cursor.rowcount, 1000)
self.connection.autocommit = True
del cursor
def test_ping(self):
if is_maxscale():
self.skipTest("MAXSCALE return wrong thread id")
new_conn = create_connection()
id = new_conn.connection_id
self.connection.kill(id)
try:
new_conn.ping()
except mariadb.InterfaceError:
pass
del new_conn
new_conn = create_connection()
new_conn.auto_reconnect = True
id = new_conn.connection_id
self.connection.kill(id)
new_conn.ping()
new_id = new_conn.connection_id
self.assertTrue(id != new_id)
del new_conn
def test_ed25519(self):
if is_skysql():
self.skipTest("Test fail on SkySQL")
default_conf = conf()
if is_maxscale():
self.skipTest("MAXSCALE doesn't support ed25519 for now")
if self.connection.server_version < 100122:
self.skipTest("ed25519 not supported")
conn = self.connection
curs = conn.cursor(buffered=True)
if self.connection.server_name == "localhost":
curs.execute("select * from information_schema.plugins where plugin_name='unix_socket' and plugin_status='ACTIVE'")
if curs.rowcount > 0:
del curs
self.skipTest("unix_socket is active")
cursor = conn.cursor()
try:
cursor.execute("INSTALL SONAME 'auth_ed25519'")
except (mariadb.DatabaseError, mariadb.OperationalError):
self.skipTest("Server couldn't load auth_ed25519")
cursor.execute("DROP USER IF EXISTS eduser")
if self.connection.server_version < 100400:
cursor.execute("CREATE USER eduser@'%' IDENTIFIED VIA ed25519 "
"USING '6aW9C7ENlasUfymtfMvMZZtnkCVlcb1ssxOLJ0kj/AA'")
else:
cursor.execute("CREATE USER eduser@'%' IDENTIFIED VIA ed25519 "
"USING PASSWORD('MySup8%rPassw@ord')")
cursor.execute("GRANT ALL on " + default_conf["database"] + ".* to eduser@'%'")
conn2 = create_connection({"user": "******", "password":
"******"})
cursor.execute("DROP USER IF EXISTS eduser")
try:
create_connection({"user": "******", "password": "******", "plugin_dir": "wrong_plugin_dir"})
self.fail("wrong plugin directory, must not have found authentication plugin")
except (mariadb.OperationalError):
pass
cursor.execute("DROP USER IF EXISTS eduser")
del cursor, conn2
def test_conpy69(self):
if is_skysql():
self.skipTest("skipping on SkySQL")
if is_maxscale():
self.skipTest(
"skipping on Maxscale: bug https://jira.mariadb.org/browse/MXS-3921"
)
conn = create_connection()
conn.autocommit = True
cursor1 = conn.cursor()
cursor1.execute(
"CREATE DATABASE IF NOT EXISTS `中文考试` CHARACTER SET 'utf8mb4'")
cursor1.execute("COMMIT")
default_conf = conf()
default_conf["database"] = "中文考试"
pool = mariadb.ConnectionPool(pool_name="test_conpy69")
try:
pool.set_config(**default_conf)
except:
pool.close()
raise
try:
for i in range(1, 6):
pool.add_connection()
conn = mariadb.connect(pool_name="test_conpy69")
conn.autocommit = True
cursor = conn.cursor()
cursor.execute("select database()")
row = cursor.fetchone()
self.assertEqual(row[0], "中文考试")
cursor.execute(
"CREATE TABLE t1 (a varchar(255)) character set utf8mb4")
cursor.execute("insert into t1 values (?)", ("123.45 中文考试", ))
cursor.execute("select a from t1", buffered=True)
row = cursor.fetchone()
self.assertEqual(row[0], "123.45 中文考试")
cursor1.execute("DROP SCHEMA 中文考试")
finally:
pool.close()
def test_parameter(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
cursor = self.connection.cursor()
cursor.execute(
"CREATE TEMPORARY TABLE test_parameter(a int auto_increment primary key not "
"null, b int, c int, d varchar(20),e date)")
cursor.execute("SET @@autocommit=0")
c = (1, 2, 3, "bar", datetime.date(2018, 11, 11))
list_in = []
for i in range(1, 30000):
row = (i, i, i, "bar", datetime.date(2019, 1, 1))
list_in.append(row)
cursor.executemany(
"INSERT INTO test_parameter VALUES (%s,%s,%s,%s,%s)", list_in)
self.connection.commit()
cursor.execute("SELECT * FROM test_parameter order by a")
list_out = cursor.fetchall()
self.assertEqual(list_in, list_out)
cursor.close()
def test_laststatement(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
cursor = self.connection.cursor(named_tuple=1)
cursor.execute(
"CREATE TEMPORARY TABLE test_laststatement (id int, name varchar(64), "
"city varchar(64))")
self.assertEqual(
cursor.statement,
"CREATE TEMPORARY TABLE test_laststatement (id int, name varchar(64), city varchar(64))"
)
params = [(1, u"Jack", u"Boston"), (2, u"Martin", u"Ohio"),
(3, u"James", u"Washington"), (4, u"Rasmus", u"Helsinki"),
(5, u"Andrey", u"Sofia")]
cursor.executemany("INSERT INTO test_laststatement VALUES (?,?,?)",
params)
cursor.execute("SELECT * FROM test_laststatement ORDER BY id")
self.assertEqual(cursor.statement,
"SELECT * FROM test_laststatement ORDER BY id")
del cursor
def test_pyformat(self):
if is_maxscale():
self.skipTest("MAXSCALE doesn't support BULK yet")
cursor = self.connection.cursor()
cursor.execute(
"CREATE TEMPORARY TABLE pyformat (id int, name varchar(64), city varchar(64))"
)
params = [{
"id": 1,
"name": u"Jack",
"city": u"Boston"
}, {
"id": 2,
"name": u"Martin",
"city": u"Ohio"
}, {
"id": 3,
"name": u"James",
"city": u"Washington"
}, {
"id": 4,
"name": u"Rasmus",
"city": u"Helsinki"
}, {
"id": 5,
"name": u"Andrey",
"city": u"Sofia"
}]
cursor.executemany(
"INSERT INTO pyformat VALUES (%(id)s,%(name)s,%(city)s)", params)
self.assertEqual(cursor.rowcount, 5)
cursor.execute("commit")
cursor.execute("SELECT name FROM pyformat WHERE id=5")
row = cursor.fetchone()
self.assertEqual(row[0], "Andrey")