MySQL Bugs: #53517: Query resturns a different results after doing BACKUP

Bug #53517	Query resturns a different results after doing BACKUP
Submitted:	9 May 2010 3:57	Modified:	25 Jun 2010 7:14
Reporter:	Riza Marhaban	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	5.1.46	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Backup, query

Description:
MySql returns a wrong DESC direction after doing backup or other related with backup process. Probably!

How to repeat:
Create a table `books` with BookID, Title and PublishedYear columns. The PublishedYear is a varchar type and the record is like this [year|month] for example: 2010|01, 2010|04, 2009|08 etc.

And do this query for pagination:

SET @rownum := 0;
SELECT * FROM
(

   SELECT (@rownum:=@rownum+1) AS `Rownumber`, b.BookID,b.Title,b.PublishedYear
    FROM `books` AS b
      WHERE ((YEAR(NOW())-LEFT(b.PublishedYear,4)*1) < 2)
       ORDER BY b.PublishedYear DESC,b.BookID DESC

) AS t WHERE
  (`Rownumber` > 0) AND (`Rownumber` <= 10);

It will return first page index 0 with 10 records starting from the latest [year|month] order.

The returned should be ok. Now do Backup using MySql Adminsitrator or MySql Workbanch. And execute the query again. The ORDER BY b.PublishedYear DESC,b.BookID DESC will return result is the same like this ORDER BY b.Title ASC,b.BookID ASC.

The results not sorted from the latest [year|month] instead is from the Title. Notes about the book Title. I have a records of title that use numbers in the title such as "100 Ways to bla bla bla" and it always return the title that starts with numbers.

I don't know weather backup is really the causes but it works like this to repeat the problem for me.

Suggested fix:
For this time, to fix it, restart MySql service. Done. Probably anyone can help with this. I don't want to restart MySql service after each backup because this never happen before with the previous version.

Do you have the same odd result when you don't use GUI tools like Workbench or Administrator?

What happens when you are using plane CLI (mysql) and fire the query?

I'm still waiting for the activities report from the database server admin. As far as I know, this odd results happens on sunday morning. So, it probably from some other database users that might do the back up or other activities.

This problem happen periodically every sunday morning in the database server. I cannot report more after I get the database server activity report. After the mysql service restarted. The problem just gone away and return to normal query results.

So it is not repeatable?

@Susanne Ebrecht: I don't know. Because it happen if there is a backup process or probably other than backup process. That is why I report this bug. The odd query always happen which is never happen in previous versions ex. MySql 5.1.11 etc.

Unfortunately, we can't do much here without more informations.

I need to know if it is Workbench/Administrator related or not.

We need to be able to reproduce it.

@Susanne Ebrecht:

I try to get the report from the the hosting company but they haven't send me any notice. But the problem do exist. I cannot generate the symptoms locally in my own server since it never happen, but it happen in the hosting database server.

They do help me with restarting the MySql server serice and it returns to normal again. This is very unusual. I don't know if you can see from the query at top. Or can you tell me if there is a best query for paging a records. All I need is a simple query that returns a paging with index page (ex. index 0 is Page 1) and page size (ex. 10 records). Thanks.

We will wait for your feedback.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".