MySQL dump file import help needed

Destry · 2011-04-29 09:11:05

I’m creating a secured dev spot for a client project (WordPress, Ha!). Using Joyent’s virtualmin tools, I’ve created the database (empty), assigned a user (plus my master account should work too), and have dump file at the ready.

But, I can’t seem to import the darn thing. The problem with phpMyAdmin seems to be because the dump file is too large. But I also can’t seem to use command-line, and this is where I don’t understand why. I can ssh into my Joyent account just fine. I can list the databases and see the new one there. I can even export a couple of existing databases, but I can’t seem to import the new one.

The command I’m using, mentioned all over the web, is mysql -u[USERNAME] -p [DB_NAME] < dump.sql. In this case I leave the password out and add it when asked. (If I add the password, I’m still asked for it again, so it doesn’t seem to matter either way.)

The error I get is always something like ERROR 1044 (42000) at line 22: Access denied for user, but both the username and password are correct.

I even ran this on the command line mysql -u [USERNAME] -p[PASSWORD], and it gave me the “Welcome to the MySQL monitor…”, etc. So the credentials must be good.

The dump file was from a msql db version 5.0.77, while my Joyent is version 5.0.90 so I doubt that’s the problem.

Any other “Doh!” things to try before I start a Joyent support ticket? (Btw, I’ve never really liked the Joyent community vibe, which is why I’m asking here.)

Dragondz · 2011-04-29 09:24:49

Hi

have you see into the dump file if it set the database itself: uses [database]?

(It s dumb question but sometimes something obvious happen in those cases)

Destry · 2011-04-29 09:39:04

Dragondz wrote:

(It s dumb question but sometimes something obvious happen in those cases)

Hi Dragondz. Nothing is a dumb question when dealing with me. :)

That could be the case, though I’m not sure how I might check that out. Assuming it were true, would I then need to delete the database I created to let the dump file generate it on it’s own?

Dragondz · 2011-04-29 09:50:10

hi destry

if you find that line in the dump file juste delete it and try import again.

(Open the dump file using an text editor like notepad++ or VIM and make the changes).

Cheers

jakob · 2011-04-29 12:01:50

Another idea: if it is just timing out during importing, try importing a few tables at a time.

Destry · 2011-05-03 12:46:58

Thanks, Jakob.

That would have been my next move, but I came across some info to check my “max_allowed_packet” value in the etc/my.cnf file, and to increase that if it was missing. Apparently, if that line is missing in the file, it means your packet is set at a default of 1M. To increase you add the needed line with whatever value (eg., max_allowed_packet=35M). Reboot MySQL and it kicks-in.

When I tried to load DB again, it worked. All a little over my head, but I’m not complaining.

Note this was a packet set in the database server, not the database client. Apparently you can and/or need to increase them both, but just doing it in the server was good enough for me.

Destry · 2011-11-29 00:09:23

My old MBP died the other day, so I had to get a new one and start fresh with Lion (not really complaining). But I had to re-jigger some stuff, of course, to get local dev working (using the native installs), but running into another odd problem with dump imports.

I keep getting an error…

ERROR 1048 (23000) at line 119: Column ‘Body’ cannot be null

What I discovered so far is that only 4 of the 19 or so tables load. The fourth table in this case is the Textpattern table, and if I look at the Body data in the dump file, it shows there are indeed three articles that seem to be indicated as “NULL” in the “Body” fields, though it’s hard to match it up correctly.

But the odd thing is those articles do have data in the body field, so I’m not sure why it’s showing like that in the file, thus being called out as an error (if I’ve even pinpointed the location right).

Any ideas are welcome. Is there a way to force an import over the top of a NULL like that? I’ve scoured the web all day and I“m thinking there’s probably not, but I“m hoping someone will tell me different.

Btw, the dumps were created in mysql version 5.0.45 and I’m importing them into 5.5.18… just working on the first one so far.

Destry · 2011-11-29 10:34:10

Got it working.

Destry · 2015-09-10 12:19:31

Recycling an old thread for a similar issue.

I’m using this command to import a db on localhost:

mysql -u root -p -h localhost db_name < db_name.sql

Which should be asking for my password at that point, like it has in the past, but instead I get this… whatever it’s called:

mysql -u root -p -h localhost db_name < db_name.sql
    -> wtf?
    -> \c

What’s going on? Why am I getting the arrow?

The same thing happens if I use this, in case you’re wondering:

mysql -uroot -p db_name < db_name.sql

ruud · 2015-09-10 13:35:16

What happens if you do mysql -u root -p? Does it then ask you for a password?

gaekwad · 2015-09-10 13:54:44

Destry wrote #294646:

I’m using this command to import a db on localhost:

mysql -u root -p -h localhost db_name < db_name.sql...

Which should be asking for my password at that point, like it has in the past, but instead I get this… whatever it’s called:

mysql -u root -p -h localhost db_name < db_name.sql...

What’s going on? Why am I getting the arrow?

That looks like you’re already in mysql, rather than the shell. From the top:

mysql runs the MySQL Server component admin thingy
-u root says to run mysql as the MySQL root user
-p is a password prompt
-h specifies the host
db_name is the database
< is the indicator to import the next thing…
…which is a dump file called db_name.sql

If you’re already in mysql, you can try this:

use db_name;
source db_name.sql;

Note the ; at the end of each line — that’s effectively a confirmation that you want to run the stuff before it and without them you get a continuation over multiple lines, which is indicated by the -> you’re getting. Any help?

Destry · 2015-09-10 14:17:58

ruud wrote #294659:

What happens if you do mysql -u root -p? Does it then ask you for a password?

Sorry, ruud. I’m stupid. Pete was right. I was logged into the mysql shell when trying to do that.

All good now. Thanks, Pete.

Textpattern CMS

Textpattern CMS support forum

#1 2011-04-29 09:11:05