Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Okay, I'm sure this will turn out to be something dumb, but then I'm a dumb n00b DBA.

I've got a largish table, about 163 million rows and something like 44 GiB. After some experiments with mysqldump for backups, I attempted to build a tab-separated output using select in batch mode.

I tried

echo 'select * from node;' | mysql -u xx -p -B database > file

and it terminates with the message "Killed".

so I tried logging into mysql in batch more, same query, same result.

I tried logging in in the normal fashion, and doing select * from node;

Still the same result: "Killed". HOWEVER

create table temp_node (select * from node);

works fine.

So I'm clearly missing something basic, but googling hasn't helped. Have pity on a n00b and tell me what's happening?

Please note the question isn't about running the command per se, it's that when I run the command, or execute select * from node; from the mysql> prompt, mysql terminates with the message "Killed."

Oh, and how I can export that damned table for loading later?

share|improve this question

2 Answers 2

Okay, I thought that was a familiar message. That's from LINUX saying "process ran out of memory."

Well, there's not a lot more to say. The mysql process itself runs out of memory and is terminated by the OS. I dn't have a good feel for the implementation, but it appears that it's trying something like pulling the whole table into memory to do the select *.

share|improve this answer
    
You should also check the mysql error log and append the error message (if there was one.) –  ypercube Feb 6 at 23:52

I see two issues

  1. You should specify the target database
  2. You should supply the password

For example

  • user is root
  • password is rootbeer
  • target DB is mydb

You should be running it like this:

echo 'select * from node;' | mysql -uroot -prootpass -Dmydb -B > file

Using just -p alone would cause mysql to hang in the process without an open mechanism for you to key in the password. Even if you could key in the password, you still need a target database.

Another way to express this is the following:

echo 'select * from mydb.node;' | mysql -uroot -prootpass -B > file

As for exporting the table, perhaps doing a mysqldump

mysqldump -uroot -prootpass mydb node > node_table.sql

This creates an SQL script to drop the table, create the table , insert data into the table, and index it.

Give it a Try !!!

UPDATE 2014-02-06 14:04 EST

SUGGESTION

Change from this

echo 'select * from mydb.node;' | mysql -uroot -prootpass -B > file

to this

mysql -uroot -prootpass -ANe"select * from mydb.node" -B > file
share|improve this answer
    
Sorry, Rolando, I was just abbreviating for conciseness. I did indeed have the database named; also, with -p and no password, at least on UNIX if goes to /dev/tty for the password, so I get a prompt. –  Charlie Martin Feb 6 at 18:37
    
As here: $ mysql -u fred -p Enter password: –  Charlie Martin Feb 6 at 18:38
    
Please edit the question so that you show how you specified the target database at the time of execution. –  RolandoMySQLDBA Feb 6 at 18:39
    
I've edited the question. Note that the mysql command line tool terminates with message "Killed." even when I run the select from the mysql prompt. –  Charlie Martin Feb 6 at 18:44
    
You're still missing the point: the query select * from node; won't complete even typed from the mysql command line. The mysql tool terminates. –  Charlie Martin Feb 6 at 19:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.