Merlinovich
18th January 2012 23:31 UTC

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

Merlinovich
20th January 2012 00:24 UTC

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\MySQLServer5.1\bin\mysql.exe"--user=root--password="$rootPassword"<dump.sql`

msroboto
20th January 2012 16:26 UTC

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

Merlinovich
20th January 2012 16:38 UTC

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.

Merlinovich
23rd January 2012 02:38 UTC

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?

demiller9
23rd January 2012 05:32 UTC

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'

Merlinovich
23rd January 2012 08:12 UTC

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.

demiller9
23rd January 2012 14:17 UTC

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"'

Merlinovich
23rd January 2012 21:09 UTC

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. :up: :)

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}