How fall in love with Failing assertion: !m_fatal?

The story about real love.
Once upon a time there was a bug about resizing innodb buffer pool size online ->
#77564 which was fixed on version MySQL 5.7.9.
But you can not stop the real love. Suddenly she came up and truely surprised me,because I was recently freed from depression. And now the same thing is happening again. With MySQL 5.7.12 if you try to resize:

mysql> set global innodb_buffer_pool_size = 107374182400;
Query OK, 0 rows affected (0.01 sec)

2016-05-31 11:50:18 0x7f7926ba1740 InnoDB: Assertion failure in thread 140158317500224 in file ut0ut.cc line 935
InnoDB: Failing assertion: !m_fatal
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xef0a4b]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x7aef91]
/lib64/libpthread.so.0(+0xf100)[0x7f75f1ada100]
/lib64/libc.so.6(gsignal+0x37)[0x7f75f04ce5f7]
/lib64/libc.so.6(abort+0x148)[0x7f75f04cfce8]
/usr/sbin/mysqld[0x77f3ee]
/usr/sbin/mysqld(ib::fatal_or_error::~fatal_or_error()+0x2f8)[0x10c5ba8]
/usr/sbin/mysqld[0x11b1e46]
/usr/sbin/mysqld(hash0_create(unsigned long)+0x63)[0x11b2f73]
/usr/sbin/mysqld(ib_create(unsigned long, latch_id_t, unsigned long, unsigned long)+0x41)[0x11b0411]
/usr/sbin/mysqld(btr_search_sys_resize(unsigned long)+0x1d1)[0x10f0ec1]
/usr/sbin/mysqld(buf_pool_resize()+0xcfc)[0x110ccfc]
/usr/sbin/mysqld(buf_resize_thread+0x197)[0x110e5a7]
/lib64/libpthread.so.0(+0x7dc5)[0x7f75f1ad2dc5]
/lib64/libc.so.6(clone+0x6d)[0x7f75f058f28d]

Oh no please, stop it. She said no, I will appear again and again with different options -> #81654
If you try to start MySQL with maximum innodb_log_buffer_size=4294967295, you will see the same:

2016-05-31 11:50:18 0x7f7926ba1740 InnoDB: Assertion failure in thread 140158317500224 in file ut0ut.cc line 935
InnoDB: Failing assertion: !m_fatal
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xef0a4b]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x7aef91]
/lib64/libpthread.so.0(+0xf100)[0x7f7926780100]
/lib64/libc.so.6(gsignal+0x37)[0x7f79251745f7]
/lib64/libc.so.6(abort+0x148)[0x7f7925175ce8]
/usr/sbin/mysqld[0x77f3ee]
/usr/sbin/mysqld(_ZN2ib14fatal_or_errorD2Ev+0x2f8)[0x10c5ba8]
/usr/sbin/mysqld[0xf8469e]
/usr/sbin/mysqld(_Z8log_initv+0xdd)[0xf8e8dd]
/usr/sbin/mysqld(_Z34innobase_start_or_create_for_mysqlv+0x1fd2)[0x106e942]
/usr/sbin/mysqld[0xf38c45]
/usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x51)[0x7fa341]
/usr/sbin/mysqld[0xce3675]
/usr/sbin/mysqld(_Z11plugin_initPiPPci+0x620)[0xcea5a0]
/usr/sbin/mysqld[0x7a7efd]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x842)[0x7a9422]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7f7925160b15]
/usr/sbin/mysqld[0x79f255]

And for the next time do not mess up with real love.

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.