SQLinfo.ru - Все о MySQL Highload++ 2017

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 06.08.2017 08:55:48

sql157
Завсегдатай
Зарегистрирован: 17.03.2016
Сообщений: 92

Не запускается MySql.

Используется:
Windows - 7x64;
MySQl - MySQL-5.7-x64;
Пользователь - администратор;
Антивирусы - Аваст (отключён).

Расположения:
Сервер - e:\srw\webopsrvr;
MySQl - e:\srw\webopsrvr\modules\database\MySQL-5.7-x64\;
Таблицы MySQl - "\\\\Server\\e\\srv\\db\\webopsrvr\\userdata\\MySQL-5.7-x64" (локальная сеть);

Логи


Запрос
e:\srw\webopsrvr\modules\database\MySQL-5.7-x64\bin\mysqld.exe, Version: 5.7.16-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument


2017-08-06T04:06:31.932785Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-08-06T04:06:31.933786Z 0 [Note] e:\srw\webopsrvr\modules\database\MySQL-5.7-x64\bin\mysqld.exe (mysqld 5.7.16-log) starting as process 4300 ...
2017-08-06T04:06:31.978788Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2017-08-06T04:06:31.980788Z 0 [Note] InnoDB: Uses event mutexes
2017-08-06T04:06:31.980788Z 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier
2017-08-06T04:06:31.980788Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-08-06T04:06:31.981788Z 0 [Note] InnoDB: Number of pools: 1
2017-08-06T04:06:31.981788Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2017-08-06T04:06:31.984788Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-08-06T04:06:32.001789Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-08-06T04:06:32.397812Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-08-06T04:06:32.408813Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 91192078032
2017-08-06T04:06:32.408813Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 91192078041
2017-08-06T04:06:32.416813Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 91192078041
2017-08-06T04:06:32.417813Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-08-06T04:06:32.417813Z 0 [Note] InnoDB: Starting crash recovery.

2017-08-06T04:06:44.464502Z 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2017-08-06T04:06:44.465502Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2017-08-06T04:06:44.465502Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2017-08-06T04:06:44.465502Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\sys\sys_config.ibd' OS error: 103
2017-08-06T04:06:44.465502Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `sys/sys_config`. Please refer to <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html">http://dev.mysql.com/doc/refman/5.7/en/ &hellip; adict.html</a> for how to resolve the issue.

2017-08-06T04:06:44.465502Z 0 [Warning] InnoDB: Ignoring tablespace `sys/sys_config` because it could not be opened.
2017-08-06T04:06:46.703630Z 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2017-08-06T04:06:46.703630Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2017-08-06T04:06:46.703630Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2017-08-06T04:06:46.703630Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\tst_rb_mn_02_1\002_tst_01_vr_05_tbl_frm2_ssl.ibd' OS error: 103
2017-08-06T04:06:46.703630Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `tst_rb_mn_02_1/002_tst_01_vr_05_tbl_frm2_ssl`. Please refer to <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html">http://dev.mysql.com/doc/refman/5.7/en/ &hellip; adict.html</a> for how to resolve the issue.

2017-08-06T04:06:46.704630Z 0 [Warning] InnoDB: Ignoring tablespace `tst_rb_mn_02_1/002_tst_01_vr_05_tbl_frm2_ssl` because it could not be opened.
2017-08-06T04:06:46.706631Z 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2017-08-06T04:06:46.706631Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2017-08-06T04:06:46.706631Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2017-08-06T04:06:46.706631Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\tst_rb_mn_02_1\002_tst_02_vr_08_tbl_frm2_ssl.ibd' OS error: 103
2017-08-06T04:06:46.706631Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `tst_rb_mn_02_1/002_tst_02_vr_08_tbl_frm2_ssl`. Please refer to <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html">http://dev.mysql.com/doc/refman/5.7/en/ &hellip; adict.html</a> for how to resolve the issue.

