Re: [Maria-discuss] Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71
On Mon, Sep 16, 2019 at 5:48 PM Reindl Harald 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 ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71
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]> ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71
On Mon, Sep 16, 2019 at 5:26 PM Reindl Harald 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 ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71
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 /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 ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71
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 /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 secur...@mariadb.org ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71
Am 14.09.19 um 14:05 schrieb Sergei Golubchik: > Hi, Reindl! > > 1. Do you actually have /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 /dbmail/*271* files ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
