Solved

MySQL 4 to MySQL 5

Posted on 2013-06-02
2
271 Views
Last Modified: 2013-06-02
I am being forced to convert an application from MySQL 4 to 5.

I thought it would be pretty simple. The problem I've run into is size.

The full database dumped into a sql file is 324MB. The MAXIMUM I am allows to import into the MySQL 5 created database is about 49 MB.

First question, is there a way to increase that? I'm guessing it's a server parameter & that the host (1 & 1 will not allow increasing it.

So I considered breaking it into pieces. There are tw individual tables that are themselves over 49MB. One is 115 MB & ones is 80.

I think I can selectively export the needed data in chunks under 49 MB each, I already did the biggest one; 18 MB in a csv file.

The real question is if I do the 4 or 5 largest tables individually, is there an easy way to export all the others in one "chunk"? There are a total of 64 tables, of course I understand I can do them one by one. I mean the 57 or 58 that I can lump together.

I am using phpmysql to to the exports / imports.

Thanks
0
Comment
Question by:Richard Korts
2 Comments
 
LVL 11

Accepted Solution

by:
SANDY_SK earned 500 total points
ID: 39214707
Hi rkorts,

49MB is actually too small for it to stop, in fact i have restored dumps which are in GBs in mysql 5

what you can do is check in the my.ini file and change the value of the variable  max_allowed_packet to something like 500 MB like shown below. (This is possible if you have direct access to the mysql server)

max_allowed_packet=500M

when you are changing this value make sure you have stopped the mysql service and then restart it.

also try restoring it using the mysqldump command rather than phpmysql.
0
 

Author Comment

by:Richard Korts
ID: 39214724
To SANDY_SK

Thanks; I found that if I used gzip I could zip the 118 MB (largest table) to 20 MB.

So I'll just load it in a few pieces.

Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Install MySQL 5.6 and PHP on Centos Linux 6 120
Whether to use true/false, yes/no or 0/1 11 58
updating the date data 12 30
PHP & MySQL - Rounding Results from a Select Query 3 25
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question