InnoDB: Cannot read first page in datafile: ./database_namexxx/table_nameyyy.ibd, Space ID:18446744073709551615, Flags: 0. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue

MySQL & MariaDB Percona XtraBackup
1

Hello,

Recently, I have issues with Xtrabackup (xtrabackup version 2.4.22 based on MySQL server 5.7.32 Linux (x86_64) (revision id: c99a781) with MySQL 5.7 (mysql Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using EditLine wrapper).

When taking full backup, I got this error.

Full log here:

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql/data --tmpdir=/var/lib/mysql/tmp --server-id=101520948 --open_files_limit=100000 --innodb_file_per_table=1 --innodb_data_file_path=ibdata1:10M:autoextend --innodb_buffer_pool_size=2048M --innodb_log_file_size=256M --log_bin=mysql-bin.log --tmpdir=/var/lib/mysql/tmp

xtrabackup: recognized client arguments: --port=3306 --socket=/var/run/mysqld/mysqld.sock --user=admin --password=* --host=127.0.0.1 --backup=1 --stream=xbstream --user=admin --password=*

230709 03:00:05 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock’ as ‘admin’ (using password: YES).

230709 03:00:05 version_check Connected to MySQL server

230709 03:00:05 version_check Executing a version check against the server


230709 03:00:05 version_check Done.

230709 03:00:05 Connecting to MySQL server host: 127.0.0.1, user: admin, password: set, port: 3306, socket: /var/run/mysqld/mysqld.sock

Using server version 5.7.34-0ubuntu0.18.04.1-log

xtrabackup version 2.4.22 based on MySQL server 5.7.32 Linux (x86_64) (revision id: c99a781)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /var/lib/mysql/data

xtrabackup: open files limit requested 100000, set to 100000

xtrabackup: using the following InnoDB configuration:

xtrabackup: innodb_data_home_dir = .

xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup: innodb_log_group_home_dir = ./

xtrabackup: innodb_log_files_in_group = 2

xtrabackup: innodb_log_file_size = 268435456

InnoDB: Number of pools: 1

230709 03:01:40 >> log scanned up to (6240622024)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 10335 for <database_namexxx>/<table_namexxx>, old maximum was 0

230709 03:01:41 >> log scanned up to (6240622024)

230709 03:01:42 >> log scanned up to (6240622024)

230709 03:01:43 >> log scanned up to (6240622024)

230709 03:01:44 >> log scanned up to (6240622024)

230709 03:01:45 >> log scanned up to (6240622024)

230709 03:01:46 >> log scanned up to (6240622024)

230709 03:01:47 >> log scanned up to (6240622024)

230709 03:01:48 >> log scanned up to (6240622024)

230709 03:01:49 >> log scanned up to (6240622024)

230709 03:01:50 >> log scanned up to (6240622024)

230709 03:01:51 >> log scanned up to (6240622024)

230709 03:01:52 >> log scanned up to (6240622024)

230709 03:01:53 >> log scanned up to (6240622024)

230709 03:01:54 >> log scanned up to (6240622024)

230709 03:01:55 >> log scanned up to (6240622024)

230709 03:01:56 >> log scanned up to (6240622024)

230709 03:01:57 >> log scanned up to (6240622024)

230709 03:01:58 >> log scanned up to (6240622024)

230709 03:01:59 >> log scanned up to (6240622024)

230709 03:02:00 >> log scanned up to (6240622024)

230709 03:02:01 >> log scanned up to (6240622024)

230709 03:02:02 >> log scanned up to (6240622024)

230709 03:02:03 >> log scanned up to (6240622024)

230709 03:02:04 >> log scanned up to (6240622024)

230709 03:02:05 >> log scanned up to (6240622024)

230709 03:02:06 >> log scanned up to (6240622024)

230709 03:02:07 >> log scanned up to (6240622024)

230709 03:02:08 >> log scanned up to (6240622024)

230709 03:02:09 >> log scanned up to (6240622024)

230709 03:02:10 >> log scanned up to (6240622024)

230709 03:02:11 >> log scanned up to (6240622024)

230709 03:02:12 >> log scanned up to (6240622024)

230709 03:02:13 >> log scanned up to (6240622024)

230709 03:02:14 >> log scanned up to (6240622024)

230709 03:02:15 >> log scanned up to (6240622024)

230709 03:02:16 >> log scanned up to (6240622024)

230709 03:02:17 >> log scanned up to (6240622024)

230709 03:02:18 >> log scanned up to (6240622024)

230709 03:02:19 >> log scanned up to (6240622024)

230709 03:02:20 >> log scanned up to (6240622024)

230709 03:02:21 >> log scanned up to (6240622024)

230709 03:02:22 >> log scanned up to (6240622024)

230709 03:02:23 >> log scanned up to (6240622024)

230709 03:02:24 >> log scanned up to (6240622024)

230709 03:02:25 >> log scanned up to (6240622024)

230709 03:02:26 >> log scanned up to (6240622024)

230709 03:02:27 >> log scanned up to (6240622024)

230709 03:02:28 >> log scanned up to (6240622024)

230709 03:02:29 >> log scanned up to (6240622024)

230709 03:02:30 >> log scanned up to (6240622024)

230709 03:02:31 >> log scanned up to (6240622024)

230709 03:02:32 >> log scanned up to (6240622024)

230709 03:02:33 >> log scanned up to (6240622024)

230709 03:02:34 >> log scanned up to (6240622024)

230709 03:02:35 >> log scanned up to (6240622024)

230709 03:02:36 >> log scanned up to (6240622024)

230709 03:02:37 >> log scanned up to (6240622024)

230709 03:02:38 >> log scanned up to (6240622024)

230709 03:02:39 >> log scanned up to (6240622024)

230709 03:02:40 >> log scanned up to (6240622024)

230709 03:02:41 >> log scanned up to (6240622024)

230709 03:02:42 >> log scanned up to (6240622024)

230709 03:02:43 >> log scanned up to (6240622024)

230709 03:02:44 >> log scanned up to (6240622024)

230709 03:02:45 >> log scanned up to (6240622024)

230709 03:02:46 >> log scanned up to (6240622024)

230709 03:02:47 >> log scanned up to (6240622024)

230709 03:02:48 >> log scanned up to (6240622024)

230709 03:02:49 >> log scanned up to (6240622024)

230709 03:02:50 >> log scanned up to (6240622024)

230709 03:02:51 >> log scanned up to (6240622024)

230709 03:02:52 >> log scanned up to (6240622024)

230709 03:02:53 >> log scanned up to (6240622024)

230709 03:02:54 >> log scanned up to (6240622024)

230709 03:02:55 >> log scanned up to (6240622024)

230709 03:02:56 >> log scanned up to (6240622024)

230709 03:02:57 >> log scanned up to (6240622024)

230709 03:02:58 >> log scanned up to (6240622024)

230709 03:02:59 >> log scanned up to (6240622024)

230709 03:03:00 >> log scanned up to (6240622024)

230709 03:03:01 >> log scanned up to (6240622024)

230709 03:03:02 >> log scanned up to (6240622024)

230709 03:03:03 >> log scanned up to (6240622024)

230709 03:03:04 >> log scanned up to (6240622024)

230709 03:03:05 >> log scanned up to (6240622024)

230709 03:03:06 >> log scanned up to (6240622024)

230709 03:03:07 >> log scanned up to (6240622024)

230709 03:03:08 >> log scanned up to (6240622024)

230709 03:03:09 >> log scanned up to (6240622024)

230709 03:03:10 >> log scanned up to (6240622024)

230709 03:03:11 >> log scanned up to (6240622024)

230709 03:03:12 >> log scanned up to (6240622024)

230709 03:03:13 >> log scanned up to (6240622024)

230709 03:03:14 >> log scanned up to (6240622024)

230709 03:03:15 >> log scanned up to (6240622024)

230709 03:03:16 >> log scanned up to (6240622024)

230709 03:03:17 >> log scanned up to (6240622024)

230709 03:03:18 >> log scanned up to (6240622024)

230709 03:03:19 >> log scanned up to (6240622024)

230709 03:03:20 >> log scanned up to (6240622024)

230709 03:03:21 >> log scanned up to (6240622024)

230709 03:03:22 >> log scanned up to (6240622024)

230709 03:03:23 >> log scanned up to (6240622024)

230709 03:03:24 >> log scanned up to (6240622024)

230709 03:03:25 >> log scanned up to (6240622024)

230709 03:03:26 >> log scanned up to (6240622024)

230709 03:03:27 >> log scanned up to (6240622024)

230709 03:03:28 >> log scanned up to (6240622024)

230709 03:03:29 >> log scanned up to (6240622024)

InnoDB: Cannot read first page of ‘./<database_nameyyy>/<table_namezzz>.ibd’ I/O error

InnoDB: Cannot read first page in datafile: .//<database_nameyyy>/<table_namezzz>.ibd, Space ID:18446744073709551615, Flags: 0. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

Any ideas ? Should I try to increase open file limits ?

2

Hi @Cong_Tran and welcome to Percona Community,

Can you confirm if you can query the table in question? Do you see any errors regarding that table in error log?

mysql> pager md5sum; select * from database_nameyyy.table_namezzz;

Thanks,
K

3

Thanks you @kedarpercona for your reply.

I can execute your suggested query and see no errors in error log.

mysql> pager md5sum;
PAGER set to ‘md5sum’
mysql> select * from database_nameyyy.table_namezzz;
Empty set (0.00 sec)

4

hmmm! table seems “not corrupt”.
What command are you running? Are you executing the xtrabackup with OS user with correct read permission?

About “Should I try to increase open file limits ?”. No, cause Xtrabackup will let you know that clearly in the error log. Also I see xtrabackup: open files limit requested 100000, set to 100000, do we have that many files?! It appears that backup fails on the very first table read.

Thanks,
K

5

My command is:

sudo xtrabackup --backup --stream=xbstream --user=admin --password=* 2>/tmp/xtrabackup.log | gzip -3 | openssl enc -aes-256-cbc -salt -pass pass:superpassword -pbkdf2 -iter 1000000 > /var/lib/mysql/backup.xbstream.gz.enc

OS user who run this commands belongs to sudo group and have enough permission.
This mysql has around 63366 tables so we increase its limit to 100000 open files, but not sure is it enough or we should tune other options.

mysql> select count(*) FROM INFORMATION_SCHEMA.TABLES;
+----------+
| count(*) |
+----------+
|    63366 |
+----------+
1 row in set (0.20 sec)

Here is our my.cnf:

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
user = mysql
port = 3306

# Fixed Path.
basedir = /usr/bin/mysql
datadir = /var/lib/mysql/data
tmpdir = /var/lib/mysql/tmp
plugin_dir = /usr/lib/mysql/plugin
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
lc-messages-dir=/usr/share/mysql

# Turn on performance_schema
performance_schema = ON

local-infile = 0
myisam-recover-options = BACKUP,FORCE

# Turn off query cache
query_cache_type = 0
query_cache_limit = 0
query_cache_size = 0

# Service ID use for create replica
server_id = 101520948

open_files_limit = 100000
max_connections= 151
wait_timeout = 120
skip-external-locking = 1

# Only support innodb.
default_storage_engine = innodb
# 1 file per table.
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:10M:autoextend
# 25% Memory.
innodb_buffer_pool_size = 2048M
# 12.5% of innodb_buffer_pool_size
innodb_log_file_size=256M
# expire binary log older than 3 days
expire_logs_days=3

# Buffer per threads. Keep default of mysql. ~5 MB/thread.
join_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
sort_buffer_size = 256K
max_allowed_packet = 4MB
thread_stack = 192K

# Others default
key_buffer_size = 8M
tmp_table_size = 16M
max_heap_table_size = 16M
table_open_cache = 2000
skip-name-resolve
skip-host-cache
performance_schema = on

[mysqldump]
quick = 1
quote-names = 1
max_allowed_packet = 16M

[isamchk]
key_buffer = 16M
6

I suggest force rebuilding that table.

mysql> ALTER TABLE database_nameyyy.table_namezzz ENGINE=InnoDB;

Then run PXB again and see if the issue persists.

1 Like