LOAD DATA INFILE problem

adavis

I just tried to insert text from a data file via phpMyAdmin. I got this error message:

Database mydatabase - table sQuestions running on localhost
Error

SQL-query :

LOAD DATA LOCAL INFILE '/var/tmp/phpUPRyeC' INTO TABLE sQuestions FIELDS TERMINATED BY '|' ESCAPED BY '\' LINES TERMINATED BY '\r\n'

MySQL said:
Access denied for user: 'myDomain@localhost' (Using password: YES)

I have never gotten this error message before and have used this method of loading large amounts of data from a text file on several occasions. This list happens to be a list of survey questions, but I have used this phpMyAdmin option for lots of different types of data.

Does anyone know why this would have started happening?

Thanks,
Alisa

rachel2004

It looks like you either didn't supply a password or used an incorrect password for the MySQL user account (not your operating system user account) to run the command.

Make sure sure your MySQL user account has the privileges required to run the LOAD DATA INFILE syntax.

adavis

I haven't changed anything on my end, nor have a changed how I log in or what password I'm using. I have the phpMyAdmin folder password protected, but that should affect this.

This is for a school website and I loaded all the students, their class schedules, and course descriptions last August. In addition, I added the course catalog about 2 months ago. I get most of these files in excel and then just convert them to a text file and use phpMyAdmin to load the records into at table of my choice.

What I can't figure out is why it would stop working when I haven't changed anything I was doing, unless the server admin made a change without telling me. I'm able to hit the database from my scripts and I'm able to do everything except a "LOAD DATA LOCAL INFILE" from within phpMyAdmin. I just tried inserting a record and did so with no problem.

rachel2004

Have you tried executing the command from the MySQL client?

adavis

I'm not sure exactly what you're talking about, but I think you're asking if I've written a little script and executed it. I have not tried to do that yet. Since this is "home" work and not "office" work, I've only been working on it during my breaks. I will try it at home tonight and if that doesn't work, then I'll try the other tomorrow night when I have more time. Wednesday nights are generally pretty hectic with not much time to play.

If you're talking about something else that my feeble little brain isn't grasping right now, then please explain what you're talking about and how to do it or where to look to figure it out. I have the SAMS PHP and MySQL Web Development book and several O'Reilly PHP books.

rachel2004

I'm pretty certain that the book you're using will mention the "mysql monitor" (or possibly called the "mysql client").

On Windows, you'd run it form the command line. It's not a GUI. On Linux or Unix or Mac OS X, you'd run it from the shell. Post back after you've had a chance to do some research while at home.

adavis

I found it in my SAMS book.

My desktop is Windows. The server is Linix running Apache. I am assuming that I would run this from the DOS prompt.

rachel2004

Right, from you Windows computer, either Telnet or SSH to the Linux server running MySQL. Yes, on Windows, you'd need to use your command prompt to do all of that. Just check out your books. They will explain better than I can how to connect.

adavis

No luck. I tried to telnet in. I can connect to the server, but after that, everything I enter is not understood. My book says:

Your command prompt might look different depending on the operating system and shell you are using.

I'll mess with it later. Got to get back to the real job that actually pays me money. 😉

mhalloran

Almost all servers have Telnet disabled these days for security reasons. You need to use SSH.

If you have a Linux or Mac machine, open the terminal and type:
ssh -l username hostname.com

If you are on a PC, PenguiNet is a great SSH client (http://www.siliconcircus.com/penguinet/download.php) Once installed, logging in is quite straight forward.

Regardless of how you get in, you need to log in to MySQL to enter commands directly on the server. The command is
mysql -uusername -p databasename_db

There is no space between the u and the username.

When I'm programming, I always like to have mysql open in the terminal and test all queries in there first. Just a personal preference I guess.

Good luck

adavis

I downloaded PenguiNet. Same as telnet. I get logged in. When I start typing in "mysql -uusername -p databasename_db", I type mysql and as soon as I hit the space bar, it comes back and says:

mysql 500 MYSQL not understood.

I remember now that telnet IS turned off on this server.

rachel2004

It's possible that your hosting company has set-up other parameters in order to connect to your MySQL db. For example, they may require that you define your port number.

If you contact your hosting company, they should provide you this info.

adavis

I can't call them. If I do, they may kick us off the server. This is a school site that they are donating space for, but if I ask for any tech support one more time, I think we're gone. They want us to buy our own server, but there's no money in the budget. I just read an email and we still have 86 computers that are PIII's and running Windows 95, that have to be replaced before we can even think about a server.

I'll have to do what I've been doing all along, work around it.

rachel2004

Before you go pounding sand... ;-)

