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

error when seeding from table with many records #4

Open
skwl opened this issue Oct 13, 2013 · 19 comments

Comments

Projects
None yet
8 participants
@skwl
Copy link

commented Oct 13, 2013

I created a seed from a table with 14 columns and over 5000 rows but when I ran "php artisan db:seed", I got a general error: 1390 prepared statement contains too many placeholders.

I edited the seed code to make two inserts instead of seeding all of the rows in one and then it worked.

\DB::table('users')->insert(array (

@BeingTomGreen

This comment has been minimized.

Copy link

commented Oct 13, 2013

I could be wrong but to many placeholders sounds like a query issue to me..

I've seen this issue before when having a seed file were some entries have mismatched keys. That is to say if you add a key to on entry it must exist for every one, otherwise the query wouldn't work.

@skwl

This comment has been minimized.

Copy link
Author

commented Oct 14, 2013

It is a query issue. Apparently, the number of placeholders is limited to about 65536 per query. With 14 columns per row, I can only insert 4681 rows in one prepared statement. So if I startt another prepared statement after row 4681 by adding "\DB::table('users')->insert(array (" with the appropriate commas and parantheses, I can get the seed to work.

I could not find the place to add code to iseeds that would limit the number of placeholders per prepared statement.

@BeingTomGreen

This comment has been minimized.

Copy link

commented Oct 14, 2013

I did some digging into this, and I found an answer on SO by Trent Lloyd:

"The maximum number of placeholders for values in a prepared statement is [limited to] the maximum value of a 16-bit unsigned integer, or specfically [sic]: 65,536."

If you take a look at the source code for init_param_array function, specifically line 2124 you can see where this limit is imposed.

This issue is neither iSeed or Laravel related.

@skwl

This comment has been minimized.

Copy link
Author

commented Oct 15, 2013

The issue with iSeed is that it creates a seed that will fail because it exceeds 65,536 placeholders when creating a seed from a large table. I am not skilled enough to edit the code to create multiple prepared statements when necessary. If the number of columns times the number of rows exceeds 65,536, then it would be necessary to limit the number of rows in each statement to 65,536 divided by the number of columns.

@BeingTomGreen

This comment has been minimized.

Copy link

commented Oct 16, 2013

I'm unsure how/if @tihomiro wants to tackle this..

@tihomiro

This comment has been minimized.

Copy link
Member

commented Oct 18, 2013

Since it didn't feel that I was gonna have free time to tackle this anytime soon, I asked my coleague Ivan to have a look.

@ivanbatic

This comment has been minimized.

Copy link
Member

commented Oct 18, 2013

Fixed by splitting input data into chunks of 500 elements per insert statement.
I tried seeding 15 000 rows with 20 columns, and it worked fine.

@tihomiro tihomiro closed this Oct 18, 2013

@BeingTomGreen

This comment has been minimized.

Copy link

commented Oct 18, 2013

A limit of 500 would only allow 131 columns. I'd suggest dropping the limit further, or making it dynamic or customizable?

@nateritter

This comment has been minimized.

Copy link

commented Oct 21, 2014

Everything worked fine for me out of the box, until I tried to run a 61MB file (1.3MM rows). With that, I get a memory failure. I tried increasing memory limit to as high as I could, but it still failed. Is there a way to rewrite this so it doesn't try to get everything in a table into memory?

@ivanbatic

This comment has been minimized.

Copy link
Member

commented Oct 21, 2014

@nateritter
It is certainly possible to rewrite the generator so it uses only a few megabytes of memory.
However, doing it in such way would be contrary to the concept of Laravel's seeds.

Running it on a 1.3M rows sounds like you are trying to use iSeed as a DB backup/migration tool and I must suggest using the native mysql/mysqldump commands which have minimal memory impact and maximal speed and reliability.

@nateritter

This comment has been minimized.

Copy link

commented Oct 21, 2014

I'm not using it as a backup/migration tool. Static data which is needed for my app to work (ie, cities, and their relevant data points, attributes of properties, amenities for locations, etc).

Kinda bummed about this as it's not unreasonable in this day and age to think that you're working with an application with a lot of data. The only other workaround for this is to manually extract certain rows that I know will relate properly to as/to foreign keys. Or, don't test features which use this data (not an option). Both of those workarounds suck.

@nateritter

This comment has been minimized.

Copy link

commented Dec 24, 2016

.... aaaand I'm back. Same issue as 2014 on a new application, and I see it's unfortunately not been modified to accompany this use case. :/

@tihomiro

This comment has been minimized.

Copy link
Member

commented Dec 24, 2016

@nateritter OK, I agree that this should be rewritten in a way that the data is split into chunks while reading from DB instead on writing to file, whilst pipelining the output to seed file directly. I'll reopen the issue, though it might take a while before I have some free time to deal with this. I'll do my best.

@tihomiro tihomiro reopened this Dec 24, 2016

@nateritter

This comment has been minimized.

Copy link

commented Dec 26, 2016

@tihomiro tihomiro added the bug label Dec 30, 2016

@eliasgonzalezugalde

This comment has been minimized.

Copy link

commented May 23, 2017

I do not understand what they are referring to: splitting input data into smaller chunks of elements per insert statement. Could you give me some examples of how to generate a seed with artisan? My table has 75859 records :(

@tihomiro

This comment has been minimized.

Copy link
Member

commented May 23, 2017

@eliasgonzalezugalde sorry, iSeed won't be able to help. This feature is, sadly, still waiting for a couple of hours of my free time.

@eliasgonzalezugalde

This comment has been minimized.

Copy link

commented Jun 3, 2017

OK, thanks for answering.

@shift-keshav-pudaruth

This comment has been minimized.

Copy link

commented Apr 11, 2018

Seeding an sql lite database will fail, if too many parameters are present (in the insert statement). See: https://www.sqlite.org/c3ref/c_limit_attached.html#sqlitelimitvariablenumber

Edit: Functionality is already present in Iseed.php, Line 224 but it's not implemented. Tested and working.

@psadaic

This comment has been minimized.

Copy link
Contributor

commented Dec 6, 2018

PR -> #132

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.