Re: Migrating bayes to mysql fails with parsing errors

[Mark Martinec]

Dave,

> dbg: bayes: error inserting token for line: t 1 0 1308114254
>   4fd2b3f2f0 dbg: bayes: _put_token: Updated an unexpected number
>   of rows. [repeats ...]

Which version of MySQL?

Did you remember to replace TYPE=MyISAM with TYPE=InnoDB in the
schema (according to README.bayes) if you are using the recommended
Mail::SpamAssassin::BayesStore::MySQL as the bayes_store_module?

Please try the following patch (against 3.3.2), at least it should provide
more informative diagnostics:


--- lib/Mail/SpamAssassin/BayesStore/MySQL.pm   (revision 1138020)
+++ lib/Mail/SpamAssassin/BayesStore/MySQL.pm   (working copy)
@@ -872,7 +872,8 @@
     }
     else {
       # $num_rows was not what we expected
-      dbg("bayes: _put_token: Updated an unexpected number of rows.");
+      dbg("bayes: _put_token: Updated an unexpected number of rows: %s, ".
+          "id: %s, token: %s",  $num_rows, $self->{_userid}, $token);
       $self->{_dbh}->rollback();
       return 0;
     }
@@ -987,8 +988,20 @@
       else {
        my $num_rows = $rc;
 
-       $need_atime_update_p = 1 if ($num_rows == 1 || $num_rows == 2);
-       $new_tokens++ if ($num_rows == 1);
+        # With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if
+        # the row is inserted as a new row and 2 if an existing row is updated
+
+        if ($num_rows == 1) {
+          $new_tokens++;
+          $need_atime_update_p = 1;
+        } elsif ($num_rows == 2) {
+          $need_atime_update_p = 1;
+        } else {
+          # $num_rows was not what we expected
+          dbg("bayes: _put_token: Updated an unexpected number of rows: %s, ".
+              "id: %s, token: %s",  $num_rows, $self->{_userid}, $token);
+          $error_p = 1;
+        }
       }
     }
 
@@ -1026,10 +1039,10 @@
       }
     }
     else {
-      # $num_rows was not what we expected
-      dbg("bayes: _put_tokens: Updated an unexpected number of rows.");
-      $self->{_dbh}->rollback();
-      return 0;
+      info("bayes: _put_tokens: no atime updates needed?  Num of tokens: %d",
+           scalar keys %{$tokens});
+#     $self->{_dbh}->rollback();
+#     return 0;
     }
   }
 


Mark