WFA ERROR [Create lunMigrate Job] Create table failed: Warning: Using a password on the command

[COMITSUPPORT]

I've got this error

ERROR [Create lunMigrate Job] Create table failed: Warning: Using a password on the command line interface can be insecure.
12:52:38.373 ERROR [Create lunMigrate Job] Command failed for Workflow '7toC LUN Migrate Job Baseline' with error : Create table failed: Warning: Using a password on the command line interface can be insecure.

I found a bug, 892276

but i was not able to resolve by changing the password either by  changing the service account

I use WFA 3.0.0.0.1P1

[sinhaa]

COMMITSUPPORT,

        I've seen this error before. I believe you are having a command where you are using the mysql.exe present in WFA/mysql/bin folder. It started to appear from WFA3.0 when WFA upgreded its MYSQL version and the new mysql version started to give this warning if password is used in the the mysql.exe cli command line. This new warning has an error code, so it appears as an error in command execution.

There is NO way to supress this warning from mysql side. But there is a way( actually 2 ways) to resolve this issue in WFA with minimal code change. I had resolved it for a customer, but didn't post the solution for general availability. Now I think this should be posted for everyone. 

Also  can you share your command code?

sinhaa

[sinhaa]

COMITSUPPORT

You have reached the right bug, 892276, I've given the public report on the workaround to resolve it. But I see that l didn't mention that you would also need to change a bit of your command code.

The solution is about executing mysql.exe without having to provide the password in the command line. By creating a new login-path with the credentials wfa/Wfa123. Accoring modify the command code too, a bit.

Approach 1:

1. Create a new mysql login-path for wfa 

  Open a cmd prompt as “Administrator” on WFA and do the following.

C:\Program Files\NetApp\WFA\mysql\bin>mysql_config_editor set --login-path=wfa --host=localhost --user=wfa --password

Enter password:

Enter Wfa123 as password.

2. Change properties of NetApp WFA Server service to start it as any Administrator account and not LocalSystem (Default).

Once done you would need to modify your command code in the line where you are using mysql.exe

3. Modify your command code in the line where its using mysql.exe. Now this depends on the code. A sample is below, understand and see if you can can modify it. If you give your command code, i'll return you with the fix.

......

......

$pwd = Get-Location

$mysqlcmd = $pwd.Path+"\..\..\..\..\mysql\bin\mysql.exe"

$MySQLDatabase = 'playground'
$MySQLHost = "localhost"

$MySQLLoginPath = 'wfa'

$MySQLAdminUserName = 'wfa'

$cmd1 = "SELECT * FROM cm_storage.cluster"

# Attempt Command Execution via mysql.exe
$output = & "$mysqlcmd" "--login-path=$MySQLLoginPath" "--user=$MySQLAdminUserName" "--host=$MySQLHost" "-B" "-f" "-e$cmd1" "$MySQLDatabase" 2>&1

.......

.......

Approach 2:

Alternate way is using cmdlet Invoke-MySqlQuery to execute your SQL queries and get data. But this would mean you need to restructure your entire code as the output of this execution is a list of row objects. I would suggest this for new commands, but for existing ones, you can do approach 1.

sinhaa

[COMITSUPPORT]

Yes you are right, it worked.. all fine except the Update lunMigrate Job

Update failed: ERROR 1045 (28000): Access denied for user 'wfa'@'localhost' (using password: NO)

Source code:

param (
[parameter(Mandatory=$true, HelpMessage="Destination Cluster Name")]
[string]$TargetCluster,

[parameter(Mandatory=$true, HelpMessage="Destination Vserver")]
[string]$TargetVserver,

[parameter(Mandatory=$true, HelpMessage="Destination Volume")]
[string]$TargetVolume,

[parameter(Mandatory=$false, HelpMessage="New Job State.")]
[string]$State
)

# Default credentials for playground database
$MySQLAdminUserName = 'wfa'
$MySQLAdminPassword = 'Wfa123'
$MySQLDatabase = 'playground'
$MySQLHost = "localhost"
$MySQLLoginPath = 'wfa'

