Loading dump file to MySQL

I have been going around in circles to get a dump file uploaded in MySQL. I suppose the problem is redirecting or a delimiter problem in the command to run mysql.exe, but all the same I haven´t been able to nail down a solution. Basically all my different tries at calling mysql.exe are commented in this snippet of code:

SETOUTPATH "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin"
File "C:\ICIS5\Database\MySQL_Dump\dump.sql"
; nsExec::Exec `"$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=root --password="$rootPassword" -e
"LOAD DATA INFILE 'IWIS_CENTRAL_DMS.CSV' INTO TABLE mysql.proc FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' SET db = 'iwis_central_dms';"`
;nsExec::Exec `"$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=root --password="$rootPassword" < "dump.sql" > "$DESKTOP\MySQL.log"`
;Pop $0
ExpandEnvStrings $0 %COMSPEC%
;GetFullPathName /SHORT $1 $PROGRAMFILES\MySQL\MySQL Server 5.1\bin\dump.sql
;Exec '"$0" /C "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe" < $1'
ExecWait `"$0" /C "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=root --password="$rootPassword" < "dump.sql"`
;nsExec::ExecToStack /OEM `"$0" /C "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=root --password="$rootPassword" < "dump.sql"`
;ExecDos::Exec /DETAILED `"$0" /C "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=root --password="$rootPassword" < "dump.sql"`
;ExecDos::Exec /DETAILED `"$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=root --password="$rootPassword" < "dump.sql"`
Pop $0
${If} $BoolShowInfoMessages == "True"
MessageBox MB_OK 'Passing the stored procedures and functions to iwis_central_dms.* with return code (should be zero) {$0}'
${EndIf}
StrCpy $0 "$INSTDIR\SP_install.log"
Push $0
Call DumpLog

Also I have not been successful in getting the return string from the call, which perhaps could reveal something. The DumpLog routine just returns "O" in the first line and "E" in the second line of SP_install.log, so I cant see either what error the mysql call is returning.

Could anybody orient me what might be the problem, please?

I think that perhaps my last post was not so easy to comment on.

I will try to be more concise this time.
The following commands work well when opening mysql as a console application:
cmd>
C:\>cd program files
C:\Program Files>cd mysql
C:\Program Files\MySQL>cd mysql server 5.1
C:\Program Files\MySQL\MySQL Server 5.1>cd bin
C:\Program Files\MySQL\MySQL Server 5.1\bin>dir dump.sql
Volume in drive C has no label.
Volume Serial Number is C023-00CE

Directory of C:\Program Files\MySQL\MySQL Server 5.1\bin

2012/01/17 15:04 26,066 dump.sql
1 File(s) 26,066 bytes
0 Dir(s) 206,608,224,256 bytes free

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql --user=root --password="<pass>" < dump.sql

This command terminates without errors, and the dump is present in the database.
However, my attempts to run it in the installer is like this:

nsExec::Exec `"$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=root --password="$rootPassword" < dump.sql` 

This command when running the installer returns $0 as 1, meaning an error. However I don't know what the error is,
and I have not been successful in logging this. Of course the whole idea of using nsExec is hiding the console from
the user, but I don't know how to get the errors logged so I can see them. Using ExecWait instead of nsExec::Exec a console
is opened, but closes immediately, so that is no clue either.
Can someone help me?

Just as a test have you hardcoded the password just to see if that works?

Hi msroboto,

The password is taken from an input screen where the user types it in. The <pass> just means "the password which I will not reveal to NSIS forum" 😉

Answering your question, no I hadn't tried to hardcode the password, but on your suggestion I just did, and it didn't work (as I would have expected). The passing of the password from the screen to this area of the program, running mysql commands, had already been confirmed with other calls to mysql that did what they were supposed to.

Running the command mentioned above with ExecWait as this:

ExecWait `"$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=root --password="<pass>" < dump.sql`
Pop $0
MessageBox MB_OK "Dump file loaded with ExecWait error info {$0}"

The below is a fraction of the output to the console window, however I wasn't able to capture all the output because of stopping it with the "Pause" key, and the window disappears immediately when stopping execution:

C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe Ver 14.14 Distrib 5.1.45,
for Win32 (ia32)
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe [OPTIONS] [database
]
-?, --help Display this help and exit.
-I, --help Synonym for -?
--auto-rehash Enable automatic rehashing. One doesn't need to use
'rehash' to get table and field completion, but startup
and reconnecting may take a longer time. Disable with
--disable-auto-rehash.
etc.

That the options are displayed perhaps does indicate that mysql.exe does not understand one of the parameters.

Any help on to how I can get the full output logged from the console command mysql.exe would be greatly appreciated. Someone must have had the same problem as me, to not know what errors a console application was reporting?

You could try nsExec::ExecToLog or nsExec::ExecToStack. ExecToLog will put the output into the installer log window, ExecToStack puts each line of output onto the stack for you to pop and process one by one.

