Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.2.16
-
None
-
Linux-CentOS - Production system
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
- duplicates
-
MDEV-14585 Automatically remove #sql- tables in innodb dictionary during recovery
-
- Closed
-