Since sometime last year, I have noticed a lot of MySQL related errors in my blog’s Apache server log, it simply happens every 2 minutes and annoys me a lot. I have tried to research it many many times, but unable to find anything useful from Google. There were lots of similar errors, but none of the solutions helped me to resolve my issue.
Please see below sample errors from my log:
[Wed Feb 19 06:30:02.362480 2020] [php7:notice] [pid 13374] [client xx.xxx.xx.xxx:53608] WordPress database error Duplicate entry 'social-network' for key 'slug' for query INSERT INTO `wp_terms` (`name`, `slug`, `term_group`) VALUES ('Social Network', 'social-network', 0) made by do_action_ref_array('wp_split_shared_term_batch'), WP_Hook->do_action, WP_Hook->apply_filters, _wp_batch_split_terms, _split_shared_term
[Wed Feb 19 06:30:02.364301 2020] [php7:notice] [pid 13374] [client xx.xxx.xx.xxx:53608] WordPress database error Duplicate entry 'ubuntu' for key 'slug' for query INSERT INTO `wp_terms` (`name`, `slug`, `term_group`) VALUES ('Ubuntu', 'ubuntu', 0) made by do_action_ref_array('wp_split_shared_term_batch'), WP_Hook->do_action, WP_Hook->apply_filters, _wp_batch_split_terms, _split_shared_term
[Wed Feb 19 06:30:02.366020 2020] [php7:notice] [pid 13374] [client xx.xxx.xx.xxx:53608] WordPress database error Duplicate entry 'windows' for key 'slug' for query INSERT INTO `wp_terms` (`name`, `slug`, `term_group`) VALUES ('Windows', 'windows', 0) made by do_action_ref_array('wp_split_shared_term_batch'), WP_Hook->do_action, WP_Hook->apply_filters, _wp_batch_split_terms, _split_shared_term
It floods my log every 2 minutes and I am just dying to try to get rid of it.
Finally, I spent a bit more time last night and nailed down the issue. It was due to the fact that I have Category and Tag with exactly the same NAME.
Firstly, the reason for the constant logging of such message is because the in built Workpress Cron job via PHP file wp-cron.php under WordPress’s root directory. I learnt that this script does not run continuously, but is only triggered upon every page load. This is OK for a low traffic sites, but can cause problems if your website has high traffic. I think this is designed to be used by site owners who can’t setup cron job on the system that hosts their site, like shared host.
The better way is to disable this wp-cron.php script and setup your own cron job if you have access to the OS cron setup. To do so:
- Disable wp-cron.php by going into root directory and locate the configuration file wp-config.php
- And put below code just after “define(‘DB_COLLATE’, ”);”
define('DB_COLLATE', ''); // Disable default WP's cron job, we will setup our own define('DISABLE_WP_CRON', true); - Then add below line into your cron job setup by running “crontab -e”
30 * * * * wget -q -O - https://cloudera.ericlin.me/wp-cron.php?doing_wp_cron >/dev/null 2>&1
This will trigger the WP cron job every 30 minutes, rather than current 2 minutes. How often is up to you.
After that, I need to fix the duplicate terms from Categories and Tags. To find out which terms have duplicates, run below query against your WordPress’s backend database. My example below works for MySQL:
SELECT t.term_id, t.name, t.slug, tt.taxonomy
FROM wp_terms t
JOIN wp_term_taxonomy tt ON (t.term_id = tt.term_id)
WHERE t.term_id IN (
SELECT tt.term_id FROM wp_term_taxonomy tt
LEFT JOIN wp_terms t ON t.term_id = tt.term_id
GROUP BY tt.term_id
HAVING count(*) > 1
)
ORDER BY t.name;
+---------+----------------+----------------+---------------+ | term_id | name | slug | taxonomy | +---------+----------------+----------------+---------------+ | 201 | Big Data | big-data | post_tag | | 201 | Big Data | big-data | category | | 183 | CMS | cms | post_tag | | 183 | CMS | cms | category | | 18 | Database | database | post_tag | | 18 | Database | database | category | | 19 | Fedora | fedora | post_tag | | 19 | Fedora | fedora | category | | 163 | Java | java | category | | 163 | Java | java | post_tag | | 21 | Javascript | javascript | category | | 21 | Javascript | javascript | post_tag | | 22 | MySQL | mysql | post_tag | | 22 | MySQL | mysql | category | | 23 | PHP | php | post_tag | | 23 | PHP | php | category | | 4 | Social Network | social-network | link_category | | 4 | Social Network | social-network | post_tag | | 15 | Ubuntu | ubuntu | post_tag | | 15 | Ubuntu | ubuntu | category | | 17 | Windows | windows | post_tag | | 17 | Windows | windows | category | +---------+----------------+----------------+---------------+ 22 rows in set (0.01 sec)
You can see that I have quite a few duplicates, most of them are between Tags and Categories, but also one between Link Category and Categories. And yes, they are case-insensitive, so CMS == cms as far as WordPress and MySQL are concerned.
To fix the issue, I have to decide for the same NAME, whether I want to keep them under Tag or Category. Then I will have to update all posts under those Tags or Categories to appropriate ones and then remove the duplicates.
After that, my Apache Error log is now as clean as a new pin.
I believe there are lots of you out there have the same issue as mine. Hope above information can help to you save your tons of time, so that you do not waste as much as I did in the last few months.
