Link to home
Start Free TrialLog in
Avatar of rgb192
rgb192Flag for United States of America

asked on

running a large .sql file

I have a 500mb sql file


I am trying to open and put in sql manager for mysql ems, but says out of memory error

I also tried with mysql workbench


I can open the file in notepad

or large text file viewer  but it is too large to run


maybe I could use a sql server manager to use with mysql


what should I do
ASKER CERTIFIED SOLUTION
Avatar of johanntagle
johanntagle
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rgb192

ASKER

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 86
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> mysql -u root -p -h localhost xcart444
    ->


I am trying to use database xcart444 on localhost for user root with no password

I first want to select * from table
no what I gave you was meant to be run in the windows command line, calling the mysql client program.  Since you are already in the program, you can do:

use xcart444;
select * from table;
Avatar of rgb192

ASKER

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 87
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.16    |
+-----------+
1 row in set (0.04 sec)

mysql> use xcart;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'xcart'
mysql> mysql -u root -p -h localhost xcart444;
ERROR 1064 (42000): 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 'mysql
 -u root -p -h localhost xcart444' at line 1
mysql>
Again you DO NOT run "mysql -u root -p -h localhost xcart444;" on the mysql prompt but the WINDOWS command prompt.

 Access denied for user ''@'localhost' to database 'xcart' just means that - an anonymous user is not allowed to access the xcart database.  Log on as the root user, then grant privileges to another user - easiest would be:

grant all on xcart444 .* to username@localhost identified by 'password';

See http://dev.mysql.com/doc/refman/5.5/en/grant.html
Go to your windows command line, then execute:

mysql -u root -p -h localhost xcart444

You may need to include the full location of your mysql client program when you call it.  Once inside, you should be able to select from your tables
SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rgb192

ASKER

>>
 Access denied for user ''@'localhost' to database 'xcart' just means that - an anonymous user is not allowed to access the xcart database.  Log on as the root user, then grant privileges to another user - easiest would be:

grant all on xcart444 .* to username@localhost identified by 'password';




how to log on as the root user
I already gave it to you above.  here it is again:

mysql -u root -p -h localhost xcart444
Avatar of rgb192

ASKER

mysql> mysql -u root -p -h localhost xcart444;
ERROR 1064 (42000): 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 'mysql
 -u root -p -h localhost xcart444' at line 1
mysql>
Avatar of rgb192

ASKER

mysql> mysql -u root -p -h localhost xcart444;
ERROR 1064 (42000): 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 'mysql
 -u root -p -h localhost xcart444' at line 1
mysql> mysql -u root -p -h localhost;
ERROR 1064 (42000): 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 'mysql
 -u root -p -h localhost' at line 1
mysql>
Avatar of rgb192

ASKER

same error when I do

mysql -u root;
How many times do we have to say you do not run that inside MySQL prompt but on windows command line.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rgb192

ASKER

using cmd.exe



Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Acer>mysql -u root;
'mysql' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\Acer>mysql -u root -p
'mysql' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\Acer>mysql -u root -p;
'mysql' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\Acer>
Again I also said above that you may have to look for the actual path to the mysql client binaries and include it when you call the program.

Please try to understand the error message you are getting and not just copy+paste here for someone else to interpret it for you.  A single "'mysql' is not recognized as an internal or external command, operable program or batch file." should have already told you that Windows cannot find the mysql client program.  That is a standard Windows error message, not a MySQL issue.
Avatar of rgb192

ASKER

I tried

C:\Program Files\MySQL\

but there is no folder

>> path

should I look in 'environment variables'
See if there's a C:\Program Files\MySQL\bin and if there are .exe files in there.  One of them is the one you need
Avatar of rgb192

ASKER

there is no
C:\Program Files\MySQL\
folder
Sorry I thought you said there's one but still cannot find the needed files.  Anyway, I assisted your "Where is Mysql" question clarifying to the other experts what you actually need.
Based on the output in your other question it looks lilke it's C:\wamp\bin\mysql\mysql5.5.20\bin\mysql.exe

Maybe you can go to that directory then run it.  Again it's "mysql -u root -p -h localhost xcart444" without any semicolon.  Semicolon is only for ending sql statements, and this is not an SQL statement but a windows executable file being called with parameters.
Turns out there's also a C:\xampp\mysql\bin\mysql.exe so just try which one will work.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
From my first reply at the top:


Do you just need to run the whole SQL script?  If so why not just run mysql command line:

mysql -u username -p -h hostname databasename < filename.sql

I realize now the author must have been confused when I said "mysql command line" -> I meant mysql command line client which is run on the OS command line.  But I already clarified that many times over in succeeding posts.
Avatar of rgb192

ASKER

thanks for answer