Let try this:

Connect to your web server running MySQL using SSH as you've already done.
From the prompt, type:

whereis mysql

If that works, should see some directory paths like:

/usr/local/mysql
/some/dir/mysql or whatever.

Type the following:

cd /usr/local/mysql

Then try connecting your mysql monitor with either:

mysql db_name -u mysql_username -p

./bin/mysql db_name -u mysql_username -p

If no luck, try the next path that was listed after your whereis command.
If still no luck... revisit phpmyadmin thingy and troubleshoot why it's not working.

Post back your results.

Keep in mind that your mysql_username is not necessarily the same as your username when telnetting or SSH-ing to connect to the web server. They are seperate accounts: one for the web server and another for the MySQL server.

adavis

I tried this, but it was the same as before. As soon as I hit the space bar, it came back saying that '"whatever" is not understood'. And I tried phpMyAdmin at home with the same "Access Denied" message as I was getting at work.

So... I tried writing a simple script to do the LOAD DATA INFILE. After a couple of tries, I got it to "work" in as much as the sql statement was valid, BUT, now I'm back to where I was with phpMyAdmin. :

$sql = LOAD DATA INFILE 'C:\apache\htdocs\personal\ma\data\questions.txt' INTO TABLE sQuestions FIELDS TERMINATED BY '|'

I get this error message:

1045:Access denied for user: 'myaddress@localhost' (Using password: YES)

So, my conclusion is that my sysadmin has changed something on the server so that I can't run this function anymore. Maybe he thinks it a security issue. I think my only option is to write a script with inserts and do it one line at a time. I'm not to hot with reading flat files, so it might be a bit of trial and error before I get it right, but it's a lot easier than typing each question in when someone has already typed the in excel or word.

I may write up a question about this and get the chair of our technology committe who is also a school board member to send it. In the fall, I'll have a thousand or more student records to load.

Thanks for all you advise and help. You guys are great!
Alisa

rachel2004

Alisa, you might be able to convince the school board to pay for the cost of hosting their site with a web hosting company. I don't know where you are in the world, but in the United States, prices are somewhat nominal with about $10-40/month being the norm. Of course, you'd be looking for a hosting company that provides SSH access, PHP 4.3.x, MySQL, and tech support.

I don't want to make any recommendations on where to look for a web hosting company. Maybe some other posters will make some suggestions.

adavis

Rachel,

I'm in the southeast US. When I used to freelance, I hosted my sites at Neureal. They were really inexpensive. I had the minimum *nix package of 3 URLS at the cost as $3.95/month. They have since changed. I think the min pkg is 5 URLS for $6.95/month. They have php/msql which was all I needed at the time. But, I needed steady income so quit the freelancing and got a "real" job with a regular paycheck and vacation and sick leave and a 401K and stock options and on and on... The freelancing didn't work because I didn't know how to market or get clients. I might do better now, but I like my regular paycheck even though I don't like being tied to this chair 40+ hours a week.

The advantage of getting or own server is that we can put anything on it that we want to, including a ssl which I think we will need in the near future. If the school could pay me 2/3's of what I make now, I think I'd jump at it because, I think I could make up the difference on the side with freelance, but they can only pay me 1/2 of what I make now and 1/4 of what I'm worth 😉 and I can't pay a private school tuition for my youngest and college for my oldest and myself since I would need to go back and get my teaching certificate if I did that. Oh well, getting too long.

Thanks,
Alisa