SELECT * FROM table shown in processlist when opening big tables

SELECT * FROM table shown in processlist when opening big tables

Postby Hanne Berthelsen » Tue 08 Mar 2005 16:31

Why is the "SELECT * FROM table" shown in the processlist when opening a table (using unit TMyTable) even though FetchAll is false and Options.QueryRecCount is false ?

I have just had a problem with a huge database (of 5.7 mill records) that failed inserting records into the table and the application died.
"SELECT * from table" was shown in the processlist.
MyISAMCHK didn't help the table - and we had to create a new table.

Have you got any idea about what was wrong with the table / PC / MySQL setup?

I have tested a table - when having more than 212 records in the table then the "SELECT * from table" statement is shown in the processlist when opening the table - for smaller tables it was not shown.

Regards Hanne
Hanne Berthelsen
 
Posts: 15
Joined: Thu 18 Nov 2004 07:51

Postby Ikar » Wed 09 Mar 2005 15:48

This state must be shown for those connections that haven't received all requested data from the server. So for FetchAll = True this message must disappear quickly.

> that failed inserting records into the table and the application died.

The problem occurs because of locking MyISAM tables. If you try to update underfetched table MySQL waits while it will be completely fetched. Please refer to MySQL Reference manual 5.3 Locking Issues for details. To avoid the problem you can use any of following solutions.
- set FetchAll to True;
- change type of the table into, for example, InnoDB.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Re: SELECT * FROM table shown in processlist when opening big tables

Postby r34 » Fri 11 Mar 2005 08:17

Hanne Berthelsen wrote:
I have just had a problem with a huge database (of 5.7 mill records) that failed inserting records into the table and the application died.
"SELECT * from table" was shown in the processlist.
MyISAMCHK didn't help the table - and we had to create a new table.

Regards Hanne


I hate to tell you this :-) but that's a 'trival' database, not a huge one, for mysql! Assuming you had a where clause in your select statement, say you were only getting 5000 records back, it should take about .02 of a second, and even if you were getting all 5.7 million back it should run in a few seconds on any decent computer...
r34
 
Posts: 14
Joined: Mon 28 Feb 2005 06:33

Re: SELECT * FROM table shown in processlist when opening big tables

Postby Sonyk » Tue 03 May 2005 12:50

Hanne Berthelsen wrote:Why is the "SELECT * FROM table" shown in the processlist when opening a table (using unit TMyTable) even though FetchAll is false and Options.QueryRecCount is false ?

I have just had a problem with a huge database (of 5.7 mill records) that failed inserting records into the table and the application died.
"SELECT * from table" was shown in the processlist.
MyISAMCHK didn't help the table - and we had to create a new table.

Have you got any idea about what was wrong with the table / PC / MySQL setup?

I have tested a table - when having more than 212 records in the table then the "SELECT * from table" statement is shown in the processlist when opening the table - for smaller tables it was not shown.

Regards Hanne


I HAD THE SAME PROBLEM!!! AND ALSO THE SOLUTION GIVEN IS NOT GOOD.
I HAVE NO IDEEA WHAT TO DO, BUT I REALY NEED A SOLUTION. I WROTE A KIND OF LOADER WHICH UPLOAD MORE THAN 580 TABLES. THE BIGGEST TABLE IS 3GB WITH MORE THAN 14,5 MILLION RECORDS IN IT, AND IF I MAKE THE CHANGES I RECEIVE THE "OUT OF MEMORY" MESSAGE.
For example I should insert only 111 record in this table. I delete some records and after that I start to insert.

can you help me???

Sandor Kacso
Sonyk
 

Postby Ikar » Wed 04 May 2005 10:55

Unlikely you have to modify all million records at once. Using WHERE clause minimize a number of retrieved records and then modify them. If you needn't delete and edit records but only insert new ones then using LIMIT 0 or TMyLoader will greatly speed up a process.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56


Return to MySQL Data Access Components