[MDEV-9678] Data Directory bug Created: 2016-03-03  Updated: 2016-05-04  Resolved: 2016-03-23

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.1.12, 10.0, 10.1
Fix Version/s: 10.1.13

Type: Bug Priority: Major
Reporter: Michael Van Der Beek Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 0
Labels: upstream-fixed
Environment:

Centos 5.


 Description   

Hi All,

I'm running these rpms:

MariaDB-common-10.1.12-1
 
MariaDB-server-10.1.12-1
 
MariaDB-test-10.1.12-1
 
MariaDB-shared-10.1.12-1
 
MariaDB-connect-engine-10.1.12-1
 
MariaDB-client-10.1.12-1
 
MariaDB-compat-10.1.12-1

I've created a directory /drbd2

# ls -al /drbd2
 
total 28
 
drwxr-xr-x  4 mysql mysql  4096 Mar  3 13:58 .
 
drwxr-xr-x 31 root  root   4096 Mar  3 12:11 ..
 
drwxrwx---  2 mysql mysql  4096 Mar  3 13:58 radius
 
drwx------  2 mysql mysql 16384 Mar  3 12:12 lost+found

When I try to create a myisam with Data Directory it works:

CREATE TABLE `radacct3` (
 
   `radacctid` bigint(20) NOT NULL AUTO_INCREMENT,   `acctsessionid` varchar(64) NOT NULL DEFAULT '',   `acctuniqueid` varchar(32) NOT NULL DEFAULT '',   `username` varchar(64) NOT NULL DEFAULT '',   `groupname` varchar(64) NOT NULL DEFAULT '',   `realm` varchar(64) DEFAULT '',   `nasipaddress` varchar(15) NOT NULL DEFAULT '',   `nasportid` varchar(15) DEFAULT NULL,   `nasporttype` varchar(32) DEFAULT NULL,   `acctstarttime` datetime DEFAULT NULL,   `acctstoptime` datetime DEFAULT NULL,   `acctsessiontime` int(11) DEFAULT NULL,   `acctauthentic` varchar(32) DEFAULT NULL,   `connectinfo_start` varchar(50) DEFAULT NULL,   `connectinfo_stop` varchar(50) DEFAULT NULL,   `acctinputoctets` bigint(20) DEFAULT NULL,   `acctoutputoctets` bigint(20) DEFAULT NULL,   `calledstationid` varchar(50) NOT NULL DEFAULT '',   `callingstationid` varchar(50) NOT NULL DEFAULT '',   `acctterminatecause` varchar(32) NOT NULL DEFAULT '',   `servicetype` varchar(32) DEFAULT NULL,   `framedprotocol` varchar(32) DEFAULT NULL,   `framedipaddress` varchar(15) NOT NULL DEFAULT '',   `acctstartdelay` int(11) DEFAULT NULL,   `acctstopdelay` int(11) DEFAULT NULL,   `acctupdatetime` datetime DEFAULT NULL,   `deleted` char(1) DEFAULT '',   `objectclass` varchar(64) DEFAULT 'AAA-SessionInfo',   PRIMARY KEY (`radacctid`),   KEY `username` (`username`),   KEY `framedipaddress` (`framedipaddress`),   KEY `acctsessionid` (`acctsessionid`),   KEY `acctsessiontime` (`acctsessiontime`),   KEY `acctuniqueid` (`acctuniqueid`),   KEY `acctstarttime` (`acctstarttime`),   KEY `acctstoptime` (`acctstoptime`),   KEY `nasipaddress` (`nasipaddress`),   KEY `acctupdatetime` (`acctupdatetime`),   KEY `deleted` (`deleted`),   KEY `objectclass` (`objectclass`) 
) ENGINE=Myisam data directory='/drbd2';
 
Query OK, 0 rows affected (0.00 sec)


# ls -al /drbd2/
 
total 28
 
drwxr-xr-x  4 mysql mysql  4096 Mar  3 14:12 .
 
drwxr-xr-x 31 root  root   4096 Mar  3 12:11 ..
 
drwxrwx---  2 mysql mysql  4096 Mar  3 13:58 radius
 
drwx------  2 mysql mysql 16384 Mar  3 12:12 lost+found
 
-rw-rw----  1 mysql mysql     0 Mar  3 14:12 radacct3.MYD


MariaDB [ant_truauth]> drop table radacct3;
 
Query OK, 0 rows affected (0.00 sec)

So it looks like it correctly puts the myisam data file in the /drbd2 directory.
However creating a table with Innodb seems to fail and I can't figure out the answer.
The radacct3.idb file should have been created in the /drbd2/radius/ directory.

