monitor progress of MariaDB dump file import

[sunburns]

I am importing a very large SQL dump file. The file creates tables (about 50) and inserts values into the tables. I do this by running a system command in my program:

system ("mysql -u theuser --password=thepassword --force thedatabase < thedumpfile");

It takes up to 30 minutes to complete. I'd like to give the user an indication of what's going on, since it takes so long. I'm looking for ideas on the best way to do this.

I know the table names before I begin the import. I'm thinking I could display a list of all the table names, maybe colored in red. Then, as each insert statement is complete, I could color the table name green. But what's the best way to do this? I could parse the dump file, doing each "create table" and "insert into" statement individually, but I think that's a bad idea. This could introduce errors in the import of the file (which would be catastrophic) for the sake of the GUI (which is simply nice-to-have).

Perhaps a separate thread (or even a program) that checks the database periodically to see if a table exists. If it does, I know the previous table (they're alphabetical in the dump file) must be complete. But I hesitate to ping on the database while it's being written to. Is there a safe way to do this?

Thanks in advance for something clever. I get extra points for a snazzy GUI.

[d_stranz]

The MySQL server is multithreaded and designed for simultaneous multi-client access, so I doubt that a simple query by a read-only client would cause problems while building the DB. Since I assume you are not doing this on a live DB, so why not just test it out as you proposed? I also assume you have some way of testing the DB integrity and completeness once the import has finished, so you can verify that the test queries caused no problems.

[Lesiok]

And the --verbose option, and showing the output from mysql ?

[smyk]

Or use QtProcess and mysql --progress-reports. QtProcess emits signals (https://doc.qt.io/qt-5/qprocess.html#readyReadStandardOutput), when mysql write something to stdout. I don't know, if mysql --progress-repors produces usable output to build a progress indicator on it, i hope it does

[d_stranz]

produces usable output to build a progress indicator on it

Probably not since you can't tell in advance how much output will be created, but you could stream that output to a text window to give the user something to watch while the DB is being built.

Since the DB dump is just an ASCII file, you could scan it in advance and count the number of certain key commands (like CREATE TABLE) and use that to set the upper limit for a progress indicator. If the MySQL progress report shows the command it is executing, then you could look for the same command to appear in stdout and bump the progress indicator each time.

[smyk]

Probably not since you can't tell in advance how much output will be created, ...
The db engine and client library has methods (https://mariadb.com/kb/en/progress-reporting/#how-to-add-support-for-progress-peporting-to-a-mysql-client) for watching progress of each db process. I didn't check the sources of mysql or mysqlbackup, but i suspect they support this feature.
The other possibility would be the direct use of MySQL client API. Only it would be much more complex to implement ...

Edit:

I checked the source code (https://github.com/mysql/mysql-server/blob/8.0/client/mysql.cc) of MySQL mysql. Is seems that it don't use the process progress support :( But mysql from MariaDB (https://github.com/MariaDB/server/blob/10.9/client/mysql.cc) does :)

[sunburns]

Thanks for the suggestions. Sorry for the slow response - blame Covid.

The progress monitoring for MaridDB is pretty limited; only a few commands are supported:

https://mariadb.com/kb/en/progress-reporting/#supported-commands

I ended up using the --verbose option and writing a slot for the readyReadStandardOutput() signal. This slot then emits a signal when the output has "create table" in it.


SqlDumpImportProcess::SqlDumpImportProcess() : QProcess()
{
}

void SqlDumpImportProcess::importFile(QString file_name, QString database_name)
{
setStandardInputFile(file_name);
connect(this, SIGNAL(readyReadStandardOutput()), this, SLOT(slot_read_std_out()));
QString process_command("mysql -u tempuser --verbose --password=temppassword --force ");
process_command.append(database_name);
start(process_command);
waitForFinished(-1); // -1 means don't time out
}

void SqlDumpImportProcess::slot_read_std_out()
{
printEntry;
QByteArray byte_array = readAllStandardOutput();
QString out_string(byte_array);
out_string.simplified();
int create_index = out_string.indexOf("CREATE TABLE", 0, Qt::CaseInsensitive);
if (create_index >= 0)
{
QString insert_substring = out_string.mid(create_index);
QString table_name = insert_substring.section(" ", 2, 2); // 0 = "CREATE", 1 = "TABLE", 2 = table name
table_name.remove("`");
emit signal_create_table(table_name);
}
}