Bringing in Datafile into Mysql?

[fvs]

I'm trying to bring in a data text file from my Home directory into a Database
in mysql, I tried to use this
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use duckwear
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> load data infile '/Users/franksoranno/mysql.txt'
-> into table cust_num_names
-> fields terminated by','
-> lines terminated by '\n';
ERROR 13: Can't get stat of '/Users/franksoranno/mysql.txt' (Errcode: 13)
mysql>
but seem to fail, I know I'm missing something. Help

[derekhed]

First off, just because you can see the file, doesn't mean that the mysql user can. Matter of fact, it shouldn't be able to look inside your /User/franksoranno/ directory. Try putting the file somewhere public, like /Users/Shared/ and try it then. And of course, while you are at it, check the permissions on your file.

[fvs]

derek, I tried using /Public/Drop\box/DataFile.txt and it still don't work? Even changed permissions.
What is error 13? Thanks for trying.

[derekhed]

Well, as it turns out, the drop box isn't a good place either. It is sort of a one-way mirror, you can put stuff in, but other users can't get it out. Did you try the Users/Shared/ folder?

[fvs]

derek, It worked in that directory, Thank you.
Is that the way i can bring over my Filemaker Pro data? Using /Users/Shared?
Thanks once more, fvs

[derekhed]

Have a look at your MySQL documentation. It should have been installed with the program. Mine is like this:
file:///usr/local/mysql/docs/manual.html#LOAD_DATA

Note the section. It talks about some of the server settings and versions you need enabled to use 'infile'.

[derekhed]

Glad it worked.

I am not up on FileMaker Pro. Anyone else?

[acme.mail.order]

Your file read problem is interesting - when you run mysql from the command line it executes as the logged in user, not the mysql daemon and thus should not have any problems reading a file anywhere in your home folder.

About moving Filemaker to mysql - very simple. Open your database and do any finds that are relevant, sort if it makes sense to do so, then File -> Export the found set. Select comma-separated values on the first screen and the fields you want on the next. Then use the load data infile command in mysql. If you do this a lot, make a script both in filemaker and the shell.

If this is a daily event, use the filemaker script to call the shell script with the Applescript step.

If you have date fields involved you will have to check if filemaker exports the dates formatted or unix timestamp. Some post-export processing might be necessary.

[derekhed]

Not quite as the logged in user, since he couldn't read the file when it was below his home directory.

From the manual:

Quote: For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all

Sounds like the Public directory takes care of it.

[acme.mail.order]

Yep, that would do it. I wonder what the thinking was behind that restriction. I would expect any command I execute in the shell to have at least my permissions.

[fvs]

Guys, When I just use command (mysql) in my terminal it starts up, But it doesn't let me go far.
mysql> show Databases;
+----------+
| Database |
+----------+
| test |
+----------+
1 row in set (0.00 sec)

mysql> use Database
ERROR 1044: Access denied for user: '@localhost' to database 'database'
mysql>
But when I use the command (mysql -u root -p) then enter PW, It opens and reponse to all syntax, Except I can't use it (thus should not have any problems reading a file anywhere in your home folder.) I can't read as you stated.
How can I use to read any file in any folder from mysql? Is their a special syntax?

[acme.mail.order]

open mysql as root and show us the result of the following query:

show grants;

if that gives an error, use;

show grants for 'franksoranno@localhost';

I think you're butting up against system security. If you were using a windows machine it would probably work right away, but then you'd be asking us how to close all the security holes

[fvs]

acme, I tried;
mysql> show grants;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show grants for 'franksoranno@localhost';
ERROR 1145: The host or user argument to GRANT is too long
mysql>
What next?

[acme.mail.order]

running mysql as root and running the shell as root are two separate events. The former gets you access to anything in the sql world, the latter gets you anything in the filesystem.
We want to find out if your user has the appropriate permissions to do stuff. If `franksorrano` is too long we need to work out what part of it mysql is using.

you can also try (connect to mysql as root)

select * from mysql.user;


I would suggest at this point that we go back to the manual and restart from first principles - something is missing.

[fvs]

OK it works at root.

[stetner]

Quote: Originally Posted by fvs mysql> show Databases; +----------+ | Database | +----------+ | test | +----------+ 1 row in set (0.00 sec) mysql> use Database ERROR 1044: Access denied for user: '@localhost' to database 'database' mysql>

'Database' is the title on the column. The actual database above is 'test'. try 'use test;'

[derekhed]

MySQL provides very fine-grained control of the databases through user, database, host, global based permissions and more. Like ACME said, it is best you read up on how you use this all before you start throwing data at your server. The more thought and design you put into it up front, the less time you will spend redoing things later. The manual included in your installation will get you started. Come on back with more questions as you run into them.

[fvs]

derekhed, How do I open the manual in mysql? When I use 'help;' or '\h' for help, I get only a brief syntax of help. Thanks for your help.
Thanks to all you guys whom replied.

[acme.mail.order]

That's all you usually get at the command line - the -h/--help option is intended as a brief syntax reminder.

try 'man mysql' for a slightly more detailed reminder, post #6 above for the local documentation, or http://dev.mysql.com/doc/ for the full, straight-from-the-source instruction manual.

[fvs]

derek, It didn't work for me in terminal or Mysql mode.
file:///usr/local/mysql/docs/manual.html#LOAD_DATA