MariaDB [ant_truauth]> CREATE TABLE `radacct3` (
 
   `radacctid` bigint(20) NOT NULL AUTO_INCREMENT,   `acctsessionid` varchar(64) NOT NULL DEFAULT '',   `acctuniqueid` varchar(32) NOT NULL DEFAULT '',   `username` varchar(64) NOT NULL DEFAULT '',   `groupname` varchar(64) NOT NULL DEFAULT '',   `realm` varchar(64) DEFAULT '',   `nasipaddress` varchar(15) NOT NULL DEFAULT '',   `nasportid` varchar(15) DEFAULT NULL,   `nasporttype` varchar(32) DEFAULT NULL,   `acctstarttime` datetime DEFAULT NULL,   `acctstoptime` datetime DEFAULT NULL,   `acctsessiontime` int(11) DEFAULT NULL,   `acctauthentic` varchar(32) DEFAULT NULL,   `connectinfo_start` varchar(50) DEFAULT NULL,   `connectinfo_stop` varchar(50) DEFAULT NULL,   `acctinputoctets` bigint(20) DEFAULT NULL,   `acctoutputoctets` bigint(20) DEFAULT NULL,   `calledstationid` varchar(50) NOT NULL DEFAULT '',   `callingstationid` varchar(50) NOT NULL DEFAULT '',   `acctterminatecause` varchar(32) NOT NULL DEFAULT '',   `servicetype` varchar(32) DEFAULT NULL,   `framedprotocol` varchar(32) DEFAULT NULL,   `framedipaddress` varchar(15) NOT NULL DEFAULT '',   `acctstartdelay` int(11) DEFAULT NULL,   `acctstopdelay` int(11) DEFAULT NULL,   `acctupdatetime` datetime DEFAULT NULL,   `deleted` char(1) DEFAULT '',   `objectclass` varchar(64) DEFAULT 'AAA-SessionInfo',   PRIMARY KEY (`radacctid`),   KEY `username` (`username`),   KEY `framedipaddress` (`framedipaddress`),   KEY `acctsessionid` (`acctsessionid`),   KEY `acctsessiontime` (`acctsessiontime`),   KEY `acctuniqueid` (`acctuniqueid`),   KEY `acctstarttime` (`acctstarttime`),   KEY `acctstoptime` (`acctstoptime`),   KEY `nasipaddress` (`nasipaddress`),   KEY `acctupdatetime` (`acctupdatetime`),   KEY `deleted` (`deleted`),   KEY `objectclass` (`objectclass`) 
) ENGINE=Innodb data directory='/drbd2';
 
ERROR 1005 (HY000): Can't create table `radius`.`radacct3` (errno: -1 "Internal error < 0 (Not system error)")

The mysql.log shows:

2016-03-03 13:58:19 2aaebe6eed40 InnoDB: Error: Write to file ./radius/radacct3.isl failed at offset 0.
 
InnoDB: 31 bytes should have been written, only 0 were written.
 
InnoDB: Operating system error number 22.
 
InnoDB: Check that your OS and file system support files of this size.
 
InnoDB: Check also that the disk is not full or a disk quota exceeded.
 
InnoDB: Error number 22 means 'Invalid argument'.
 
InnoDB: Some operating system error numbers are described at
 
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html

I managed to do this with the i686 version, which I mistakenly installed as I thought was the 64bit version. After upgrading to the 64bit version of Mariadb it seems to fail.

# df -h
 
Filesystem            Size  Used Avail Use% Mounted on
 
(removed the other directories)
 
/dev/drbd2            7.7G  146M  7.2G   2% /drbd2
 
 
 
# df -i
 
Filesystem            Inodes   IUsed   IFree IUse% Mounted on
 
(removed the other directories)
 
/dev/drbd2           1024128      12 1024116    1% /drbd2

Hence its not a diskspace or inode issue.
There are no errors in /var/log/messages.

Since I could created the myisam version I think permissions to the directory is fine for mariadb to create the files.

Any idea what is wrong?

Regards,

Michael

 Comments   
Comment by Elena Stepanova [ 2016-03-03 ]

Michaelv, please check everything about your regular datadir, the one that is in @@datadir – its existence, permissions, diskspace, inode, etc.
According to your error log, InnoDB fails to create ./radius/radacct3.isl, not ibd file. The isl file is created in the regular datadir.

Comment by Michael Van Der Beek [ 2016-03-04 ]

Hi Elena,

Oh looks like my thinking is wrong.
Okay the default mysql is /var/lib/mysql which is symlink to /drbd

datadir	/var/lib/mysql/
 
lrwxrwxrwx  1 mysql  mysql     19 Aug 19  2014 mysql -> /drbd/var/lib/mysql
 
 
 
