-4

I’m developing an archive where people can search for cars registered with mobility parking permit in Israel.

I have CSV file that is updated on a daily base from the Israeli Ministry of Transportation.

I wish to run a cron job that downloads the file, parse it and insert each car plate into the database.

The problem is the server, after about 5000 records returns 503 error. What can I do to split the file that have more than 50,000 car plates?

Thank you!

Robin
  • 397
  • 1
  • 7
Liam Mizrahi
  • 1
  • 1
  • Split the CSV? You can generate your own files after parsing the original and before inserting into the database. – Dov Rine Jul 12 '19 at 10:55
  • You’re right but the method I want to do is download from the ministry of transportation the file on weekly/daily base automatically and parse it into the database – Liam Mizrahi Jul 12 '19 at 10:58
  • I understand, but if the server can't handle the load, maybe you are better off splitting the csv yourself and then inserting into the database at a rate that won't kill the server. This seems especially doable b/c the file is updated so infrequently. – Dov Rine Jul 12 '19 at 11:05
  • Also, if you have control of the server, you should find out why it's so easily overwhelmed. 5k records should be nothing to a db server. – Dov Rine Jul 12 '19 at 11:06

2 Answers2

1

I noted that you did testing via the browser when testing your code. A 503 exception occurs when PHP reaches it's maximum execution time.

To mitigate this issue you could do one of the two following:

1. Update your php.ini file

max_execution_time = 180

This will allow your code to run for 180 seconds. Remember to restart php.

2. Set the time limit directly in your PHP code

set_time_limit(180);

This will set the maximum execution time once the code triggers. This will only affect the current PHP script.

From the PHP documentation:

set_time_limit ( int $seconds ) : bool

Set the number of seconds a script is allowed to run. If this is reached, the script returns a fatal error. The default limit is 30 seconds or, if it exists, the max_execution_time value defined in the php.ini.

Community
  • 1
  • 1
Robin
  • 397
  • 1
  • 7
0

503 means your php request is failing. It probably is running out of either time or RAM while processing your big CSV file. php generally handles web page display for many users of a web server, so it typically has 30 seconds and a few tens of megabytes of RAM available. But you are using it to handle a large batch-like request.

You can probably find out more by asking to see the web server's error log. It almost certainly has some log entries explaining what happened to cause your 503 error

What to do? You have some choices.

  1. You can ask the person who maintains the php program on your web server to increase the RAM and/or time limit available to that program. Your favorite search engine will give you, or that person, information about how to do those things. (It may also have a file upload size limit too small for your CSV file.)

  2. If you can connect to your MySQL database directly from your machine holding your CSV file, using the mysql client program, or some other client program (HeidiSQL? MySQL Workbench?), you can use LOAD DATA INFILE to parse your CSV and load the rows directly into your table. This is blazing fast and effective. Read this. MYSQL import data from csv using LOAD DATA INFILE

  3. You can use a program on your machine to split your CSV file into smaller chunks and upload each one individually. There are plenty of simple programs to split CSV files; it's hard to recommend one without knowing whether your machine is Windows / Mac / Linux etc.

  4. You can run a program on your machine (which you will have to create) to compare yesterday's CSV file to today's, and only upload the rows that are different.

O. Jones
  • 103,626
  • 17
  • 118
  • 172