MySQL batch mode problems

[rsimmons]

I'm trying to create some tables in a database using batch mode. I'm getting the following error when I try to execute the sql:

ERROR 1044 at line 10: Access denied for user: 'xalrubinstudio@scooter.futurequest.net' to database 'xalrubinstudio_2

Line 10 is the first non-comment line in the sql file. It reads:
USE xalrubinstudio_2

I'm typing the following command at the shell prompt:
mysql -u xalrubinstudio -p -h 69.5.6.126 < structure.sql

To clear up a few obvious things:
1) I can and have logged into MySQL using my password.
2) xalrubinstudio_2 does exist.
3) I'm using the following comment symbol: --

I've used batch mode before, and I just can't figure out what I'm doing wrong here. Any ideas?

Thanks,
Raymond

[Terra]

Line 10 is the first non-comment line in the sql file. It reads:
USE xalrubinstudio_2

Try changing that to:
USE xalrubinstudio_2;

of note, the missing semicolon...

You will most likely want to specify one of your databases, so that MySQL can fully validate your credentials:
mysql -u xalrubinstudio -p -h 69.5.6.126 xalrubinstudio_2 < structure.sql

even this will work:
mysql -u xalrubinstudio -p -h 69.5.6.126 xalrubinstudio < structure.sql

since you are changing databases within the batch script...

I would also _highly_ recommend you use the proper MySQL hostname provided to you in your MySQL Activation Letter *instead* of using the IP address of: 69.5.6.126

FutureQuest does retain the right to shift your databases to another backside MySQL engine without notice, and using the provided MySQL hostname is what offers us the flexibility to do so transparently...

--
Terra
--all it takes is one character to ruin a perfectly good script--
FutureQuest

[Terra]

And finally...

I took a peek at the file, and it is clear that you did not upload that file in ASCII mode... It is full of "CRLF" or "\r\n" line endings...

You can fix this in one of three ways (there are many more):
1) Go into your CNC FileManager and use the CRLF strip feature
2) Dowload the file, via FTP, and reupload it in ASCII mode (not Binary mode)
3) At the CLI by doing: perl -pi -e 's#\r\n$#\n#' structure.sql

the mysql command will correctly import dump files with CRLF and it will also allow USE statement to be used without a trailing semicolon, however when it tried to parse the following line:
USE xalrubinstudio_2

without the semicolon, it saw the database name as:
xalrubinstudio_2\r

What I first noticed was your error message that you pasted in, which was missing the ending single quote
-- database 'xalrubinstudio_2

This led me to start backtracking, and sure enough my hypothesis was correct, your import file was full of \r\n terminators...

In short, it was a combination of fire and gasoline... Separate, there should have been no problem, but when you combine them - KaBoom!

--
Terra
--pyrotechnics 101--
FutureQuest

[rsimmons]

I continue to be amazed by the willingness of the FQ staff to go the extra mile in helping clients resolve technical issues. Thanks so much!

I haven't had a chance to upload the file in ASCII format, but I'm 100% certain that will resolve it. Your response makes complete sense. And I know exactly how that file ended up in binary format and I'm frustrated that I let that slip past me

Kind regards,
Raymond Simmons