# df -h
 
Filesystem            Size  Used Avail Use% Mounted on
 
/dev/sda5             4.0G  318M  3.5G   9% /var
 
tmpfs                 7.9G     0  7.9G   0% /dev/shm
 
/dev/drbd0             10G  3.3G  6.3G  34% /drbd
 
/dev/drbd1            9.9G  151M  9.2G   2% /drbd1
 
/dev/drbd2            7.7G  146M  7.2G   2% /drbd2
 
 
 
# df -i
 
Filesystem            Inodes   IUsed   IFree IUse% Mounted on
 
/dev/sda5             262944    2364  260580    1% /var
 
tmpfs                2054508       6 2054502    1% /dev/shm
 
/dev/drbd0            655776     610  655166    1% /drbd
 
/dev/drbd1           1310720      14 1310706    1% /drbd1
 
/dev/drbd2           1024128      12 1024116    1% /drbd2
 
 
 
# ls -al /drbd/var/lib/
 
total 12
 
drwxr-xr-x  3 root  root  4096 Jun 12  2010 .
 
drwxr-xr-x  4 root  root  4096 Nov  6 13:26 ..
 
drwxr-xr-x 16 mysql mysql 4096 Mar  3 15:30 mysql

Now I can create the table in that default data location.

MariaDB [radius]> CREATE TABLE `radacct3` (  `radacctid` bigint(20) NOT NULL AUTO_INCREMENT,   `acctsessionid` varchar(64) NOT NULL DEFAULT '',   `acctuniqueid` varchar(32) NOT NULL DEFAULT '',   `username` varchar(64) NOT NULL DEFAULT '',   `groupname` varchar(64) NOT NULL DEFAULT '',   `realm` varchar(64) DEFAULT '',   `nasipaddress` varchar(15) NOT NULL DEFAULT '',   `nasportid` varchar(15) DEFAULT NULL,   `nasporttype` varchar(32) DEFAULT NULL,   `acctstarttime` datetime DEFAULT NULL,   `acctstoptime` datetime DEFAULT NULL,   `acctsessiontime` int(11) DEFAULT NULL,   `acctauthentic` varchar(32) DEFAULT NULL,   `connectinfo_start` varchar(50) DEFAULT NULL,   `connectinfo_stop` varchar(50) DEFAULT NULL,   `acctinputoctets` bigint(20) DEFAULT NULL,   `acctoutputoctets` bigint(20) DEFAULT NULL,   `calledstationid` varchar(50) NOT NULL DEFAULT '',   `callingstationid` varchar(50) NOT NULL DEFAULT '',   `acctterminatecause` varchar(32) NOT NULL DEFAULT '',   `servicetype` varchar(32) DEFAULT NULL,   `framedprotocol` varchar(32) DEFAULT NULL,   `framedipaddress` varchar(15) NOT NULL DEFAULT '',   `acctstartdelay` int(11) DEFAULT NULL,   `acctstopdelay` int(11) DEFAULT NULL,   `acctupdatetime` datetime DEFAULT NULL,   `deleted` char(1) DEFAULT '',   `objectclass` varchar(64) DEFAULT 'AAA-SessionInfo',   PRIMARY KEY (`radacctid`),   KEY `username` (`username`),   KEY `framedipaddress` (`framedipaddress`),   KEY `acctsessionid` (`acctsessionid`),   KEY `acctsessiontime` (`acctsessiontime`),   KEY `acctuniqueid` (`acctuniqueid`),   KEY `acctstarttime` (`acctstarttime`),   KEY `acctstoptime` (`acctstoptime`),   KEY `nasipaddress` (`nasipaddress`),   KEY `acctupdatetime` (`acctupdatetime`),   KEY `deleted` (`deleted`),   KEY `objectclass` (`objectclass`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
Query OK, 0 rows affected (0.12 sec)

That would imply that the isl file can be created in the default directory and the ibd file also in the default directory.
So no issues of space or inode or permissions.

MariaDB [radius]> drop table radacct3;
 
Query OK, 0 rows affected (0.03 sec)

But I cannot create the table with the data directory in another location.

MariaDB [radius]> CREATE TABLE `radacct3` (  `radacctid` bigint(20) NOT NULL AUTO_INCREMENT,   `acctsessionid` varchar(64) NOT NULL DEFAULT '',   `acctuniqueid` varchar(32) NOT NULL DEFAULT '',   `username` varchar(64) NOT NULL DEFAULT '',   `groupname` varchar(64) NOT NULL DEFAULT '',   `realm` varchar(64) DEFAULT '',   `nasipaddress` varchar(15) NOT NULL DEFAULT '',   `nasportid` varchar(15) DEFAULT NULL,   `nasporttype` varchar(32) DEFAULT NULL,   `acctstarttime` datetime DEFAULT NULL,   `acctstoptime` datetime DEFAULT NULL,   `acctsessiontime` int(11) DEFAULT NULL,   `acctauthentic` varchar(32) DEFAULT NULL,   `connectinfo_start` varchar(50) DEFAULT NULL,   `connectinfo_stop` varchar(50) DEFAULT NULL,   `acctinputoctets` bigint(20) DEFAULT NULL,   `acctoutputoctets` bigint(20) DEFAULT NULL,   `calledstationid` varchar(50) NOT NULL DEFAULT '',   `callingstationid` varchar(50) NOT NULL DEFAULT '',   `acctterminatecause` varchar(32) NOT NULL DEFAULT '',   `servicetype` varchar(32) DEFAULT NULL,   `framedprotocol` varchar(32) DEFAULT NULL,   `framedipaddress` varchar(15) NOT NULL DEFAULT '',   `acctstartdelay` int(11) DEFAULT NULL,   `acctstopdelay` int(11) DEFAULT NULL,   `acctupdatetime` datetime DEFAULT NULL,   `deleted` char(1) DEFAULT '',   `objectclass` varchar(64) DEFAULT 'AAA-SessionInfo',   PRIMARY KEY (`radacctid`),   KEY `username` (`username`),   KEY `framedipaddress` (`framedipaddress`),   KEY `acctsessionid` (`acctsessionid`),   KEY `acctsessiontime` (`acctsessiontime`),   KEY `acctuniqueid` (`acctuniqueid`),   KEY `acctstarttime` (`acctstarttime`),   KEY `acctstoptime` (`acctstoptime`),   KEY `nasipaddress` (`nasipaddress`),   KEY `acctupdatetime` (`acctupdatetime`),   KEY `deleted` (`deleted`),   KEY `objectclass` (`objectclass`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 data directory="/drbd2";
 
ERROR 1005 (HY000): Can't create table `ant_truauth`.`radacct3` (errno: -1 "Internal error < 0 (Not system error)")

I've also tried it such that the data directory is in a plain old ext3 partiton, it also creates this error, hence its not the drbd partition.

Regards,

Michael

Comment by Elena Stepanova [ 2016-03-07 ]

According to the output that you pasted, there seems to be something weird with databases on your instance.
By default, MariaDB prompt is MariaDB [<database>]>, I assume you didn't change it.

So, in the initial description you pasted the following:

MariaDB [ant_truauth]> CREATE TABLE `radacct3` ( ... ) ENGINE=Innodb data directory='/drbd2';
 
ERROR 1005 (HY000): Can't create table `radius`.`radacct3` (errno: -1 "Internal error < 0 (Not system error)")

So, your current database is ant_truauth, you create a table without schema name specification, so it should be created in the current schema. However, according to the error message, server attempts to create it in radius schema.

In your last comment, it's the other way round – the current schema is radius, but the table is being created in ant_truauth.
Can you shed any light on this? Which databases do you actually have? 

ariaDB [radius]> CREATE TABLE `radacct3` (  ... ) ENGINE=InnoDB DEFAULT CHARSET=latin1 data directory="/drbd2";
 
ERROR 1005 (HY000): Can't create table `ant_truauth`.`radacct3` (errno: -1 "Internal error < 0 (Not system error)")

Comment by Michael Van Der Beek [ 2016-03-08 ]

Hi Elena,

Sorry I didn't want the real database name to be show so I changed it to radius from ant_truauth

Regards,

Michael

Comment by Elena Stepanova [ 2016-03-08 ]

Michaelv,
Many sources blame innodb_use_native_aio for this error. While I suppose it shouldn't be a problem with ext3 at least, I think it's worth checking if the error disappears on a server started with innodb_use_native_aio=0.

If it does not help, please attach your cnf file; please also check the contents of both radius (ant_truauth) subdirs under /drbd/var/lib/mysql and /drbd2.

Comment by Michael Van Der Beek [ 2016-03-09 ]

Hi Elena,

I've tried both innodb_use_native_aio=0 and =1 both still creates this error.
This editor in this web site seems to not like "hash space" it takes it as an indent

/dev/drbd0 on /drbd type ext3 (rw,noatime,nodiratime,barrier=0)
 
/dev/drbd1 on /drbd1 type ext3 (rw,noatime,nodiratime,barrier=0)
 
/dev/drbd2 on /drbd2 type ext3 (rw,noatime,nodiratime,barrier=0)


[root@truauth1 ~]# ls -al /drbd2/*
 
/drbd2/ant_truauth:
 
total 8
 
drwxrwx--- 2 mysql mysql 4096 Mar  9 09:58 .
 
drwxr-xr-x 4 mysql mysql 4096 Mar  3 15:24 ..
 
 
 
/drbd2/lost+found:
 
total 20
 
drwx------ 2 mysql mysql 16384 Mar  3 12:12 .
 
drwxr-xr-x 4 mysql mysql  4096 Mar  3 15:24 ..


[root@truauth1 ~]# ls -al /drbd/var/lib/mysql/ant_truauth/
 
total 683548
 
drwx------  2 mysql mysql      4096 Mar  9 10:01 .
 
drwxr-xr-x 16 mysql mysql      4096 Mar  9 10:01 ..
 
-rw-rw----  1 mysql mysql      2614 Feb 12 13:50 ant_attribute.frm
 
-rw-rw----  1 mysql mysql    212992 Feb 12 13:50 ant_attribute.ibd
 
-rw-rw----  1 mysql mysql      2000 Feb 12 13:50 ant_attribute_values_enum.frm
 
-rw-rw----  1 mysql mysql    131072 Feb 12 13:50 ant_attribute_values_enum.ibd
 
-rw-rw----  1 mysql mysql      1358 Feb 12 13:50 ant_config.frm
 
-rw-rw----  1 mysql mysql     98304 Feb 12 13:50 ant_config.ibd
 
-rw-rw----  1 mysql mysql      1964 Feb 12 13:50 ant_group_attribute_grp.frm
 
-rw-rw----  1 mysql mysql    131072 Feb 12 13:50 ant_group_attribute_grp.ibd
 
-rw-rw----  1 mysql mysql      2318 Feb 12 13:50 ant_group_attribute_indiv.frm
 
-rw-rw----  1 mysql mysql    131072 Feb 12 13:50 ant_group_attribute_indiv.ibd
 
-rw-rw----  1 mysql mysql      1969 Feb 12 13:50 ant_group.frm
 
-rw-rw----  1 mysql mysql    114688 Feb 12 13:50 ant_group.ibd
 
-rw-rw----  1 mysql mysql      1967 Feb 12 13:50 ant_grouptemplate_attribute_grp.frm
 
-rw-rw----  1 mysql mysql    131072 Feb 12 13:50 ant_grouptemplate_attribute_grp.ibd
 
-rw-rw----  1 mysql mysql      1967 Feb 12 13:50 ant_grouptemplate_attribute_indiv.frm
 
-rw-rw----  1 mysql mysql    131072 Feb 12 13:50 ant_grouptemplate_attribute_indiv.ibd
 
-rw-rw----  1 mysql mysql      1922 Feb 12 13:50 ant_grouptemplate.frm
 
-rw-rw----  1 mysql mysql    114688 Feb 12 13:50 ant_grouptemplate.ibd
 
-rw-rw----  1 mysql mysql      1116 Feb 12 13:50 ant_mobile_info.frm
 
-rw-rw----  1 mysql mysql     98304 Feb 12 13:50 ant_mobile_info.ibd
 
-rw-rw----  1 mysql mysql      9243 Feb 12 13:50 ant_user_info.frm
 
-rw-rw----  1 mysql mysql    294912 Feb 12 13:50 ant_user_info.ibd
 
-rw-rw----  1 mysql mysql      2517 Feb 12 13:50 ant_vendor.frm
 
-rw-rw----  1 mysql mysql    131072 Feb 12 13:50 ant_vendor.ibd
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_10.frm
 
-rw-rw----  1 mysql mysql         0 Nov  6 13:26 a_radacct_closed_10.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 12 13:50 a_radacct_closed_10.MYI
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_11.frm
 
-rw-rw----  1 mysql mysql         0 Nov  6 13:26 a_radacct_closed_11.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 12 13:50 a_radacct_closed_11.MYI
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_12.frm
 
-rw-rw----  1 mysql mysql         0 Nov  6 13:26 a_radacct_closed_12.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 12 13:50 a_radacct_closed_12.MYI
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_1.frm
 
-rw-rw----  1 mysql mysql         0 Nov  6 13:26 a_radacct_closed_1.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 12 13:50 a_radacct_closed_1.MYI
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_2.frm
 
-rw-rw----  1 mysql mysql       156 Feb 19 12:08 a_radacct_closed_2.MYD
 
-rw-rw----  1 mysql mysql     10240 Feb 19 12:08 a_radacct_closed_2.MYI
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_3.frm
 
-rw-rw----  1 mysql mysql         0 Nov  6 13:26 a_radacct_closed_3.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 12 13:50 a_radacct_closed_3.MYI
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_4.frm
 
-rw-rw----  1 mysql mysql         0 Mar  2 01:00 a_radacct_closed_4.MYD
 
-rw-rw----  1 mysql mysql      1024 Mar  2 01:00 a_radacct_closed_4.MYI
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_5.frm
 
-rw-rw----  1 mysql mysql         0 Mar  2 01:00 a_radacct_closed_5.MYD
 
-rw-rw----  1 mysql mysql      1024 Mar  2 01:00 a_radacct_closed_5.MYI
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_6.frm
 
-rw-rw----  1 mysql mysql         0 Mar  2 01:00 a_radacct_closed_6.MYD
 
-rw-rw----  1 mysql mysql      1024 Mar  2 01:00 a_radacct_closed_6.MYI
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_7.frm
 
-rw-rw----  1 mysql mysql         0 Mar  2 01:00 a_radacct_closed_7.MYD
 
-rw-rw----  1 mysql mysql      1024 Mar  2 01:00 a_radacct_closed_7.MYI
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_8.frm
 
-rw-rw----  1 mysql mysql         0 Mar  2 01:00 a_radacct_closed_8.MYD
 
-rw-rw----  1 mysql mysql      1024 Mar  2 01:00 a_radacct_closed_8.MYI
 
-rw-rw----  1 mysql mysql      9664 Feb 12 13:50 a_radacct_closed_9.frm
 
-rw-rw----  1 mysql mysql         0 Mar  2 01:00 a_radacct_closed_9.MYD
 
-rw-rw----  1 mysql mysql      1024 Mar  2 01:00 a_radacct_closed_9.MYI
 
-rw-rw----  1 mysql mysql      6720 Feb 12 13:50 a_radacct.frm
 
-rw-rw----  1 mysql mysql 230686720 Feb 24 16:14 a_radacct.ibd
 
-rw-rw----  1 mysql mysql       444 Feb 24 11:02 a_radacct_temp.frm
 
-rw-rw----  1 mysql mysql     98304 Feb 24 11:03 a_radacct_temp.ibd
 
-rw-rw----  1 mysql mysql      9710 Feb 12 13:50 a_radacct_tmp.frm
 
-rw-rw----  1 mysql mysql         0 Jul 17  2014 a_radacct_tmp.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 12 13:50 a_radacct_tmp.MYI
 
-rw-rw----  1 mysql mysql      2965 Feb 12 13:50 cluster_list_display.frm
 
-rw-rw----  1 mysql mysql    131072 Feb 12 13:50 cluster_list_display.ibd
 
-rw-rw----  1 mysql mysql        65 Jun  9  2010 db.opt
 
-rw-rw----  1 mysql mysql      8598 Feb 12 13:50 ldapuser.frm
 
-rw-rw----  1 mysql mysql         0 Jul 17  2014 ldapuser.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 12 13:50 ldapuser.MYI
 
-rw-rw----  1 mysql mysql      2098 Feb 12 13:50 nas.frm
 
-rw-rw----  1 mysql mysql    114688 Feb 12 13:50 nas.ibd
 
-rw-rw----  1 mysql mysql      7972 Feb 24 10:57 radacct2.frm
 
-rw-rw----  1 mysql mysql         0 Feb 24 10:57 radacct2.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 24 10:57 radacct2.MYI
 
-rw-rw----  1 mysql mysql      7963 Mar  3 13:57 radacct.frm
 
-rw-rw----  1 mysql mysql    278528 Mar  3 13:57 radacct.ibd
 
-rw-rw----  1 mysql mysql      1890 Feb 12 13:40 radcheck.frm
 
-rw-rw----  1 mysql mysql         0 Feb 12 13:40 radcheck.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 12 13:50 radcheck.MYI
 
-rw-rw----  1 mysql mysql      1861 Feb 12 13:40 radgroupcheck.frm
 
-rw-rw----  1 mysql mysql       184 Feb 12 13:40 radgroupcheck.MYD
 
-rw-rw----  1 mysql mysql      3072 Feb 12 13:50 radgroupcheck.MYI
 
-rw-rw----  1 mysql mysql      1861 Feb 12 13:40 radgroupreply.frm
 
-rw-rw----  1 mysql mysql        96 Feb 12 13:40 radgroupreply.MYD
 
-rw-rw----  1 mysql mysql      3072 Feb 12 13:50 radgroupreply.MYI
 
-rw-rw----  1 mysql mysql      2881 Mar  2 16:33 radippool.frm
 
-rw-rw----  1 mysql mysql 465567744 Mar  2 19:18 radippool.ibd
 
-rw-rw----  1 mysql mysql      1185 Feb 12 13:40 radpostauth.frm
 
-rw-rw----  1 mysql mysql         0 Feb 12 13:40 radpostauth.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 12 13:50 radpostauth.MYI
 
-rw-rw----  1 mysql mysql      1860 Feb 12 13:40 radreply.frm
 
-rw-rw----  1 mysql mysql         0 Feb 12 13:40 radreply.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 12 13:50 radreply.MYI
 
-rw-rw----  1 mysql mysql      1110 Feb 12 13:40 radusergroup.frm
 
-rw-rw----  1 mysql mysql         0 Feb 12 13:40 radusergroup.MYD
 
-rw-rw----  1 mysql mysql      1024 Feb 12 13:50 radusergroup.MYI
 
-rw-rw----  1 mysql mysql      1571 Feb 12 13:50 Relogin.frm
 
-rw-rw----  1 mysql mysql    114688 Feb 12 13:50 Relogin.ibd
 
-rw-rw----  1 mysql mysql      8674 Jul 17  2014 SIMTMSI2.frm
 
-rw-rw----  1 mysql mysql         0 Jul 17  2014 SIMTMSI2.MYD
 
-rw-rw----  1 mysql mysql      1024 Jul 17  2014 SIMTMSI2.MYI
 
-rw-rw----  1 mysql mysql      8590 Jul 17  2014 SIMTMSI.frm
 
-rw-rw----  1 mysql mysql         0 Jul 17  2014 SIMTMSI.MYD
 
-rw-rw----  1 mysql mysql      1024 Jul 17  2014 SIMTMSI.MYI
 
-rw-rw----  1 mysql mysql      8930 Jul 17  2014 SIMUSER.frm
 
-rw-rw----  1 mysql mysql         0 Jul 17  2014 SIMUSER.MYD
 
-rw-rw----  1 mysql mysql      1024 Jul 17  2014 SIMUSER.MYI
 
-rw-rw----  1 mysql mysql      8828 Jul 17  2014 TRIPLET.frm
 
-rw-rw----  1 mysql mysql         0 Jul 17  2014 TRIPLET.MYD
 
-rw-rw----  1 mysql mysql      1024 Jul 17  2014 TRIPLET.MYI

/etc/my.cnf

 
[client]
 
host            = localhost
 
port            = 3306
 
user            = root
 
password        = xxxxxx
 
socket          = /var/lib/mysql/mysql.sock
 
 
 
[mysqld]
 
tmpdir=/dev/shm
 
skip-name-resolve
 
wait_timeout=43200
 
#Increase connect_timeout from 5 to 10
 
connect_timeout=10
 
#Decrease interactive_timeout from 28800 to 100
 
interactive_timeout=120
 
#Increase join_buffer_size from 131072 to 1M
 
join_buffer_size=32K
 
#Increase query_cache_size from 0 to 128M
 
query_cache_size=32M
 
#Increase query cache limit from 1048576 to 2M
 
query_cache_limit=256k
 
query_cache_min_res_unit = 2k
 
#Increase max allowed packet size from 1M to 16M
 
max_allowed_packet=16M
 
#Increase table cache cache from 256 to 1024
 
table_cache=1024
 
#Increase sort buffer size from 1M
 
sort_buffer_size=32K
 
#Increase read buffer size from 1M
 
read_buffer_size=1M
 
#Increase read_rnd_buffer_size to 4M
 
read_rnd_buffer_size=4M
 
#Other settings
 
max_connections=200
 
sort_buffer_size=2M
 
key_buffer_size=384M
 
#Increase until threads created does not grow anymore
 
thread_cache_size=256
 
#Try number of CPUs*2 for thread_concurrency
 
#thread_concurrency=4
 
datadir=/var/lib/mysql
 
socket=/var/lib/mysql/mysql.sock
 
user=mysql
 
#Default to using old password format for compatibility with mysql 3.x
 
#clients (those using the mysqlclient10 compatibility package).
 
old_passwords=1
 
 
 
#INNODB Tuning
 
low_priority_updates=1
 
back_log=1500
 
 
 
#files
 
innodb_file_per_table
 
innodb_log_file_size = 512M
 
innodb_log_buffer_size= 32M
 
innodb_log_files_in_group = 3
 
innodb_open_files = 1024
 
table_open_cache = 4000
 
table_open_cache_instances = 8
 
 
 
#buffers
 
innodb_buffer_pool_size=4G
 
innodb_buffer_pool_instances=4
 
innodb_log_buffer_size = 64M
 
 
 
#tuning
 
#innodb_use_atomic_writes = 1
 
innodb_checksum_algorithm=NONE
 
innodb_doublewrite = 0
 
innodb_support_xa = 0
 
innodb_thread_concurrency =0
 
innodb_flush_log_at_trx_commit = 0
 
sync_binlog = 0
 
innodb_flush_method = O_DIRECT
 
innodb_max_dirty_pages_pct=50
 
innodb_use_native_aio = 0
 
innodb_stats_persistent = 1
 
innodb_spin_wait_delay = 6
 
 
 
#perf special
 
innodb_adaptive_flushing =1
 
innodb_flush_neighbors = 0
 
#innodb_flush_neighbor_pages=cont
 
innodb_old_blocks_time=1000
 
innodb_read_io_threads = 64
 
innodb_write_io_threads = 64
 
innodb_io_capacity=100
 
innodb_purge_threads =1
 
innodb_adaptive_hash_index =1
 
 
 
#Monitoring (Optiona)
 
innodb_monitor_enable = '%'
 
performance_schema = ON
 
performance_schema_instrument = '%=on'
 
 
 
#MYISAM tuning
 
delayed_queue_size=10000
 
delayed_insert_limit=300
 
max_delayed_threads=40
 
slow-query-log=1
 
slow-query-log-file = /var/log/mysql-slow.log
 
long_query_time=1
 
 
 
 
 
#bind to local host only
 
bind-address=127.0.0.1
 
 
 
#Disabling symbolic-links is recommended to prevent assorted security risks;
 
#to do so, uncomment this line:
 
#symbolic-links=0
 
 
 
[mysqld_safe]
 
log-error=/var/log/mysqld.log
 
pid-file=/var/run/mysqld/mysqld.pid

Comment by Elena Stepanova [ 2016-03-09 ]

Thanks, I can now reproduce it.
The culprit here is innodb_flush_method=O_DIRECT, as a workaround please try to remove it.

The problem also exists in MySQL 5.6, but is apparently fixed in MySQL 5.7. I assume it will be fixed in MariaDB 10.2 automatically after the merge of InnoDB/XtraDB 5.7, but I will assign it to jplindst to see if he considers it important enough to backport the fix to 10.1. For this purpose, I am setting the Fix version to 10.1 – it is not a commitment, it can be changed to a higher value after consideration.

Comment by Michael Van Der Beek [ 2016-03-11 ]

Hi Elena,

Yes confirmed that the O_DIRECT is the problem.

Thanks for your help.

O_DIRECT is to allow for faster writes as it removes double buffering to the harddisk. This can cause performance issues.

From the "Affects version 10.0" line, I guess it affects 10.0 also.

If its not going to be ported to 10.1 line, can you tell me when is the 10.2 version expected?

Regards,

Michael

Comment by Elena Stepanova [ 2016-03-11 ]

Michaelv,
We don't have information about 10.2 release date yet, but it's not going to become production-ready any time soon.
For whether the fix will be ported to 10.1, let's wait for jplindst decision, he's an innoDB expert and will be able to conclude whether it is technically possible and safe enough to have it in 10.1.

Comment by Jan Lindström (Inactive) [ 2016-03-18 ]

Hmm, what file system you have on that directory, I could not yet repeat using DATA_DIRECTORY='/home/jan' but I have:

/dev/sdb1 on / type ext4 (rw,relatime,errors=remount-ro,data=ordered)

Comment by Jan Lindström (Inactive) [ 2016-03-23 ]

commit f71c45c71db8c7862fc50b8382ccdbff4772e258
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Tue Mar 22 17:55:23 2016 +0200

MDEV-9678: Data Directory bug

Problem was that link file (.isl) is also opened using O_DIRECT
mode and if this fails the whole create table fails on internal
error.

Fixed by not using O_DIRECT on link files as they are used only
on create table and startup and do not contain real data.
O_DIRECT failures are successfully ignored for data files
if O_DIRECT is not supported by file system on used
data directory.

Comment by Ariel Filotti [ 2016-04-08 ]

I was using innodb_flush_method = O_DIRECT in my server config. This is recommended in the Percona Configuration Wizard:

"If you are using Linux, then you should usually open files in O_DIRECT mode unless you know that another method is better."

After applying this commit, the performance of the slave SQL thread decreased tenfold, to the point where the slave started to lag behind the master. I had to remove innodb_flush_method from the server config, and then the performance got back up to where it used to be.

I tested this on CentOS 7 and Ubuntu 16.04 (Beta2).

Comment by Axel Schwenke [ 2016-05-04 ]

filotti - this regression is MDEV-9916. It's a pity that your comment wasn't noticed before.

Generated at Thu Feb 08 07:36:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.