Plugin Author
Ajay
(@ajay)
I think something broke down in the database during the upgrades, as this has been a recurring issue.
Can you change that to Query variable based so I can take a look at your site. Also, worth checking your database to see what the structures of the primary keys are as something that button doesn’t work (I’m not sure why as it has worked in all my tests)
Query variable based is set (or should be).
Also, worth checking your database to see what the structures of the primary keys are as something that button doesn’t work (I’m not sure why as it has worked in all my tests)
I’m somewhat ignorant in MySQL, so how would I determine this?
Plugin Author
Ajay
(@ajay)
If you have access to phpMyAdmin when you browse the Structure of the table you can see the various indexes at the bottom of the table – see the wp_top_ten and wp_top_ten_daily tables in your database.
The primary keys will be “postnumber, blog_id” and “postnumber, dp_date, blog_id” respectively.
What you can do is run this query in the database if not present.
ALTER TABLE wp_top_ten DROP PRIMARY KEY;
ALTER TABLE wp_top_ten_daily DROP PRIMARY KEY;
ALTER TABLE wp_top_ten ADD PRIMARY KEY(postnumber, blog_id);
ALTER TABLE wp_top_ten_daily ADD PRIMARY KEY(postnumber, dp_date, blog_id);
Always backup your database before doing any major changes just to be safe.
Sorry for the delay.
> If you have access to phpMyAdmin when you browse the Structure of the table you can see the various indexes at the bottom of the table – see the wp_top_ten and wp_top_ten_daily tables in your database.
Under “Indexes” for “wp_<some_ID>_top_ten it is saying, “No index defined!”
For wp_<some_ID>_top_ten_daily, it says the keyname is PRIMARY and the type is BTREE.
Plugin Author
Ajay
(@ajay)
Can you please use the above code but change it to the correct table names.
More importantly do you see only the wp_top_ten tables or you see a lot of these with similar names. If so it might be the old issue of the tables not being upgraded from older versions of the plugin.
Note the wp might be something else
The two tables I mentioned are all that’s out there. I’ll have to run those commands, once I figure out how to do that. (Never manually done SQL commands for my site.)
Plugin Author
Ajay
(@ajay)
If you’re in phpMyAdmin, you can run the queries there. see this tutorial https://www.siteground.com/tutorials/phpmyadmin/query/
Error
SQL query:
ALTER TABLE wp_XXXXX_top_ten DROP PRIMARY KEY
MySQL said: Documentation
#1091 – Can’t DROP ‘PRIMARY’; check that column/key exists
Remember what I said before.
Under “Indexes” for “wp_<some_ID>_top_ten it is saying, “No index defined!”
And then there’s this.
Error
SQL query:
ALTER TABLE wp_XXXXX_top_ten ADD PRIMARY KEY(postnumber, dp_date, blog_id)
MySQL said: Documentation
#1072 – Key column ‘dp_date’ doesn’t exist in table
-
This reply was modified 6 years, 5 months ago by
AstroNerdBoy.
For the wp_XXXXX_top_ten_daily table, the DROP PRIMARY KEY worked (I didn’t grab the text for that).
Error
SQL query:
ALTER TABLE wp_XXXXX_top_ten_daily ADD PRIMARY KEY(postnumber, blog_id)
MySQL said: Documentation
#1062 – Duplicate entry ‘16417-1’ for key ‘PRIMARY’
Still no changes in Visit times, though the Daily Visit count still seems right.
Plugin Author
Ajay
(@ajay)
Can you please run
ALTER TABLE wp_XXXXX_top_ten ADD PRIMARY KEY(postnumber, blog_id)
The dp_date only applies to the daily table.
That’s weird that it is coming up with an error for duplicate entry when I couldn’t drop the PRIMARY key since it supposedly didn’t exist. (But then my SQL knowledge is limited to having a vague understanding of what the commands you gave me are doing within the database.)
Plugin Author
Ajay
(@ajay)
Are you using phpMyAdmin? If so, you can drop the indices directly from the interface.
If you’re browsing the Structure of the table and scroll down to the bottom and remove it from there:
https://stackoverflow.com/questions/4320005/phpmyadmin-removing-an-index
If I click the wp_xxxxx_top_ten table link, a warning appears at the top of the page for the table.
Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available. Documentation
There’s no option for dropping the table. There is an option from the list of all tables.
I clicked that and the table is gone. Ooops. So I just deactivated and reactivated the plugin. That restored the table. Of course it was completely empty, but the table is now working. There are no longer warnings and there is a drop element as well.
To be honest, I think the table had become unusable. As I had mentioned before, there was no PRIMARY key when I previously looked in phpMyAdmin. And this table always generated warnings when looking at structure or anything else. So what I did may have been the only solution unless there was a way to add the key. But I think that was what you were having me attempt before, right?
In completely dropping the table, I lost total counts, but I was never going to have true total counts because (1) I installed this plugin quite some time after the blog started and (2) I had run a cleanup on the table (via the automated tools in the plugin) to trim it back. So I’m not going to worry about that.