Get-WFALogger -Info -message $("Obtaining MySQL location.")
# Attempt to find mysql.exe based on the wfa tmp working path, or default location if not found
$pwd = Get-Location
$mysqlcmd = $pwd.Path+"\..\..\..\..\..\mysql\bin\mysql.exe"
$mysqlcmd_found = Test-Path $mysqlcmd
if ($mysqlcmd_found -ne $true)
{
$mysqlcmd = "$($env:ProgramFiles)\NetApp\WFA\mysql\bin\mysql.exe"
}
Get-WFALogger -Info -message $("Preparing Command")

$cmd1 = "UPDATE playground.lunMigrateJob SET " +
"State='$State' "+
"WHERE TargetCluster='$TargetCluster' AND "+
"TargetVserver='$TargetVserver' AND "+
"TargetVolume='$TargetVolume'"

#$key_name + "='" + $key_value + "'"

for ($tries = 0; $tries -lt 20 ; $tries++) {
# Attempt update via mysql.exe
Get-WFALogger -Info -message $("Executing SQL: " + $cmd1)
$output = & "$mysqlcmd" "--user=$MySQLAdminUserName" "--host=$MySQLHost" "-B" "-f" "-e$cmd1" "$MySQLDatabase" 2>&1
$ret = $?
if ($ret -eq $false -and $output -match "ERROR 2013") {
Get-WFALogger -Info -message $("Got error '$output' from mysql. Sleeping for 5s and retrying")
Start-Sleep 5
continue
}
break
}

if ($ret -eq $false) {
throw $("Update failed: $output")
}

[sinhaa]

@

----

Are you sure about this Access Denied Error is coming still after making the changes I suggested. By default the DB user 'wfa' has ALL PRIVILEDGES on playground. So unless any grants modified on a table ( in your case table playground.lunMigrateJob )  this user can do any query updates.

I've tested with a simple UPDATE query on a playground table using a smilar construct as I suggested to you. and it works for me.

See below:

To verify, lets keep WFA out and try if you can run UPDATE queries on this table from mysql CLI : 

1. Check the grants for this user

C:\NetApp2\WFA\mysql\bin>mysql -u wfa -p -e "show grants for wfa"
Enter password: ******
+----------------------------------------------------------------------------------------------------+
| Grants for wfa@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wfa'@'%' IDENTIFIED BY PASSWORD '*BC42C719CEC89F3C2714BBE2E36725A6C49A8A73' |
| GRANT SELECT ON `oci`.* TO 'wfa'@'%' |
| GRANT SELECT ON `people2`.* TO 'wfa'@'%' |
| GRANT ALL PRIVILEGES ON `playground`.* TO 'wfa'@'%' |
| GRANT SELECT ON `dvd`.* TO 'wfa'@'%' |
| GRANT SELECT ON `hypervisor`.* TO 'wfa'@'%' |
| GRANT SELECT ON `deleteit`.* TO 'wfa'@'%' |
| GRANT SELECT ON `performance`.* TO 'wfa'@'%' |
| GRANT SELECT ON `oracle`.* TO 'wfa'@'%' |
| GRANT SELECT ON `people`.* TO 'wfa'@'%' |
| GRANT SELECT ON `vc`.* TO 'wfa'@'%' |
| GRANT SELECT ON `storage`.* TO 'wfa'@'%' |
| GRANT SELECT ON `ad`.* TO 'wfa'@'%' |
| GRANT SELECT ON `oracle_script`.* TO 'wfa'@'%' |
| GRANT SELECT ON `scheme_vserver_comment`.* TO 'wfa'@'%' |
| GRANT SELECT ON `active_directory`.* TO 'wfa'@'%' |
| GRANT SELECT ON `cm_storage`.* TO 'wfa'@'%' |
| GRANT SELECT ON `cm_performance`.* TO 'wfa'@'%' |
+----------------------------------------------------------------------------------------------------+

2. Run the UPDATE query from similar as above and see if that works. If you keep getting the same Access Denied error, we have zeroed down the problem.

3. You can also try to execute the query from the WFA cmdlet.

