Bug 219238

Summary:	databases/mysql56-server: Incorrect key file for table
Product:	Ports & Packages	Reporter:	v.chernyadev
Component:	Individual Port(s)	Assignee:	Jochen Neumeister <joneum>
Status:	Closed FIXED
Severity:	Affects Some People	CC:	freebsd, joneum, marko.cupac, w.schwarzenfeld
Priority:	---	Keywords:	needs-qa
Version:	Latest	Flags:	koobs: maintainer-feedback? (joneum)
Hardware:	Any
OS:	Any

Description v.chernyadev 2017-05-12 12:50:21 UTC

Error for some kind of data. Affects only in FreeBSD (in Linux and Windows all Ok):
---
[Err] 126 - Incorrect key file for table '/tmp/mysql/#sqld5fc_d5aac_4.MYI'; try to repair it
---
There is lot of space if /tmp/mysql/. Checked at 5 FreeBSD servers, also whith clean installation whith defaults.
I can provide a table and SQL to repeat, but only on private message.
Also repeated in percona56-server. 
In mysql/percona 5.5 it's Ok.

Comment 1 Marko Cupać 2017-05-16 10:43:34 UTC

I am seeing similar messages in err:

2017-05-15 23:36:33 70484 [ERROR] /usr/local/libexec/mysqld: Incorrect key file for table '/var/db/mysql_tmpdir/#sql11354_267_2.MYI'; try to repair it
2017-05-15 23:36:33 70484 [ERROR] /usr/local/libexec/mysqld: Incorrect key file for table '/var/db/mysql_tmpdir/#sql11354_267_2.MYI'; try to repair it
2017-05-15 23:36:33 70484 [ERROR] Got an error from unknown thread, /wrkdirs/usr/ports/databases/mysql56-server/work/mysql-5.6.36/storage/myisam/mi_write.c:223

Why reference to poudriere workdir? This looks fishy to me.

My environment:
11.0-RELEASE-p10 amd64 (both host&jails)
mysql56-server-5.6.36 (on jail1)
php70-pdo-7.0.18 (on jail2)
php70-pdo_mysql-7.0.18 (on jail2)

Overrides from my.cnf.default:

# added by pacija for nextcloud
binlog-format                   = MIXED
innodb_large_prefix             = on
innodb_file_format              = barracuda
innodb_file_per_table           = true

This server hosts quite a few databases for quite a few applications, including nextcloud, ejabberd and piwik. Above happens with sonerezh web application. I have also reported problem there:
https://github.com/Sonerezh/sonerezh/issues/303

Perhaps inaccessibility of all my virtual hosts in apache at the time of above message has something to do with it:

[Tue May 16 00:09:50.726014 2017] [proxy_fcgi:error] [pid 73526] (70007)The timeout specified has expired: [client 193.53.106.34:62791] AH01075: Error dispatching request to : (polling)

Comment 2 Marko Cupać 2017-05-21 10:50:43 UTC

Problem appears not to be present in mysql57-server-5.7.18.

Comment 3 Walter Schwarzenfeld freebsd_triage

2018-02-03 01:48:16 UTC

We have version 5.6.39, does the problem still exist?

Comment 4 Mahdi Mokhtari freebsd_committer

2018-02-03 07:17:52 UTC

(In reply to w.schwarzenfeld from comment #3)
As far as I tested with apache24 and mysql56 (from HEAD) on my poudriere-jail today-morning, I don't observe the problem anymore...

I'd suggest waiting for reporter's confirm as well.

Comment 5 Alexey Koscheev 2018-05-14 17:28:09 UTC

Hi.

I hit this problem in MySQL server 5.6.40 and in 5.7.21 with internal_tmp_disk_storage_engine=MYISAM (default is INNODB).

See also
https://bugs.mysql.com/bug.php?id=90573

Comment 6 Kubilay Kocak freebsd_committer

2020-01-25 03:03:30 UTC

^Triage: Assign to new maintainer (ports r523992)

Comment 7 Jochen Neumeister freebsd_committer

2020-02-28 23:00:41 UTC

PR from 2017. I'll close up here. If the problem still exists, please reopen

Comment 8 Alexey Koscheev 2020-02-29 08:02:13 UTC

Very sad, that problem was closed so. 
In this moment, it is no critically actual, because of MySQL 5.6's EOL not so far.