[MDEV-11898] tablespace does not exist or is just being dropped Created: 2017-01-24  Updated: 2018-09-21  Resolved: 2018-03-14

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.1.21
Fix Version/s: 10.3.3

Type: Bug Priority: Major
Reporter: Reindl Harald Assignee: Marko Mäkelä
Resolution: Fixed Votes: 1
Labels: None
Environment:

Linux

Issue Links:
Duplicate
duplicates MDEV-14585 Automatically remove #sql- tables in ... Closed
Relates
relates to MDEV-13227 Assertion failure len < 16384 in file... Closed
relates to MDEV-12548 Add maria backup tool to MariaDB 10.2 Closed

 Description   

why can't there be a simple starup option to cleanup that orpahned references at startup one for all instead need to consider dump and re-create the whole server?

in 2009 mysqld crahsed due 'alter table' (files-per-table) and temporary files where left, after they did not change their timestamp for days i deleted them and the warnings started - to get rid of them i created a table with the same structure and named them identically

with 10.1.x the slightly mismatch leads to crash the server at startup, see below

removing that files and mysqld starts again but eevery time with the warning the the file does not exist and https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html is not helpful

MariaDB [dbmail]> drop table `#sql2-704-271`;
 
ERROR 1051 (42S02): Unknown table 'dbmail.#sql2-704-271'
 
MariaDB [dbmail]> drop table `#dbmail##sql2-704-271`;
 
ERROR 1051 (42S02): Unknown table 'dbmail.#dbmail##sql2-704-271'
 
MariaDB [dbmail]> drop table `##sql2-704-271`;
 
ERROR 1051 (42S02): Unknown table 'dbmail.##sql2-704-271'
 
MariaDB [dbmail]>
 
 
 
MariaDB [dbmail]> drop table `#mysql50##sql2-704-271`;
 
ERROR 1051 (42S02): Unknown table 'dbmail.#mysql50##sql2-704-271'
 
MariaDB [dbmail]> 
 
 
MariaDB [dbmail]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
 
+----------+----------------------+------+--------+-------+-------------+------------+---------------+
 
| TABLE_ID | NAME                 | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
 
+----------+----------------------+------+--------+-------+-------------+------------+---------------+
 
|      672 | dbmail/#sql2-704-271 |   41 |      5 |   545 | Barracuda | Compressed |          8192 |
 
+----------+----------------------+------+--------+-------+-------------+------------+---------------+
 
1 row in set (0,00 sec)

that crap temp files never deleted are from years ago and a crash

until now it was enough to keep that tempfiles, with 10.1.x mariadb don't start at all - how to get rid of that crap

the idiotic warnings are one thing but the segfauls now...
it's about a decade that it's not possible to cleanup such stuff

http://grokbase.com/t/mysql/mysql/136kx5qp1w/how-can-i-drop-a-table-that-is-named-logs-sql-ib203-and-appeared-after-mysql-crash

2017-01-23 22:01:27 139913013700928 [ERROR] InnoDB: Trying to do i/o to a tablespace which exists without .ibd data file. i/o type 10, space id 545, page no 0, i/o length 8192 bytes
 
2017-01-23 22:01:27 7f40097b2940  InnoDB: Error: trying to access tablespace 545 page no. 0,
 
InnoDB: but the tablespace does not exist or is just being dropped.
 
2017-01-23 22:01:27 139913013700928 [ERROR] InnoDB: tablespace id is 545 in the data dictionary but in file ./dbmail/#sql2-704-271.ibd it is 690!
 
 
 
2017-01-23 22:01:31 140380157278528 [ERROR] InnoDB: Trying to do i/o to a tablespace which exists without .ibd data file. i/o type 10, space id 545, page no 0, i/o length 8192 bytes
 
2017-01-23 22:01:31 7faccd687940  InnoDB: Error: trying to access tablespace 545 page no. 0,
 
InnoDB: but the tablespace does not exist or is just being dropped.
 
InnoDB: Error: Unable to read tablespace 545 page no 0 into the buffer pool after 100 attempts
 
InnoDB: The most probable cause of this error may be that the table has been corrupted.
 
InnoDB: You can try to fix this problem by using innodb_force_recovery.
 
InnoDB: Please see reference manual for more details.
 
InnoDB: Aborting...
 
2017-01-23 22:01:31 7faccd687940  InnoDB: Assertion failure in thread 140380157278528 in file buf0buf.cc line 3080
 
InnoDB: We intentionally generate a memory trap.
 
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
 
InnoDB: If you get repeated assertion failures or crashes, even
 
InnoDB: immediately after the mysqld startup, there may be
 
InnoDB: corruption in the InnoDB tablespace. Please refer to
 
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
 
InnoDB: about forcing recovery.
 
170123 22:01:31 [ERROR] mysqld got signal 6 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.



 Comments   
Comment by Reindl Harald [ 2017-01-24 ]

currently at every startup you get that error messages - the global tablespace is really horrible compared to MyISAM where a table are just two files and if they are gone, well, they are gone

2017-01-24 10:54:19 7f292c6d6940 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
2017-01-24 10:54:19 139814815754560 [ERROR] InnoDB: Could not find a valid tablespace file for 'dbmail/#sql2-704-271'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2017-01-24 10:54:19 139814815754560 [ERROR] InnoDB: Tablespace open failed for '"dbmail"."#sql2-704-271"', ignored.

Comment by Marko Mäkelä [ 2018-03-14 ]

hreindl, I fully agree with you: the InnoDB system tablespace is horrible and should be removed (MDEV-11633). But it is a long journey to get there. (Already before joining Innobase in 2003 I thought that it is a bad concept. Before I joined MariaDB 13¼ years later, doing that was out of the question.)

When it comes to dropping orphaned tables, I believe that your #mysql50##sql… trick should have worked if you had manually created a matching #sql….frm file. That said, I fixed this bug in MariaDB 10.3.3 under
MDEV-14585 Automatically remove #sql- tables in innodb dictionary during recovery

Before I felt safe to do this, I had to make rename operations crash-safe in InnoDB (MDEV-14717). This required an undo log format change, so it was not doable in a GA version, where we try to allow downgrades within the same major version series. I also did some fixes to DROP TABLE in MDEV-13407.

Comment by Reindl Harald [ 2018-03-14 ]

i would be happy if there would be only a startup option allow automatically remove all references for the stuff below - that problem is from 2009 and was silenced by fake files but with 10.1 their existence leaded to the crash i reported here, so i decided just delete them and live with the warnings at startup, but there should be really a capability to cleanup that issue

do i understand "MDEV-14585 Automatically remove #sql- tables in innodb dictionary during recovery" with 10.3 will do the trick *without a need that these files exist?

2018-03-14 12:38:27 139973611099328 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-03-14 12:38:27 139973611099328 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-03-14 12:38:27 139973611099328 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2018-03-14 12:38:27 139973611099328 [ERROR] InnoDB: Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71
2018-03-14 12:38:27 139973611099328 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-03-14 12:38:27 139973611099328 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-03-14 12:38:27 139973611099328 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2018-03-14 12:38:27 139973611099328 [ERROR] InnoDB: Could not find a valid tablespace file for `dbmail/#sql2-704-271`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2018-03-14 12:38:27 139973611099328 [Warning] InnoDB: Ignoring tablespace for `dbmail`.`#sql2-704-271` because it could not be opened.

Generated at Thu Feb 08 07:53:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.