Bug 286503 - MySQL error while importing large QIF file.
Summary: MySQL error while importing large QIF file.
Status: RESOLVED FIXED
Alias: None
Product: kmymoney
Classification: Applications
Component: general (show other bugs)
Version: 4.6.1
Platform: Gentoo Packages Linux
: NOR crash
Target Milestone: ---
Assignee: KMyMoney Devel Mailing List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-11-13 16:15 UTC by Mac Michaels
Modified: 2012-03-03 16:14 UTC (History)
2 users (show)

See Also:
Latest Commit:
Version Fixed In:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Mac Michaels 2011-11-13 16:15:28 UTC
Version:           4.6.1 (using KDE 4.6.5) 
OS:                Linux

  After running 2 days importing 15 years of Quicken data in QIF file I got a error message that I failed to capture the text of. As I recall it was about a missing security definition.

  I clicked OK and got the following error message:

Problem adding or matching imported transaction with id '2001-07-11-e26e2d3-1': Error in function const QMap<QString, MyMoneyTransaction> MyMoneyStorageSql::fetchTransactions(const QString&, const QString&, bool) const : reading Transaction
Driver = QMYSQL, Host = drum, User = wmichaels1, Database = KMyMoney
Driver Error: QMYSQL: Unable to rollback transaction
Database Error No 2006: MySQL server has gone away
Text: MySQL server has gone away QMYSQL: Unable to rollback transaction
Error type 2
Executed: SELECT id, txType, postDate, memo, entryDate, currencyId, bankId FROM kmmTransactions WHERE txType = 'N'  ORDER BY id;
Query error No 2006: MySQL server has gone away QMYSQL: Unable to execute query
Error type 2

  I clicked continue and got this message:

Problem adding or matching imported transaction with id '2001-07-25-60a04f1-1': Error in \
function const QMap<QString, MyMoneyTransaction> MyMoneyStorageSql::fetchTransactions(con\
st QString&, const QString&, bool) const : reading Transaction
Driver = QMYSQL, Host = drum, User = wmichaels1, Database = KMyMoney
Driver Error: QMYSQL: Unable to rollback transaction
Database Error No 2006: MySQL server has gone away
Text: MySQL server has gone away QMYSQL: Unable to rollback transaction
Error type 2
Executed: SELECT id, txType, postDate, memo, entryDate, currencyId, bankId FROM kmmTransa\
ctions WHERE txType = 'N'  ORDER BY id;
Query error No 2006: MySQL server has gone away QMYSQL: Unable to execute query
Error type 2

  I clicked continue and got the above sequence of 3 error message boxes several times.

  I clicked cancel and got the following error box:

Uncaught error. Please report the details to the developers
details:
Empty commit unit stack while trying to commit in file /vault2/distfiles/tmp/portage/app-office/kmymoney-4.6.1/work/kmymoney-4.6.1/kmymoney/mymoney/storage/mymoneystoragesql.cpp line 885

  The application closed and crash bug report screen came up.  Here is the generated crash data:

Application: KMyMoney (kmymoney), signal: Aborted
[Current thread is 1 (Thread 0x7f26c7df0760 (LWP 7858))]

Thread 2 (Thread 0x7f26b08bb700 (LWP 7873)):
#0  0x00007f26c25c6a53 in poll () from /lib64/libc.so.6
#1  0x00007f26bd66d4bd in g_main_context_iterate.clone.6 () from /usr/lib64/libglib-2.0.so.0
#2  0x00007f26bd66d9ed in g_main_context_iteration () from /usr/lib64/libglib-2.0.so.0
#3  0x00007f26c4089046 in QEventDispatcherGlib::processEvents(QFlags<QEventLoop::ProcessEventsFlag>) () from /usr/lib64/qt4/libQtCore.so.4
#4  0x00007f26c405d482 in QEventLoop::processEvents(QFlags<QEventLoop::ProcessEventsFlag>) () from /usr/lib64/qt4/libQtCore.so.4
#5  0x00007f26c405d6c4 in QEventLoop::exec(QFlags<QEventLoop::ProcessEventsFlag>) () from /usr/lib64/qt4/libQtCore.so.4
#6  0x00007f26c3f736b4 in QThread::exec() () from /usr/lib64/qt4/libQtCore.so.4
#7  0x00007f26c403f038 in QInotifyFileSystemWatcherEngine::run() () from /usr/lib64/qt4/libQtCore.so.4
#8  0x00007f26c3f75f05 in QThreadPrivate::start(void*) () from /usr/lib64/qt4/libQtCore.so.4
#9  0x00007f26c51b1c5c in start_thread () from /lib64/libpthread.so.0
#10 0x00007f26c25cf67d in clone () from /lib64/libc.so.6

