Hi @madd1974,
Sorry to hear about the hiccup. You can run the migration again by visiting the following page in your WP Admin area while logged in.
https://www.abenteuer-brettspiele.de/wp-admin/?koko_analytics_migrate_from_version=1.8.6
After it runs successfully, the primary key on the wp_koko_analytics_post_stats database table should indeed be on the date and the path_id column.
Let me know if that helped please.
Best,
Danny
When I open this URL, it takes a few seconds to load and then the normal WP-dashboard opens.
Should I see something else? A migrate button?
In any case, nothing has changed in the database table. post_id is still primary.
By the way, updating to the new version 2.0.12 did not help either.
Hi @madd1974,
Since the script is meant to run automatically (in the background), there is no visual feedback of any kind. But since the page seems to be loading for longer than usual, the plugin should be doing something in the background. What’s weird is that if that is the case, it should definitely change the primary key once it finishes…
Are there any errors in your PHP error log?
What happens if you go to https://www.abenteuer-brettspiele.de/wp-admin/?koko_analytics_action=migrate_post_stats_to_v2 . It should load for a while and then redirect you to your WP Admin dashboard without the ?koko_analytics_action=… part.
Let me know please.
The URL https://www.abenteuer-brettspiele.de/wp-admin/?koko_analytics_action=migrate_post_stats_to_v2 loads about 5 seconds and then there is the normal dashboard.
When I activate DEBUG in wp-config.php than there is the following Error:
WordPress-Datenbank-Fehler: [Duplicate entry ‘2020-05-10-480’ for key ‘PRIMARY’]
ALTER TABLE wp_koko_analytics_post_stats DROP PRIMARY KEY, ADD PRIMARY KEY(date, path_id)
Thank you @madd1974 – that is helpful. Does your site have post ID’s larger than 16777215 by any chance?
@madd1974 It seems as if something during the migration has caused a duplicate entry in the post_stats table which means it will be forever stuck. We can fix it though. The easiest way would be for you to run a few SQL queries against your database, eg through PhpMyAdmin.
Here’s the SQL required:
RENAME TABLE wp_koko_analytics_post_stats TO wp_koko_analytics_post_stats_old;
CREATE TABLE wp_koko_analytics_post_stats (
date DATE NOT NULL,
path_id INT UNSIGNED NOT NULL,
post_id INT UNSIGNED NOT NULL DEFAULT 0,
visitors INT UNSIGNED NOT NULL DEFAULT 0,
pageviews INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (date, path_id)
) ENGINE=INNODB CHARACTER SET=ascii;
INSERT INTO wp_koko_analytics_post_stats (date, path_id, post_id, visitors, pageviews) SELECT date, path_id, post_id, SUM(visitors), SUM(pageviews) FROM wp_koko_analytics_post_stats_old GROUP BY date, path_id;
If your database has another table prefix then the default wp_ then make sure to change it accordingly (or let me know and I’ll update the SQL for you.
Let me know how that worked or if anything is unclear please.
The largest Post ID is about 20000. So that can’t be it.
It has worked with your SQL code, and path ID is now the primary.
However, most posts and pages still show far too few page views.
I compared an SQL backup from before the Koko migration with a current SQL backup.
On August 17, the wp_koko_analytics_post_stats table had 619,634 entries.
Today it only has 560,573 entries.
So it seems that around 60,000 entries were lost during the migration. Is there any way to restore these from the backup to the wp_koko_analytics_post_stats table?
Hi @madd1974,
Definitely, but any data collected between then and now will be lost as it is very hard to merge records (due to potential duplicate primary keys).
You can re-instate the back-up and then perform the migration again to see if that fixes your issue – I am not entirely sure at this point where the missing posts have gone except for a hiccup during the migration itself. Since your site is quite large, I recommend performing the migration using WP CLI using the following command, if you have access to it:
wp koko-analytics migrate_post_stats_to_v2
If you want, you can send me the back-up file and I can pull out just the Koko Analytics database tables for you (so that the rest of your site is untouched). You can also send me a back-up of today and I can see whether I can try to merge the two.
Best,
Danny
