I know this problem was asked several times, but no solution worked for me so far. I am struggling with this for over 2 weeks and I am out of options.
System:Ubuntu 18.04 LTS
MySQL version:mysql Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
I suspect it may be a problem with one large database (over 4GB) I had to recover from frm idb. I know the method of recovery was successful after recovery of single table I checked and the data was there, not sure if with the other tables something is not corrupted BUT:
I tried recovery method described here and problem is even when I do mysqlcheck --all-databases
I get error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '
so unable to check.
From what I see in syslog server also crashed when I log into mysql and do use epffilm;
Using other advice from SO i created /etc/my.cnf :
[mysql]
connect_timeout = 43200
max_allowed_packet = 2048M
net_buffer_length = 512M
debug-info = TRUE
also content of my /etc/mysql/my.cnf:
client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
#socket = /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
lc_messages = en_US
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
#
# * Fine Tuning
#
max_connections = 100
connect_timeout = 1000000
wait_timeout = 600
max_allowed_packet = 1073741824
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 32M
max_heap_table_size = 32M
net_read_timeout = 31536000
net_write_timeout = 31536000
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size = 128M
#open-files-limit = 2000
table_open_cache = 400
myisam_sort_buffer_size = 512M
concurrent_insert = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit = 128K
query_cache_size = 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type = DEMAND
#
# * 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 logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings = 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
log_slow_rate_limit = 1000
log_slow_verbosity = query_plan
#log-queries-not-using-indexes
#log_slow_admin_statements
#
# 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
#report_host = master1
#auto_increment_increment = 2
#auto_increment_offset = 1
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog = 1
expire_logs_days = 7
max_binlog_size = 100M
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * 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!
default_storage_engine = InnoDB
innodb_buffer_pool_size = 6G
innodb_log_buffer_size = 512M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
#
#
# Helpful with recovery
#
innodb_force_recovery=3
innodb_purge_threads=0
# * 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
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
[mysqldump]
quick
quote-names
max_allowed_packet = 1024M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completion
[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.
#
!include /etc/mysql/mariadb.cnf
!includedir /etc/mysql/conf.d/
Any insight is appreciated.
Update:
I put a crash log on pastenbin
2
Answers
Hello this has already occurred to me …
In my case my server was low on memory …
And my database was full of posts.
That is … he had a lot of information on two types of decodes. (MyISAM & Innodb) in the same bank …
The solution I found was to export this database …
This is a backup …
Create a database from scratch by deciding on single encoding (Innodb) only.
And doing the import little by little checking all the tables and possible errors.
Another option I made was to remove what was old and put it in another database. To be part of another site.
Data integration would be through links between the two sites …
I hope it helped you!
Strong hug. Good luck!
Send news!
Where did you get these?? (I will comment on it.)
They are much too large. These could explain running out of memory and crashing.
Remove those from my.cnf. If some large values are needed for a single load, then set them only within that connection. And shrink
innodb_buffer_pool_size
by 2.5G to allow room for such a one-time task.How much RAM do you have?