[MDEV-32165] After mysqld shuts down normally, restart mysqld and the partition table cannot be found. Created: 2023-09-14  Updated: 2023-10-30  Resolved: 2023-10-30

Status: Closed
Project: MariaDB Server
Component/s: Platform Windows
Affects Version/s: 10.5.22
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: kennyliao Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None
Environment:

windows


 Description    

2023-08-30 16:52:57 0 [Note] .\bin\mysqld (initiated by: sys[sys] @  [127.0.0.1]): Normal shutdown
 
2023-08-30 16:52:57 0 [Note] Event Scheduler: Killing the scheduler thread, thread id 2
 
2023-08-30 16:52:57 0 [Note] Event Scheduler: Waiting for the scheduler thread to reply
 
2023-08-30 16:52:57 0 [Note] Event Scheduler: Stopped
 
2023-08-30 16:52:57 0 [Note] Event Scheduler: Purging the queue. 50 events
 
2023-08-30 16:52:57 0 [Note] InnoDB: FTS optimize thread exiting.
 
2023-08-30 16:52:57 0 [Note] InnoDB: Starting shutdown...
 
2023-08-30 16:53:19 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
 
2023-08-30 16:53:19 0 [Note] InnoDB: Shutdown completed; log sequence number 63880810; transaction id 69971
 
2023-08-30 16:53:19 0 [Note] .\bin\mysqld: Shutdown complete
 
 
 
2023-08-30 17:00:20 0 [Note] Starting MariaDB 10.5.22-MariaDB-log source revision 7e650253dc488debcb0898ebe6d385bf6dfa3656 as process 49816
 
2023-08-30 17:00:20 0 [Warning] The parameter innodb_buffer_pool_instances is deprecated and has no effect.
 
2023-08-30 17:00:20 0 [Warning] The parameter innodb_page_cleaners is deprecated and has no effect.
 
2023-08-30 17:00:20 0 [Note] InnoDB: Uses event mutexes
 
2023-08-30 17:00:20 0 [Note] InnoDB: Compressed tables use zlib 1.2.13
 
2023-08-30 17:00:20 0 [Note] InnoDB: Number of pools: 1
 
2023-08-30 17:00:20 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
 
2023-08-30 17:00:20 0 [Note] InnoDB: Initializing buffer pool, total size = 2147483648, chunk size = 134217728
 
2023-08-30 17:00:20 0 [Note] InnoDB: Completed initialization of buffer pool
 
2023-08-30 17:00:21 0 [Note] InnoDB: 128 rollback segments are active.
 
2023-08-30 17:00:22 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2023-08-30 17:00:22 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2023-08-30 17:00:22 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
 
2023-08-30 17:00:22 0 [Note] InnoDB: 10.5.22 started; log sequence number 64595296; transaction id 72181
 
2023-08-30 17:00:22 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2023-08-30 17:00:22 0 [Note] Server socket created on IP: '::'.
 
2023-08-30 17:00:22 2 [Note] Event Scheduler: scheduler thread started with id 2
 
2023-08-30 17:00:22 0 [Note] Reading of all Master_info entries succeeded
 
2023-08-30 17:00:22 0 [Note] Added new Master_info '' to hash table
 
2023-08-30 17:00:22 0 [Note] .\bin\mysqld: ready for connections.
 
Version: '10.5.22-MariaDB-log'  socket: ''  port: 3306  mariadb.org binary distribution
 
 
 
2023-08-30 17:00:57 55 [ERROR] Failed to open table dsdb/attendance_record_202310#p#p20231001.
 
 
 
2023-08-30 17:00:57 55 [ERROR] Failed to open table dsdb/attendance_record_202311#p#p20231101.
 
 
 
2023-08-30 17:00:57 55 [ERROR] Failed to open table dsdb/attendance_record_202312#p#p20231201.
 
 
 
2023-08-30 17:00:57 55 [ERROR] Failed to open table dsdb/attendance_record_202401#p#p20240101.
 
 
 
2023-08-30 17:00:57 55 [ERROR] Failed to open table dsdb/attendance_record_202402#p#p20240201.
 
 
 
2023-08-30 17:00:58 55 [ERROR] Failed to open table dsdb/attendance_record_202403#p#p20240301.
 
 
 
2023-08-30 17:00:58 55 [ERROR] Failed to open table dsdb/attendance_record_202404#p#p20240401.
 
 
 
2023-08-30 17:00:58 55 [ERROR] Failed to open table dsdb/attendance_record_202405#p#p20240501.
 
 
 
2023-08-30 17:00:58 55 [ERROR] Failed to open table dsdb/attendance_record_202406#p#p20240601.
 
 
 
2023-08-30 17:00:58 55 [ERROR] Failed to open table dsdb/attendance_record_202407#p#p20240701.



 Comments   
Comment by Daniel Black [ 2023-09-14 ]

