GLOBAL.GTID_PURGED and SESSION.SQL_LOG_BIN related SETs in Database Dump Will Cause Db Import to Fail

[davidalger]

Databases coming from Aurora RDS frequently have GTID related SETs which cause problems when importing into a local non-replicated MariaDB container:

$ zgrep -E '\@\@(GLOBAL\.GTID_PURGED|SESSION\.SQL_LOG_BIN)' ./magento.sql.gz 
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED='';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED='';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

The errors these will cause looks like the following:

==> [16:33:13] Importing database
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

[davidalger]

This change appears to have caused issues with database imports. At least two different individuals (and myself!) have run into the following errors:

==> [21:47:37] Importing database
 251MiB 0:28:53 [ 148KiB/s] [==============================================================================================================================================================================================================================================================================================================>] 100%            
ERROR 1064 (42000) at line 17659: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Binary file (standard input) matches' at line 1
ERROR: Command `"${WARDEN_DIR}/bin/warden" env exec -T db mysql -u"${MYSQL_USER}" -p"${MYSQL_PASSWORD}" "${MYSQL_DATABASE}"` on line 45 failed with exit code 1
ERROR: Command `warden db import` on line 231 failed with exit code 1

Why this is occurring, I'm not entirely sure, but as I've never seen it prior to this being introduced this will be reverted in 0.5.3 release.

[davidalger]

I finally figured out what caused the failures when importing databases running through grep; the magical binary file detection grep has.

Grep has the following special flag which seems to resolve this issue:

-a, --text
Process a binary file as if it were text; this is equivalent to the --binary-files=text option.

The issue is because grep is switching to binary mode half way through filtering the piped data, and is echoing Binary file (standard input) matches to the stdout pipe which is passed to MySQL which is then chocking on the data. What I'm not certain of is why grep is detecting binary data on some database dumps (although it's not surprising).

Alternate solution is to use sed to strip the lines (sed is already used to change the definers on functions and triggeres to CURRENT_USER and it works reliably):

LC_ALL=C sed -E '/\@\@(GLOBAL\.GTID_PURGED|SESSION\.SQL_LOG_BIN)/d'

This solution of using sed is the one I plan to implement.