InnoDB: space header page consists of zero bytes in tablespace

On a server, starting MySQL i get following error

[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.

mkdir ~/mysql-problem-2017-01-03
mv /var/lib/mysql/ib_* ~/mysql-problem-2017-01-03

Now i edit /etc/my.cnf as follows

[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.

[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

Need help with Linux Server or WordPress? We can help!

Leave a Reply

Your email address will not be published. Required fields are marked *