MySQL update on duplicate key

PowerShell Help
1

I’m inserting some values retrieved from my servers into MySQL db but having problems when it comes to updates. My UNIQUE key is the hostname so what I’m trying to achieve is when my script attempts to insert into a row where the hostname already exists it’ll only update all the other fields. My query is

$query = “INSERT INTO table(‘host_name’,‘value1’,‘value2’,‘value3’,‘date_entered’,‘host_id’) VALUE (‘$host’,‘$value1’,‘$value2’,‘$value3’, now(),Null) ON DUPLICATE KEY UPDATE (host_name=”$host",value1=“$value1”,value2=“$value2”,value3=“$value3”,date_entered=now(),host_id=Null);"
Powershell seems not to like the apostrophes in this line. Can someone suggest a workaround?

2

Looking at the highlighting in the ISE I think it’s the double quote marks that are causing the problem. Try escaping the " with a backtick `

$query = "INSERT INTO table('host_name','value1','value2','value3','date_entered','host_id') VALUE ('$host','$value1','$value2','$value3', now(),Null) ON DUPLICATE KEY UPDATE (host_name=`"$host`",value1=`"$value1`",value2=`"$value2`",value3=`"$value3`",date_entered=now(),host_id=Null);"
3

Looks like you switched from using single-quotes to double-quotes part way through the query. If you need to do that, then you should escape the double-quotes, but I suspect single would work fine:

$query = "INSERT INTO table('host_name','value1','value2','value3','date_entered','host_id') VALUE ('$host','$value1','$value2','$value3', now(),Null) ON DUPLICATE KEY UPDATE (host_name='$host',value1='$value1',value2='$value2',value3='$value3',date_entered=now(),host_id=Null);"

That being said, building a query as a string like this may be leaving you open to SQL injection attacks, if any of those variables are not 100% trusted. The safe way to do this is with a parameterized query, which you can learn about from many places, but here’s a decent example on doing it from PowerShell: https://powershellstation.com/2009/09/15/executing-sql-the-right-way-in-powershell/

4

I replaced all the double quotes within the query with single quotes yet still get the problem. The link to the correct method gets blocked by some internal filter. If I replace the outmost quotes with singles I won’t get my variables interpreted as variables but as strings.

5

I don’t know what to tell you; you still haven’t really told us what the problem is. Can you copy and paste the actual error message?

6

The error states
Exception calling “ExecuteNonQuery” with “0” argument(s): “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ ‘host_name’, ‘value1’, ‘value2’, ‘value3’, ’ at line 2” At C:\test.ps1:70 char:3 + $rowsInserted = $command.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:slight_smile: , MethodInvocationException + FullyQualifiedErrorId : MySqlException

I connect using the following function:

function ConnectToMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {

[void][system.reflection.Assembly]::LoadWithPartialName(“MySql.Data”)

Open Connection

$connStr = “server=” + $MySQLserver + “;port=3306;uid=” + $user + “;pwd=” + $pass + “;database=”+$database+“;Pooling=FALSE”
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand(“USE $database”, $conn)
return $conn

}

function WriteMySQLQuery($conn, [string]$query) {

$command = $conn.CreateCommand()
$command.CommandText = $query
$rowsInserted = $command.ExecuteNonQuery()
$command.Dispose()
if ($rowsInserted) {
return $rowInserted
} else {
return $false
}
}

$conn = ConnectToMySQL $user $pass $MySQLHost $database
$rows = WriteMySQLQuery $conn $query

On different forum it was recommended to use the following syntax for the query

$query=@"
INSERT INTO table(
‘host_name’,
‘value1’,
‘value2’,
‘value3’,
‘date_entered’,
‘host_id’
)
VALUE(
‘$host’,
‘$value1’,
‘$value2’,
‘$value3’,
now(),
Null
)
ON DUPLICATE KEY
UPDATE(
host_name=‘$host’,
value1=‘$value1’,
value2=‘$value2’,
value3=‘$value3’,
date_entered=now(),
host_id=Null
)
"@

Apart for looking much clearer (I see the indentation isn’t visible when I past it here) it also eliminated the ISE underlining my code yet the error remains.

7

Looks like you’ll need to read up on MYSQL documentation, then. :slight_smile: A quick search turned this up: MySQL :: MySQL 5.7 Reference Manual :: 9.2 Schema Object Names

Looks like the single quotation mark is used to quote values, but not identifiers (such as table / column names). You can quote identifiers with backticks (though you’d need to double those up, since backtick is PowerShell’s escape character as well), but in this case, there’s no reason to do so. Try this:

$query=@"
INSERT INTO table(
host_name,
value1,
value2,
value3,
date_entered,
host_id
)
VALUE(
'$host',
'$value1',
'$value2',
'$value3',
now(),
Null
)
ON DUPLICATE KEY
UPDATE(
host_name='$host',
value1='$value1',
value2='$value2',
value3='$value3',
date_entered=now(),
host_id=Null
)
"@
8

That looks better. This time it gets further up to the UPDATE bit. So basically same error but this time near '(
host_name=‘host1’,
value1=‘Y’,
value2=‘F’, …
Must be something it doesn’t like in the quotation there.

9

Well, I linked to the MYSQL documentation page in my last reply. Read through the parts of that which are relevant to what you’re trying to do, and figure it out. :slight_smile:

10

Thanks for getting me on the right track. I’ll see if I can get this working and update the thread when done.

11

What worked is the following

$query = “INSERT INTO table (host_name,value1,value2,value3,date_entered,host_id) VALUE (‘$host’,‘$value1’,‘$value2’,‘$value3’, now(),Null) ON DUPLICATE KEY UPDATE host_name=‘$host’,value1=‘$value1’,value2=‘$value2’,value3=‘$value3’,date_entered=now(),host_id=Null;”

So no parenthesis after DUPLICATE

12

Cool! :slight_smile: