Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL Error: too many SQL variables #87

Closed
MattNewberry opened this issue Oct 2, 2018 · 20 comments

Comments

Projects
None yet
2 participants
@MattNewberry
Copy link

commented Oct 2, 2018

Describe the bug
When using a table with a primary key and secondary index, WebSQL queries fail to execute on large-ish datasets.

SQLError {code: 5, message: "could not prepare statement (1 too many SQL variables)"}

Example
With a table consisting of 2 indexes and 7500 records, the following error occurs with the WebSQL adapater during the initial connect:

_this._sql(false, "SELECT id FROM " + table, [], function (result) {

Error:
SELECT data from Vehicles WHERE id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (7483) [392430, 392437, 392441, 392442, 392443, 392446, 392449, 392450, 392451, 392455, 392533, 392537, 392539, 392540, 392546, 392555, 392556, 392558, 392561, 392566, 392572, 392574, 392576, 392581, 392582, 392583, 392584, 392585, 392597, 392598, 392605, 392609, 392620, 392631, 392643, 392647, 392656, 392657, 392659, 392660, 392662, 392663, 392668, 392669, 392670, 392671, 392672, 392683, 392690, 392697, 392700, 392710, 392713, 392716, 392718, 392724, 392727, 392734, 392743, 392755, 392762, 392766, 392767, 392771, 392789, 392793, 392797, 392816, 392820, 392821, 392822, 392831, 392834, 392842, 392843, 392847, 392851, 392852, 392854, 392858, 392859, 392861, 392862, 392863, 392872, 392936, 392939, 392942, 392948, 392949, 392952, 392956, 392960, 392961, 392969, 392974, 392976, 392979, 392983, 392987, …] SQLError {code: 5, message: "could not prepare statement (1 too many SQL variables)"}

Expected behavior
Expectation is for queries to execute correctly despite the data size and index count.

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Oct 2, 2018

Thanks for the heads up, I'll try to get a fix out this week.

@ClickSimply ClickSimply added the bug label Oct 2, 2018

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 3, 2018

Awesome, thank you!

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 3, 2018

Of note, I saw this same error with just 2000 or so records when using multiple indexes on a table, as well.

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Oct 10, 2018

This should be resolved as of v1.7.7, just pushed it to NPM

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 16, 2018

I can confirm this works in WebSQL, however when running on iOS devices this still fails. This appears to be due to the SQLITE_MAX_VARIABLE_NUMBER setting which defaults to 999 in sqlite. Is there value in assembling queries within Nano that can leverage sqlite directly, instead of Nano doing the heavy lifting before and after querying against id? limit() does not fix this either as it appears Nano still needs to query all records prior to limiting.

Edit: I realize now in my original issue only stating WebSQL to be at fault, thus you only fixed WebSQL. Thank you for that, and my regrets for not testing in sqlite as well at the time.

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 16, 2018

I've attempted to carry over your WebSQL fix into the Sqlite adapter but have faced issues with getting the plugin to compile. It appears to use an older version of webpack. I hate to file issues and not offer PRs, but in this case, I'm unsure how to resolve the build system issues.

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Oct 16, 2018

No worries, I'll take care of this later today/early tomorrow. Should be able to knockout the iPad issue that was opened a few days ago at the same time.

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Oct 17, 2018

Just pushed an update to the SQLite plugin, should be good now.

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 18, 2018

You're absolutely going to kill me... This is what I get for rushing through my comments. We're using the Adapter-SQLite-Cordova plugin, and not the Sqlite3 adapter. Would you mind updating that adapter as well with the same fix?

Send me your PayPal, happy to contribute to your beer or coffee fund!

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 30, 2018

@ClickSimply Any chance we could get an update for the Adapter-SQLite-Cordova plugin?

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Oct 31, 2018

Yep, should have this taken care of tomorrow.

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Oct 31, 2018

Thank you!

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Nov 2, 2018

@ClickSimply I hate to keep pestering about this, but this is a blocker for us and we're unable to ship our app without this fix. Any chance you have time to take a look again soon?

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Nov 6, 2018

@ClickSimply We're at your mercy here at this time. Do you have any updates on when you could provide us with this fix? It's seemingly as straight forward as the fixes you did for the other adapters. Thank you!

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Nov 7, 2018

I'll be able to get to this tonight, sorry it's taken a while...

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Nov 8, 2018

@ClickSimply Hey Scott! Thanks for the update - I've checked the commit log and it doesn't appear the fix made it out. We're a day away from shipping a beta to customers and this is a show stopper for us. We'll pay for your time if that's what's needed to make this fix available, but are desperate for this fix to be made available. I wrote a fix here locally that mirrors the ones you did for the other adapters but was unable to get the build system working correctly. Your time is greatly appreciated.

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Nov 8, 2018

My last attempt at build my fix did not work, however I directly applied your Adapter-Sqlite3 fixes to the Adapter-Sqlite-Cordova module and it now appears to work. Please see attached PR.

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Nov 8, 2018

@ClickSimply Not sure if this is related to my PR or a prior issue in Adapter-Sqlite-Cordova, however some string indexes are not working as expected. It appears the strings are being truncated and thus do not match the desired where() when provided.

@ClickSimply

This comment has been minimized.

Copy link
Owner

commented Nov 10, 2018

NanoSQL 1.7.9 was just released to NPM as well as the Cordova Adapter 1.5.0. The truncated indexes and other issues should be good to go now.

Let me know if you're still having issues following the updates.

@MattNewberry

This comment has been minimized.

Copy link
Author

commented Nov 12, 2018

Thank you! I appreciate your time and helping address these bugs. Keep up the good work!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.