Mysql bug with order + limit ?

Anon

Hi,
I just wanted to clusterize a database output in certain chunks like 0 - 100, 100 - 200, 200 - 300... (WITH LIMIT)
In addition I wanted to give the possibility to sort the output (with ORDER BY)

The database output is shown up in a HTML table.

Now I've experienced a really strange behave:
As long as I just specify a limit, everything goes all right but when I specify
a order too, then my MYSQL does just nothing - there is zero output!! where only some orders do not work!

The best thing is: wehen I click around and specify some other orders and check back to the one which did not work, it suddenly works.

SO, does anyone have an idea what could cause this?

MySQL Version: 3.23.34a

thx RAPHEAD

gushy

Well it should work!! I suppose it could be your MySQL version, try upgrading.

does error() or errno() give you any output?

I've used order by and limit and it works, here's is a statement straight out of one of my scripts:

"SELECT * FROM report ORDER BY status ASC, id ASC limit $offset,$limit"

Hope that helps.

-- Nick Gushlow

Anon

Hi Nik,
I think I've experienced a bug, I've updated to Version 3.23.38 and it seems to be ok now.
(not 100% sure but up to now there were no faults 🙂 ). There was on bug mentined with ORDER BY in the chgelog!

I ask myself one thing. Ok MYSQL is really a great deal but it couldnt compete within the enerprise terretory with such bugs.

thx for your help!

gushy

Glad you solved it. If it was a bug at least it's fixed now.

As for your last comment, MySQL was never designed to be an enterprise level database. It's fine for small and medium business, and I know some people using it for large scale apps that I wouldn't even dream of trying, but when you get up to enterprise level you need to be considering the big boys like Oracle.

-- Nick Gushlow

Anon

Hello,

I have faceing the same problem with mysql version 3.23.38

Most interesting thing is that in some cases, there is no problem and some time fails query.
So problem is with a some special cases of query with limit and order by , not in all cases.

Any body can help me out with this problem.

regards,
shahzad

gushy

If it works most of the time, then it would indicate that your problem lies in one of two places.

the sql statement you are performing (syntax error perhaps - try getting output from mysql_errno() and mysql_error()).
The data in the table is the cause of your problems. What are you trying to order by?

You say it fails, can you be more specific?

can you provide an example statement that fails? your table structure would also help.

-- Nick Gushlow.

Anon

Here is query:

SELECT [ fields to slect ] from article ,asubject ,atype ,author, source left join user on user.userId = article.user where artstatus!='N' and artsourceId = sourceId and authorId = artauthorId and arttypeid = atypeId and asubjectId=artsubjectid and artauthorid=145 order by articleId desc limit 1 , 20

There is no syntax error.
Table contain Arabic / English data.
Data in the table may be causing problem.

When my application in php is getting data from mysql it gets some rows. And show no error. (mysql_error)
When I run same query on phpmyadmin query prompt, it gives an error with no description.
If I remove limit portion of query, then show there is no error even on phpmyadmin.
So problem lies with limit portion of query.

regards,
shahzad

gushy

So you are saying it returns some rows? just not 20. Is that right?

Does the query work from the mysql console?

As you say the syntax seems fine, my only comment would be to put '' around the 145.

I've not used mixed language systems before so that could be the problem, but I'm not sure.

--Nick Gushlow

Anon

So it is closing of issue now.

There is no bug in MySQL 3.23.38 regarding limit / order by , problem lies in phpMyAdmin 2.1.0
Upgrade to phpMyAdmin 2.2.0 will reslove the issue.

I think limit portion of phpmyadmin 2.1.0 conflicts
with default limiting feature of myadmin for pageing.
But in phpMyAdmin 2.2.0 this issue is solved.

check change log here:
http://phpmyadmin.sourceforge.net/ChangeLog.txt

Thanks Nick for your help.

regards,
shahzad

gushy

No problem, glad you got it sorted.

-- Nick Gushlow.