Import large .sql file with php and zend framework

Question

I have to import a (big) .sql file to MySQL with PHP and the Zend framework.

The connection is open, and I have $_sql containing the whole content of the SQL file, but - of course, a simple

$this->db->query($_sql);

will not work.

What can I do to import this large file?

Update: Yes, it is a mysql-dump with structure-definitions of my mysql-database. And yes, it need to run from within php without using command line.

SOLVED: I need a mysqli object to fix that, mysqli->multiQuery is the way to go. Here's what my (unit test) bootstrap look like:

<?php

// Define path to application directory
defined('APPLICATION_PATH')
    || define('APPLICATION_PATH', realpath(dirname(__FILE__) . '/../application'));

// Define application environment
defined('APPLICATION_ENV')
    || define('APPLICATION_ENV', (getenv('APPLICATION_ENV') ? getenv('APPLICATION_ENV') : 'testing'));

// Ensure library/ is on include_path
set_include_path(implode(PATH_SEPARATOR, array(
    realpath(APPLICATION_PATH . '/../library'),
    get_include_path(),
)));

require_once 'Zend/Loader/Autoloader.php';
Zend_Loader_Autoloader::getInstance();

// Create application, bootstrap, and run
$application = new Zend_Application(
        APPLICATION_ENV,
        APPLICATION_PATH . '/configs/application.ini'
);

$options = $application->getOptions();

$_sqlFile = realpath(APPLICATION_PATH.'\\..\\tests\\sql\\');
$_sqlFile .= '\\myLargeSQL.sql';
$sql = file_get_contents($_sqlFile);
$mysqliobject = new mysqli(
        $options['resources']['db']['params']['host'],
        $options['resources']['db']['params']['username'],
        $options['resources']['db']['params']['password'],
        $options['resources']['db']['params']['dbname']
);
$mysqliobject->multi_query($sql);

if ($mysqliobject->errno <> 0) {
    throw new Exception('Error creating temp database in ('.__FILE__.' on line '.__LINE__.')');
}

$mysqliobject->close();

Don't worry about the \, this is only temporarly (will fix that soon) and yes, i am on a windows machine. So, maybe somebody can use it.

Is the SQL file one giant SQL statement or is it multiple smaller ones? If it's multiple smaller ones, you can run the SQL file one command at a time. — Mr. Llama, Mar 09 '12 at 16:08
Do you really need the query executed using ZF? I usually revert to command-line `mysql` with the `source` command for large `.sql` files. — David Weinraub, Mar 09 '12 at 16:09
Yes, it is a mysql-dump and yes, it needs to run within php without command line. I guess, it must be possible to run a large sql-file from php/zf even there are much commands in it. It will run with CLI, so max_exec_time is not needed. This all is part of UnitTesting an application and temporarly autocreating of the needed tables in mysql (with MEMORY-Engine, so it will not stay). P.S. No, sqlite does not work, it have to use exacly the same structure as in mysql and sqlite does not know many datatypes, so: i have to use mysql and it must be possible to execute a large file). — Paladin, Mar 12 '12 at 08:31
It is not important, how large the file is, for all errors appear in the first 2 lines (in my old code). So, with mysqli->multiQuery option in my new code, all works well. — Paladin, Mar 12 '12 at 15:46

dextervip · Answer 1 · 2012-03-10T04:36:23.560

You would able to do it with the following code.

$db = Zend_Db_Table::getDefaultAdapter();
$db->beginTransaction();
$sql = file_get_contents(APPLICATION_PATH . '/path/to/your/data.sql');
$db->query($sql);
$db->commit();

However if your sql is too much big, It may exceed php maximum execution time. I would recommend you, create a php script and run it from the php command line. For example in your public folder, create php script called import.php

 <?php

 // Define path to application directory
 defined('APPLICATION_PATH')
    || define('APPLICATION_PATH', realpath(dirname(__FILE__) . '/../application'));

 // Define application environment
 defined('APPLICATION_ENV')
    || define('APPLICATION_ENV', (getenv('APPLICATION_ENV') ? getenv('APPLICATION_ENV') : 'development'));

 // Ensure library/ is on include_path
 set_include_path(implode(PATH_SEPARATOR, array(
        realpath(APPLICATION_PATH . '/../library'),
        get_include_path(),
    )));

 /** Zend_Application */
 require_once 'Zend/Application.php';

 // Create application, bootstrap, and run
 $application = new Zend_Application(
            APPLICATION_ENV,
            APPLICATION_PATH . '/configs/application.ini'
 );

 $options = $application->getOptions();

 $db = Zend_Db::factory($options['resources']['db']['adapter'], array(
        'host' => $options['resources']['db']['params']['host'],
        'username' => $options['resources']['db']['params']['username'],
        'password' => $options['resources']['db']['params']['password'],
        'dbname' => $options['resources']['db']['params']['dbname']
    ));

 //OR 
 //$application->getBootstrap()->bootstrap('db');
 //$db = Zend_Db_Table::getDefaultAdapter();

 $db->beginTransaction();
 $sql = file_get_contents(APPLICATION_PATH . '/path/to/your/data.sql');
 $db->query($sql);
 $db->commit();

Make sure your application.ini has a default db table adapter as zendframework documentation. Otherwise you can configure manually the database settings. In the example you could put the sql file in the application folder.You can change to suit your needs.

Then you can run it from your command line, go to the public folder in the command line and run the command

 php import.php

Update:

Another simple way to import large sql files, it would be using a tool. I am used to use Navicat (http://www.navicat.com/en/products/navicat_premium/premium_overview.html). It's so easy and fast to import/export

Thanks but it does not work. maybe because, there are many lines of sql-code (mysql-dump). have an error in the second line. when i command out the first one, the error is in the third, so i think there's a problem with the ; seperator somehow. When i execute the same sql in phpMyAdmin, all works well. — Paladin, Mar 12 '12 at 08:33
Well Yeah maybe could be it, I have been using it for long sql files and works fine. Anyway I see you found out the solution, you need to answer or choose an answer :) @SaschaPresnac — dextervip, Mar 12 '12 at 22:21

score 1 · Answer 2 · answered Mar 10 '12 at 05:52

1

probably fastest way to import large file is using cli:

mysql -u username -p databasename < yourfile.sql

answered Mar 10 '12 at 05:52

b.b3rn4rd

8,494
2
45
57

score 0 · Answer 3 · answered Mar 09 '12 at 22:47

0

I think, it's not task for ZF. Try to use sypex dymper - http://sypex.net/en/ - perfect tool for sql import/export

answered Mar 09 '12 at 22:47

turist_ua

114
1
1
7

score 0 · Answer 4 · answered Mar 12 '12 at 08:51

0

http://www.mysqldumper.net/

It's a open Source Web-Application. If you don't want to use it you can browse the Code and see how they do it. It's open source.

MySQL-Dumper can handle .sql files with 200+MB...

answered Mar 12 '12 at 08:51

riyuk

174
1
6

Import large .sql file with php and zend framework

4 Answers4