Merge pull request #7112 from piwik/6436_action_dupes

Refactor new action insertion code so duplicate actions will not exist + provide means to fix duplicates

5 files changed, 866 insertions, 0 deletions

diff --git a/plugins/CoreAdminHome/Commands/FixDuplicateLogActions.php b/plugins/CoreAdminHome/Commands/FixDuplicateLogActions.php
new file mode 100644
index 0000000000..54b53afabe
--- /dev/null
+++ b/plugins/CoreAdminHome/Commands/FixDuplicateLogActions.php
@@ -0,0 +1,215 @@
+<?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\Plugins\CoreAdminHome\Commands;
+
+use Piwik\Common;
+use Piwik\Container\StaticContainer;
+use Piwik\DataAccess\Actions;
+use Piwik\DataAccess\ArchiveInvalidator;
+use Piwik\Plugin\ConsoleCommand;
+use Piwik\Plugins\CoreAdminHome\Model\DuplicateActionRemover;
+use Piwik\Timer;
+use Psr\Log\LoggerInterface;
+use Symfony\Component\Console\Input\InputInterface;
+use Symfony\Component\Console\Input\InputOption;
+use Symfony\Component\Console\Output\OutputInterface;
+
+/**
+ * Finds duplicate actions rows in log_action and removes them. Fixes references to duplicate
+ * actions in the log_link_visit_action table, log_conversion table, and log_conversion_item
+ * table.
+ *
+ * Prior to version 2.11, there was a race condition in the tracker where it was possible for
+ * two or more actions with the same name and type to be inserted simultaneously. This resulted
+ * in inaccurate data. A Piwik database with this problem can be fixed using this class.
+ *
+ * With version 2.11 and above, it is still possible for duplicate actions to be inserted, but
+ * ONLY if the tracker's PHP process fails suddenly right after inserting an action. This is
+ * very rare, and even if it does happen, report data will not be affected, but the extra
+ * actions can be deleted w/ this class.
+ */
+class FixDuplicateLogActions extends ConsoleCommand
+{
+    /**
+     * Used to invalidate archives. Only used if $shouldInvalidateArchives is true.
+     *
+     * @var ArchiveInvalidator
+     */
+    private $archiveInvalidator;
+
+    /**
+     * DAO used to find duplicate actions in log_action and fix references to them in other tables.
+     *
+     * @var DuplicateActionRemover
+     */
+    private $duplicateActionRemover;
+
+    /**
+     * DAO used to remove actions from the log_action table.
+     *
+     * @var Actions
+     */
+    private $actionsAccess;
+
+    /**
+     * @var LoggerInterface
+     */
+    private $logger;
+
+    /**
+     * Constructor.
+     *
+     * @param ArchiveInvalidator $invalidator
+     * @param DuplicateActionRemover $duplicateActionRemover
+     * @param Actions $actionsAccess
+     * @param LoggerInterface $logger
+     */
+    public function __construct(ArchiveInvalidator $invalidator = null, DuplicateActionRemover $duplicateActionRemover = null,
+                                Actions $actionsAccess = null, LoggerInterface $logger = null)
+    {
+        parent::__construct();
+
+        if ($invalidator === null) {
+            $invalidator = new ArchiveInvalidator();
+        }
+        $this->archiveInvalidator = $invalidator;
+
+        if ($duplicateActionRemover === null) {
+            $duplicateActionRemover = new DuplicateActionRemover();
+        }
+        $this->duplicateActionRemover = $duplicateActionRemover;
+
+        if ($actionsAccess === null) {
+            $actionsAccess = new Actions();
+        }
+        $this->actionsAccess = $actionsAccess;
+
+        if ($logger === null) {
+            $logger = StaticContainer::get('Psr\Log\LoggerInterface');
+        }
+        $this->logger = $logger;
+    }
+
+    protected function configure()
+    {
+        $this->setName('core:fix-duplicate-log-actions');
+        $this->addOption('invalidate-archives', null, InputOption::VALUE_NONE, "If supplied, archives for logs that use duplicate actions will be invalidated."
+            . " On the next cron archive run, the reports for those dates will be re-processed.");
+        $this->setDescription('Removes duplicates in the log action table and fixes references to the duplicates in '
+                            . 'related tables. NOTE: This action can take a long time to run!');
+    }
+
+    protected function execute(InputInterface $input, OutputInterface $output)
+    {
+        $invalidateArchives = $input->getOption('invalidate-archives');
+
+        $timer = new Timer();
+
+        $duplicateActions = $this->duplicateActionRemover->getDuplicateIdActions();
+        if (empty($duplicateActions)) {
+            $output->writeln("Found no duplicate actions.");
+            return;
+        }
+
+        $output->writeln("<info>Found " . count($duplicateActions) . " actions with duplicates.</info>");
+
+        list($numberRemoved, $allArchivesAffected) = $this->fixDuplicateActionReferences($duplicateActions, $output);
+
+        $this->deleteDuplicatesFromLogAction($output, $duplicateActions);
+
+        if ($invalidateArchives) {
+            $this->invalidateArchivesUsingActionDuplicates($allArchivesAffected, $output);
+        } else {
+            $this->printAffectedArchives($allArchivesAffected, $output);
+        }
+
+        $logActionTable = Common::prefixTable('log_action');
+        $this->writeSuccessMessage($output, array(
+            "Found and deleted $numberRemoved duplicate action entries in the $logActionTable table.",
+            "References in log_link_visit_action, log_conversion and log_conversion_item were corrected.",
+            $timer->__toString()
+        ));
+    }
+
+    private function invalidateArchivesUsingActionDuplicates($archivesAffected, OutputInterface $output)
+    {
+        $output->write("Invalidating archives affected by duplicates fixed...");
+        foreach ($archivesAffected as $archiveInfo) {
+            $this->archiveInvalidator->markArchivesAsInvalidated(
+                array($archiveInfo['idsite']), $archiveInfo['server_time'], $period = false);
+        }
+        $output->writeln("Done.");
+    }
+
+    private function printAffectedArchives($allArchivesAffected, OutputInterface $output)
+    {
+        $output->writeln("The following archives used duplicate actions and should be invalidated if you want correct reports:");
+        foreach ($allArchivesAffected as $archiveInfo) {
+            $output->writeln("\t[ idSite = {$archiveInfo['idsite']}, date = {$archiveInfo['server_time']} ]");
+        }
+    }
+
+    private function fixDuplicateActionReferences($duplicateActions, OutputInterface $output)
+    {
+        $dupeCount = count($duplicateActions);
+
+        $numberRemoved = 0;
+        $allArchivesAffected = array();
+
+        foreach ($duplicateActions as $index => $dupeInfo) {
+            $name = $dupeInfo['name'];
+            $toIdAction = $dupeInfo['idaction'];
+            $fromIdActions = $dupeInfo['duplicateIdActions'];
+
+            $numberRemoved += count($fromIdActions);
+
+            $output->writeln("<info>[$index / $dupeCount]</info> Fixing duplicates for '$name'");
+
+            $this->logger->debug("  idaction = {idaction}, duplicate idactions = {duplicateIdActions}", array(
+                'idaction' => $toIdAction,
+                'duplicateIdActions' => $fromIdActions
+            ));
+
+            foreach (DuplicateActionRemover::$tablesWithIdActionColumns as $table) {
+                $archivesAffected = $this->fixDuplicateActionsInTable($output, $table, $toIdAction, $fromIdActions);
+                $allArchivesAffected = array_merge($allArchivesAffected, $archivesAffected);
+            }
+        }
+
+        $allArchivesAffected = array_values(array_unique($allArchivesAffected, SORT_REGULAR));
+
+        return array($numberRemoved, $allArchivesAffected);
+    }
+
+    private function fixDuplicateActionsInTable(OutputInterface $output, $table, $toIdAction, $fromIdActions)
+    {
+        $timer = new Timer();
+
+        $archivesAffected = $this->duplicateActionRemover->getSitesAndDatesOfRowsUsingDuplicates($table, $fromIdActions);
+
+        $this->duplicateActionRemover->fixDuplicateActionsInTable($table, $toIdAction, $fromIdActions);
+
+        $output->writeln("\tFixed duplicates in " . Common::prefixTable($table) . ". <comment>" . $timer->__toString() . "</comment>.");
+
+        return $archivesAffected;
+    }
+
+    private function deleteDuplicatesFromLogAction(OutputInterface $output, $duplicateActions)
+    {
+        $logActionTable = Common::prefixTable('log_action');
+        $output->writeln("<info>Deleting duplicate actions from $logActionTable...</info>");
+
+        $idActions = array();
+        foreach ($duplicateActions as $dupeInfo) {
+            $idActions = array_merge($idActions, $dupeInfo['duplicateIdActions']);
+        }
+
+        $this->actionsAccess->delete($idActions);
+    }
+}
\ No newline at end of file
diff --git a/plugins/CoreAdminHome/Model/DuplicateActionRemover.php b/plugins/CoreAdminHome/Model/DuplicateActionRemover.php
new file mode 100644
index 0000000000..51a93bab47
--- /dev/null
+++ b/plugins/CoreAdminHome/Model/DuplicateActionRemover.php
@@ -0,0 +1,193 @@
+<?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\Plugins\CoreAdminHome\Model;
+
+use Piwik\Common;
+use Piwik\Container\StaticContainer;
+use Piwik\DataAccess\TableMetadata;
+use Piwik\Db;
+use Psr\Log\LoggerInterface;
+
+/**
+ * Provides methods to find duplicate actions and fix duplicate action references in tables
+ * that reference log_action rows.
+ */
+class DuplicateActionRemover
+{
+    /**
+     * The tables that contain idaction reference columns.
+     *
+     * @var string[]
+     */
+    public static $tablesWithIdActionColumns = array(
+        'log_link_visit_action',
+        'log_conversion',
+        'log_conversion_item'
+    );
+
+    /**
+     * DAO used to get idaction column names in tables that reference log_action rows.
+     *
+     * @var TableMetadata
+     */
+    private $tableMetadataAccess;
+
+    /**
+     * @var LoggerInterface
+     */
+    private $logger;
+
+    /**
+     * List of idaction columns in each table in $tablesWithIdActionColumns. idaction
+     * columns are table columns with the string `"idaction"` in them.
+     *
+     * @var string[]
+     */
+    private $idactionColumns;
+
+    /**
+     * Constructor.
+     *
+     * @param TableMetadata $tableMetadataAccess
+     * @param LoggerInterface $logger
+     */
+    public function __construct($tableMetadataAccess = null, $logger = null)
+    {
+        if ($tableMetadataAccess === null) {
+            $tableMetadataAccess = new TableMetadata();
+        }
+        $this->tableMetadataAccess = $tableMetadataAccess;
+
+        if ($logger === null) {
+            $logger = StaticContainer::get('Psr\Log\LoggerInterface');
+        }
+        $this->logger = $logger;
+
+        $this->idactionColumns = $this->getIdActionTableColumnsFromMetadata();
+    }
+
+    /**
+     * Returns list of all duplicate actions in the log_action table by name and the lowest action ID.
+     * The duplicate actions are returned with each action.
+     *
+     * @return array Contains the following elements:
+     *
+     *               * **name**: The action's name.
+     *               * **idaction**: The action's ID.
+     *               * **duplicateIdActions**: An array of duplicate action IDs.
+     */
+    public function getDuplicateIdActions()
+    {
+        $sql = "SELECT name, COUNT(*) AS count, GROUP_CONCAT(idaction ORDER BY idaction ASC SEPARATOR ',') as idactions
+                  FROM " . Common::prefixTable('log_action') . "
+              GROUP BY name, hash, type HAVING count > 1";
+
+        $result = array();
+        foreach (Db::fetchAll($sql) as $row) {
+            $dupeInfo = array('name' => $row['name']);
+
+            $idActions = explode(",", $row['idactions']);
+            $dupeInfo['idaction'] = array_shift($idActions);
+            $dupeInfo['duplicateIdActions'] = $idActions;
+
+            $result[] = $dupeInfo;
+        }
+        return $result;
+    }
+
+    /**
+     * Executes one SQL statement that sets all idaction columns in a table to a single value, if the
+     * values of those columns are in the specified set (`$duplicateIdActions`).
+     *
+     * Notes:
+     *
+     * The SQL will look like:
+     *
+     *     UPDATE $table SET
+     *         col1 = IF((col1 IN ($duplicateIdActions)), $realIdAction, col1),
+     *         col2 = IF((col2 IN ($duplicateIdActions)), $realIdAction, col2),
+     *         ...
+     *      WHERE col1 IN ($duplicateIdActions) OR col2 IN ($duplicateIdActions) OR ...
+     *
+     * @param string $table
+     * @param int $realIdAction The idaction to set column values to.
+     * @param int[] $duplicateIdActions The idaction values that should be changed.
+     */
+    public function fixDuplicateActionsInTable($table, $realIdAction, $duplicateIdActions)
+    {
+        $idactionColumns = array_values($this->idactionColumns[$table]);
+        $table = Common::prefixTable($table);
+
+        $inFromIdsExpression = $this->getInFromIdsExpression($duplicateIdActions);
+        $setExpression = "%1\$s = IF(($inFromIdsExpression), $realIdAction, %1\$s)";
+
+        $sql = "UPDATE $table SET\n";
+        foreach ($idactionColumns as $index => $column) {
+            if ($index != 0) {
+                $sql .= ",\n";
+            }
+            $sql .= sprintf($setExpression, $column);
+        }
+        $sql .= $this->getWhereToGetRowsUsingDuplicateActions($idactionColumns, $duplicateIdActions);
+
+        Db::query($sql);
+    }
+
+    /**
+     * Returns the server time and idsite of rows in a log table that reference at least one action
+     * in a set.
+     *
+     * @param string $table
+     * @param int[] $duplicateIdActions
+     * @return array with two elements **idsite** and **server_time**. idsite is the site ID and server_time
+     *               is the date of the log.
+     */
+    public function getSitesAndDatesOfRowsUsingDuplicates($table, $duplicateIdActions)
+    {
+        $idactionColumns = array_values($this->idactionColumns[$table]);
+        $table = Common::prefixTable($table);
+
+        $sql = "SELECT idsite, DATE(server_time) as server_time FROM $table ";
+        $sql .= $this->getWhereToGetRowsUsingDuplicateActions($idactionColumns, $duplicateIdActions);
+        return Db::fetchAll($sql);
+    }
+
+    private function getIdActionTableColumnsFromMetadata()
+    {
+        $result = array();
+        foreach (self::$tablesWithIdActionColumns as $table) {
+            $columns = $this->tableMetadataAccess->getIdActionColumnNames(Common::prefixTable($table));
+
+            $this->logger->debug("Found following idactions in {table}: {columns}", array(
+                'table' => $table,
+                'columns' => implode(',', $columns)
+            ));
+
+            $result[$table] = $columns;
+        }
+        return $result;
+    }
+
+    private function getWhereToGetRowsUsingDuplicateActions($idactionColumns, $fromIdActions)
+    {
+        $sql = "WHERE ";
+        foreach ($idactionColumns as $index => $column) {
+            if ($index != 0) {
+                $sql .= "OR ";
+            }
+
+            $sql .= sprintf($this->getInFromIdsExpression($fromIdActions), $column) . " ";
+        }
+        return $sql;
+    }
+
+    private function getInFromIdsExpression($fromIdActions)
+    {
+        return "%1\$s IN (" . implode(',', $fromIdActions) . ")";
+    }
+}
\ No newline at end of file
diff --git a/plugins/CoreAdminHome/tests/Fixture/DuplicateActions.php b/plugins/CoreAdminHome/tests/Fixture/DuplicateActions.php
new file mode 100644
index 0000000000..7d98a57670
--- /dev/null
+++ b/plugins/CoreAdminHome/tests/Fixture/DuplicateActions.php
@@ -0,0 +1,171 @@
+<?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\Plugins\CoreAdminHome\tests\Fixture;
+
+use Piwik\Common;
+use Piwik\Db;
+use Piwik\Tests\Framework\Fixture;
+
+/**
+ * Fixture that adds log table rows that use duplicate actions.
+ */
+class DuplicateActions extends Fixture
+{
+    const DUMMY_IDVISITOR = 'c1d2a36653fd88e2';
+
+    private static $dataToInsert = array(
+        'log_action' => array(
+            array('name' => 'action1', 'type' => 1),
+            array('name' => 'action1', 'type' => 1),
+            array('name' => 'action1', 'type' => 1),
+
+            array('name' => 'action2', 'type' => 2),
+            array('name' => 'ACTION2', 'type' => 1),
+            array('name' => 'action4', 'type' => 3),
+            array('name' => 'ACTION2', 'type' => 1),
+            array('name' => 'action5', 'type' => 2),
+
+            array('name' => 'action2', 'type' => 2),
+            array('name' => 'action4', 'type' => 3),
+            array('name' => 'ACTION2', 'type' => 1),
+            array('name' => 'action4', 'type' => 3),
+        ),
+        'log_link_visit_action' => array(
+            array(
+                'idsite' => 1,
+                'idvisitor' => self::DUMMY_IDVISITOR,
+                'idvisit' => 1,
+                'server_time' => '2012-01-01 00:00:00',
+                'time_spent_ref_action' => 100,
+                'idaction_url_ref' => 1,
+                'idaction_name_ref' => 2,
+                'idaction_name' => 3,
+                'idaction_url' => 4,
+                'idaction_event_action' => 5,
+                'idaction_event_category' => 6,
+                'idaction_content_interaction' => 7,
+                'idaction_content_name' => 8,
+                'idaction_content_piece' => 9,
+                'idaction_content_target' => 10,
+            ),
+            array(
+                'idsite' => 2,
+                'idvisitor' => self::DUMMY_IDVISITOR,
+                'idvisit' => 2,
+                'server_time' => '2013-01-01 00:00:00',
+                'time_spent_ref_action' => 120,
+                'idaction_url_ref' => 2,
+                'idaction_name_ref' => 3,
+                'idaction_name' => 5,
+                'idaction_url' => 7,
+                'idaction_event_action' => 9,
+                'idaction_event_category' => 10,
+                'idaction_content_interaction' => 11,
+                'idaction_content_name' => 11,
+                'idaction_content_piece' => 12,
+                'idaction_content_target' => 12,
+            ),
+        ),
+        'log_conversion' => array(
+            array(
+                'idvisit' => 1,
+                'idsite' => 1,
+                'idvisitor' => self::DUMMY_IDVISITOR,
+                'server_time' => '2012-02-01 00:00:00',
+                'idgoal' => 1,
+                'buster' => 1,
+                'url' => 'http://example.com/',
+                'location_country' => 'nz',
+                'visitor_count_visits' => 1,
+                'visitor_returning' => 1,
+                'visitor_days_since_order' => 1,
+                'visitor_days_since_first' => 1,
+                'idaction_url' => 4,
+            ),
+
+            array(
+                'idvisit' => 2,
+                'idsite' => 2,
+                'idvisitor' => self::DUMMY_IDVISITOR,
+                'server_time' => '2012-03-01 00:00:00',
+                'idgoal' => 2,
+                'buster' => 2,
+                'url' => 'http://example.com/',
+                'location_country' => 'nz',
+                'visitor_count_visits' => 1,
+                'visitor_returning' => 1,
+                'visitor_days_since_order' => 1,
+                'visitor_days_since_first' => 1,
+                'idaction_url' => 7,
+            )
+        ),
+        'log_conversion_item' => array(
+            array(
+                'idsite' => 1,
+                'idvisitor' => self::DUMMY_IDVISITOR,
+                'server_time' => '2012-02-01 00:00:00',
+                'idvisit' => 1,
+                'idorder' => 1,
+                'price' => 10,
+                'quantity' => 2,
+                'deleted' => 0,
+                'idaction_sku' => 1,
+                'idaction_name' => 2,
+                'idaction_category' => 3,
+                'idaction_category2' => 4,
+                'idaction_category3' => 5,
+                'idaction_category4' => 6,
+                'idaction_category5' => 7,
+            ),
+            array(
+                'idsite' => 2,
+                'idvisitor' => self::DUMMY_IDVISITOR,
+                'server_time' => '2012-01-09 00:00:00',
+                'idvisit' => 2,
+                'idorder' => 2,
+                'price' => 10,
+                'quantity' => 1,
+                'deleted' => 1,
+                'idaction_sku' => 2,
+                'idaction_name' => 3,
+                'idaction_category' => 5,
+                'idaction_category2' => 7,
+                'idaction_category3' => 8,
+                'idaction_category4' => 9,
+                'idaction_category5' => 10,
+            )
+        )
+    );
+
+    public function setUp()
+    {
+        parent::setUp();
+
+        foreach (self::$dataToInsert as $table => $rows) {
+            self::insertRowData($table, $rows);
+        }
+    }
+
+    private static function insertRowData($unprefixedTable, $rows)
+    {
+        $table = Common::prefixTable($unprefixedTable);
+        foreach ($rows as $row) {
+            if ($unprefixedTable == 'log_action') {
+                $row['hash'] = crc32($row['name']);
+            }
+
+            if (isset($row['idvisitor'])) {
+                $row['idvisitor'] = pack("H*", $row['idvisitor']);
+            }
+
+            $placeholders = array_map(function () { return "?"; }, $row);
+            $sql = "INSERT INTO $table (" . implode(',', array_keys($row)) . ") VALUES (" . implode(',', $placeholders) . ")";
+            Db::query($sql, array_values($row));
+        }
+    }
+}
\ No newline at end of file
diff --git a/plugins/CoreAdminHome/tests/Integration/FixDuplicateActionsTest.php b/plugins/CoreAdminHome/tests/Integration/FixDuplicateActionsTest.php
new file mode 100644
index 0000000000..ca56f3b026
--- /dev/null
+++ b/plugins/CoreAdminHome/tests/Integration/FixDuplicateActionsTest.php
@@ -0,0 +1,175 @@
+<?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\Plugins\CoreAdminHome\tests\Integration;
+
+use Piwik\Common;
+use Piwik\Console;
+use Piwik\Db;
+use Piwik\Plugin\Manager as PluginManager;
+use Piwik\Plugins\CoreAdminHome\tests\Fixture\DuplicateActions;
+use Piwik\Plugins\QueuedTracking\tests\Framework\TestCase\IntegrationTestCase;
+use Symfony\Component\Console\Tester\ApplicationTester;
+
+/**
+ * @group Core
+ */
+class FixDuplicateActionsTest extends IntegrationTestCase
+{
+    /**
+     * @var DuplicateActions
+     */
+    public static $fixture = null;
+
+    /**
+     * @var ApplicationTester
+     */
+    protected $applicationTester = null;
+
+    public function setUp()
+    {
+        parent::setUp();
+
+        $application = new Console();
+        $application->setAutoExit(false);
+
+        $this->applicationTester = new ApplicationTester($application);
+    }
+
+    public function test_FixDuplicateLogActions_CorrectlyRemovesDuplicates_AndFixesReferencesInOtherTables()
+    {
+        $result = $this->applicationTester->run(array(
+            'command' => 'core:fix-duplicate-log-actions',
+            '--invalidate-archives' => 0,
+            '-vvv' => false
+        ));
+
+        $this->assertEquals(0, $result, "Command failed: " . $this->applicationTester->getDisplay());
+
+        $this->assertDuplicateActionsRemovedFromLogActionTable();
+        $this->assertDuplicatesFixedInLogLinkVisitActionTable();
+        $this->assertDuplicatesFixedInLogConversionTable();
+        $this->assertDuplicatesFixedInLogConversionItemTable();
+
+        $this->assertContains("Found and deleted 7 duplicate action entries", $this->applicationTester->getDisplay());
+
+        $expectedAffectedArchives = array(
+            array('idsite' => '1', 'server_time' => '2012-01-01'),
+            array('idsite' => '2', 'server_time' => '2013-01-01'),
+            array('idsite' => '1', 'server_time' => '2012-02-01'),
+            array('idsite' => '2', 'server_time' => '2012-01-09'),
+            array('idsite' => '2', 'server_time' => '2012-03-01'),
+        );
+        foreach ($expectedAffectedArchives as $archive) {
+            $this->assertContains("[ idSite = {$archive['idsite']}, date = {$archive['server_time']} ]", $this->applicationTester->getDisplay());
+        }
+    }
+
+    private function assertDuplicateActionsRemovedFromLogActionTable()
+    {
+        $actions = Db::fetchAll("SELECT idaction, name FROM " . Common::prefixTable('log_action'));
+        $expectedActions = array(
+            array('idaction' => 1, 'name' => 'action1'),
+            array('idaction' => 4, 'name' => 'action2'),
+            array('idaction' => 5, 'name' => 'ACTION2'),
+            array('idaction' => 6, 'name' => 'action4'),
+            array('idaction' => 8, 'name' => 'action5'),
+        );
+        $this->assertEquals($expectedActions, $actions);
+    }
+
+    private function assertDuplicatesFixedInLogLinkVisitActionTable()
+    {
+        $columns = array(
+            'idaction_url_ref',
+            'idaction_name_ref',
+            'idaction_name',
+            'idaction_url',
+            'idaction_event_action',
+            'idaction_event_category',
+            'idaction_content_interaction',
+            'idaction_content_name',
+            'idaction_content_piece',
+            'idaction_content_target'
+        );
+        $rows = Db::fetchAll("SELECT " . implode(',', $columns) . " FROM " . Common::prefixTable('log_link_visit_action'));
+        $expectedRows = array(
+            array(
+                'idaction_url_ref' => '1',
+                'idaction_name_ref' => '1',
+                'idaction_name' => '1',
+                'idaction_url' => '4',
+                'idaction_event_action' => '5',
+                'idaction_event_category' => '6',
+                'idaction_content_interaction' => '5',
+                'idaction_content_name' => '8',
+                'idaction_content_piece' => '4',
+                'idaction_content_target' => '6'
+            ),
+            array(
+                'idaction_url_ref' => '1',
+                'idaction_name_ref' => '1',
+                'idaction_name' => '5',
+                'idaction_url' => '5',
+                'idaction_event_action' => '4',
+                'idaction_event_category' => '6',
+                'idaction_content_interaction' => '5',
+                'idaction_content_name' => '5',
+                'idaction_content_piece' => '6',
+                'idaction_content_target' => '6'
+            )
+        );
+        $this->assertEquals($expectedRows, $rows);
+    }
+
+    private function assertDuplicatesFixedInLogConversionTable()
+    {
+        $rows = Db::fetchAll("SELECT idaction_url FROM " . Common::prefixTable('log_conversion'));
+        $expectedRows = array(
+            array('idaction_url' => 4),
+            array('idaction_url' => 5)
+        );
+        $this->assertEquals($expectedRows, $rows);
+    }
+
+    private function assertDuplicatesFixedInLogConversionItemTable()
+    {
+        $columns = array(
+            'idaction_sku',
+            'idaction_name',
+            'idaction_category',
+            'idaction_category2',
+            'idaction_category3',
+            'idaction_category4',
+            'idaction_category5'
+        );
+        $rows = Db::fetchAll("SELECT " . implode(',', $columns) . " FROM " . Common::prefixTable('log_conversion_item'));
+        $expectedRows = array(
+            array(
+                'idaction_sku' => '1',
+                'idaction_name' => '1',
+                'idaction_category' => '1',
+                'idaction_category2' => '4',
+                'idaction_category3' => '5',
+                'idaction_category4' => '6',
+                'idaction_category5' => '5'
+            ),
+            array(
+                'idaction_sku' => '1',
+                'idaction_name' => '1',
+                'idaction_category' => '5',
+                'idaction_category2' => '5',
+                'idaction_category3' => '8',
+                'idaction_category4' => '4',
+                'idaction_category5' => '6'
+            )
+        );
+        $this->assertEquals($expectedRows, $rows);
+    }
+}
+
+FixDuplicateActionsTest::$fixture = new DuplicateActions();
\ No newline at end of file
diff --git a/plugins/CoreAdminHome/tests/Integration/Model/DuplicateActionRemoverTest.php b/plugins/CoreAdminHome/tests/Integration/Model/DuplicateActionRemoverTest.php
new file mode 100644
index 0000000000..a898ef1616
--- /dev/null
+++ b/plugins/CoreAdminHome/tests/Integration/Model/DuplicateActionRemoverTest.php
@@ -0,0 +1,112 @@
+<?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\Plugins\CoreAdminHome\tests\Integration\Model;
+
+use Piwik\Common;
+use Piwik\Db;
+use Piwik\Plugins\CoreAdminHome\Model\DuplicateActionRemover;
+use Piwik\Plugins\CoreAdminHome\tests\Fixture\DuplicateActions;
+use Piwik\Tests\Framework\TestCase\IntegrationTestCase;
+
+/**
+ * @group Core
+ */
+class DuplicateActionRemoverTest extends IntegrationTestCase
+{
+    /**
+     * @var DuplicateActions
+     */
+    public static $fixture = null;
+
+    /**
+     * @var DuplicateActionRemover
+     */
+    private $duplicateActionRemover;
+
+    public function setUp()
+    {
+        parent::setUp();
+
+        $this->duplicateActionRemover = new DuplicateActionRemover();
+    }
+
+    public function test_getDuplicateIdActions_ReturnsDuplicateIdActions_AndTreatsLowestIdActionAsOriginal()
+    {
+        $expectedResult = array(
+            array('name' => 'action1', 'idaction' => 1, 'duplicateIdActions' => array(2, 3)),
+            array('name' => 'ACTION2', 'idaction' => 5, 'duplicateIdActions' => array(7, 11)),
+            array('name' => 'action2', 'idaction' => 4, 'duplicateIdActions' => array(9)),
+            array('name' => 'action4', 'idaction' => 6, 'duplicateIdActions' => array(10, 12)),
+        );
+        $actualResult = $this->duplicateActionRemover->getDuplicateIdActions();
+        $this->assertEquals($expectedResult, $actualResult);
+    }
+
+    public function test_fixDuplicateActionsInTable_CorrectlyUpdatesIdActionColumns_InSpecifiedTable()
+    {
+        $this->duplicateActionRemover->fixDuplicateActionsInTable('log_conversion_item', 5, array(3, 6, 7, 10));
+
+        $columns = array('idaction_sku', 'idaction_name', 'idaction_category', 'idaction_category2',
+                         'idaction_category3', 'idaction_category4', 'idaction_category5');
+
+        $expectedResult = array(
+            array(
+                'idaction_sku' => '1',
+                'idaction_name' => '2',
+                'idaction_category' => '5',
+                'idaction_category2' => '4',
+                'idaction_category3' => '5',
+                'idaction_category4' => '5',
+                'idaction_category5' => '5'
+            ),
+            array(
+                'idaction_sku' => '2',
+                'idaction_name' => '5',
+                'idaction_category' => '5',
+                'idaction_category2' => '5',
+                'idaction_category3' => '8',
+                'idaction_category4' => '9',
+                'idaction_category5' => '5'
+            ),
+        );
+        $actualResult = Db::fetchAll("SELECT " . implode(", ", $columns) . " FROM " . Common::prefixTable('log_conversion_item'));
+        $this->assertEquals($expectedResult, $actualResult);
+    }
+
+    public function test_getSitesAndDatesOfRowsUsingDuplicates_ReturnsTheServerTimeAndIdSite_OfRowsUsingSpecifiedActionIds()
+    {
+        $row = array(
+            'idsite' => 3,
+            'idvisitor' => pack("H*", DuplicateActions::DUMMY_IDVISITOR),
+            'server_time' => '2012-02-13 00:00:00',
+            'idvisit' => 5,
+            'idorder' => 6,
+            'price' => 15,
+            'quantity' => 21,
+            'deleted' => 1,
+            'idaction_sku' => 3,
+            'idaction_name' => 3,
+            'idaction_category' => 12,
+            'idaction_category2' => 3,
+            'idaction_category3' => 3,
+            'idaction_category4' => 3,
+            'idaction_category5' => 3,
+        );
+        Db::query("INSERT INTO " . Common::prefixTable('log_conversion_item') . " (" . implode(", ", array_keys($row))
+            . ") VALUES ('" . implode("', '", array_values($row)) . "')");
+
+        $expectedResult = array(
+            array('idsite' => 1, 'server_time' => '2012-02-01'),
+            array('idsite' => 3, 'server_time' => '2012-02-13')
+        );
+        $actualResult = $this->duplicateActionRemover->getSitesAndDatesOfRowsUsingDuplicates('log_conversion_item', array(4, 6, 12));
+        $this->assertEquals($expectedResult, $actualResult);
+    }
+}
+
+DuplicateActionRemoverTest::$fixture = new DuplicateActions();
\ No newline at end of file