drush sql:sync fails with new version of mariadb

drush_sql_sync_mariadb_workaround.md

Workaround for drush sql:sync failing with new version of mariadb

Problem

The command drush sql:sync @remote @self -y fails with the following error:

 [notice] Starting to import dump file onto target database.

In SiteProcess.php line 214:
                                                                                                  
  The command "/app/vendor/bin/drush sql:query --strict=0 --file=/tmp/hsldrupal_20240930_201323.  
  sql.gz --file-delete --uri=https://cua.lndo.site" failed.                                       
                                                                                                  
  Exit Code: 1(General error)                                                                     
                                                                                                  
  Working directory:                                                                              
                                                                                                  
  Output:                                                                                         
  ================                                                                                
                                                                                                  
                                                                                                  
  Error Output:                                                                                   
  ================                                                                                
                                                                                                  
  In SqlCommands.php line 183:                                                                    
                                                                                                  
                                                                                                  
    Query failed. Rerun with --debug to see any error message. ERROR at line 1: Unknown command   
  '\                                                                                              
    -'.

What's going on

In a recent update to mariadb, dump commands added a line of meta data:

/*M!999999\- enable the sandbox mode */

Drush attempts to execute the query (the database dump is a query) and fails because it doesn't understand the new first line.

A solution

Add an additional instruction to the query using an --extra-dump parameter to the drush sql:sync command, à la:

drush sql-sync @<remote alias> @self --yes --extra-dump=" | sed '1d'"

What's going on in that --extra-dump parameter?

In the command drush sql-sync @<remote alias> @self --yes --extra-dump=" | sed '1d'":

• --extra-dump tells mysql that we're adding something to the query in the database received from the remote server
• sed invokes the Stream Editor, a utility that allows changes to be made to a data stream
• 1d means "delete the first line." In this context, 1 refers to the first line, and d is the delete command.

So piping through sed '1d'alters the database file, removing its first line, before mysql gets its hands on the stream.

Summary:

The sed '1d' command deletes the first line of the SQL dump generated by drush sql-sync, likely to remove unnecessary metadata or comments that might interfere with the import process.