Can't create/write to file /tmp/#sql...

mbsmt

mbsmt

Verified User
Joined
Jul 24, 2013
Messages
175
Location
Mashhad, Iran
Hello. It's about a month that I have a strange issue on MariaDB. For each customer, a same platform installed on the server which retrieve the records of database in each page. Everything works well, but everyday at about 7:45AM we detect an error while running one pages of that application for customers.
This is a sample bug we receive:

[STDERR] PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1 Can't create/write to file '/tmp/#sql_5a34_2.MAI' (Errcode: 2 "No such file or directory") in /home/crmuser/domains/crmuser.com/public_html/view/tickets.php:832
Click to expand...

As we reset the mysqld service on the server, everything goes well and work without any further issue to the next day!

This is the condition of partitions:

1622369628848.png

May you help me to solve the problem please?
 
Zhenyapan said:
and what you see in mysql log?
Click to expand...
Nothing special.
This is everything visible in MySQL log file:

Code: 
InnoDB: Database physically writes the file full: wait...
190810 12:40:40  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
190810 12:40:41  InnoDB: Waiting for the background threads to start
190810 12:40:42 Percona XtraDB (http://www.percona.com) 5.5.61-MariaDB-38.13 started; log sequence number 0
190810 12:40:42 [Note] Plugin 'FEEDBACK' is disabled.
190810 12:40:42 [Note] Server socket created on IP: '0.0.0.0'.
190810 12:40:42 [Note] Event Scheduler: Loaded 0 events
190810 12:40:42 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.63-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
190810 13:41:51 [Note] /usr/sbin/mysqld: Normal shutdown
190810 13:41:51 [Note] Event Scheduler: Purging the queue. 0 events
190810 13:41:51  InnoDB: Starting shutdown...
190810 13:41:53  InnoDB: Shutdown completed; log sequence number 1668254
190810 13:41:53 [Note] /usr/sbin/mysqld: Shutdown complete

As the time in the log file is not similar to the time of problem.
 
I don't know how your /tmp directory is setup for permissions.
You might want to try this:
chmod 1777 /tmp
 
Richard G said:
I don't know how your /tmp directory is setup for permissions.
You might want to try this:
chmod 1777 /tmp
Click to expand...
I applied the chmod as you said, and will wait for its effect. But the strangest issue is that this problem happens only once about 7:30AM. I reset the mysqld service at 7AM to prevent the problem, but it happens! When I reset the mysqld again, everything goes well to the next day!!!
 
Last edited:
That is indeed very odd. This seems some cronjob causing the issue.
It might be caused by that ticket application then maybe? I don't know.
 
Richard G said:
That is indeed very odd. This seems some cronjob causing the issue.
It might be caused by that ticket application then maybe? I don't know.
Click to expand...
No it is not for the ticketing application as it opens different tickets. Just on opening some tickets this problem happens. Also, all other sections of application work well without any issue!
 
mbsmt said:
Just on opening some tickets this problem happens.
Click to expand...
Yes but if this is the only application causing this, it still might (most likely even) be an application bug, especially since this happens on 07.30 AM, there must be something specifically running at that time to be causing this.
People don't create tickets exactly at 07.30 am every day. So that is what I'm wondering about. Somewhere in some log there must be something to be found.
However, since I'm not a mysql guru, I also can point to things which can be found on the net which you probably also looked at.

But since your reply I presume the chmod command had no effect and the issue occured again this morning?
 
Richard G said:
Yes but if this is the only application causing this, it still might (most likely even) be an application bug, especially since this happens on 07.30 AM, there must be something specifically running at that time to be causing this.
People don't create tickets exactly at 07.30 am every day. So that is what I'm wondering about. Somewhere in some log there must be something to be found.
However, since I'm not a mysql guru, I also can point to things which can be found on the net which you probably also looked at.

But since your reply I presume the chmod command had no effect and the issue occured again this morning?
Click to expand...
Richard, I ran your suggested code for chmod at the time of problem occurrence, but the issue did not solve with this solution.
I'm checking something else to find the problem. But I am sure that the software itself is not the matter as it works without any issue in other times.
 
Peter Laws said:
See matigo's answer here https://stackoverflow.com/questions...ral-error-1-cant-create-write-to-file-tmp-sql.

If it happens at the set time, must be a cron, if so, what does it do?
Click to expand...
The question in this like is for me, and I saw his answer too. It was not the solution.
I commented some lines in crontab and the problem did not repeat. I don't know which one was the reason of that problem
The commented lines are:

Code: 
0 4 * * * rm -rf /tmp/systemd-private-*
0 4 * * * service lsws restart

The application which is running on this server is for enterprises and SMEs. So they work with it in the working hours. To remove the session temporary files we created the second line which reset the litespeed service. But I don't remember what was the reason of creation the first line in the crontab. It might be the reason of issue I have mentioned in this topic. I am not sure...
 
You must log in or register to reply here.
Back
Top