Problems adding AUTO_INCREMENT id column to large MySQL table

Question

Failed Attempt 1

I was trying to import a huge SQL file (with 600 millions records & 239 GB) into a MySQL database with a different schema, in order to get a new column with an "auto-generated ID."

I'm using MySQL Workbench 6.2 on Windows, but it always failed with the error message "MySQL server has gone away" after importing tens of millions of records.

I was using "Server -> Data Import" feature to do the import.

I tried to add

wait_timeout = 288000
max_allowed_packet = 300G

into my.ini inside the folder of MySQL Server, but the same error occurred.

The error log is as follows:

14:16:37 Restoring C:\data.sql
Running: mysql.exe --defaults-file="c:\users\***\appdata\local\temp\5\tmpcfyyxk.cnf"  --protocol=tcp --host=127.0.0.1 --user=shodan --port=8080 --default-character-set=utf8 --comments --database=test2 < "C:\\data.sql"
ERROR 2006 (HY000) at line 13776162: MySQL server has gone away

Operation failed with exitcode 1
16:25:51 Import of C:\data.sql has finished with 1 errors

Failed Attempt 2

If I instead just add a new column in the original table, it just takes too long to execute the SQL query and never succeeds.

If the import issue cannot be solved, is there any way to directly add a new column with auto-generated ID into the huge table?

Answer 1

Rather than the solutions you've been trying, I suggest this alternative:

1. create a new table that duplicates the old one + adds an auto_increment id,
2. copy the data from the old table into the new one, and
3. rename the tables so the new one replaces the old one.

Let's assume your current_table has the columns col1 VARCHAR(255) NOT NULL, which is currently acting as your primary key, and col2 VARCHAR(255) NULL, which is just some other data.

CREATE TABLE new_table (
    id INT UNSIGNED AUTO_INCREMENT,
    col1 VARCHAR(255) NOT NULL, -- Make your old PK a simple not null column
    col2 VARCHAR(255) NULL,
    PRIMARY KEY (id), -- PK moves to the new id column
    INDEX (col1) -- Replace your old PK with an index
) ENGINE=InnoDB;

INSERT INTO new_table (col1, col2) 
SELECT col1, col2 
FROM current_table;

RENAME TABLE current_table TO old_table,
             new_table TO current_table;

This ought to work better than simply adding a column or importing into a new DB. It will also maintain the current table structure and create a replacement, allowing you a fallback if things go awry. When you've validated that the new table structure works as expected, you can drop the old table. If it fails, you can drop the new table and rollback to the old one.

Answer 2

Check the MySQL Error Log to see if the server is crashing and restarting for some reason: https://dev.mysql.com/doc/refman/5.6/en/error-log.html

If that is not the case, the most likely cause is max_allowed_packet. You want to change this on both server and client, to be 1G. You can do that like this.

1. Change setting on the server:

   SET GLOBAL max_allowed_packet_size=1*1024*1024*1024;

2. Add --max_allowed_packet=1G to your command:

   mysql.exe --defaults-file="c:\users***\appdata\local\temp\5\tmpcfyyxk.cnf" --protocol=tcp --max_allowed_packet=1G --host=127.0.0.1 --user=shodan --port=8080 --default-character-set=utf8 --comments --database=test2 < "C:\data.sql"

Answer 3

It's better to use mysqldump & mysql command line utility or

SELECT ... INTO OUTFILE

and

LOAD DATA INFILE

for such operation. Just do it directly on the mysql server, not remotely.

https://dev.mysql.com/doc/refman/5.6/en/load-data.html

If you only need to alter that table to add autoinc column, look at pt-online-schema-change. If you don't need 100% uptime, it can be done just using classic ALTER TABLE statement without any problem. You just need enough free disk space.