columns are missing in the target DB table. Directly show suggestions on which alter table statements to run.

[TRILOS]

Using Matomo 3.14.0, added Migration plugin via web interface, started recommended console migration command set to insert data from active Matomo instance to a WordPress website with plugin https://wordpress.org/plugins/matomo/ v1.2.0 - command and result see below.
Before this, the WP plugin has been activated and tracking has been started.
The missing columns are not present in source database, too.
(Can I add missing columns and values manually?)

$ /opt/plesk/php/7.3/bin/php ./console migration:measurable --source-idsite="2" --target-db-host="localhost" --target-db-port="3306" --target-db-username="..." --target-db-password='...' --target-db-name="..." --target-db-prefix="wp_matomo_" --disable-db-transactions --dry-run
Dry run is enabled. No entries will be written on the target DB.
Are you sure you want to migrate the data for idSite 2. (y/N)y

The following columns are missing in the target DB table "wp_matomo_site": custom_css, custom_css_file
The following columns are missing in the target DB table "wp_matomo_log_visit": location_provider
The following columns are missing in the target DB table "wp_matomo_log_conversion": referer_visit_server_date
ERROR [2020-07-28 14:08:42] 5249  Uncaught exception: (...)/plugins/Migration/Commands/Migrate.php(84): Please make sure both Matomo instances are on the same version
Please make sure both Matomo instances are on the same version
#0 (...)/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(257): Piwik\Plugins\Migration\Commands\Migrate->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#1 (...)/vendor/symfony/console/Symfony/Component/Console/Application.php(874): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#2 (...)/vendor/symfony/console/Symfony/Component/Console/Application.php(195): Symfony\Component\Console\Application->doRunCommand(Object(Piwik\Plugins\Migration\Commands\Migrate), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#3 [internal function]: Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#4 (...)/core/Console.php(140): call_user_func(Array, Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#5 (...)/core/Access.php(644): Piwik\Console->Piwik\{closure}()
#6 (...)/core/Console.php(141): Piwik\Access::doAsSuperUser(Object(Closure))
#7 (...)/core/Console.php(93): Piwik\Console->doRunImpl(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#8 (...)/vendor/symfony/console/Symfony/Component/Console/Application.php(126): Piwik\Console->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#9 (...)/console(32): Symfony\Component\Console\Application->run()
#10 {main}


  [Exception]
  Please make sure both Matomo instances are on the same version

[TRILOS]

Came upon #16 which seems similar. Would be great to know sql statements for a solution before a patch.

[tsteur]

@TRILOS it seems there are some third party plugins used on the Matomo On-Premise. Easiest way to fix this be to install the same additional plugins in WordPress.

This location_provider column could be added by running this query.

ALTER TABLE wp_matomo_log_visit ADD `location_provider` varchar(100) DEFAULT NULL;

Unfortunately, I don't know which third party plugins specify these columns: referer_visit_server_date, custom_css, custom_css_file .

I'll adjust the title to tweak the plugin so it directly tells you what to execute.

[tsteur]

On the Matomo On-Premise you could get the needed values by executing commands like show create table matomo_site and show create table matomo_log_conversion . The matomo_ prefix may need to be adjusted or removed depending on your setup.

Then you use like

ALTER TABLE wp_matomo_site ADD `custom_css` $TYPE_FROM_SHOW_CREATE_TABLE_STATEMENT;
ALTER TABLE wp_matomo_site ADD `custom_css_file` $TYPE_FROM_SHOW_CREATE_TABLE_STATEMENT;
ALTER TABLE wp_matomo_log_conversion ADD `referer_visit_server_date` $TYPE_FROM_SHOW_CREATE_TABLE_STATEMENT;

[TRILOS]

Thank you @tsteur for supporting this.

The source database belongs to a self-hosted Matomo 3.14.0.
The target database belnogs to the official Matomo WordPress Plugin.

DB table "wp_matomo_log_conversion": referer_visit_server_date
ist not(!) included in the source database.
I´ve created one with type DATE in the target database to see if it works.

DB table "wp_matomo_site": custom_css, custom_css_file
are included in the source database in table piwik_site and I´ve created them in the target database similar to the source.

I´ve created location_provider in DB table "wp_matomo_log_visit" from your recommendation.

I think, the Miration plugin should pre-check wether all plugins in the source instance are activated on the target isntance (I´m afraid the are only listed in a config file, not in the db). If the check fails, it gives a warning with a list of the missing plugins with a recommendation to install them in the target or remove them from the source. An overriding with user acknowledgemt for data loss can be provided.

But there´s still the question why referer_visit_server_date is required while it has not existed yet.

[TRILOS]

Next try after creating columns mentioned before:

The following columns are missing in the source DB table "piwik_log_conversion": location_provider ERROR [2020-07-29 14:42:17] 22844 Uncaught exception: (...)/plugins/Migration/Commands/Migrate.php(84): Please make sure both Matomo instances are on the same version Please make sure both Matomo instances are on the same version
Added location_provider and started command mentioned above.

Dry run is enabled. No entries will be written on the target DB.
Are you sure you want to migrate the data for idSite 2. (y/N)y
Processing SiteMigration at 2020-07-29 14:52:59
Target site is 519260 at 2020-07-29 14:52:59
Processed SiteMigration at 2020-07-29 14:52:59
Processing SiteUrlMigration at 2020-07-29 14:52:59
Found 3 site urls at 2020-07-29 14:52:59
Processed SiteUrlMigration at 2020-07-29 14:52:59
Processing SiteSettingMigration at 2020-07-29 14:52:59
Found 0 site settings at 2020-07-29 14:52:59
Processed SiteSettingMigration at 2020-07-29 14:52:59
Processing GoalsMigration at 2020-07-29 14:52:59
Found 0 goals at 2020-07-29 14:52:59
Processed GoalsMigration at 2020-07-29 14:52:59
Processing SegmentsMigration at 2020-07-29 14:52:59
Found 0 segments at 2020-07-29 14:52:59
Processed SegmentsMigration at 2020-07-29 14:52:59
Processing AnnotationsMigration at 2020-07-29 14:52:59
Processed AnnotationsMigration at 2020-07-29 14:52:59
Processing LogMigration at 2020-07-29 14:52:59
Found 330548 visits at 2020-07-29 14:53:00
PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in (...)/libs/Zend/Db/Statement/Mysqli.php on line 304
ERROR [2020-07-29 14:53:01] 25663  Fatal error encountered: (...)/libs/Zend/Db/Statement/Mysqli.php(304): Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes)
 on (...)/libs/Zend/Db/Statement/Mysqli.php(304)

Changed memory_limit in php.ini from 128M to 256M and the dry run completed with "Processed ArchiveMigration at 2020-07-29 14:55:57".

[tsteur]

@TRILOS you could try lowering the limit of visits that are being migrated at once. Eg change in plugins/Migration/Db/BatchQuery.php the limit from 10000 to 2000 and see if that helps? It is defined here: https://github.com/matomo-org/plugin-Migration/blob/3.0.5/Db/BatchQuery.php#L20

[tsteur]

Just seeing the note below... if it shows "Processed ArchiveMigration at 2020-07-29 14:55:57" then it seems to have completed fully.

[TRILOS]

@tsteur Yes, regarding the import, everything ist alright now. After this, measuring has been switched to WP Plugin.

[Stan-vw]

If I understand correctly, this issue has now been resolved with the suggestions above.

I'll close this ticket, but feel free to reopen it in case the same issue happens again