In my current project, I needed to load zip code data into a MySQL table. There are a number of ways to do this, but I wanted a solution that could flex to much larger datasets and run fast. After doing some research, it seemed like the best way to accomplish this was to use MySQL’s LOAD DATA INFILE and combine it with a normal Laravel migration.
Since it took me a few hours of Googling and pouring over a number of sources to get things working, I thought I’d pay it foward and write up the process I would have followed if I had known what I was doing when I started.
Optionally: Your my.cnf may need the variable local_infile to be set to true. You can check in MySQL (mysql>> show variables) or you can sudo nano my.cnf from /etc/mysql. If it isn’t present add the following and save:
[Server] local_infile=true
Required: Next, you need to add the MYSQL_ATTR_LOCAL_INFILE to your PDO options. You can do this in config/database.php in the mysql block of the connections array:
'mysql' => [ 'driver' => 'mysql', 'host' => env('DB_HOST'), 'database' => env('DB_DATABASE'), 'username' => env('DB_USERNAME'), 'password' => env('DB_PASSWORD'), 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', 'strict' => false, 'options' => array(PDO::MYSQL_ATTR_LOCAL_INFILE => true), ],
If you use Lumen, create a config folder at your project’s root and copy the /vendor/laravel/lumen-framework/config/database.php to it. Add the above to your MySQL configuration. Lumen looks for config files contained within /vendor/laravel/lumen-framework/config in the config directory.
You may have to set both or you might get access denied or forbidden errors and you will do much Googling and be very sad like all of these poor souls.
I located tab delimited zip code data at http://download.geonames.org/export/dump/. While I have an actual use for the data, it works well as a source to try this yourself. Don’t “massage” the data in preparation for import. It’s fine just the way it is. That’s generally true for most source data you will want to load. Do your tinkering programmatically.
$ php artisan make:model Models/Zip -m
In Laravel 5.1 and 5.2 you need to specify the -m option in order to have the migration generated for you. In 5.0, the migration was generated automatically.
public function up() { Schema::create('zip_codes', function (Blueprint $table) { $table->increments('id'); $table->string('country_code', 2)->nullable();; $table->string('zip_code', 20); $table->string('city', 180)->nullable(); $table->string('state_name', 100)->nullable(); $table->string('state_code', 20)->nullable(); $table->string('county_name', 100)->nullable(); $table->string('county_code', 20)->nullable(); $table->string('community_name', 100)->nullable(); $table->string('community_code', 20)->nullable(); $table->double('latitude')->nullable(); $table->double('longitude')->nullable(); $table->tinyInteger('accuracy')->unsigned()->nullable(); $table->tinyInteger('status')->unsigned(); $table->nullableTimestamps(); }); // up() is not closed here as you will add to the method -- see below
All columns except id, status and timestamps (created_at, updated_at) are present in the source file. While I could update this reference by reloading it or rerunning this migration, I want to be able to leave open the possibility I may have foreign key constraints or allow an admin to update the data directly.
Since we want to load the data from someplace convenient to us on the server, I chose to put the file in a directory at my project’s root. You can get the path using the base_path()
helper function in Laravel. Of course, you can store your data files elswhere, you’ll just have to define the fully qualified path. There is one exception if you plan to use Envoyer, you can’t store your data in the /storage folder.1
$file = base_path('data/file.txt'); $query = "LOAD DATA LOCAL INFILE '" . $file . "' INTO TABLE zip_codes (country_code, zip_code, city, state_name, state_code, county_name, county_code, community_name, community_code, latitude, longitude, accuracy, @status, @created_at, @updated_at) SET status=1,created_at=NOW(),updated_at=null"; DB::connection()->getpdo()->exec($query); } // close up()
You’ll note that the columns not included in the data are referenced as variables (@) in the query and their values are set on line 53.
Remember that we are loading a tab delimitted file and the default delimiter and line endings are \t and \n. If you have a comma delimitted file you’ll need to specify:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'
You can specify whatever single character delimitter your file format uses. So, if you have a pipe delimitted file, you can use FIELDS TERMINATED BY '|'
.
Additionally, if you have a header row you will want to add:
IGNORE 1 LINES
Of course, you can run across files that are malformed or require some cleanup before they are useable in your application. Since you can execute any additional queries you like via PDO, now is a good time to do any needed clean up. MySQL’s string functions can come in handy, but you may have to write some php in some cases. I would suggest exhausting your MySQL options before coding anything. You’ll probably learn something and queries are virtually guaranteed to run fast since you’ll only be operating against one table.
$ php artisan migrate
If your migration errors, forget php artisan migrate:rollback
or php artisan migrate:refresh
because the table will have already been created. Just delete the table, make your changes and run php artisan migrate
. While you could wrap your migrations in transactions, that’s probably not all that useful because your migrations shouldn’t be failing constantly anyway.
There are roughly 44k rows in the file and it loads unbelievably fast. In fact, it loads so fast you’ll check the table just to verify the data is actually there.
Also, you could use seeders and I do use one to combine a number of migrations that include data imports, but I can’t tell you how many times I forget to run that one seeder class before testing. One last note, this is all in pre-production. After the project is in production, data updates will have to be managed differently in my case as I’m loading data from a large number of sources and that data will drive the application.
If there is a better way to accomplish this with respect to implementation or architecture, I would genuinely like to know.
I wrote this a long time ago. I would recommend moving the actual data loading to a seeder. For my use case at the time, I had a large number of migrations which were loading a ton of reference data for a project that was not yet in production and –seed was not an option for php artisan migrate:refresh
in the version of Laravel I was using. Also, only create the model if you actually need it.
1 The /storage folder is to be used for post deployment data storage. Envoyer sets up the folder with the Laravel structured one level below your /current folder. When your new releases are cloned down, the storage folder is removed. The benefit is /storage will be consistent across your deployments. For example, you will want to use it for things like user uploads or anything else that gets generated and needs to be saved. Caches for files and sessions are other good examples.
please see mysql user permission
+————————————————————————————————————————+
| Grants for [email protected] |
+————————————————————————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘script’@’localhost’ IDENTIFIED BY PASSWORD ‘*3478ERJD$#%#$%^’ |
+————————————————————————————————————————+
We have found another solution and it works for me by mysqli, check this:
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_LOCAL_INFILE, true);
$mysqli->real_connect(‘localhost’,’user’,’password’,’databse’);
$sql = “LOAD DATA LOCAL INFILE ‘Downloads/slots_1.txt’ INTO TABLE table_name FIELDS TERMINATED BY ‘:::,’ LINES TERMINATED BY ‘^^^\n’ (a,b)”;
if(!$mysqli->query($sql))
printf(“Errormessage: %s\n”, $mysqli->error);
But I think that is not a proper solution, have a look and let us know
Hello Shawn
I got same error again :
SQLSTATE[HY000]: General error: 29 File ‘/tmp_loaddata/campaign_slots_1.txt’ not found (Errcode: 13) (SQL: LOAD DATA INFILE ‘/tmp_loaddata/campaign_slots_1.txt’
INTO TABLE campaign_slots_1
FIELDS TERMINATED BY ‘:::,’
LINES TERMINATED BY ‘^^^\n’
(id, campaign_level_id, campaign_strategy_id, slot_status, campaign_ranking_bid, campaign_residual_bid, slot_debit_value))
I put ‘options’ => [PDO::MYSQL_ATTR_LOCAL_INFILE => true], like below under config/databse.php –
‘connections’ => [
…..
‘mysql’ => [
…….
‘options’ => [PDO::MYSQL_ATTR_LOCAL_INFILE => true],
],
]
please check where I am doing wrong
Hi Vivek, I’ve some issue (laravel side i think) and I hope you can help in some way.
I’ve two projects one Laravel 5.0 and the other Laravel 5.2 (5.2.26), the second one is just a rebuilt of first one.
They both use the same db, same user, db configuration , etc. (are on the same server and use the same mysql server)
I’ve a console command again the same on both projects.:
\DB::connection()->getPdo()->exec(“LOAD DATA LOCAL INFILE ‘” . storage_path(‘myfile.txt’) . “‘ IGNORE INTO TABLE domains LINES TERMINATED BY ‘\\n’ IGNORE 1 LINES (domain)”);
I’ve the same configuration option:
‘options’ => [PDO::MYSQL_ATTR_LOCAL_INFILE => true]
It works only on the 5.0 project. On laravel 5.2 it throw:
SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version
(note on mysql localinfile is on)
solved using:
‘options’ => [\PDO::MYSQL_ATTR_LOCAL_INFILE => true, \PDO::MYSQL_ATTR_READ_DEFAULT_GROUP=>’client’],
Thanks for the information, not exactly obvious when it’s something we’ve never done before. Worked like a charm.
I would suggest giving more detail about where that options key is placed in the database.php file since I made it top level and that was clearly not where it was supposed to go. Ended up doing another search online to see how to pass PDO parameters to a database connection.
Amazingly fast insertion of CSV data. Thanks for sorting this out. Was able to import 4M+ rows in just a few seconds.
As a suggestion for your code above:
1. On line 18 and line 37 different table names are used (zips versus zip_codes)
2. On line 22 and line 39 different names are used (postal_code versus zip_code)
After changing these names it worked like a charm.
Thanks!
Hi Shawn, thanks for code. I am trying but return error is:
PDO::exec(): HTTP request failed! HTTP/1.1 403 Forbidden
hi dude thanks for the code. got this error:
PDO::exec(): MySQL server has gone away
Hello
We are using lumen, and we are getting same error:
PDOException in CampaignSlots.php line 290:
SQLSTATE[HY000]: General error: 29 File ‘/home/vivek/Downloads/tmp_loaddata/campaign_slots_1.txt’ not found (Errcode: 13)
if we use LOAD DATA LOCAL INFILE get this error:
PDOException in CampaignSlots.php line 290:
SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version
we have set the ‘options’ => [PDO::MYSQL_ATTR_LOCAL_INFILE => true],’ under vendor/lumen-framwork/config/database.php’ but not succeed.
Please help