InnoDB: space header page consists of zero bytes in tablespace
On a server, starting MySQL i get following error
|
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 |
[root@3blogger log]# cat mysqld.log 170102 21:34:02 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 2017-01-02 21:34:02 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-01-02 21:34:02 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-72.2) starting as process 8048 ... 2017-01-02 21:34:02 8048 [Note] Plugin 'FEDERATED' is disabled. 2017-01-02 21:34:02 8048 [Note] InnoDB: Using atomics to ref count buffer pool pages 2017-01-02 21:34:02 8048 [Note] InnoDB: The InnoDB memory heap is disabled 2017-01-02 21:34:02 8048 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2017-01-02 21:34:02 8048 [Note] InnoDB: Memory barrier is not used 2017-01-02 21:34:02 8048 [Note] InnoDB: Compressed tables use zlib 1.2.3 2017-01-02 21:34:02 8048 [Note] InnoDB: Using Linux native AIO 2017-01-02 21:34:02 8048 [Note] InnoDB: Using CPU crc32 instructions 2017-01-02 21:34:02 8048 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2017-01-02 21:34:02 8048 [Note] InnoDB: Completed initialization of buffer pool 2017-01-02 21:34:02 8048 [Note] InnoDB: Highest supported file format is Barracuda. 2017-01-02 21:34:02 8048 [Note] InnoDB: Log scan progressed past the checkpoint lsn 644142666552 2017-01-02 21:34:02 8048 [Note] InnoDB: Database was not shutdown normally! 2017-01-02 21:34:02 8048 [Note] InnoDB: Starting crash recovery. 2017-01-02 21:34:02 8048 [Note] InnoDB: Reading tablespace information from the .ibd files... 2017-01-02 21:34:02 8048 [ERROR] InnoDB: space header page consists of zero bytes in tablespace ./3blogger/wp_wfNet404s.ibd (table 3blogger/wp_wfNet404s) 2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:1024 Pages to analyze:64 2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 1024, Possible space_id count:0 2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:2048 Pages to analyze:32 2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 2048, Possible space_id count:0 2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:4096 Pages to analyze:16 2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 4096, Possible space_id count:0 2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:8192 Pages to analyze:8 2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 8192, Possible space_id count:0 2017-01-02 21:34:02 8048 [Note] InnoDB: Page size:16384 Pages to analyze:4 2017-01-02 21:34:02 8048 [Note] InnoDB: Page size: 16384, Possible space_id count:0 2017-01-02 21:34:02 7fcef14617e0 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. InnoDB: Error: could not open single-table tablespace file ./3blogger/wp_wfNet404s.ibd InnoDB: We do not continue the crash recovery, because the table may become InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it. InnoDB: To fix the problem and start mysqld: InnoDB: 1) If there is a permission problem in the file and mysqld cannot InnoDB: open the file, you should modify the permissions. InnoDB: 2) If the table is not needed, or you can restore it from a backup, InnoDB: then you can remove the .ibd file, and InnoDB will do a normal InnoDB: crash recovery and ignore that table. InnoDB: 3) If the file system or the disk is broken, and you cannot remove InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf InnoDB: and force InnoDB to continue crash recovery here. 170102 21:34:02 mysqld_safe mysqld from pid file /var/lib/mysql/3blogger.3blogger.com.pid ended [root@3blogger log]# |
This was because the server used InnoDB database engine and my.cnf was not configured properly.
First i removed the log files.
|
1 2 |
mkdir ~/mysql-problem-2017-01-03 mv /var/lib/mysql/ib_* ~/mysql-problem-2017-01-03 |
Now i edit /etc/my.cnf as follows
|
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 |
[root@3blogger etc]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 port = 3306 socket=/var/lib/mysql/mysql.sock back_log = 50 max_connections = 100 max_connect_errors = 10 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M read_buffer_size = 2M read_rnd_buffer_size = 16M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = MYISAM thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 2 server-id = 1 key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@3blogger etc]# |
This MySQL config is based on /usr/share/doc/mysql-server-5.1.73/my-innodb-heavy-4G.cnf provided my MySQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[root@server12 ~]# rpm -q --filesbypkg mysql-server-5.1.73-7.el6.x86_64 | grep .cnf mysql-server /usr/share/doc/mysql-server-5.1.73/my-huge.cnf mysql-server /usr/share/doc/mysql-server-5.1.73/my-innodb-heavy-4G.cnf mysql-server /usr/share/doc/mysql-server-5.1.73/my-large.cnf mysql-server /usr/share/doc/mysql-server-5.1.73/my-medium.cnf mysql-server /usr/share/doc/mysql-server-5.1.73/my-small.cnf mysql-server /usr/share/mysql/my-huge.cnf mysql-server /usr/share/mysql/my-innodb-heavy-4G.cnf mysql-server /usr/share/mysql/my-large.cnf mysql-server /usr/share/mysql/my-medium.cnf mysql-server /usr/share/mysql/my-small.cnf [root@server12 ~]# |
You can also see this default MySQL InnoDB Heavy Config at
https://github.com/twitter/mysql/blob/master/support-files/my-innodb-heavy-4G.cnf.sh
Now i started MySQL, everything worked fine.
See mysql