B4J Question Mysql.exe in Jshell - running or not running?

[Kevin McLaughlin]

Hello,

This site has been great as an information source for every problem I've run into so far, right up to where I am. Please assist if you can.

I've used B4J to create a mysqldump of the database using the shell. Now I'm trying to use the shell to restore the database. I've been getting intermittent results, so I'm not sure where I'm going wrong, but here is the code that I'm using so far:

Operating on Win10 OS
(Code is snipped to involve anything related to the shell.

  Dim args_Zip, args_SQL As List
   Dim rs As ResultSet
   Dim RestoreDBShell, RestoreDBShell2 As Shell
   Dim ShellResult As ShellSyncResult

  args_SQL.Initialize
   args_SQL.Add("--user=" & DBUserName)       ' Assign username
   args_SQL.Add("-p" & DBUserPassword)     ' Assign Password (Insecure methodology)
   args_SQL.Add("tabdb_schema < "& TabDirectory & RestoreSQLfile)       ' Database to restore to

  Log(args_SQL)

  RestoreDBShell2.Initialize("RestoreDBShell2", MySQL_Path, args_SQL)
  RestoreDBShell2.WorkingDirectory = TabDirectory
   ShellResult = RestoreDBShell2.RunSynchronous(-1)

Log(" Restore Result: (" & ShellResult.Success & ")  " & ShellResult.StdOut)

Log output
(ArrayList) [--user=DBUserName, -pDBUserPassword, tabdb_schema < g:\kevin\coding\tdb\data\tabs\20160917.sql]
Restore Result: (true)

In the same function, but further up, is another shell command to uncompress the .zip which contains the .sql that I'm trying to execute. I've put in a 5 second delay just in case concurrency was an issue. The restore file is small (for now), so it shouldn't take any time.

I can run the command manually when going through cmd.exe and it does restore the database, so I'm pretty sure syntax is good.

When the command is run in B4J, the code continues without errors, but the database does not change. (I've removed one row from a table, before doing the restore in B4J. It doesn't get re-added when I run the line in B4J jshell, but it does get readded from the cmd shell)

The only time I got something which tells me it might not be running, is when I run it with restoreDBShell2.runwithoutputevents(-1) one time. I've recieved some output from std_out that contains the same output as though I've got a syntax error

Can anybody see / advise what I'm missing?

Thank you,

 

[inakigarm]

Another post about jshell states that blank spaces on arg list "are not welcomed"

Can you add the content of mysql_path var?

 

[Kevin McLaughlin]

mysql_path = "c:\program files\mysql\mysql workbench 6.3 ce\mysql.exe"

Interestingly, the mysqldump.exe is in the same directory, and that executed without a hitch.

I have assembled the arguments as per

  args_SQL.Initialize
   args_SQL.Add("--user=" & DBUserName)       ' Assign username
   args_SQL.Add("-p" & DBUserPassword)     ' Assign Password (Insecure methodology)
'   'args_SQL.Add("-v")
   args_SQL.Add("tabdb_schema")       ' Database to restore to
'   args_SQL.Add("tabdb_schema < "& TabDirectory & RestoreSQLfile)       ' Database to restore to
   args_SQL.Add("<")
'   args_SQL.Add("-e")
   args_SQL.Add(TabDirectory & RestoreSQLfile)     ' Path of the SQL file

No change in results. As you can see, I tried with -e as well. When I did, it consistantly came back with the help in std_out - mainly because it didn't like the pathname.

I'm open to other ways of executing if you have any of those tricks up your sleave.

Thanks,

 

[Erel]

The problem is probably with the redirection (<). Try to run it with cmd /c. This means that the executable is cmd.exe.
Something like:

Dim args As  List = Array ("/c", MySQL_Path, "--user=" & username", ...)
shell.Initialize(..., "cmd.exe", args)

 

[Kevin McLaughlin]

Unfortunately, no success. Here is where I'm at Codewise:

   args_SQL.Initialize
   args_SQL.Add("/c")
   args_SQL.Add(MySQL_Path)
   args_SQL.Add("--user=" & DBUserName)       ' Assign username
   args_SQL.Add("-p" & DBUserPassword)     ' Assign Password (Insecure methodology)
   args_SQL.Add("tabdb_schema < "& TabDirectory & RestoreSQLfile)       ' Database to restore to

  Log(args_SQL)
   
    Delay (1000)
   
   RestoreDBShell2.Initialize("RestoreDBShell2", "cmd.exe", args_SQL)
   RestoreDBShell2.WorkingDirectory = TabDirectory
   ShellResult = RestoreDBShell2.RunSynchronous(-1)

I've tried playing with the case - no change.

It hasn't made a difference if it is running or not, but I do have the Workbench running, I also am not changing the state of my connection to the database.
Since the SQL is droping tables if they exist, do I have to change the state of my connection to the database to perform a restore from a file?

Since both of these methods haven't worked, maybe there's just something stopping the SQL portion of the restore from running?

Thanks,
-Kev

 

[Erel]

Have you tested it with InitializeDoNotHandleQuotes ?

 

[Kevin McLaughlin]

Yes. Unfortunately, No new symptoms (good or bad) to report.

 

[Kevin McLaughlin]

New information to report. I tried two workarounds:

  ' Batch file Attempt
   RestoreBatchFile = RestoreFile.SubString2(0, RestoreFile.IndexOf(".zip")) & ".bat"
   If File.Exists(TabDirectory, RestoreBatchFile) Then File.Delete(TabDirectory, RestoreBatchFile)
   File.WriteString(TabDirectory, RestoreBatchFile, $"""$ & MySQL_Path & $"" --user="$ & DBUserName & " -p" & DBUserPassword & " tabdb_schema < "& TabDirectory & RestoreSQLfile)
   
   args_SQL.Initialize
   args_SQL.Add("/c")
   args_SQL.Add(RestoreBatchFile)
   
   RestoreDBShell2.Initialize("RestoreDBShell2", "cmd.exe", args_SQL)
   'RestoreDBShell2.Initialize("RestoreDBShell2", RestoreBatchFile, Null)
   RestoreDBShell2.WorkingDirectory = TabDirectory
   RestoreDBShell2.RunWithOutputEvents(-1)

Maybe this will tip off the cause of the problem I was having.

As you can see from above, I wrote my command into a batch script and then running that. The only change I had to make to the command was putting the quotes around the MySQL_Path variable.
Unfortunately, That didn't work either
The batch file works when I execute from a command shell or through windows explorer
However, when I ran the batch script using the "/c" and cmd.exe, the restore worked.

I tried unsuccessfully trying to trace back to see what the final fix was. I tried running:

    args_SQL.Initialize
'   args_SQL.Add("/c")
'   args_SQL.Add(RestoreBatchFile)
   
   'args_SQL.Add(MySQL_Path)
   args_SQL.Add("--user=" & DBUserName)       ' Assign username
   args_SQL.Add("-p" & DBUserPassword)     ' Assign Password (Insecure methodology)
   'args_SQL.Add("tabdb_schema < "& TabDirectory & RestoreSQLfile)       ' Database to restore to
   
'   args_SQL.Add("-v")
   args_SQL.Add("tabdb_schema")       ' Database to restore to
   args_SQL.Add("<")
'   args_SQL.Add("-e")
   args_SQL.Add(RestoreSQLfile)     ' Path of the SQL file

  RestoreDBShell.Initialize("RestoreDBShell", $"""$ & MySQL_Path & $"""$, args_SQL)
   RestoreDBShell.WorkingDirectory = TabDirectory
   ShellResult = RestoreDBShell.RunSynchronous(-1)

I got the mysql.exe help from std_out. The same symptoms as if I didn't pass the arguments

So, I can't tell what the root problem was that I was doing wrong. Can you tell me what you see here?

At least I have a working version to continue from.

Thanks,