Thread 1 (Thread 0x7f26c7df0760 (LWP 7858)):
[KCrash Handler]
#6  0x00007f26c2530835 in raise () from /lib64/libc.so.6
#7  0x00007f26c2531b35 in abort () from /lib64/libc.so.6
#8  0x00007f26c2dbbadd in __gnu_cxx::__verbose_terminate_handler() () from /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
#9  0x00007f26c2db9d16 in __cxxabiv1::__terminate(void (*)()) () from /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
#10 0x00007f26c2db9d43 in std::terminate() () from /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
#11 0x00007f26c2db9e4e in __cxa_throw () from /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
#12 0x000000000045c759 in main ()

  I canceled the bug reporting wizard.

  I restarted application manually and the KMyMoney database login reported that the database was in use, restart anyway.  The application took much longer to start than quicken with the same data on a much slower computer.  None of the stock accounts had any shares of stock in them.  There were entries in the checking accounts.  Response when switching accounts is very slow.

Reproducible: Didn't try

Steps to Reproduce:
Select MySQL as the database in KMyMoney.
Import a vary large QIF file (8,655,192 bytes)
Let it run 2 or 3 days on an Intel Core2 system.

Actual Results:  
Got error messages described above.

Expected Results:  
Complete with all QIF data properly imported.

OS: Linux (x86_64) release 3.0.6-gentoo
Compiler: x86_64-pc-linux-gnu-gcc
Comment 1 Tony B 2011-11-25 09:30:24 UTC
Sorry for the delay in replying, but I have been away.

The critical message here would seem to be "MySQL server has gone away".

The following is an excerpt from the MySQL reference manual explanation of this error:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection.

By default, the server closes the connection after eight hours if nothing has happened.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Given the length of time your run is taking, could this perhaps be the reason? I'm afraid I don't know enough about the QIF importer to tell. Perhaps one of the other developers could analyze the backtrace and see what's happening.

