MySQL Document Store versus Bug hunter

Oh, there is a cool new feature added? And you are going to implement and use it?
Well, the brain of Bug lover is not working in that way, for me new feature is cool in terms of playing. It is a new place where hunt season is opened suddenly ๐Ÿ˜€

Currently I am in Percona Live 2016 and you can catch me every time -> my name is some sort of pain to pronounce but you can just say Shako instead of Shahriyar.

Yesterday there was an interesting discussion about bug reports and in general what is a “Bug” and how i can identify that if it is a Bug or not?
Here are general rules:

1. Trust in yourself. If somebody says that, hey it is an expected behaviour, just do not believe. Go ahead and fill a report. Insist in your opinion at last it will be converted to DOC issue, pretty valid DOC Bug -> http://bugs.mysql.com/bug.php?id=80350

2. Use Debug or Debug+Valgrind builds for your tests -> http://bugs.mysql.com/bug.php?id=80745

3. Be prepared for additional required infos. It is crucial that you give as much as possible info what you exactly did.

4. Time to time search and whatch for new bug reports. In fact reading bug reports and comments from experts is equal to reading a heavy book.

5. Be social. All open source guys are very friendly. You can contact them via Twitter or some other tool and just say hello.

6. Wait for new features ๐Ÿ˜€ (Go and test MySQL Document Store just like me)

http://bugs.mysql.com/bug.php?id=81036
http://bugs.mysql.com/bug.php?id=81037
http://bugs.mysql.com/bug.php?id=81040
http://bugs.mysql.com/bug.php?id=81044
http://bugs.mysql.com/bug.php?id=81060
http://bugs.mysql.com/bug.php?id=81061
http://bugs.mysql.com/bug.php?id=81162

If you are in Percona Live 2016 come and find me to have a discussion about bugs and life.

Interesting actions on MySQL’s storage engines :)

Did you ever think about altering MyISAM table to use Innodb tablespace?
Well that’s weird but you can run those alter statement without any error:

Sample table:

mysql> show create table sbtest1;

| sbtest1 | 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 AUTO_INCREMENT=685441 DEFAULT CHARSET=utf8 MAX_ROWS=1000000 |

Alter engine:

mysql> alter table sbtest1 engine=myisam;
Query OK, 685440 rows affected (19.06 sec)
Records: 685440 Duplicates: 0 Warnings: 0

Use encryption with MyISAM ๐Ÿ˜›

mysql> alter table sbtest1 encryption='y';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Use page compression:

mysql> alter table sbtest1 compression='zlib';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Use Innodb tablespace file:

mysql> alter table sbtest1 tablespace=innodb_file_per_table;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

Well in fact it is not going to use these features for sure. It is just kind of fun.

Related BUG report -> http://bugs.mysql.com/bug.php?id=80731

Well, you can even create a MEMORY table with encryption and compression enabled:

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 `s1` */ ENGINE=MEMORY DEFAULT CHARSET=utf8 MAX_ROWS=1000000 COMPRESSION='zlib' ENCRYPTION='y';

If you try to convert this table to InnoDB:

mysql> alter table sbtest2 engine=innodb;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Lovely SIGSEGV ๐Ÿ™‚


