[opensuse] MySQL Load Data Local Infile problem
To the database gurus I've just started exploring mysql (I'm a db newbie), and run into a 'feature'. When I do LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO TABLE mytable; I get Error 1148: The used command is not allowed with this MySQL version I know that it's disabled for security reasons, but I don't want to have to enter each record individually. Is there a hack to get round this restriction, or do I have to recompile mysql? Bob -- Registered Linux User #463880 FSFE Member #1300 GPG-FP: A6C1 457C 6DBA B13E 5524 F703 D12A FB79 926B 994E openSUSE 11.3 64-bit, Kernel 2.6.36.90-desktop, KDE 4.5.3 Intel Core2 Quad Q9400 2.66GHz, 8GB DDR RAM, nVidia GeForce 9600GT -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Bob Williams wrote:
To the database gurus
I've just started exploring mysql (I'm a db newbie), and run into a 'feature'.
When I do LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO TABLE mytable;
I get Error 1148: The used command is not allowed with this MySQL version
You probably need to start mysql with '--load-infile'. -- Per Jessen, Zürich (2.1°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Thursday 27 Jan 2011 15:21:31 Per Jessen wrote:
Bob Williams wrote:
To the database gurus
I've just started exploring mysql (I'm a db newbie), and run into a 'feature'.
When I do LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO TABLE mytable;
I get Error 1148: The used command is not allowed with this MySQL version
You probably need to start mysql with '--load-infile'.
Yes, I tried that, mysql --load-infile -u root -p but it's an 'unknown option' :( Bob -- Registered Linux User #463880 FSFE Member #1300 GPG-FP: A6C1 457C 6DBA B13E 5524 F703 D12A FB79 926B 994E openSUSE 11.3 64-bit, Kernel 2.6.36.90-desktop, KDE 4.5.3 Intel Core2 Quad Q9400 2.66GHz, 8GB DDR RAM, nVidia GeForce 9600GT -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Bob Williams wrote:
On Thursday 27 Jan 2011 15:21:31 Per Jessen wrote:
Bob Williams wrote:
To the database gurus
I've just started exploring mysql (I'm a db newbie), and run into a 'feature'.
When I do LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO TABLE mytable;
I get Error 1148: The used command is not allowed with this MySQL version
You probably need to start mysql with '--load-infile'.
Yes, I tried that,
mysql --load-infile -u root -p
but it's an 'unknown option' :(
Yes, for the mysql _client_, but you need to start the mysql _server_ with that option. This means editing /etc/init.d/mysql or stopping and starting the server manually. -- Per Jessen, Zürich (1.9°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Thursday 27 Jan 2011 15:56:35 Per Jessen wrote:
Bob Williams wrote:
On Thursday 27 Jan 2011 15:21:31 Per Jessen wrote:
Bob Williams wrote:
To the database gurus
I've just started exploring mysql (I'm a db newbie), and run into a 'feature'.
When I do LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO TABLE mytable;
I get Error 1148: The used command is not allowed with this MySQL version
You probably need to start mysql with '--load-infile'.
Yes, I tried that,
mysql --load-infile -u root -p
but it's an 'unknown option' :(
Yes, for the mysql _client_, but you need to start the mysql _server_ with that option. This means editing /etc/init.d/mysql or stopping and starting the server manually.
Ah. OK Bob -- Registered Linux User #463880 FSFE Member #1300 GPG-FP: A6C1 457C 6DBA B13E 5524 F703 D12A FB79 926B 994E openSUSE 11.3 64-bit, Kernel 2.6.36.90-desktop, KDE 4.5.3 Intel Core2 Quad Q9400 2.66GHz, 8GB DDR RAM, nVidia GeForce 9600GT -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Bob Williams wrote:
On Thursday 27 Jan 2011 15:56:35 Per Jessen wrote:
Bob Williams wrote:
On Thursday 27 Jan 2011 15:21:31 Per Jessen wrote:
Bob Williams wrote:
To the database gurus
I've just started exploring mysql (I'm a db newbie), and run into a 'feature'.
When I do LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO TABLE mytable;
I get Error 1148: The used command is not allowed with this MySQL version
You probably need to start mysql with '--load-infile'.
Yes, I tried that,
mysql --load-infile -u root -p
but it's an 'unknown option' :(
Yes, for the mysql _client_, but you need to start the mysql _server_ with that option. This means editing /etc/init.d/mysql or stopping and starting the server manually.
Ah. OK
Alternatively, you prepare INSERT sql statements in a file and process those using mysql -u <user> -p -D<database> <sqlfile -- Per Jessen, Zürich (1.4°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
El 27/01/11 12:56, Per Jessen escribió:
This means editing /etc/init.d/mysql or stopping and starting the server manually.
No!, you should add load-infile to the mysqld section in /etc/my.cnf You must not edit /etc/init.d/mysql unless you have a very good reason, this is not one. ;) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Cristian Rodríguez wrote:
El 27/01/11 12:56, Per Jessen escribió:
This means editing /etc/init.d/mysql or stopping and starting the server manually.
No!, you should add load-infile to the mysqld section in /etc/my.cnf
Very true, a much better place. -- Per Jessen, Zürich (0.0°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Friday 28 Jan 2011 08:37:07 Per Jessen wrote:
Cristian Rodríguez wrote:
El 27/01/11 12:56, Per Jessen escribió:
This means editing /etc/init.d/mysql or stopping and starting the server manually.
No!, you should add load-infile to the mysqld section in /etc/my.cnf
Very true, a much better place.
OK, I'm following all this. What is the syntax for adding load-infile to this file? All the entries seem to be of the form option = value Ah. I see 'skip-locking' on a line by itself. So, I just need to add 'load- infile' under that? My other problem has been getting the server running. Because I'd forgotten the root password (I must have set it a long time ago), I've been through various convolutions involving stopping the server, running mysql_safe --skip- grant-tables, and updating the password. Mysql_safe dumps me at a promptless console, which seems to accept input commands, but doesn't honour exit or quit, so I end up killing it by closing the terminal. This doesn't seem very satisfactory, as shown by the fact that the server won't restart. I'm starting and stopping it in Yast > System Services (Runlevel). I think I might have to try that old rebooting trick. :-o Thanks for your help. Bob -- Registered Linux User #463880 FSFE Member #1300 GPG-FP: A6C1 457C 6DBA B13E 5524 F703 D12A FB79 926B 994E openSUSE 11.3 64-bit, Kernel 2.6.36.90-desktop, KDE 4.5.3 Intel Core2 Quad Q9400 2.66GHz, 8GB DDR RAM, nVidia GeForce 9600GT -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Bob Williams wrote:
On Friday 28 Jan 2011 08:37:07 Per Jessen wrote:
Cristian Rodríguez wrote:
El 27/01/11 12:56, Per Jessen escribió:
This means editing /etc/init.d/mysql or stopping and starting the server manually.
No!, you should add load-infile to the mysqld section in /etc/my.cnf
Very true, a much better place.
OK, I'm following all this. What is the syntax for adding load-infile to this file? All the entries seem to be of the form
option = value
Ah. I see 'skip-locking' on a line by itself. So, I just need to add 'load- infile' under that?
Yep, that's it. You can optionally use 'load-infile=0' or 'load-infile=1'.
My other problem has been getting the server running. Because I'd forgotten the root password (I must have set it a long time ago), I've been through various convolutions involving stopping the server, running mysql_safe --skip- grant-tables, and updating the password. Mysql_safe dumps me at a promptless console, which seems to accept input commands, but doesn't honour exit or quit, so I end up killing it by closing the terminal. This doesn't seem very satisfactory, as shown by the fact that the server won't restart. I'm starting and stopping it in Yast > System Services (Runlevel).
http://lmgtfy.com/?q=mysql+server+password+reset http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-... -- Per Jessen, Zürich (0.4°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Hi Per, On Friday 28 Jan 2011 10:46:44 Per Jessen wrote:
:)
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting -permissions-unix
Many thanks for the Google tips. I had already done similar searches and found similar resources. My problem is getting the server to start. The reset password instructions say to stop the server by ... --quote-- Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name. You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command: shell> kill `cat /mysql-data-directory/host_name.pid` --end quote-- I found the pid in /var/lib/mysql/localhost.pid but if I try to restart the server (as root), I get barrowhillfarm:~ # /etc/init.d/mysql start Starting service MySQL warning: /var/run/mysql/mysql.sock didn't appear within 30 seconds chmod: cannot access `/var/run/mysql/mysqld.pid': No such file or directory failed IOW, it is looking in /var/run not /var/lib /var/run/mysql contains a 22B file called tmpdir Bob -- Registered Linux User #463880 FSFE Member #1300 GPG-FP: A6C1 457C 6DBA B13E 5524 F703 D12A FB79 926B 994E openSUSE 11.3 64-bit, Kernel 2.6.36.90-desktop, KDE 4.5.3 Intel Core2 Quad Q9400 2.66GHz, 8GB DDR RAM, nVidia GeForce 9600GT -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Bob Williams wrote:
My problem is getting the server to start. The reset password instructions say to stop the server by ...
--quote-- Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name.
You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command:
shell> kill `cat /mysql-data-directory/host_name.pid` --end quote--
I found the pid in /var/lib/mysql/localhost.pid
Hi Bob, hmm, I would have expected to find it in: /var/run/mysql/mysqld.pid
but if I try to restart the server (as root), I get
barrowhillfarm:~ # /etc/init.d/mysql start Starting service MySQL warning: /var/run/mysql/mysql.sock didn't appear within 30 seconds
Check the mysqld log file - probably in /var/log/mysql/mysqld.log or /var/lib/mysql/mysqld.log I would guess something isn't quite right in /etc/my.cnf -- Per Jessen, Zürich (0.6°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Friday 28 Jan 2011 14:24:45 Per Jessen wrote:
Bob Williams wrote:
My problem is getting the server to start. The reset password instructions say to stop the server by ...
--quote-- Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name.
You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command:
shell> kill `cat /mysql-data-directory/host_name.pid` --end quote--
I found the pid in /var/lib/mysql/localhost.pid
Hi Bob,
hmm, I would have expected to find it in:
/var/run/mysql/mysqld.pid
but if I try to restart the server (as root), I get
barrowhillfarm:~ # /etc/init.d/mysql start Starting service MySQL warning: /var/run/mysql/mysql.sock didn't appear within 30 seconds
Check the mysqld log file - probably in /var/log/mysql/mysqld.log or /var/lib/mysql/mysqld.log
I would guess something isn't quite right in /etc/my.cnf
You're quite right. It didn't like the load-infile option (unknown) so it kept aborting. OK, I'm only going to be creating small databases, so I can insert each record one by one. However, after removing the offending line from /etc/my.cnf, I still can't start the server. The last few lines of the mysqld.log say ... 110128 14:51:30 [ERROR] Plugin 'InnoDB' init function returned error. 110128 14:51:30 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 110128 14:51:30 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 110128 14:51:30 [ERROR] Do you already have another mysqld server running on port: 3306 ? 110128 14:51:30 [ERROR] Aborting 110128 14:51:30 [Note] /usr/sbin/mysqld: Shutdown complete 110128 14:51:30 mysqld_safe mysqld from pid file /var/run/mysql/mysqld.pid ended but I can't see that there's another server instance running. If there was, I should be able to use it? Bob -- Registered Linux User #463880 FSFE Member #1300 GPG-FP: A6C1 457C 6DBA B13E 5524 F703 D12A FB79 926B 994E openSUSE 11.3 64-bit, Kernel 2.6.36.90-desktop, KDE 4.5.3 Intel Core2 Quad Q9400 2.66GHz, 8GB DDR RAM, nVidia GeForce 9600GT -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
Bob Williams wrote:
Check the mysqld log file - probably in /var/log/mysql/mysqld.log or /var/lib/mysql/mysqld.log
I would guess something isn't quite right in /etc/my.cnf
You're quite right. It didn't like the load-infile option (unknown) so it kept aborting.
If isn't a typo, that can only be because the mysqld binary was compiled without the option to enable load-infile.
OK, I'm only going to be creating small databases, so I can insert each record one by one.
You can also batch the INSERT statements in a file, and run them from stdin: mysql <insert-statements.txt
However, after removing the offending line from /etc/my.cnf, I still can't start the server. The last few lines of the mysqld.log say ...
110128 14:51:30 [ERROR] Plugin 'InnoDB' init function returned error. 110128 14:51:30 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
That is weird.
110128 14:51:30 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 110128 14:51:30 [ERROR] Do you already have another mysqld server running on port: 3306 ? 110128 14:51:30 [ERROR] Aborting
110128 14:51:30 [Note] /usr/sbin/mysqld: Shutdown complete
110128 14:51:30 mysqld_safe mysqld from pid file /var/run/mysql/mysqld.pid ended
but I can't see that there's another server instance running. If there was, I should be able to use it?
Almost certainly, yes. You can check if someone is already listening on port 3306: "netstat -ltn". Also, "pidof mysqld" will tell you the pid if any running mysqld process. -- Per Jessen, Zürich (0.2°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Friday 28 Jan 2011 15:43:26 Per Jessen wrote:
Bob Williams wrote:
Check the mysqld log file - probably in /var/log/mysql/mysqld.log or /var/lib/mysql/mysqld.log
I would guess something isn't quite right in /etc/my.cnf
You're quite right. It didn't like the load-infile option (unknown) so it kept aborting.
If isn't a typo, that can only be because the mysqld binary was compiled without the option to enable load-infile.
OK, I'm only going to be creating small databases, so I can insert each record one by one.
You can also batch the INSERT statements in a file, and run them from stdin:
mysql <insert-statements.txt
However, after removing the offending line from /etc/my.cnf, I still can't start the server. The last few lines of the mysqld.log say ...
110128 14:51:30 [ERROR] Plugin 'InnoDB' init function returned error. 110128 14:51:30 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
That is weird.
110128 14:51:30 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 110128 14:51:30 [ERROR] Do you already have another mysqld server running on port: 3306 ? 110128 14:51:30 [ERROR] Aborting
110128 14:51:30 [Note] /usr/sbin/mysqld: Shutdown complete
110128 14:51:30 mysqld_safe mysqld from pid file /var/run/mysql/mysqld.pid ended
but I can't see that there's another server instance running. If there was, I should be able to use it?
Almost certainly, yes. You can check if someone is already listening on port 3306: "netstat -ltn".
Also, "pidof mysqld" will tell you the pid if any running mysqld process.
Hurrah! Many thanks, Per, there were two pids. Killed both, server starts OK. I've learnt a lot, thank you for your patience. Bob -- Registered Linux User #463880 FSFE Member #1300 GPG-FP: A6C1 457C 6DBA B13E 5524 F703 D12A FB79 926B 994E openSUSE 11.3 64-bit, Kernel 2.6.36.90-desktop, KDE 4.5.3 Intel Core2 Quad Q9400 2.66GHz, 8GB DDR RAM, nVidia GeForce 9600GT -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On 01/28/2011 10:10 AM, Bob Williams wrote:
Almost certainly, yes. You can check if someone is already listening on
port 3306: "netstat -ltn".
Also, "pidof mysqld" will tell you the pid if any running mysqld process. Hurrah! Many thanks, Per, there were two pids. Killed both, server starts OK.
I've learnt a lot, thank you for your patience.
Bob
Bob, Another trick in ~/.my.cnf (if your user and mysql install is secured) is to set your login info in the file (then set the appropriate file permission) to allow you to use mysql without having to provide the '-u user -p' string every time you want to do something. It is very handy. Just add the following to ~/.my.cnf: [mysqladmin] password = yourMySQLUserPass user = yourMySQLUser [mysql] password = yourMySQLUserPass user = yourMySQLUser [mysqldump] password = yourMySQLUserPass user = yourMySQLUser and, of course, replace the yourMySQL... data with the actual values. Then all you need to do is type: mysql <whatever> and you are good to go... -- David C. Rankin, J.D.,P.E. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
David, On Friday 28 Jan 2011 17:10:15 David C. Rankin wrote:
Bob,
Another trick in ~/.my.cnf (if your user and mysql install is secured) is to set your login info in the file (then set the appropriate file permission) to allow you to use mysql without having to provide the '-u user -p' string every time you want to do something. It is very handy. Just add the following to ~/.my.cnf:
[mysqladmin] password = yourMySQLUserPass user = yourMySQLUser
[mysql] password = yourMySQLUserPass user = yourMySQLUser
[mysqldump] password = yourMySQLUserPass user = yourMySQLUser
and, of course, replace the yourMySQL... data with the actual values. Then all you need to do is type:
mysql <whatever>
and you are good to go...
Many thanks, all useful stuff. Having fun INSERTING all the members of our sailing club. There's only 30 ATM, so once it's done, it's done :) Bob -- Registered Linux User #463880 FSFE Member #1300 GPG-FP: A6C1 457C 6DBA B13E 5524 F703 D12A FB79 926B 994E openSUSE 11.3 64-bit, Kernel 2.6.36.90-desktop, KDE 4.5.3 Intel Core2 Quad Q9400 2.66GHz, 8GB DDR RAM, nVidia GeForce 9600GT -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
On Thu, 2011-01-27 at 15:39 +0000, Bob Williams wrote:
On Thursday 27 Jan 2011 15:21:31 Per Jessen wrote:
Bob Williams wrote:
To the database gurus
I've just started exploring mysql (I'm a db newbie), and run into a 'feature'.
When I do LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO TABLE mytable;
I get Error 1148: The used command is not allowed with this MySQL version
You probably need to start mysql with '--load-infile'.
Yes, I tried that,
mysql --load-infile -u root -p
but it's an 'unknown option' :(
If it's a valid SQL command file, rename to .sql then try mysql < filename.sql. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org For additional commands, e-mail: opensuse+help@opensuse.org
participants (5)
-
Bob Williams -
Cristian Rodríguez -
David C. Rankin -
Mike McMullin -
Per Jessen