How can I copy a subset of columns from one InnoDB table to another, without locking?

Question

I have a MySQL instance running on Amazon's RDS. This means that I cannot change any of the binlog settings.

I have a table source, created like this, which contains around 2 billion rows:

CREATE TABLE `source` (
   id INT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

I have another table destination, with the same columns, but the id is a BIGINT:

CREATE TABLE `destination` (
   id BIGINT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

The source table has gaps in the ID that I want to compact. I'd like to copy all rows in source to destination, without the id column, something like this:

INSERT INTO `destination` (value1, value2)
SELECT value1, value2 FROM `source`; 

How can I accomplish this, without locking the source table? The copy itself is going to take an extremely long time, due to the size of the table, and I can't have it locked for that long.

I essentially want to run the above statement with READ UNCOMMITTED isolation, but this isn't possible due to my inability to change any binary log settings.

Answer 1

IF you are not updating or deleting old rows, the way to do it with minimal locking is to do it chunk by chunk. See here for doing chunking for deleting; it can be adapted for your 'copy'. When you get to the end of the copying, stop writes long enough to copy the final chunk and rename:

Then, if your real goal was to change to BIGINT, finish with

RENAME TABLE source TO old,
             destination TO source;
DROP TABLE old;

Answer 2

Alternative way, which possible to use if You do not worry about updates of old rows (same restriction as other answer)

because MySQL lock table for INSERT FROM SELECT and for SELECT INTO table, but at the same time - do not lock for:

• Normal SELECT, like

  SELECT t1.col1, t1.col2, t1.col3 FROM table1 t2 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE t2.id IS NULL

I use ETL Job (Talend) which open flow from source table and write to destination.

From the MySQL point of view - it is just select to external client

Live usage example - on monthly basis, we are prepare data-set for past moth, the reason - by adding few columns on the fly we increase speed of calculation more than hundred times. The source table under high regular INSERT/UPDATE loading but for current period only.

"manual" alternative - store result of query into local (on client machine or S3) csv file, than bulk insert from csv.

ETL just automate this process and exclude intermediate csv file. Many developer tools like MySQL Workbench, Navicat, DBVisualizer (any serious tools) help You to store result of query to local file with different formats.

The benefits of this way:

• it work faster than chunks, because we read data in single step, than we can use Bulk insert.
• it more easy for design any changes on data on the fly

Answer 3

Here's the real problem: Normal SELECT statements against an InnoDB table will not lock any rows, but INSERT INTO...SELECT does. The easiest way around this is to first select the desired rows from the source table into a file, then import the file into the target table.

One way to do this:

SELECT... INTO OUTFILE, then LOAD DATA INFILE, e.g.:

Given this table:

mysql> select * from source_table;
+----+-----------+---------+----------+
| id | columnA   | columnB | columnC  |
+----+-----------+---------+----------+
|  1 | monkey    | mammal  | jungle   |
|  2 | alligator | reptile | swamp    |
|  3 | elephant  | mammal  | savannah |
+----+-----------+---------+----------+
3 rows in set (0.00 sec)

Extract columnA and columnB where columnC = 'swamp' and store the results in a file in the schema subdirectory:

mysql> select NULL, columna, columnB into outfile 'swampanimals.txt' from source_table where columnC='swamp';

The NULL there is a placeholder for the id column. It will be replaced with the auto_increment value when loaded later.

Then load the resulting file into the target table:

mysql> load data infile 'swampanimals.txt' into table target_table;

Assuming that target_table was empty to start with, it will look like this:

mysql> select * from target_table;
+----+-----------+---------+
| id | columnA   | columnB |
+----+-----------+---------+
|  1 | alligator | reptile |
+----+-----------+---------+
1 row in set (0.00 sec)

Given that you're using RDS, may not be able to use SELECT...INTO OUTFILE -- I don't know about that. If you can't use it, you could do something like this instead:

mysql --host=<rdshost> -s -e "SELECT NULL, columnA, columnB FROM source_table WHERE columnC='swamp'" | sed -r "s/NULL/\\\N/" > swampanimals.txt

That sed is the only way I know to get the NULL into the output as a NULL character instead of as the literal string "NULL". Then you would use LOCAL when doing the load data infile:

mysql> load data local infile "swampanimals.txt" into table target_table;

One problem you might have, since you have two billion rows of data, is that the LOAD DATA INFILE could create a very large transaction and cause your ibdata file, which is where the undo logs are stored, to be stretched out. You can work around this by loading the data from the file in chunks. Here's a sweet trick for that, but it requires a modern version of split. Given a large input file called "bigfile" and loading it into the table "target_table" of schema "target_schema":

split -C100M --filter="mysql target_schema -e \"LOAD DATA LOCAL INFILE '/dev/stdin' INTO TABLE target_table\"" bigfile

That will break the file into 100 MB chunks, but chunks will not be split across lines. That way you wouldn't have any transactions > 100 MB in size and your undo logs wouldn't get stretched out.

Refer to: https://dev.mysql.com/doc/refman/5.6/en/select-into.html and https://dev.mysql.com/doc/refman/5.6/en/load-data.html for full details on SELECT...INTO OUTFILE and LOAD DATA INFILE.