Cordova iOS app freezes while inserting 100+ records

[vasam]

Hi All,

My Cordova iOS app freezes while inserting 100+ records. It may freeze on 146 transaction or on 206 transaction. Total transactions are 506.
while testing in Xcode console I see following output:

2015-07-23 23:22:05.551 Wp[1890:297164] void SendDelegateMessage(NSInvocation *): delegate (webView:decidePolicyForNavigationAction:request:frame:decisionListener:) failed to return after waiting 10 seconds. main run loop mode: kCFRunLoopDefaultMode
2015-07-23 23:22:38.648 Wp[1890:297111] Received memory warning.
2015-07-23 23:23:12.838 Wp[1890:297111] Received memory warning.
2015-07-23 23:23:21.885 Wp[1890:297111] Received memory warning.
2015-07-23 23:23:21.921 Wp[1890:297111] Received memory warning.
2015-07-23 23:23:22.279 Wp[1890:297111] Received memory warning.
2015-07-23 23:23:23.541 Wp[1890:297111] Received memory warning.

There are simple insert queries per transaction like this:

INSERT INTO friends (user_id, server_id, fname, lname, email, phone, phone2, photo, photo_medium, local_photo, bday, bmonth, byear, vk_url, vk_id, fb_url, fb_id, ok_url, ok_id, phone_id, hide_birthday_event, related_user, merged_contacts, modified) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

What can cause of memory leaking?

Thanks.

[brodycj]

Unfortunately this is not enough information. I need a complete, self-contained test program that can demonstrate your issue. You can also send it to info@litehelpers.net.

UPDATE: Here is a test program that can be adapted to simulate a similar scenario: https://github.com/brodybits/Cordova-sqlite-memtest

It would still help to know what kind of data the OP is using for the VALUES.

[FilippoBenassutiCDM]

With X-code check memory usage of your app.

I had same problem at beginning. I found that the app with the amount of data I was using was using too much memory so managing with different slice of rows I solved my problems.

Lowering number of rows for your query you can avoid the problem.

[brodycj]

Thanks @FilippoBenassutiCDM. This issue looks similar to #299, not sure if it is exactly the same or not. I will try to reproduce this issue in the next 1-2 weeks but do not promise it.

[brodycj]

I am able to reproduce a crash using the following test program code:

        var test_rows = 200000;

        var ts_prefix = 'teststring12345';

        var db = window.sqlitePlugin.openDatabase({name: "test.db"});

        var i, j;
        var cq = 'CREATE TABLE IF NOT EXISTS tt (text1 text';
        for (i=2; i<=25; ++i) cq += ', text'+i+ ' text';
        for (i=1; i<=5; ++i) cq += ', int'+i + ' integer';
        for (i=1; i<=5; ++i) cq += ', float'+i + ' float';
        for (i=1; i<=5; ++i) cq += ', nulltext'+i+ ' text DEFAULT NULL';
        cq += ')';

        console.log('cq: ' + cq);

        console.log('test_rows: ' + test_rows);

        db.executeSql('DROP TABLE IF EXISTS tt');

        db.transaction(function(tx) {
            tx.executeSql(cq);
            for (i=1; i<=test_rows; ++i) {
                var vv = [];
                for (j=1; j<=25; ++j) vv.push(ts_prefix + '-' + i + '-' + j);
                for (j=1; j<=5; ++j) vv.push(i*100 + j);
                for (j=1; j<=5; ++j) vv.push(i*101.11 + j*1.101);
                for (j=1; j<=5; ++j) vv.push(null);
                tx.executeSql('INSERT INTO tt VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', vv);
            }

        }, function(err) {
            console.log('tx error');
        }, function(res) {
            console.log('tx ok');
    });

[rsutphin]

@brodybits, it's great that you were able to come up with a simpler repro than the ones on #299.

Playing around with it, my theory is that the problem is that all the statements & values executed by executeSql are retained in the plugin's memory until the transaction completes and then executed all at once. For a large number of complex inserts, this gets to be too much memory and iOS kills the app.

The spec is vague, but it seems like it would be legal to execute the statements in the database as they came in instead of retaining all of them until the end. Can you think of a reason why that would be a bad idea?

[brodycj]

It could change the behavior in certain cases. If some of the executeSql() calls in a transaction have a callback that queue more sql statements, there is a chance that your idea would change the order. I want to keep things as determinate as possible to avoid complex, hard-to-understand debugging issues that may come up with applications in the field.

I have found a solution, which I will describe later today or sometime tomorrow.

[vasam]

Hi Guys,

Just a heads up on this issue.
I solved it by removing then adding ios platform and reinstalling all plugins and

In our app we use following plugins:
$ cordova plugins ls
com.phonegap.plugins.PushPlugin 2.5.0 "PushPlugin"
com.phonegap.plugins.facebookconnect 0.11.0 "Facebook Connect"
cordova-plugin-camera 1.2.0 "Camera"
cordova-plugin-contacts 1.1.0 "Contacts"
cordova-plugin-device 1.0.1 "Device"
cordova-plugin-file 2.1.0 "File"
cordova-plugin-file-transfer 1.2.1 "File Transfer"
cordova-plugin-globalization 1.0.1 "Globalization"
cordova-plugin-inappbrowser 1.0.1 "InAppBrowser"
cordova-plugin-media 1.0.1 "Media"
cordova-plugin-network-information 1.0.1 "Network Information"
cordova-plugin-whitelist 1.0.0 "Whitelist"
io.litehelpers.cordova.sqlite 0.7.10-pre "Cordova sqlite storage plugin"

Initially I though some plugin may use too many memory. I created new app in cordova, added just SQLite plugin. Then step by step was adding each plugin. I was surprised there was no any crashes even if I was adding 40000 records!

After set of tests I decided to remove then add platform as well as all plugins in our main app. That helped. App interfaces works much more faster now.

I can't explain what was the issue in my case, probably cordova keeps some cached data. Any thoughts?

[brodycj]

@vasam that sounds very strange. It looks like an installation problem that I am very happy to see has gone away for you.

[rsutphin]

It seems like it would be possible to preserve the current semantics but also issue queries closer to when they come in. But it would probably be complicated — if you've got a better option, that's good to hear.

[brodycj]

Thanks @rsutphin for your interest and ideas. I do already have a possible solution which I am planning to distribute under different license terms (GPL or commercial options). I expect to post it tomorrow.

I am also planning some API enhancements that would allow the user to issue the transactions in smaller batches, under his/her own control. Will describe sometime later.

[brodycj]

When I run the test code on Android, it will freeze or crash when I run it with test_rows = 20000 (20 thousand) or greater. The iOS version in this project works fine for test_rows up to 180000 (180 thousand).

[brodycj]

Please see the version in https://github.com/litehelpers/Cordova-sqlite-enterprise-free (with a different licensing scheme) for some internal memory improvements. If you continue to see memory issues please file a bug report there.