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

sql157 · 06.08.2017 08:55:48

Используется:
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)

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

sql157 · 06.08.2017 11:28:22

Файл 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)

paulus · 07.08.2017 18:18:54

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

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

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

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

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

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

#1 06.08.2017 08:55:48

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

#2 06.08.2017 11:28:22

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

#3 07.08.2017 18:18:54

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

Board footer