Go to WFA\bin and double-click on file ps.cmd

 It launches a Powershell console for you. Try the Update query as below:

Invoke-MySqlQuery -Query <your UPDATE query>

The thing is GRANT command is only available to the root user, so unless you get the root password, you can't change the GRANTS for any user.

Once you confirm the behaviour, we can resolve the issue.

sinhaa

[COMITSUPPORT]

Here you are

C:\Program Files\NetApp\WFA\mysql\bin>mysql -u wfa -p -e "show grants for wfa"
Enter password: ******
+-------------------------------------------------------------------------------
---------------------+
| Grants for wfa@%
|
+-------------------------------------------------------------------------------
---------------------+
| GRANT USAGE ON *.* TO 'wfa'@'%' IDENTIFIED BY PASSWORD '*BC42C719CEC89F3C2714B
BE2E36725A6C49A8A73' |
| GRANT SELECT ON `performance`.* TO 'wfa'@'%'
|
| GRANT SELECT ON `vc`.* TO 'wfa'@'%'
|
| GRANT SELECT ON `storage`.* TO 'wfa'@'%'
|
| GRANT ALL PRIVILEGES ON `playground`.* TO 'wfa'@'%'
|
| GRANT SELECT ON `cm_storage`.* TO 'wfa'@'%'
|
| GRANT SELECT ON `cm_performance`.* TO 'wfa'@'%'
|
+-------------------------------------------------------------------------------
---------------------+

C:\Program Files\NetApp\WFA\mysql\bin>mysql --login-path=wfa --host=localhost --user=wfa -B -f -e "select * from playground.play"
ERROR 1146 (42S02) at line 1: Table 'playground.play' doesn't exist

C:\Program Files\NetApp\WFA\mysql\bin>mysql --login-path=wfa --host=localhost --user=wfa -B -f -e "select * from playground"
ERROR 1045 (28000): Access denied for user 'wfa'@'localhost' (using password: NO)

[COMITSUPPORT]

Maybe this happend because i have change the wfa password more than one time. First time i used a non default password. After reverting to the default password to Wfa123 i ve got a prompt to overwrite the WFA directory and i enter yes... I test the workflow and worked except the update. I will try the suggested workaround with the permissions and let you know

[ktim]

You are using an old version of the workflow that is not fully compatible with WFA 3.0. An updated version is available. Please follow private-communities/forum link in fieldportal to obtain the latest version and post any questions there.

[COMITSUPPORT]

Wrong post

[sinhaa]

ktim,COMMITSUPPORT

    This query has been resolved. The correct answer is my 2nd post on this thread.

sinhaa

[ktim]

Hi sinhaa,

The solution was to use the version of the workflow that was designed to work with WFA 3.0 (and is also compatible with 2.2*).

I don't think modifying the WFA install is acceptable for generic WFA workflows, and there are some issues with Invoke-MysqlQuery which make it unusable in some circumstances, in particular if you want to capture errors.

Some of my code will create or upgrade a playground database table. It first attempts a regular INSERT/REPLACE and if that fails will run appropriate CREATE or UPDATE TABLE commands. Invoke-MysqlQuery caches all errors and doesn't pass any distinguishing information back to the caller to determine what happened.

My solution was a mixture of use of Invoke-MysqlQuery (once it was determined to be available) and redirecting and capturing mysql error output so that it didn't cause the command to fail (it seems any data on the error stream makes WFA treat the command as failed).

e.g.

$mysqlcmd = $pwd.Path+"\..\..\..\..\mysql\bin\mysql.exe"
$mysqlcmd_found = Test-Path $mysqlcmd
if ($mysqlcmd_found -ne $true)
{
$mysqlcmd = "$($env:ProgramFiles)\NetApp\WFA\mysql\bin\mysql.exe"
}

try {
$testMysql = New-Object MySql.Data.MySqlClient.MySqlConnection
$testMysql.Close()
$haveMysql = $True
} catch {
$haveMysql = $False
}

