Piwik › Update

Piwik database will be upgraded from version 2.13.1 to the new version 3.0.0.

The following plugins will be updated: LanguagesManager, Goals.

The following dimensions will be updated: log_link_visit_action.idpageview, log_link_visit_action.interaction_position, log_link_visit_action.time_spent_ref_action, log_visit.config_browser_engine, log_visit.config_browser_name, log_visit.config_browser_version, log_visit.config_cookie, log_visit.config_device_brand, log_visit.config_device_model, log_visit.config_device_type, log_visit.config_director, log_visit.config_flash, log_visit.config_gears, log_visit.config_java, log_visit.config_os, log_visit.config_pdf, log_visit.config_quicktime, log_visit.config_realplayer, log_visit.config_resolution, log_visit.config_silverlight, log_visit.config_windowsmedia, log_visit.location_browser_lang, log_visit.location_country, log_visit.location_latitude, log_visit.location_longitude, log_visit.referer_url, log_visit.visit_entry_idaction_name, log_visit.visit_entry_idaction_url, log_visit.visit_exit_idaction_name, log_visit.visit_exit_idaction_url, log_visit.visit_goal_buyer, log_visit.visit_goal_converted, log_visit.visit_total_actions, log_visit.visit_total_events, log_visit.visit_total_interactions, log_visit.visit_total_searches, log_visit.visit_total_time, log_visit.visitor_count_visits, log_visit.visitor_days_since_first, log_visit.visitor_days_since_last, log_visit.visitor_days_since_order, log_visit.visitor_localtime, log_visit.visitor_returning.

Important notes for large Piwik installations

This is a major update! It will take longer than usual. The following advice is especially important for large installations.

If you have a large Piwik database, updates might take too long to run in the browser. In this situation, you can execute the updates from your command line:

php /home/homak/public_html/analytics/console core:update

If you manage a high traffic Piwik server, we recommend to momentarily disable visitor Tracking and put the Piwik User Interface in maintenance mode.

› Click here to view and copy the list of SQL queries that will get executed

