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
? [email protected] ? https://www.crc.id.au
? +61 (3) 9001 6090 ? 0412 935 897
