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

raeburn raeburn at source.lon-capa.org
Tue Mar 2 14:51:02 EST 2021


raeburn		Tue Mar  2 19:51:02 2021 EDT

  Modified files:              
    /loncom/interface	loncoursedata.pm 
  Log:
  - Bug 6949 
    Length of insert statement should not exceed max_allowed_packet size 
    when making SQL inserts into md5_*_performance or md5_*_parameters tables
  
  
-------------- next part --------------
Index: loncom/interface/loncoursedata.pm
diff -u loncom/interface/loncoursedata.pm:1.204 loncom/interface/loncoursedata.pm:1.205
--- loncom/interface/loncoursedata.pm:1.204	Tue Mar 13 16:48:18 2018
+++ loncom/interface/loncoursedata.pm	Tue Mar  2 19:51:02 2021
@@ -1,6 +1,6 @@
 # The LearningOnline Network with CAPA
 #
-# $Id: loncoursedata.pm,v 1.204 2018/03/13 16:48:18 raeburn Exp $
+# $Id: loncoursedata.pm,v 1.205 2021/03/02 19:51:02 raeburn Exp $
 #
 # Copyright Michigan State University Board of Trustees
 #
@@ -1082,6 +1082,9 @@
     $dbh->do($request);
 }
 
+my $requested_max_packet = 0;
+my $max_allowed_packet;
+
 sub store_student_data {
     my ($sname,$sdom,$courseid,$student_data) = @_;
     #
@@ -1102,12 +1105,33 @@
     my $starttime = Time::HiRes::time;
     my $elapsed = 0;
     my $rows_stored;
-    my $store_parameters_command  = 'INSERT IGNORE INTO '.$parameters_table.
+    my $store_parameters_prefix  = 'INSERT IGNORE INTO '.$parameters_table.
         ' VALUES '."\n";
     my $num_parameters = 0;
-    my $store_performance_command = 'INSERT IGNORE INTO '.$performance_table.
+    my $store_performance_prefix = 'INSERT IGNORE INTO '.$performance_table.
         ' VALUES '."\n";
     return ('error',undef) if (! defined($dbh));
+    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_parameters_values = ();
+    my $curr_params_values = '';
+    my $curr_params_length = 0;
+    my @store_performance_values = ();
+    my $curr_perf_values = '';
+    my $curr_perf_length = 0;
+    my ($max_param,$max_perf);
+    if ($max_allowed_packet) {
+        $max_param = $max_allowed_packet - length($store_parameters_prefix);
+        $max_perf = $max_allowed_packet - length($store_performance_prefix);
+    }
     while (my ($current_symb,$param_hash) = each(%{$student_data})) {
         #
         # make sure the symb is set up properly
@@ -1120,10 +1144,29 @@
                                               $symb_id,$student_id,
                                               $parameter)."',".
                                                   $dbh->quote($value)."),\n";
-                $num_parameters ++;
                 if ($sql_parameter !~ /''/) {
-                    $store_parameters_command .= $sql_parameter;
+                    if ($max_param) {
+                        my $length = length($sql_parameter);
+                        if ($length > $max_param) {
+                            &Apache::lonnet::logthis("SQL parameter insert for student: $sname for parameter: $parameter would exceed max_allowed_packet size");
+                            &Apache::lonnet::logthis("symb_id: $symb_id");
+                            &Apache::lonnet::logthis("Skipping this item.  You may want to increase the max_allowed_packet size from the current: $max_allowed_packet");
+                            next;
+                        } else {
+                            if ($length + $curr_params_length > $max_param) {
+                                push(@store_parameters_values,$curr_params_values);
+                                $curr_params_values = $sql_parameter;
+                                $curr_params_length = $length;
+                            } else {
+                                $curr_params_values .= $sql_parameter;
+                                $curr_params_length += $length;
+                            }
+                        }
+                    } else {
+                        $curr_params_values .= $sql_parameter;
+                    }
                     #$rows_stored++;
+                    $num_parameters ++;
                 }
             }
         }
