Linux hv3 3.13.0-86-generic #131-Ubuntu SMP Thu May 12 23:33:13 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

So far I couldn't reproduce the problem with the same (mostly) config file, same table structure and size and random data, on a Galera 10.0.25 node.
So, if you can answer any of the questions below, it would help. I understand that some questions presume experiments which could result in new crashes and you might not be able to afford it.

• is it certainly the right table definition? ANALYZE is run on ac2_schematic, but CREATE is for ac_schematic;
• is the crash reproducible on the same server where it happened before?
• is the crash reproducible on the server when it is not a part of a Galera cluster (and better yet, started without any wsrep options at all, so it's not even a single-node cluster, just a standalone server)?
• is there anything suspicious in the error log before the crash, especially anything that indicates that either ac_schematic or any tables in mysql schema are corrupted?
• did you try to run CHECK TABLE on ac_schematic and statistics tables? If not, could you please do it?

  CHECK TABLE mysql.column_stats, mysql.index_stats, mysql.table_stats EXTENDED;

  -- the one below would also be better with EXTENDED, but it can take a lot of time:

  CHECK TABLE ac_schematic;

• would it be possible to upload the .frm, .MYI and .MAD files of all four tables to our FTP server ftp.askmonty.org/private?

Meanwhile, psergey, cvicentiu, could you please take a look at the stack trace? Even though not debug, it's readable, maybe you'll have any ideas.
I'm adding cvicentiu to the loop because he fixed a somewhat similar one some time ago: MDEV-7912. That time it was a Windows-specific problem though.

Hi Elena,

I'll do my best to answer your questions. Some are easier than others. I'll start with the easy ones.

I was not reproduce the crash with the simple tests given below. But I want to reiterate that the same crash happened on two servers, hv3 and hv4, at the same point in a weekly process with about 10 minutes separation, which corresponds to the staggered update start times. There is more possibly relevant detail in 5) below.

1) The ac_schematic table definition correct. I typed ac2_schematic into the report by mistake (now corrected). The software using the table is called ac2 but the table is called ac_schematic. There is no table called ac2_schematic.

2) No However, after I recovered the mysqlds from the crash yesterday, I ran Analyze from MysqlWorkbench. I ran this today:

MariaDB [autocomplete1]> ANALYZE LOCAL TABLE ac_schematic PERSISTENT FOR ALL;

+----------------------------+---------+----------+-----------------------------------------+

| Table                      | Op      | Msg_type | Msg_text                                |

+----------------------------+---------+----------+-----------------------------------------+

| autocomplete1.ac_schematic | analyze | status   | Engine-independent statistics collected |

| autocomplete1.ac_schematic | analyze | status   | Table is already up to date             |

+----------------------------+---------+----------+-----------------------------------------+

2 rows in set (4 min 23.83 sec)

3) This would be very nervous-making. I'm not sure it's worth the trouble given the result in 2) above.

4) There is nothing unusual in the error log. Every hour, node hv5 desyncs itself for about 4 minutes to make a backup. hv3 and hv4 (on which 2 servers the same crash happened executing the same command) have a note in the error log every time it happens, which serves as a timestamp in the error log, which looks like this

160624 12:35:02 [Note] WSREP: Member 1.0 (hv5) desyncs itself from group

160624 12:40:26 [Note] WSREP: Member 1.0 (hv5) resyncs itself to group

160624 12:40:26 [Note] WSREP: Member 1.0 (hv5) synced with group.

160624 13:35:03 [Note] WSREP: Member 1.0 (hv5) desyncs itself from group

160624 13:39:17 [Note] WSREP: Member 1.0 (hv5) resyncs itself to group

160624 13:39:17 [Note] WSREP: Member 1.0 (hv5) synced with group.

160624 14:35:02 [Note] WSREP: Member 1.0 (hv5) desyncs itself from group

160624 14:38:30 [ERROR] mysqld got signal 11 ;

This could be because you hit a bug. It is also possible that this binary

or one of the libraries it was linked against is corrupt, improperly built,

or misconfigured. This error can also be caused by malfunctioning hardware.

For a while I considered that the size of the error log on hv3 might be causing a problem. It was 4.7GiB. But then the same crash happened on hv4 a few minutes later and its error log was a reasonable size.

5) The myisam data and index files for this database are prepared elsewhere and copied to the server. After copy to a temp dir outside datadir, the script runs in a shell "myisamchk --silent --force --update-state --key_buffer_size=1G --sort_buffer_size=1G --read_buffer_size=16M --write_buffer_size=16M *.MYI". After this they are copied into datadir. It didn't log any errors.

Then the same script that runs runs CHECK TABLE CHANGED on each tables immediately prior to ANALYZE. It didn't log any errors.

MariaDB [(none)]> CHECK TABLE mysql.column_stats, mysql.index_stats, mysql.table_stats EXTENDED;

+--------------------+-------+----------+----------+

| Table              | Op    | Msg_type | Msg_text |

+--------------------+-------+----------+----------+

| mysql.column_stats | check | status   | OK       |

