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 __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