From my web host I have dumped a database to the file janbase.sql (70
MB).

I now want to upload it to mysql on my local machine.

At the windows command prompt I type the command mysql -u root -p. I
am asked for the password and give it. The prompt is now mysql>.

I type: mysql -p -h localhost mt_janbase < janbase.sql;

and get error 1064 (42000).

When I type: mysql - u root -p password mt_janbase < janbase.sql;

I get error 1064 (42000).

In which folder should janbase.sql be when I attempt this? mysql.exe
seems to sit in C:\Program Files\xampp\mysql\bin\.

What am I doing wrong?

I use Windows XP, MySQL 5.0.18.

Regards,

Jan Nordgreen

On Fri, 23 Nov 2007 19:11:57 +0100, damezumari <jannordgreen@gmail.com>  
wrote:

> From my web host I have dumped a database to the file janbase.sql (70
> MB).
>
> I now want to upload it to mysql on my local machine.
>
> At the windows command prompt I type the command mysql -u root -p. I
> am asked for the password and give it. The prompt is now mysql>.
>
> I type: mysql -p -h localhost mt_janbase < janbase.sql;
>
> and get error 1064 (42000).
>
> When I type: mysql - u root -p password mt_janbase < janbase.sql;
>
> I get error 1064 (42000).
>
> In which folder should janbase.sql be when I attempt this? mysql.exe
> seems to sit in C:\Program Files\xampp\mysql\bin\.
>
> What am I doing wrong?

Afaik, 1064 means there is something wrong with the dump itself (syntax  
error). Normally this is followed by the line & a snippet of code near the  
error. So it's not a question of how to import it, but rather what's wrong  
with janbase.sql (reserved words in higher MySQL versions that weren't  
reserved in lower versions are often a cause of this).
-- 
Rik Wasmus

Thank you for your answer!

To test it I used phpmyadmin to export a database on my local machine.
I copied the sql file to C:\Program Files\xampp\mysql\bin\ and tried

mysql -p -h localhost mt_janbase < cmea.sql;
and
mysql - u root -p password mt_janbase < cmea.sql;

after
mysql -u root -p
and typing in the password.

In both cases I got error 1064 (42000) in line 1 and my command
repeated in the error message.

So, I must be doing something wrong! :)

But, I don't know what it is.

Any help is greatly appreciated.

Regards,

Jan Nordgreen

PS:

cmea.sql starts like this:

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 24, 2007 at 04:24 AM
-- Server version: 5.0.18
-- PHP Version: 5.1.1
--
-- Database: `cmea`
--

-- --------------------------------------------------------

--
-- Table structure for table `cmea_membership`
--

CREATE TABLE `cmea_membership` (
  `memberid` int(11) NOT NULL auto_increment,
  `ID` double default '0',

On Sat, 24 Nov 2007 00:36:10 -0800 (PST), damezumari
<jannordgreen@gmail.com> wrote:

>Thank you for your answer!
>
>To test it I used phpmyadmin to export a database on my local machine.
>I copied the sql file to C:\Program Files\xampp\mysql\bin\ and tried
>
>mysql -p -h localhost mt_janbase < cmea.sql;
>and
>mysql - u root -p password mt_janbase < cmea.sql;
>
>after
>mysql -u root -p
>and typing in the password.
>
>In both cases I got error 1064 (42000) in line 1 and my command
>repeated in the error message.
>
>So, I must be doing something wrong! :)
>
>But, I don't know what it is.
>
>Any help is greatly appreciated.
>
>Regards,
>
>Jan Nordgreen
>
>PS:
>
>cmea.sql starts like this:
>
>-- phpMyAdmin SQL Dump
>-- version 2.7.0-pl1
>-- http://www.phpmyadmin.net
>--
>-- Host: localhost
>-- Generation Time: Nov 24, 2007 at 04:24 AM
>-- Server version: 5.0.18
>-- PHP Version: 5.1.1
>--
>-- Database: `cmea`
>--
>
>-- --------------------------------------------------------
>
>--
>-- Table structure for table `cmea_membership`

Perhaps the database is not created?
Add this line:
CREATE DATABASE mt_janbase;
-- 
 (  Kees
  )
