Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11898

tablespace does not exist or is just being dropped

Details

    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.
      

      Attachments

        Issue Links

          Activity

            hreindl Reindl Harald added a comment -

            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.

            hreindl Reindl Harald added a comment - 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.

            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.

            marko Marko Mäkelä added a comment - 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 .
            hreindl Reindl Harald added a comment -

            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.

            hreindl Reindl Harald added a comment - 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.

            People

              marko Marko Mäkelä
              hreindl Reindl Harald
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.