MariaDB import issue: Error at line 1: Unknown command '\-'

[Mgldvd]

Describe the bug
The bug occurs when running a Drush command to execute a SQL query from a compressed SQL file. The process fails with an error indicating an "Unknown command '-'" at line 1 of the SQL file. This suggests that the SQL file may contain a malformed command or unexpected characters, likely due to improper file handling or corruption during the compression/decompression process. Consequently, the MySQL client encounters an invalid command at the start of the file, leading to the failure of the SQL query execution.

  The command "/var/www/vendor/bin/drush sql:query --yes --strict=0 --file=/tmp/main_20240607_164715.sql.gz --file-delete" 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 '\-'.    

To Reproduce
drush sql-sync @client.dev @client.local --yes

Workaround
This article describes the issue really well
https://gorannikolovski.com/blog/mariadb-import-issue-error-at-line-1-unknown-command

System Configuration

Q	A
Drush version?	12.5.2.0
Drupal version?	10.2.5
PHP version	8.2.18
OS?	Linux

[theteknocat]

We have been having this issue a lot recently, as our server was updated to a newer version of MariaDB that prefixes the dump file with that sandbox command. It would be really great if drush was updated such that any time it runs a DB import it adds the --force parameter.

[weitzman]

Is that --force param supported by older mariadb? Does it do anything beside fix this issue. What is it forcing? These questions need to be answered before drush adds the option. In the meanwhile, you can fix it yourself by adding --extra-dump=--force

[jwag956]

This doesn't work for me - --force needs to be on the import side - not the dump side - as as far as I can tell - drush 12.5 doesn't offer any way to add an option to the import side:

public function import($global_options, $target_dump_path, $targetRecord): void
 {
     $this->logger()->notice(dt('Starting to import dump file onto target database.'));
     $query_options = $global_options + [
         'file' => $target_dump_path,
         'file-delete' => true,
     ];
     $process = $this->processManager()->drush($targetRecord, SqlCommands::QUERY, [], $query_options);

[weitzman]

Ah right. See Examples at https://www.drush.org/latest/latest/commands/sql_connect/. You have --extra there.

[Mgldvd]

correct link i guess https://www.drush.org/latest/commands/sql_connect/

[jwag956]

Yes - however it appears that drush sql:sync can no longer be made to work with latest mariadb. Have to dump then run sql:cli (or connect). So one has to do drush sql:dump , bring the file over, and do drush sql:cli...

It would seem that adding --extra to sql:sync would not only offer a work around here but be generally useful.

[weitzman]

sql:sync already accepts all the options and should pass them to the subcommands. I haven't tried this in long time though.

in any case, folks experiencing this are using mismatched versions of mariadb between local and the server making the dump. thats asking for troubles.

[Mgldvd]

This command works for me:

drush sql-sync @client.dev @client.local --yes --extra-dump=" | sed '1d'"

sed '1d': This is a sed command that deletes the first line of the file.

[weitzman]

Workarounds are at end of https://mariadb.org/mariadb-dump-file-compatibility-change/. Also DDEV users should see https://ddev.com/blog/mariadb-dump-breaking-change/

[LenLamberg]

I encountered this problem when I used ssh to export the database from the remote site and then imported it using DDEV on my local site. I received the error: ERROR at line 1: Unknown command '-' I was able to work around the error by opening the SQL dump file in a text editor and deleting the first line, which is "/*!999999- enable the sandbox mode */ "

[theteknocat]

This --extra-dump value works really well regardless of whether the remote dump includes the "enable sandbox mode":

--extra-dump=" | awk 'NR==1 {if (/enable the sandbox mode/) next} {print}'"

This will skip the first line only if it contains the sandbox mode command and not if it doesn't, to accommodate remote servers running different versions that may or may not include it.

[ThomWilhelm]

Running on Lando locally I had this issue as our host is platform.sh that use Mariadb. The examples above using --extra-dump weren't playing nice with the --structure-tables-list parameter.

So I took a different approach. Instead of trying to remove these lines from the dump file, I upgraded my mariadb client to be the latest 10.6 version that will handle these /*!999999\- enable the sandbox mode */ lines natively. As the latest version in the debian repos seems to be 10.6.11, I had to set up the official mariadb package repo first to grab 10.6.18 which has the fix.

#!/usr/bin/env bash

apt install -y wget apt-transport-https

wget https://r.mariadb.com/downloads/mariadb_repo_setup -O /tmp/mariadb_repo_setup
echo "26e5bf36846003c4fe455713777a4e4a613da0df3b7f74b6dad1cb901f324a84  mariadb_repo_setup" | sha256sum -c -
chmod +x /tmp/mariadb_repo_setup
/tmp/mariadb_repo_setup --mariadb-server-version="mariadb-10.6"

apt clean
apt update
apt install mariadb-client -y

Hopefully this helps someone out!

[ZachC137]

Running on Lando locally I had this issue as our host is platform.sh that use Mariadb. The examples above using --extra-dump weren't playing nice with the --structure-tables-list parameter.

So I took a different approach. Instead of trying to remove these lines from the dump file, I upgraded my mariadb client to be the latest 10.6 version that will handle these /*!999999\- enable the sandbox mode */ lines natively. As the latest version in the debian repos seems to be 10.6.11, I had to set up the official mariadb package repo first to grab 10.6.18 which has the fix.

#!/usr/bin/env bash

apt install -y wget apt-transport-https

wget https://r.mariadb.com/downloads/mariadb_repo_setup -O /tmp/mariadb_repo_setup
echo "26e5bf36846003c4fe455713777a4e4a613da0df3b7f74b6dad1cb901f324a84  mariadb_repo_setup" | sha256sum -c -
chmod +x /tmp/mariadb_repo_setup
/tmp/mariadb_repo_setup --mariadb-server-version="mariadb-10.6"

apt clean
apt update
apt install mariadb-client -y

Hopefully this helps someone out!

Thank you! this worked like plug and play :)