[LON-CAPA-cvs] cvs: loncom /interface loncoursedata.pm

[raeburn]

raeburn		Fri Mar  5 17:17:57 2021 EDT

  Modified files:              
    /loncom/interface	loncoursedata.pm 
  Log:
  - Bug 6949
    - Store placeholder in place of actual submission in md5_*_responsedata table
      if length of submission > max_allowed_packet size for MySQL server.
    - Length of insert statement should not exceed max_allowed_packet size 
      when making SQL inserts into md5_*_responsedata table.
  
  
Index: loncom/interface/loncoursedata.pm
diff -u loncom/interface/loncoursedata.pm:1.206 loncom/interface/loncoursedata.pm:1.207
--- loncom/interface/loncoursedata.pm:1.206	Thu Mar  4 23:54:36 2021
+++ loncom/interface/loncoursedata.pm	Fri Mar  5 17:17:57 2021
@@ -1,6 +1,6 @@
 # The LearningOnline Network with CAPA
 #
-# $Id: loncoursedata.pm,v 1.206 2021/03/04 23:54:36 raeburn Exp $
+# $Id: loncoursedata.pm,v 1.207 2021/03/05 17:17:57 raeburn Exp $
 #
 # Copyright Michigan State University Board of Trustees
 #
@@ -777,6 +777,8 @@
     return ($symb =~ /\.task$/);
 }
 
+my $requested_max_packet = 0;
+my $max_allowed_packet;
 
 sub update_full_student_data {
     my ($sname,$sdom,$courseid) = @_;
@@ -964,9 +966,26 @@
     }
     ##
     ## Store the response data
-    $store_command = 'INSERT IGNORE INTO '.$fulldump_response_table.
+    my $store_prefix = 'INSERT IGNORE INTO '.$fulldump_response_table.
         ' VALUES '."\n";
     $store_rows = 0;
+    unless ($requested_max_packet) {
+        (undef,$max_allowed_packet) = $dbh->selectrow_array(
+                                             qq{show variables LIKE ? },
+                                             undef,
+                                             "max_allowed_packet");
+        if ($max_allowed_packet !~ /^\d+$/) {
+            $max_allowed_packet = '';
+        }
+        $requested_max_packet = 1;
+    }
+    my @store_values = ();
+    my $curr_values = '';
+    my $curr_length = 0;
+    my ($max_values);
+    if ($max_allowed_packet) {
+        $max_values = $max_allowed_packet - length($store_prefix);
+    }
     while (my ($symb_id,$hash1) = each (%$respdata)) {
         while (my ($part_id,$hash2) = each (%$hash1)) {
             while (my ($resp_id,$hash3) = each (%$hash2)) {
@@ -978,7 +997,7 @@
                     # and odd number of '\' cause insert errors to occur.  
                     # Best trap this somehow...
                     $submission = $dbh->quote($submission);
-                    $store_command .= "('".
+                    my $sql_values = "('".
                         join("','",$symb_id,$part_id,
                              $resp_id,$student_id,
                              $transaction,
@@ -986,20 +1005,51 @@
                              $data->{'response_specific'},
                              $data->{'response_specific_value'},
                              $data->{'response_specific_2'},
-                             $data->{'response_specific_value_2'}).
-                             "',".$submission."),";
+                             $data->{'response_specific_value_2'})."',";
+                    if ($max_values) {
+                        my $length = length($sql_values) + length($submission."),");
+                        if ($length > $max_values) {
+                            &Apache::lonnet::logthis("SQL responsedata insert for student: $sname would exceed max_allowed_packet size");
+                            &Apache::lonnet::logthis("symb_id: $symb_id, part_id: $part_id, resp_id: $resp_id");
+                            &Apache::lonnet::logthis("You may want to increase the max_allowed_packet size from the current: $max_allowed_packet");
+                            $sql_values .= $dbh->quote('WARNING: Submission too large -- see grading interface for actual submission')."),";
+                            $length = length($sql_values);
+                            &Apache::lonnet::logthis("Placeholder inserted instead of value of actual submission");
+                            &Apache::lonnet::logthis("See grading interface for the actual submission");
+                        } else {
+                            $sql_values .= $submission."),";
+                        }
+                        if ($length + $curr_length > $max_values) {
+                            push(@store_values,$curr_values);
+                            $curr_values = $sql_values;
+                            $curr_length = $length;
+                        } else {
+                            $curr_values .= $sql_values;
+                            $curr_length += $length;
+                        }
+                    } else {
+                        $curr_values .= $sql_values.$submission."),";
+                    }
                     $store_rows++;
                 }
             }
         }
     }
     if ($store_rows) {
-        chop($store_command);
-        $dbh->do($store_command);
-        if ($dbh->err) {
-            $returnstatus = 'error saving response data';
-            &Apache::lonnet::logthis('insert error '.$dbh->errstr());
-            &Apache::lonnet::logthis("While attempting\n".$store_command);
+        if ($curr_values ne '') {
+            push(@store_values,$curr_values);
+        }
+        foreach my $item (@store_values) {
+            chop($item);
+            if ($item ne '') {
+                $dbh->do($store_prefix.$item);
+                if ($dbh->err) {
+                    $returnstatus = 'error saving response data';
+                    &Apache::lonnet::logthis('insert error '.$dbh->errstr());
+                    &Apache::lonnet::logthis("While attempting\n".$store_prefix.$item);
+                    last;
+                }
+            }
         }
     }
     ##
@@ -1012,7 +1062,7 @@
         $returnstatus = 'error saving current data:'.$status;
     } elsif ($status ne 'okay') {
         $returnstatus .= ' error saving current data:'.$status;
-    }        
+    }
     ##
     ## Update the students time......
     if ($returnstatus eq 'okay') {
@@ -1082,9 +1132,6 @@
     $dbh->do($request);
 }
 
-my $requested_max_packet = 0;
-my $max_allowed_packet;
-
 sub store_student_data {
     my ($sname,$sdom,$courseid,$student_data) = @_;
     #