I just used the D6 dev version to force-subscribe 420 users of a certain role to a specific Content Type. Worked well - only downside is that you have to navigate through a few screens - but it does the job.

A couple of notes I made testing this (don't mean to complain - just to give you some detailed feedback):

  • I ended up running some db queries in an adjacent shell to see which recipient_uids were added/removed (and looked at totals) as I was doing bulk subscribes and unsubscribes. For someone w/o such direct db access it would be nice if they could generate lists via the Drupal GUI: showing how many/which users are now subscribed to what.
  • too bad there are only 50 users in /admin/user/user/list - that increases the likelihood of errors - as it means you have to do this multiple times if you have more than 50 users. It was only 9 sets in my case - so I wasn't terribly motivated to find a way to increase this limit of 50 users per screen. Obviously, this is not specific to subscriptions module - any bulk operation via /user/user/list is limited by this.
  • when running some mini bulk updates with a limited number of users who had already subscribed to this Content Type I found that for each of them an extra row was added to the {subscriptions} table. I'm not sure if that would translate into 2 outgoing notifications - but I ended up deciding to do a bulk unsubscribe -> followed by a bulk (re) subscribe.
  • it would have been sweet if the subscribe vs unsubscribe could be a form option in the /userdefaults/bulk form (instead of in the user/user/list for update options). As you may want to unsubscribe them from one Topic and subscribe them to another. If you have a user list with more than 50 users going back and forth a lot means very carefully re-selecting the right pager number in the /user/user/list screen as every time you go back to /user/user/list you end up on the 1st screen again.

OK, this sounds like I'm complaining - but that's not the case! It would have taken me a lot longer had I not found your -dev version. Thanks a bunch!

Comments

salvis’s picture

salvis
he/his
commented
Status: Active » Needs work

Thank you for reposting — this is very good feedback!

1. Reporting which users are subscribed to what is a common request — I wish I had that feature myself. However, this is a big task, even just coming up with a working concept is difficult. IAC, this would have to be pursued in an issue of its own (which probably already exists), and for D7 first. It would probably have to be done via Views integration and the chances for getting it backported to D6 are very small.

2. Indeed, the hard-coded pager limit of 50 is a real pain, and has been for six and a half years: #39954: Pager should allow changing the "number of items per page" (and allow "show all"). This is a core issue, but I'll have a fix for D7...

3. Duplicate rows in the {subscriptions} table won't cause duplicate notifications, but I agree that they're unwanted. I consider this to be a bug. Removing and resubscribing is not an ideal solution, because the existing subscription may have been customized. I'll look into this.

4. It was a conscious decision to do it like that, because I think it's safer to force the user to make up his mind at the start and then give feedback (and a chance to notice a fatal mistake), before the database is touched. However, I did not consider your use case at the time.

Your suggestion to have a switch on the /userdefaults/bulk form cannot be implemented directly. It would require submitting the form (before clicking one of the other tabs), meaning we'd need a [Continue] button in addition to the [Done] button, and if we wanted to still require a confirmation step, we'd need yet another click.

I'm not sure whether this is worth the trouble. If you could have all the users that you want on the user list, would you still want this feature?

My user list fix will be for D7, but have you tried the advuser module for D6? Maybe it doesn't have that 50 limit...

karing’s picture

karing
Location 🇨🇦
commented

Hi,

1. I looked for 'subscriber list' in the issues queue (status - active) and I only found http://drupal.org/node/681180 for 6.x-1.x-dev - could perhaps continue there? D7 only - fair enough. The site I tested the bulk descriptions on is a D6 but all my (and I would think most people's) new projects are D7s. I don't have any experience with Views Integration yet - but am keen to learn more. Someone suggested I should look into Views Integration for a module I've been working on (sandbox project).

2. I read your last comment on that thread :-)

3. A fix would be great. Let me know if you need me to re-reproduce that and or test a patch.

4. Will indeed no longer be an issue if #2 and #3 are both resolved.

salvis’s picture

salvis
he/his
commented
Title: Fine tuning bulk subscriptions » Keep bulk subscriptions from adding duplicate records
Category: feature » bug

1. Yes, #681180: Subscriber Numbers And Stats is a good place for this.

2. Pager Limits is out. The first snapshot will be built within 12h.

3. We'll use this issue to fix the duplicate records. I'll come back as soon as I have a fix.

karing’s picture

karing
Location 🇨🇦
commented

1. I've copied/pasted my comment on that to http://drupal.org/node/681180

2. COOL!

3. I'll stay tuned.

Thanks,

salvis’s picture

salvis
he/his
commented
Status: Needs work » Active

3. I'm unable to reproduce this, neither in D7 nor D6.

I've tried to subscribe a user multiple times to the same content types, but I did not get any duplicate records. Are you sure you have the current -dev version?

karing’s picture

karing
Location 🇨🇦
commented

