db_affected_rows return
0 if there is no update. mean everything is same in update query.
eg: update node set status=1 where nid=1;

-1 if record does n't exists.
eg: update node set status=1 where nid=-9;

check db_affected_rows function

http://api.drupal.org/api/drupal/includes%21database.mysqli.inc/function...

it call following php function

http://php.net/manual/en/mysqli.affected-rows.php


 // Try to update. On fail, try inserting.
  $return = drupal_write_record('views_natural_sort', $record, array('nid', 'field'));
  if (!db_affected_rows()) {
    $return = drupal_write_record('views_natural_sort', $record);
  }

must be like


 // Try to update. On fail, try inserting.
  $return = drupal_write_record('views_natural_sort', $record, array('nid', 'field'));
  if (db_affected_rows()==-1) {
    $return = drupal_write_record('views_natural_sort', $record);
  }
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

pdrake’s picture

I do not believe that is correct, based on the documentation. From the php.net documentation for mysqli_affected_rows: "Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error."

lalit774’s picture

FileSize
642 bytes

May be you right. but i don't think drupal control output of this function db_affected_rows

Please try to run attached script. put this file in any d6 root. after that run through browser. please remove .txt from end of file

<drupal 6 website url>/test.php

generalredneck’s picture

I just got done putting a dmp in my code to see what was returned by db_affected_rows at that point... in the case that the update fails (I create a new node), it returns a 0... in the case that the update does work (I edit an existing node)... it returns a 1. So it appears my code is correct.

generalredneck’s picture

This is the output of your script:

2
----start-----
1
----end
Affected rows (INSERT): 0
lalit774’s picture

Yes agree.

if we looking about end row

Affected rows (INSERT): 0.

The above 0 is due to there is no modification. data has already updated. i.e

update node set status=1 where nid=1;

Case 1: Now if status is already 1 for node id 1. the db_affected_rows should be return 0;

Case 2: if we did not find node 1 or we got some error in query(like table missing, field miss etc) then it should return -1.

Case 2 is good. no need to discuss.

for Case 1: it will try to insert record again.

May be this is related with php version. but on my client production site in watchdog. we got lot of duplicate insert error for "Views Natural Sort". i think this is related with this function db_affected_rows.

generalredneck’s picture

AH... got ya...

I was wondering where this all came from as you hadn't explained the situation earlier, therefore I thought you might be just browsing through the code and saw that.

Since that's the case, I'll do what I can to help you out. Is the error actually on the views_natural_sort table? it could be that there is 2 transactions happening at the exact same time possibly if it's high traffic... I'll have to think on it some more...

@pdrake any ideas? I know you are pretty good with performance scenarios like this.

lalit774’s picture

FileSize
26.69 KB
74.78 KB

When ever i run one script. it show me following errors

generalredneck’s picture

I take it that's a custom update script?

hmm...

Also if you were updating modules, can you tell me what versions you were upgrading from and to? like was it 6.x-1.0 to 6.x-1.3?

I've looked through my logs and I haven't gotten that particular error yet.

I'm still confounded...

lalit774’s picture

currently we are using views natural sort 6.x-1.1 version. i don't know about past. i have tried to move on your new version 6.x-1.3. but i got error due to title is embedded by numbers. i got that problem because my node title is in apace solr. so i did not get result without embedded number on front end(browser). that is another story.

generalredneck’s picture

@lalit774,
With the information I have, I can't get it to break... The only way I think it could happen is if for some reason nids were being reused...

I did find a bug that when you delete an item, the corresponding views_natural_sort entry is not deleted.

So for instance if you ever come across one of those ids that have been deleted before... then it might happen... but I don't see how that can happen unless a script or something is manually updating the nids of nodes to the nids of nodes that are deleted...

Like I said, I can't really be sure...

Any of this sparking any ideas for you?

Also about the Number thing... I responded back in #1844642: numeric title is not working properly. and haven't heard anything back... but I did take the hint to make documentation which I'll post the link to in the other issue.

generalredneck’s picture

Come to think of it... actually even if you were reusing IDs... this shouldn't be a problem... it should just simply update, just like if you were editing the node...

I'm running out of ideas...

