MySQL update on duplicate key

This topic contains 11 replies, has 3 voices, and was last updated by  Dave Wyatt 1 year, 9 months ago.

  • Author
    Posts
  • #32866

    JC
    Participant

    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?

  • #32867

    Matt Bloomfield
    Participant

    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);"
    
  • #32868

    Dave Wyatt
    Moderator

    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/

  • #32911

    JC
    Participant

    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.

  • #32913

    Dave Wyatt
    Moderator

    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?

  • #32914

    JC
    Participant

    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: (:) [], 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.

  • #32916

    Dave Wyatt
    Moderator

    Looks like you'll need to read up on MYSQL documentation, then. 🙂 A quick search turned this up: http://dev.mysql.com/doc/refman/5.7/en/identifiers.html

    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
    )
    "@
    
  • #32917

    JC
    Participant

    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.

  • #32918

    Dave Wyatt
    Moderator

    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. 🙂

  • #32919

    JC
    Participant

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

  • #32924

    JC
    Participant

    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

  • #32925

    Dave Wyatt
    Moderator

    Cool! 🙂

You must be logged in to reply to this topic.