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

[ghost]

Hi mattab,

I'm currently running 2 piwik installations both version 2.11.0 and your plugin installed via piwiki-marketplace. I tried to merge them but I get following errors:

[piwik@xxxxx analytics]# ./console migration:site -v --db-prefix="piwik_" 1
DEBUG SitesManager[2015-02-20 08:43:58] Db::fetchAll() executing SQL: SELECT idsite FROM stats_site
DEBUG SitesManager[2015-02-20 08:43:58] Db::fetchAll() executing SQL: SELECT idsite FROM stats_site
Please provide the destination database password: 
INFO SiteMigration[2015-02-20 08:44:03] Start transaction
INFO SiteMigration[2015-02-20 08:44:03] Migrating site config
INFO SiteMigration[2015-02-20 08:44:03] Loading existing actions
INFO SiteMigration[2015-02-20 08:44:03] Migrating log data - visits
INFO SiteMigration[2015-02-20 08:44:03] Migrating log data - link visit action
INFO SiteMigration[2015-02-20 08:44:04] Migrating log data - conversions and conversion items

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

Exception trace:
 () at /var/www/vhosts/mydomain.com/application/public/analytics/libs/Zend/Db/Statement/Pdo.php:234
 Zend_Db_Statement_Pdo->_execute() at /var/www/vhosts/mydomain.com/application/public/analytics/libs/Zend/Db/Statement.php:300
 Zend_Db_Statement->execute() at /var/www/vhosts/mydomain.com/application/public/analytics/libs/Zend/Db/Adapter/Abstract.php:479
 Zend_Db_Adapter_Abstract->query() at /var/www/vhosts/mydomain.com/application/public/analytics/libs/Zend/Db/Adapter/Pdo/Abstract.php:238
 Zend_Db_Adapter_Pdo_Abstract->query() at /var/www/vhosts/mydomain.com/application/public/analytics/core/Db/Adapter/Pdo/Mysql.php:234
 Piwik\Db\Adapter\Pdo\Mysql->query() at /var/www/vhosts/mydomain.com/application/public/analytics/libs/Zend/Db/Adapter/Abstract.php:575
 Zend_Db_Adapter_Abstract->insert() at /var/www/vhosts/mydomain.com/application/public/analytics/plugins/SiteMigration/Helper/DBHelper.php:43
 Piwik\Plugins\SiteMigration\Helper\DBHelper->executeInsert() at /var/www/vhosts/mydomain.com/application/public/analytics/plugins/SiteMigration/Migrator/TableMigrator.php:56
 Piwik\Plugins\SiteMigration\Migrator\TableMigrator->processRow() at /var/www/vhosts/mydomain.com/application/public/analytics/plugins/SiteMigration/Migrator/TableMigrator.php:47
 Piwik\Plugins\SiteMigration\Migrator\TableMigrator->migrate() at /var/www/vhosts/mydomain.com/application/public/analytics/plugins/SiteMigration/Migrator/Migrator.php:204
 Piwik\Plugins\SiteMigration\Migrator\Migrator->migrateLogVisitConversions() at /var/www/vhosts/mydomain.com/application/public/analytics/plugins/SiteMigration/Migrator/Migrator.php:114
 Piwik\Plugins\SiteMigration\Migrator\Migrator->migrate() at /var/www/vhosts/mydomain.com/application/public/analytics/plugins/SiteMigration/Commands/MigrateSite.php:97
 Piwik\Plugins\SiteMigration\Commands\MigrateSite->execute() at /var/www/vhosts/mydomain.com/application/public/analytics/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:253
 Symfony\Component\Console\Command\Command->run() at /var/www/vhosts/mydomain.com/application/public/analytics/vendor/symfony/console/Symfony/Component/Console/Application.php:874
 Symfony\Component\Console\Application->doRunCommand() at /var/www/vhosts/mydomain.com/application/public/analytics/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at n/a:n/a
 call_user_func() at /var/www/vhosts/mydomain.com/application/public/analytics/core/Console.php:58
 Piwik\Console->Piwik\{closure}() at /var/www/vhosts/mydomain.com/application/public/analytics/core/Access.php:454
 Piwik\Access::doAsSuperUser() at /var/www/vhosts/mydomain.com/application/public/analytics/core/Console.php:59
 Piwik\Console->doRun() at /var/www/vhosts/mydomain.com/application/public/analytics/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /var/www/vhosts/mydomain.com/application/public/analytics/console:27

[mattab]

Hi @subTH - thanks for the report. cc @mnapoli

[mnapoli]

@subTH do you have the same plugins (each same versions) on both installs? It could be that one plugin is installed on one Piwik and not the second one, and that plugins added additional DB columns.

Could you check if the log_conversions and log_conversions_item have the same columns too?

@mattab maybe you know if referer_visit_server_date is a column of a plugin?

[ghost]

@mnapoli yes, i updated both piwik installations and installed the plugin the same day i wanted proceed with the migration

I will check the tables later today and let you know

[ghost]

I've installed BotTracker, maybe referer_visit_server_date is a column of that plugin

[mattab]

Good question @mnapoli - I checked and actually referer_visit_server_date was an old column that we used to have, and not anymore. This was changed in https://github.com/piwik/piwik/issues/5927

[ghost]

