Mysql 1148 Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this the used command is not allowed with this mysql version php site About Us Learn more about Stack Overflow the company Business Learn more the used command is not allowed with this mariadb version about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x the used command is not allowed with this mysql version python Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How can load data local infile mysql 5.5 not allowed I correct MySQL Load Error up vote 20 down vote favorite 8 I'm not quite sure a similar question to this was closed by I'm trying to execute the following MySQL program. mysql -e "load data local infile \ '/tmp/ept_inventory_wasp_export_04292013.csv' into \ table wasp_ept_inv fields terminated by ',' \ lines terminated by '\n' ;" at the bash command line and get this error ERROR 1148
Loose-local-infile=1
(42000) at line 1: The used command is not allowed with this MySQL version How can I work around this problem? I am actually running this command from a Python program, but pulled the command out to try fiddling with it at the bash command line. I've seen how I can modify my.cnf (local-infile), but I do not want that global a change if I can avoid it. Here's the MySQL version. mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (i686) using readline 6.2 mysql csv import load local share|improve this question edited Feb 4 at 12:05 shgnInc 96111023 asked Apr 29 '13 at 18:40 octopusgrabbus 5,57772882 add a comment| 4 Answers 4 active oldest votes up vote 26 down vote accepted As documented under Security Issues with LOAD DATA LOCAL: To deal with these problems, we changed how LOAD DATA LOCAL is handled as of MySQL 3.23.49 and MySQL 4.0.2 (4.0.13 on Windows): By default, all MySQL clients and libraries in binary distributions are compiled with the --enable-local-infile option, to be compatible with MySQL 3.23.48 and before. If you build MySQL from source but do not invoke configure with the --enable-local
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn
Security Issues With Load Data Local
more about Stack Overflow the company Business Learn more about hiring developers or pymysql local infile posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community load data into mysql table from csv Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error 1148 MySQL The used command is not allowed http://stackoverflow.com/questions/16285864/how-can-i-correct-mysql-load-error with this MySQL version up vote 10 down vote favorite 4 I am using MySQL LOAD DATA LOCAL INFILE command and I get this error: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version: LOAD DATA LOCAL INFILE '/tmp/phpI0ox54' INTO TABLE `dev_tmp` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; Array ( ) http://stackoverflow.com/questions/12819098/error-1148-mysql-the-used-command-is-not-allowed-with-this-mysql-version in dc_real_estate_form_submit() (line 147 of /PATH/TO/PHP/SCRIPT). What setting can we change to allow LOAD DATA LOCAL infile? Here is the Drupal 7 code we are using: $sql = "LOAD DATA LOCAL INFILE '".$file."' INTO TABLE `dev_tmp` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES"; db_query($sql); php mysql load-data-infile share|improve this question edited Jan 2 '14 at 21:33 Eric Leschinski 47k23221190 asked Oct 10 '12 at 12:13 Chris Muench 4,98241140243 1 It requires a new permission for a while now, perhaps that's the problem? –Ja͢ck Oct 10 '12 at 12:50 2 add --local-infile while invoking mysql. It works. Eg: mysql -h localhost --local-infile –nizam.sp Apr 9 '13 at 16:22 add a comment| 4 Answers 4 active oldest votes up vote 25 down vote 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 you start MySQL on the terminal, include --local-infile=1 argument, Something like this: mysql --local-infile=1 -uroot -p
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.7 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL Using MySQL as http://dev.mysql.com/doc/mysql/en/loading-tables.html a Document Store Tutorial Connecting to and Disconnecting from the Server Entering Queries Creating and Using a Database Creating and Selecting a Database Creating a Table Loading Data into http://practicalcomputing.org/node/76 a Table Retrieving Information from a Table Selecting All Data Selecting Particular Rows Selecting Particular Columns Sorting Rows Date Calculations Working with NULL Values Pattern Matching Counting Rows Using not allowed More Than one Table Getting Information About Databases and Tables Using mysql in Batch Mode Examples of Common Queries The Maximum Value for a Column The Row Holding the Maximum of a Certain Column Maximum of Column per Group The Rows Holding the Group-wise Maximum of a Certain Column Using User-Defined Variables Using Foreign Keys Searching on Two the used command Keys Calculating Visits Per Day Using AUTO_INCREMENT Using MySQL with Apache MySQL Programs MySQL Server Administration Security Backup and Recovery Optimization Language Structure Globalization Data Types Functions and Operators SQL Statement Syntax The InnoDB Storage Engine Alternative Storage Engines High Availability and Scalability Replication MySQL Cluster NDB 7.5 Partitioning Stored Programs and Views INFORMATION_SCHEMA Tables MySQL Performance Schema MySQL sys Schema Connectors and APIs Extending MySQL MySQL Enterprise Edition MySQL Workbench MySQL 5.7 Frequently Asked Questions Errors, Error Codes, and Common Problems Restrictions and Limits Indexes MySQL Glossary Related Documentation MySQL 5.7 Release Notes Download this Manual PDF (US Ltr) - 35.6Mb PDF (A4) - 35.6Mb PDF (RPM) - 34.6Mb EPUB - 8.7Mb HTML Download (TGZ) - 8.4Mb HTML Download (Zip) - 8.5Mb HTML Download (RPM) - 7.3Mb Eclipse Doc Plugin (TGZ) - 9.3Mb Eclipse Doc Plugin (Zip) - 11.4Mb Man Pages (TGZ) - 202.2Kb Man Pages (Zip) - 307.4Kb Info (Gzip) - 3.3Mb Info (Zip) - 3.3Mb Excerpts from this Manual MySQL Backup and Recovery MySQL Globalization MySQL Information Schema My
directions on page 282 concerning bulk importing into the table ctd. I copied a single load command directly from the mysql_commands.txt file. Command copied: LOAD DATA LOCAL INFILE '~/pcfb/examples/ctd/Marrus_ctdTib515.txt' INTO TABLE ctd FIELDS TERMINATED BY ',' IGNORE 1 LINES (clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET dive=515; I've been able to use all the other MySQL directions described before this, but this one generated the following error: ERROR 1148 (42000): The used command is not allowed with this MySQL version The file pathname listed in the command is correct. the connection to the local host seems to be working, the "USE midwater" database command had been issued, the ctd table was already set up etc. MySQL version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu); Operating system is Kubuntu, on a Windows virtual box. Any thoughts? Thanks in advance. -Jessica Top Login or register to post comments Fri, 09/14/2012 - 14:22 #1 joconnell_ Offline Joined: 08/21/2012 answered my own question some versions of MySQL do not have the load local data command enabled by default. Apparently there might be a security issue with this? You can read about this here: http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html#option... If you're using the commandline to interact with MySQL , and load local data is not enabled by default, you can change this by how you call up MySQL at the command line. Use: mysql -u root --local-infile=1 Top Login or register to post comments Fri, 09/14/2012 - 14:27 #2 pcfb Offline Joined: 08/04/2010 MySQL Load data Thanks for tracking that down! We will/should add that to the caveats related to that chapter. Did that solve the problem for you? Top Login or register to post comments Tue, 02/05/2013 - 11:28 #3 joconnell_ Offline Joined: 08/21/2012 sorry for the delayed reply. sorry for the delayed reply. Yes, if you call up MySQL at the