What to do if a database in an SQL file with a million 155 thousand rows cannot be inserted using tools?

If standard tools (phpMyAdmin, Adminer, terminal utilities like mysql) cannot handle importing an SQL file of such size, there are several main strategies:

1. Using terminal utilities:

   • The most reliable method for large files.
   • Directly invoke the import command, bypassing the web server and PHP limitations.

   # For MySQL
   mysql -u your_user -p your_database < your_file.sql
   
   # For PostgreSQL
   psql -U your_user -d your_database -f your_file.sql
   

2. Splitting the file into parts:

   • A very large file may exceed memory or execution time limits.
   • The file can be split manually, with scripts, or specialized tools.
   • Import each part sequentially.

3. Configuring the web server and PHP:

   • Increase the following parameters in php.ini (for phpMyAdmin/Adminer):
     • upload_max_filesize
     • post_max_size
     • memory_limit
     • max_execution_time
     • max_input_time
   • Increase client_max_body_size in Nginx configuration or LimitRequestBody in Apache.

4. Optimizing the SQL file:

   • Remove unnecessary comments or empty lines.
   • Change the format of INSERT statements from multiple rows to a single INSERT with multiple value sets (if the file format allows and the database supports it), for example:

     -- Instead of:
     -- INSERT INTO table (col1, col2) VALUES (1, 'a');
     -- INSERT INTO table (col1, col2) VALUES (2, 'b');
     
     -- Use:
     -- INSERT INTO table (col1, col2) VALUES
     -- (1, 'a'),
     -- (2, 'b');
     

5. Using specialized tools:

   • There are utilities designed specifically for processing large SQL dumps, such as mydumper/myloader for MySQL.

6. Server-side import:

   • If you have access to the database server's filesystem, you can use commands like LOAD DATA INFILE (MySQL) or COPY (PostgreSQL), but this requires changing the file format to CSV or TAB-separated.

   -- Example for MySQL with LOAD DATA INFILE (requires CSV or other delimiter format)
   -- LOAD DATA INFILE '/path/to/your/file.csv'
   -- INTO TABLE your_table
   -- FIELDS TERMINATED BY ',' ENCLOSED BY '"'
   -- LINES TERMINATED BY '\n';
   

The choice of method depends on available resources (direct server access, configuration change capabilities) and technical expertise. Generally, using terminal utilities or splitting the file are the most reliable solutions for large data volumes.