Missing Database Table

billjoseph · April 11, 2019, 4:22pm

Running Matomo 3.9.1 (although this error showed up on earlier versions, too).

My database is getting too large for my taste, so I want to either delete some data or (more likely) reports. Knowledgebase says I can do this on the Privacy>Anonymize Data page. When I navigate to that page I get an error:
## SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mydbname.mydbprefix_privacy_logdata_anonymizations' doesn't exist

What do I need to do to fix this?

billjoseph · April 1, 2021, 9:34pm

Replying to my own topic because it’s been two years and still have no solution. I just updated to 4.2.1 and I’m still getting the error:

## SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mydbname.myydbprefix_privacy_logdata_anonymizations' doesn't exist

I’m also getting a Column not found error:

## SQLSTATE[42S22]: Column not found: 1054 Unknown column 'json_encoded' in 'field list'

that appears on Settings - Platform->Marketplace, and Diagnostic->Database Usage

Still don’t know how to fix this. Ready to ditch Matomo altogether, or maybe I should trash this installation and try again.

Lukas · April 2, 2021, 9:46am

Hi,

The privacy_logdata_anonymizations table should have been created at the 3.5.0 upgrade.

You can check

github.com

matomo-org/matomo/blob/4.x-dev/core/Updates/3.5.0-b2.php

<?php
/**
 * Matomo - free/libre analytics platform
 *
 * @link https://matomo.org
 * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
 *
 */

namespace Piwik\Updates;

use Piwik\Plugins\PrivacyManager\Model\LogDataAnonymizations;
use Piwik\Updater;
use Piwik\Updates as PiwikUpdates;
use Piwik\Updater\Migration\Factory as MigrationFactory;

class Updates_3_5_0_b2 extends PiwikUpdates
{
    /**
     * @var MigrationFactory

This file has been truncated. show original

and

github.com

matomo-org/matomo/blob/047de43169f3eaea0e251b7cfbc5664e10f6fc9c/tests/resources/OmniFixture-dump.sql#L663-L688


--
-- Table structure for table `privacy_logdata_anonymizations`
--
DROP TABLE IF EXISTS `privacy_logdata_anonymizations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `privacy_logdata_anonymizations` (
  `idlogdata_anonymization` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `idsites` text,
  `date_start` datetime NOT NULL,
  `date_end` datetime NOT NULL,
  `anonymize_ip` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `anonymize_location` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `anonymize_userid` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `unset_visit_columns` text NOT NULL,
  `unset_link_visit_action_columns` text NOT NULL,
  `output` mediumtext,
  `scheduled_date` datetime DEFAULT NULL,
  `job_start_date` datetime DEFAULT NULL,

This file has been truncated. show original

for reference on how that table should look like.

And similarly for the missing column:

github.com

matomo-org/matomo/blob/4.x-dev/core/Updates/3.5.0-b4.php#L31-L32


$this->migration->db->addColumn('plugin_setting', 'json_encoded', 'TINYINT UNSIGNED NOT NULL DEFAULT 0'),
$this->migration->db->addColumn('site_setting', 'json_encoded', 'TINYINT UNSIGNED NOT NULL DEFAULT 0'),

https://github.com/matomo-org/matomo/blob/047de43169f3eaea0e251b7cfbc5664e10f6fc9c/tests/resources/OmniFixture-dump.sql#L646

https://github.com/matomo-org/matomo/blob/047de43169f3eaea0e251b7cfbc5664e10f6fc9c/tests/resources/OmniFixture-dump.sql#L920

billjoseph · April 2, 2021, 1:51pm

Thank you so much, Lukas!!

I added the db table privacy_logdata_anonymizations, and the colums json_encoded, and everything seems to be working just fine so far.

I assume the issue was that I messed up an update somewhere along the way. I feel a lot better now. I have a client who wants a simpler alternative to Google Analytics for her suite of WordPress sites and I want to be able to recommend Matomo.

Thanks again for your help!!