5

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?

4

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

  • You should also check the mysql error log and append the error message (if there was one.) – ypercubeᵀᴹ Feb 6 '14 at 23:52
1

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
  • 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 '14 at 18:37
  • As here: $ mysql -u fred -p Enter password: – Charlie Martin Feb 6 '14 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 '14 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 '14 at 18:44
  • 1
    Please run CHECK TABLE node; and tell me if it complains. – RolandoMySQLDBA Feb 6 '14 at 19:28
0

I ran CHECK TABLE and REPAIR TABLE and am no longer getting the "killed" message. Credit goes to @RolandoMySQLDBA but this has not been supplied as an answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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