99% sure (I only downloaded the dev after I read about the bulk feature). I will make some time tomorrow to double check/reproduce things on my end.

karing’s picture

karing
Location 🇨🇦
commented

This is the version I have:

; Information added by drupal.org packaging script on 2012-03-23
version = "6.x-1.4+15-dev"

Let me try reproduce what I observed previously [I hope these sql tables will look ok...]:

BEFORE:

MariaDB [drupal_lllc_ca]> SELECT * FROM subscriptions WHERE recipient_uid = 12;
+------+--------+-------+--------------+---------------+---------------+------------+--------------+---------------+
| sid  | module | field | value        | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+------+--------+-------+--------------+---------------+---------------+------------+--------------+---------------+
|  355 | node   | nid   | 915          |            12 |             1 |         -1 |            1 |             1 |
|    9 | node   | nid   | 889          |            12 |             1 |         -1 |            1 |             1 |
|   62 | node   | nid   | 892          |            12 |             1 |         -1 |            1 |             1 |
|   63 | node   | nid   | 893          |            12 |             1 |         -1 |            1 |             1 |
|  110 | node   | nid   | 900          |            12 |             1 |         -1 |            1 |             1 |
|  151 | node   | nid   | 501          |            12 |             1 |         -1 |            1 |             1 |
|  173 | node   | nid   | 908          |            12 |             1 |         -1 |            1 |             1 |
|  957 | node   | nid   | 11           |            12 |             1 |         -1 |            1 |             1 |
|  204 | node   | nid   | 914          |            12 |             1 |         -1 |            1 |             1 |
|  217 | node   | nid   | 662          |            12 |             1 |         -1 |            1 |             1 |
|  263 | node   | type  | leaders_only |            12 |             1 |         -1 |            1 |             1 |
|  266 | node   | nid   | 910          |            12 |             1 |         -1 |            1 |             1 |
| 1044 | node   | nid   | 923          |            12 |             1 |         -1 |            1 |             1 |
|  990 | node   | nid   | 920          |            12 |             1 |         -1 |            1 |             1 |
|  712 | node   | type  | forum        |            12 |             1 |         -1 |            1 |             1 |
|  953 | node   | nid   | 918          |            12 |             1 |         -1 |            1 |             1 |
| 1039 | node   | nid   | 921          |            12 |             1 |         -1 |            1 |             1 |
+------+--------+-------+--------------+---------------+---------------+------------+--------------+---------------+
17 rows in set (0.00 sec)

NEXT:
bulk subscribing user 12 -> to forum (to which user 12 already is subscribed).

AFTER:

MariaDB [drupal_lllc_ca]> SELECT * FROM subscriptions WHERE recipient_uid = 12;
+------+--------+-------+--------------+---------------+---------------+------------+--------------+---------------+
| sid  | module | field | value        | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+------+--------+-------+--------------+---------------+---------------+------------+--------------+---------------+
|  355 | node   | nid   | 915          |            12 |             1 |         -1 |            1 |             1 |
|    9 | node   | nid   | 889          |            12 |             1 |         -1 |            1 |             1 |
|   62 | node   | nid   | 892          |            12 |             1 |         -1 |            1 |             1 |
|   63 | node   | nid   | 893          |            12 |             1 |         -1 |            1 |             1 |
|  110 | node   | nid   | 900          |            12 |             1 |         -1 |            1 |             1 |
|  151 | node   | nid   | 501          |            12 |             1 |         -1 |            1 |             1 |
|  173 | node   | nid   | 908          |            12 |             1 |         -1 |            1 |             1 |
|  957 | node   | nid   | 11           |            12 |             1 |         -1 |            1 |             1 |
|  204 | node   | nid   | 914          |            12 |             1 |         -1 |            1 |             1 |
|  217 | node   | nid   | 662          |            12 |             1 |         -1 |            1 |             1 |
|  263 | node   | type  | leaders_only |            12 |             1 |         -1 |            1 |             1 |
|  266 | node   | nid   | 910          |            12 |             1 |         -1 |            1 |             1 |
| 1044 | node   | nid   | 923          |            12 |             1 |         -1 |            1 |             1 |
| 1046 | node   | type  | forum        |            12 |             1 |         -1 |            1 |             1 |
|  990 | node   | nid   | 920          |            12 |             1 |         -1 |            1 |             1 |
|  712 | node   | type  | forum        |            12 |             1 |         -1 |            1 |             1 |
|  953 | node   | nid   | 918          |            12 |             1 |         -1 |            1 |             1 |
| 1039 | node   | nid   | 921          |            12 |             1 |         -1 |            1 |             1 |
+------+--------+-------+--------------+---------------+---------------+------------+--------------+---------------+
18 rows in set (0.00 sec)

So this is the extra row I observed. I interpreted that as a double subscription to content-type forum.

salvis’s picture

salvis
he/his
commented

