Syntax bug in MySQL
Reported by Tony | July 1st, 2008 @ 10:33 AM
I'm seeing the following error in my logs 60 times whenever the cron tries to run. That number doesn't seem to correspond to the number of campaigns (~10).
WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' feed_id = 7, hash = '5d5ec384889eba18209a37f4c547c1e1bdf8ca37'' at line 2 for query
SELECT * FROM wp_wpo_campaign_post` WHERE campaign_id = 6, feed_id = 7, hash = '5d5ec384889eba18209a37f4c547c1e1bdf8ca37' made by isduplicate
I didn't see any obvious place where there were duplicate quotes in the queries, so i'm gonna dig into what's being returned by the getItemHash function.
Comments and changes to this ticket
-
Tony July 1st, 2008 @ 11:00 AM
Every time that error appears in the log it is a different hash, but the same feed. I checked the db, and that feed has NULL stored as the hash value. I'm going to remove the feed to see if that fixes the issue.
-
core July 1st, 2008 @ 08:51 PM
I have the same issue. After running the cron job, my site sends me the following notification:
div id='error'>
WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' feed_id = 1, hash = '3eb3edb2b2c68276b05206aa51f5d02bdefbb403'' at line 2]
SELECT * FROM `wp_wpo_campaign_post`WHERE campaign_id = 1, feed_id = 1, hash = '3eb3edb2b2c68276b05206aa51f5d02bdefbb403'
WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' feed_id = 2, hash = '18434ae0abf1d7e9baea332560788f1f3dd9080e'' at line 2]
SELECT * FROM `wp_wpo_campaign_post`WHERE campaign_id = 2, feed_id = 2, hash = '18434ae0abf1d7e9baea332560788f1f3dd9080e'
WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' feed_id = 2, hash = 'f570f75d5c1f0c3958816aff88c2c37120d3a77c'' at line 2]
SELECT * FROM `wp_wpo_campaign_post`WHERE campaign_id = 2, feed_id = 2, hash = 'f570f75d5c1f0c3958816aff88c2c37120d3a77c'
WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' feed_id = 2, hash = '74fd101a9ba5afa071a6d8c546c404bdcca78b78'' at line 2]
SELECT * FROM `wp_wpo_campaign_post`WHERE campaign_id = 2, feed_id = 2, hash = '74fd101a9ba5afa071a6d8c546c404bdcca78b78'
WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' feed_id = 2, hash = 'c75d3fb4d9cbc952a9b242f04e22c3291d8691f4'' at line 2]
SELECT * FROM `wp_wpo_campaign_post`WHERE campaign_id = 2, feed_id = 2, hash = 'c75d3fb4d9cbc952a9b242f04e22c3291d8691f4'
WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' feed_id = 2, hash = 'f7e3f621c0a80913c5b83c08826f3d9b5c01543b'' at line 2]
SELECT * FROM `wp_wpo_campaign_post`WHERE campaign_id = 2, feed_id = 2, hash = 'f7e3f621c0a80913c5b83c08826f3d9b5c01543b'
piku July 3rd, 2008 @ 01:26 PM
Ditto...
[Thu Jul 03 09:00:22 2008] [error] [client 66.33.208.137] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' feed_id = 3, hash = '4db2fff1145bb93d8ab0a6b15888692e93b07d03'' at line 2 for query SELECT * FROM `wp_wpo_campaign_post`
[Thu Jul 03 09:00:22 2008] [error] [client 66.33.208.137] WHERE campaign_id = 2, feed_id = 3, hash = '4db2fff1145bb93d8ab0a6b15888692e93b07d03' made by isDuplicate
core July 3rd, 2008 @ 01:56 PM
I found the Answer!!
The problem is that mysql does not read and process comma's as listed in this file. So you need to replace both commas by the "AND" syntax instead.
1. look in the wpomatic.php file, near like 620
2. Change the commas to AND
SO WHAT READ AS:
$row = $wpdb->get_row("SELECT * FROM `{$this->db['campaign_post']}` WHERE campaign_id = {$campaign->id}, feed_id = {$feed->id}, hash = '$hash' ");
SHOULD BE:
$row = $wpdb->get_row("SELECT * FROM `{$this->db['campaign_post']}` WHERE campaign_id = {$campaign->id} AND feed_id = {$feed->id} AND hash = '$hash' ");
DID THIS HELP YOU?
piku July 3rd, 2008 @ 02:40 PM
Implemented. Will update on the next crontab triggered action. TIA (hopefully) =)
piku July 3rd, 2008 @ 03:04 PM
Looks like its working for me for the crontab that just ran 4mins ago (11pm PST).
Tony July 3rd, 2008 @ 03:11 PM
Wow, I can't believe I didn't catch that one! I've made the change and will watch for the next cron. Thanks, core!
core July 4th, 2008 @ 08:04 PM
You're welcome! Glad I caught that.. was killing me
shameless plug: check out our site to search for homes for rent on www.RentNice.com
Guillermo Rauch July 17th, 2008 @ 02:38 PM
- State changed from “new” to “resolved”
Good find. Works with all MySQL versions I tried though.. that's why it was hard to find it for me.
Ricardo Rauch April 15th, 2010 @ 08:49 AM
- Assigned user changed from “Guillermo Rauch” to “Ricardo Rauch”
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create new ticket
core