# FYI: these are the SQL queries that will be executed to upgrade your database to Piwik 3.0.0
DROP TABLE IF EXISTS `piwik_site_setting`;
CREATE TABLE `piwik_site_setting` (`idsite` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, `setting_name` VARCHAR(255) NOT NULL, `setting_value` LONGTEXT NOT NULL, PRIMARY KEY ( `idsite`, `setting_name` )) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `piwik_site` ADD COLUMN `exclude_unknown_urls` TINYINT(1) DEFAULT 0 AFTER `currency`;
UPDATE piwik_user_dashboard SET layout = '{\"config\":{\"layout\":\"33-33-33\"},\"columns\":[[{\"uniqueId\":\"widgetVisitsSummarygetEvolutionGraphforceView1viewDataTablegraphEvolution\",\"parameters\":{\"module\":\"VisitsSummary\",\"action\":\"getEvolutionGraph\",\"columns\":\"nb_visits\",\"widget\":1,\"forceView\":\"1\",\"viewDataTable\":\"graphEvolution\"},\"isHidden\":false},{\"uniqueId\":\"widgetLivewidget\",\"parameters\":{\"module\":\"Live\",\"action\":\"widget\",\"widget\":1},\"isHidden\":false}],[{\"uniqueId\":\"widgetReferrersgetKeywords\",\"parameters\":{\"module\":\"Referrers\",\"action\":\"getKeywords\",\"widget\":1},\"isHidden\":false},{\"uniqueId\":\"widgetReferrersgetWebsites\",\"parameters\":{\"module\":\"Referrers\",\"action\":\"getWebsites\",\"widget\":1},\"isHidden\":false}],[{\"uniqueId\":\"widgetUserCountryMapvisitorMap\",\"parameters\":{\"module\":\"UserCountryMap\",\"action\":\"visitorMap\",\"widget\":1},\"isHidden\":false},{\"uniqueId\":\"widgetDevicesDetectiongetBrowsers\",\"parameters\":{\"module\":\"DevicesDetection\",\"action\":\"getBrowsers\",\"widget\":1},\"isHidden\":false},{\"uniqueId\":\"widgetReferrersgetSearchEngines\",\"parameters\":{\"module\":\"Referrers\",\"action\":\"getSearchEngines\",\"widget\":1},\"isHidden\":false},{\"uniqueId\":\"widgetExampleRssWidgetrssPiwik\",\"parameters\":{\"module\":\"ExampleRssWidget\",\"action\":\"rssPiwik\",\"widget\":1},\"isHidden\":false}]]}' WHERE iddashboard = '1';
CREATE TABLE `piwik_plugin_setting` (`plugin_name` VARCHAR(60) NOT NULL, `setting_name` VARCHAR(255) NOT NULL, `setting_value` LONGTEXT NOT NULL, `user_login` VARCHAR(100) NOT NULL DEFAULT '') ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `piwik_plugin_setting` ADD INDEX index_plugin_name_user_login (`plugin_name`, `user_login`);
DELETE FROM `piwik_option` WHERE `option_name` like "Plugin_%_Settings";
DROP TABLE IF EXISTS `piwik_site_setting`;
CREATE TABLE `piwik_site_setting` (`idsite` INTEGER(10) UNSIGNED NOT NULL, `plugin_name` VARCHAR(60) NOT NULL, `setting_name` VARCHAR(255) NOT NULL, `setting_value` LONGTEXT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `piwik_site_setting` ADD INDEX(idsite, plugin_name);
ALTER TABLE `piwik_log_visit` CHANGE `idvisit` `idvisit` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `piwik_log_conversion_item` CHANGE `idvisit` `idvisit` BIGINT(10) UNSIGNED NOT NULL;
ALTER TABLE `piwik_log_conversion` CHANGE `idvisit` `idvisit` BIGINT(10) UNSIGNED NOT NULL, CHANGE `idlink_va` `idlink_va` BIGINT(10) UNSIGNED default NULL;
ALTER TABLE `piwik_log_link_visit_action` CHANGE `idlink_va` `idlink_va` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT, CHANGE `idvisit` `idvisit` BIGINT(10) UNSIGNED NOT NULL, CHANGE `idaction_name_ref` `idaction_name_ref` INTEGER(10) UNSIGNED NULL;
ALTER TABLE `piwik_user` CHANGE `password` `password` VARCHAR(255) NOT NULL;
ALTER TABLE `piwik_user_language` ADD COLUMN `use_12_hour_clock` TINYINT(1) NOT NULL DEFAULT 0 AFTER `language`;
ALTER TABLE `piwik_goal` ADD COLUMN `description` VARCHAR(255) NOT NULL DEFAULT '' AFTER `name`;
ALTER TABLE `piwik_log_visit` MODIFY COLUMN `visit_goal_buyer` TINYINT(1) NULL, MODIFY COLUMN `visit_goal_converted` TINYINT(1) NULL, MODIFY COLUMN `visitor_days_since_first` SMALLINT(5) UNSIGNED NULL, MODIFY COLUMN `visitor_days_since_order` SMALLINT(5) UNSIGNED NULL, MODIFY COLUMN `visitor_returning` TINYINT(1) NULL, MODIFY COLUMN `visitor_count_visits` INT(11) UNSIGNED NOT NULL, MODIFY COLUMN `visit_entry_idaction_name` INTEGER(10) UNSIGNED NULL, MODIFY COLUMN `visit_entry_idaction_url` INTEGER(11) UNSIGNED NULL  DEFAULT NULL, MODIFY COLUMN `visit_exit_idaction_name` INTEGER(10) UNSIGNED NULL, MODIFY COLUMN `visit_exit_idaction_url` INTEGER(10) UNSIGNED NULL DEFAULT 0, MODIFY COLUMN `visit_total_actions` INT(11) UNSIGNED NULL, ADD COLUMN `visit_total_interactions` SMALLINT UNSIGNED DEFAULT 0, MODIFY COLUMN `visit_total_searches` SMALLINT(5) UNSIGNED NULL, MODIFY COLUMN `referer_url` TEXT NULL, MODIFY COLUMN `location_browser_lang` VARCHAR(20) NULL, MODIFY COLUMN `config_browser_engine` VARCHAR(10) NULL, MODIFY COLUMN `config_browser_name` VARCHAR(10) NULL, MODIFY COLUMN `config_browser_version` VARCHAR(20) NULL, MODIFY COLUMN `config_device_brand` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, MODIFY COLUMN `config_device_model` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, MODIFY COLUMN `config_device_type` TINYINT( 100 ) NULL DEFAULT NULL, MODIFY COLUMN `config_os` CHAR(3) NULL, MODIFY COLUMN `visit_total_events` INT(11) UNSIGNED NULL, MODIFY COLUMN `visitor_localtime` TIME NULL, MODIFY COLUMN `visitor_days_since_last` SMALLINT(5) UNSIGNED NULL, MODIFY COLUMN `config_resolution` VARCHAR(18) NULL, MODIFY COLUMN `config_cookie` TINYINT(1) NULL, MODIFY COLUMN `config_director` TINYINT(1) NULL, MODIFY COLUMN `config_flash` TINYINT(1) NULL, MODIFY COLUMN `config_gears` TINYINT(1) NULL, MODIFY COLUMN `config_java` TINYINT(1) NULL, MODIFY COLUMN `config_pdf` TINYINT(1) NULL, MODIFY COLUMN `config_quicktime` TINYINT(1) NULL, MODIFY COLUMN `config_realplayer` TINYINT(1) NULL, MODIFY COLUMN `config_silverlight` TINYINT(1) NULL, MODIFY COLUMN `config_windowsmedia` TINYINT(1) NULL, MODIFY COLUMN `visit_total_time` INT(11) UNSIGNED NOT NULL, MODIFY COLUMN `location_country` CHAR(3) NULL, MODIFY COLUMN `location_latitude` decimal(9, 6) DEFAULT NULL, MODIFY COLUMN `location_longitude` decimal(9, 6) DEFAULT NULL;
ALTER TABLE `piwik_log_conversion` MODIFY COLUMN `visitor_days_since_first` SMALLINT(5) UNSIGNED NULL, MODIFY COLUMN `visitor_days_since_order` SMALLINT(5) UNSIGNED NULL, MODIFY COLUMN `visitor_returning` TINYINT(1) NULL, MODIFY COLUMN `visitor_count_visits` INT(11) UNSIGNED NOT NULL, ADD COLUMN `config_device_brand` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ADD COLUMN `config_device_model` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ADD COLUMN `config_device_type` TINYINT( 100 ) NULL DEFAULT NULL, MODIFY COLUMN `location_country` CHAR(3) NULL, MODIFY COLUMN `location_latitude` decimal(9, 6) DEFAULT NULL, MODIFY COLUMN `location_longitude` decimal(9, 6) DEFAULT NULL;
ALTER TABLE `piwik_log_link_visit_action` ADD COLUMN `idpageview` CHAR(6) NULL DEFAULT NULL, ADD COLUMN `interaction_position` SMALLINT UNSIGNED DEFAULT NULL, MODIFY COLUMN `time_spent_ref_action` INTEGER(10) UNSIGNED NULL;

Need help upgrading Piwik?

If you need support to upgrade your Piwik, the creators of Piwik are here to help you make the Piwik upgrade a success and provide all instructions, best practises and ongoing support. Contact the Piwik experts to get started upgrading your Piwik safely.

Ready to go?

The database upgrade process may take a while, so please be patient.

File integrity check failed and reported some errors. This is most likely due to a partial or failed upload of some of the Piwik files. You should reupload all the Piwik files in BINARY mode and refresh this page until it shows no error.

Database Upgrade Required

Important notes for large Piwik installations

Need help upgrading Piwik?

Ready to go?