2017-08-06T04:06:46.706631Z 0 [Warning] InnoDB: Ignoring tablespace `tst_rb_mn_02_1/002_tst_02_vr_08_tbl_frm2_ssl` because it could not be opened.
2017-08-06T04:06:46.709631Z 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2017-08-06T04:06:46.709631Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2017-08-06T04:06:46.709631Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2017-08-06T04:06:46.709631Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\tst_rb_mn_02_1\002_tst_03_vr_11_tbl_frm2_ssl.ibd' OS error: 103
2017-08-06T04:06:46.710631Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `tst_rb_mn_02_1/002_tst_03_vr_11_tbl_frm2_ssl`. Please refer to <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html">http://dev.mysql.com/doc/refman/5.7/en/ &hellip; adict.html</a> for how to resolve the issue.

2017-08-06T04:06:46.710631Z 0 [Warning] InnoDB: Ignoring tablespace `tst_rb_mn_02_1/002_tst_03_vr_11_tbl_frm2_ssl` because it could not be opened.
2017-08-06T04:06:46.719631Z 0 [ERROR] InnoDB: In file '.\tst_rb_mn_03\002_tst_01_vr_05_tbl_frm2_ssl.ibd', tablespace id and flags are 429 and 33, but in the InnoDB data dictionary they are 480 and 33. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html">http://dev.mysql.com/doc/refman/5.7/en/ &hellip; adict.html</a> for how to resolve the issue.
2017-08-06T04:06:46.720631Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `tst_rb_mn_03/002_tst_01_vr_05_tbl_frm2_ssl`. Please refer to <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html">http://dev.mysql.com/doc/refman/5.7/en/ &hellip; adict.html</a> for how to resolve the issue.

2017-08-06T04:06:46.720631Z 0 [Warning] InnoDB: Ignoring tablespace `tst_rb_mn_03/002_tst_01_vr_05_tbl_frm2_ssl` because it could not be opened.
2017-08-06T04:06:46.730632Z 0 [ERROR] InnoDB: In file '.\tst_rb_mn_03\002_tst_02_vr_08_tbl_frm2_ssl.ibd', tablespace id and flags are 430 and 33, but in the InnoDB data dictionary they are 481 and 33. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html">http://dev.mysql.com/doc/refman/5.7/en/ &hellip; adict.html</a> for how to resolve the issue.
2017-08-06T04:06:46.730632Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `tst_rb_mn_03/002_tst_02_vr_08_tbl_frm2_ssl`. Please refer to <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html">http://dev.mysql.com/doc/refman/5.7/en/ &hellip; adict.html</a> for how to resolve the issue.

2017-08-06T04:06:46.730632Z 0 [Warning] InnoDB: Ignoring tablespace `tst_rb_mn_03/002_tst_02_vr_08_tbl_frm2_ssl` because it could not be opened.
2017-08-06T04:06:46.740632Z 0 [ERROR] InnoDB: In file '.\tst_rb_mn_03\002_tst_03_vr_11_tbl_frm2_ssl.ibd', tablespace id and flags are 431 and 33, but in the InnoDB data dictionary they are 482 and 33. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html">http://dev.mysql.com/doc/refman/5.7/en/ &hellip; adict.html</a> for how to resolve the issue.
2017-08-06T04:06:46.740632Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `tst_rb_mn_03/002_tst_03_vr_11_tbl_frm2_ssl`. Please refer to <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html">http://dev.mysql.com/doc/refman/5.7/en/ &hellip; adict.html</a> for how to resolve the issue.

2017-08-06T04:06:46.741633Z 0 [Warning] InnoDB: Ignoring tablespace `tst_rb_mn_03/002_tst_03_vr_11_tbl_frm2_ssl` because it could not be opened.


