Trying to import a large sql file using BigDump. Partial fail occuring

Question

Problem:

I am using the php script: BigDump to import a sql file that phpMyAdmin says has 9344926 rows.

However when I run it, BigDump calculates and inserts 4050623 rows

It should have Total: 9344926 rows

My SQL file looks like this:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `ree`
--

-- --------------------------------------------------------

--
-- Table structure for table `media`
--

CREATE TABLE IF NOT EXISTS `media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `URL` varchar(255) DEFAULT NULL COMMENT 'URL',
  `MLSNUMBER` bigint(20) NOT NULL COMMENT 'MLSNUMBER',
  `FILETYPE` varchar(32) DEFAULT NULL COMMENT 'FILETYPE',
  `MIMETYPE` varchar(128) DEFAULT NULL COMMENT 'MIMETYPE',
  `CREATEDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'CREATEDATE',
  `MODIFYDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'MODIFYDATE',
  `POSITION` int(10) NOT NULL COMMENT 'POSITION',
  PRIMARY KEY (`id`),
  KEY `MODIFYDATE` (`MODIFYDATE`),
  KEY `MLSNUMBER` (`MLSNUMBER`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9540549 ;

--
-- Dumping data for table `media`
--

INSERT INTO `media` (`id`, `URL`, `MLSNUMBER`, `FILETYPE`, `MIMETYPE`, `CREATEDATE`, `MODIFYDATE`, `POSITION`) VALUES
(1, 'http://image.realcomponline.com/photos.rps?PATH=PROPERTY/35A00/1C07B/35A0011C07B74E/8ECA936E29E4F7.jpg&g=100&sp=0&l=0&t=0&r=10000&b=10000&o=0&1cf=0&w=320&h=240', 212069136, 'PICTURE', 'image/jpeg', '2012-07-06 09:54:50', '2012-07-06 09:54:50', 7),
(2, 'http://image.realcomponline.com/photos.rps?PATH=PROPERTY/35A00/1C07B/35A0011C07B74E/13B4A1CB2C6483.jpg&g=100&sp=0&l=0&t=0&r=10000&b=10000&o=0&1cf=0&w=320&h=240', 212069136, 'PICTURE', 'image/jpeg', '2012-07-06 09:54:52', '2012-07-06 09:54:52', 8),
(3, 'http://image.realcomponline.com/photos.rps?PATH=PROPERTY/33019/DE74A/330196DE74A041/28DBA86D5DF4D9.jpg&g=100&sp=0&l=0&t=0&r=10000&b=10000&o=0&1cf=0&w=320&h=240', 212069138, 'PICTURE', 'image/jpeg', '2012-07-06 11:00:53', '2012-07-06 11:00:53', 6);
   ...
   ...
   ...

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Question:

Is the problem with the BigDump script or am I doing something wrong? Or is there a better way to go about my 9 million row import/export?

Answer 1

I finally found the actual problem! It had nothing to do with how I was importing it, it was my local server's export that failed halfway through!

After opening up VIM and going to the end of the file, I found: Fatal error: Maximum execution time of 300 seconds exceeded...

Ahhhh, the problem was with my local server's timeout, so it only exported a partial amount of the data.

Answer 2

It's possible your PHP script is running out of memory at a certain point during execution. You can import the script via the command line with

mysql -u username -p database < yourfile.sql.

Where username is your mysql username, database is the new database and yourfile is the name of your dump file.

You could also copy the database file directly from the original database data directory into the new server database directly. However keep in mind that since InnoDB stores tables under one tablespace file. Every InnoDB table would be essentially be moved over.