Allow configuration of max execution time (#14858)

* allow configuration of max execution time

* trigger event when query time exceeded

* fix screenshot test

* added some tests and mention mariadb not supported

1 files changed, 165 insertions, 4 deletions

diff --git a/plugins/Live/tests/Integration/ModelTest.php b/plugins/Live/tests/Integration/ModelTest.php
index 2132c9434d..ac27bdf2a8 100644
--- a/plugins/Live/tests/Integration/ModelTest.php
+++ b/plugins/Live/tests/Integration/ModelTest.php
@@ -9,8 +9,11 @@
 namespace Piwik\Plugins\Live\tests\Integration;
 
 use Piwik\Common;
+use Piwik\Config;
 use Piwik\Date;
+use Piwik\Db;
 use Piwik\Piwik;
+use Piwik\Plugins\Live\Exception\MaxExecutionTimeExceededException;
 use Piwik\Plugins\Live\Model;
 use Piwik\Tests\Framework\Fixture;
 use Piwik\Tests\Framework\Mock\FakeAccess;
@@ -68,6 +71,60 @@ class ModelTest extends IntegrationTestCase
         return $validDates;
     }
 
+    public function test_handleMaxExecutionTimeError_doesNotThrowExceptionWhenNotExceededTime()
+    {
+    	$db = Db::get();
+    	$e = new \Exception('foo bar baz');
+	    $sql = 'SELECT 1';
+	    $bind = array();
+	    $segment =  '';
+	    $dateStart = Date::now()->subDay(1);
+	    $dateEnd = Date::now();
+	    $minTimestamp = 1;
+	    $limit = 50;
+        $model = new Model();
+        $model->handleMaxExecutionTimeError($db, $e, $sql, $bind, $segment, $dateStart, $dateEnd, $minTimestamp, $limit);
+        $this->assertTrue(true);
+    }
+
+	/**
+	 * @expectedException \Piwik\Plugins\Live\Exception\MaxExecutionTimeExceededException
+	 * @expectedExceptionMessage Live_QueryMaxExecutionTimeExceeded  Live_QueryMaxExecutionTimeExceededReasonUnknown
+	 */
+    public function test_handleMaxExecutionTimeError_whenTimeIsExceeded_noReasonFound()
+    {
+    	$db = Db::get();
+    	$e = new \Exception('[3024] Query execution was interrupted, maximum statement execution time exceeded');
+	    $sql = 'SELECT 1';
+	    $bind = array();
+	    $segment = '';
+	    $dateStart = Date::now()->subDay(1);
+	    $dateEnd = Date::now();
+	    $minTimestamp = null;
+	    $limit = 50;
+        $model = new Model();
+        $model->handleMaxExecutionTimeError($db, $e, $sql, $bind, $segment, $dateStart, $dateEnd, $minTimestamp, $limit);
+    }
+
+	/**
+	 * @expectedException \Piwik\Plugins\Live\Exception\MaxExecutionTimeExceededException
+	 * @expectedExceptionMessage Live_QueryMaxExecutionTimeExceeded  Live_QueryMaxExecutionTimeExceededReasonDateRange Live_QueryMaxExecutionTimeExceededReasonSegment Live_QueryMaxExecutionTimeExceededLimit
+	 */
+    public function test_handleMaxExecutionTimeError_whenTimeIsExceeded_manyReasonsFound()
+    {
+    	$db = Db::get();
+    	$e = new \Exception('Query execution was interrupted, maximum statement execution time exceeded');
+	    $sql = 'SELECT 1';
+	    $bind = array();
+	    $segment = 'userId>=1';
+	    $dateStart = Date::now()->subDay(10);
+	    $dateEnd = Date::now();
+	    $minTimestamp = null;
+	    $limit = 5000;
+        $model = new Model();
+        $model->handleMaxExecutionTimeError($db, $e, $sql, $bind, $segment, $dateStart, $dateEnd, $minTimestamp, $limit);
+    }
+
     public function test_getStandAndEndDate()
     {
         $model = new Model();
@@ -77,6 +134,54 @@ class ModelTest extends IntegrationTestCase
         $this->assertEquals('2019-01-01 00:00:00', $dateEnd->getDatetime());
     }
 
