live2travel
Wed 13th Feb '02, 6:32pm
IF you get a 1064 error on attempting to import the backup/dump of your database via the command prompt in Windows 2k Adv Server, here's why:
1: First I made 2 dumps of the database, one from within the board itself, another via the command prompt method, using the same commands you would at a bash prompt in linux/unix.
In my case this was done as follows:
c:\>cd mysql\bin (to put me in the binaries directory where the mysql command progs are located)
then:
c:\mysql\bin>mysqldump -u [myadminusername] -p vbulletin > c:\temp\dump212.sql
(note that the brackets in the string above are not entered, they're just there for instructional purposes.
(you could also call it dump*.txt, it's the same thing)
2. I then verified that I had both dump files, the one from the board, and the one that I created from the prompt. I then uninstalled mysql, shut down my machine, installed the new hard drive, rebooted windows, went into disk management, formatted new drive in NTFS with default cluster sizes and then reinstalled mysql on that new drive now called d:
I setup mysql with all the tweaked settings, then restarted the service, and created the same database as I had before, in this case called vbulletin.
3. I went back to the command prompt and entered:
d:\> cd mysql\bin
then at the new prompt:
d:\> mysql -u [myadminusername] -p vbulletin < d:\dump212.sql
it prompted me for the password which I entered
then got the following error:
ERROR 1064 at line 1: You have an error in your SQL syntax near 'Enter password:
CREATE TABLE access (
userid int(10) unsigned NOT NULL defaul' at line 1
I thought I must have entered something in wrong, so I attempted the same thing, but this time on the dump from the board.
It worked perfectly. Everything restored.
I though, how odd. So I opened up each dump file in WordPad and noticed that the one created from the command prompt had at line 1:
Enter password: # MySQL dump 8.16
You'll see that this file had "Enter password: before the # sign.
The other dump file did not.
So, in essence upon attempting to restore, I was telling mysql to prompt me for a password 2x, once via the command I entered at the prompt
-p
and the file itself was asking for one.
By taking out the "Enter password:" portion of the first line of the command line dump, everything worked perfectly.
I've verified this on 3 different machines running mysql. I can restore all 3 using the backup file without the "Enter password:", but if I just execute the command as instructed in every post on this board, I get the 1064 error.
Hopefully this help someone, or else I've just wasted 10 minutes for nothing.
Mark
Oh, and BTW, upon restoring the command line prompt dump file, any post on the board that had uploaded a picture, upon attempting to view the picture, it's completely garbled. In order to restore the picture, it had to be re-uploaded.
1: First I made 2 dumps of the database, one from within the board itself, another via the command prompt method, using the same commands you would at a bash prompt in linux/unix.
In my case this was done as follows:
c:\>cd mysql\bin (to put me in the binaries directory where the mysql command progs are located)
then:
c:\mysql\bin>mysqldump -u [myadminusername] -p vbulletin > c:\temp\dump212.sql
(note that the brackets in the string above are not entered, they're just there for instructional purposes.
(you could also call it dump*.txt, it's the same thing)
2. I then verified that I had both dump files, the one from the board, and the one that I created from the prompt. I then uninstalled mysql, shut down my machine, installed the new hard drive, rebooted windows, went into disk management, formatted new drive in NTFS with default cluster sizes and then reinstalled mysql on that new drive now called d:
I setup mysql with all the tweaked settings, then restarted the service, and created the same database as I had before, in this case called vbulletin.
3. I went back to the command prompt and entered:
d:\> cd mysql\bin
then at the new prompt:
d:\> mysql -u [myadminusername] -p vbulletin < d:\dump212.sql
it prompted me for the password which I entered
then got the following error:
ERROR 1064 at line 1: You have an error in your SQL syntax near 'Enter password:
CREATE TABLE access (
userid int(10) unsigned NOT NULL defaul' at line 1
I thought I must have entered something in wrong, so I attempted the same thing, but this time on the dump from the board.
It worked perfectly. Everything restored.
I though, how odd. So I opened up each dump file in WordPad and noticed that the one created from the command prompt had at line 1:
Enter password: # MySQL dump 8.16
You'll see that this file had "Enter password: before the # sign.
The other dump file did not.
So, in essence upon attempting to restore, I was telling mysql to prompt me for a password 2x, once via the command I entered at the prompt
-p
and the file itself was asking for one.
By taking out the "Enter password:" portion of the first line of the command line dump, everything worked perfectly.
I've verified this on 3 different machines running mysql. I can restore all 3 using the backup file without the "Enter password:", but if I just execute the command as instructed in every post on this board, I get the 1064 error.
Hopefully this help someone, or else I've just wasted 10 minutes for nothing.
Mark
Oh, and BTW, upon restoring the command line prompt dump file, any post on the board that had uploaded a picture, upon attempting to view the picture, it's completely garbled. In order to restore the picture, it had to be re-uploaded.