2017-08-06T04:07:08.867898Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-08-06T04:07:08.867898Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-08-06T04:07:08.867898Z 0 [Note] InnoDB: Setting file 'e:\srw\webopsrvr\userdata\MySQL-5.7-x64\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-08-06T04:07:09.207918Z 0 [Note] InnoDB: File 'e:\srw\webopsrvr\userdata\MySQL-5.7-x64\ibtmp1' size is now 12 MB.
2017-08-06T04:07:09.229919Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-08-06T04:07:09.229919Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-08-06T04:07:09.231919Z 0 [Note] InnoDB: Waiting for purge to start
2017-08-06T04:07:09.283922Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 37274ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2017-08-06T04:07:09.283922Z 0 [Note] InnoDB: 5.7.16 started; log sequence number 91192078041
2017-08-06T04:07:09.284922Z 0 [Note] InnoDB: Loading buffer pool(s) from e:\srw\webopsrvr\userdata\MySQL-5.7-x64\ib_buffer_pool
2017-08-06T04:07:09.284922Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-08-06T04:07:09.509935Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2017-08-06T04:07:09.511935Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2017-08-06T04:07:09.511935Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2017-08-06T04:07:09.923958Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170806  7:07:09
2017-08-06T04:07:11.037022Z 0 [Note] Event Scheduler: Loaded 0 events
2017-08-06T04:07:11.038022Z 0 [Note] e:\srw\webopsrvr\modules\database\MySQL-5.7-x64\bin\mysqld.exe: ready for connections.
Version: '5.7.16-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

 



Вопрос
Как исправить ошибку?

Неактивен

 

#2 06.08.2017 11:28:22

sql157
Завсегдатай
Зарегистрирован: 17.03.2016
Сообщений: 92

Re: Не запускается MySql.

Файл my.ini e:\srw\webopsrvr\modules\database\MySQL-5.7-x64\my.ini


 #
# ВНИМАНИЕ! Это временный файл, его редактирование бессмысленно!
#

[client]
port        = 3307
character-sets-dir    = "e:\\srw\\webopsrvr\\modules\\database\\MySQL-5.7-x64\\share\\charsets"

[mysql]
character-sets-dir    = "e:\\srw\\webopsrvr\\modules\\database\\MySQL-5.7-x64\\share\\charsets"
no-auto-rehash
no-beep

[mysqld]
#skip-character-set-client-handshake
#skip-name-resolve
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
general_log = 1
general_log_file = "e:\\srw\\webopsrvr\\userdata\\logs\\MySQL-5.7-x64_queries.log"
back_log = 100
basedir = "e:\\srw\\webopsrvr\\modules\\database\\MySQL-5.7-x64"

#работает
#bind-address = 127.0.0.1
#bind-address = 192.168.1.1
#bind-address = 0.0.0.0

#работает
bind-address = 127.0.0.1

#binlog_row_event_max_size=8K
bulk_insert_buffer_size = 2M
character-set-server=utf8
character-sets-dir    = "e:\\srw\\webopsrvr\\modules\\database\\MySQL-5.7-x64\\share\\charsets"
collation-server=utf8_general_ci
connect_timeout = 5
#datadir = "e:\\srw\\webopsrvr\\userdata\\MySQL-5.7-x64"
datadir = "\\\\Server\\e\\srv\\db\\webopsrvr\\userdata\\MySQL-5.7-x64"
default-storage-engine=InnoDB
expire_logs_days = 2
explicit_defaults_for_timestamp = TRUE
flush_time=0
ft_min_word_len=4
init-connect="SET NAMES utf8"
interactive_timeout=30
join_buffer_size = 1M
key_buffer_size = 25M
local-infile=0
#secure-file-priv = "e:\\srw\\webopsrvr\\userdata\\temp"
ssl-cert="e:\\srw\\webopsrvr\\userdata\\config\\cert_files\\server.crt"
ssl-key="e:\\srw\\webopsrvr\\userdata\\config\\cert_files\\server.key"
log-error = "e:\\srw\\webopsrvr\\userdata\\logs\\MySQL-5.7-x64_error.log"
lower_case_table_names = 2
max_allowed_packet = 32M
max_connect_errors = 10
max_connections = 30
max_heap_table_size = 25M
max_tmp_tables = 25
myisam-recover-options=backup,force
myisam_max_sort_file_size = 256M
myisam_repair_threads = 1
open_files_limit=4110
pid_file = "e:\\srw\\webopsrvr\\userdata\\temp\\mysql.pid"
port = 3307
query_cache_limit = 256K
query_cache_size=25M
query_cache_type=1
read_buffer_size=2M
read_rnd_buffer_size=4M
server-id = 1
skip-external-locking
skip-innodb_adaptive_hash_index
sort_buffer_size = 1M
table_definition_cache = 400
table_open_cache = 400
thread_cache_size = 4
tmp_table_size = 25M
tmpdir = "e:\\srw\\webopsrvr\\userdata\\temp"
transaction-isolation = READ-COMMITTED
wait_timeout=60