Program terminated with signal SIGSEGV, Segmentation fault.
#0 0x00007fb1d67ea621 in __pthread_kill (threadid=, signo=11) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
61 ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c: No such file or directory.
(gdb) bt
#0 0x00007fb1d67ea621 in __pthread_kill (threadid=, signo=11) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
#1 0x000000000186b1d9 in my_write_core (sig=11) at /opt/mysql-5.7.11/mysys/stacktrace.c:247
#2 0x0000000000e6bb52 in handle_fatal_signal (sig=11) at /opt/mysql-5.7.11/sql/signal_handler.cc:220
#3
#4 0x00000000018f2fa7 in ut_allocator::deallocate_trace (this=0x7fb1936eed90, pfx=0x4d1ffffffe8) at /opt/mysql-5.7.11/storage/innobase/include/ut0new.h:726
#5 0x00000000018f0e4c in ut_allocator::deallocate (this=0x7fb1936eed90, ptr=0x4d200000000 , n_elements=0)
at /opt/mysql-5.7.11/storage/innobase/include/ut0new.h:402
#6 0x0000000001bf6cef in dict_mem_table_free (table=0x7fb148921890) at /opt/mysql-5.7.11/storage/innobase/dict/dict0mem.cc:212
#7 0x00000000018ef159 in create_table_info_t::create_table_def (this=0x7fb1936ef2e0) at /opt/mysql-5.7.11/storage/innobase/handler/ha_innodb.cc:10044
#8 0x00000000018db93a in create_table_info_t::create_table (this=0x7fb1936ef2e0) at /opt/mysql-5.7.11/storage/innobase/handler/ha_innodb.cc:11567
#9 0x00000000018dc534 in ha_innobase::create (this=0x7fb148920840, name=0x7fb1936f2174 "./test/#sql-12bf_2", form=0x7fb1936efad0, create_info=0x7fb1936f24f0)
at /opt/mysql-5.7.11/storage/innobase/handler/ha_innodb.cc:11917
#10 0x0000000000ef1c9e in handler::ha_create (this=0x7fb148920840, name=0x7fb1936f2174 "./test/#sql-12bf_2", form=0x7fb1936efad0, info=0x7fb1936f24f0)
at /opt/mysql-5.7.11/sql/handler.cc:4819
#11 0x0000000000ef22b9 in ha_create_table (thd=0x7fb148000b70, path=0x7fb1936f2174 "./test/#sql-12bf_2", db=0x7fb148004a10 "test", table_name=0x7fb1936f1920 "#sql-12bf_2",
create_info=0x7fb1936f24f0, update_create_info=false, is_temp_table=false) at /opt/mysql-5.7.11/sql/handler.cc:4978
#12 0x00000000015a8374 in mysql_alter_table (thd=0x7fb148000b70, new_db=0x7fb148004a10 "test", new_name=0x0, create_info=0x7fb1936f24f0, table_list=0x7fb14891d820,
alter_info=0x7fb1936f2440) at /opt/mysql-5.7.11/sql/sql_table.cc:9572
#13 0x00000000017300e7 in Sql_cmd_alter_table::execute (this=0x7fb14800e5b0, thd=0x7fb148000b70) at /opt/mysql-5.7.11/sql/sql_alter.cc:316
#14 0x000000000150f2f5 in mysql_execute_command (thd=0x7fb148000b70, first_level=true) at /opt/mysql-5.7.11/sql/sql_parse.cc:4806
#15 0x00000000015112e2 in mysql_parse (thd=0x7fb148000b70, parser_state=0x7fb1936f41d0) at /opt/mysql-5.7.11/sql/sql_parse.cc:5518
#16 0x0000000001506394 in dispatch_command (thd=0x7fb148000b70, com_data=0x7fb1936f4dd0, command=COM_QUERY) at /opt/mysql-5.7.11/sql/sql_parse.cc:1428
#17 0x00000000015052ed in do_command (thd=0x7fb148000b70) at /opt/mysql-5.7.11/sql/sql_parse.cc:996
#18 0x00000000016439af in handle_connection (arg=0x48dd210) at /opt/mysql-5.7.11/sql/conn_handler/connection_handler_per_thread.cc:301
#19 0x0000000001cc0a3f in pfs_spawn_thread (arg=0x4b30a30) at /opt/mysql-5.7.11/storage/perfschema/pfs.cc:2192
#20 0x00007fb1d67e5182 in start_thread (arg=0x7fb1936f5700) at pthread_create.c:312
#21 0x00007fb1d5cf247d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

Also see related BUG report -> http://bugs.mysql.com/bug.php?id=80745

Same thing is here for MyISAM:

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 `s1` */ ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000 COMPRESSION='zlib'

But unlike with MEMORY there is no segmentation fault:

mysql> alter table sbtest2 engine=innodb;
ERROR 1112 (42000): Table '#sql-45d7_2' uses an extension that doesn't exist in this MySQL version

Really? The latest MySQL 5.7.11 does not have these features? Related report -> http://bugs.mysql.com/bug.php?id=80739

Again I should note the importance of BUG reports one more time. The BUG database is great place to learn and share.
It is also the soul of Open Source project.

Table rebuild with alter compression=’none’

As doc states:

https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html

To disable page compression, set COMPRESSION=None using ALTER TABLE. Writes to the tablespace that occur after setting COMPRESSION=None no longer use page compression. To uncompress existing pages, you must rebuild the table using OPTIMIZE TABLE after setting COMPRESSION=None.

ALTER TABLE t1 COMPRESSION="None";
OPTIMZE TABLE t1;

With regular table there is no table rebuild:

mysql> alter table sbtest4 compression='none';
Query OK, 0 rows affected (0,01 sec)
Records: 0 Duplicates: 0 Warnings: 0