Function runSQL ($cmd) {
if ($haveMysql) {
$res = @(Invoke-MysqlQuery -Query $cmd)
} else {
$output = @(& "$mysqlcmd" "--user=$MySQLAdminUserName" "--host=$MySQLHost" "--password=$MySQLAdminPassword" "-B" "-f" "-e$cmd" "$MySQLDatabase" 2>&1)

Regards,

Tim

[sinhaa]

@ I don't think modifying the WFA install is acceptable for generic WFA workflows

---

Ah... This is a matter of individual opinion Tim. As you would know there is always going to more than one possible solution for any programatic issue. I wanted to provide the solution which required the least bit of code change which any user  who didn't understand the command code can also handle it.

The solution I gave required the least bit of code change and  was in-line with MYSQL's guideline of not using the password on the CLI which is correct. And it totally works. 

@ and there are some issues with Invoke-MysqlQuery which make it unusable in some circumstances, in particular if you want to capture errors.

--------

I've fixed all those issues with Invoke-MySqlQuery in WFA3.1RC1. If you get time kindly evalutate it to your satisfaction. If there are still any left, kindly point them out and I'll fix those in 3.1GA.

sinhaa

[COMITSUPPORT]

Hi both you guys

I use the updated data source and the programmaticable changes that sinhaa made through the webex last week.

All the workflows are working fine without importing the lun-migrate dar file Tim provided.

So both version might work with v3.0

I don't know what may happend with v3.1, i have to stay with v3.0.0P1 as looks like a stable version after the changes you made

[jhawkroche]

I am currently recieving the same error in WFA 4.0RC1.  I followed the suggestions in the post, changing --pasword to --login-path and also set the wfa user password in mysql.  I am still not able to complete the workflows.  I now recieve ERROR [Create lunMigrate Job] Insert/replace failed: ERROR 1045 (28000): Access denied for user 'wfa'@'localhost' (using password: NO).  I added --pasword back in to test and still recieve the same error.

I verified user wfa access like the post suggested, it has full access.  I currently have a case open with support and they were not able to resolve the issue.

Please let me know what data and or information you would like to review.

[sinhaa]

@jhawkroche

The TSE who is handling this case is in touch with me. I told him some tests to verify everything is correct. I'll post those here too.

Test1: To verify that the passwordless login has been properly configured for the WFA MYSQL server.

1. Login to WFA windows server and open a cmd console. Open cmd console with option 'Run as Administrator' if the login user is not an Administrator user.
2. Go to <WFA_install_dir>\WFA\mysql\bin and execute the below

    mysql --login-path=wfa --host=localhost --user=wfa -B -f -e "show databases"

It should show all scheme in the results. If it doesn't then you reset your login path as your previous attempt hasn't worked.

If Test1 passed then move to Test2.

Test2: We will now run the same sql query from a WFA command.

1. Login to WFA as admin/architect
2. Add a new Command with the below code:

#####BEGIN CODE

Get-WFALogger -Info -message $("Obtaining MySQL location.")

# Attempt to find mysql.exe based on the wfa tmp working path, or default location if not found

$pwd = Get-Location

$cmd1="show databases"

#$cmd1 = "update playground.play set play.name='abcd' where play.id='1'"

$mysqlcmd = $mysqlcmd = $pwd.Path+"\..\..\..\..\mysql\bin\mysql.exe"

Write-Host $("Preparing Command")

$wfaService=(Get-WmiObject -Class Win32_service -filter "name='NA_WFA_SRV'").StartName

Get-WfaLogger -Info -Message $wfaService

if ( $wfaService -eq "LocalSystem" )

    {

        throw("NetApp WFA Server service is running as LocalSystem. Restart it using an Administrator account and try again.")

    }

#return

$output = & "$mysqlcmd" "--login-path=wfa" "--host=localhost" "--user=wfa" "-B" "-f" "-e$cmd1" "cm_storage" 2>&1

$output | % { Get-WfaLogger -Info -Message $_ }

#### END code

   3. Test this command. If this tests failed then see the error.

If both tests pass it means your WFA setup is completely ready.

If both tests pass then and still you get error on your actual commands, then you made mistake in modifying the command code. Take care of that.

sinhaa