Results 1 to 7 of 7

Thread: monitor progress of MariaDB dump file import

  1. #1
    Join Date
    Apr 2016
    Posts
    17
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default monitor progress of MariaDB dump file import

    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.

  2. #2
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,229
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: monitor progress of MariaDB dump file import

    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.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  3. #3
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,536
    Thanked 284 Times in 279 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: monitor progress of MariaDB dump file import

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

  4. #4
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    12
    Thanks
    1
    Platforms
    Unix/X11

    Default Re: monitor progress of MariaDB dump file import

    Or use QtProcess and mysql --progress-reports. QtProcess emits signals, 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

  5. #5
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,229
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: monitor progress of MariaDB dump file import

    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.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  6. #6
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    12
    Thanks
    1
    Platforms
    Unix/X11

    Default Re: monitor progress of MariaDB dump file import

    Quote Originally Posted by d_stranz View Post
    Probably not since you can't tell in advance how much output will be created, ...
    The db engine and client library has methods 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 of MySQL mysql. Is seems that it don't use the process progress support But mysql from MariaDB does
    Last edited by smyk; 25th February 2022 at 14:45.

  7. #7
    Join Date
    Apr 2016
    Posts
    17
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: monitor progress of MariaDB dump file import

    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-r...orted-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.

    Qt Code:
    1. SqlDumpImportProcess::SqlDumpImportProcess() : QProcess()
    2. {
    3. }
    4.  
    5. void SqlDumpImportProcess::importFile(QString file_name, QString database_name)
    6. {
    7. setStandardInputFile(file_name);
    8. connect(this, SIGNAL(readyReadStandardOutput()), this, SLOT(slot_read_std_out()));
    9. QString process_command("mysql -u tempuser --verbose --password=temppassword --force ");
    10. process_command.append(database_name);
    11. start(process_command);
    12. waitForFinished(-1); // -1 means don't time out
    13. }
    14.  
    15. void SqlDumpImportProcess::slot_read_std_out()
    16. {
    17. printEntry;
    18. QByteArray byte_array = readAllStandardOutput();
    19. QString out_string(byte_array);
    20. out_string.simplified();
    21. int create_index = out_string.indexOf("CREATE TABLE", 0, Qt::CaseInsensitive);
    22. if (create_index >= 0)
    23. {
    24. QString insert_substring = out_string.mid(create_index);
    25. QString table_name = insert_substring.section(" ", 2, 2); // 0 = "CREATE", 1 = "TABLE", 2 = table name
    26. table_name.remove("`");
    27. emit signal_create_table(table_name);
    28. }
    29. }
    To copy to clipboard, switch view to plain text mode 
    Last edited by d_stranz; 26th August 2022 at 00:05. Reason: missing [code] tags

  8. The following user says thank you to sunburns for this useful post:

    d_stranz (26th August 2022)

Similar Threads

  1. Replies: 2
    Last Post: 29th April 2014, 10:14
  2. How to import qss into another qss file
    By bedbuffer in forum Newbie
    Replies: 1
    Last Post: 4th April 2011, 12:22
  3. how to import a file into qt when double clicking a file.
    By berlinud in forum Qt Programming
    Replies: 14
    Last Post: 16th September 2010, 17:13
  4. Monitor a new added File
    By designer.software in forum Qt Programming
    Replies: 1
    Last Post: 27th February 2010, 10:50
  5. Replies: 0
    Last Post: 6th March 2009, 09:19

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.