MySQL Bugs: #79527: Errorcode 175: abort in group by on big tables

Bug #79527	Errorcode 175: abort in group by on big tables
Submitted:	4 Dec 2015 12:58	Modified:	8 Aug 2017 19:05
Reporter:	Vassilis Virvilis	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	filesort Errorcode: 175 - File to short; Expected more data in file

Description:
Hi,

I have also hit to the error described here

http://forums.mysql.com/read.php?20,629066,629066

[ERROR] /usr/sbin/mysqld: Sort aborted: Unexpected EOF found when reading file '/Db/tmp/MYxbmcPg' (Errcode: 175 - File to short; Expected more data in file)

No signal 11 though in my syslog.

How to repeat:
I have a really big table and I doing a double GROUP BY

SELECT entity1_id,
        entity1_count,
        entity2_type_id,
        GROUP_CONCAT(entity2_id, ',', entity2_count, ',', cooc) AS coocs
FROM entity_cooc_ref_compact
GROUP BY entity1_id, entity2_type_id;

The explain says this
+----+-------------+-------------------------+------+---------------+------+---------+------+------------+----------------+
| id | select_type | table                   | type | possible_keys | key  | key_len | ref  | rows       | Extra          |
+----+-------------+-------------------------+------+---------------+------+---------+------+------------+----------------+
|  1 | SIMPLE      | entity_cooc_ref_compact | ALL  | NULL          | NULL | NULL    | NULL | 1182837899 | Using filesort |
+----+-------------+-------------------------+------+---------------+------+---------+------+------------+----------------+

The table is like this

entity_cooc_ref_compact | CREATE TABLE `entity_cooc_ref_compact` (
  `entity1_id` int(11) DEFAULT NULL,
  `entity1_type_id` int(11) DEFAULT NULL,
  `entity1_count` int(11) DEFAULT NULL,
  `entity2_id` int(11) DEFAULT NULL,
  `entity2_type_id` int(11) DEFAULT NULL,
  `entity2_count` int(11) DEFAULT NULL,
  `cooc` int(11) DEFAULT NULL,
  `refs` longblob,
  KEY `entity1_id` (`entity1_id`),
  KEY `entity1_type_id` (`entity1_type_id`),
  KEY `entity2_id` (`entity2_id`),
  KEY `entity2_type_id` (`entity2_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

This was working okay with mysql-5.5

Also this guy looks like it has the same problem

http://www.helptouser.com/database/77980-unexpected-eof-during-select-on-huge-myisam-table...

Thu bug persists.

I did a repair table. The bug remains.

I rebooted the mysql-server + I rebooted the machine. The bug remains.

I rebooted - fsck - drop table and create new table. The bug remains.

I think the error is encoutered in 4 places in the mysql code

./mysys/mf_iocache.c
./mysys/my_read.c
./mysys/my_pread.c
./mysys/my_fstream.c

I am trying to put printf to see which one it is. Running the test as we speak

Next step may involve kernel - upgrade (from 4.2 to 4.3).

  Stay tuned

After adding printfs I found that the error comes from ./mysys/my_pread.c

I am adding more prints around it to get more information

    Vassilis

I am getting this,

I added some printfs mimicking the DEBUG statements around there

fd: 157 Seek: 25187153075 Buffer: 0x7f826ffff010 Count: 2147483625 MyFlags: 20
Read only 2147479552 bytes off 2147483625 from 157, my_errno: 175, errno: 0

Some notes: 

buffer-size = 2147483625 = 2G - 23
offset = 25187153075 ~= 11.287 * 2147483625

read_bytes = 2147479552 = 2147483625 - 4073 = 2G - 4096

#lsof -a -p `pidof mysqld`
mysqld  7695 mysql  156u   REG              252,0  17990811648  826771 /lvm2/lvm-vol0/logical-vol0/Db/tmp/MY5nlu7q (deleted)
mysqld  7695 mysql  157u   REG              252,0  41399326465  826773 /lvm2/lvm-vol0/logical-vol0/Db/tmp/MYZtFx7f (deleted)

This is an MyISAM table. In my conf I have

key-buffer-size         =       2G
myisam-sort-buffer-size =       2G
sort-buffer-size        =       2G

I finally manage it to workaround it by setting 

key-buffer-size         =       1G
myisam-sort-buffer-size =       1G
sort-buffer-size        =       1G

My Database is located in a LVM assembled by various disks including several overall the network with the vblade/aoe (ATA over ethernet) setup.

Looks like kernels 4.2/4.3 under this setup do not return the full 2G requested and mysql barfs because it has called my_pread with MyFlags = 20 = MY_WME | MY_NABP

where MY_NABP = /* Error if not all bytes read/writen */

I am not 100% sure if that was triggered by linux kernel change or from the upgrade from mysql 5.5 to 5.6

One question though: Why mysql requires the kernel to retrieve all the data requested? AFAIK UNIX does not guarantee that it will return the number of bytes requested. This is known as short read and every application should guard against it.

Or Am I missing something? - besides the fact I am talking to myself here in this bugreport.

Thanks a bunch for the help and the moral support.

    Vassilis

Hi,

I did manage to reproduce this with 5.6 but I'm not sure if this is a "bug or a feature". The problem, as I see it trough tests, happens only with >2G tables and only MyISAM and only hosted on external storage, so I can't say how much priority it can get (if any).

Workaround - reduce cache below 1.6G
Solution - move to 5.7 and InnoDB :)

all best
Bogdan

p.s. yes the short read is something you "should guard from" but myisam is all about performance and not about "security". You are not getting (almost never) short reads from a local disk (so, performance again).