+    public function test_isLookingAtMoreThanOneDay_whenNoDateSet()
+    {
+        $model = new Model();
+        $this->assertTrue($model->isLookingAtMoreThanOneDay(null, null, null));
+    }
+
+    public function test_isLookingAtMoreThanOneDay_whenNoStartDateSet()
+    {
+        $model = new Model();
+        $this->assertTrue($model->isLookingAtMoreThanOneDay(null, Date::now(), null));
+    }
+
+    public function test_isLookingAtMoreThanOneDay_whenNoStartDateSetAndMinTimestampIsOld()
+    {
+        $model = new Model();
+        $this->assertTrue($model->isLookingAtMoreThanOneDay(null, Date::now(), Date::now()->subDay(5)->getTimestamp()));
+    }
+
+    public function test_isLookingAtMoreThanOneDay_whenNoStartDateSetButMinTimestampIsRecent()
+    {
+        $model = new Model();
+        $this->assertFalse($model->isLookingAtMoreThanOneDay(null, Date::now(), Date::now()->subHour(5)->getTimestamp()));
+    }
+
+    public function test_isLookingAtMoreThanOneDay_whenNoEndDateIsSet_StartDateIsOld()
+    {
+        $model = new Model();
+        $this->assertTrue($model->isLookingAtMoreThanOneDay(Date::now()->subDay(5), null, null));
+    }
+
+    public function test_isLookingAtMoreThanOneDay_whenNoEndDateIsSet_StartDateIsRecent()
+    {
+        $model = new Model();
+        $this->assertFalse($model->isLookingAtMoreThanOneDay(Date::now()->subHour(5), null, null));
+    }
+
+    public function test_isLookingAtMoreThanOneDay_whenStartAndEndDateIsSet_onlyOneDay()
+    {
+        $model = new Model();
+        $this->assertFalse($model->isLookingAtMoreThanOneDay(Date::yesterday()->subDay(1), Date::yesterday(), null));
+    }
+
+    public function test_isLookingAtMoreThanOneDay_whenStartAndEndDateIsSet_moreThanOneDay()
+    {
+        $model = new Model();
+        $this->assertTrue($model->isLookingAtMoreThanOneDay(Date::yesterday()->subDay(2), Date::yesterday(), null));
+    }
+
     public function test_makeLogVisitsQueryString()
     {
         $model = new Model();
@@ -92,7 +197,7 @@ class ModelTest extends IntegrationTestCase
                 $minTimestamp = false,
                 $filterSortOrder = false
         );
-        $expectedSql = ' SELECT sub.* FROM
+        $expectedSql = ' SELECT  sub.* FROM
                 (
                     SELECT log_visit.*
                     FROM ' . Common::prefixTable('log_visit') . ' AS log_visit
@@ -134,7 +239,7 @@ class ModelTest extends IntegrationTestCase
                 $minTimestamp = false,
                 $filterSortOrder = false
         );
-        $expectedSql = ' SELECT sub.* FROM
+        $expectedSql = ' SELECT  sub.* FROM
                 (
                     SELECT log_visit.*
                     FROM ' . Common::prefixTable('log_visit') . ' AS log_visit
@@ -175,7 +280,7 @@ class ModelTest extends IntegrationTestCase
                 $minTimestamp = false,
                 $filterSortOrder = false
         );
-        $expectedSql = ' SELECT sub.* FROM
+        $expectedSql = ' SELECT  sub.* FROM
                 (
                     SELECT log_visit.*
                     FROM ' . Common::prefixTable('log_visit') . ' AS log_visit
@@ -214,7 +319,7 @@ class ModelTest extends IntegrationTestCase
             $minTimestamp = false,
             $filterSortOrder = false
         );
-        $expectedSql = ' SELECT sub.* FROM
+        $expectedSql = ' SELECT  sub.* FROM
                 (
 
                     SELECT log_inner.*
@@ -248,6 +353,62 @@ class ModelTest extends IntegrationTestCase
         $this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedBind), SegmentTest::removeExtraWhiteSpaces($bind));
     }
 
+    public function test_makeLogVisitsQueryString_addsMaxExecutionHintIfConfigured()
+    {
+        $config = Config::getInstance();
+        $general = $config->General;
+        $general['live_query_max_execution_time'] = 30;
+        $config->General = $general;
+
+        $model = new Model();
+        list($dateStart, $dateEnd) = $model->getStartAndEndDate($idSite = 1, 'month', '2010-01-01');
+        list($sql, $bind) = $model->makeLogVisitsQueryString(
+            $idSite = 1,
+            $dateStart,
+            $dateEnd,
+            $segment = false,
+            $offset = 0,
+            $limit = 100,
+            $visitorId = false,
+            $minTimestamp = false,
+            $filterSortOrder = false
+        );
+        $expectedSql = 'SELECT  /*+ MAX_EXECUTION_TIME(30000) */  sub.* FROM (';
+
+        $general['live_query_max_execution_time'] = -1;
+        $config->General = $general;
+
+        $this->assertStringStartsWith($expectedSql, trim($sql));
+    }
+
+    public function test_makeLogVisitsQueryString_doesNotAddsMaxExecutionHintForVisitorIds()
+    {
+        $config = Config::getInstance();
+        $general = $config->General;
+        $general['live_query_max_execution_time'] = 30;
+        $config->General = $general;
+
+        $model = new Model();
+        list($dateStart, $dateEnd) = $model->getStartAndEndDate($idSite = 1, 'month', '2010-01-01');
+        list($sql, $bind) = $model->makeLogVisitsQueryString(
+            $idSite = 1,
+            $dateStart,
+            $dateEnd,
+            $segment = false,
+            $offset = 0,
+            $limit = 100,
+            $visitorId = '1234567812345678',
+            $minTimestamp = false,
+            $filterSortOrder = false
+        );
+        $expectedSql = 'SELECT  sub.* FROM (';
+
+        $general['live_query_max_execution_time'] = -1;
+        $config->General = $general;
+
+        $this->assertStringStartsWith($expectedSql, trim($sql));
+    }
+
     public function test_splitDatesIntoMultipleQueries_notMoreThanADayUsesOnlyOneQuery()
     {
         $dates = $this->splitDatesIntoMultipleQueries('2010-01-01 00:00:00', '2010-01-02 00:00:00', $limit = 5, $offset = 0);