@mattab strange, they are not the same

CREATE TABLE IF NOT EXISTS piwik_log_conversion ( idvisit int(10) unsigned NOT NULL, idsite int(10) unsigned NOT NULL, idvisitor binary(8) NOT NULL, server_time datetime NOT NULL, idaction_url int(11) DEFAULT NULL, idlink_va int(11) DEFAULT NULL, idgoal int(10) NOT NULL, buster int(10) unsigned NOT NULL, idorder varchar(100) DEFAULT NULL, items smallint(5) unsigned DEFAULT NULL, url text NOT NULL, location_longitude float(10,6) DEFAULT NULL, location_latitude float(10,6) DEFAULT NULL, location_region char(2) DEFAULT NULL, location_country char(3) NOT NULL, location_city varchar(255) DEFAULT NULL, visitor_count_visits smallint(5) unsigned NOT NULL, visitor_returning tinyint(1) NOT NULL, visitor_days_since_order smallint(5) unsigned NOT NULL, visitor_days_since_first smallint(5) unsigned NOT NULL, referer_type tinyint(1) unsigned DEFAULT NULL, referer_name varchar(70) DEFAULT NULL, referer_keyword varchar(255) DEFAULT NULL, revenue_discount float DEFAULT NULL, revenue float DEFAULT NULL, revenue_shipping float DEFAULT NULL, revenue_subtotal float DEFAULT NULL, revenue_tax float DEFAULT NULL, custom_var_k1 varchar(200) DEFAULT NULL, custom_var_v1 varchar(200) DEFAULT NULL, custom_var_k2 varchar(200) DEFAULT NULL, custom_var_v2 varchar(200) DEFAULT NULL, custom_var_k3 varchar(200) DEFAULT NULL, custom_var_v3 varchar(200) DEFAULT NULL, custom_var_k4 varchar(200) DEFAULT NULL, custom_var_v4 varchar(200) DEFAULT NULL, custom_var_k5 varchar(200) DEFAULT NULL, custom_var_v5 varchar(200) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS stats_log_conversion ( idvisit int(10) unsigned NOT NULL, idsite int(10) unsigned NOT NULL, idvisitor binary(8) NOT NULL, server_time datetime NOT NULL, idaction_url int(11) DEFAULT NULL, idlink_va int(11) 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, visitor_count_visits smallint(5) unsigned NOT NULL, visitor_days_since_first smallint(5) unsigned NOT NULL, visitor_days_since_order smallint(5) unsigned NOT NULL, location_country char(3) NOT NULL, location_region char(2) DEFAULT NULL, location_city varchar(255) DEFAULT NULL, location_latitude float(10,6) DEFAULT NULL, location_longitude float(10,6) DEFAULT NULL, url text NOT NULL, idgoal int(10) NOT NULL, buster int(10) unsigned NOT NULL, idorder varchar(100) DEFAULT NULL, items smallint(5) unsigned DEFAULT NULL, revenue float DEFAULT NULL, revenue_subtotal float DEFAULT NULL, revenue_tax float DEFAULT NULL, revenue_shipping float DEFAULT NULL, revenue_discount float DEFAULT NULL, custom_var_k1 varchar(200) DEFAULT NULL, custom_var_v1 varchar(200) DEFAULT NULL, custom_var_k2 varchar(200) DEFAULT NULL, custom_var_v2 varchar(200) DEFAULT NULL, custom_var_k3 varchar(200) DEFAULT NULL, custom_var_v3 varchar(200) DEFAULT NULL, custom_var_k4 varchar(200) DEFAULT NULL, custom_var_v4 varchar(200) DEFAULT NULL, custom_var_k5 varchar(200) DEFAULT NULL, custom_var_v5 varchar(200) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

the older installation has the additional column referer_visit_server_date, like you described above

[mnapoli]

Could it be that an upgrade didn't remove the column by mistake? @mattab or maybe the column wasn't deleted in an update script (at least I can't find it in the commit). Is it safe to just delete the column?

[mattab]

Oh! it's possible we didn't delete the column in an update script, which would be a bug. It's safe to delete the column, however if we do it in core it would be a "major db upgrade" which we unfortunately cannot yet do as we are blocked by https://github.com/piwik/piwik/issues/6953

[ghost]

In order to finish my migration I will delete the column manually. Thx for your fast investigation!

[bountham]

how do you delete column manually

[ghost]

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

ALTER TABLE tablename DROP COLUMN colname;

[nekohayo]

Well I hit this today when "migrating" one of the sites from a Piwik 2.14.3 instance to another Piwik 2.14.3 instance. The age of the site in question probably is a factor in this, as some other sites (that are only 1-year-old) did not encounter this issue.

Not sure why this ticket is closed though as there is technically still a bug that users will encounter.

For the time being, I did

ALTER TABLE `piwik_log_conversion` DROP `referer_visit_server_date`;

And that seems to have allowed the migration to work.

[monty241]

Using SiteMigrator I've had approximately 15 problems like these. Solved (worked around) it by doing on source piwik: desc TABLENAME and then making sure the target piwik had the same column list, using:

alter table add/drop ...

Nonetheless, a more robust mechanism would be welcome. Maybe isolated to sitemgirator.

[mattab]

Thanks for the report. This was also reported in #30 and #36