1

I am trying to create an index to my table using alter query. My my.cnf file

innodb_data_home_dir = /usr/local/mysql5/data
innodb_data_file_path = ibdata1:60021538816;ibdata2:300M;ibdata3:30000M;ibdata4:10000M;ibdata5:10000M:autoextend
innodb_buffer_pool_instances = 3
innodb_buffer_pool_size = 3G
innodb_additional_mem_pool_size = 8M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 256M
innodb_additional_mem_pool_size = 128M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 100
innodb_file_per_table
innodb_flush_method=O_DIRECT

Still every time my alter query

alter table user add unique index idx_emailHash (emailHash);

giving Table 'user' is full? What am I missing. I am using MySQL 5.6

Some more info

[root@db data]# ll | grep user
    -rw-rw----. 1 mysql mysql       19551 Jun 10 14:33 user.frm
    -rw-rw----. 1 mysql mysql 28412215296 Jun 10 22:58 user.ibd

[root@db data]# ll | grep ibd
-rwxr-xr-x. 1 mysql mysql 60021538816 Jun 10 22:58 ibdata1
-rw-rw----. 1 mysql mysql   314572800 Jun 10 22:20 ibdata2
-rw-rw----. 1 mysql mysql 31457280000 Jun 10 22:33 ibdata3
-rw-rw----. 1 mysql mysql 10485760000 Jun 10 22:51 ibdata4
-rw-rw----. 1 mysql mysql 10485760000 Jun 10 22:51 ibdata5
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Ankit Bansal
  • 61
  • 3

1 Answers1

0

When InnoDB says a table is full, it has nothing to do with the OS. It has to do with filling an undo logs. InnoDB has 128 undo logs but a single operation can fill just undo until it cannot record any more changes to hold during the indexing operation.

I once had a client that had 106GB free inside 2TB of system table file (ibdat1a, ibdata2). My solution was add another file (ibdata3). The client was not using innodb_file_per_table (See my old post ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full)

You are using innodb_file_per_table. Still, undo logs have limits as to the amount of MVCC info it holds.

I have mentioned this situation over the last 10 years in the DBA StackExchange.

Possible Workaround #1

Since you are using MySQL 5.6, trying using Online DDL.

alter table user
    add unique index idx_emailHash (emailHash)
   ,algorithm=inplace
   ,lock=none
;

That way, changes will be put into a temp table rather than glutting the Undo Logs.

Possible Workaround #2

If Workaround #1 does not work, then use pt-online-schema-change. I mentioned using it back in September 2019 (Adding indexes to production MySQL database without downtime?)

You would do something like this

pt-online-schema-change user --alter "add unique index idx_emailHash (emailHash)"

This will handle all the gory details in a temp table using triggers.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536