So the rebuild will be on optimize table run.

But if you want table rebuild each time when you run compression=’none’ you can add encryption=’N’ option ๐Ÿ˜›

Sample 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 '',
`pad_c` int(11) GENERATED ALWAYS AS ((char_length(`pad`) + char_length(`c`))) STORED,
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB AUTO_INCREMENT=1843230 DEFAULT CHARSET=latin1 COMPRESSION='none' ENCRYPTION='N'

mysql> alter table sbtest1 compression='none';
Query OK, 1843229 rows affected (18,03 sec)
Records: 1843229 Duplicates: 0 Warnings: 0

mysql> show processlist;
+----+------+-----------+--------+---------+------+-------------------+----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+-------------------+----------------------------------------+
| 2 | root | localhost | dbtest | Query | 18 | copy to tmp table | alter table sbtest1 compression='none' |
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+--------+---------+------+-------------------+----------------------------------------+
2 rows in set (0,00 sec)

Running several times:

mysql> alter table sbtest1 compression='none';
Query OK, 1843229 rows affected (19,05 sec)
Records: 1843229 Duplicates: 0 Warnings: 0

mysql> alter table sbtest1 compression='none';
Query OK, 1843229 rows affected (17,50 sec)
Records: 1843229 Duplicates: 0 Warnings: 0

Yes it is from BUG report -> http://bugs.mysql.com/bug.php?id=80386

How to get rid of orphaned tables in MySQL?

This post is about situation where comments in BUG reports are really valuable in term of learning and getting some internal view of going process.
Let’s take a look at -> http://bugs.mysql.com/bug.php?id=80183

The report is about how you can not drop general tablespace if it was a crash of MySQL instant while running alter statement.


mysql> create tablespace ts1 add datafile '/var/lib/mysql_tablespaces/ts1.ibd' engine=innodb;
Query OK, 0 rows affected (0,02 sec)

Kill -9 process while altering:


mysql> alter table sbtest1 tablespace ts1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

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` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=808237 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

mysql> drop tablespace ts1;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts1

As you see in the middle of alter process i killed the process and then can not drop the general tablespace file.

And here is the original explanation from developer Kevin Lewis (KLEWIS2)

The test crashes the server in the middle of an ALTER TABLE that moves
a table into a general tablespace. The recovery process restores the
table in its original location, but the temporary table is left as an
orphan table in the general tablespace.

You can see it inside a general tablespace this way:

mysql> select * from information_schema.innodb_sys_tables where name like
'%#sql%';
+----------+----------------------------+------+--------+-------+-------------
+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT
| ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+----------------------------+------+--------+-------+-------------
+------------+---------------+------------+
| 289 | test/#sql-ib288-3493953630 | 161 | 6 | 24 | Barracuda
| Dynamic | 0 | General |
+----------+----------------------------+------+--------+-------+-------------
+------------+---------------+------------+

mysql> select space, name, space_type from
information_schema.innodb_sys_tablespaces where name='ts1';
+-------+------+------------+
| SPACE | NAME | SPACE_TYPE |
+-------+------+------------+
| 24 | ts1 | General |
+-------+------+------------+

The FRM file is named differently. It will be something like this in
the database directory: #sql-1874_2.frm

This table is located in the destination general tablespace and was the
table that would have been renamed to sbtest1 had the ALTER TABLE succeeded.

DROP TABLE #sql-1874_2 does not work because the frm file and the table are
named differently. In order to drop this orphaned temporary table you need
to rename the FRM file to the same base name as the InnoDB table:
mv #sql-1874_2.frm #sql-ib288-3493953630.frm

Then you can drop this table using the `#mysql50#` prefix before the table
name
which keeps mysql from expanding `#` to `@0023`. NOTE: You must use the
backticks!

mysql> drop table `#mysql50##sql-ib288-3493953630`;
Query OK, 0 rows affected (0.02 sec)

mysql> drop tablespace ts1;
Query OK, 0 rows affected (0.00 sec)

[1 Feb 2016 22:11] Kevin Lewis (KLEWIS2)

The problem of orphaned temporary tables has been around a long time. The
process for deleting these tables has also been known since at least 5.0.

When a crash happens during an ALTER TABLE that is rebuilding the table using
a temporary table name, that temporary table will remain after recovery and
is called an ‘orphan temp table’.

Usually, these tables are file-per-table so they can be deleted to save
space. Of course the proper way to dispose of them is to use DROP TABLE
`#mysql50#{frm and ibd base file name}`; The `#mysql50#` prefix tells MySQL
server not to convert `#` characters to `@0023`.

