When you try to import large file (if you are working with big BLOB columns), like i am trying to import 140 MB size .sql file, then MySQL server gives MYSQL sever has gone away error. Here is the screen shot.
To solve this problem, you need to set max_allowed_packet in my.ini file (in Windows) in MySQL server. You can set this variable in two ways.
1. By MySQL Administrator GUI :
a. Click on start >> All Programes >> MySQL
b. Now Right Click on MySQL Administrator and Run as administrator .Here is the screen shot.
c. Next enter password in MySQL Administration login window. It will open new window.Here is the screen shot.
d. Next select Startup Variables menu from left hand site menu bar.Next select Advanced Networking Tab . Next check Max. packet size check-box in Data / Memory size section. Next set whatever size you want to set in Max. packet size 's Text-box .Here is the screen shot.
e. Next click on Apply changes. Next got to Service Control menu at left hand side menu. Next click on Stop Service and Start Service again. Here is the screen shot.
f. Now all done, import your file (size whatever your have set in Max. packet size ).It will successfully import.
2. Directly add max_allowed_packet variable value in my.ini file:
a. Go to MySQL installation folder (For example C:\Program Files\MySQL\MySQL Server 5.1) , open my.ini file add max_allowed_packet=140M in the end. if you save this file sometimes it can be say Access Denied.
b. So copy this file on Desktop and add max_allowed_packet=140M and save.
c. Now go to original my.ini file and change the name of file (e.g myBakup.ini for backup purpose if something went wrong ) and paste my.ini file that you have changed on Desktop in MySQL Server 5.1 folder.
d. Now restart the MySQL server. All done.
0 comments:
Post a Comment