c[_] Sometimes looking too long into another's mind
     just feels like plunging the toilet with your bare
     hands. (Michael Rosen)  (#396)

I still can not get it to work even when I just do an export with
phpmyadmin on my local computer and then try to import from the
command line.

This is what I have tried:

1. In phpmyadmin I manually created a database called 'sol' with no
tables.

2. I copied the file below to C:\Program Files\xampp\mysql\bin
\sol01.sql.

CREATE TABLE `table1` (
  `first` varchar(255) collate latin1_general_ci NOT NULL,
  `last` varchar(255) collate latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `table1` VALUES ('Elvis', 'Presley');
INSERT INTO `table1` VALUES ('Doris ', 'Day');

3. At the command prompt:
mysql -u root -p
typed the password alpha
mysql -u root -p alpha sol < sol01.sql;

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 alpha sol < sol01.sql;' at line 1

I use MySQL 5.0.18, Win XP Home Edition, Mozilla Firefox, phpMyAdmin
2.7.0-pl1.

What am I doing wrong?

Regards,

Jan Nordgreen

On 26 Nov, 10:21, damezumari <jannordgr...@gmail.com> wrote:
> I still can not get it to work even when I just do an export with
> phpmyadmin on my local computer and then try to import from the
> command line.
>
> This is what I have tried:
>
> 1. In phpmyadmin I manually created a database called 'sol' with no
> tables.
>
> 2. I copied the file below to C:\Program Files\xampp\mysql\bin
> \sol01.sql.
>
> CREATE TABLE `table1` (
>   `first` varchar(255) collate latin1_general_ci NOT NULL,
>   `last` varchar(255) collate latin1_general_ci NOT NULL
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
>
> INSERT INTO `table1` VALUES ('Elvis', 'Presley');
> INSERT INTO `table1` VALUES ('Doris ', 'Day');
>
> 3. At the command prompt:
> mysql -u root -p
> typed the password alpha
> mysql -u root -p alpha sol < sol01.sql;
>
> 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 alpha sol < sol01.sql;' at line 1
>
> I use MySQL 5.0.18, Win XP Home Edition, Mozilla Firefox, phpMyAdmin
> 2.7.0-pl1.
>
> What am I doing wrong?
>
> Regards,
>
> Jan Nordgreen

You should be typing
mysql -u root -p alpha sol < sol01.sql
at the windows command prompt, not at the mysql> command prompt

> You should be typing
> mysql -u root -p alpha sol < sol01.sql
> at the windows command prompt, not at the mysql> command prompt

Thanks for the help!

I went to the command prompt. Changed the folder to c:\ and typed:

mysql -u root -p alpha sol < sol01.sql

The error message was: "The system cannot find the file specified."

I then moved sol01.sql to c:\ and tried again: mysql -u root -p alpha
sol < sol01.sql

I also tried mysql -u root -p alpha sol < sol01.sql;

Both times I got a long presentation of mysql that started like this:
"MYSQL Ver 14.12..." and went on to explain all its parameters, but no
table was imported.

I still need help to get this to work.

Regards,

Jan Nordgreen

damezumari wrote:
>> You should be typing
>> mysql -u root -p alpha sol < sol01.sql
>> at the windows command prompt, not at the mysql> command prompt
> 
> Thanks for the help!
> 
> I went to the command prompt. Changed the folder to c:\ and typed:
> 
> mysql -u root -p alpha sol < sol01.sql
> 
> The error message was: "The system cannot find the file specified."
> 
> I then moved sol01.sql to c:\ and tried again: mysql -u root -p alpha
> sol < sol01.sql
> 
> I also tried mysql -u root -p alpha sol < sol01.sql;
> 
> Both times I got a long presentation of mysql that started like this:
> "MYSQL Ver 14.12..." and went on to explain all its parameters, but no
> table was imported.
> 
> I still need help to get this to work.
> 
> Regards,
> 
> Jan Nordgreen
> 

Don't specify your password on the command line.  Wait for the prompt 
Alternatively, if you do specify the passwords, there is no space after 
the -p.



-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Thanks!

I removed the space I had after "-p" and it worked!

mysql -u root -palpha sol < sol01.sql

Regards,

Jan Nordgreen

Thanks for all the help to import my 70MB sql file created by an
earlier version of mysql!

I use Windows XP, MySQL 5.0.18 on my local computer.

Here are the steps that worked.

1.
I used putty.exe to export the database on my web host to a folder on
my web host. I called the file janbase.sql. It was about 70MB. My web
host uses MySQL 4.1.11.

2.
I used Filezilla to download janbase.sql from my web host to my local
computer.

3.
I opened janbase.sql in my Alleycode HTML editor. There were about 200
comments like:

/*!40000 ALTER TABLE `citc_description` DISABLE KEYS */

I replaced every case of "/*!" with "-- ", thus turning them into
acceptable comments.

4.
In phpmyadmin I created a new database called mt_janbase.

5.
I copied janbase.sql to c:\.

6.
I went to the windows command prompt and typed:

mysql -u root -palpha mt_janbase<janbase.sql

(note: there can be no space between "-p" and the password "alpha")

--- fin ---

Regards,

Jan Nordgreen