Avatar of rgb192
rgb192
Flag 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
MySQL ServerDatabasesMicrosoft SQL Server

Avatar of undefined
Last Comment
rgb192

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
johanntagle

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
johanntagle

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;
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>
Your help has saved me hundreds of hours of internet surfing.
fblack61
johanntagle

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
johanntagle

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
Tomas Helgi Johannsson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
johanntagle

I already gave it to you above.  here it is again:

mysql -u root -p -h localhost xcart444
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>
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>
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
rgb192

ASKER
same error when I do

mysql -u root;
johanntagle

How many times do we have to say you do not run that inside MySQL prompt but on windows command line.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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>
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
johanntagle

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.
rgb192

ASKER
I tried

C:\Program Files\MySQL\

but there is no folder

>> path

should I look in 'environment variables'
johanntagle

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
rgb192

ASKER
there is no
C:\Program Files\MySQL\
folder
johanntagle

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.
johanntagle

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
johanntagle

Turns out there's also a C:\xampp\mysql\bin\mysql.exe so just try which one will work.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
johanntagle

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.
rgb192

ASKER
thanks for answer
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes