[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