Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.


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?

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
You should green check your own answer so the topic appears as solved –  atxdba Jul 12 '12 at 0:41
    
"You can accept your own answer in 14 hours" –  Blenderer Jul 12 '12 at 3:54
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.