Invalid index on xf_poll_vote

[Rigel Kentaurus]

I had multiple votes on polls not working, and spent a fun half an hour tracking how it could have happened.

On one of my installations, polls were not working. Basically only the first vote was getting into consideration and the multiple votes were not. By debugging this, I realized that this was the problem

show index from xf_poll_vote;

which gave me this

+--------------+------------+-----------------+--------------+------------
| Table        | Non_unique | Key_name        | Seq_in_index | Column_name
+--------------+------------+-----------------+--------------+------------
| xf_poll_vote |          0 | poll_id_user_id |            1 | poll_id   
| xf_poll_vote |          0 | poll_id_user_id |            2 | user_id

This means I had a unique index on (poll_id, user_id), which means just one vote per user per poll (no multiple votes).

I have no idea how I got that. Maybe an add-on that didn't clean up correctly after uninstalling. It is definitely NOT happening on a new XenForo installation, and not all of my XF boards have this issue. It is not a XF bug (hence why this thread is not in the bugs section).

So,

1) Webmasters reading this, could you run that query above, and check if poll_id_user_id has Non_Unique set to 0 or 1? Would be good if I detected that I was not the only one with the issue

And .. 2) For the XenForo devs, the query is inserting it like this:

$res = $db->query('
INSERT IGNORE INTO xf_poll_vote
(user_id, poll_response_id, poll_id, vote_date)
VALUES
(?, ?, ?, ?)
', array($userId, $voteResponseId, $pollId, $voteDate));

After the poll improvements, INSERT IGNORE does not make a lot of sense. It was actually obscuring the error for me. Looks to me that if that one fails, that is an error.

 

[Chris D]

Looks ok to me:

 

[Rigel Kentaurus]

Looks ok to me:

View attachment 80118

Thanks.
I probably installed some add-on that messed it up and didn't clean up. After several years, could be anything.
I just redid the index.