using mysql client to read a file to create a table in a database in a mysql server

Discussion in 'Ruby' started by julian, Mar 28, 2006.

  1. julian

    julian Guest

    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
    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' <using
    password: NO>
    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
    julian, Mar 28, 2006
    1. Advertisements

  2. julian

    Avatar Guest

    have you tried mysql -u <username> -p <password> ?
    Avatar, Mar 28, 2006
    1. Advertisements

  3. julian

    julian Guest

    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, Mar 28, 2006
  4. julian

    Avatar Guest

    appologies there was an error in my last post anyway.

    ERROR: 1045 <28000>: Access denied for user 'ODBC'@'localhost' <using
    password: NO>

    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

    instead try

    "mysql -u <your username here eg. root> -p depot_development
    <db/create.sql return"

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


    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
    Avatar, Mar 28, 2006
  5. julian

    julian Guest

    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
    julian, Mar 28, 2006
  6. julian

    julian Guest

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

    and I get ERROR 1045 <28000>: Access denied for user
    'depot_development'@'localhost' <using password:NO>

    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
    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!
    julian, Mar 28, 2006
  7. julian

    Avatar Guest

    -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.
    Avatar, Mar 29, 2006
  8. julian

    julian Guest

    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 -u root -p depot_development

    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..
    julian, Mar 29, 2006
  9. julian

    Avatar Guest

    the physical database files are normally in the "data" dir in the mysql

    eg on my machine

    you shouldnt relaly need them though...

    some what confused about what you are trying to achieve, when you
    import the sql file the data is then stored in the database, you can
    see if the database is there by logging into mysql

    "mysql -h -u root -p"

    when you have the mysql prompt (mysql> ) type

    show databases;

    this should then give you an output along these lines....

    mysql> show databases;
    | Database |
    | information_schema |
    | mysql |
    | test |
    | depot_development |
    4 rows in set (0.02 sec)

    like i say though, im not too sure of what you are trying to achieve,
    if you speecify a bit more maybe i can help you further.
    Avatar, Apr 6, 2006
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JL
  2. -
    Alan Krueger
    Jul 29, 2005
  3. keithb
    Jun 7, 2006
  4. Jeffrey H. Coffield
  5. Christopher Aldridge
    Christopher Aldridge
    Aug 9, 2005
  6. Alex Dowad
    Michel Demazure
    May 1, 2010
  7. JL
    Tim Chase
    Dec 14, 2013
  8. Sam
    Mark Lawrence
    Jan 17, 2014