By paulrandall on

Hello everyone,

Desperately in need of a little advice on MySQL importing of a large Drupal dump file.

Last week we had to re-image a server and we are in the process of re-importing various MySQL databases and one of them is a Drupal 7.2.2 (from the changelog). Anyway it's going on a 1&1 Plesk server and the SQL files is 48Mb in size because it's a fully populated Drupal. Think of it as a server migration. I've uploaded it to the users webspace and have been using the 'mysql --password=xxxxxxx --user=xxxxx dbname

Changing the max allowed packets is not an option so I was wondering if anyone can tell me whether I can empty any tables of data and successfully import this database?. I can import it into 'Sequel Pro' on my Mac and see and manipulate the data.

Any help would be appreciated.

Cheers

Paul

Comments

yelvington’s picture

yelvington commented

This is a database administration issue that can be resolved:

http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

It's possible that the source of the problem is cache tables. You should truncate the cache tables before exporting, or use the Backup and Migrate module, which omits cache data when it exports. If you are unable to administer the database (or get your hosting company to do it for you), you might try importing the data into a system you can control (i.e., your laptop) and truncating the cache there.

paulrandall’s picture

paulrandall commented

Hi Yelvington and seasons greetings to you sir :-)

I'll give that a go. When you say cache tables is it OK to truncate every cache table? i.e. every table beginning with 'cache_' as well as the main 'cache' table or should I avoid truncating some of them? if so which ones?

Sorry to sound dumb but this is not exactly something I do in a daily basis and it's at east 8 years since I touched Drupal as I don't tend to do much design anymore. Mainly hosting :-)

After this I have to then figure out what file/folder permissions of set on Plesk 11. What a nightmare.

Cheers again for your help.

Paul

Jaypan’s picture

Jaypan commented

You can safely truncate (not delete) any files that begin with 'cache'.

theroyal’s picture

theroyal commented

I just had the same issue,
I did'nt truncate the cache tables.
what I did is increasing the max_allowed_packet ,
Plesk usually set this value to 1M by default,
so I increased it to 32M from /etc/my.cnf
I was using filezella with the root user.

you might need to restart the Mysql after that.