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;