lalit774’s picture

i have done some rnd regarding db_affected_rows and drupal_write_record functions in drupal 6. There is some glitch in drupal 6 to use db_affected_rows function. There are some queries run of every page refresh. Those queries overwrite value of db_affected_rows.

#2 script provides output of #4. if you change any thing in #2 script you will get same result. (i.e i have change table name in drupal_write_record function. i have put views_natural_sort_1 table name. that table is not existed in database. it give me same(#2) result.)

$return = drupal_write_record('views_natural_sort1', $record,'nid');

drupal_write_record function return following things in $return

0 query error
1 SAVED_NEW
2 SAVED_UPDATED

// Try to update. On fail, try inserting.
  $return = drupal_write_record('views_natural_sort', $record, array('nid', 'field'));
  if (!db_affected_rows()) {
    $return = drupal_write_record('views_natural_sort', $record);
  }

must be like

// Try to update. On fail, try inserting.
  $return = drupal_write_record('views_natural_sort', $record, array('nid', 'field'));
  if ($return==1) {
    $return = drupal_write_record('views_natural_sort', $record);
  }

Please on php display error in php.ini. After that you can see errors.

#7 i have run custom script. that script do node_load and update some values in node. after that it run node_save function. in views_natural_sort there is node_api hook. which is try to insert a record every time when node_save run..

generalredneck’s picture

Sounds easy enough. let me make the change and do some light testing to make sure it doesn't change behavior besides that of which you are seeing if this all works out, I'll at the very least give you authorship of the commit.

generalredneck’s picture

So here's the deal, reguardless of whether the row is actually updated or not, drupal_write_record sends back SAVED_UPDATED if you pass in items for the keys array for the 3rd parameter... in this case array('nid', 'field').

With that said, I was trying to avoid doing a select before the write because I hate making unnecessary queries. It looks like I'll have to at the very least make 1 though...

Try this patch and let me know if you have any better luck... My testing shows it working for normal circumstances... maybe even a little bit faster than it used to be during the index rebuild. The last part may not work for you since you are using 1.1. This was built off of 6.x-1.x-dev as your problem will still exist in that version as well.

lalit774’s picture

I have tried to find out other best method to do best solution. i have seen your patch. but sorry i have different point of view. we should not put node id in a array or cached array. we can put anything in array is a finite and limited value(may be max 1000). you know node id is infinite number depends upon website to website. That patch code can be a performance issue. for eg. today in my client website contains 50k + node same as views_natural_sort table. i believe array with 50K+ data would be a performance issue against one sql query to check existing record.

My client website nodes increase time by time. may be after 6 month it will cross 80k+.

function _views_natural_sort_store_node($node) {
  $record = new stdClass();  
  $record->nid = $node->nid;
  $record->field = 'title';
  $record->content = _views_natural_sort_filter_content($node->title);
  $exists = db_result(db_query('SELECT 1 FROM {views_natural_sort} WHERE nid = %d limit 1', $record->nid));
  if ($exists) {  //update  
    drupal_write_record('views_natural_sort', $record, array('nid', 'field'));
  }else{      //insert
     drupal_write_record('views_natural_sort', $record);
  }
} 

lalit774’s picture

i have tested on my client website #15. i did n't get any notice message. everything seems good. i believe these notice messages also impact on performance. because my custom script runs every hour and perform lot of nodes.

generalredneck’s picture

I did some research and some chatting with some of my friends. The array shouldn't be an issue, except that i'm catering to an edge case that 100s or 1000s of nodes will be updated in a single script execution. With that said, the overall performance would be diminished.

The only problem I see with your check query is you assume the record for the nid is going to be for title, which in this case would be correct for now... but in the future, I'm going to support natural sort on any field. In that case, it would be

$exists = db_result(db_query('SELECT 1 FROM {views_natural_sort} WHERE nid = %d AND field = %s limit 1', array($record->nid, $record->field));

Thanks for all your hard work and dedication to this issue. I'll get a fix in for this in the next couple of days.

generalredneck’s picture

Status: Active » Closed (fixed)

sorry it took so long to get the fix in.

Got it committed.

generalredneck’s picture

Issue summary: View changes

sol