| Bug #81367 | Irrelevant error message altering table engine -> The table '#sql-9ae_5' is full | ||
|---|---|---|---|
| Submitted: | 10 May 2016 13:42 | Modified: | 10 May 2016 14:13 |
| Reporter: | Shahriyar Rzayev | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 5.7.12 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[10 May 2016 14:13]
MySQL Verification Team
Hello Shahriyar, Thank you for the report. Thanks, Umesh
[10 May 2016 14:14]
MySQL Verification Team
-- 5.7.12
mysql> CREATE TABLESPACE `s1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS sbtest1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `sbtest1` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `k` int(10) unsigned NOT NULL DEFAULT '0',
-> `c` char(120) NOT NULL DEFAULT '',
-> `pad` char(60) NOT NULL DEFAULT '',
-> PRIMARY KEY (`id`),
-> KEY `k_1` (`k`)
-> ) ENGINE=InnoDB TABLESPACE=s1 AUTO_INCREMENT=2000001 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> set @id:=0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> insert into `sbtest1` values (@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)),(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)),(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)),(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000));
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into `sbtest1`
-> select @id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000) from
-> `sbtest1` k1, `sbtest1` k2, `sbtest1` k3, `sbtest1` k4,`sbtest1` k5,`sbtest1` k6, `sbtest1` k7, `sbtest1` k8, `sbtest1` k9,
-> `sbtest1` k0,`sbtest1` ka, `sbtest1` kb, `sbtest1` kc, `sbtest1` kd limit 100000;
Query OK, 100000 rows affected (4.61 sec)
Records: 100000 Duplicates: 0 Warnings: 0
mysql> alter table sbtest1 encryption='N';
Query OK, 100004 rows affected (3.31 sec)
Records: 100004 Duplicates: 0 Warnings: 0
mysql> alter table sbtest1 compression='None';
Query OK, 100004 rows affected, 1 warning (2.93 sec)
Records: 100004 Duplicates: 0 Warnings: 1
mysql> show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=latin1 COMPRESSION='None' ENCRYPTION='N'
1 row in set (0.00 sec)
mysql> alter table sbtest1 engine=memory;
ERROR 1114 (HY000): The table '#sql-7865_3f' is full
[10 May 2016 14:15]
MySQL Verification Team
Also, related Bug #80739
Description: Simple run: mysql> show create table sbtest2\G *************************** 1. row *************************** Table: sbtest2 Create Table: CREATE TABLE `sbtest2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) /*!50100 TABLESPACE `t1` */ ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=latin1 COMPRESSION='none' ENCRYPTION='N' Altering to memory: mysql> alter table sbtest2 engine=memory; ERROR 1114 (HY000): The table '#sql-9ae_5' is full Altering to myisam: mysql> alter table sbtest2 engine=myisam; ERROR 1031 (HY000): Table storage engine for '#sql-9ae_5' doesn't have this option How to repeat: See description Suggested fix: In general preventing running "alter engine != innodb" command with encryption/general tablespace/compression enabled tables. Because only Innodb supports these features.