From e27a48a0a49737055b3b7b7d21ec9760c75add61 Mon Sep 17 00:00:00 2001
From: Mark Wells <mark@freeside.biz>
Date: Thu, 26 Jun 2014 13:50:55 -0700
Subject: [PATCH] remove mysql cursors until we can get them working correctly,
 #28895

---
 FS/FS/Cursor.pm | 56 +++++++++++++++++++++++++++++++++-----------------------
 1 file changed, 33 insertions(+), 23 deletions(-)

diff --git a/FS/FS/Cursor.pm b/FS/FS/Cursor.pm
index f6d8816a7..67a98eab4 100644
--- a/FS/FS/Cursor.pm
+++ b/FS/FS/Cursor.pm
@@ -39,13 +39,12 @@ and returns an FS::Cursor object to fetch the rows one at a time.
 sub new {
   my $class = shift;
   my $q = FS::Record::_query(@_); # builds the statement and parameter list
-  my $dbh = myconnect();
+  my $dbh;
 
   my $self = {
     query => $q,
     class => 'FS::' . ($q->{table} || 'Record'),
     buffer => [],
-    dbh   => $dbh,
     position => 0, # for mysql
   };
   bless $self, $class;
@@ -60,12 +59,21 @@ sub new {
 
   my $statement;
   if ( driver_name() eq 'Pg' ) {
+    $self->{dbh} = $dbh = myconnect();
     $statement = "DECLARE ".$self->{id}." CURSOR FOR ".$q->{statement};
   } elsif ( driver_name() eq 'mysql' ) {
     # build a cursor from scratch
-    $statement = "CREATE TEMPORARY TABLE $self->{id} 
-      (rownum INT AUTO_INCREMENT, PRIMARY KEY (rownum))
-      $q->{statement}";
+    #
+    #
+    # there are problems doing it this way, and we don't have time to resolve
+    # them all right now...
+    #$statement = "CREATE TEMPORARY TABLE $self->{id} 
+    #  (rownum INT AUTO_INCREMENT, PRIMARY KEY (rownum))
+    #  $q->{statement}";
+
+    # one of those problems is locking, so keep everything on the main session
+    $self->{dbh} = $dbh = FS::UID::dbh();
+    $statement = $q->{statement};
   }
 
   my $sth = $dbh->prepare($statement)
@@ -83,8 +91,11 @@ sub new {
   } elsif ( driver_name() eq 'mysql' ) {
     # make sure we're not holding any locks on the tables mentioned
     # in the query
-    $dbh->commit if driver_name() eq 'mysql';
-    $self->{fetch} = $dbh->prepare("SELECT * FROM $self->{id} ORDER BY rownum LIMIT ?, $buffer");
+    #$dbh->commit if driver_name() eq 'mysql';
+    #$self->{fetch} = $dbh->prepare("SELECT * FROM $self->{id} ORDER BY rownum LIMIT ?, $buffer");
+
+    # instead, fetch all the rows at once
+    $self->{buffer} = $sth->fetchall_arrayref( {} );
   }
 
   $self;
@@ -115,25 +126,24 @@ sub fetch {
 
 sub refill {
   my $self = shift;
-  my $sth = $self->{fetch};
-  $sth->bind_param(1, $self->{position}) if driver_name() eq 'mysql';
-  $sth->execute or die $sth->errstr;
-  my $result = $self->{fetch}->fetchall_arrayref( {} );
-  $self->{buffer} = $result;
-  $self->{position} += $sth->rows;
-  scalar @$result;
+  if (driver_name() eq 'Pg') {
+    my $sth = $self->{fetch};
+    $sth->bind_param(1, $self->{position}) if driver_name() eq 'mysql';
+    $sth->execute or die $sth->errstr;
+    my $result = $self->{fetch}->fetchall_arrayref( {} );
+    $self->{buffer} = $result;
+    $self->{position} += $sth->rows;
+    scalar @$result;
+  } # mysql can't be refilled, since everything is buffered from the start
 }
 
 sub DESTROY {
   my $self = shift;
+  return if driver_name() eq 'mysql';
+
   return unless $self->{pid} eq $$;
-  if ( driver_name() eq 'Pg' ) {
-    $self->{dbh}->do('CLOSE '. $self->{id})
-      or die $self->{dbh}->errstr; # clean-up the cursor in Pg
-  } elsif ( driver_name() eq 'mysql' ) {
-    # nothing; the temporary table will evaporate when the 
-    # session closes.
-  }
+  $self->{dbh}->do('CLOSE '. $self->{id})
+    or die $self->{dbh}->errstr; # clean-up the cursor in Pg
   $self->{dbh}->rollback;
   $self->{dbh}->disconnect;
 }
@@ -146,8 +156,8 @@ Replace all uses of qsearch with this.
 
 =head1 BUGS
 
-MySQL doesn't support cursors in interactive sessions, only in stored 
-procedures, so we implement our own.  This has not been extensively tested.
+Still doesn't really support MySQL, but it pretends it does, by simply
+running the query and returning records one at a time.
 
 The cursor will close prematurely if any code issues a rollback/commit. If
 you need protection against this use qsearch or fork and get a new dbh
-- 
2.11.0