Author Topic: Error importing large sql dump file  (Read 22971 times)

0 Members and 1 Guest are viewing this topic.

Offline
*
Error importing large sql dump file
« on: February 20, 2015, 02:07:36 AM »
Hi,

It's been 4 days now but I cannot import my sql dump file (351MB)
I tried many times to import this to PHPMyAdmin but still receiving errors.
I think the file is to big.

IS changing the value of max_allowed_packet help importing file?? Where to find this??

Please help Admin!


Offline
*
Re: Error importing large sql dump file
« Reply #1 on: February 20, 2015, 10:34:38 AM »
did you try to upload file and import with mysql command?

create database and user with CWP then upload file with ftp or whatever you use.
go to folder where is sql file uploaded

example:

cd /home/USERNAME/public_html/

import command:

mysql -u USERNAME -p DB_NAME < FILE_NAME.SQL

Offline
*
Re: Error importing large sql dump file
« Reply #2 on: March 10, 2015, 11:55:35 PM »
for bigger files its always the best to use the command line as zakrpa specified
AntiDDoS Protection (web + mail)
http://centos-webpanel.com/website-ddos-protection-proxy

Join our Development Team and get paid !
http://centos-webpanel.com/develope-modules-for-cwp


Services Monitoring & RBL Monitoring
http://centos-webpanel.com/services-monitor


Do you need Fast and FREE Support included for your CWP linux server?
http://centos-webpanel.com/noc-partner-list
Installation Instructions
http://centos-webpanel.com/installation-instructions
Get Fast Support Here
http://centos-webpanel.com/support-services

Offline
*
Re: Error importing large sql dump file
« Reply #3 on: March 25, 2015, 04:39:20 PM »
for bigger files its always the best to use the command line as zakrpa specified

I'm trying to import a database (due to transferring the site to another server).
 My database is actually very small, but I still get the error #1153 - Got a packet bigger than 'max_allowed_packet' bytes

mysql default config seems to be Max: 2,048KiB

How can i increase the max_allowed_packet? I cannot find this in /etc/my.cnf, and in php.ini neither.

I tried to import the database from terminal still it doesn't work, i get the same error #Got a packet bigger than 'max_allowed_packet' bytes

any help will be greatly appreciated.

Re: Error importing large sql dump file
« Reply #4 on: March 25, 2015, 04:56:14 PM »
did u try this? change max allowed packet according to ur need.

mysql --max_allowed_packet=100M -u root -p database < dump.sql

Offline
*
Re: Error importing large sql dump file
« Reply #5 on: March 27, 2015, 09:10:08 PM »
did u try this? change max allowed packet according to ur need.

mysql --max_allowed_packet=100M -u root -p database < dump.sql

I did succeed and could finally import database.

Unfortunately i didn't document properly what I did, but, the solution was in php.ini. I did change a couple of things, I remember: post_max_size = 50M, upload_max_filesize = 50M, and I did it from command line, firstly I double checked in info.php  where was the relevant php.ini file.
I tried to edit mysql conf file to no avail.
Editing  php.ini  from command line did the trick.

Thanks i really appreciate any help, and ready to share any useful output. (unfortunately, i don't find easy to get it right with CWP)

 

Offline
*
Re: Error importing large sql dump file
« Reply #6 on: March 27, 2015, 09:20:39 PM »
in cwp you have php.ini editor for this most commonly required changes.
AntiDDoS Protection (web + mail)
http://centos-webpanel.com/website-ddos-protection-proxy

Join our Development Team and get paid !
http://centos-webpanel.com/develope-modules-for-cwp


Services Monitoring & RBL Monitoring
http://centos-webpanel.com/services-monitor


Do you need Fast and FREE Support included for your CWP linux server?
http://centos-webpanel.com/noc-partner-list
Installation Instructions
http://centos-webpanel.com/installation-instructions
Get Fast Support Here
http://centos-webpanel.com/support-services

Offline
*
Re: Error importing large sql dump file
« Reply #7 on: March 27, 2015, 11:15:36 PM »
in cwp you have php.ini editor for this most commonly required changes.

Yes i did look in it, unfortunately my trouble was that I couldn't find anything like max_allowed_packet. I ended up by trial and error, changing some params as specified aboce, and at the end it did work.

Somehow I wish this stuff was a little bit more documented, but I uderstand CWP is free and one must be prepared to spend time to find the way around and resolve issues. I love this panel, and I truly hope I can succeed to get it running properly. I'm not there yet though.

Re: Error importing large sql dump file
« Reply #8 on: March 28, 2015, 01:18:39 AM »
You need to define max_allowed_packet in /etc/my.cnf file not in php.ini file.

Offline
*
Re: Error importing large sql dump file
« Reply #9 on: March 28, 2015, 01:10:29 PM »
You need to define max_allowed_packet in /etc/my.cnf file not in php.ini file.

that's the first thing I did, but made no changes. (restarted the services, rebooted etc.)
I did risolve only when I edited php.ini. I don't know why, but that's what happened.

ps.
in the /etc/my.cnf file there wasn't max_allowed_packet entry, I tried to add it to it, but made no change)

Thanks for support.