sujatha <sujatha.sivakumar@mariadb.com> writes:
DBA's enable sql_mode='ORACLE' when they would like to use ORACLE's PL/SQL language.
But this is a property of the individual query executed, not a global property of the binlog. The binlog will in general consist of a mix of queries that require sql_mode=oracle, and queries that require default mode.
Even if we turn off the sql_mode='ORACLE' at the start of mysqlbinlog output, the binlog replay will still fail as the ORACLE's PL/SQL syntax is not understood by regular parser.
The mysqlbinlog output needs to set the correct sql_mode=oracle/default for each query, it's not enough to set it at the start of the output. And as Andrei pointed out, this is already done - just for the GTID event, it does not happen. Thus the BEGIN output by mysqlbinlog for GTID event can fail if the last query before that happened to set sql_mode=ORACLE. So it seems to me the natural fix here would be to set a default sql_mode as part of the output for GTID event which matches the syntax used (BEGIN). This makes the mysqlbinlog output for GTID work the same as other binlog events, and might also protect against other strange sql_modes that could interfere with the auto-generated "BEGIN" statement.
A new fix approach is considered. Please refer Commit-id: 'f963fa52ed0' 'mysqlbinlog' tool will replace all 'BEGIN' statements with 'START TRANSACTION' unconditionally.
That may be fine - though as you pointed out yourself to Sergei, there might be backwards compatibility concerns (especially when done in a GA release which gets automatically updated as security updates in production sites) for user scripts that parse the mysqlbinlog putput. So it seems worth considering if the fix of setting sql_mode for GTID event output, just as for other events, would be a more appropriate fix. Hope this helps, - Kristian.