mysqlimport. takes the same connection parameters as the mysql command line shell. Make sure to use the -L flag to use a file on the local file system, otherwise it will (strangely) assume the file is on the server. There is also an analogous variant to the load data infile command, i.e., load data local … Read more
You need to use the SET clause, along with a variable to reference the contents of the row at that column. In your column list, you assign your date column to a variable name. You can then use it in your SET statement. (Note, I haven’t got MySQL in front of me to test this … Read more
if you’re using innodb and bulk loading here are a few tips: sort your csv file into the primary key order of the target table : remember innodb uses clustered primary keys so it will load faster if it’s sorted ! typical load data infile i use: truncate <table>; set autocommit = 0; load data … Read more
Loading a local file in MySQL is a security hazard and is off by default, you want to leave it off if you can. When it is not permitted you get this error: ERROR 1148 (42000): The used command is not allowed with this MySQL version Solutions: Use –local-infile=1 argument on the mysql commandline: When … Read more
Here’s a thread on the MySQL forums that discusses exactly this. Here’s the answer, posted by Ken Tassell Problem resolved using the command below: grant file on *.* to kentest@localhost identified by ‘kentest1’;
If you’d do echo($sql); before you execute it you’d see that syntax of your query is incorrect for following reasons: Filename should be enclosed in quotes rather than backticks because it’s a string literal not an identifier. There is absolutely no need to call mysql_escape_string() to specify a delimiter in FIELDS TERMINATED BY and ENCLOSED … Read more
I just ran into this issue as well. I had to add LOCAL to my SQL statement. For example, this gives the permission problem: LOAD DATA INFILE ‘{$file}’ INTO TABLE {$table} Add LOCAL to your statement and the permissions issue should go away. Like so: LOAD DATA LOCAL INFILE ‘{$file}’ INTO TABLE {$table}
You could use SQL for this: $file=”backups/mytable.sql”; $result = mysql_query(“SELECT * INTO OUTFILE ‘$file’ FROM `##table##`”); Then just point a browser or FTP client at the directory/file (backups/mytable.sql). This is also a nice way to do incremental backups, given the filename a timestamp for example. To get it back in to your DataBase from that … Read more
Ran into the same issue as root and threw me for a moment could be an issue with your server settings set with compile to test login to console with the same user and try your load data command if you get the same error, try closing console and running mysql -u USER -p –local-infile=1 … Read more
You can have MySQL set values for certain columns during import. If your id field is set to auto increment, you can set it to null during import and MySQL will then assign incrementing values to it. Try putting something like this in the SQL tab in phpMyAdmin: LOAD DATA INFILE ‘path/to/file.csv’ INTO TABLE your_table … Read more