Clever Geek Handbook

Php or mysql gets stuck while importing to db

we have a php script that runs this console command

cat import.sql | mysql -u user -ppassword

so it just imports the file to DB

the file is quite large ~ 120 mb

but it only contains insert / replace requests like this one:

/*!40000 ALTER TABLE `actionList` DISABLE KEYS */;
REPLACE INTO `actionList` VALUES (1,'buttonClick','Click on a button'),(2,'buttonClose','Click on the close button');
/*!40000 ALTER TABLE `actionList` ENABLE KEYS */;

I removed the lock table from the code.

Now when this script starts my loading of other pages until this script is finished.

Any ideas why this is happening?

+3


source to share


2 answers


It was not a DB lock, it was a session lock.

When we ran the script, it took a long time and the session was open, so other pages tried to use the session but could not.

So I added:



session_write_close();

and he solved the problem.

0


source


Use a different password for the import process and do not import it as root.

You can also restrict the user:



mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank';
mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
    ->     WITH MAX_QUERIES_PER_HOUR 20
    ->          MAX_UPDATES_PER_HOUR 10
    ->          MAX_CONNECTIONS_PER_HOUR 5
    ->          MAX_USER_CONNECTIONS 2;

For more information: http://dev.mysql.com/doc/refman/5.0/en/user-resources.html

0


source






More articles:


All Articles