What dsdb/attendance_record* files exist? Do you have any non-default configuration options set?

Comment by kennyliao [ 2023-09-14 ]

1、dsdb/attendance_record* files exist ibd and frm

2、The following is the parameter value for my. ini:

[client]
 
port=3306
 
 
 
[mysqld]
 
secure_file_priv=..\\mysql
 
port=3306
 
pid-file=Storage.pid
 
basedir=..
 
datadir=..\\data
 
tmpdir=..\\tmp
 
slave_load_tmpdir=..\\tmp
 
character-set-server=utf8
 
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
 
table_open_cache=10240
 
back_log=500
 
max_connections=1000
 
max_connect_errors=10000
 
thread_cache_size=100
 
thread_stack=192K
 
sysdate-is-now=1
 
ft_min_word_len=4
 
lower_case_table_names=1
 
max_allowed_packet=16M
 
max_heap_table_size=64M
 
max_length_for_sort_data=8M
 
net_buffer_length=65536
 
join_buffer_size=8M
 
sort_buffer_size=16M
 
binlog_cache_size=4M
 
query_cache_type=0
 
query_cache_size=0
 
tmp_table_size=64M
 
slave_net_timeout=30
 
event_scheduler=ON
 
skip-name-resolve=ON
 
skip-external-locking=ON
 
skip-slave-start=ON
 
skip_symbolic_links=ON
 
secure_auth=ON
 
log-warnings=2
 
skip-grant-tables=OFF
 
allow-suspicious-udfs=OFF
 
local_infile=OFF
 
log-bin=Storage
 
binlog-format=mixed
 
max-binlog-size=512M
 
expire-logs-days=7
 
log-error=Storage
 
server-id=1
 
sync_binlog=1000
 
slave_skip_errors=1517
 
default-storage-engine=InnoDB
 
innodb_file_per_table=1
 
transaction_isolation=REPEATABLE-READ
 
innodb_strict_mode=1
 
innodb_buffer_pool_size=2048M
 
innodb_data_file_path=ibdata1:200M:autoextend
 
innodb_open_files=1000
 
innodb_write_io_threads=32
 
innodb_read_io_threads=32
 
innodb_thread_concurrency=0
 
innodb_io_capacity=8000
 
innodb_flush_log_at_trx_commit=2
 
innodb_log_buffer_size=16M
 
innodb_log_file_size=100M
 
innodb_adaptive_flushing=1
 
innodb_max_dirty_pages_pct=60
 
innodb_lock_wait_timeout=15
 
innodb_fast_shutdown=0
 
innodb_rollback_on_timeout=ON
 
innodb_old_blocks_time=1000
 
interactive_timeout=28800
 
wait_timeout=28800
 
innodb_lru_scan_depth=8096
 
log_bin_trust_function_creators=1
 
innodb-buffer-pool-load-at-startup=0
 
innodb-buffer-pool-load-now=0
 
 
 
innodb-buffer-pool-dump-at-shutdown=0 
innodb-buffer-pool-load-abort=1
 
 
 
innodb-buffer-pool-instances=2
 
innodb-page-cleaners=16
 
innodb-purge-threads=8
 
innodb-checksum-algorithm=CRC32
 
optimizer_switch="index_merge=off"
 
 
 
[mysqldump]
 
quick=ON
 
net_buffer_length=65536
 
max_allowed_packet=16M
 
 
 
[mysql]
 
default-character-set=utf8
 
no-auto-rehash=ON

Comment by kennyliao [ 2023-09-15 ]

Sergei Golubchik, noformat ,what?

These logs printed from the file *.err，it should be right.

Comment by Marko Mäkelä [ 2023-09-27 ]

I see that the message was slightly changed by serg in MDEV-5574, but it was originally added in the InnoDB Plugin for MySQL 5.1, which used to be distributed separately by Oracle until InnoDB Plugin 1.0.4 was included in the MySQL 5.1.38 distribution (MariaDB commit). The original commit history was added separately later, and the original commit message says the following:

Introduce retry/sleep logic as a workaround for a transient bug where ::open fails for partitioned tables randomly if we are using one file per table. (Bug #33349)

That MySQL bug description says:

It looks like a race condition is possible when this command is run:

alter table foo partition pmax into
 
( partition p733384 values less than (733384) ENGINE = InnoDB,
 
partition pmax values less than maxvalue)

The same basic command at times shows this error although everything was completed successfully:

[ERROR] Cannot find table test/foo#P#p733384 from the internal data dictionary of InnoDB though the .frm file for the table exists....

I would expect that metadata locks (MDL) ultimately fixed this problem and the original reason of outputting that message went away.

kennyliao, were any ALTER TABLE or other DDL statements executed before the server had been shut down? Would it be possible to attach the procmon.exe log for the server startup? From MDEV-32027 you should be familiar with that tool.

Generated at Thu Feb 08 10:29:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.