hyphen1370: Hi. Most probably it was fixed. I have not seen a similar stack for a long time. I could find anything that fits to the oracle bug stack in Jira.
MariaDB 10.2-10.6 with --base64-output option mysqlbinlog returns output, that cannot not be parsed by mysql client (will return syntax error). mysqlbinlog v.3.3 (in 10.2) returns a warning, but option is still not deprecated, while in 3.5 there is no warning, but otherwise the same behaviour.
With --base64-output=auto works as expected
WARNING: The --base64-output=always flag and the --base64-output flag (with '=MODE' omitted), are deprecated. The output generated when these flags are used cannot be parsed by mysql 5.6.0 and later. The flags will be removed in a future version. Please use --base64-output=auto instead.
|
example of the output:
#210323 10:55:08 server id 1 end_log_pos 453 CRC32 0x267c0178 /LpZYAIBAAAAVwAAAMUBAAAIAA4AAAAAAAAABAAAGgAAAAAAAAEAACBUAAAAAAYDc3RkBCEAIQAI
|
AG15ZGIAQ1JFQVRFIERBVEFCQVNFIG15ZGJ4AXwm
|
# at 453
|
#210323 10:56:50 server id 1 end_log_pos 495 CRC32 0x2a10fec0 GTID 0-1-2 ddl
|
Thanks.
I am using 10.2.37 and tried to restore a binlog with -d option, which requested to use this option by the product call "CommVault". There is the command they use:
mysqlbinlog -d "act" /opt/commvault/iDataAgent64/jobResults/test/mysql-bin.012176 --result-file="/tmp/test7.sql"
It produces the sql file that doesn't include "BINLOG" keyword
However, when using same command without -d option, it contains the "BINLOG" keyword and I restored the result file. I have uploaded mysql-bin.012176 to the ftp private folder.
Could you take a look?
Thanks
hyphen1370, -d option does not cause the loss of BINLOG ' prefix, the argument-less base64-output and base64-output=always do, as my colleague reported.
To help you fast, make sure to not pass neither of the two to mysqlbinlog also implicitly through my.cnf.
While the argument-less base64-output is documented to have the default value of 'auto' this is not actually the case per my testing so must be fixed.
sachin.setiya.007: --base64-output must have 'auto' default which is not the case now.
mysqlbinlog -d "act" /opt/commvault/iDataAgent64/jobResults/test/mysql-bin.012176 --base64-output=auto --result-file="/tmp/test7.sql" still returned statement without binlog keyword, any other sugguestion?
Thanks
hyphen1370,
> any other sugguestion?
To provide your version 
. And it could be that something confused your testing.
I have just tested against 10.2.36 that you were interested in the description, to NOT confirm --base64-output=auto works incorrectly. It works fine. Here is an example of well-formed output (I cut off unneeded head and tail parts of the total output).
../client/mysqlbinlog --base64-output=auto /dev/shm/var/mysqld.1/data/master-bin.000001
|
|
|
=>
|
# at 329
|
#210504 12:17:28 server id 1 end_log_pos 371 CRC32 0x26539db5 GTID 0-1-1 ddl
|
/*!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=1*//*!*/;
|
# at 371
|
#210504 12:17:28 server id 1 end_log_pos 484 CRC32 0x337b3ca3 Query thread_id=10 exec_time=0 error_code=0
|
use `test`/*!*/;
|
SET TIMESTAMP=1620119848/*!*/;
|
SET @@session.pseudo_thread_id=10/*!*/;
|
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
|
SET @@session.sql_mode=1411383296/*!*/;
|
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
|
/*!\C latin1 *//*!*/;
|
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
|
SET @@session.lc_time_names=0/*!*/;
|
SET @@session.collation_database=DEFAULT/*!*/;
|
DROP TABLE `t1`,`t2` /* generated by server */
|
/*!*/;
|
# at 484
|
#210504 12:17:28 server id 1 end_log_pos 526 CRC32 0xfc32093d GTID 0-1-2 ddl
|
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
|
# at 526
|
#210504 12:17:28 server id 1 end_log_pos 642 CRC32 0x695547b8 Query thread_id=10 exec_time=0 error_code=0
|
SET TIMESTAMP=1620119848/*!*/;
|
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB
|
/*!*/;
|
# at 642
|
#210504 12:17:28 server id 1 end_log_pos 684 CRC32 0x3b37c66c GTID 0-1-3 trans
|
/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
|
BEGIN
|
/*!*/;
|
# at 684
|
# at 734
|
#210504 12:17:28 server id 1 end_log_pos 734 CRC32 0x0b0ce9b5 Annotate_rows:
|
#Q> INSERT INTO t1 VALUES (100)
|
#210504 12:17:28 server id 1 end_log_pos 779 CRC32 0xae1e40ad Table_map: `test`.`t1` mapped to number 35
|
# at 779
|
#210504 12:17:28 server id 1 end_log_pos 817 CRC32 0x0ac882c4 Write_rows: table id 35 flags: STMT_END_F
|
|
|
BINLOG '
|
KBGRYBMBAAAALQAAAAsDAAAAACMAAAAAAAEABHRlc3QAAnQxAAEDAACtQB6u
|
KBGRYBcBAAAAJgAAADEDAAAAACMAAAAAAAEAAf/+ZAAAAMSCyAo=
|
'/*!*/;
|
# at 817
|
#210504 12:17:28 server id 1 end_log_pos 848 CRC32 0xca9221ad Xid = 2263
|
COMMIT/*!*/;
|
|
Elkin, is that a bug at all? I've run mysqlbinlog -vv --base64-output=auto mysql-bin.012176 — there were no row events in the database act, only table map events. So it's no surprise, that -d act shows nothing — because there is nothing.
serg, --base64-output=always, or just --base64-output - both has been planned for deprecation are buggy to produce unparsable output.
Hi Andrei,
I have implemented your suggested MTR test to compare --base64-output=auto against no specification.
The full patch is here
The buildbot link is here
Could you please compose summaries for each of the remained option values for 10.6.
Fix is implemented in 10.5.11 with no merge conflict observed when cherry-picking to 10.6.
10.6 Patch: https://github.com/MariaDB/server/commit/13e782eb84441d0c2ba1ce9d0109a7534d1995fa
Documentation updates for 10.6 to come.
MySQL 8.0 removed the 'always' option from --base64-output, and this fix follows that trend and removed the option from MariaDB. Additionally, now when --base64-output is specified, a value must be specified with it. When the flag is provided without a value, an error is presented.