There are a number of other possible causes of this error listed in the manual, but without further information (e.g. the MySQL logs for the period) it's not possible to be specific.
Comment 2 allan 2011-11-25 11:03:08 UTC
(In reply to comment #0)
> Version:           4.6.1 (using KDE 4.6.5) 
> OS:                Linux
> 
>   After running 2 days importing 15 years of Quicken data in QIF file I got a
> error message that I failed to capture the text of. As I recall it was about a
> missing security definition.
> 
>   I clicked OK and got the following error message:
> 
> Problem adding or matching imported transaction with id '2001-07-11-e26e2d3-1':
> Error in function const QMap<QString, MyMoneyTransaction>
> MyMoneyStorageSql::fetchTransactions(const QString&, const QString&, bool)
> const : reading Transaction
> Driver = QMYSQL, Host = drum, User = wmichaels1, Database = KMyMoney
> Driver Error: QMYSQL: Unable to rollback transaction
> Database Error No 2006: MySQL server has gone away
> Text: MySQL server has gone away QMYSQL: Unable to rollback transaction
> Error type 2
> Executed: SELECT id, txType, postDate, memo, entryDate, currencyId, bankId FROM
> kmmTransactions WHERE txType = 'N'  ORDER BY id;
> Query error No 2006: MySQL server has gone away QMYSQL: Unable to execute query
> Error type 2
> 
>   I clicked continue and got this message:
> 
> Problem adding or matching imported transaction with id '2001-07-25-60a04f1-1':
> Error in \
> function const QMap<QString, MyMoneyTransaction>
> MyMoneyStorageSql::fetchTransactions(con\
> st QString&, const QString&, bool) const : reading Transaction
> Driver = QMYSQL, Host = drum, User = wmichaels1, Database = KMyMoney
> Driver Error: QMYSQL: Unable to rollback transaction
> Database Error No 2006: MySQL server has gone away
> Text: MySQL server has gone away QMYSQL: Unable to rollback transaction
> Error type 2
> Executed: SELECT id, txType, postDate, memo, entryDate, currencyId, bankId FROM
> kmmTransa\
> ctions WHERE txType = 'N'  ORDER BY id;
> Query error No 2006: MySQL server has gone away QMYSQL: Unable to execute query
> Error type 2
> 
>   I clicked continue and got the above sequence of 3 error message boxes
> several times.
> 
>   I clicked cancel and got the following error box:
> 
> Uncaught error. Please report the details to the developers
> details:
> Empty commit unit stack while trying to commit in file
> /vault2/distfiles/tmp/portage/app-office/kmymoney-4.6.1/work/kmymoney-4.6.1/kmymoney/mymoney/storage/mymoneystoragesql.cpp
> line 885
> 
>   The application closed and crash bug report screen came up.  Here is the
> generated crash data:
> 
> Application: KMyMoney (kmymoney), signal: Aborted
> [Current thread is 1 (Thread 0x7f26c7df0760 (LWP 7858))]
> 
> Thread 2 (Thread 0x7f26b08bb700 (LWP 7873)):
> #0  0x00007f26c25c6a53 in poll () from /lib64/libc.so.6
> #1  0x00007f26bd66d4bd in g_main_context_iterate.clone.6 () from
> /usr/lib64/libglib-2.0.so.0
> #2  0x00007f26bd66d9ed in g_main_context_iteration () from
> /usr/lib64/libglib-2.0.so.0
> #3  0x00007f26c4089046 in
> QEventDispatcherGlib::processEvents(QFlags<QEventLoop::ProcessEventsFlag>) ()
> from /usr/lib64/qt4/libQtCore.so.4
> #4  0x00007f26c405d482 in
> QEventLoop::processEvents(QFlags<QEventLoop::ProcessEventsFlag>) () from
> /usr/lib64/qt4/libQtCore.so.4
> #5  0x00007f26c405d6c4 in
> QEventLoop::exec(QFlags<QEventLoop::ProcessEventsFlag>) () from
> /usr/lib64/qt4/libQtCore.so.4
> #6  0x00007f26c3f736b4 in QThread::exec() () from /usr/lib64/qt4/libQtCore.so.4
> #7  0x00007f26c403f038 in QInotifyFileSystemWatcherEngine::run() () from
> /usr/lib64/qt4/libQtCore.so.4
> #8  0x00007f26c3f75f05 in QThreadPrivate::start(void*) () from
> /usr/lib64/qt4/libQtCore.so.4
> #9  0x00007f26c51b1c5c in start_thread () from /lib64/libpthread.so.0
> #10 0x00007f26c25cf67d in clone () from /lib64/libc.so.6
> 
> Thread 1 (Thread 0x7f26c7df0760 (LWP 7858)):
> [KCrash Handler]
> #6  0x00007f26c2530835 in raise () from /lib64/libc.so.6
> #7  0x00007f26c2531b35 in abort () from /lib64/libc.so.6
> #8  0x00007f26c2dbbadd in __gnu_cxx::__verbose_terminate_handler() () from
> /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
> #9  0x00007f26c2db9d16 in __cxxabiv1::__terminate(void (*)()) () from
> /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
> #10 0x00007f26c2db9d43 in std::terminate() () from
> /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
> #11 0x00007f26c2db9e4e in __cxa_throw () from
> /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
> #12 0x000000000045c759 in main ()
> 
>   I canceled the bug reporting wizard.
> 
>   I restarted application manually and the KMyMoney database login reported
> that the database was in use, restart anyway.  The application took much longer
> to start than quicken with the same data on a much slower computer.  None of
> the stock accounts had any shares of stock in them.  There were entries in the
> checking accounts.  Response when switching accounts is very slow.
> 
> Reproducible: Didn't try
> 
> Steps to Reproduce:
> Select MySQL as the database in KMyMoney.
> Import a vary large QIF file (8,655,192 bytes)
> Let it run 2 or 3 days on an Intel Core2 system.
> 
> Actual Results:  
> Got error messages described above.
> 
> Expected Results:  
> Complete with all QIF data properly imported.
> 
> OS: Linux (x86_64) release 3.0.6-gentoo
> Compiler: x86_64-pc-linux-gnu-gcc

(In reply to comment #0)
> Version:           4.6.1 (using KDE 4.6.5) 
> OS:                Linux
> 
>   After running 2 days importing 15 years of Quicken data in QIF file I got a
> error message that I failed to capture the text of. As I recall it was about a
> missing security definition.
> 
>   I clicked OK and got the following error message:
> 
> Problem adding or matching imported transaction with id '2001-07-11-e26e2d3-1':
> Error in function const QMap<QString, MyMoneyTransaction>
> MyMoneyStorageSql::fetchTransactions(const QString&, const QString&, bool)
> const : reading Transaction
> Driver = QMYSQL, Host = drum, User = wmichaels1, Database = KMyMoney
> Driver Error: QMYSQL: Unable to rollback transaction
> Database Error No 2006: MySQL server has gone away
> Text: MySQL server has gone away QMYSQL: Unable to rollback transaction
> Error type 2
> Executed: SELECT id, txType, postDate, memo, entryDate, currencyId, bankId FROM
> kmmTransactions WHERE txType = 'N'  ORDER BY id;
> Query error No 2006: MySQL server has gone away QMYSQL: Unable to execute query
> Error type 2
> 
>   I clicked continue and got this message:
> 
> Problem adding or matching imported transaction with id '2001-07-25-60a04f1-1':
> Error in \
> function const QMap<QString, MyMoneyTransaction>
> MyMoneyStorageSql::fetchTransactions(con\
> st QString&, const QString&, bool) const : reading Transaction
> Driver = QMYSQL, Host = drum, User = wmichaels1, Database = KMyMoney
> Driver Error: QMYSQL: Unable to rollback transaction
> Database Error No 2006: MySQL server has gone away
> Text: MySQL server has gone away QMYSQL: Unable to rollback transaction
> Error type 2
> Executed: SELECT id, txType, postDate, memo, entryDate, currencyId, bankId FROM
> kmmTransa\
> ctions WHERE txType = 'N'  ORDER BY id;
> Query error No 2006: MySQL server has gone away QMYSQL: Unable to execute query
> Error type 2
> 
>   I clicked continue and got the above sequence of 3 error message boxes
> several times.
> 
>   I clicked cancel and got the following error box:
> 
> Uncaught error. Please report the details to the developers
> details:
> Empty commit unit stack while trying to commit in file
> /vault2/distfiles/tmp/portage/app-office/kmymoney-4.6.1/work/kmymoney-4.6.1/kmymoney/mymoney/storage/mymoneystoragesql.cpp
> line 885
> 
>   The application closed and crash bug report screen came up.  Here is the
> generated crash data:
> 
> Application: KMyMoney (kmymoney), signal: Aborted
> [Current thread is 1 (Thread 0x7f26c7df0760 (LWP 7858))]
> 
> Thread 2 (Thread 0x7f26b08bb700 (LWP 7873)):
> #0  0x00007f26c25c6a53 in poll () from /lib64/libc.so.6
> #1  0x00007f26bd66d4bd in g_main_context_iterate.clone.6 () from
> /usr/lib64/libglib-2.0.so.0
> #2  0x00007f26bd66d9ed in g_main_context_iteration () from
> /usr/lib64/libglib-2.0.so.0
> #3  0x00007f26c4089046 in
> QEventDispatcherGlib::processEvents(QFlags<QEventLoop::ProcessEventsFlag>) ()
> from /usr/lib64/qt4/libQtCore.so.4
> #4  0x00007f26c405d482 in
> QEventLoop::processEvents(QFlags<QEventLoop::ProcessEventsFlag>) () from
> /usr/lib64/qt4/libQtCore.so.4
> #5  0x00007f26c405d6c4 in
> QEventLoop::exec(QFlags<QEventLoop::ProcessEventsFlag>) () from
> /usr/lib64/qt4/libQtCore.so.4
> #6  0x00007f26c3f736b4 in QThread::exec() () from /usr/lib64/qt4/libQtCore.so.4
> #7  0x00007f26c403f038 in QInotifyFileSystemWatcherEngine::run() () from
> /usr/lib64/qt4/libQtCore.so.4
> #8  0x00007f26c3f75f05 in QThreadPrivate::start(void*) () from
> /usr/lib64/qt4/libQtCore.so.4
> #9  0x00007f26c51b1c5c in start_thread () from /lib64/libpthread.so.0
> #10 0x00007f26c25cf67d in clone () from /lib64/libc.so.6
> 
> Thread 1 (Thread 0x7f26c7df0760 (LWP 7858)):
> [KCrash Handler]
> #6  0x00007f26c2530835 in raise () from /lib64/libc.so.6
> #7  0x00007f26c2531b35 in abort () from /lib64/libc.so.6
> #8  0x00007f26c2dbbadd in __gnu_cxx::__verbose_terminate_handler() () from
> /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
> #9  0x00007f26c2db9d16 in __cxxabiv1::__terminate(void (*)()) () from
> /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
> #10 0x00007f26c2db9d43 in std::terminate() () from
> /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
> #11 0x00007f26c2db9e4e in __cxa_throw () from
> /usr/lib/gcc/x86_64-pc-linux-gnu/4.5.3/libstdc++.so.6
> #12 0x000000000045c759 in main ()
> 
>   I canceled the bug reporting wizard.
> 
>   I restarted application manually and the KMyMoney database login reported
> that the database was in use, restart anyway.  The application took much longer
> to start than quicken with the same data on a much slower computer.  None of
> the stock accounts had any shares of stock in them.  There were entries in the
> checking accounts.  Response when switching accounts is very slow.
> 
> Reproducible: Didn't try
> 
> Steps to Reproduce:
> Select MySQL as the database in KMyMoney.
> Import a vary large QIF file (8,655,192 bytes)
> Let it run 2 or 3 days on an Intel Core2 system.
> 
> Actual Results:  
> Got error messages described above.
> 
> Expected Results:  
> Complete with all QIF data properly imported.
> 
> OS: Linux (x86_64) release 3.0.6-gentoo
> Compiler: x86_64-pc-linux-gnu-gcc

I don't use the database very often, so can't speak from experience, but I remember that there have been comments about it being a bit sluggish in certain areas.

So, what I'm wondering is whether you have tried removing it from the equation by just having your file open as a standard .kmy file and then doing the import?
You could later save to database.

If you are able to load your file that way, could you supply the information from the menu item File>>File-information.  If you can't get a .kmy file, the only suggestion I can think of at the moment is that the input file could perhaps be split into sections, either manually or by exporting in sections by date.
Comment 3 Mac Michaels 2011-11-25 13:11:21 UTC
I agree that the likely problem is the MySQL server dropping the connection.  A work around might be to extend the socket timeout in the MySQL server.  A better fix consists of trying to reestablish the connection to the database again.  Appropriate retry count and time between retries parameters are added to the KMyMoney configuration file.  Other applications that use MySQL reconnect because the database may be located in another machine on the network and the network may have been interrupted.  This should be a general recovery strategy for KMyMoney as several users on different machines may use the same KMyMoney database. 


Another bug that is bothering me is the uncaught error when I canceled the import after I realized that MySQL had a problem. It should not crash the application, just abort and quit trying to rollback the database.

>....repeated error messages [see original post]
> 
>   I clicked continue and got the above sequence of 3 error message boxes
> several times.
> 
>   I clicked cancel and got the following error box:
> 
> Uncaught error. Please report the details to the developers
> details:
> Empty commit unit stack while trying to commit in file
> /vault2/distfiles/tmp/portage/app-office/kmymoney-4.6.1/work/kmymoney-4.6.1/kmymoney/mymoney/storage/mymoneystoragesql.cpp
> line 885
Comment 4 Fernando Vilas 2012-03-03 16:03:25 UTC
Git commit 4da92dfa03209670bddb7801926a25741c0ad6ec by Fernando Vilas.
Committed on 03/03/2012 at 16:58.
Pushed by fvilas into branch 'master'.
Related: bug 294046

Attempt to reconnect to the database (once) on accidental disconnects. If this still throws, we need to notify the user and reset the UI to the same state as "kmymoney -n".

Add a test case to reproduce the problem in the storage layer.

M  +53   -9    kmymoney/mymoney/storage/mymoneydatabasemgr.cpp
M  +16   -0    kmymoney/mymoney/storage/mymoneydatabasemgrtest.cpp
M  +1    -0    kmymoney/mymoney/storage/mymoneydatabasemgrtest.h

http://commits.kde.org/kmymoney/4da92dfa03209670bddb7801926a25741c0ad6ec
Comment 5 Fernando Vilas 2012-03-03 16:14:18 UTC
Git commit bbc4a66601127374787457fbf9a38fa43ac094d5 by Fernando Vilas.
Committed on 03/03/2012 at 16:58.
Pushed by fvilas into branch '4.6'.
Related: bug 294046

Attempt to reconnect to the database (once) on accidental disconnects. If this still throws, we need to notify the user and reset the UI to the same state as "kmymoney -n".

Add a test case to reproduce the problem in the storage layer.
(cherry picked from commit 4da92dfa03209670bddb7801926a25741c0ad6ec)

M  +53   -9    kmymoney/mymoney/storage/mymoneydatabasemgr.cpp
M  +16   -0    kmymoney/mymoney/storage/mymoneydatabasemgrtest.cpp
M  +1    -0    kmymoney/mymoney/storage/mymoneydatabasemgrtest.h

http://commits.kde.org/kmymoney/bbc4a66601127374787457fbf9a38fa43ac094d5