[MDEV-7850] MariaDB doesn't show thread_id for ROW-based events in binlog - JIRA

Details

Type: Bug
Status: Stalled
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.1, 10.0
Fix Version/s: 10.1, 10.0
Component/s: Replication
Labels:
- verified

Description

It seems that MySQL 5.6 includes the thread id in the binary log, which can be very handy. It would be nice if MariaDB also did this.

From MariaDB 10.1.3:

#150326 12:13:53 server id 1  end_log_pos 407   GTID 0-1-55 trans
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=55*//*!*/;
BEGIN
/*!*/;
# at 407
# at 449
#150326 12:13:53 server id 1  end_log_pos 449   Table_map: `test`.`t1` mapped to number 23
#150326 12:13:53 server id 1  end_log_pos 487   Write_rows: table id 23 flags: STMT_END_F

BINLOG '
cVoUVRMBAAAAKgAAAMEBAAAAABcAAAAAAAEABHRlc3QAAnQxAAIDAwAC
cVoUVRcBAAAAJgAAAOcBAAAAABcAAAAAAAEAAv/8AQAAAAMAAAA=
'/*!*/;
### INSERT INTO `test`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
# at 487
#150326 12:13:53 server id 1  end_log_pos 514   Xid = 13
COMMIT/*!*/;
# at 514

From MySQL 5.6.22:

#150326 11:52:25 server id 1  end_log_pos 192 CRC32 0xcf566f36  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1427395945/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 192
#150326 11:52:25 server id 1  end_log_pos 238 CRC32 0x85143a10  Table_map: `test`.`t1` mapped to number 72
# at 238
#150326 11:52:25 server id 1  end_log_pos 282 CRC32 0xc79c4fe7  Write_rows: table id 72 flags: STMT_END_F

BINLOG '
aVUUVRMBAAAALgAAAO4AAAAAAEgAAAAAAAEABHRlc3QAAnQxAAIDAwACEDoUhQ==
aVUUVR4BAAAALAAAABoBAAAAAEgAAAAAAAEAAgAC//wBAAAAAQAAAOdPnMc=
'/*!*/;
### INSERT INTO `test`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
# at 282
#150326 11:52:25 server id 1  end_log_pos 313 CRC32 0xd6b11791  Xid = 15
COMMIT/*!*/;
# at 313

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Kolbe Kegel added a comment - 2015-03-26 21:18

This is incredibly useful if a user finds strange binary log entires and needs to correlate them with the general log or audit log (or slow query log, or...).

Show

Kolbe Kegel added a comment - 2015-03-26 21:18 This is incredibly useful if a user finds strange binary log entires and needs to correlate them with the general log or audit log (or slow query log, or...).

4 older comments

Hide

Permalink

Kolbe Kegel added a comment - 2015-03-26 23:58

Yes, I agree. We should add this back ASAP. It's not acceptable to omit thread_id from the binlog.

Show

Kolbe Kegel added a comment - 2015-03-26 23:58 Yes, I agree. We should add this back ASAP. It's not acceptable to omit thread_id from the binlog.

Hide

Permalink

Elena Stepanova added a comment - 2015-07-03 20:00 - edited

Kristian Nielsen, Sergei Golubchik, Michael Widenius,
Please, please, could it be implemented? It's really difficult to analyze problems without it.
I"m setting it to be fixed in 10.0/10.1 to make sure it's back on the radar, but if it cannot go to 10.0, please feel free to adjust the value.

Show

Elena Stepanova added a comment - 2015-07-03 20:00 - edited Kristian Nielsen , Sergei Golubchik , Michael Widenius , Please, please, could it be implemented? It's really difficult to analyze problems without it. I"m setting it to be fixed in 10.0/10.1 to make sure it's back on the radar, but if it cannot go to 10.0, please feel free to adjust the value.

Hide

Permalink

Kristian Nielsen added a comment - 2015-07-05 12:42

Why not instead put the GTID into the slow log / general log / audit log?
That would seem more useful, and still allows to cross-reference.
And it does not penalise the performance of normal production systems.

Show

Kristian Nielsen added a comment - 2015-07-05 12:42 Why not instead put the GTID into the slow log / general log / audit log? That would seem more useful, and still allows to cross-reference. And it does not penalise the performance of normal production systems.

Hide

Permalink

Elena Stepanova added a comment - 2015-07-05 12:57 - edited

I'm okay with that as well, as long as it's safe for whatever popular tools exist for working with these logs. (I don't know what those tools will be, if any, as I use my hand-made ones; but I assume if there are some, Kolbe Kegel will know).
We know for sure that having thread_id in the binary log won't break anything, because it was there before, and partially still is.

Kristian Nielsen,
On the second thought – but it's impossible?
At least in the general log, queries appear before they are executed, so they can't have GTID yet? And making them appear afterwards would be a disaster, because currently it's often the only way to find out which query the server crashed upon.
Adding them to the slow log or audit log only does not make any sense, the whole point is to have the complete sequence.

Show

Elena Stepanova added a comment - 2015-07-05 12:57 - edited I'm okay with that as well, as long as it's safe for whatever popular tools exist for working with these logs. (I don't know what those tools will be, if any, as I use my hand-made ones; but I assume if there are some, Kolbe Kegel will know). We know for sure that having thread_id in the binary log won't break anything, because it was there before, and partially still is. Kristian Nielsen , On the second thought – but it's impossible? At least in the general log, queries appear before they are executed, so they can't have GTID yet? And making them appear afterwards would be a disaster, because currently it's often the only way to find out which query the server crashed upon. Adding them to the slow log or audit log only does not make any sense, the whole point is to have the complete sequence.

Hide

Permalink

Kolbe Kegel added a comment - 2015-07-06 21:39

Kristian Nielsen, GTID also included in other logs would be good, but it doesn't resolve the issue entirely. GTIDs in the binary log do not help to determine which statements were executed by a particular thread, do they? Being able to see the full sequence of actions executed by a single thread is an important use case.

And Elena's point about the general log is crucial. It is not possible to get GTIDs into the general log; it seems like the only chance is to use thread_id from the binlog to compare with thread_id from the general log.

Show

Kolbe Kegel added a comment - 2015-07-06 21:39 Kristian Nielsen , GTID also included in other logs would be good, but it doesn't resolve the issue entirely. GTIDs in the binary log do not help to determine which statements were executed by a particular thread, do they? Being able to see the full sequence of actions executed by a single thread is an important use case. And Elena's point about the general log is crucial. It is not possible to get GTIDs into the general log; it seems like the only chance is to use thread_id from the binlog to compare with thread_id from the general log.

People

Assignee:

Unassigned

Reporter:

Kolbe Kegel

Votes:

0 Vote for this issue

Watchers:

3 Start watching this issue

Dates

Created:

2015-03-26 21:16

Updated:

2015-07-06 21:39

Agile

View on Board