I'm getting a bunch of errors trying to access my MySQL database, which seem related, but which kick in at different times. So I had a Django/Celery (using RabbitMQ) framework running machine learning jobs distributed across three different computers with a MySQL database to store the results in, and I all of the sudden couldn't access any of my data from Django. I tried to access the database directly as well, but no luck. When I ran sudo service mysql status it said it was stopped, so I restarted the service and then the computer with no luck.
The big issue is that I seem to be getting inconsistent and intermittent errors, and I can't seem to piece them together into one problem. For example, sometimes when I run mysql -u root -p or mysqldump commands (to at least try and save the data I've collected), I get a
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
Although I've checked, and that socket is there at that path, and that's the same path that my /etc/mysql/my.cnf file specifies under socket. According to answers at here and elsewhere, I've also tried to change permissions, and change my bind-address in my.cnf from 0.0.0.0 to be 127.0.0.1, localhost, omitted the line altogether, and all to no avail. Other times the mysql command will work, but when I try and say use database_name, then it will fail with the same error.
Other times I get
mysqldump: Got error: 2013: Lost ocnnection to MySQL server at 'reading initial communication packet', system error: 104 when trying to connect.
or
ERROR 2013 (HY000): Lost connection to MySQL server during query
I've looked for answers to this error here and elsewhere, but I'm on the actual machine where the server is, and so network issues don't seem to make sense to me.
If I do manage to get in a little ways, I often end up running into
Error 2006 (HY000): MySQL server has gone away
Followed by the usual Error 2002.
I managed to run a mysqlcheck at one point to analyze my databases, and it looked okay, but I have to get lucky because I mostly get these same errors there and when trying to run a mysqldump as well. I've also tried a lot of the suggestions here without a ton of luck.
Running sudo tail -n 50 /var/log/mysql/error.log gets me the following (although it doesn't seem that useful):
171004 11:20:14 InnoDB: Waiting for the background threads to start
171004 11:20:15 InnoDB: 5.5.50 started; log sequence number 10765763470
171004 11:20:15 [Note] Server hostname (bind-address): 'localhost'; port: 3306
171004 11:20:15 [Note] - 'localhost' resolves to '127.0.0.1';
171004 11:20:15 [Note] Server socket created on IP: '127.0.0.1'.
171004 11:20:15 [Note] Event Scheduler: Loaded 0 events
171004 11:20:15 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.50-0ubuntu0.14.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.adobe'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.macromedia'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.smartgit'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.adobe'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.macromedia'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.smartgit'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.adobe'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.macromedia'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.smartgit'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.adobe'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.macromedia'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.smartgit'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.adobe'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.macromedia'
171004 11:20:15 [ERROR] Invalid (old?) table or database name '.smartgit'
171004 11:20:17 InnoDB: Operating system error number 0 in a file operation.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'Linux aio'.
InnoDB: Cannot continue operation.
171004 11:20:17 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release.
Please use the full name instead.
171004 11:20:17 [Note] Plugin 'FEDERATED' is disabled.
171004 11:20:17 InnoDB: The InnoDB memory heap is disabled
171004 11:20:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins
171004 11:20:17 InnoDB: Compressed tables use zlib 1.2.8
171004 11:20:17 InnoDB: Using Linux native AIO
171004 11:20:17 InnoDB: Initializing buffer pool, size = 128.0M
171004 11:20:17 InnoDB: Completed initialization of buffer pool
171004 11:20:17 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 10765607749
171004 11:20:17 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 10765763470
171004 11:20:18 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
171004 11:20:18 InnoDB: Waiting for the background threads to start
EDIT: As requested, the my.cnf is here:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
#bind-address = localhost
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 400M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
