Importing a large CSV only gives 5000 rows

[Peter Wolanin]

I upped the PHP limits to allow me to load a ~10MB csv with 47k lines

After doing so, I see the table only has 5000 rows.

using dev version 31057a4795417ba72cb2194dd2ea418c1cb3f5ba

[phpLiteAdmin]

phpLiteAdmin imports 5000 rows per transaction. This means if you import 47k lines of csv, it will split it into 10 transactions. If you end up with only 5000 lines, this means the import was interrupted after the first transaction. I guess you hit PHP's max_execution _time or, less likely, memory_limit. Check your logs and try to increase max_execution_time to something like 300 (5 minutes).

I noticed phpLiteAdmin tries to set the max_execution_time to -1 (no limit) when exporting csv, but not when importing. I guess we should also do it when importing.

[phpLiteAdmin]

Please try the latest development version rev c5f545d , it tries to set the time limit to -1 when importing csv.

[Peter Wolanin]

That did not seems to make a difference. Using c5f545dfcdd181c50e8742d8ea9b14ae9641cae1

Also seeing this notice - maybe it's not supporting using a tab separator? I put in \t in the separator field.

[Thu Sep 27 14:48:02 2018] PHP Notice:  fgetcsv(): delimiter must be a single character in phpliteadmin-git/classes/Database.php on line 1314

[Peter Wolanin]

I also got this warning I think during the import:

[Thu Sep 27 14:55:37 2018] PHP Warning:  count(): Parameter must be an array or an object that implements Countable in phpliteadmin-git/classes/Database.php on line 1315

[Peter Wolanin]

The failure here seems to be when creating the table and then importing in the same operation.

If I empty the table and import again I seem to get the full number of rows.

[Peter Wolanin]

Maybe it's trying to create the table each time? I get this error in the web UI:

table '2018-04-30-19125-FVE' already exists

[Peter Wolanin]

Yes, looks like a bug in building the import_csv() SQL where the "CREATE TABLE" is added to the start of the SQL and so is called again for every 5k rows.

One easy fix would be to add "IF NOT EXISTS" in line ~1322 of classes/Database.php

[phpLiteAdmin]

Importing CSV into a new table ran CREATE TABLE every 5k rows with the same table name. Thus, the second failed and only up to 5k rows are imported. This should fix issue #367 https://bitbucket.org/phpliteadmin/public/issues/367/importing-a-large-csv-only-gives-5000-rows

→ <<cset 2529c330b873>>

[phpLiteAdmin]

Thank you Peter for your detailed analysis of the problem. It should be fixed with commit 2529c33. Please let me know if this fixes your problem.

[phpLiteAdmin]

With commit 6c5ef40 the PHP warning you encountered should not happen anymore. It is a new warning in PHP 7 and very common.

[phpLiteAdmin]

With commit 9fcd1fb, tab delimiters should now be working using \t.

[Peter Wolanin]

Yes, tab separated seems to work now. Could import 17k rows using 9fcd1fb61d5e4f3e7da9e3f285e50a027e87bcdd