How do you get the actual query for insert, update and delete events?

[Farhan-Karaz]

With this code:

	for {
		fmt.Println("\nWaiting for next event...")
		e, err := bl.NextEvent()
		fmt.Println("Got event:", e.Header.EventType)
		if err != nil {
			if err == io.EOF {
				break
			}
			panic(err)
		}

		if e.Header.EventType.IsWriteRows() {
			fmt.Println("EVENT TYPE: insert rows")
		} else if e.Header.EventType.IsUpdateRows() {
			fmt.Println("EVENT TYPE: update rows")
		} else if e.Header.EventType.IsDeleteRows() {
			fmt.Println("EVENT TYPE: delete rows")
		}

		qe, ok := e.Data.(binlog.QueryEvent)
		if ok {
			fmt.Println("QUERY EVENT:",qe.Query)
		}
		rse, ok := e.Data.(binlog.RowsEvent)
		if ok {
			fmt.Println("ROWS EVENT:",rse.TableMap.TableName, rse.TableMap.SchemaName)
		}
		tme, ok := e.Data.(binlog.TableMapEvent)
		if ok {
			fmt.Println("TABLE MAP EVENT:", tme.TableName, tme.SchemaName)
		}
	}

I get the following output:

Waiting for next event...
Got event: query
QUERY EVENT: BEGIN

Waiting for next event...
Got event: tableMap
TABLE MAP EVENT: test_table test_db

Waiting for next event...
Got event: writeRowsV2
EVENT TYPE: insert rows
ROWS EVENT: test_table test_db

How do I get the actual values inserted? Even more importantly, how do I get the full query?

[santhosh-tekuri]

mysql has 3 types of binlog formats:

• statement-based logging: this generates query events
• row-based logging: this generates rows events
• mixed logging: this generates both query and rows events

to check what type of binlog format is set:

mysql> SHOW VARIABLES LIKE 'binlog_format';

to change the format:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

you can read more at:

• https://dev.mysql.com/doc/refman/5.7/en/binary-log-formats.html
• https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html

in your case, it seems your mysql server is using row-based logging

[Farhan-Karaz]

Actually it is using row based logging, I just checked. Does row based logging not include the actual values? Looking at the actual binary logs, it has the correct SQL queries we need.

[santhosh-tekuri]

typo: I meant your server has row-based. change it to either mixed or statement-based to get query events

[santhosh-tekuri]

row based logging includes actual values.

see the cli: https://github.com/santhosh-tekuri/binlog/blob/main/cmd/binlog/main.go

[santhosh-tekuri]

I suggest to run the cli with different options and play with it.

next understand cli code. it has all the code that answers your query

[Farhan-Karaz]

I noticed that there is also a NextRow() function, but there seems to be an internal error.
Running this:

for {
	bl.NextRow()
}

yields this:

panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x0 addr=0xb8 pc=0x4ce429]

goroutine 1 [running]:
github.com/santhosh-tekuri/binlog.nextRow(0xc00014c000?)
        C:/Users/user/go/pkg/mod/github.com/santhosh-tekuri/[email protected]/rbr.go:309 +0x29
github.com/santhosh-tekuri/binlog.(*Remote).NextRow(...)
        C:/Users/user/go/pkg/mod/github.com/santhosh-tekuri/[email protected]/remote.go:242
main.main()
        C:/Users/user/main.go:42 +0xc9
exit status 2

It seems to be happening because bl.binlogReader in:

// remote.go:241
func (bl *Remote) NextRow() (values []interface{}, valuesBeforeUpdate []interface{}, err error) {
	return nextRow(bl.binlogReader)
}

...is always nil.

[Farhan-Karaz]

Ah, I see. You have to call bl.NextEvent() before calling bl.NextRow() because the former populates the binlogReader *reader field.

[santhosh-tekuri]

also it should be called only if the event you got is rows event