On 2018-05-15 14:33, Lofgren, Eric wrote:
> I’m running a SL 7 server that’s being used for a couple data projects
> using MySQL Server - which I confess I don’t know much about. Two have
> my students have both encountered errors recently, either joining
> tables or just restarting MySQL Server, with the logs reading things
> to the effect of:
> 
> 2018-05-15T01:26:21.114290Z 0 [Warning] TIMESTAMP with implicit
> DEFAULT value is deprecated. Please use
> --explicit_defaults_for_timestamp server option (see documentation for
> more details).
> 2018-05-15T01:26:21.115489Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.22)
> starting as process 20634 ...
> 2018-05-15T01:26:21.117733Z 0 [Note] InnoDB: PUNCH HOLE support 
> available
> 2018-05-15T01:26:21.117758Z 0 [Note] InnoDB: Mutexes and rw_locks use
> GCC atomic builtins
> 2018-05-15T01:26:21.117763Z 0 [Note] InnoDB: Uses event mutexes
> 2018-05-15T01:26:21.117765Z 0 [Note] InnoDB: GCC builtin
> __atomic_thread_fence() is used for memory barrier
> 2018-05-15T01:26:21.117768Z 0 [Note] InnoDB: Compressed tables use zlib 
> 1.2.3
> 2018-05-15T01:26:21.117770Z 0 [Note] InnoDB: Using Linux native AIO
> 2018-05-15T01:26:21.117949Z 0 [Note] InnoDB: Number of pools: 1
> 2018-05-15T01:26:21.118023Z 0 [Note] InnoDB: Using CPU crc32 
> instructions
> 2018-05-15T01:26:21.118970Z 0 [Note] InnoDB: Initializing buffer pool,
> total size = 128M, instances = 1, chunk size = 128M
> 2018-05-15T01:26:21.124401Z 0 [Note] InnoDB: Completed initialization
> of buffer pool
> 2018-05-15T01:26:21.125581Z 0 [Note] InnoDB: If the mysqld execution
> user is authorized, page cleaner thread priority can be changed. See
> the man page of setpriority().
> 2018-05-15T01:26:21.136851Z 0 [Note] InnoDB: Highest supported file
> format is Barracuda.
> 2018-05-15T01:26:21.152895Z 0 [Note] InnoDB: Creating shared
> tablespace for temporary tables
> 2018-05-15T01:26:21.153010Z 0 [Note] InnoDB: Setting file './ibtmp1'
> size to 12 MB. Physically writing the file full; Please wait ...
> 2018-05-15T01:26:21.155633Z 0 [Warning] InnoDB: 1048576 bytes should
> have been written. Only 970752 bytes written. Retrying for the
> remaining bytes.
> 2018-05-15T01:26:21.199054Z 0 [Warning] InnoDB: 1048576 bytes should
> have been written. Only 610304 bytes written. Retrying for the
> remaining bytes.
> 2018-05-15T01:26:21.229038Z 0 [Warning] InnoDB: 1048576 bytes should
> have been written. Only 622592 bytes written. Retrying for the
> remaining bytes.
> 2018-05-15T01:26:21.256550Z 0 [Warning] InnoDB: 1048576 bytes should
> have been written. Only 626688 bytes written. Retrying for the
> remaining bytes.
> 2018-05-15T01:26:21.282360Z 0 [Warning] InnoDB: 1048576 bytes should
> have been written. Only 630784 bytes written. Retrying for the
> remaining bytes.
> 2018-05-15T01:26:21.306692Z 0 [Warning] InnoDB: 1048576 bytes should
> have been written. Only 634880 bytes written. Retrying for the
> remaining bytes.
> 2018-05-15T01:26:21.330202Z 0 [Warning] InnoDB: 1048576 bytes should
> have been written. Only 638976 bytes written. Retrying for the
> remaining bytes.
> 2018-05-15T01:26:21.352910Z 0 [Warning] InnoDB: 1048576 bytes should
> have been written. Only 643072 bytes written. Retrying for the
> remaining bytes.
> 2018-05-15T01:26:21.375361Z 0 [Warning] InnoDB: Retry attempts for
> writing partial data failed.
> 2018-05-15T01:26:21.375391Z 0 [ERROR] InnoDB: Write to file
> ./ibtmp1failed at offset 11534336, 1048576 bytes should have been
> written, only 643072 were written. Operating system error number 28.
> Check that your OS and file system support files of this size. Check
> also that the disk is not full or a disk quota exceeded.
> 2018-05-15T01:26:21.375406Z 0 [ERROR] InnoDB: Error number 28 means
> 'No space left on device'
> 2018-05-15T01:26:21.375410Z 0 [Note] InnoDB: Some operating system
> error numbers are described at
> http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
> 2018-05-15T01:26:21.375416Z 0 [ERROR2018-05-15T01:26:21.845562Z 0
> [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
> 2018-05-15T01:26:21.845603Z 0 [ERROR] Plugin 'InnoDB' init function
> returned error.
> 2018-05-15T01:26:21.845612Z 0 [ERROR] Plugin 'InnoDB' registration as
> a STORAGE ENGINE failed.
> 2018-05-15T01:26:21.845621Z 0 [ERROR] Failed to initialize builtin 
> plugins.
> 2018-05-15T01:26:21.845626Z 0 [ERROR] Aborting
> 
> 2018-05-15T01:26:21.845658Z 0 [Note] Binlog end
> 2018-05-15T01:26:21.845742Z 0 [Note] Shutting down plugin 'CSV'
> 2018-05-15T01:26:21.846165Z 0 [Note] /usr/sbin/mysqld: Shutdown 
> complete
> 
> A quick check of whether or not that’s genuinely a disk space problem
> using df -hT shows the following:
> 
> [user@computer home]$ df -hT
> Filesystem          Type      Size  Used Avail Use% Mounted on
> /dev/mapper/sl-root xfs        50G   50G   12M 100% /
> devtmpfs            devtmpfs   16G     0   16G   0% /dev
> tmpfs               tmpfs      16G   28M   16G   1% /dev/shm
> tmpfs               tmpfs      16G  242M   16G   2% /run
> tmpfs               tmpfs      16G     0   16G   0% /sys/fs/cgroup
> tmpfs               tmpfs      16G   36K   16G   1% /tmp
> /dev/sda1           xfs       497M  230M  268M  47% /boot
> /dev/mapper/sl-home xfs       411G   14G  397G   4% /home
> tmpfs               tmpfs     3.2G   40K  3.2G   1% /run/user/1000
> tmpfs               tmpfs     3.2G     0  3.2G   0% /run/user/0
> 
> The database lives on /home, which doesn’t seem anywhere near full,
> and the number of bytes that “should have been written” in that error
> message are nowhere near a threat to the capacity, which suggests
> something else might be going wrong.
> 
> Does anyone have a notion of what’s going on? My suspicion is that the
> problem is that the temporary file is just ./file, which is in the
> root directory, which as you can see *is* full - if this is the case,
> is there a way to redirect where those temporary files are made? Or
> should I just try to expand that?

Your ibtmp1 file will be written to /var/lib/mysql/ by default. That's 
on a partition that is full.

Either reconfigure mysql to create this elsewhere, or fix the ugly 
partition system :)

-- 
Steven Haigh

? [log in to unmask]     ? https://www.crc.id.au
? +61 (3) 9001 6090    ? 0412 935 897