Laravel - seeding large SQL file

laravel import sql file
laravel seeder
laravel factory
laravel run sql file
laravel-backup
laravel generate seeder from database
laravel seeder file creation
laravel seeder faker

A memory exhaustion happens when I run my DB seed script in production.

Below is my seed script.

class MembershipTableSeeder extends Seeder 
{
    public function run()
    {
        DB::table('members')->delete();

        foreach (range(1, 99) as $days){
            Members::create(array('membership_code' => 'test'.$days));
        }

        DB::unprepared(file_get_contents(app_path()."/database/seeds/members.sql"));
    }
}

So what I did was add a no-limit on my seed script.

ini_set('memory_limit', '-1');

The problem now is that when I run the script it logs the output into the terminal the content of the SQL script (which is very, very big).

Is there a good way of running a SQL dump inside my DB seeds that doesn't consume much memory? What I did now was run it manually:

mysql -uuser -p db < script.sql

The problem happens because when using Db::unprepared it also logs the query to the laravel.log file, making in background much more actions then you think, from this side you have memory exhaust. If you are not running the safe mode I would stick to executing the console command like this:

exec("mysql -u ".\Config::get('database.mysql.user')." -p".\Config::get('database.mysql.password')." ".\Config::get('database.mysql.database')." < script.sql")

Laravel - seeding large SQL file, Laravel - seeding large SQL file. A memory exhaustion happens when I run my DB seed script in production. Below is my seed script. Laravel includes a simple method of seeding your database with test data using seed classes. All seed classes are stored in the database/seeds directory. Seed classes may have any name you wish, but probably should follow some sensible convention, such as UserSeeder, etc. By default, a DatabaseSeeder class is defined for you.

For others who prefer a more Laravel-ish solution, this is how I handled it:

/**
 * This class is responsible for running the data dump sql.
 * It is recommended to update this class instead of creating new ones for new database content dumps.
 */
class DatabaseDumpSeeder extends Seeder
{
    /**
     * Run the database seeds.
     * @throws \Exception
     */
    public function run()
    {
        // Note: these dump files must be generated with DELETE (or TRUNCATE) + INSERT statements
        $sql = file_get_contents(__DIR__ . '/dumps/dump-20150709.sql');

        if (! str_contains($sql, ['DELETE', 'TRUNCATE'])) {
            throw new Exception('Invalid sql file. This will not empty the tables first.');
        }

        // split the statements, so DB::statement can execute them.
        $statements = array_filter(array_map('trim', explode(';', $sql)));

        foreach ($statements as $stmt) {
            DB::statement($stmt);
        }
    }
}

Laravel. seeding large sql file, days)); } DB::unprepared(file_get_contents(app_path()."/database/seeds/​members.sql")); } }. So what I did was add a no-limit on my seed script. Laravel 4: Seeding Large CSV Files Using MySQL Load Data Problem: I wanted to quickly populate some tables using some large CSVs (400k+ records) but parsing the files and importing each line was out of the question and doing one mass insert would have used up a lot of memory.

Create Seeder File "PostalCodeTableSeeder.php" in Project_directory/database/seeds

use Illuminate\Database\Seeder;

class PostalCodeTableSeeder extends Seeder {
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        // =============================================================
        // file Path -> Project/app/configs/database.php
        // get the database name, database username, database password
        // =============================================================
        $db     = \Config::get('database.connections.mysql.database');
        $user   = \Config::get('database.connections.mysql.username');
        $pass   = \Config::get('database.connections.mysql.password');

        // $this->command->info($db);
        // $this->command->info($user);
        // $this->command->info($pass);

        // running command line import in php code
        exec("mysql -u " . $user . " -p" . $pass . " " . $db . " < postal_codes.sql");
        // postal_codes.sql is inside root folder
    }
}

Also add the class name into Project_directory/database/seed/DatabaseSeeder.php like code below

use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $this->call(PostalCodeTableSeeder::class);
        // $this->call(UsersTableSeeder::class);
    }
}

Is there a way to storage a .sql file when seeding database in Laravel?, So that when I enter php artisan migrate:fresh --seed command it would first erase all data then populate cities table from .sql file and then finish  Normally we use db:seed in Laravel by faker data. However, today, I will show you how to migrate SQL dump file in Laravel DB Seeder. Step 1. Run migration command-php artisan migrate Step 2. Download your dump file and paste into database/seeds/source folder. - database -- seeds --- source ---- dump_file Step 3

Laravel Importing SQL dumps with seeds, Seeding with Laravel is really useful for populating the database for //path to sql file $sql = base_path('dump.sql'); //collect contents and pass  Now, is there a way for me to automatically populate cities table without having to manually add that query. So that when I enter php artisan migrate:fresh --seed command it would first erase all data then populate cities table from .sql file and then finish seeding other tables? Any help is appreciated. Here is my seeder currently. UserSeeder.php

Laravel. seeding large sql file, Laravel. seeding large sql file. Question. A memory exhaust happens when I run my db seed script in production. Below is the my seed script. File Visibility. In Laravel's Flysystem integration, "visibility" is an abstraction of file permissions across multiple platforms. Files may either be declared public or private. When a file is declared public, you are indicating that the file should generally be accessible to

Database: Seeding - Laravel, All seed classes are stored in the database/seeds directory. Seed seeding into multiple files so that no single seeder class becomes overwhelmingly large. Laravel includes an easy method of seeding your database with test data using seed classes. All seed classes are stored under the database/seeds directory. Seed classes may have some name you wish but probably should follow any sensible convention, such as UsersTableSeeder, I temsTableSeeder etc. Default, a DatabaseSeeder class is defined for you.

Comments
  • How many rows it contains?
  • using the mysql binary is probably the best way to go about it. The alternative parse and split your SQL into smaller statements and run multiple queries.
  • It's just 16MB sql file.
  • Do you run php in safe mode?
  • Just a suggestion about the first part: Member::create in a loop - don't do it, instead use DB::table(..)->insert(..) and pass there array of rows to insert. It will be much faster and less memory consuming.
  • I would give it a try for a more Laravel-ish solutinon, but if your file is really big (a common case) this would not help
  • thanks for the tip! I turned it off cause my DB dump was too large.
  • How about multiple .sql?
  • I find this approach very memory exhausting, the script parses and iterates the SQL file, what if you have a file over 5000 lines? Although this is just my opinion.
  • Yes, running sql scripts from php is very memory exhausting. I strongly advise not to use a database seeder to import production data. It is only for test/development data.
  • A couple of thousand lines will be fine, nothing major. Anything more than that though and I'd look at seeding it yourself from mysql, or better yet just execute a mysql loadfile from laravel by running a command against the console.
  • add some explanation
  • ` exec("mysql -u " . $user . " -p" . $pass . " " . $db . " < postal_codes.sql");` How about multiple .sql?