These orphaned files can exist inside the system tablespace if that is where
the ALTER TABLE is doing it work.

MySQL 5.7 has added General tablespaces and they can also have these orphaned
tables. But unlike the system tablespace, general tablespaces can be
dropped… if they are empty. An orphaned temp table will keep it from being
dropped. And just like an orphaned table in the system tablespace, it can use
pages in the tablespace, wasting that space.

What a great explanation ๐Ÿ™‚

And the clean thing is here is, this bug will be fixed in MySQL 5.8.

Azerbaijan MUG 27 FEB 2016 meetup

Great to have some meetups locally, to spread MySQL love among people.
On 27 FEB 2016 we have some talk about MySQL backup and recovery, available tools, what we are doing to secure our data and etc.
There were too much Oracle DB users where we have interesting discussions. How features are differ, how MySQL implements flashback? ๐Ÿ˜›

I want to share few photos with MySQL planet ๐Ÿ™‚
And of course i would like to thank Percona for sending me T-Shirts and stickers.

10995298_10208629863526167_7545924371217737212_n

10399028_10208629864846200_6085591794504828182_n

12804606_10208629866326237_4356855176861578569_n

12794413_10208629863966178_8361078380492186464_n

How to get confused about buffer pool size allocation?

How it feels when you realize that, something going to be scary in term of question – “Wait, why? how?”
Let’s see:

I am trying to allocate 9G buffer pool size with 64 instances:

innodb_buffer_pool_instances=64
innodb_buffer_pool_size=9G

From error log it said that:

InnoDB: Initializing buffer pool, total size = 16G, instances = 64, chunk size = 128M

“Wait, why? how?” N1 ๐Ÿ™‚

From select it is also allocated 16G:

mysql> select @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
| 16.000000000000 |
+------------------------------------------+

Just continue to increase pool size:

If you give 17G:

[Note] InnoDB: Initializing buffer pool, total size = 24G, instances = 64, chunk size = 128M

If you give 25G:

[Note] InnoDB: Initializing buffer pool, total size = 32G, instances = 64, chunk size = 128M

If you give 33G:

[Note] InnoDB: Initializing buffer pool, total size = 40G, instances = 64, chunk size = 128M

So each time it will allocate 7G more

“Wait, why? how?” N2 ๐Ÿ™‚

But the situation is getting more fun when you read the doc:

http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size

Buffer pool size must always be a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you alter innodb_buffer_pool_chunk_size, innodb_buffer_pool_size is automatically adjusted to a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances that is not less than current buffer pool size. The adjustment occurs when the buffer pool is initialized.

So in fact Buffer Pool Size is always multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances?
Then it must be 128M * 64 = 8G.

It doesn’t matter that, you give 33G in my.cnf and then you learn from Log it is allocated size is 40G, then you learn that it is in fact 8G in size ๐Ÿ™‚

Keep calm there is already a related BUG report -> http://bugs.mysql.com/bug.php?id=80350

Special thanks to Zhai Weixiang for comment in bug report.

The magical abandoned .ibd files after crash recovery with MySQL 5.7.10

If you are running ‘OPTIMIZE TABLE’ or altering ‘TABLESPACE’ just be careful not to kill MySQL service while running these commands ๐Ÿ™‚
Here is a test case:
We have a 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`)
) ENGINE=InnoDB AUTO_INCREMENT=2427738 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

After a while we want to ‘OPTIMIZE’ this table:

mysql> optimize table sbtest1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Uups ๐Ÿ™‚ MySQL process was killed/crashed.

[root@ps-5 mysql]# ls -l dbtest/
total 1092792
-rw-r-----. 1 mysql mysql 65 ั„ะตะฒ 4 03:43 db.opt
-rw-r-----. 1 mysql mysql 8632 ั„ะตะฒ 4 03:46 sbtest1.frm
-rw-r-----. 1 mysql mysql 784334848 ั„ะตะฒ 4 03:53 sbtest1.ibd
-rw-r-----. 1 mysql mysql 8632 ั„ะตะฒ 4 03:43 #sql-21ee_3.frm
-rw-r-----. 1 mysql mysql 8632 ั„ะตะฒ 4 03:53 #sql-3037_2.frm
-rw-r-----. 1 mysql mysql 201326592 ั„ะตะฒ 4 03:43 #sql-ib41-423909887.ibd
-rw-r-----. 1 mysql mysql 159383552 ั„ะตะฒ 4 03:53 #sql-ib47-3672662344.ibd

As a reasult for each crash we will have #sql-ib47-3672662344.ibd like files(tablespaces).

Another case if you try, to alter table’s tablespace.
Here is a table in general tablespace, which we want to copy back to innodb_file_per_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` (`k`)
) /*!50100 TABLESPACE `ts6` */ ENGINE=InnoDB AUTO_INCREMENT=808237 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

