# Using mysql client to read a file to create a table in a dat

**URL:** <https://www.ruby-forum.com/t/using-mysql-client-to-read-a-file-to-create-a-table-in-a-dat/56120>\
**Category:** Ruby\
**Created:** [March 28, 2006, 1:02pm UTC](https://www.ruby-forum.com/t/using-mysql-client-to-read-a-file-to-create-a-table-in-a-dat/56120 "2006-03-28T13:02:02Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![julian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/74df32/32.png) [@julian](https://www.ruby-forum.com/u/julian)\
**Post date:** [March 28, 2006, 1:02pm UTC](https://www.ruby-forum.com/t/using-mysql-client-to-read-a-file-to-create-a-table-in-a-dat/56120/1 "2006-03-28T13:02:02Z")

</div>

I have a little tech problem:  
I have a lap top running xp home service pack two  
and I have installed ruby 1.8.2 and rails 1.0.0 I also have mysql  
4.1.16-nt via tcp/ip running on the laptop, so all three pieces of  
software are on the laptop.

Background :this version of mysql comes with a client, but  
unfortunately the client does not talk to the server in its standard  
configuration because the mysql server has enhansed security, so the  
new style passwords are not understood if the client uses them, (which  
it does by default). any way as you are may be aware you can change the  
password so that the client can connect to the server… but that’s  
another story…  
I have used SciTE to create a file called create .sql , this file is a  
number of lines of sql to create a table. Now ordinarilly I would  
create a table using Mysql Query browser to issue some lines of sql to  
create the table making sure I use the correct datasbase of course, but  
this time I am wanting to use the mysql client to execute the code in  
the create.sql file which is in the db directory of my rails  
application.  
so i type from the prompt  
mysql depot\_development \<db/create.sql return  
depot\_development is the the database I want to put the table into, and  
it was created previously by me.  
I get back:  
ERROR: 1045 \<28000\>: Access denied for user ‘ODBC’@‘localhost’   
mysql error messages are sometimes confussing-- I think??  
I wonder what’s going on, can any one help me please ta v much  
Julian K Bowler

---

<div class="post-metadata">

**Author:** ![julian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/74df32/32.png) [@julian](https://www.ruby-forum.com/u/julian)\
**Post date:** [March 28, 2006, 1:07pm UTC](https://www.ruby-forum.com/t/using-mysql-client-to-read-a-file-to-create-a-table-in-a-dat/56120/2 "2006-03-28T13:07:22Z")

</div>

have you tried mysql -u -p ?

---

<div class="post-metadata">

**Author:** ![julian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/74df32/32.png) [@julian](https://www.ruby-forum.com/u/julian)\
**Post date:** [March 28, 2006, 1:23pm UTC](https://www.ruby-forum.com/t/using-mysql-client-to-read-a-file-to-create-a-table-in-a-dat/56120/3 "2006-03-28T13:23:00Z")

</div>

I appologise, I do not understand your post Aviator please write  
more… I do v much appreciate your responce and will keep an eye on  
this topic over the next couple of days at least… Ta v Much  
what does the syntax you describe do please. ta once more 4 your help  
julian

---

<div class="post-metadata">

**Author:** ![julian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/74df32/32.png) [@julian](https://www.ruby-forum.com/u/julian)\
**Post date:** [March 28, 2006, 1:54pm UTC](https://www.ruby-forum.com/t/using-mysql-client-to-read-a-file-to-create-a-table-in-a-dat/56120/4 "2006-03-28T13:54:08Z")

</div>

appologies there was an error in my last post anyway.

ERROR: 1045 \<28000\>: Access denied for user ‘ODBC’@‘localhost’

your mysql seems to be setup to not allow un auth’ d action

you say you tried the command “mysql depot\_development \<db/create.sql  
return”

instead try

“mysql -u -p depot\_development  
\<db/create.sql return”

you should then be prompted for the password the account specified.

eg.

```
avatar:/usr/local/mysql/bin root# ./mysql create.sql

```

i receive the same error as you

```
ERROR 1045 (28000): Access denied for user 'root'@'localhost'

```

(using password: NO)

however if I use:

```
avatar:/usr/local/mysql/bin root# ./mysql -u root -p create.sql

```

I get promted for the password:

```
Enter password:

```

please try this, if this still does not ork i shall try to help you  
further.

---

<div class="post-metadata">

**Author:** ![julian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/74df32/32.png) [@julian](https://www.ruby-forum.com/u/julian)\
**Post date:** [March 28, 2006, 2:09pm UTC](https://www.ruby-forum.com/t/using-mysql-client-to-read-a-file-to-create-a-table-in-a-dat/56120/5 "2006-03-28T14:09:06Z")

</div>

very many thanks for your helpfull responce, I will try this latter on  
today, I am multitasking at mo between ruby and rails and a big web  
design course that I must make progress with as well, Ta V Much 4 your  
help and I am sure I will get further with this with your help v  
graitfull, i will post back to this subject with news… j

---

<div class="post-metadata">

**Author:** ![julian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/74df32/32.png) [@julian](https://www.ruby-forum.com/u/julian)\
**Post date:** [March 28, 2006, 10:39pm UTC](https://www.ruby-forum.com/t/using-mysql-client-to-read-a-file-to-create-a-table-in-a-dat/56120/6 "2006-03-28T22:39:31Z")

</div>

I am still a wee bit confussed, I have tried from the depot prompt  
which is my rails application typing in:  
mysql -u -p deport\_development \<db/creare.sql

and I get ERROR 1045 \<28000\>: Access denied for user  
‘depot\_development’@‘localhost’

depot\_development is the name of the database in my sql it is not a  
user. so I wonder what is the syntax of the mysql command, (which is  
the client version of mysql not the server!?)  
Also because I am using mysql server 4.1.16 the installed client  
software will not connect to the server because when you try the server  
will say authentication required. some where around mysql 4.1.1, i do  
not know exactly, the mysql server will only allow the users that have  
the older, shorter password type. This does seem crazzy but there is i  
guess a senceible reason… which I do not Know…  
I tried writing a php script to simply connect to the database server  
and select a database using root and its password, which is sheba and  
it works just fine so I know that the password that root currently has  
works.  
When you issue a mysql statement from the command line, what is the  
syntax please, and what is the -u and -p you mention, if you type in as  
I say above the mysql client does not ask for a password to connect to  
the mysql server, but why not??

I do not think the problem is the old and new passwords that the mysql  
server accepts I think it is to do with some other problem…  
can you suggest some very simple mysql statements that I can try out  
from the cmd prompt so I can see what works and what does not work?  
Please suggest some articles I can read about how to run a mysql  
command to actually run a flat file containing a bunch of mysql  
statements for creating a simple table?  
I know that I can create the table by typing the commands in to the  
mysql query browser, because I have done this many times and it works  
absolutely fine. I will keep an eye on this particular series of  
postings tommarrow… and if in a few days the trail goes totally stone  
cold may start a new topic… if that is not frowned upon!

---

<div class="post-metadata">

**Author:** ![julian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/74df32/32.png) [@julian](https://www.ruby-forum.com/u/julian)\
**Post date:** [March 29, 2006, 11:54am UTC](https://www.ruby-forum.com/t/using-mysql-client-to-read-a-file-to-create-a-table-in-a-dat/56120/7 "2006-03-29T11:54:08Z")

</div>

-u is user -p is password

the \<\> around root are not needed

do you have some kind of IM software?

if so mail me and i will try to talk you through it.

---

<div class="post-metadata">

**Author:** ![julian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/74df32/32.png) [@julian](https://www.ruby-forum.com/u/julian)\
**Post date:** [March 29, 2006, 8:09pm UTC](https://www.ruby-forum.com/t/using-mysql-client-to-read-a-file-to-create-a-table-in-a-dat/56120/8 "2006-03-29T20:09:30Z")

</div>

The air is clearing, or what ever the expression is.  
When you want to run the mysql client to read or interrpret a flat file  
I think you need some thing like:  
depot\> mysql -h 127.0.0.1 -u root -p depot\_development  
\<db/create.sql

You press return and then the prompt asks for a password, in this case  
it is the root password, I entered that and there was no errors wow…  
thats more like it!!!

The depot\_development is the name of the database, (which I guess must  
exist on the server!?) However the flat file with the sql in myst live  
in the db directory.  
the next question is where is the db directory,  
the answer is under directory depot , under directory ruby.  
The version of mysql server I am using is 4.1.16nt under tcp/ip  
The other problem I had was a syntax error in the create.sql file i had  
as part of the table price decimal(10.2) not null, but is should be  
price decimal(10,2) , i.e. using a comma.  
I am a bit of a newcommer to this so on a massive steep learning curve,  
but you help Avatar was very much appreciated indeed Thanks a lot ta  
brill ta excellent etc…