#innodb_fast_shutdown
#innodb_file_per_table = 1
#innodb_force_recovery=1
#skip-innodb_doublewrite
innodb_buffer_pool_instances=1
innodb_buffer_pool_size = 128M
innodb_checksum_algorithm=0
innodb_concurrency_tickets=5000
innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_home_dir = "e:\\srw\\webopsrvr\\userdata\\MySQL-5.7-x64"
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 30
innodb_log_buffer_size = 1M
innodb_log_file_size = 64M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_old_blocks_time=1000
innodb_read_io_threads = 8
innodb_stats_on_metadata=0
innodb_thread_concurrency=4
innodb_write_io_threads = 8

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 50M
sort_buffer_size = 50M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 2048
 



Если я правильно понял то файлы указанные в логах необходимо искать в папке таблиц.
\Server\e\srv\db\webopsrvr\userdata\MySQL-5.7-x64\

Наличие файлов указанных в логах:
отсутствует папка sys - .\sys\sys_config.ibd
отсутствует папка tst_rb_mn_02_1-.\tst_rb_mn_02_1\002_tst_01_vr_05_tbl_frm2_ssl.ibd
отсутствует папка tst_rb_mn_02_1 - .\tst_rb_mn_02_1\002_tst_02_vr_08_tbl_frm2_ssl.ibd
отсутствует папка tst_rb_mn_02_1 - .\tst_rb_mn_02_1\002_tst_03_vr_11_tbl_frm2_ssl.ibd

имеется - .\tst_rb_mn_03\002_tst_01_vr_05_tbl_frm2_ssl.ibd
имеется - .\tst_rb_mn_03\002_tst_02_vr_08_tbl_frm2_ssl.ibd
имеется - .\tst_rb_mn_03\002_tst_03_vr_11_tbl_frm2_ssl.ibd

Краткое описание
Пытался установить Редмин по инструкции
http://nlb-creations.com/2013/06/26/ins … windows-7/

Т.е. 
Установил Rails; 
Создал новый проект Ruby on Rails; 
Запустил Rails; 

Дошёл до п.11
`11) In the cmd window, make sure you are still in your Redmine project’s directory, and run the following command:
bundle install
`
появились ошибки. Забросил установку.   
Дополнительно: перезагрузил комп кнопкой 'reset' на корпусе.   

Потом удалил Ruby и Rails.

После данных манипуляций **MySQl** начал запускаться через раз.
А сегодня вообще перестал.

Отредактированно sql157 (06.08.2017 11:30:40)

Неактивен

 

#3 07.08.2017 18:18:54

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6635

Re: Не запускается MySql.

Судя по логу, MySQL не удается открыть соответствующие файлы. Проще всего, наверное, удалить все, создать новую структуру и восстановить из бэкапа.

Задам сразу вопросов:

1. Почему вы храните данные на сетевом диске? Это чревато проблемами с невозможностью записать какие-то данные, с одновременным доступом к данным, я уж молчу про то, что будут проблемы со скоростью.

2. Оно раньше работало в таком режиме? Очень странная конфигурация, я допускаю, что оно могло так работать, но хочется быть уверенным, что это не первая инсталляция, а именно возникшая в процессе проблема.

3. Параллельно на сетевом диске не мог кто-то поковыряться?

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson