innodb_data_file_path is not checked for duplicate file names | Percona Server 5.6.22-72.0 failed to start
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
| 5.5 |
Triaged
|
High
|
Unassigned | ||
| 5.6 |
Triaged
|
High
|
Unassigned | ||
| 5.7 |
Triaged
|
High
|
Unassigned |
Bug Description
Server shut down normally without error. Upon restart, I encountered this error--
2015-03-04 05:49:13 2b054eea2cf0 InnoDB: Error: tablespace size stored in header is 147013632 pages, but
2015-03-04 05:49:13 2b054eea2cf0 InnoDB: the sum of data file sizes is only 147003840 pages
I did not modify 'innodb_
Original:
innodb_
Made a typo and got the server online with this statement:
innodb_
Works consistently, although fallout is unclear. Tables all seem to be in tact without corruption. I'm envisioning problems will crop up eventually.
I cannot modify that line really in any way, though. For example, adding additional files results in the error returning.
chrisjob1021 (chris-obrien-1021) wrote : | #2 |
2015-03-04 05:49:06 0 [Warning] options --log-slow-
2015-03-04 05:49:06 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_
2015-03-04 05:49:06 60863 [Warning] Buffered warning: Could not increase number of max_open_files to more than 1009999 (request: 1068587)
2015-03-04 05:49:06 60863 [Warning] Buffered warning: Changed limits: table_cache: 494994 (requested 524288)
2015-03-04 05:49:06 60863 [Note] Plugin 'FEDERATED' is disabled.
2015-03-04 05:49:06 60863 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-03-04 05:49:06 60863 [Note] InnoDB: The InnoDB memory heap is disabled
2015-03-04 05:49:06 60863 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-03-04 05:49:06 60863 [Note] InnoDB: Memory barrier is not used
2015-03-04 05:49:06 60863 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-03-04 05:49:06 60863 [Note] InnoDB: Using Linux native AIO
2015-03-04 05:49:06 60863 [Note] InnoDB: Using CPU crc32 instructions
2015-03-04 05:49:06 60863 [ERROR] InnoDB: Linux Native AIO not supported. You can either move tmpdir to a file system that supports native AIO or you can set innodb_
2015-03-04 05:49:06 60863 [ERROR] InnoDB: Linux Native AIO check on tmpdir returned error[22]
2015-03-04 05:49:06 60863 [Warning] InnoDB: Linux Native AIO disabled.
2015-03-04 05:49:06 60863 [Note] InnoDB: Initializing buffer pool, size = 1.0G
2015-03-04 05:49:06 60863 [Note] InnoDB: Completed initialization of buffer pool
2015-03-04 05:49:06 60863 [Note] InnoDB: Highest supported file format is Barracuda.
2015-03-04 05:49:06 60863 [Note] InnoDB: The log sequence numbers 85296094955072 and 85296094955072 in ibdata files do not match the log sequence number 85296110999966 in the ib_logfiles!
2015-03-04 05:49:06 60863 [Note] InnoDB: Database was not shutdown normally!
2015-03-04 05:49:06 60863 [Note] InnoDB: Starting crash recovery.
2015-03-04 05:49:06 60863 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-03-04 05:49:06 60863 [Note] InnoDB: Restoring possible half-written data pages
2015-03-04 05:49:06 60863 [Note] InnoDB: from the doublewrite buffer...
2015-03-04 05:49:13 60863 [Note] InnoDB: 128 rollback segment(s) are active.
2015-03-04 05:49:13 60863 [Note] InnoDB: Waiting for purge to start
2015-03-04 05:49:13 2b054eea2cf0 InnoDB: Error: tablespace size stored in header is 147013632 pages, but
2015-03-04 05:49:13 2b054eea2cf0 InnoDB: the sum of data file sizes is only 147003840 pages
2015-03-04 05:49:13 2b054eea2cf0 InnoDB: Cannot start InnoDB. The tail of the system tablespace is
2015-03-04 05:49:13 2b054eea2cf0 InnoDB: missing. Have you edited innodb_
2015-03-04 05:49:13 2b054eea2cf0 InnoDB: inappropriate way, removing ibdata files from there?
2015-03-04 05:49:13 2b054eea2cf0 InnoDB: You can set innodb_
2015-03-04 05:49:13 2b054eea2cf0 InnoDB: a startup if you are t...
Nilnandan Joshi (nilnandan-joshi) wrote : | #3 |
Hi,
First error in log,
2015-03-04 05:49:06 60863 [ERROR] InnoDB: Linux Native AIO not supported. You can either move tmpdir to a file system that supports native AIO or you can set innodb_
2015-03-04 05:49:06 60863 [ERROR] InnoDB: Linux Native AIO check on tmpdir returned error[22]
you should not put tmpdir into memfs to avoid this error.
"Made a typo and got the server online with this statement:
innodb_
Here, I can see ibdata4 twice, was it intentionally set? you mean with this, you were able to start server?
Can you please provide my.cnf and what is the current page size you are using for InnoDB? (by default, it is 16K)
chrisjob1021 (chris-obrien-1021) wrote : | #4 |
Hi,
"Here, I can see ibdata4 twice, was it intentionally set? you mean with this, you were able to start server?"
It was unintentionally set, but only way I could get it online.
We are using default page size for InnoDB.
my.cnf--
[mysqlclient]
socket=
[mysql]
socket=
secure-auth=0
[mysqladmin]
socket=
[mysqld]
datadir=
socket=
tmpdir=
slave-load-
back_log=4096
port=3306
skip-external-
max_allowed_packet = 128M
max_heap_table_size = 67108864
max_user_
max_connections = 20000
group_concat_
table_open_cache = 524288
table_definitio
tmp_table_size = 67108864
sort_buffer_size = 262144
read_buffer_size = 1M
read_rnd_
join_buffer_size = 16777216
query_cache_type = 0
query_cache_size = 0
max_connect_errors = 1000000
wait_timeout = 1
connect_timeout = 2
interactive_timeout = 1
skip-external-
log-error
slow_query_log = 0
min_examined_
log_queries_
long_query_time = 2
log_slow_
skip-name-resolve
thread_cache_size = 16384
user=root
log-warnings
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
binlog-format=ROW
transaction-
innodb_
innodb_
innodb_
innodb_
slave_exec_mode = IDEMPOTENT
slave-skip-
slave_transacti
log-slave-updates
expire_logs_days=26
log-bin=
max_relay_log_size = 1G
max_binlog_size = 1G
relay-log=
binlog_cache_size = 33554432
binlog_
server-id = 1
auto_increment_
max_sp_
innodb_
table_open_
metadata_
innodb_
innodb_
innodb_
innodb_
innodb_
binlog_
relay_log_
slave_sql_
master_
relay_log_
master_
key_buffer_size=0
innodb_
innodb_
innodb_
log_warnings=1
innodb_
innodb_
Nilnandan Joshi (nilnandan-joshi) wrote : | #5 |
Hi,
Thanks for the details. I can see that all datafiles are stored in different partitions. Are they using same file system?
innodb_
/mysql3/
/mysql4/
/mysql5/
/mysql5/
Also the size of ibdata4 file is,
-rw-rw---- 1 mysql mysql 388477485056 Mar 5 17:58 ibdata4
388477485056 = 379372544K = 370481M
Can you try to set below path and check if it works?
innodb_
OR
innodb_
If you still see the issue then please provide /var/log/messages related to that disk. Waiting for your feedback.
chrisjob1021 (chris-obrien-1021) wrote : | #6 |
Each partition is on distinct RAID array and all use XFS file system.
I have tried this line and it did not result in the server coming online. It spit out the error I described initially.
"innodb_
I have not tried the second line yet. Please advise if you think it would make a difference.
We do not see any issues related to the disk in /var/log/messages or associated logs. An xfs_check did not reveal any errors.
There are two problem here:
1. How you ended up with tablespace size stored in header being exactly 153M bigger than total size of all ibdata* files when you had it set as:
innodb_
To find this out I asked for the entire error log, while you sent me last part for the startup attemp, but nothing before that. This is why we later asked for /var/log/messages etc... Probably the file was extending when some crash or unplanned restart happened, and error log should show that.
2. Why you can start it with adding the same file twice into innodb_
innodb_
but can not start with just:
innodb_
It would be nice to get error log content for this attemp as well (or, again, entire error log, with everything you've tried and what happened before).
For the second problem, in the source code (storage/
...
2769 sum_of_
2770
2771 for (i = 0; i < srv_n_data_files; i++) {
2772 sum_of_
2773 }
...
2821 if (!srv_read_
2822 && srv_auto_
2823 && sum_of_
2824
2825 ut_print_
2826 fprintf(stderr,
2827 " InnoDB: Error: tablespace size stored in header"
2828 " is %lu pages, but\n",
2829 (ulong) tablespace_
2830 ut_print_
2831 fprintf(stderr,
2832 " InnoDB: the sum of data file sizes"
2833 " is only %lu pages\n",
2834 (ulong) sum_of_
2835
2836 if (srv_force_recovery == 0) {
2837
2838 ut_print_
2839 fprintf(stderr,
2840 " InnoDB: Cannot start InnoDB. The tail of"
...
and from that it's clear that only total size from all entries in innodb_
So, if there is some other value to try for startup, it's this one:
innodb_
I've added 153M of "missing" size accor...
chrisjob1021 (chris-obrien-1021) wrote : | #8 |
I tried the line as you described--
innodb_
And it still resulted in failure to start.
Let me get you full log from the entire event.
chrisjob1021 (chris-obrien-1021) wrote : | #9 |
The complete log file from the event is attached. Please let me know what else you need.
Validating innodb_
Basically, I am able to reproduce something similar (not exactly the same) with a very simple text case. Look:
[root@centos ~]# mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23-72.1-log Percona Server (GPL), Release 72.1, Revision 0503478
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@datadir, @@innodb_
+------
| @@datadir | @@innodb_
+------
| /var/lib/mysql/ | ibdata1:
+------
1 row in set (0.00 sec)
mysql> exit
Bye
[root@centos ~]# vi /etc/my.cnf
[root@centos ~]# service mysql restart
Shutting down MySQL (Percona Server)
Starting MySQL (Percona Server)...... SUCCESS!
[root@centos ~]# ls -lh /var/lib/
-rw-rw----. 1 mysql mysql 18M Mar 16 20:17 /var/lib/
-rw-rw----. 1 mysql mysql 12M Mar 16 20:17 /var/lib/
So, it was a very basic setup of Percona Server with 2 data files. One was 18M at the moment when I started this test, then I've added another one, 12M in size and auto extending. So far it's OK.
Now, I planned to add another one, 13M in size (for whatever reason), ibdata3, but made a typo and added ibdata2 for a second time 9buth with different size 13M):
[root@centos ~]# grep innodb_data_file /etc/my.cnf
innodb_
[root@centos ~]# service mysql restart
Shutting down MySQL (Percona Server)
Starting MySQL (Percona Server)
[root@centos ~]# ps aux | grep mysqld
root 4539 0.0 0.0 103244 868 pts/0 S+ 20:19 0:00 grep mysqld
So, we had got an error and in the error log we see:
[root@centos ~]# tail -80 /var/log/mysqld.log
150316 20:18:54 mysqld_safe mysqld from pid file /var/lib/
150316 20:18:58 mysqld_safe Transparent huge pages are already set to: never.
150316 20:18:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2015-03-16 20:19:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_
2015-03-16 20:19:00 4337 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-
2015-03-16 20:19:00 4337 [Note] Plugi...
chrisjob1021 (chris-obrien-1021) wrote : | #12 |
Good info!
However, I will say that I did not touch 'innodb_
tags: | added: upstream |
tags: | added: i52048 |
summary: |
- Percona Server 5.6.22-72.0 failed to start + innodb_data_file_path is not checked for duplicate file names | Percona + Server 5.6.22-72.0 failed to start |
Percona now uses JIRA for bug reports so this bug report is migrated to: https:/
Please, send the entire error log from this MySQL server and the output of:
ls -l /mysql5/ inno_db/ data