AWS Lambda中运行PowerShell脚本更新MySQL表报错求助

Hey there, let's break down why your script is failing in Lambda and how to fix it. The core problem here is that your local environment has the MySQL .NET Connector installed (via the MSI), so PowerShell can load the MySql.Data assembly—but AWS Lambda doesn't have that assembly by default. When [System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") runs in Lambda, it returns null, and any subsequent calls to methods on that null object throw the "null-valued expression" error you're seeing.

Here's how to resolve this, plus some fixes for other script issues that might trip you up:

1. Replace the MSI-dependent assembly with a Lambda-compatible solution #

You have two solid options to get the MySQL driver into your Lambda deployment:

Option A: Use the MySqlConnector PowerShell Module #

This is the cleaner approach for PowerShell scripts, as it's designed to be portable and doesn't require manual DLL handling:

• Locally install the module:

  Install-Module -Name MySqlConnector -Scope CurrentUser -Force
  

• Package the module with your Lambda deployment:
  Lambda doesn't have this module pre-installed, so you need to include it in your deployment package. Copy the module folder (usually found at C:\Users\<YourUsername>\Documents\WindowsPowerShell\Modules\MySqlConnector) into your deployment zip's root or a Modules subfolder.
• Update your script to use the module's cmdlets:
  Instead of manually creating connections and commands, use Connect-MySqlConnection and Invoke-MySqlQuery for simpler, more reliable code.

Option B: Package the MySql.Data DLL directly #

If you prefer sticking with the official MySQL .NET assembly:

• Grab the DLL from your local installation:
  Find MySql.Data.dll in your MySQL Connector Net folder (e.g., C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.18\Assemblies\v4.5.2\MySql.Data.dll).
• Add it to your deployment package:
  Put the DLL in the root of your Lambda zip file.
• Update the assembly loading line in your script:
  Replace the outdated LoadWithPartialName with Add-Type (which reliably loads local DLLs):

  Add-Type -Path .\MySql.Data.dll
  

2. Fix other critical script issues #

Your script has a few syntax and logic problems that need fixing too:

Broken Connection String #

Your original connection string uses incorrect PowerShell string concatenation. Replace it with string interpolation (cleaner and less error-prone):

$ConnectionString = "server=$MySQLHost;port=3306;uid=$MySQLAdminUserName;pwd=$MySQLAdminPassword;database=$MySQLDatabase"

SQL Injection Risk & Parameterized Queries #

Never directly insert variables into SQL strings—this is a huge security risk and can break queries if values contain special characters. Use parameterized queries instead:

# Example for the AVAILABLE update
$Query = "UPDATE ng_work SET state = 'AVAILABLE' WHERE ipAddress = @ServerIP"
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$Command.Parameters.AddWithValue("@ServerIP", $s)
# Execute the command (you don't need a DataAdapter for UPDATE/DELETE)
$Command.ExecuteNonQuery()

Note: For UPDATE/DELETE statements, you don't need a MySqlDataAdapter or DataSet—just call ExecuteNonQuery() to run the command.

query user Compatibility in Lambda #

If your Lambda uses the Linux PowerShell runtime, the query user command doesn't exist (it's a Windows-only command). If you're targeting a Windows Lambda runtime, this should work, but you need to ensure the Lambda execution role has network access to your target servers (port 445 for remote query user).

Variable Consistency #

Your script mixes case for variables like $dataAdapter and $DataSet—PowerShell is case-insensitive, but consistent casing makes your code easier to read and debug.

Example Fixed Script (Using MySql.Data DLL) #

Here's a cleaned-up version of your script incorporating the fixes above:

$MySQLAdminUserName = 'user'
$MySQLAdminPassword = 'Cal'
$MySQLDatabase = 'cal'
$MySQLHost = '10.22.115.111'
$servers = @('10.22.32.163') # Wrap IP in quotes to treat as string

try {
    # Load the local MySql.Data DLL
    Add-Type -Path .\MySql.Data.dll

    $ConnectionString = "server=$MySQLHost;port=3306;uid=$MySQLAdminUserName;pwd=$MySQLAdminPassword;database=$MySQLDatabase"
    $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection($ConnectionString)
    $Connection.Open()

    foreach ($s in $servers) {
        $activeSessionFound = $false
        # Capture query user output (handle potential errors if server is unreachable)
        try {
            $userOutput = query user /server:$s 2>&1
            foreach ($ServerLine in $userOutput -split "`n") {
                $ServerLine = $ServerLine.Trim()
                if (-not $ServerLine) { continue }
                # Skip header line
                if ($ServerLine -match 'USERNAME\s+SESSIONNAME') { continue }
                
                $Parsed_Server = $ServerLine -split '\s+'
                if ($Parsed_Server.Count -ge 5) {
                    $state = $Parsed_Server[4]
                    if ($state -eq 'Active') {
                        $activeSessionFound = $true
                        break # No need to check further lines
                    }
                }
            }
        }
        catch {
            Write-Warning "Failed to query server $s : $_"
            $activeSessionFound = $false
        }

        # Update database based on session status
        if ($activeSessionFound) {
            $Query = "UPDATE ng_work SET state = 'AVAILABLE' WHERE ipAddress = @ServerIP"
        }
        else {
            $Query = "UPDATE ng_work SET state = 'STOPPED' WHERE ipAddress = @ServerIP"
        }

        $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
        $Command.Parameters.AddWithValue("@ServerIP", $s)
        $rowsAffected = $Command.ExecuteNonQuery()
        Write-Host "Updated $rowsAffected rows for server $s"
    }

    # Optional: Verify the updates
    $verifyQuery = "SELECT * FROM ng_work"
    $verifyCommand = New-Object MySql.Data.MySqlClient.MySqlCommand($verifyQuery, $Connection)
    $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($verifyCommand)
    $dataSet = New-Object System.Data.DataSet
    $dataAdapter.Fill($dataSet)
    $dataSet.Tables[0] | Format-Table
}
catch {
    Write-Error "Error: $_"
    Write-Error "Detailed error: $($Error[0].Exception)"
}
finally {
    if ($Connection -and $Connection.State -eq 'Open') {
        $Connection.Close()
    }
}

Final Notes #

• Deployment Package: Make sure your Lambda zip includes either the MySqlConnector module or the MySql.Data.dll file.
• Network Access: Ensure Lambda has outbound access to your MySQL server (port 3306) and target Windows servers (port 445 for query user).
• Runtime: Confirm you're using a Windows Lambda runtime if you rely on query user—Linux runtimes don't support this command.

内容的提问来源于stack exchange，提问作者Mallar Sen