I suspect your problem is the input redirection "< dump.sql". You should execute a command shell (cmd.exe, but don't hardcode that, use the environment variable %COMSPEC%)

ReadEnvStr $0 COMSPEC
ExecWait '"$0" /c "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=root --password="<pass>" < dump.sql'

Thanks demiller9

I reckon you are right that it is the redirection <dump.sql that is the problem, since other similar calls to mysql.exe have worked. The suggestion you gave however, did not solve the problem.

The following commands

ReadEnvStr $0 COMSPEC
ExecWait '"$0" /c "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=root --password="<pass>" < dump.sql'
Pop $0
MessageBox MB_OK "Dump file loaded with ExecWait error info {$0}"

... reveals that reading the environment string returned $0 as C:\windows\system32\cmd.exe which is hardly a surprise. But not getting closer to a solution.

I have tried different things with dump.sql, for instance it was a longer file name, and I shortened it to below 8.3 standard. I also experimented with full path inlcuded, or " " delimiters etc. All to no avail. The real solution would probably be either to find another way to specify the input file (dump.sql) to mysql, or to get the specific error message from mysql.exe.

Instead I tried nsExec::ExecToLog and now realize that the output does go to the installer log window, so it is useful. I didn't notice before because in one instance gave only a single error. It then complained about not being able to find a file. Later it said it couldn't find the command "C:\Program" so I tried to get file names with the 8.3 standard. Then I came up with this version:

ReadEnvStr $0 COMSPEC
GetFullPathName /SHORT $1 "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe"
GetFullPathName /SHORT $2 "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\dump.sql"
nsExec::ExecToLog '"$1" --user=root --password="<pass>" < "$2"'

In this case the mysql.exe is run, but showing all the options. It apparently still does not process the dump.sql file. In the following version where there is no space after "<"

nsExec::ExecToLog '"$1" --user=root --password="<pass>" <"$2"'

the output is peculiar:

Output folder: C:\PROGRA~1\MySQL\MYSQLS~1.1\bin
ERROR 1049 (42000): Unknown database '<c:\progra~1\mysql\mysqls~1.1\bin\dump.sql'
Completed

However the database name is already part of the dump.sql file itself, so there should be no need to specify it.

I'm getting closer, but not quite there.

What is the current working directory when you run mysql? If you are not specifying a full path for the dump.sql, it may not be able to find it. (Set the cwd with SetOutPath).

Perhaps the quotes on the executable and the redirected sql file are being mangled by cmd.exe. Try putting "IF 1==1" in front of the executable program name:

ExecWait '"$0" /c IF 1==1 "$ProgramFiles\...\mysql.exe" --blah --blah < "$ProgramFiles\...\dump.sql"'

Because the ExecToLog showed the redirection symbol in that error 1049 message, I think it doesn't understand redirection. You might try ExecToLog with the 'cmd.exe /c if 1==1 ' prefix to the mysql.exe.

Thanks a million, demiller9!

I would not have coped without you to crunch this hard nut. The redirection and passing of parameter is somewhat an occult magical thing that only goes well if you have experience and luck on your side. Thank you very much for the input. 👍 🙂

Paying attention to your comments and also trying to do my own thing, the "final product" looks like this:

SETOUTPATH "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin"
File "C:\ICIS5\Database\MySQL_Dump\iwis_central_dms_SP_dump_2012-01-17.sql"
ExpandEnvStrings $0 %COMSPEC%
GetFullPathName /SHORT $1 "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\mysql.exe"
GetFullPathName /SHORT $2 "$PROGRAMFILES\MySQL\MySQL Server 5.1\bin\iwis_central_dms_SP_dump_2012-01-17.sql"
nsExec::Exec `"$0" /c IF 1==1 "$1" --user=root --password="$rootPassword" < "$2"`
Pop $0
${If} $BoolShowInfoMessages == "True"
MessageBox MB_OK 'Passing the stored procedures and functions to iwis_central_dms.* with return code (should be zero) {$0}'
${EndIf}

The $1 and $2 variables contain the full path of mysql.exe and the dump file, but in 8.3 format after getting the short file name. $rootPassword is a declared variable that gets initialized elsewhere from the input screen from the user.

If anybody is interested in using this solution, let me just give a little more background information. I am using MySQL with MyISAM tables so in a new installation I can copy the tables as any other files without worrying about any stuff in other databases, neither indices etc. that just gets copied along. However, the stored procedures are never stored in the database itself, but in the mysql database. This means that after copying the bulk of data, these must be deployed separately, which is essentially what I'm doing here. I can't distribute the mysql database because the user might have one already, with SPs from other databases, hence this step above that deploys the SPs.

The dump file is obtained from SQLYog asking for a dump from this specific database of only SPs and functions, and that the database name is put implicit in the dump. The dump file is just text file and can be edited without problems if needed. For instance I changed the username within each SP because that was convenient in my situation (others might have other needs).