We had faced a weird error for one of our client under Emergency MySQL Support , it was a slave server. Client stated that whenever he tries to Start the replication it goes of loop of restarts. We had a sharing session with the client, At first we intended to rebuild the slave with a fresh backup from the master since the data size was 22G.

We have used Xtrabackup for quick and online backup, we had prepared the backup and the instance was started, till this point it was stable when we started the replication mysqld went in restart mode. We had tailed errorlog to see the live error in another session.

2016-07-21 12:44:53 9939 [ERROR] InnoDB: File ./ ((/userinfo.ibd: 'Linux aio' returned OS error 0. Cannot continue operation.

We were pretty confused on this we had a check at the entire configuration, like the checksum being used open files limit for the mysql user, os level logs “/var/log/messages” for any OS or hardware related error.

After checking all the above we went for another rebuild of the server since the data size was small.

Again we had the same error popping and the server went into a loop of restarts, we were totally perplexed on this, but this time we had started, mysqld directly from the shell, we had the below on the screen.

File size limit exceeded mysqld --defaults-file=/etc/my.cnf.

On seeing we believed the issue was due restriction at OS level.

On checking the os limit for user (ulimit) we found the below might be the issue.

This imposes a file size limit for the particular, but surprisingly here we had this restriction for the user ‘root’. When the replication is invoked the MySQL touches the tablespace of size 3GB (userinfo.idb) it triggers the restarts.

The desired value for file size should be set to `unlimited`.

The above can be set as

#ulimit –f unlimited or by editing the file “ /etc/security/limits.conf ” with respect to the user.

The client has too much security restriction on OS level and it is not possible to get it fixed from OS level.It needs multiple level approvals from the information security team.

Workaround:

Here we fixed the issue by switching the user to “mysql” (su – mysql), and initialised mysqld_safe .

Optimize performance, enhance reliability, and ensure data security with Mydbops' MySQL InnoDB Cluster Consulting and Support Services. Our team can help you fine-tune temporary tablespace configuration, troubleshoot performance issues, and implement best practices. Contact us today to schedule a free consultation.