Priming MySQL Using Laravel Migrations
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.
Prerequisites
- An earlier version of this post recommended adding local_infile=true to your my.cnf. However, this step should not be needed. If you set the ‘options’ value in your database.php and it doesn’t work, try adding local_infile = true to your my.cnf and restarting MySQL.
- If you don’t have access to my.cnf, you can try removing LOCAL and loading the file from your MySQL data directory.
- If PDO doesn’t work for you, see the comment below which references a method to accomplish it using mysqli. As noted, this is NOT a desirable workaround.
- If you just can’t make it work, try a package like https://github.com/goodby/csv instead.
- Environment. I have tested this on Laravel 5.2 and Lumen 5.2 under PHP 7 and MySQL 5.7. using a Per Project Homestead installation since that’s what most people will be using. You can also try another virtual machine if you prefer. I have also run this in production on Forge provisioned servers running PHP 5.6/7 and MySQL 5.6/5.7.
- Your MySQL user must have the ‘file’ privilege. The homestead and forge users have all privileges by default.
Configure MySQL and PDO
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.
The Data
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.
Create a Model and Migration
$ 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.
Edit the Migration
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.
Load the Data
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
Garbage In Garbage Out
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.
Run the Migration
$ 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.
Disco
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.
Postscript
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.
27 Comments
-
-
Hi Vivek,
Did you add local_infile=true to your my.cnf and restart MySQL? In my experience, I had to add it to both my PDO options and to my.cnf. The errors you are getting are both related to not having set the configuration properly.
-
Hi,
Thanks for the reply ,Yes I have already add the ‘local_infile=true’ on my.cnf but not works for me.
SQLSTATE[HY000]: General error: 29 File ‘/home/vivek/Downloads/tmp_loaddata/campaign_slots_1.txt’ not found (Errcode: 13)
-
Are you using Homestead? Does your MySQL user have the ‘file’ privilege?
-
I am new for laravel (lumen) how can I check this.
The permission of my file is:
-rwxrwxrwt 1 mysql www-data 3021465 Jan 13 12:13 slots_1.txt
-
-
-
-
-
To find out what privileges you have for your MySQL user, go to your database client or mysql terminal and issue SHOW GRANTS using the user you use to connect to your database; If you see something other than ALL PRIVILEGES that does not contain FILE, then you need to grant that permission to the MySQL user you are connecting with in your application. This is not a file permission issue.
What version of MySQL are you running? I did see a note about INFILE not working in 5.6.16 but I don’t know how credible that report is.
-
The php version is
PHP Version 5.6.17-1+deb.sury.org~precise+2
and mysql is 5.5.46
-
Well, looks like I’m stumped for the moment. I will have to test under Lumen.
Here are the relevant MySQL docs: http://dev.mysql.com/doc/refman/5.7/en/load-data.html
And here are the docs on privileges: http://dev.mysql.com/doc/refman/5.7/en/show-grants.html
-
-
-
please see mysql user permission
+————————————————————————————————————————+
| Grants for script@localhost |
+————————————————————————————————————————+
| 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
-
OK, I tested under Lumen 5.2.1 with the latest Per Project Homestead.
First, edit /bootstrap/app.php and uncomment $app->withFacades();
Next either create a config/database.php in your project root or copy from /vendor/laravel/lumen-framework/config/database.php to config at your project root.
Edit to add ‘options’:
‘options’ => [PDO::MYSQL_ATTR_LOCAL_INFILE => true],
Finally, run your migration. Oh, and the privileges you referenced are fine.
I actually tried this with and and without enabling it in my.cnf and it works perfectly fine using the ‘options’ attribute alone as noted above.
-
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
-
Facepalm. You have to use LOAD DATA LOCAL INFILE. You are missing LOCAL.
-
SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version (SQL: LOAD DATA LOCAL 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’m really at a loss at this point. Try moving the file to within your project directory and chmod it 777.
-
I move the file on my project directory with two different path but not works,
On storage path
PDOException in CampaignSlots.php line 290:
SQLSTATE[HY000]: General error: 29 File ‘/var/www/pantrywiser.com/lumenSetup/storage/tmp_loaddata/campaign_slots_1.txt’ not found (Errcode: 13)On root path :
PDOException in CampaignSlots.php line 290:
SQLSTATE[HY000]: General error: 29 File ‘/var/www/pantrywiser.com/lumenSetup/tmp_loaddata/campaign_slots_1.txt’ not found (Errcode: 13)
-
-
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’],-
Zofe, interesting and glad you solved it. What does your my.cnf look like? Thanks for posting and following up!
-
-
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.
-
Glad it was helpful. Thanks for the suggestion to clarify the addition of the PDO option. I expanded it to include the entire mysql block under connections.
-
-
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!
-
Stunning right? Glad the post was helpful and thanks for the edits.
-
-
Hi Shawn, thanks for code. I am trying but return error is:
PDO::exec(): HTTP request failed! HTTP/1.1 403 Forbidden-
What is your use case for trying to run this from a route? If you followed along, just run the migration:
$ php artisan migrate
-
-
hi dude thanks for the code. got this error:
PDO::exec(): MySQL server has gone away-
Hey Jeremy,
Really tough to say without knowing what data you are trying to load, MySQL version and settings, etc. Have a look at this SO question: PDO: MySQL server has gone away and NB wait_timeout and max_allowed_packet. Let me know how it goes. I haven’t had a use for this in some time, but could give it a try on latest MySQL/PHP/Laravel if all else fails.
-
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