alter table sbtest1 tablespace=innodb_file_per_table; -> of course just run kill -9 for with MySQL PID.

As a result for each crash again magical ‘.ibd’ files.

[root@mysql-57 mysql]# du -hs dbtest/*
4,0K dbtest/db.opt
12K dbtest/sbtest1.frm
220M dbtest/#sql-ib97-1735019704.ibd
220M dbtest/#sql-ib97-2605617507.ibd
220M dbtest/#sql-ib97-2732989978.ibd
220M dbtest/#sql-ib97-4027484021.ibd

The weird thing here is that, it is impossible to drop/delete or something similar from MySQL side on this abandoned ‘tablespace’ files.
They are all searchable from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES:

mysql> select * from information_schema.innodb_sys_tablespaces where name like '%dbtest%';
dbtest/#sql-ib41-423909887 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 201326592 | 201326592 |
dbtest/sbtest1 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 784334848 | 758312960 |
dbtest/#sql-ib47-3672662344 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 159383552 | 159338496 |

The only option is to remove manually, but as a result you will get a warnings about missing tablespace files in error log after each start.

See Related BUG reports:

http://bugs.mysql.com/bug.php?id=80263
http://bugs.mysql.com/bug.php?id=80181

How to lose General Tablespace in MySQL 5.7.10

If you have not be careful with General Tablespaces in MySQL 5.7.10, you must be at least from now.
I have reported several bugs related to General Tablespaces for eg.:

* #77814 -> Altering table twice,with same tablespace will increase general tablespace size (verfied)

* #77556 -> Segmentation fault while creating general tablespace in disk full Linux (fixed/closed)

* #77830 -> “ALGORITHM=INPLACE is not supported” no error with tablespace alter (open)

* #80070 -> allocated_size and file_size differ if create general tablespace outside datadir (verified)

* #80181 -> Abandoned .ibd files after each crash recovery,killing while altering tablespace (verified)

* #80182 -> Unsupported extension error for new features while ‘create table like’ (verified)

But the most critical one is to lose tablespace after crash recovery:

* ##80183 -> ALTER TABLESPACE is not crash resistant (verified)

Reproduce steps are:
Create General Tablespace:

mysql> create tablespace ts1 add datafile '/var/lib/mysql_tablespaces/ts1.ibd' engine=innodb;
Query OK, 0 rows affected (0,02 sec)

Kill -9 process while altering:

mysql> alter table sbtest1 tablespace ts1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Then start and look at table if it was altered:

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` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=808237 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

No it is in innodb_file_per_table as usual.
But:

mysql> drop tablespace ts1;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts1

From now you can’t drop this tablespace.
If you have encountered any other scenario report it ๐Ÿ™‚

OOM killer vs. MySQL 5.7.10 epic results with over-allocating memory

Recently i have figured out that we can over-allocate InnoDB buffer pool size as I have already reported:

#79850

So as a result I can start MySQL with 300GB buffer pool size in my 16G laptop.

Config:

innodb_buffer_pool_size=300G
innodb_buffer_pool_instances=64

Started as:

2016-01-05 15:23:58 16211 [Note] InnoDB: Initializing buffer pool, size = 300.0G
2016-01-05 15:24:31 16211 [Note] InnoDB: Completed initialization of buffer pool

If you think that it is not harmful, let’s examine another report:

#79880

With MySQL 5.7.10 in my VM on CentOS 7:

I have:

[root@mysql-57 ~]# free -m
total used free shared buff/cache available
Mem: 3428 377 664 1 2387 2862
Swap: 2047 92 1955

[root@mysql-57 ~]# uname -a
Linux mysql-57 3.10.0-327.4.4.el7.x86_64 #1 SMP Tue Jan 5 16:07:00 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

Installed MySQL 5.7.10 using YUM repo.

Try to start MySQL with:

innodb_buffer_pool_size=20G
innodb_buffer_pool_instances=6

It will start as usual.

My test table:

The test table:

mysql> show create table sbtest1;
| sbtest1 | 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` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=25661595 DEFAULT CHARSET=latin1 |

