MySQL Query with LARGE number of records gets Killed
command-line-interface, mysql
Solution
The mysql client probably runs out of memory.
Use the --quick option to not buffer results in memory.
Problem
I run the following query from my shell : ``` mysql -h my-host.net -u myuser -p -e "SELECT component_id, parent_component_id FROM myschema.components comp INNER JOIN my_second_schema.component_parents related_comp ON comp.id = related_comp.component_id ORDER BY component_id;" > /tmp/IT_component_parents.txt ``` The query runs for a LONG time and then gets KILLED. However if I add `LIMIT 1000`, then the query runs till the end and output is written in file. I further investigated and found (using COUNT(*)) that the total number of records that would be returned are 239553163. Some information about my server is here: MySQL 5.5.27 ``` +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 28800 | +----------------------------+----------+ ``` Here's STATE of the query as I monitored : ``` copying to tmp table on disk sorting results sending data writing to net sending data writing to net sending data writing to net sending data ... KILLED ``` Any guesses what's wrong here ?