Mysql phpMyadmin Timeout Question

Question

I have a very large database in Mysql. I need it to be maintained this way. for example its about 300,000 records in Innodb.

I parsed the records into the database but now I need to refine it by converting some of the field types for example a long int to tinyint or something. When I run this through phpMyadmin the connection after 6mins halts and I get a connection timeout error?.

Since php sent the request to mysql shouldn't mysql just be doing its thing no matter what until its done? so even if the php page times out it will it still get done?. Or when the connection times out does Mysql just give up on the request?.

I need to run a filter on the database through php and I want to be able to update the page every 30 seconds with new results it finds as its continuing its search , but if the user navigates away I would still like to finish the search as it creates a table for that user. So if they run a query and close their browser when they come back to it will it have continued with the search ?.

Never worked with a big db like this before , if users run a textual search I know it will take a long time. Any insights /tips or guidance would be appreciated .

Thanks

@user685590 +1 for the question. What phpMyAdmin version are you using? — eisberg, Mar 31 '11 at 11:36
What are the InnoDB options of your MySQL server ? such as buffer pool size ? — Serty Oan, Mar 31 '11 at 13:04

score 1 · Accepted Answer · answered Aug 03 '11 at 07:23

1

You can also generally use the mysql client on the server itself. It tends to be my favorite for imported large DBs.

mysql -u user -p -h localhost dbname < import.sql

Or in your case to get to the command prompt (same as the SQL window in phpmyadmin:

mysql -u user -p -h localhost dbname

answered Aug 03 '11 at 07:23

ImpressTheNet Web Developer

377
2
2

Thanks for the advice. Maybe needs mentioning that you need to have import.sql accessible and loaded to your server. – kontur Aug 30 '12 at 07:18

score 0 · Answer 2 · answered Mar 31 '11 at 13:05

In InnoDB, all user activity occurs inside a transaction. So, if the query is not going into the end, the transaction won't be committed and it will be leaved.

Nevertheless, in PHP, you can tell a script to continue even if the user is stopping the web page. It is done with the function ignore_user_abort().

but I think phpMyAdmin doesn't give a ignore_user_abort feature.

Another solution would be to use asynchronous MySQL query but I think it is not (yet) possible with PHP. Maybe here, but not in phpMyAdmin neither.

Mysql phpMyadmin Timeout Question

2 Answers2

Linked