def test_datatypes(self):
""" test every data type """
conn = self.connections[0]
c = conn.cursor()
yield c.execute("create table test_datatypes (b bit, i int, l bigint, f real, s varchar(32), u varchar(32), bb blob, d date, dt datetime, ts timestamp, td time, t time, st datetime)")
try:
# insert values
v = (True, -3, 123456789012, 5.7, "hello'\" world", u"Espa\xc3\xb1ol", "binary\x00data".encode(conn.charset), datetime.date(1988,2,2), datetime.datetime(2014, 5, 15, 7, 45, 57), datetime.timedelta(5,6), datetime.time(16,32), time.localtime())
yield c.execute("insert into test_datatypes (b,i,l,f,s,u,bb,d,dt,td,t,st) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", v)
yield c.execute("select b,i,l,f,s,u,bb,d,dt,td,t,st from test_datatypes")
r = c.fetchone()
self.assertEqual(util.int2byte(1), r[0])
self.assertEqual(v[1:10], r[1:10])
self.assertEqual(datetime.timedelta(0, 60 * (v[10].hour * 60 + v[10].minute)), r[10])
self.assertEqual(datetime.datetime(*v[-1][:6]), r[-1])
yield c.execute("delete from test_datatypes")
# check nulls
yield c.execute("insert into test_datatypes (b,i,l,f,s,u,bb,d,dt,td,t,st) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", [None] * 12)
yield c.execute("select b,i,l,f,s,u,bb,d,dt,td,t,st from test_datatypes")
r = c.fetchone()
self.assertEqual(tuple([None] * 12), r)
yield c.execute("delete from test_datatypes")
# check sequence type
yield c.execute("insert into test_datatypes (i, l) values (2,4), (6,8), (10,12)")
yield c.execute("select l from test_datatypes where i in %s order by i", ((2,6),))
r = c.fetchall()
self.assertEqual(((4,),(8,)), r)
finally:
yield c.execute("drop table test_datatypes")
def encoded(self, server_id, master_id=0):
"""
server_id: the slave server-id
master_id: usually 0. Appears as "master id" in SHOW SLAVE HOSTS
on the master. Unknown what else it impacts.
"""
# 1 [15] COM_REGISTER_SLAVE
# 4 server-id
# 1 slaves hostname length
# string[$len] slaves hostname
# 1 slaves user len
# string[$len] slaves user
# 1 slaves password len
# string[$len] slaves password
# 2 slaves mysql-port
# 4 replication rank
# 4 master-id
lhostname = len(self.hostname.encode())
lusername = len(self.username.encode())
lpassword = len(self.password.encode())
packet_len = (
1 + # command
4 + # server-id
1 + # hostname length
lhostname + 1 + # username length
lusername + 1 + # password length
lpassword + 2 + # slave mysql port
4 + # replication rank
4) # master-id
MAX_STRING_LEN = 257 # one byte for length + 256 chars
return (struct.pack('<i', packet_len) + int2byte(COM_REGISTER_SLAVE) +
struct.pack('<L', server_id) +
struct.pack('<%dp' % min(MAX_STRING_LEN, lhostname + 1),
self.hostname.encode()) +
struct.pack('<%dp' % min(MAX_STRING_LEN, lusername + 1),
self.username.encode()) +
struct.pack('<%dp' % min(MAX_STRING_LEN, lpassword + 1),
self.password.encode()) +
struct.pack('<H', self.port) + struct.pack('<l', 0) +
struct.pack('<l', master_id))
def _read_table_id(self):
# Table ID is 6 byte
# pad little-endian number
table_id = self.packet.read(6) + int2byte(0) + int2byte(0)
return struct.unpack('<Q', table_id)[0]
def _read_table_id(self):
# Table ID is 6 byte
# pad little-endian number
table_id = self.packet.read(6) + int2byte(0) + int2byte(0)
return struct.unpack('<Q', table_id)[0]
def __connect_to_stream(self):
# log_pos (4) -- position in the binlog-file to start the stream with
# flags (2) BINLOG_DUMP_NON_BLOCK (0 or 1)
# server_id (4) -- server id of this slave
# log_file (string.EOF) -- filename of the binlog on the master
self._stream_connection = yield self.tornado_mysql_wrapper(
**self.__connection_settings)
self.__use_checksum = yield self.__checksum_enabled()
# If checksum is enabled we need to inform the server about the that
# we support it
if self.__use_checksum:
cur = self._stream_connection.cursor()
yield cur.execute(
"set @master_binlog_checksum= @@global.binlog_checksum")
cur.close()
if self.slave_uuid:
cur = self._stream_connection.cursor()
yield cur.execute("set @slave_uuid= '%s'" % self.slave_uuid)
cur.close()
if self.slave_heartbeat:
# 4294967 is documented as the max value for heartbeats
net_timeout = float(
self.__connection_settings.get('read_timeout', 4294967))
# If heartbeat is too low, the connection will disconnect before,
# this is also the behavior in mysql
heartbeat = float(min(net_timeout / 2., self.slave_heartbeat))
if heartbeat > 4294967:
heartbeat = 4294967
# master_heartbeat_period is nanoseconds
heartbeat = int(heartbeat * 1000000000)
cur = self._stream_connection.cursor()
yield cur.execute("set @master_heartbeat_period= %d" % heartbeat)
cur.close()
self._register_slave()
if not self.auto_position:
# only when log_file and log_pos both provided, the position info is
# valid, if not, get the current position from master
if self.log_file is None or self.log_pos is None:
cur = self._stream_connection.cursor()
yield cur.execute("SHOW MASTER STATUS")
self.log_file, self.log_pos = cur.fetchone()[:2]
cur.close()
prelude = struct.pack('<i', len(self.log_file) + 11) \
+ int2byte(COM_BINLOG_DUMP)
if self.__resume_stream:
prelude += struct.pack('<I', self.log_pos)
else:
prelude += struct.pack('<I', 4)
if self.__blocking:
prelude += struct.pack('<h', 0)
else:
prelude += struct.pack('<h', 1)
prelude += struct.pack('<I', self.__server_id)
prelude += self.log_file.encode()
else:
# Format for mysql packet master_auto_position
#
# All fields are little endian
# All fields are unsigned
# Packet length uint 4bytes
# Packet type byte 1byte == 0x1e
# Binlog flags ushort 2bytes == 0 (for retrocompatibilty)
# Server id uint 4bytes
# binlognamesize uint 4bytes
# binlogname str Nbytes N = binlognamesize
# Zeroified
# binlog position uint 4bytes == 4
# payload_size uint 4bytes
# What come next, is the payload, where the slave gtid_executed
# is sent to the master
# n_sid ulong 8bytes == which size is the gtid_set
# | sid uuid 16bytes UUID as a binary
# | n_intervals ulong 8bytes == how many intervals are sent for this gtid
# | | start ulong 8bytes Start position of this interval
# | | stop ulong 8bytes Stop position of this interval
# A gtid set looks like:
# 19d69c1e-ae97-4b8c-a1ef-9e12ba966457:1-3:8-10,
# 1c2aad49-ae92-409a-b4df-d05a03e4702e:42-47:80-100:130-140
#
# In this particular gtid set, 19d69c1e-ae97-4b8c-a1ef-9e12ba966457:1-3:8-10
# is the first member of the set, it is called a gtid.
# In this gtid, 19d69c1e-ae97-4b8c-a1ef-9e12ba966457 is the sid
# and have two intervals, 1-3 and 8-10, 1 is the start position of the first interval
# 3 is the stop position of the first interval.
gtid_set = GtidSet(self.auto_position)
encoded_data_size = gtid_set.encoded_length
header_size = (
2 + # binlog_flags
4 + # server_id
4 + # binlog_name_info_size
4 + # empty binlog name
8 + # binlog_pos_info_size
4) # encoded_data_size
prelude = b'' + struct.pack('<i', header_size + encoded_data_size) \
+ int2byte(COM_BINLOG_DUMP_GTID)
# binlog_flags = 0 (2 bytes)
prelude += struct.pack('<H', 0)
# server_id (4 bytes)
prelude += struct.pack('<I', self.__server_id)
# binlog_name_info_size (4 bytes)
prelude += struct.pack('<I', 3)
# empty_binlog_name (4 bytes)
prelude += b'\0\0\0'
# binlog_pos_info (8 bytes)
prelude += struct.pack('<Q', 4)
# encoded_data_size (4 bytes)
prelude += struct.pack('<I', gtid_set.encoded_length)
# encoded_data
prelude += gtid_set.encoded()
if tornado_mysql.__version__ < "0.6":
self._stream_connection.wfile.write(prelude)
self._stream_connection.wfile.flush()
else:
self._stream_connection._write_bytes(prelude)
self._stream_connection._next_seq_id = 1
self.__connected_stream = True
def __connect_to_stream(self):
# log_pos (4) -- position in the binlog-file to start the stream with
# flags (2) BINLOG_DUMP_NON_BLOCK (0 or 1)
# server_id (4) -- server id of this slave
# log_file (string.EOF) -- filename of the binlog on the master
self._stream_connection = yield tornado_mysql.connect(**self.__connection_settings)
self.__use_checksum = yield self.__checksum_enabled()
# If checksum is enabled we need to inform the server about the that
# we support it
if self.__use_checksum:
cur = self._stream_connection.cursor()
yield cur.execute("set @master_binlog_checksum= @@global.binlog_checksum")
cur.close()
if not self.auto_position:
# only when log_file and log_pos both provided, the position info is
# valid, if not, get the current position from master
if self.log_file is None or self.log_pos is None:
cur = self._stream_connection.cursor()
yield cur.execute("SHOW MASTER STATUS")
self.log_file, self.log_pos = cur.fetchone()[:2]
cur.close()
prelude = struct.pack('<i', len(self.log_file) + 11) \
+ int2byte(COM_BINLOG_DUMP)
if self.__resume_stream:
prelude += struct.pack('<I', self.log_pos)
else:
prelude += struct.pack('<I', 4)
if self.__blocking:
prelude += struct.pack('<h', 0)
else:
prelude += struct.pack('<h', 1)
prelude += struct.pack('<I', self.__server_id)
prelude += self.log_file.encode()
else:
# Format for mysql packet master_auto_position
#
# All fields are little endian
# All fields are unsigned
# Packet length uint 4bytes
# Packet type byte 1byte == 0x1e
# Binlog flags ushort 2bytes == 0 (for retrocompatibilty)
# Server id uint 4bytes
# binlognamesize uint 4bytes
# binlogname str Nbytes N = binlognamesize
# Zeroified
# binlog position uint 4bytes == 4
# payload_size uint 4bytes
# What come next, is the payload, where the slave gtid_executed
# is sent to the master
# n_sid ulong 8bytes == which size is the gtid_set
# | sid uuid 16bytes UUID as a binary
# | n_intervals ulong 8bytes == how many intervals are sent for this gtid
# | | start ulong 8bytes Start position of this interval
# | | stop ulong 8bytes Stop position of this interval
# A gtid set looks like:
# 19d69c1e-ae97-4b8c-a1ef-9e12ba966457:1-3:8-10,
# 1c2aad49-ae92-409a-b4df-d05a03e4702e:42-47:80-100:130-140
#
# In this particular gtid set, 19d69c1e-ae97-4b8c-a1ef-9e12ba966457:1-3:8-10
# is the first member of the set, it is called a gtid.
# In this gtid, 19d69c1e-ae97-4b8c-a1ef-9e12ba966457 is the sid
# and have two intervals, 1-3 and 8-10, 1 is the start position of the first interval
# 3 is the stop position of the first interval.
gtid_set = GtidSet(self.auto_position)
encoded_data_size = gtid_set.encoded_length
header_size = (2 + # binlog_flags
4 + # server_id
4 + # binlog_name_info_size
4 + # empty binlog name
8 + # binlog_pos_info_size
4) # encoded_data_size
prelude = b'' + struct.pack('<i', header_size + encoded_data_size) \
+ int2byte(COM_BINLOG_DUMP_GTID)
# binlog_flags = 0 (2 bytes)
prelude += struct.pack('<H', 0)
# server_id (4 bytes)
prelude += struct.pack('<I', self.__server_id)
# binlog_name_info_size (4 bytes)
prelude += struct.pack('<I', 3)
# empty_binlog_name (4 bytes)
prelude += b'\0\0\0'
# binlog_pos_info (8 bytes)
prelude += struct.pack('<Q', 4)
# encoded_data_size (4 bytes)
prelude += struct.pack('<I', gtid_set.encoded_length)
# encoded_data
prelude += gtid_set.encoded()
if tornado_mysql.__version__ < "0.6":
self._stream_connection.wfile.write(prelude)
self._stream_connection.wfile.flush()
else:
self._stream_connection._write_bytes(prelude)
self.__connected_stream = True
