GoAdmio

import

Import a SQL dump into an existing MySQL/MariaDB database, pre-creating tables from the dump's INSERT statements

On this page: Overview · Quick start · Syntax · Arguments · Output · Examples · Troubleshooting · Details · See also


Overview

/go/import loads a .sql dump file into a database that already exists. It:

[!WARNING] Errors are hidden. The import continues past failed statements and discards MariaDB's error output, and the command prints no summary. A run that looks successful can leave tables empty or partly filled. Always check the result afterwards (see Examples).


Quick start

# 1. Create the empty database
mariadb -e "CREATE DATABASE example_db"

# 2. Import the dump
/go/import /root/backup.sql example_db

Syntax

/go/import <file> <database>

Arguments

Position Argument Required Notes
1 file Yes Path to a plain-text .sql dump. Compressed files (.gz, .zip) are not supported.
2 database Yes The name of an existing database. It is not created for you.

Neither argument is checked. A wrong path or database name does not produce an error message.

Dump requirements

What the command does depends on what the dump contains.

Dump contains Result
DROP TABLE and CREATE TABLE (the mysqldump default) Works like a normal import. The dump's own table definitions replace the placeholders.
INSERT statements with column names, and no CREATE TABLE The intended use. Placeholder tables are built from the column names.
INSERT statements without column names, and no CREATE TABLE Fails silently. The placeholder table has one column, which does not fit the rows.
CREATE TABLE without a matching DROP TABLE (or CREATE TABLE IF NOT EXISTS) The placeholder table is kept and the dump's definition is skipped, so the table keeps the placeholder structure.

To get column names in INSERT statements, export with mysqldump --complete-insert.

Placeholder tables

Part What is created
First column INT AUTO_INCREMENT PRIMARY KEY
Every other column TEXT

Original data types, lengths, defaults, indexes, and keys are not rebuilt. Strings, dates, and decimals end up as text, and a first column that is not an integer key does not fit.


Output

Stage What you see
Table check One line that updates in place: [<number>/<total>] Checking Table <table>
Import A pv progress bar with size, time, speed, and percentage
Finish Nothing. There is no summary and no error report.

Examples

# Create the database, then import into it
mariadb -e "CREATE DATABASE example_db"
/go/import /root/backup.sql example_db
# Check the result: list databases and sizes
/go/dbls
# Check the result: count rows in one table
mariadb -e "SELECT COUNT(*) FROM example_db.wp_posts"
# See the real errors by importing by hand (not silenced)
mariadb --force example_db < /root/backup.sql
# Decompress first if the dump is compressed
gunzip /root/backup.sql.gz
/go/import /root/backup.sql example_db

Troubleshooting

Symptom Likely cause Fix
Nothing imported and no message The database does not exist. Create it with mariadb -e "CREATE DATABASE <name>", then run again.
pv: command not found pv is not installed. Install pv with the server's package manager.
Tables are empty or missing rows Statements failed and the errors were hidden. Import by hand without silencing errors (see Examples) to read them.
Every column is TEXT, no indexes The dump had no CREATE TABLE, or no DROP TABLE before it, so the placeholder was kept. Use a dump that has DROP TABLE IF EXISTS and CREATE TABLE, or alter the tables yourself.
Tables with a single odd column, no data The dump's INSERT statements have no column names. Export again with mysqldump --complete-insert.
Some columns are missing The name is a reserved word (such as key or order) or contains special characters. Names are used without quoting. Rename the column in the dump, or create the table by hand.
Existing tables disappeared The dump contains DROP TABLE IF EXISTS. Import into an empty database, and keep a backup of anything you need.
Old and new rows are mixed, or rows were skipped The tables already held data, and duplicate keys were ignored. Import into an empty database.
Gibberish or nothing from a .gz file Compressed dumps are read as plain text. Decompress first.
No progress for a long time at the start The whole file is scanned for tables before the bar appears. Wait. This takes longer for large dumps.
Cannot read the file The file is not readable by the user running the command, or the path contains spaces. Run as root, and avoid spaces in the path.

Details

What it does

flowchart TD
    A["/go/import file database"] --> B["Append the command to /var/log/go/import.log"]
    B --> C["Read the MariaDB root password from config.php"]
    C --> D["Scan INSERT statements for table and column names"]
    D --> E["Save the list to tmp/database.db"]
    E --> F["For each table: create it, then add each column as TEXT"]
    F --> G["Stream the dump through pv into mariadb --force"]

Steps in order

  1. Appends a timestamped line with the arguments to /var/log/go/import.log.
  2. Reads the MariaDB root password from /usr/local/goadmio/include/config/config.php.
  3. Scans the dump for the text between INSERT and VALUES and turns it into one line per table: table|column1|column2|....
  4. Saves those lines in /usr/local/goadmio/tmp/<database>.db and counts them.
  5. For each line:
    1. creates the table with the first column as INT AUTO_INCREMENT PRIMARY KEY
    2. adds every column as TEXT
    3. ignores errors, so tables and columns that already exist are skipped
  6. Sends the dump through pv into mariadb --force, with errors hidden.

Notes


See also

Command Description
sql Create a MySQL database and import an SQL file into it
table Import a single table from a dump file in MySQL/MariaDB
mysql Create a MySQL database
dbls List all databases and their sizes
repair Repair a MySQL database
copydb Copy a MySQL/MariaDB database from one server to another
csv Export a MySQL/MariaDB table to a CSV file
mysqlbackup Back up MariaDB/MySQL to a remote server via rsync