And now i am creating a general tablespace:

mysql> CREATE TABLESPACE `sbtest1` ADD DATAFILE 'sbtest1.ibd' ENGINE=INNODB;
Query OK, 0 rows affected (0,02 sec)

Created as sbtest1.ibd file on MySQL datadir:

[root@mysql-57 ~]# ls /var/lib/mysql | grep sbtest
sbtest1.ibd

And then if you try to alter sbtest1 table’s tablespace:

mysql> ALTER TABLE sbtest1 TABLESPACE = sbtest1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Well, if you think this is a crash. NO! OOM killer has some fun with mysqld process ๐Ÿ™‚

Jan 8 03:18:03 mysql-57 kernel: Out of memory: Kill process 7613 (mysqld) score 956 or sacrifice child
Jan 8 03:18:03 mysql-57 kernel: Killed process 7613 (mysqld) total-vm:28896136kB, anon-rss:3331568kB, file-rss:0kB
Jan 8 03:18:03 mysql-57 systemd: mysqld.service: main process exited, code=killed, status=9/KILL
Jan 8 03:18:03 mysql-57 systemd: Unit mysqld.service entered failed state.
Jan 8 03:18:03 mysql-57 systemd: mysqld.service failed.
Jan 8 03:18:03 mysql-57 systemd: mysqld.service holdoff time over, scheduling restart.
Jan 8 03:18:03 mysql-57 systemd: Starting MySQL Server...
Jan 8 03:18:07 mysql-57 systemd: Started MySQL Server.
Jan 8 03:23:34 mysql-57 systemd: Stopping MySQL Server...
Jan 8 03:23:38 mysql-57 systemd: Starting MySQL Server...
Jan 8 03:23:39 mysql-57 systemd: Started MySQL Server.

The tragic results for us:

#RESULT 1

sbtest1.ibd general tablespace file size will grow after each kill and it will not reclaimed:

[root@mysql-57 ~]# ls -lth /var/lib/mysql | grep sbtest1
-rw-r-----. 1 mysql mysql 1,9G ัะฝะฒ 8 02:54 sbtest1.ib
d

Also there will be temp files on dbtest(database folder) which are also will not be removed:

[root@mysql-57 ~]# ls -lth /var/lib/mysql/dbtest/
total 8,1G
-rw-r-----. 1 mysql mysql 6,7G ัะฝะฒ 8 03:27 sbtest1.ibd
-rw-r-----. 1 mysql mysql 8,5K ัะฝะฒ 8 03:24 sbtest1.frm
-rw-r-----. 1 mysql mysql 1,5G ัะฝะฒ 8 03:18 #sql-ib41-3573152321.ibd
-rw-r-----. 1 mysql mysql 8,5K ัะฝะฒ 8 03:17 #sql-1dbd_2.frm
-rw-r-----. 1 mysql mysql 8,5K ัะฝะฒ 8 02:53 #sql-1caa_2.frm
-rw-r-----. 1 mysql mysql 8,5K ัะฝะฒ 8 02:49 #sql-835_9cf.frm
-rw-r-----. 1 mysql mysql 65 ัะฝะฒ 7 08:23 db.opt

#RESULT2

Messy output from metadata information:

mysql> select * from INNODB_SYS_TABLESPACES where name like '%btest%';
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
| 54 | sbtest1 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 2021654528 | 2021658624 |
| 59 | dbtest/#sql-ib41-3573152321 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 1526726656 | 1526730752 |
| 62 | dbtest/sbtest1 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 7092568064 | 7092572160 |
+-------+-----------------------------+------+-------------+------------+-----------+---------------+------------+---------------+------------+----------------+
3 rows in set (0,00 sec)

mysql> select * from information_schema.INNODB_SYS_DATAFILES where path like '%btest%';
+-------+-----------------------------------+
| SPACE | PATH |
+-------+-----------------------------------+
| 54 | ./sbtest1.ibd |
| 59 | ./dbtest/#sql-ib41-3573152321.ibd |
| 62 | ./dbtest/sbtest1.ibd |
+-------+-----------------------------------+
3 rows in set (0,00 sec)

#RESULT3

And even we can not drop tablespace:

mysql> drop tablespace sbtest1;
ERROR 1529 (HY000): Failed to drop TABLESPACE sbtest1

The golden rule for us, allocating memory based on available physical memory of our servers. Do not rely on Virtual Memory of OS.
Rely on real life.
Dear MySQL community thanks for attention.