On very high traffic Piwik servers, prevent integer overflow by making auto_increment fields BIGINT UNSIGNED (#10548)

* Change Latitude and Longitude data types to DECIMAL instead of FLOAT

* Add system test that forces an integer overflow, currently failing as expected

* Make all overflow-able fields BIGINT UNSIGNED

* Make overflow-able fields BIGINT UNSIGNED

* These fields were not covered by the new SimulateAutoIncrementIntegerOverflow test

* fix comment

* Make it clear that id* fields are UNSIGNED when installing Piwik

* BIGINT(10) for consistency

* Update core fields to BIGINT

* UI tests: New fields to be updated to bigint

1 files changed, 78 insertions, 0 deletions

diff --git a/tests/PHPUnit/System/SimulateAutoIncrementIntegerOverflowTest.php b/tests/PHPUnit/System/SimulateAutoIncrementIntegerOverflowTest.php
new file mode 100644
index 0000000000..026c7719a1
--- /dev/null
+++ b/tests/PHPUnit/System/SimulateAutoIncrementIntegerOverflowTest.php
@@ -0,0 +1,78 @@
+<?php
+/**
+ * Piwik - free/libre analytics platform
+ *
+ * @link    http://piwik.org
+ * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
+ */
+namespace Piwik\Tests\System;
+
+use Piwik\Common;
+use Piwik\Db;
+use Piwik\Tests\Framework\TestCase\SystemTestCase;
+use Piwik\Tests\Fixtures\OneVisitorTwoVisits;
+
+/**
+ * Simulates the case where there are more than 4 billion visits and pages, and check that Piwik
+ * can handle this use case without hitting MySQL INT UNSIGNED overflow.
+ *
+ * This system test will compare against the OneVisitorTwoVisitsWithCookieSupportTest system test
+ * (OneVisitorTwoVisits_withCookieSupport* expected api responses) and expect the same API responses.
+ *
+ * @group SimulateAutoIncrementIntegerOverflowTest
+ * @group Core
+ */
+class SimulateAutoIncrementIntegerOverflowTest extends SystemTestCase
+{
+    /** OneVisitorTwoVisits */
+    public static $fixture = null; // initialized below class
+
+    /**
+     * @dataProvider getApiForTesting
+     */
+    public function testApi($api, $params)
+    {
+        $this->runApiTests($api, $params);
+        $this->checkAutoIncrementIdsAreMoreThanFourBillion();
+    }
+
+    private function checkAutoIncrementIdsAreMoreThanFourBillion()
+    {
+        $fieldsThatShouldNotOverflow = array(
+            'log_visit' => 'idvisit',
+            'log_action' => 'idaction',
+            'log_link_visit_action' => 'idlink_va'
+        );
+        $this->assertGreaterThan(4294967294, self::$fixture->maxUnsignedIntegerValue);
+
+        foreach($fieldsThatShouldNotOverflow as $table => $autoIncrementField) {
+            $table = Common::prefixTable($table);
+            $value = Db::fetchOne("SELECT MAX($autoIncrementField) FROM $table ");
+            $this->assertGreaterThan(self::$fixture->maxUnsignedIntegerValue, $value, 'in ' . $table);
+        }
+    }
+
+    public function getApiForTesting()
+    {
+        $apiToCall = array(
+            'VisitTime', 'VisitsSummary', 'VisitorInterest', 'VisitFrequency', 'DevicesDetection',
+            'UserCountry',
+            'Provider', 'Goals', 'CustomVariables', 'CoreAdminHome', 'DevicePlugins',
+            'Actions',  'Referrers',
+        );
+
+        return array(
+            array($apiToCall, array(
+                'idSite' => self::$fixture->idSite,
+                'date' => self::$fixture->dateTime,
+                'compareAgainst' => 'OneVisitorTwoVisits_withCookieSupport',
+            ))
+        );
+    }
+
+}
+
+SimulateAutoIncrementIntegerOverflowTest::$fixture = new OneVisitorTwoVisits();
+SimulateAutoIncrementIntegerOverflowTest::$fixture->useThirdPartyCookies = true;
+SimulateAutoIncrementIntegerOverflowTest::$fixture->useSiteSearch = true;
+SimulateAutoIncrementIntegerOverflowTest::$fixture->simulateIntegerOverflow = true;
\ No newline at end of file