[Maria-discuss] Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71
as far as i remember i was told 10.3 is able to fix that after a full dcade now but it don't 2019-09-11 18:18:12 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2019-09-11 18:18:12 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2019-09-11 18:18:12 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2019-09-11 18:18:12 0 [ERROR] InnoDB: Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71 2019-09-11 18:18:12 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2019-09-11 18:18:12 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2019-09-11 18:18:12 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2019-09-11 18:18:12 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``dbmail`.`#sql2-704-271``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2019-09-11 18:18:12 0 [Warning] InnoDB: Ignoring tablespace for `dbmail`.`#sql2-704-271` because it could not be opened. MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; +----------+----------------------+------+--------+-------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+----------------------+------+--------+-------+------------+---------------+------------+ | 672 | dbmail/#sql2-704-271 | 41 | 5 | 545 | Compressed | 8192 | Single | +----------+----------------------+------+--------+-------+------------+---------------+------------+ 1 row in set (0.002 sec) MariaDB [(none)]> use dbmail ERROR 1051 (42S02): Unknown table 'dbmail.dbmail/#sql2-704-271' MariaDB [dbmail]> DROP TABLE `dbmail#sql2-704-271`; ERROR 1051 (42S02): Unknown table 'dbmail.dbmail#sql2-704-271' MariaDB [dbmail]>
Hi, Reindl! 1. Do you actually have <datadir>/dbmail/*271* files ? 2. What did you do to end up in this situation? May be an interrupted ALTER TABLE? A crash or kill -9, perhaps? On Sep 11, Reindl Harald wrote:
as far as i remember i was told 10.3 is able to fix that after a full dcade now but it don't
2019-09-11 18:18:12 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2019-09-11 18:18:12 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2019-09-11 18:18:12 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2019-09-11 18:18:12 0 [ERROR] InnoDB: Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71 2019-09-11 18:18:12 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2019-09-11 18:18:12 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2019-09-11 18:18:12 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2019-09-11 18:18:12 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``dbmail`.`#sql2-704-271``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2019-09-11 18:18:12 0 [Warning] InnoDB: Ignoring tablespace for `dbmail`.`#sql2-704-271` because it could not be opened.
MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; +----------+----------------------+------+--------+-------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+----------------------+------+--------+-------+------------+---------------+------------+ | 672 | dbmail/#sql2-704-271 | 41 | 5 | 545 | Compressed | 8192 | Single | +----------+----------------------+------+--------+-------+------------+---------------+------------+ 1 row in set (0.002 sec)
MariaDB [(none)]> use dbmail ERROR 1051 (42S02): Unknown table 'dbmail.dbmail/#sql2-704-271' MariaDB [dbmail]> DROP TABLE `dbmail#sql2-704-271`; ERROR 1051 (42S02): Unknown table 'dbmail.dbmail#sql2-704-271' MariaDB [dbmail]>
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Am 14.09.19 um 14:05 schrieb Sergei Golubchik:
Hi, Reindl!
1. Do you actually have <datadir>/dbmail/*271* files ?
no as you can see "Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd'", i deleted the temp files 10 years ago after one week of not used obviously from anything and didn't imagine the weird behavior of innodb
2. What did you do to end up in this situation? May be an interrupted ALTER TABLE? A crash or kill -9, perhaps?
yes, 10 yeas ago before systemd came out a f**ing homegrown monitoring solution before systemed came in my way shortly after apply the inndob compression patch and cmpresison was done and frankly after excatly a full deacde i am tired of that dumb startup errors and it's time that innodb forgets about the <datadir>/dbmail/*271* files
Hi, Reindl! On Sep 14, Reindl Harald wrote:
Am 14.09.19 um 14:05 schrieb Sergei Golubchik:
Hi, Reindl!
1. Do you actually have <datadir>/dbmail/*271* files ?
no as you can see "Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd'", i deleted the temp files 10 years ago after one week of not used obviously from anything and didn't imagine the weird behavior of innodb
Okay. It looks like the only way to get rid of this warning would be to drop this table manually. Like, `create table t1 ... engine=innodb` then copy t1.idb and t1.frm to #sql2-704-271.* and then drop t1 and `#mysql50#sql2-704-271` tables. InnoDB doesn't do it automatically, because #sql2* tables might actually contain important user data in some rare cases. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Am 16.09.19 um 15:58 schrieb Sergei Golubchik:
Hi, Reindl!
On Sep 14, Reindl Harald wrote:
Am 14.09.19 um 14:05 schrieb Sergei Golubchik:
Hi, Reindl!
1. Do you actually have <datadir>/dbmail/*271* files ?
no as you can see "Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd'", i deleted the temp files 10 years ago after one week of not used obviously from anything and didn't imagine the weird behavior of innodb
Okay.
It looks like the only way to get rid of this warning would be to drop this table manually. Like, `create table t1 ... engine=innodb` then copy t1.idb and t1.frm to #sql2-704-271.* and then drop t1 and `#mysql50#sql2-704-271` tables.
tried that years ago i even gone that far to find out which tables that originally was and copied the real ones to the expected temp-table filenames to silence that startup errors for some years with 10.1 or 10.2 i had to delete both files because MariaDB crashed at startup because they don't really matched what the global tablespace expected it's a shame that you simply can't get rid of garbage from the global tablespace
On Mon, Sep 16, 2019 at 5:26 PM Reindl Harald <h.reindl@thelounge.net> wrote:
Am 16.09.19 um 15:58 schrieb Sergei Golubchik:
It looks like the only way to get rid of this warning would be to drop this table manually. Like, `create table t1 ... engine=innodb` then copy t1.idb and t1.frm to #sql2-704-271.* and then drop t1 and `#mysql50#sql2-704-271` tables.
Actually, you should not copy the .ibd file, because that could trigger an assertion failure in InnoDB. (Heikki's opinion was that the best thing to do when encountering corruption is to take down the whole server. I disagree, but it would be a lot of work to fix all the code.) InnoDB should allow DROP TABLE just fine even if the .ibd file does not exist.
it's a shame that you simply can't get rid of garbage from the global tablespace
Starting with MDEV-14585 InnoDB actually does drop #sql- tables during startup. The #sql2 tables are intentionally preserved, because during ALTER TABLE…ALGORIHTM=COPY there are multiple internal commits, and if the server is killed at the right moment, then the user table will be known only by #sql- and #sql2 names. We do not want to remove the only copy of the table. Ultimately, for new DDL statements, this should be fixed when MDEV-17567 implements Atomic DDL. I do not think that we can even then safely remove #sql2 tables from old installations, because we must think of upgrade scenarios. Best regards, Marko
Am 16.09.19 um 16:43 schrieb Marko Mäkelä:
it's a shame that you simply can't get rid of garbage from the global tablespace
Starting with MDEV-14585 InnoDB actually does drop #sql- tables during startup. The #sql2 tables are intentionally preserved, because during ALTER TABLE…ALGORIHTM=COPY there are multiple internal commits, and if the server is killed at the right moment, then the user table will be known only by #sql- and #sql2 names. We do not want to remove the only copy of the table.
Ultimately, for new DDL statements, this should be fixed when MDEV-17567 implements Atomic DDL. I do not think that we can even then safely remove #sql2 tables from old installations, because we must think of upgrade scenarios.
that all don't justify why the drop table simply can't remove the reference to something that literally don#t exist for a whole decade either that idiotic table is known or it's unknown if it's unknow get rid of it or at least allow the admin to do so with doing some crazy dance introducing way more room for troubles than just forget about something which isn't there anyways MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; +----------+----------------------+------+--------+-------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+----------------------+------+--------+-------+------------+---------------+------------+ | 672 | dbmail/#sql2-704-271 | 41 | 5 | 545 | Compressed | 8192 | Single | +----------+----------------------+------+--------+-------+------------+---------------+------------+ 1 row in set (0.002 sec) MariaDB [(none)]> use dbmail ERROR 1051 (42S02): Unknown table 'dbmail.dbmail/#sql2-704-271' MariaDB [dbmail]> DROP TABLE `dbmail#sql2-704-271`; ERROR 1051 (42S02): Unknown table 'dbmail.dbmail#sql2-704-271' MariaDB [dbmail]>
On Mon, Sep 16, 2019 at 5:48 PM Reindl Harald <h.reindl@thelounge.net> wrote:
Am 16.09.19 um 16:43 schrieb Marko Mäkelä:
Ultimately, for new DDL statements, this should be fixed when MDEV-17567 implements Atomic DDL. I do not think that we can even then safely remove #sql2 tables from old installations, because we must think of upgrade scenarios.
that all don't justify why the drop table simply can't remove the reference to something that literally don#t exist for a whole decade
either that idiotic table is known or it's unknown
Like I wrote, we have to think of upgrade scenarios. Specifically, we must keep in mind that before MDEV-14717 (in MariaDB Server 10.3 and 10.2.19+), the InnoDB-internal RENAME operations were not crash-safe at all. So, even if the InnoDB data dictionary thinks that the table name should start with #sql2, it is perfectly possible that the data file is known to the file system as something else. The file is not necessarily entirely missing. And we cannot afford to read the first page of each data file at startup, to find such orphan files. That is simply too slow. In fact, I removed such code (which I believed to be unnecessary) in 10.2.24 and 10.3.15: MDEV-18733 MariaDB slow start after crash recovery
if it's unknow get rid of it or at least allow the admin to do so with doing some crazy dance introducing way more room for troubles than just forget about something which isn't there anyways
You are welcome to submit a well designed, implemented and tested patch to address this. -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation
participants (3)
-
Marko Mäkelä -
Reindl Harald -
Sergei Golubchik