Sunday, April 01, 2007

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?

POST A COMMENT

27 Comments:

Blogger Sidhu said....

lol...too good...no bind variables, hard coding of dates and then order by post_date :D

Sidhu

Mon Apr 02, 12:38:00 AM EDT  

Anonymous Anonymous said....

"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)

Mon Apr 02, 01:51:00 AM EDT  

Blogger Jeff Moss said....

..more importantly, how come you can see this code - security anyone?

Mon Apr 02, 02:26:00 AM EDT  

Blogger Patrick Wolf said....

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

Mon Apr 02, 03:34:00 AM EDT  

Anonymous Laurent Schneider said....

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

Mon Apr 02, 03:36:00 AM EDT  

Blogger Thomas Kyte said....

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"

Mon Apr 02, 05:59:00 AM EDT  

Blogger Laurent Schneider said....

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?

Mon Apr 02, 06:39:00 AM EDT  

Blogger Laurent Schneider said....

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

Mon Apr 02, 06:48:00 AM EDT  

Blogger Stew said....

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

Mon Apr 02, 07:38:00 AM EDT  

Anonymous 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?

Mon Apr 02, 09:21:00 AM EDT  

Blogger Alex said....

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

Mon Apr 02, 10:15:00 AM EDT  

Anonymous Mark said....

Anonymous,

"MySQL caches differently"

How exactly does MySQL cache? Could you please provide a link?

Thanks,
Mark

Mon Apr 02, 11:18:00 AM EDT  

Anonymous Markku Uttula said....

MySQL server has gone away

Is this MySQL's equivalent for the "end-of-file on communication channel" (ORA-03113).

Mon Apr 02, 11:19:00 AM EDT  

Anonymous Tom Fox said....

The error is output from PHP, not directly from MySQL. The PHP code is trapping the MySQL error and displaying a user friendly version.

Mon Apr 02, 11:41:00 AM EDT  

Anonymous Anonymous said....

> ..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

Mon Apr 02, 11:50:00 AM EDT  

Anonymous Anonymous said....

------------------------------

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'

Mon Apr 02, 11:53:00 AM EDT  

Anonymous Mark said....

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

Mon Apr 02, 12:03:00 PM EDT  

Anonymous Tom Fox said....

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

Mon Apr 02, 12:58:00 PM EDT  

Blogger jim said....

"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.)

Mon Apr 02, 01:10:00 PM EDT  

Anonymous Steve said....

------------------------------

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().

Mon Apr 02, 01:19:00 PM EDT  

Blogger Sidhu said....

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

Mon Apr 02, 02:03:00 PM EDT  

Blogger Gregory said....

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.

Mon Apr 02, 03:22:00 PM EDT  

Blogger Gregory said....

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.

Mon Apr 02, 03:49:00 PM EDT  

Anonymous Anonymous said....

Mmmh, I wouldn't compare Oracle and MySQL about "neat error messages"... :)

Mon Apr 02, 05:00:00 PM EDT  

Blogger Alex said....

Gegory, check the link I mentioned above - I tried to explain there few bits of this behavior.

Mon Apr 02, 08:37:00 PM EDT  

Anonymous Anonymous said....

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

Tue Apr 03, 02:36:00 AM EDT  

Blogger Sai said....

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.

Wed Apr 11, 03:17:00 AM EDT  

POST A COMMENT

<< Home