SELECT * FROM table shown in processlist when opening big tables

[Hanne Berthelsen]

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

[Ikar]

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.

[r34]

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

[Sonyk]

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

[Ikar]

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.