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

Problem with temporary objects

    XMLWordPrintable

    Details

      Description

      Dears,

      I would like to report a bug if someone else didnt report till now.
      Before a month we migrated from MySQL to MariaDB (10.2.16) but we have an issue with reindexing of tables. During reindex of tables there occurred a deadlock (error log below):

      2018-07-25  4:08:03 139624830838528 [Note] InnoDB: Transactions deadlock detected,_ _dumping detailed information.
      2018-07-25  4:08:03 139624830838528 [Note] InnoDB: 
      *** (1) TRANSACTION:
       
      TRANSACTION 1183260774, ACTIVE 1 sec setting auto-inc lock
      mysql tables in use 7, locked 7
      LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
      MySQL thread id 3370, OS thread handle 139654155511552, query id 613253413 10.4.0.113 _xxxx_user update
      REPLACE INTO `newplatform`.`_accounts_new` (`id`, `username`, `password`, `type`, `createdate`,_ `lastupdatedate`, `active`, `expired`, `termsagreed`, `dynamicschemainstanceid`) VALUES (NEW.`id`, NEW.`username`, NEW.`password`, NEW.`type`, NEW.`createdate`, _NEW.`lastupdatedate`, NEW.`active`, NEW.`expired`, NEW.`termsagreed`, NEW.`dynamicschemainstanceid`)
      2018-07-25  4:08:03 139624830838528 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE_ GRANTED:
      ...
      

      for this reason MariaDB created a temporary file called '#sql-7b3_3c18' in mysql directory of my database (/var/lib/mysql/databasename/#sql-7b3_3c18).
      From this moment MariaDB started to appear the error log below every second:

      2018-07-25  4:13:33 139624830535424 [ERROR] Invalid (old?) table or database name '#sql-7b3_3c18'
      ...
      

      After, I deleted these two files '#sql-7b3_3c18.frm' and '#sql-7b3_3c18.ibd' from Linux terminal (my mistake) and restarted the MariaDB Service. Error log didn't appear anymore (because of restart) but the files still exists in INNODB_SYS_TABLES:

      MariaDB [(none)]> *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 | SPACE_TYPE |
      +----------+----------------------------+------+--------+-------+-------------+------------+---------------+------------+
      |     4792 | newplatform/#sql-7b3_3c18  |   33 |      8 |  4753 | Barracuda   | Dynamic    |             0 | Single     |
      +----------+----------------------------+------+--------+-------+-------------+------------+---------------+------------+
      1 rows in set (0.00 sec)
      

      According to link: https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html
      I tried to create these two files as empty:
      touch '#sql-7b3_3c18.frm'
      touch '#sql-7b3_3c18.ibd'

      and also to copy the original table files to this name:
      cp accounts.frm '#sql-7b3_3c18.frm'
      cp accounts.ibd '#sql-7b3_3c18.ibd'

      Then tried to detele from MariaDB terminal but no success:

      MariaDB [newplatform]> DROP TABLE `#sql-7b3_3c18`;
      ERROR 1051 (42S02): Unknown table 'newplatform.#sql-7b3_3c18'
      MariaDB [newplatform]> DROP TABLE `#mysql50##sql-7b3_3c18`;
      ERROR 1051 (42S02): Unknown table 'newplatform.#mysql50##sql-7b3_3c18'
      

      If I tried to create this table with database script 'CREATE TABLE `#sql-7b3_3c18` (...)' i have the message:

      ERROR 1005 (HY000): Can't create table `newplatform`.`#sql-7b3_3c18` (errno: 121 "Duplicate key on write or update")
      

      Any idea what can the problem be?

      Thank you in advance,
      Shkemb

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              shkemb Shkemb
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: