CODEWITHSUNDEEP
mysqlgoogle-cloud-sql
WNG
WNG

Reputation: 3805

Error 1114 : table is full when inserting an AUTO_INCREMENT in cloudsql

I have a 8Go table in my CloudSQL database that (for now) doesn't have a primary key. It is composed of 52 million rows of 20 columns each.

I would like to add one, since I will remove duplicates and doing so without primary key is too timeconsuming for MySQL

However, I am having issues to make this run, and I'm wondering if I'm actually doing it in the most efficient way.

I wanted to add this primary key using the following line :

 ALTER TABLE mytable ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST

I expected this request to increase the size of my database by a few hundreds MB, and run rather quickly.

However, when I ran the request, I saw the database usage increase a lot ( + 14 GB), and after 2 hours of running time, I saw the following error message :

"Error 1114 : the table 'mytable' is full".

When this error occurs, the database storage usage goes back to normal, but no change has been made to mytable

My questions are : - What is CloudSQL trying to do when I launch my request, and how come it takes him so long ? - What could I do to make my request executed more quickly by CloudSQL ? What am I missing ?

Upvotes: 0

Views: 439

Answers (2)

WNG
WNG

Reputation: 3805

I still have no clue concerning what happened with this request :

ALTER TABLE mytable ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST

But this alternative worked :

  • Creating a copy table with the exact same fields than in mytable plus the auto_increment
  • insert every record of the first table to this new table

This alternative actually increased less the storage than the one I use in my question (which ends up failing)

Upvotes: 0

Herman
Herman

Reputation: 2072

From http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

"If you use "ALTER TABLE mytable AUTO_INCREMENT = N" the MySQL server will create a new table, copy all the data from the old table to the new, delete the old and then rename the new one even though there is no structural change to the table. "

Upvotes: 0

Related Questions