I have a batch file that has been runnign fine for months, during my vacation it started to fail, such nice timing! The temporary fix is to restart the mariadb server every day… not ideal.

I then tried to add a few parameters in the mysqldump which seems to have worked. When I started to change the call to import the data I got a few different errors depending on what I tried. This is the current batch file, later I’ll share the orginal 2 lines of code that used to work but that needs to also have the foreign_key_checks

Batch File

@echo off
setlocal enableDelayedExpansion

REM Get current date in YYYYMMDD format
set "dt=%date:~0,4%%date:~5,2%%date:~8,2%"

REM Define backup path
set "backups_path=%~dp0backups"

REM Create the backups folder if it doesn't exist
if not exist "%backups_path%" mkdir "%backups_path%"

REM Load locations array and location length
call load_locations.bat

REM Loop through each location
for /l %%x in (0, 1, %locations_len%) do (
    REM Build dynamic full path for each location
    set "dump_file=%backups_path%!locations[%%x].brand!_!locations[%%x].name!_!locations[%%x].db!_%dt%.sql"
    set "file_name=!locations[%%x].brand!_!locations[%%x].name!_!locations[%%x].db!_%dt%.sql"

    IF NOT "%~1"=="--no-dump" (
        REM Clean up old backups for this location, keeping only the latest two
        call :printMessage "Cleaning up old backups for location !locations[%%x].db!"
        for /f "skip=2 delims=" %%f in ('dir /b /o-d "%backups_path%!locations[%%x].brand!_!locations[%%x].name!_!locations[%%x].db!_*.sql"') do (
            del "%backups_path%%%f"
            call :printMessage "Deleted old backup file: %%f"
        )

        REM Perform the mysqldump
        call :printMessage "Exporting database !locations[%%x].db! to !file_name!"
        mysqldump --login-path=!locations[%%x].name! --single-transaction --quick --opt --extended-insert --disable-keys --add-drop-table --set-gtid-purged=OFF !locations[%%x].db! > !dump_file!

        IF ERRORLEVEL 1 (
            call :printMessage "mysqldump failed for database !locations[%%x].db!"
            goto:eof
        ) ELSE (
            call :printMessage "Export of !locations[%%x].db! completed successfully."
        )
    )

    IF EXIST !dump_file! (
        call :printMessage "Importing dump file !file_name! into database !locations[%%x].db!"

    REM Build MariaDB import command
    set "cmd_mariadb="C:Program FilesMariaDB 11.2binmariadb.exe" --defaults-extra-file="C:Mattdevkpismariadb_login.ini" !locations[%%x].db! -e "SET foreign_key_checks=0; source !dump_file!; SET foreign_key_checks=1;""

    REM Execute MariaDB import command
    REM call !cmd_mariadb!
    cmd /c %cmd_mariadb%


        IF ERRORLEVEL 1 (
            call :printMessage "MariaDB import failed for !locations[%%x].db!"
            goto:eof
        ) ELSE (
            call :printMessage "Import of !locations[%%x].db! completed successfully."
        )
    ) ELSE (
        call :printMessage "Dump file !dump_file! not found, skipping import."
    )
)

endlocal
goto:eof

REM Function to print messages to both console and log file
:printMessage
echo %dt% - %time:~0,8% - %~1 >> "%~dp0logs.txt"
echo %dt% - %time:~0,8% - %~1
goto:eof

load_location.bat

@echo off

:: Sets location 1 settings
set locations[0].brand=aaa
set locations[0].name=a
set locations[0].db=a

:: Sets location 2 settings
set locations[1].brand=bbb
set locations[1].name=b
set locations[1].db=b

:: Find the # of items to loop through
set locations_len=0 

:Loop 
if defined locations[%locations_len%].name ( 
set /a locations_len+=1
GOTO :Loop 
)

:: Adjust lenght for for loop
set  /a locations_len-=1

mariadb_login.ini

[client]
user=batch_user
password=password
host=127.0.0.1
port=3306

Error message”

C:Mattdevkpis>dump_load_allezup_rgp3.bat 
20240917 - 13:40:47 - Cleaning up old backups for location a 
20240917 - 13:40:47 - Exporting database a to file_20240917.sql 
20240917 - 13:43:03 - Export of a completed successfully. 
20240917 - 13:43:03 - Importing dump file file_20240917.sql into database allezup '""C:Program' is not recognized as an internal or external command, operable program or batch file. 
20240917 - 13:43:03 - MariaDB import failed for a

I’ve tried to use ” and “”

Also tried

    set cmd_mariadb=""C:Program FilesMariaDB 11.2binmariadb.exe" --defaults-extra-file="C:Mattdevkpismariadb_login.ini" !locations[%%x].db! -e "SET foreign_key_checks=0; source '"!dump_file!"'; SET foreign_key_checks=1;""

I’ve used a GPT, I know I know, and couldn’t make much progress:

The previous version of this file was too slow so I tried to add parameters to speed things up and avoid the mariadb server to timeout.

    set cmd_mariadb="C:Program FilesMariaDB 11.2binmariadb.exe" --defaults-extra-file=C:Mattdevkpismariadb_login.ini !locations[%%x].db! -e "source !dump_file!"
    call !cmd_mariadb!