You are using MariaDB? I wonder whether that's what makes the difference...

Here's the D6 function that saves the subscriptions:

/**
 * Create a subscription.
 */
function subscriptions_write_subscription($module, $field, $value, $author_uid, $recipient_uid, $send_interval = 1, $send_updates = 0, $send_comments = 0) {
  db_query("UPDATE {subscriptions} SET send_interval = %d, send_updates = %d, send_comments = %d WHERE module = '%s' AND field ='%s' AND value='%s' AND recipient_uid = %d AND author_uid = %d", $send_interval, $send_updates, $send_comments, $module, $field, $value, $recipient_uid, $author_uid);
  if (!db_affected_rows()) {
    @db_query("INSERT INTO {subscriptions} (module, field, value, author_uid, recipient_uid, send_interval, send_updates, send_comments)  VALUES ('%s', '%s', '%s', %d, %d, %d, %d, %d)", $module, $field, $value, $author_uid, $recipient_uid, $send_interval, $send_updates, $send_comments);
  }
}

Assuming you have Devel installed and configured, can you wrap a dpm() call around the db_affected_rows() call, so that it becomes

  if (!dpm(db_affected_rows(), 'affected rows')) {

? This returns 1 for me, and consequently, the INSERT is not done. It seems to return 0 for you, which would explain the duplicate row.

I wonder whether MariaDB tries to be smart and return 0 because the UPDATE statement did not actually change the row when it set the same values that were already there.

karing’s picture

karing
Location 🇨🇦
commented

I have a local copy of this site on a MAMP stack - so let me try and see if I can reproduce it here.

Before:

mysql> SELECT * FROM subscriptions WHERE recipient_uid = 12;
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
| sid | module | field | value          | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
|   1 | node   | tid   | 10             |            12 |             1 |         -1 |            1 |             1 |
|   2 | node   | nid   | 854            |            12 |             1 |         -1 |            1 |             1 |
|   3 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|   5 | node   | tid   | 10             |            12 |             1 |         12 |            1 |             1 |
|  20 | node   | nid   | 860            |            12 |             1 |         -1 |            1 |             1 |
|  23 | node   | nid   | 861            |            12 |             1 |         -1 |            1 |             1 |
|  24 | node   | nid   | 862            |            12 |             1 |         -1 |            1 |             1 |
|  25 | node   | nid   | 863            |            12 |             1 |         -1 |            1 |             1 |
|  26 | node   | nid   | 864            |            12 |             1 |         -1 |            1 |             1 |
|  27 | node   | nid   | 832            |            12 |             1 |         -1 |            1 |             1 |
|  33 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
|  35 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
12 rows in set (0.00 sec)

Adding bulksubscribe user 12 to 3 Content - types to which she already is subscribed - adds 3 more rows. Output from devel:

    affected rows => 1

    affected rows => 1

    affected rows => 1

    Your subscriptions were updated.

After:

mysql> SELECT * FROM subscriptions WHERE recipient_uid = 12;
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
| sid | module | field | value          | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
|   1 | node   | tid   | 10             |            12 |             1 |         -1 |            1 |             1 |
|   2 | node   | nid   | 854            |            12 |             1 |         -1 |            1 |             1 |
|   3 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|   5 | node   | tid   | 10             |            12 |             1 |         12 |            1 |             1 |
|  39 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
|  20 | node   | nid   | 860            |            12 |             1 |         -1 |            1 |             1 |
|  23 | node   | nid   | 861            |            12 |             1 |         -1 |            1 |             1 |
|  24 | node   | nid   | 862            |            12 |             1 |         -1 |            1 |             1 |
|  25 | node   | nid   | 863            |            12 |             1 |         -1 |            1 |             1 |
|  26 | node   | nid   | 864            |            12 |             1 |         -1 |            1 |             1 |
|  27 | node   | nid   | 832            |            12 |             1 |         -1 |            1 |             1 |
|  38 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|  33 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
|  35 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
|  37 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
15 rows in set (0.00 sec)

mysql>

Triple checking the .info for subscriptions module on my local MAMP server:

; Information added by drupal.org packaging script on 2012-03-23
version = "6.x-1.4+15-dev"
core = "6.x"
project = "subscriptions"
datestamp = "1332463184"

I have a custom subscriptionsedit module but that just contains some hook_form_alter() statements [for #descriptions and to hide some content types from the user Subscription settings based on the user's role].

Any other things I could try look at to help understand what may be happening?

salvis’s picture

salvis
he/his
commented

I'm starting to get desperate...

Here's what I would do now:

0. Put a dpm('For new subscriptions only!!!'); between the if and the @db_query shown in #8 to verify that we really don't enter the if (!1) block. (We're getting paranoid here...) Or comment out the INSERT and check whether it makes any difference.

1. Search through the entire code base for
INSERT INTO {subscriptions}
and (EDIT!)
drupal_write_record('subscriptions'

2. Put a dpm('something that you recognize'); call ahead of each of those statements.

3. Find out where the heck these rows are inserted.

It can't be that rows get inserted without a corresponding INSERT statement being executed. It can't be that you don't find that beast!

I'm not sure whether Devel's query logging records INSERTs, too. That could be an alternative route.

karing’s picture

karing
Location 🇨🇦
commented

We're entering the if block:

mysql> SELECT * FROM subscriptions WHERE recipient_uid=12;
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
| sid | module | field | value          | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
|   1 | node   | tid   | 10             |            12 |             1 |         -1 |            1 |             1 |
|   2 | node   | nid   | 854            |            12 |             1 |         -1 |            1 |             1 |
|   3 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|   5 | node   | tid   | 10             |            12 |             1 |         12 |            1 |             1 |
|  39 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
|  20 | node   | nid   | 860            |            12 |             1 |         -1 |            1 |             1 |
|  23 | node   | nid   | 861            |            12 |             1 |         -1 |            1 |             1 |
|  24 | node   | nid   | 862            |            12 |             1 |         -1 |            1 |             1 |
|  25 | node   | nid   | 863            |            12 |             1 |         -1 |            1 |             1 |
|  26 | node   | nid   | 864            |            12 |             1 |         -1 |            1 |             1 |
|  27 | node   | nid   | 832            |            12 |             1 |         -1 |            1 |             1 |
|  38 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|  33 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
|  35 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
|  37 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
15 rows in set (0.04 sec)

Bulk subscribing to three already existing content type subscriptions (note: affected rows now = 2; and indeed if I do this exact some thing again it becomes = 3)

affected rows => 2
in if statement!!!
affected rows => 2
in if statement!!!
affected rows => 2
in if statement!!!
Your subscriptions were updated.

mysql> mysql> SELECT * FROM subscriptions WHERE recipient_uid=12;
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
| sid | module | field | value          | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
|   1 | node   | tid   | 10             |            12 |             1 |         -1 |            1 |             1 |
|   2 | node   | nid   | 854            |            12 |             1 |         -1 |            1 |             1 |
|   3 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|  40 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
|   5 | node   | tid   | 10             |            12 |             1 |         12 |            1 |             1 |
|  39 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
|  20 | node   | nid   | 860            |            12 |             1 |         -1 |            1 |             1 |
|  23 | node   | nid   | 861            |            12 |             1 |         -1 |            1 |             1 |
|  24 | node   | nid   | 862            |            12 |             1 |         -1 |            1 |             1 |
|  25 | node   | nid   | 863            |            12 |             1 |         -1 |            1 |             1 |
|  26 | node   | nid   | 864            |            12 |             1 |         -1 |            1 |             1 |
|  27 | node   | nid   | 832            |            12 |             1 |         -1 |            1 |             1 |
|  38 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|  33 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
|  35 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
|  37 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
|  41 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|  42 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
18 rows in set (0.00 sec)


/**
 * Create a subscription.
 */
function subscriptions_write_subscription($module, $field, $value, $author_uid, $recipient_uid, $send_interval = 1, $send_updates = 0, $send_comments = 0) {
  db_query("UPDATE {subscriptions} SET send_interval = %d, send_updates = %d, send_comments = %d WHERE module = '%s' AND field ='%s' AND value='%s' AND recipient_uid = %d AND author_uid = %d", $send_interval, $send_updates, $send_comments, $module, $field, $value, $recipient_uid, $author_uid);
//  if (!db_affected_rows()) {
  if (!dpm(db_affected_rows(), 'affected rows')) {
  	dpm('in if statement!!!');
    @db_query("INSERT INTO {subscriptions} (module, field, value, author_uid, recipient_uid, send_interval, send_updates, send_comments)  VALUES ('%s', '%s', '%s', %d, %d, %d, %d, %d)", $module, $field, $value, $author_uid, $recipient_uid, $send_interval, $send_updates, $send_comments);
  }
}

I tar -x the orginal subscriptions-6.x-1.x-dev.tar.gz again - and ran a diff w/ the code I've edited - just to make sure no other edits slipped in and - none have:

Karins-MacBook-Pro:modules sysadmin$ diff subscriptions/subscriptions.module subscriptions_dpm/subscriptions.module
398c398,400
<   if (!db_affected_rows()) {
---
> //  if (!db_affected_rows()) {
>   if (!dpm(db_affected_rows(), 'affected rows')) {
>       dpm('in if statement!!!');
Karins-MacBook-Pro:modules sysadmin$

Hopefully this will give you some ideas as to what may be happening here. What's affected rows keeping track of?

salvis’s picture

salvis
he/his
commented

I'm sorry about the delay, Karin.

db_affected_rows() tells you how many rows were changed by the preceding UPDATE statement. The logic is as follows — there should be two cases:

1. We don't have this subscription yet: in that case, the UPDATE will not find any matching rows to update, db_affected_rows() will be 0, and !db_affected_rows() (read "NOT db_affected_rows()") will be TRUE, and we execute the INSERT to save the new subscription.

2. We're updating an existing subscription: in that case, the UPDATE will find a matching row, db_affected_rows() will be 1 (or more, if there were duplicates), and !db_affected_rows() will be FALSE (either way), and we skip the INSERT, since we've just updated the existing subscription.

Getting a 1 (or 2 in your experiment because you already have bogus rows) and still going to the INSERT means that your NOT operator (the !) or your if statement is not working as expected. This really has me stumped.

Here's what we can try to really nail it down: replace 

    if (!db_affected_rows()) {

with

    $affected_rows = db_affected_rows();
    dpm((!$affected_rows ? "TRUE" : "FALSE"), "db_affected_rows() returned $affected_rows; negated this is");
    if ($affected_rows > 0 && !$affected_rows) {
      dpm("It's impossible to get here, or at least it should be impossible...");
    }
    if ($affected_rows) {   // no ! here
      dpm("$affected_rows existing row(s) was/were updated, we don't need to INSERT.");
    }
    else {
        dpm("No rows were updated, we INSERT a new subscription.");

What does that give you?

karing’s picture

karing
Location 🇨🇦
commented

Hi - no worries about any delays - not at all. Things got a little nuts here the past two weeks as well.

Ok, that (replace) gives me:

db_affected_rows() returned 0; negated this is => TRUE
No rows were updated, we INSERT a new subscription.

I double checked I don't have any lingering copies of subscriptions.module in the /all/modules . I have been noticing some other issues when users are trying to update their own subscriptions. I was able to reproduce it this afternoon. If you think that this may be related I'll document it in detail here.

-- Karin

salvis’s picture

salvis
he/his
commented

Good, that's the expected behavior for new subscriptions.

Now, what do you get if you change an existing subscription?

Yes, there's something wrong here, and we need all the information we can get.

karing’s picture

karing
Location 🇨🇦
commented

"Good, that's the expected behavior for new subscriptions."

It wasn't a new subscription though -> I was replicating what I did above: I was bulk subscribing a user to a content type to which the user was already subscribed. So my {subscriptions} now looks like (so the 18 rows we had in #11 + 3 newly inserted rows):

mysql> SELECT * FROM subscriptions WHERE recipient_uid=12;
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
| sid | module | field | value          | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
|   1 | node   | tid   | 10             |            12 |             1 |         -1 |            1 |             1 |
|   2 | node   | nid   | 854            |            12 |             1 |         -1 |            1 |             1 |
|   3 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|  40 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
|   5 | node   | tid   | 10             |            12 |             1 |         12 |            1 |             1 |
|  39 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
|  20 | node   | nid   | 860            |            12 |             1 |         -1 |            1 |             1 |
|  23 | node   | nid   | 861            |            12 |             1 |         -1 |            1 |             1 |
|  24 | node   | nid   | 862            |            12 |             1 |         -1 |            1 |             1 |
|  25 | node   | nid   | 863            |            12 |             1 |         -1 |            1 |             1 |
|  26 | node   | nid   | 864            |            12 |             1 |         -1 |            1 |             1 |
|  27 | node   | nid   | 832            |            12 |             1 |         -1 |            1 |             1 |
|  38 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|  33 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
|  35 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
|  37 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
|  41 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|  42 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
|  43 | node   | type  | webform_report |            12 |             1 |         -1 |            1 |             1 |
|  44 | node   | type  | forum          |            12 |             1 |         -1 |            1 |             1 |
|  45 | node   | type  | board          |            12 |             1 |         -1 |            1 |             1 |
+-----+--------+-------+----------------+---------------+---------------+------------+--------------+---------------+
21 rows in set (0.00 sec)

If you can't' reproduce this - it must be something that's only producing a conflict/bug on our two installs of this site - because of something... I need to dig deeper. Get you more bits to think about - hopefully there will soon be a aha moment.

May / may not be related - but just as mysterious as what we're seeing above.

1. user karin trying to change Send Interval for self: user karin (lots of permissions):
My Profile -> Subscriptions -> Settings
toggle Send Interval from As soon as possible to: Daily
Save
Status Message: The changes have been saved.
But if I open Settings again - Send Interval is back at: As soon as possible

2. If karin masquerades as another user karin can also NOT update the other user's Send Interval.

3. If instead of masquerading as the other user - user karin simply goes to the other user's:
/user/1071/subscriptions directly
user karin CAN change the Send Interval

So I can't change my own subscriptions via the GUI -> but I can change other people's if I go to their subscriptions page directly.

That's all I have on this thus far!

salvis’s picture

salvis
he/his
commented

I can't remember meeting anyone else using MariaDB, but I haven't heard anything bad about it either.

This is really crazy. In #9 you reported getting a count of 1, and in #11 a count of 2. Assuming you have a recent version of Devel, dpm() returns this value and when negated it would be FALSE, i.e. no INSERT.

Now suddenly you're reporting a count of 0!

I'm not doubting you, but it seems impossible that in

  if (!dpm(db_affected_rows(), 'affected rows')) {

db_affected_rows() returned 1 and 2, and in

    $affected_rows = db_affected_rows();
    dpm((!$affected_rows ? "TRUE" : "FALSE"), "db_affected_rows() returned $affected_rows; negated this is");

it returns 0.

What is your PHP version?

May / may not be related - but just as mysterious as what we're seeing above.

I agree completely. Assuming you're referring to the Masquerade module, I don't know how that one works, but if you're able to update the other user but not your own subscriptions, the difference might be that you have duplicate records and the other user doesn't. Other than that, I see no difference between your #1 and #3.

Duplicate records should not make any difference (UPDATE should update all matching records!), as I've said before, but we can't take anything for granted anymore...

karing’s picture

karing
Location 🇨🇦
commented

I'm quite sure it's not related to MariaDB - after first seeing the duplicate subscription records appear on the production copy of the site (w/ MariaDB) I was able to reproduce it on a working copy of that site on my local MAMP. Every {subscriptions} in this thread [from #9 on] is from the local MAMP copy - using MySQL. I do have full access to the server on which we run the production copy - but it made sense to take this apart locally.

PHP on my MAMP is 5.2 - on the production server we recently went to 5.3

That my db_affected_rows() are now 0 (and were 1, 2 and going up previously) - in between I installed a non-related module; I've run updatedb and I've done a drush cc all. It could very well be that I may have forgotten to run updatedb after replacing the recommended release subscriptions module with the dev version of it. Perhaps I missed some hook_update_x()?

I'm going to clean up the duplicate records (dump the extra rows) and start all over - reproducing the creation of duplicate records on both the production server as well as the local MAMP copy - I'll look at db_affected_rows(); I'll try find some time for this tomorrow. Want to help get this into aha status soon.

-- Karin

salvis’s picture

salvis
he/his
commented

db_affected_rows() is a core function in D6: http://api.drupal.org/api/search/6/db_affected_rows

I don't think running update.php or installing/removing "normal" contribs could affect db_affected_rows(). We're using nothing but two well-known core function (db_query() and db_affected_rows()) — if we can't rely on these, then all bets are off!

This is only marginally related to Subscriptions, really. Any module could try to use a simple UPDATE.

I'm running out of ideas...

karing’s picture

karing
Location 🇨🇦
commented

Ok, I'm going back to where things started: production server; MariaDB; php 5.3

1.0 Adding Duplicate Records

Before:

MariaDB [drupal_lllc_ca]> SELECT * FROM subscriptions WHERE recipient_uid = 12;
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
| sid  | module | field | value | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
| 1104 | node   | type  | blog  |            12 |             1 |         94 |            1 |             1 |
| 1182 | node   | nid   | 501   |            12 |             1 |         -1 |            1 |             1 |
| 1181 | node   | nid   | 915   |            12 |             1 |         -1 |            1 |             1 |
| 1105 | node   | type  | blog  |            12 |             1 |       1651 |            1 |             1 |
| 1197 | node   | type  | forum |            12 |             1 |         -1 |            1 |             1 |
| 1200 | node   | nid   | 921   |            12 |             1 |         -1 |            1 |             1 |
| 1205 | node   | nid   | 11    |            12 |             1 |         -1 |            1 |             1 |
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
7 rows in set (0.00 sec)

admin/user/user
select user (with id = 12);
subscribe the selected users to -> Update
Content Types
Select Forum topic
Save
Your subscriptions were updated

After:

MariaDB [drupal_lllc_ca]> SELECT * FROM subscriptions WHERE recipient_uid = 12;
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
| sid  | module | field | value | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
| 1104 | node   | type  | blog  |            12 |             1 |         94 |            1 |             1 |
| 1231 | node   | type  | forum |            12 |             1 |         -1 |            1 |             1 |
| 1182 | node   | nid   | 501   |            12 |             1 |         -1 |            1 |             1 |
| 1181 | node   | nid   | 915   |            12 |             1 |         -1 |            1 |             1 |
| 1105 | node   | type  | blog  |            12 |             1 |       1651 |            1 |             1 |
| 1197 | node   | type  | forum |            12 |             1 |         -1 |            1 |             1 |
| 1200 | node   | nid   | 921   |            12 |             1 |         -1 |            1 |             1 |
| 1205 | node   | nid   | 11    |            12 |             1 |         -1 |            1 |             1 |
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
8 rows in set (0.00 sec)

So we've added a duplicate subscription for forum topic:
| 1231 | node | type | forum | 12 | 1 | -1 | 1 | 1 |

subscriptions.info on production server:

name = Subscriptions
description = The basic Subscriptions framework.
package = "Subscriptions"
core = 6.x

; Information added by drupal.org packaging script on 2012-03-23
version = "6.x-1.4+15-dev"
core = "6.x"
project = "subscriptions"
datestamp = "1332463184"

emacs subscriptions.module

/**
 * Create a subscription.
 */
function subscriptions_write_subscription($module, $field, $value, $author_uid, $recipient_uid, $send_interval = 1, $send_updates = 0, $send_comments = 0) {
  db_query("UPDATE {subscriptions} SET send_interval = %d, send_updates = %d, send_comments = %d WHERE module = '%s' AND field ='%s' AND value='%s' AND recipient_uid = %d AND author_uid = %d", $send_interval, $send_updates, $send_comments, $module, $field, $value, $rec\
ipient_uid, $author_uid);
  /* debug info */
  $modified = function_exists('dpm') ? dpm(db_affected_rows(), 'affected rows') : db_affected_rows();
  if (!$modified) {
  /* if (!db_affected_rows()) { */
    @db_query("INSERT INTO {subscriptions} (module, field, value, author_uid, recipient_uid, send_interval, send_updates, send_comments)  VALUES ('%s', '%s', '%s', %d, %d, %d, %d, %d)", $module, $field, $value, $author_uid, $recipient_uid, $send_interval, $send_updates\
, $send_comments);
  }
}

Hold on... this looks different from on my MAMP:

/**
 * Create a subscription.
 */
function subscriptions_write_subscription($module, $field, $value, $author_uid, $recipient_uid, $send_interval = 1, \
$send_updates = 0, $send_comments = 0) {
  db_query("UPDATE {subscriptions} SET send_interval = %d, send_updates = %d, send_comments = %d WHERE module = '%s'\
 AND field ='%s' AND value='%s' AND recipient_uid = %d AND author_uid = %d", $send_interval, $send_updates, $send_co\
mments, $module, $field, $value, $recipient_uid, $author_uid);
  if (!db_affected_rows()) {
    @db_query("INSERT INTO {subscriptions} (module, field, value, author_uid, recipient_uid, send_interval, send_upd\
ates, send_comments)  VALUES ('%s', '%s', '%s', %d, %d, %d, %d, %d)", $module, $field, $value, $author_uid, $recipie\
nt_uid, $send_interval, $send_updates, $send_comments);
  }
}

subscriptions.info on MAMP:

name = Subscriptions
description = The basic Subscriptions framework.
package = "Subscriptions"
core = 6.x

; Information added by drupal.org packaging script on 2012-03-23
version = "6.x-1.4+15-dev"
core = "6.x"
project = "subscriptions"
datestamp = "1332463184"

Two identical .info files but two different .module files? I've not added any debugging statements in subscriptions.module on the production server. Can this get any weirder? Does that look like something you used to have in a dev version?

I'll work on getting devel module installed and configured on the production server so I can start looking at those dpm statements.

-- Karin

salvis’s picture

salvis
he/his
commented 
  /* debug info */
  $modified = function_exists('dpm') ? dpm(db_affected_rows(), 'affected rows') : db_affected_rows();
  if (!$modified) {
  /* if (!db_affected_rows()) { */

No, that's not my code. I never use block comments for single lines.

But the functionality should be the same, provided that your Devel/dpm() is a recent -dev version. It's possible that the release version of Devel has dpm() still returning NULL, which could explain the confusion if the old dpm() is there.

karing’s picture

karing
Location 🇨🇦
commented

Hi there! Ok, I've done more work on trying to figure out what's going on - and... upgrading to the recent dev (+17) version appears to have solved things for me. Below please find my notes:

A. On production server: I managed to recreate the issue without the bulk subscribe. Simply going into a users subscriptions interface page eg user/39/subscriptions/type - toggling the existing Send interval created an extra line in the {subscriptions}. It does update the Send Interval properly - but it also adds an extra row:

before:

MariaDB [drupal_lllc_ca]> SELECT * FROM subscriptions WHERE recipient_uid = 39;
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
| sid  | module | field | value | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
|   76 | node   | nid   | 717   |            39 |             1 |         -1 |            1 |             1 |
|  182 | node   | nid   | 23    |            39 |             1 |         -1 |            1 |             1 |
|  183 | node   | nid   | 491   |            39 |             1 |         -1 |            1 |             1 |
|  190 | node   | nid   | 910   |            39 |             1 |         -1 |            1 |             1 |
|  839 | node   | type  | forum |            39 |         86400 |         -1 |            1 |             1 |
| 1242 | node   | type  | forum |            39 |         86400 |         -1 |            1 |             1 |
| 1158 | node   | nid   | 932   |            39 |         86400 |         -1 |            1 |             1 |
| 1246 | node   | type  | forum |            39 |         86400 |         -1 |            1 |             1 |
| 1247 | node   | type  | forum |            39 |         86400 |         -1 |            1 |             1 |
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
9 rows in set (0.00 sec)

After (toggle forum interval to hourly):

MariaDB [drupal_lllc_ca]> SELECT * FROM subscriptions WHERE recipient_uid = 39;
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
| sid  | module | field | value | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
|   76 | node   | nid   | 717   |            39 |             1 |         -1 |            1 |             1 |
|  182 | node   | nid   | 23    |            39 |             1 |         -1 |            1 |             1 |
|  183 | node   | nid   | 491   |            39 |             1 |         -1 |            1 |             1 |
|  190 | node   | nid   | 910   |            39 |             1 |         -1 |            1 |             1 |
|  839 | node   | type  | forum |            39 |          3600 |         -1 |            1 |             1 |
| 1242 | node   | type  | forum |            39 |          3600 |         -1 |            1 |             1 |
| 1158 | node   | nid   | 932   |            39 |         86400 |         -1 |            1 |             1 |
| 1246 | node   | type  | forum |            39 |          3600 |         -1 |            1 |             1 |
| 1247 | node   | type  | forum |            39 |          3600 |         -1 |            1 |             1 |
| 1249 | node   | type  | forum |            39 |          3600 |         -1 |            1 |             1 |
+------+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
10 rows in set (0.00 sec)

B. Back to local install (MAMP) -> copied the dev (+15) version from the production server. I'm able to reproduce what I see above. I did not copy the {subscriptions} so on my local install this user started with a clean slate:

mysql> SELECT * FROM subscriptions WHERE recipient_uid =39;
Empty set (0.00 sec)

Forum topic: toggle to hourly - save

mysql> SELECT * FROM subscriptions WHERE recipient_uid =39;
+-----+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
| sid | module | field | value | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+-----+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
|  48 | node   | type  | forum |            39 |          3600 |         -1 |            1 |             1 |
+-----+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
1 row in set (0.00 sec)

Forum topic: toggle to daily - save

mysql> SELECT * FROM subscriptions WHERE recipient_uid =39;
+-----+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
| sid | module | field | value | recipient_uid | send_interval | author_uid | send_updates | send_comments |
+-----+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
|  48 | node   | type  | forum |            39 |         86400 |         -1 |            1 |             1 |
|  49 | node   | type  | forum |            39 |         86400 |         -1 |            1 |             1 |
+-----+--------+-------+-------+---------------+---------------+------------+--------------+---------------+
2 rows in set (0.00 sec)

Devel info as this is happening:

    debugging this
    affected rows => 1
    Your subscriptions were updated.

C. Ok, let's start all over on local MAMP install: rm -r subscriptions module -> replace it with the new dev (+17) from your project/subscriptions page. Things are working fine! I can not get it to create extra rows.

D. On local install: Back to the version (+15) I was using before: yes - I'm able to create duplicate rows again.

E. Installing the dev (+17) version on the production server: Yes! Fixed things there too (sigh so definitively no MariDB issue) - no more duplicate rows are being created.

F. Running some test Bulk updates: things are fine, no duplicate rows are being created.

G. Then things got a little confusing again - after the bulk subscribe test I used Masquearade module to switch to user 39 then at first things looked fine - but then the checkbox in the user/39/subscriptions/type didn't show as marked anymore - I had to recheck it -> after hitting save I got back to the bulk update 'finished' screen (odd) and it looked like this also didn't create a new row - but at some point it did. I've not been able to reproduce that since - perhaps it was a very odd entanglement of permissions/caching.

H. Let's test all normal user case scenarios:
me updating user's 39 Forum Send interval: works properly - no additional rows and Send Interval is updated properly.
me masquerading as user 39: works properly
me bulk subscribing user 39 to forum: checking box forum + as soon as possible: table looks fine

Conclusion: doing a lot better now w/ duplicate rows!

- Karin

salvis’s picture

salvis
he/his
commented
Status: Active » Fixed

Whew, what a ride. So, in the end, updating to the latest -dev is what helped, and we don't really know what the problem was. It seems high time to make a new release now.

Re. G: The bulk update state is kept in your session. Switching users before closing the bulk update could certainly cause odd behavior.

Thank you for your perseverence and careful reporting.

I'm marking this fixed, feel free to reopen it if needed.

P.S. Be sure to update Devel regularly to the latest -dev version. It's continually under development.

karing’s picture

karing
Location 🇨🇦
commented

Yes, please do mark it fixed :-) New release: I only ended up using the -dev version b/c I really, really needed the bulk subscribe feature.

Thank you very much for helping me think this through!

PS I still have an issue for a user updating their own future Send interval. I will open a new issue for that.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

silverwing’s picture

silverwing commented
Issue summary: View changes

Corrected typo