@@ -1165,31 +1208,66 @@
                 "('".join("','",$symb_id,$student_id,$part_id,$part,
                                 $solved,$tries,$awarded,$award,
                                 $awarddetail,$timestamp)."'),\n";
-            $store_performance_command .= $sql_performance;
+            if ($max_perf) {
+                my $length = length($sql_performance);
+                if ($length > $max_perf) {
+                            &Apache::lonnet::logthis("SQL performance insert for student: $sname would exceed max_allowed_packet size");
+                            &Apache::lonnet::logthis("symb_id: $symb_id");
+                            &Apache::lonnet::logthis("Skipping this item.  You may want to increase the max_allowed_packet size from the current: $max_allowed_packet");
+                            next;
+                } else {
+                    if ($length + $curr_perf_length > $max_perf) {
+                        push(@store_performance_values,$curr_perf_values);
+                        $curr_perf_values = $sql_performance;
+                        $curr_perf_length = $length;
+                    } else {
+                        $curr_perf_values .= $sql_performance;
+                        $curr_perf_length += $length;
+                    }
+                }
+            } else {
+                $curr_perf_values .= $sql_performance;
+            }
             $rows_stored++;
         }
     }
+    if ($curr_params_values ne '') {
+        push(@store_parameters_values,$curr_params_values);
+    }
+    if ($curr_perf_values ne '') {
+        push(@store_performance_values,$curr_perf_values);
+    }
     if (! $rows_stored) { return ($returnstatus, undef); }
-    $store_parameters_command =~ s|,\n$||;
-    $store_performance_command =~ s|,\n$||;
     my $start = Time::HiRes::time;
-    $dbh->do($store_performance_command);
-    if ($dbh->err()) {
-        &Apache::lonnet::logthis('performance bigass insert error:'.
-                                 $dbh->errstr());
-        &Apache::lonnet::logthis('command = '.$/.$store_performance_command);
-        $returnstatus = 'error: unable to insert performance into database';
-        return ($returnstatus,$student_data);
+    foreach my $item (@store_performance_values) {
+        $item =~ s|,\n$||;
+        if ($item ne '') {
+            $dbh->do($store_performance_prefix.$item);
+            if ($dbh->err()) {
+                &Apache::lonnet::logthis('performance insert error:'.
+                                         $dbh->errstr());
+                &Apache::lonnet::logthis('command = '.$/.$store_performance_prefix.$item);
+                $returnstatus = 'error: unable to insert performance into database';
+                return ($returnstatus,$student_data);
+            }
+        }
     }
-    $dbh->do($store_parameters_command) if ($num_parameters>0);
-    if ($dbh->err()) {
-        &Apache::lonnet::logthis('parameters bigass insert error:'.
-                                 $dbh->errstr());
-        &Apache::lonnet::logthis('command = '.$/.$store_parameters_command);
-        &Apache::lonnet::logthis('rows_stored = '.$rows_stored);
-        &Apache::lonnet::logthis('student_id = '.$student_id);
-        $returnstatus = 'error: unable to insert parameters into database';
-        return ($returnstatus,$student_data);
+    if ($num_parameters > 0) {
+        foreach my $item (@store_parameters_values) {
+            $item =~ s|,\n$||;
+            if ($item ne '') {
+                $dbh->do($store_parameters_prefix.$item);
+                if ($dbh->err()) {
+                     &Apache::lonnet::logthis('parameters insert error:'.
+                                              $dbh->errstr());
+                     &Apache::lonnet::logthis('command = '.$/.$store_parameters_prefix.$item);
+                     &Apache::lonnet::logthis('rows_stored = '.$rows_stored);
+                     &Apache::lonnet::logthis('student_id = '.$student_id);
+                     $returnstatus = 'error: unable to insert parameters into database';
+                     return ($returnstatus,$student_data);
+                }
+            }
+        }
     }
     $elapsed += Time::HiRes::time - $start;
     return ($returnstatus,$student_data);


More information about the LON-CAPA-cvs mailing list