Piwik doesn't track anymore


(Primo Retem) #1

Hi,

2 days ago I installed a couple of plugins and since then, Piwik isn’t tracking any hits any more.

One of the Plugins was the “better geo localization” plugin and I think this is the culprit, because it altered the database.
Since my first version (0.5.something) the database was altered a lot of times but without any problems.
Is it possible to reinstall piwik on a different domain with a fresh, clean database and reimport the data from my actual, altered database?

A dump of the old data with following import into the clean database won’t help because the structures don’t match anymore.
Is there a tool for such a task? Exporting only data that matches the structure of the original database after a fresh install?
And what do I do with the archive tables? Can they be copied and they will be correctly recognized?

I don’t know if it helps, but this is my actual structure:

--
-- Datenbank: `prpiwik`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_access`
--

CREATE TABLE `piwik_access` (
  `login` varchar(100) NOT NULL,
  `idsite` int(10) unsigned NOT NULL,
  `access` varchar(10) default NULL,
  PRIMARY KEY  (`login`,`idsite`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_archive_blob_2009_01`
--

CREATE TABLE `piwik_archive_blob_2009_01` (
  `idarchive` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `idsite` int(10) unsigned default NULL,
  `date1` date default NULL,
  `date2` date default NULL,
  `period` tinyint(3) unsigned default NULL,
  `ts_archived` datetime default NULL,
  `value` mediumblob,
  PRIMARY KEY  (`idarchive`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_archive_numeric_2009_01`
--

CREATE TABLE `piwik_archive_numeric_2009_01` (
  `idarchive` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `idsite` int(10) unsigned default NULL,
  `date1` date default NULL,
  `date2` date default NULL,
  `period` tinyint(3) unsigned default NULL,
  `ts_archived` datetime default NULL,
  `value` float default NULL,
  PRIMARY KEY  (`idarchive`,`name`),
  KEY `index_idsite_dates_period` (`idsite`,`date1`,`date2`,`period`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_goal`
--

CREATE TABLE `piwik_goal` (
  `idsite` int(11) NOT NULL,
  `idgoal` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `match_attribute` varchar(20) NOT NULL,
  `pattern` varchar(255) NOT NULL,
  `pattern_type` varchar(10) NOT NULL,
  `case_sensitive` tinyint(4) NOT NULL,
  `revenue` float NOT NULL,
  `deleted` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`idsite`,`idgoal`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_logger_api_call`
--

CREATE TABLE `piwik_logger_api_call` (
  `idlogger_api_call` int(10) unsigned NOT NULL auto_increment,
  `class_name` varchar(255) default NULL,
  `method_name` varchar(255) default NULL,
  `parameter_names_default_values` text,
  `parameter_values` text,
  `execution_time` float default NULL,
  `caller_ip` int(10) unsigned default NULL,
  `timestamp` timestamp NULL default NULL,
  `returned_value` text,
  PRIMARY KEY  (`idlogger_api_call`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_logger_error`
--

CREATE TABLE `piwik_logger_error` (
  `idlogger_error` int(10) unsigned NOT NULL auto_increment,
  `timestamp` timestamp NULL default NULL,
  `message` text,
  `errno` int(10) unsigned default NULL,
  `errline` int(10) unsigned default NULL,
  `errfile` varchar(255) default NULL,
  `backtrace` text,
  PRIMARY KEY  (`idlogger_error`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_logger_exception`
--

CREATE TABLE `piwik_logger_exception` (
  `idlogger_exception` int(10) unsigned NOT NULL auto_increment,
  `timestamp` timestamp NULL default NULL,
  `message` text,
  `errno` int(10) unsigned default NULL,
  `errline` int(10) unsigned default NULL,
  `errfile` varchar(255) default NULL,
  `backtrace` text,
  PRIMARY KEY  (`idlogger_exception`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_logger_message`
--

CREATE TABLE `piwik_logger_message` (
  `idlogger_message` int(10) unsigned NOT NULL auto_increment,
  `timestamp` timestamp NULL default NULL,
  `message` text,
  PRIMARY KEY  (`idlogger_message`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_log_action`
--

CREATE TABLE `piwik_log_action` (
  `idaction` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `hash` int(10) unsigned NOT NULL,
  `type` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`idaction`),
  KEY `index_type_hash` (`type`,`hash`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=280165 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_log_conversion`
--

CREATE TABLE `piwik_log_conversion` (
  `idvisit` int(10) unsigned NOT NULL,
  `idsite` int(10) unsigned NOT NULL,
  `visitor_idcookie` char(32) NOT NULL,
  `server_time` datetime NOT NULL,
  `idaction_url` int(11) default NULL,
  `idlink_va` int(11) default NULL,
  `referer_idvisit` int(10) unsigned default NULL,
  `referer_visit_server_date` date default NULL,
  `referer_type` int(10) unsigned default NULL,
  `referer_name` varchar(70) default NULL,
  `referer_keyword` varchar(255) default NULL,
  `visitor_returning` tinyint(1) NOT NULL,
  `location_country` char(3) NOT NULL,
  `location_continent` char(3) NOT NULL,
  `url` text NOT NULL,
  `idgoal` int(10) unsigned NOT NULL,
  `revenue` float default NULL,
  PRIMARY KEY  (`idvisit`,`idgoal`),
  KEY `index_idsite_datetime` (`idsite`,`server_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_log_link_visit_action`
--

CREATE TABLE `piwik_log_link_visit_action` (
  `idlink_va` int(11) NOT NULL auto_increment,
  `idvisit` int(10) unsigned NOT NULL,
  `idaction_url` int(10) unsigned NOT NULL,
  `idaction_url_ref` int(10) unsigned NOT NULL,
  `idaction_name` int(10) unsigned default NULL,
  `time_spent_ref_action` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`idlink_va`),
  KEY `index_idvisit` (`idvisit`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1180440 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_log_profiling`
--

CREATE TABLE `piwik_log_profiling` (
  `query` text NOT NULL,
  `count` int(10) unsigned default NULL,
  `sum_time_ms` float default NULL,
  UNIQUE KEY `query` (`query`(100))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_log_visit`
--

CREATE TABLE `piwik_log_visit` (
  `idvisit` int(10) unsigned NOT NULL auto_increment,
  `idsite` int(10) unsigned NOT NULL,
  `visitor_localtime` time NOT NULL,
  `visitor_idcookie` char(32) NOT NULL,
  `visitor_returning` tinyint(1) NOT NULL,
  `visit_first_action_time` datetime NOT NULL,
  `visit_last_action_time` datetime NOT NULL,
  `visit_server_date` date NOT NULL,
  `visit_exit_idaction_url` int(11) NOT NULL,
  `visit_entry_idaction_url` int(11) NOT NULL,
  `visit_total_actions` smallint(5) unsigned NOT NULL,
  `visit_total_time` smallint(5) unsigned NOT NULL,
  `visit_goal_converted` tinyint(1) NOT NULL,
  `referer_type` int(10) unsigned default NULL,
  `referer_name` varchar(70) default NULL,
  `referer_url` text NOT NULL,
  `referer_keyword` varchar(255) default NULL,
  `config_md5config` char(32) NOT NULL,
  `config_os` char(3) NOT NULL,
  `config_browser_name` varchar(10) NOT NULL,
  `config_browser_version` varchar(20) NOT NULL,
  `config_resolution` varchar(9) NOT NULL,
  `config_pdf` tinyint(1) NOT NULL,
  `config_flash` tinyint(1) NOT NULL,
  `config_java` tinyint(1) NOT NULL,
  `config_director` tinyint(1) NOT NULL,
  `config_quicktime` tinyint(1) NOT NULL,
  `config_realplayer` tinyint(1) NOT NULL,
  `config_windowsmedia` tinyint(1) NOT NULL,
  `config_gears` tinyint(1) NOT NULL,
  `config_silverlight` tinyint(1) NOT NULL,
  `config_cookie` tinyint(1) NOT NULL,
  `location_ip` int(10) unsigned NOT NULL,
  `location_browser_lang` varchar(20) NOT NULL,
  `location_country` char(3) NOT NULL,
  `location_continent` char(3) NOT NULL,
  `location_provider` varchar(100) default NULL,
  `visit_entry_idaction` int(11) NOT NULL,
  `page` tinyint(4) default NULL,
  `location_geoip_continent` varchar(100) default NULL,
  `location_geoip_country` varchar(100) default NULL,
  `location_geoip_city` varchar(100) default NULL,
  `location_geoip_latitude` decimal(7,4) default NULL,
  `location_geoip_longitude` decimal(7,4) default NULL,
  PRIMARY KEY  (`idvisit`),
  KEY `index_idsite_date_config` (`idsite`,`visit_server_date`,`config_md5config`(8)),
  KEY `index_idsite_datetime_config` (`idsite`,`visit_last_action_time`,`config_md5config`(8)),
  KEY `index_idsite_idvisit` (`idsite`,`idvisit`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=319983 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_option`
--

CREATE TABLE `piwik_option` (
  `option_name` varchar(64) NOT NULL,
  `option_value` longtext NOT NULL,
  `autoload` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`option_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_site`
--

CREATE TABLE `piwik_site` (
  `idsite` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(90) NOT NULL,
  `main_url` varchar(255) NOT NULL,
  `ts_created` timestamp NULL default NULL,
  `timezone` varchar(50) NOT NULL,
  `currency` char(3) NOT NULL,
  `excluded_ips` text NOT NULL,
  `excluded_parameters` varchar(255) NOT NULL,
  `feedburnerName` varchar(100) default NULL,
  PRIMARY KEY  (`idsite`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_site_url`
--

CREATE TABLE `piwik_site_url` (
  `idsite` int(10) unsigned NOT NULL,
  `url` varchar(255) NOT NULL,
  PRIMARY KEY  (`idsite`,`url`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_user`
--

CREATE TABLE `piwik_user` (
  `login` varchar(100) NOT NULL,
  `password` char(32) NOT NULL,
  `alias` varchar(45) NOT NULL,
  `email` varchar(100) NOT NULL,
  `token_auth` char(32) NOT NULL,
  `date_registered` timestamp NULL default NULL,
  PRIMARY KEY  (`login`),
  UNIQUE KEY `uniq_keytoken` (`token_auth`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_user_dashboard`
--

CREATE TABLE `piwik_user_dashboard` (
  `login` varchar(100) NOT NULL,
  `iddashboard` int(11) NOT NULL,
  `layout` text NOT NULL,
  PRIMARY KEY  (`login`,`iddashboard`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `piwik_user_language`
--

CREATE TABLE `piwik_user_language` (
  `login` varchar(100) NOT NULL,
  `language` varchar(10) NOT NULL,
  PRIMARY KEY  (`login`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;