| mysql.index_stats  | check | status   | OK       |

| mysql.table_stats  | check | status   | OK       |

+--------------------+-------+----------+----------+

3 rows in set (0.00 sec)

MariaDB [(none)]> CHECK TABLE autocomplete1.ac_schematic;

+----------------------------+-------+----------+----------+

| Table                      | Op    | Msg_type | Msg_text |

+----------------------------+-------+----------+----------+

| autocomplete1.ac_schematic | check | status   | OK       |

+----------------------------+-------+----------+----------+

1 row in set (36.68 sec)

6) Yes, I'll upload those files if I can manage.

I want to draw attention to these lines in my.cnf:

binlog_ignore_db                = autocomplete1

binlog_ignore_db                = autocomplete2

replicate_ignore_db             = autocomplete1

replicate_ignore_db             = autocomplete2

The ac_* tables in the autocomplete1 and 2 databases are read-only as far as the app is concerned. They are recreated once a week double-buffer style. They are MyISAM because the process of creating the tables is extremely slow in InnoDB (10s of hours vs hours). The recreation and updates use massive DML queries that I don't dare replicate in Galera. I would prefer to have these tables in a separate mysqld from the rest of the app but haven't had time to do that yet.

The I introduced the ANALYZE LOCAL TABLE command in the weekly process (as described in 5) above) for the first time this week on production servers. I did this because after a MariaDB update a few weeks ago, Sphinx indexer sometimes selects far fewer rows from the autocomplete tables than it should. This error corresponded to incorrect table stats as shown in MysqlWorkbench. And Sphinx indexer ran correctly after I analyzed tables from MysqlWorkbench.

My suspicion is that the problem could be related to how DDL like ANALYZE TABLE (statement replication) behaves with replicate_ignore_ and binlog_ignore in a Galera cluster. Just speculation.

For now I will remove "PERSISTENT FOR ALL" and see if "ANALYZE LOCAL TABLE ac_schematic" crashes any servers next week.

I believe I have uploaded the requested files via ftp. Please confirm.

Yes, the files have arrived, thanks. Maybe we'll have some luck with them, although of course since it's not reliably reproducible on your side, the hope is rather optimistic.
Thanks for your answers to the questions. Regarding (3) I agree that there is no point in this experiment if the crash is not reproducible in (2).

ANALYZE LOCAL TABLE __ PERSISTENT FOR ALL can use a surprising amount of filesystem space.

While trying to recreate the crash, I had a test VM with 55.7GiB used and 42.6GiB free in the filesystem holding datadir and tmpdir. While the analyze command ran, that gradually reached 98.3GiB and 0 (zero), then returned to the initial state in the next 5 seconds.

In the command output there was a line

The largest data table analyzed was 6.6GiB and the largest index file was 5.1GiB. So it's surprising that mysqld used more than 42GiB additional file space to analyze the tables. The docs provide a warning: "Currently, engine-independent statistics is collected by doing full table and full index scans, and can be quite expensive." But this implies io and cpu expense, not disk space.

The note actually implies everything (maybe it's a bad wording though). We have a task about the tmp space problem: MDEV-6529.
I thought it could have been related to the crash, but I expect the error log would have some indication of it, and there is none.
If you have a reason to believe tmp could have gone full at the time of the crash, please let us know.

The stacktrace unfortunately doesn't give me any clue about what could be happening.

The test VM I used had equivalent tables and data and FS dimensions. It used more than 42GiB to do the ANALYZE TABLE. The server that crashed had about 40 free. So I think I can infer that it did run out of space.

The test VM did not crash but it was also not running Galera with a steady stream of wsreps going both up and down.

Thanks, it's helpful. I suppose it's possible that if the server runs out of space at an unfortunate moment, it can crash rather than complain about it as it did in your test VM and as it was happening in the other bug report. I will see if I can reproduce the crash this way.

Looking further into it, I take back my words about it being the same problem as MDEV-6529.
MDEV-6529 is mostly about big VARCHAR columns (and unfortunate data layouts); but your table does not have a single VARCHAR column.

While with INT columns the disk usage is also somewhat more than desired, it should be nowhere near the values that you observe.

I ran the same ANALYZE on your table, and expectedly had the top tmp disk usage of ~300 MB (measurements were taken every second, so the peak couldn't have been missed).

So, if your server indeed takes over 40 GB executing the same query on the same table, it's very strange and worrisome.

If you can give us temporary access to your test server, psergey is willing to investigate the disk usage problem "on site". If he discovers the reason, maybe it will turn out to be the root cause of the initial crash as well.

thefsb, are you still experiencing the problem? And if you are, can you provide the access as suggested above?

Hi Elena,

I changed my app, removing the PERSISTENT FOR ALL clause and I no longer experience this problem. I cannot easily set up the access you would need. So that's all rather unhelpful. Sorry.

On the plus side, you can maybe close this bug because I think got some information wrong in the report. My app runs ANALYZE TABLE on about 10 tables in sequence and I think I was wrong about which of them triggered this error. I now think it was the previous table which does have VARCHARs.