Neat Error Message...
WordPress database error: [MySQL server has gone away]SELECT DISTINCT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts WHERE post_date < '2007-04-01 20:12:25' AND post_date != '0000-00-00 00:00:00' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC
Neat error message - "has gone away"....
Bummed about the lack of bind variables, I rather suspect that by hard coding the date in there, they might tend to generate lots of unique SQL statements...
Funny, I wonder how you can order by post_date, after stripping the day, hour, minute and second away - that part doesn't make sense to me... Post_date doesn't exist anymore after the GROUP BY! How could you possibly sort by it?
posted by Thomas Kyte at 9:35 PM
27 Comments:
lol...too good...no bind variables, hard coding of dates and then order by post_date :D
Sidhu
"Bummed about the lack of bind variables, I rather suspect that by hard coding the date in there, they might tend to generate lots of unique SQL statements..."
MySQL caches differently (also depending on what version of MySQL they are using), so the unique statements aren't the issue they are in Oracle (which is why developers raised on MySQL don't 'get' bind variables)
..more importantly, how come you can see this code - security anyone?
The additional DISTINCT is also interesting. I can not really imagine that you can get equal rows after the GROUP BY of YEAR and MONTH...
Patrick
why distinct ?
It would be interesting to know what expect mysql when you order by something you do not group by. Here however it is possible to sort by post_date and group by year,month
however it is possible to sort by post_date and group by year,month
how so? can you explain the logic?
that would be like:
select trunc(id/10)
from t
group by trunc(id/10)
order by id;
Now, order by happens logically AFTER a group by operation - so, by the time you get to the order by, ID doesn't exist anymore.
So, I'm still at a loss as to how to explain the order by "logically"
ok, it is not always possible, but in this case it is somehow, sometimes (not in Oracle) possible to first sort then group.
x y
- -
a 1
a 2
c 3
c 4
b 5
b 6
select x, sum(y) from group by x order by y;
x sum(y)
- ---------
a 3
c 7
b 11
but, of course, I am not sure this works in mysql (and for sure it does not work in oracle)...
Did the request ever worked?
maybe in oracle we could have something like
select x,sum(y) from (
select x,min(y)over(partition by x) m,y from t
)group by x,m
order by m;
to get the same result. This is just guessing
To me that just seems like defensive coding. MySQL 5 has bind variables. The people at wordpress still offer support for MySQL 4. Perhaps they should branch in a better way to handle the new features of MySQL 5
But whats this about?
AND post_date != '0000-00-00 00:00:00'
Don't show me any records from the big bang?
Tom,
That's funny indeed when moving to MySQL from a real database. I noticed the same and experimented a bit on MySQL GROUP BY and DISTINCT oddity.
However, I never saw "has gone away". Interesting.
Alex
Anonymous,
"MySQL caches differently"
How exactly does MySQL cache? Could you please provide a link?
Thanks,
Mark
MySQL server has gone away
Is this MySQL's equivalent for the "end-of-file on communication channel" (ORA-03113).
The error is output from PHP, not directly from MySQL. The PHP code is trapping the MySQL error and displaying a user friendly version.
> ..more importantly, how come you
> can see this code - security
> anyone?
Many of the popular blogging sites dump database error messages and evey SQL to the user session when they crash.
Security? Oh yeah, we need to think about that...
sPh
------------------------------
Anonymous said....
But whats this about?
AND post_date != '0000-00-00 00:00:00'
Don't show me any records from the big bang?
------------------------------
'0000-00-00' ('yyyy-mm-dd') is the start of AD, not big bang. Had it been bing bang, the format would likely be '000000000000-00-00'
Tom Fox,
"The error is output from PHP, not directly from MySQL. The PHP code is trapping the MySQL error and displaying a user friendly version."
Looks to me like it's mysql itself - http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
Mark
Mark,
I stand corrected. Last time I used MySQL, it threw a technical error out, similar to the ones in the link, and WordPress beautified it.
Thanks for the link!
Tom
"MySQL server has gone away" is from MySQL, but the output of the SQL statement is from WordPress. (And while I understand the security concerns, we are talking about an open-source blogging tool here. It's not like the statements it generates can't be found through other means.)
------------------------------
Anonymous said....
But whats this about?
AND post_date != '0000-00-00 00:00:00'
Don't show me any records from the big bang?
------------------------------
Wordpress allows setting up of posts to "self-publish" at some future time using this field. Since the field is auto-populated with the zeros, it would appear to be published while it really has not since the default is to list all posts published before now().
Hi Tom
One out of the way question...
Do you use any RSS Feed/Atom reader ?
If yes then which one ?
If no then how do you manage all the blogs which you read frequently ;)
Sidhu
Well, I don't now the finer points of mySQL, but I do have my own wordpress blog so I logged into mySQL and the initial query returns:
+------+-------+-------+
| year | month | posts |
+------+-------+-------+
| 2007 | 1 | 1 |
| 2006 | 12 | 5 |
| 2006 | 5 | 2 |
| 2006 | 3 | 2 |
| 2005 | 10 | 4 |
| 2005 | 9 | 1 |
| 2005 | 5 | 1 |
| 2005 | 4 | 1 |
| 2005 | 3 | 3 |
| 2005 | 2 | 20 |
| 2005 | 1 | 29 |
| 2004 | 11 | 1 |
| 2004 | 9 | 1 |
| 2004 | 8 | 1 |
| 2004 | 7 | 2 |
| 2004 | 6 | 2 |
| 2004 | 5 | 4 |
+------+-------+-------+
17 rows in set (0.00 sec)
Where as when I remove the order by clause I get
+------+-------+-------+
| year | month | posts |
+------+-------+-------+
| 2004 | 5 | 4 |
| 2004 | 6 | 2 |
| 2004 | 7 | 2 |
| 2004 | 8 | 1 |
| 2004 | 9 | 1 |
| 2004 | 11 | 1 |
| 2005 | 1 | 29 |
| 2005 | 2 | 20 |
| 2005 | 3 | 3 |
| 2005 | 4 | 1 |
| 2005 | 5 | 1 |
| 2005 | 9 | 1 |
| 2005 | 10 | 4 |
| 2006 | 3 | 2 |
| 2006 | 5 | 2 |
| 2006 | 12 | 5 |
| 2007 | 1 | 1 |
+------+-------+-------+
17 rows in set (0.00 sec)
Beyond the argument of how or why it works, I can only state that it does work. Perhaps the order by is being applied to the entire post_date column.
Just to follow-up my earlier message - It looks like you can include any column that is not in the initial query for your order by clause. I've tried ID (the autoincrement PK of that table) and also POST_DATE_GMT and gotten results in order descending by date.
Even more interesting - if you order by YEAR(post_date) DESC it will sort them by descending years with months in no particular order, and if you order by MONTH(post_date) DESC you'll get months in order, but not years. If you try to order by both DESC e.g.:
ORDER BY YEAR(post_date) DESC, MONTH(post_date) DESC
It only gives the order by the second parameter.
Mmmh, I wouldn't compare Oracle and MySQL about "neat error messages"... :)
Gegory, check the link I mentioned above - I tried to explain there few bits of this behavior.
I found it difficult to find 'authorative' sources for MySQL caching but there seems to be a 'results' cache (which I guess will be what Oracle 11g will offer)
http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html
Then there are prepared statements that use bind variables.
http://dev.mysql.com/doc/internals/en/prepared-stored.html
The following indicates that 4.1 didn't cache the execution plan:
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
Finally, the following document seems to be more up-to-date with MySQL 5.0 references, and still states (sections 9.2/9.3)
"Our prepared statement engine does not save the execution plan in a prepared statement for reuse, and ideally we should not create it at prepare stage."
http://downloads.mysql.com/docs/internals-en.pdf
DBA's at one of the top 5 busiest web sites in the world use MySQL for their production database and they are inserting (OLTOP, not a batch job) about 300 million records into one table in a day. And they also have few indexes on that table.
At our company, Oracle is dying with "buffer busy waits" while inserting about 100 million inserts per day into one table with all the neat partitioning and stuff like that.
It seems like, there are very few areas where MySQL for sure beats Oracle in a big way. Being a Oracle fan....I hate to admit that.
POST A COMMENT
<< Home