sql error when updating a page

What database are your running? I found an issue reported with the db_affected_rows and changed that line to the recommendation. I can't test it though because it works with my version of MySQL.

I have committed the change to dev. So try that version and let me know if the problem persists.

Tom

no response, assumed working fine

Hello,

I have the same bug. I have mysql-client-5.1 installed on a Ubuntu 10.10 server. Do you think to resolve this warning for Mysql 5.1 ?

Thx for this usefull module.

The fix works for me on MySQL 5.1 however, the fix was only in the dev version. I updated the revision to 2.2 to get it into the official release.

If you try that one it should fix the problem.

Reopened: I have the same problem with module version 6.x-2.2.
Error message: User warning: Duplicate entry '1' for key 'PRIMARY' query: INSERT INTO contentoptimizer (aid,keyword) VALUES (1,'MYKEYWORD') in _db_query()
I'm using pressflow 6.22, mysql client 5.1.41 (mysqli)

Same ^^

Getting the same error using 2.2. Any updates? Using in regard to the whole SEO Tools module.

I suffered from this same problem.

Some surfing around led me to http://drupal.org/node/805858, the long and the short of which is that my particular MySQL library is such that the call to db_affected_rows is returning zero when the keyword has not changed. (Content optimizer is expecting that db_affected_rows should return 1 if there is already an entry in the table with that particular aid.)

This function uses the same pattern as variable_set does, the relevant code of which is:

  db_query("UPDATE {variable} SET value = '%s' WHERE name = '%s'", $serialized_value, $name);
  if (!db_affected_rows()) {
    @db_query("INSERT INTO {variable} (name, value) VALUES ('%s', '%s')", $name, $serialized_value);
  }

Note the at sign in the second call to db_query. This silences the warnings. Thus I made the same change to contentoptimizer.module, specifically:

function contentoptimizer_set_keyword($aid, $keyword) {
  (snip)
  if (!(db_affected_rows() > 0)) {
    $sql = "INSERT INTO {contentoptimizer}(aid,keyword) VALUES (%d,'%s')";
    @db_query($sql, $aid, $keyword);
  }
}

Hope this helps.

I have a non latin website with contentoptimizer 6.x 2.2

I get the same type of error.

Here is the code my site runs:

/**
 * Saves keyword to databse
 * 
 * @param int $aid
 *   analysis id of analysis keyword is associated with
 * @param str $keyword
 *   keyword to save
 */
function contentoptimizer_set_keyword($aid, $keyword) {
  $sql = "
    UPDATE {contentoptimizer}
    SET keyword = '%s'
    WHERE aid = %d
  ";
  db_query($sql, $keyword, $aid);
  if (!(db_affected_rows() > 0)) {
    $sql = "
      INSERT INTO {contentoptimizer}
      (aid,keyword) VALUES
      (%d,'%s')
    ";
    db_query($sql, $aid, $keyword);
  }
}

I noticed that the error is ike this :

user warning: Duplicate entry '0' for key 'PRIMARY' query: INSERT INTO dr_contentoptimizer (aid,keyword) VALUES (0,'') in /var/www... sites/all/modules/contentoptimizer/contentoptimizer.module on line 84.

which means php is trying to update/insert with an empty '' $keyword variable...

The fix is to add a check for emptyness:

/**
 * Saves keyword to databse
 * 
 * @param int $aid
 *   analysis id of analysis keyword is associated with
 * @param str $keyword
 *   keyword to save
 */
function contentoptimizer_set_keyword($aid, $keyword) {

  if ( ! empty($keyword) ) {

  $sql = "
    UPDATE {contentoptimizer}
    SET keyword = '%s'
    WHERE aid = %d
  ";
  db_query($sql, $keyword, $aid);
  if (!(db_affected_rows() > 0)) {
    $sql = "
      INSERT INTO {contentoptimizer}
      (aid,keyword) VALUES
      (%d,'%s')
    ";
    db_query($sql, $aid, $keyword);
  }

  }

}

tested - no more errors
yay!

solution in #11 fixed it for me, thanks!

I am closing this issue because Drupal 6.x has reached EOL and is no longer supported as of February 26th, 2016.