Я перезапустил свое ведомое устройство, чтобы изменить параметры конфигурации, пропустить обратный поиск имени хоста при подключении и включить журнал медленных запросов.
Я отредактировал /etc/my.cnf, сделав только эти изменения, затем перезапустил mysqld с помощью /etc/init.d/mysql restart
Все выглядело хорошо, но когда я подключаюсь к msyqld удаленно или локально, хотя он подключается нормально, небольшая проблема заключается в том, что mysqld вылетает всякий раз, когда вы пытаетесь выполнить какой-либо оператор.
Клиент выглядит так:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.31-1ubuntu2-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: mydb
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (61)
ERROR:
Can't connect to the server
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (61)
ERROR:
Can't connect to the server
ERROR 2006 (HY000): MySQL server has gone away
Bus error
Журнал ошибок mysqld выглядит так:
101210 16:35:51 InnoDB: Error: (1500) Couldn't read the MAX(job_id) autoinc value from the index (PRIMARY).
101210 16:35:51 InnoDB: Assertion failure in thread 140245598570832 in file handler/ha_innodb.cc line 2595
InnoDB: Failing assertion: error == DB_SUCCESS
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
101210 16:35:51 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=3
max_threads=600
threads_connected=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1328077 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x18209220
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f8d791580d0 thread_stack 0x20000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x8b4f89]
/usr/sbin/mysqld(handle_segfault+0x383) [0x5f8f03]
/lib/libpthread.so.0 [0x7f902a76a080]
/lib/libc.so.6(gsignal+0x35) [0x7f90291f8fb5]
/lib/libc.so.6(abort+0x183) [0x7f90291fabc3]
/usr/sbin/mysqld(ha_innobase::open(char const*, int, unsigned int)+0x41b) [0x781f4b]
/usr/sbin/mysqld(handler::ha_open(st_table*, char const*, int, int)+0x3f) [0x6db00f]
/usr/sbin/mysqld(open_table_from_share(THD*, st_table_share*, char const*, unsigned int, unsigned int, unsigned int, st_table*, bool)+0x57a) [0x64760a]
/usr/sbin/mysqld [0x63f281]
/usr/sbin/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)+0x626) [0x641e16]
/usr/sbin/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x5db) [0x6429cb]
/usr/sbin/mysqld(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int)+0x1e) [0x642b0e]
/usr/sbin/mysqld(mysqld_list_fields(THD*, TABLE_LIST*, char const*)+0x22) [0x70b292]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x146d) [0x60dc1d]
/usr/sbin/mysqld(do_command(THD*)+0xe8) [0x60dda8]
/usr/sbin/mysqld(handle_one_connection+0x226) [0x601426]
/lib/libpthread.so.0 [0x7f902a7623ba]
/lib/libc.so.6(clone+0x6d) [0x7f90292abfcd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x18213c70 =
thd->thread_id=3
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
101210 16:35:51 mysqld_safe Number of processes running now: 0
101210 16:35:51 mysqld_safe mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
101210 16:35:54 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...
101210 16:35:56 InnoDB: Started; log sequence number 456 143528628
101210 16:35:56 [Warning] 'user' entry 'root@PSDB102' ignored in --skip-name-resolve mode.
101210 16:35:56 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
101210 16:35:56 [Note] Event Scheduler: Loaded 0 events
101210 16:35:56 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.31-1ubuntu2-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
101210 16:36:11 InnoDB: Error: (1500) Couldn't read the MAX(job_id) autoinc value from the index (PRIMARY).
101210 16:36:11 InnoDB: Assertion failure in thread 139955151501648 in file handler/ha_innodb.cc line 2595
InnoDB: Failing assertion: error == DB_SUCCESS
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
101210 16:36:11 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=1
max_threads=600
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1328077 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x18588720
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f49d916f0d0 thread_stack 0x20000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x8b4f89]
/usr/sbin/mysqld(handle_segfault+0x383) [0x5f8f03]
/lib/libpthread.so.0 [0x7f4c8a73f080]
/lib/libc.so.6(gsignal+0x35) [0x7f4c891cdfb5]
/lib/libc.so.6(abort+0x183) [0x7f4c891cfbc3]
/usr/sbin/mysqld(ha_innobase::open(char const*, int, unsigned int)+0x41b) [0x781f4b]
/usr/sbin/mysqld(handler::ha_open(st_table*, char const*, int, int)+0x3f) [0x6db00f]
/usr/sbin/mysqld(open_table_from_share(THD*, st_table_share*, char const*, unsigned int, unsigned int, unsigned int, st_table*, bool)+0x57a) [0x64760a]
/usr/sbin/mysqld [0x63f281]
/usr/sbin/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)+0x626) [0x641e16]
/usr/sbin/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x5db) [0x6429cb]
/usr/sbin/mysqld(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int)+0x1e) [0x642b0e]
/usr/sbin/mysqld(mysqld_list_fields(THD*, TABLE_LIST*, char const*)+0x22) [0x70b292]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x146d) [0x60dc1d]
/usr/sbin/mysqld(do_command(THD*)+0xe8) [0x60dda8]
/usr/sbin/mysqld(handle_one_connection+0x226) [0x601426]
/lib/libpthread.so.0 [0x7f4c8a7373ba]
/lib/libc.so.6(clone+0x6d) [0x7f4c89280fcd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x18599950 =
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
101210 16:36:11 mysqld_safe Number of processes running now: 0
101210 16:36:11 mysqld_safe mysqld restarted
Конфигурация
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
innodb_file_per_table
innodb_buffer_pool_size=10G
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
skip-slave-start
server-id=3
#
# * IMPORTANT
# If you make changes to these settings and your system uses apparmor, you may
# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /DB2/mysql
tmpdir = /tmp
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
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
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 = 600
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 32M
#
skip-federated
slow-query-log
skip-name-resolve
Обновить: Я выполнил инструкции согласно http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html и установите innodb_force_recovery = 4, и в журналах отображается другая ошибка, но поведение остается прежним:
101210 19:14:15 mysqld_safe mysqld restarted
101210 19:14:19 InnoDB: Started; log sequence number 456 143528628
InnoDB: !!! innodb_force_recovery is set to 4 !!!
101210 19:14:19 [Warning] 'user' entry 'root@PSDB102' ignored in --skip-name-resolve mode.
101210 19:14:19 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
101210 19:14:19 [Note] Event Scheduler: Loaded 0 events
101210 19:14:19 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.31-1ubuntu2-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
101210 19:14:32 InnoDB: error: space object of table mydb/__twitter_friend,
InnoDB: space id 1602 did not exist in memory. Retrying an open.
101210 19:14:32 InnoDB: error: space object of table mydb/access_request,
InnoDB: space id 1318 did not exist in memory. Retrying an open.
101210 19:14:32 InnoDB: error: space object of table mydb/activity,
InnoDB: space id 1595 did not exist in memory. Retrying an open.
101210 19:14:32 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=1
max_threads=600
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1328077 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x1753c070
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f7a0b5800d0 thread_stack 0x20000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x8b4f89]
/usr/sbin/mysqld(handle_segfault+0x383) [0x5f8f03]
/lib/libpthread.so.0 [0x7f7cbc350080]
/usr/sbin/mysqld(ha_innobase::innobase_get_index(unsigned int)+0x46) [0x77c516]
/usr/sbin/mysqld(ha_innobase::innobase_initialize_autoinc()+0x40) [0x77c640]
/usr/sbin/mysqld(ha_innobase::open(char const*, int, unsigned int)+0x3f3) [0x781f23]
/usr/sbin/mysqld(handler::ha_open(st_table*, char const*, int, int)+0x3f) [0x6db00f]
/usr/sbin/mysqld(open_table_from_share(THD*, st_table_share*, char const*, unsigned int, unsigned int, unsigned int, st_table*, bool)+0x57a) [0x64760a]
/usr/sbin/mysqld [0x63f281]
/usr/sbin/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)+0x626) [0x641e16]
/usr/sbin/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x5db) [0x6429cb]
/usr/sbin/mysqld(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int)+0x1e) [0x642b0e]
/usr/sbin/mysqld(mysqld_list_fields(THD*, TABLE_LIST*, char const*)+0x22) [0x70b292]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x146d) [0x60dc1d]
/usr/sbin/mysqld(do_command(THD*)+0xe8) [0x60dda8]
/usr/sbin/mysqld(handle_one_connection+0x226) [0x601426]
/lib/libpthread.so.0 [0x7f7cbc3483ba]
/lib/libc.so.6(clone+0x6d) [0x7f7cbae91fcd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x1754d690 =
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Посмотреть здесь: http://bugs.mysql.com/bug.php?id=44030
Филип С.
Я была такая же проблема. Я пробовал обновиться с 5.1.33 до 5.1.36, но mysqld все равно вылетал при обращении к базе данных.
Некоторые детали, большинство из которых, вероятно, не имеют отношения к делу, но, возможно, что-то всплывет: это новый ноутбук, и я только что начал новый проект. MD Turion X2 Dual-Core Mobile RM-74 с 4 ГБ ОЗУ Размер БД на диске составляет 1,67 ГБ. 1 ГБ ОЗУ используется как ramdisk. Свежая установка 5.1.33 на MS Vista 64 с помощью установщика проекта WAMP. Множество ALTER, так как это среда разработки. Добавление столбцов, индексов и т.д. Также переименование столбцов. Ошибка возникала между сеансами кодирования, а не во время сеанса кодирования. Вся схема db была создана с использованием MySQL Administrator, а все манипуляции с данными выполнялись с помощью php и mysqli. Все, что связано с MySQL, совершенно новое. Извините, я не могу предоставить файлы данных.
В любом случае, поскольку обновление не помогло мне получить доступ к данным, и с момента моего последнего резервного копирования было выполнено много работы, я хотел восстановить / получить как можно больше схемы БД, меня не интересовали данные . Я действительно получил большую часть данных. Хотя я уверен, что процесс можно оптимизировать, у меня сработало следующее: C: \ wamp \ bin \ mysql \ mysql5.1.36 \ bin> mysqlshow.exe -u root, затем для каждой таблицы C: \ wamp \ bin \ mysql \ mysql5.1.36 \ bin> mysqlshow.exe -u root до тех пор, пока я не получу ошибку mysqlshow.exe: Невозможно перечислить столбцы в db:, table:: Потеряно соединение с сервером MySQL во время запроса, затем я перезапустил mysqld, запустил консоль MySQL и \ u drop table для таблицы, которая вызвала сбой. После этого все вернулось к норме, за исключением потери одной маленькой таблицы, которую я мог бы легко воссоздать.
Эта проблема может возникнуть, если у вас низкое предпочтительное значение для переменных mysqladmin "wait_timeout" | grep wait_timeout