| Bug #90711 | mysqld: Duplicate entry 'innodb_system' for key 'name' | ||
|---|---|---|---|
| Submitted: | 2 May 2018 5:20 | Modified: | 1 Nov 2018 7:21 |
| Reporter: | Mathias Berchtold | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 8.0.11 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[2 May 2018 5:20]
Mathias Berchtold
[2 May 2018 7:52]
MySQL Verification Team
Please provide the my.ini file. Thanks.
[2 May 2018 8:09]
Mathias Berchtold
my.ini
Attachment: my.ini (text/plain), 13.61 KiB.
[2 May 2018 8:28]
MySQL Verification Team
Thank you for the feedback. Please elaborate step by step the process you did to upgrade. Thanks.
[2 May 2018 8:42]
Mathias Berchtold
I have followed the instructions here: https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/upgrading-strategies.html "In-Place Upgrade" Then I verified the prerequisites: mysqlcheck -u root -p --all-databases --check-upgrade then ran all the queries Stopped mysql service Downloaded the mysql 8.0.11 GA .zip file, extracted it and ran: mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini"
[2 May 2018 11:13]
MySQL Verification Team
5.7.22 running-stopped service-remove bin and share directory
Attachment: 90711_1.png (image/png, text), 31.38 KiB.
[2 May 2018 11:16]
MySQL Verification Team
plaqced 8.0 bin and share, modify sql_mode of my.ini start server with warnings
Attachment: 90711_3.txt (text/plain), 7.39 KiB.
[2 May 2018 11:17]
MySQL Verification Team
started server after mysql_upgrade
Attachment: 90711_2.png (image/png, text), 41.20 KiB.
[2 May 2018 11:21]
MySQL Verification Team
Started service of 8.0.11
Attachment: 90711_4.png (image/png, text), 30.23 KiB.
[2 May 2018 11:22]
Mathias Berchtold
What is the purpose of your comments? The error message points to the table spaces and you do not have the same data (mysql database, etc). Testing this with the default data folder will not trigger the issue.
[2 May 2018 11:28]
MySQL Verification Team
Thank you for the feedback. I did an upgrade from 5.7.22 to 8.0.11 without problem: First picture shows you 5.7.22 running. Service stopped. Removed the fully bin and share directories and replaced them by 8.0.11 bin and share directories. Tried to start server which failed by wrong sql_mode in my.ini, removed NO_AUTO_CREATE_USER. Started server which presents warnings and error, then run mysql_upgrade. Started server as standalone and as service w/o problem.
[2 May 2018 11:30]
MySQL Verification Team
Then sorry, what is your test case for. I can't repeat without a repeatable test case.
[2 May 2018 11:32]
Mathias Berchtold
Can you please assign somebody else to this case. Preferably somebody that understands the issue.
[2 May 2018 12:19]
MySQL Verification Team
Thank you for the feedback, Mathias. I quickly tried to reproduce the issue by creating dummy schema and provisioning huge data and then attempted migration i.e "in place upgrade" from 5.7.22 to 8.0.11 but didn't notice any issues. Is there anyway you can provide logical dump(if you prefer then you may mark it as private after posting here, if dump size is greater than 3MB then upload it to our sftp - details in the files tab of this bug report) of the schema from your environment? Looking at the error message it seems *some* issue while migrating table spaces. Have you used any general tablespaces? Alternatively, my colleague Chiran has checked internally with Developers and recommendation is to use “Upgrade checker”, quoting as is from official blog - "What UC does is connect to specified server and, if the server version is supported (at the moment only 5.7) and user has enough privileges (ALL privileges with GRANT option), runs a series of checks. If any issues are discovered, it displays them along with any advice targeted at resolving those issues. Not all the issues discovered have the same gravity (they are all tagged either “notice”, “warning”, or “error”) and results in failed upgrade process, but you should consider them all to be able to take full advantage of MySQL 8.0. At the end, UC prints a summary and returns an integer value describing he severity of the issues found: 0 – no issues or only ones categorized as notice, 1 – No fatal errors were found, but some potential issues were detected, 2 – UC found errors that must be fixed before upgrading to 8.0." More details here https://mysqlserverteam.com/mysql-shell-8-0-4-introducing-upgrade-checker-utility/ Regards, Umesh
[2 May 2018 14:32]
Mathias Berchtold
The upgrade tool did not show any fatal errors. I have attached the mysqldump as a private attachment. And I have also uploaded the complete mysql data folder to the SFTP server. Filename: mysql-bug-90711.zip Thank you.
[3 May 2018 7:56]
MySQL Verification Team
Thank you for the requested details. I'm seeing the issue on windows and when using provided datadir. Thanks, Umesh
[3 May 2018 7:56]
MySQL Verification Team
Test
Attachment: 90711.results (application/octet-stream, text), 9.94 KiB.
[28 May 2018 12:42]
Amitabh Das
Posted by developer: This looks like an issue that comes from an RC version of 5.7 (5.7.6 and 5.7.7)
[30 May 2018 12:47]
Ståle Deraas
Posted by developer: Mathias, can you verify that our theory on why this issue appears stems from 5.7.6 or 5.7.7 ?
[30 May 2018 13:11]
Mathias Berchtold
I cannot recall what was the first 5.7.x build that was installed. But I'm fairly certain it was not any of the milestones (e.g. 5.7.6).
[7 Aug 2018 14:44]
Amitabh Das
Posted by developer: "innodb_system" as an entry in information_schema.INNODB_SYS_TABLESPACES was introduced in 5.7.6 and removed in 5.7.8, and so I'm not able to get the upgrade sequence to reproduce this situation. Of course, it may be possible that I missed a scenario, so can you help me with the upgrade path that you took?
[1 Dec 2021 10:15]
Piotr Sikora
I have same issue. I upgrading from 5.6 to 5.7.6 and next to 5.7.36 After that I need to upgrade to 8.0 and see same error. I found that I have record "innodb_system_ in INNODB_SYS_TABLESPACES but I can't delete it: mysql> SELECT * FROM INNODB_SYS_TABLESPACES WHERE name = 'innodb_system'; +-------+---------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+ | SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | +-------+---------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+ | 0 | innodb_system | 0 | Antelope | Compact or Redundant | 16384 | 0 | System | 0 | 0 | 0 | +-------+---------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+ 1 row in set (0.01 sec) mysql> DELETE FROM INNODB_SYS_TABLESPACES WHERE name = 'innodb_system'; ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'information_schema' mysql> I'am using docker verions.
[27 Nov 2023 7:48]
Trimurthy Pothanaboyina
Facing similar issue. From MySQL Error Log : 2023-11-18T02:43:53.318028Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-log-path: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path. 2023-11-18T02:43:53.318086Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.33-25) starting as process 1472 2023-11-18T02:43:53.350371Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory. 2023-11-18T02:43:53.350427Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2023-11-18T02:44:19.414153Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. mysqld: Duplicate entry 'mysql/slave_worker_info' for key 'tablespaces.name' 2023-11-18T02:44:19.735742Z 1 [ERROR] [MY-011006] [Server] Got error 168 from SE while migrating tablespaces. 2023-11-18T02:44:19.738855Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2023-11-18T02:44:19.738874Z 0 [ERROR] [MY-010119] [Server] Aborting 2023-11-18T02:44:22.043376Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.33-25) Percona Server (GPL), Release 25, Revision 60c9e2c5. 2023-11-18T03:03:47.770488Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it. 2023-11-18T03:03:47.770887Z 0 [ERROR] [MY-010119] [Server] Aborting From : INNODB_SYS_TABLESPACES mysql> SELECT * FROM information_schema.INNODB_SYS_TABLESPACES WHERE name like '%slave_worker_info%'; +-------+-------------------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+ | SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | +-------+-------------------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+ | 5 | mysql/slave_worker_info | 0 | Antelope | Compact or Redundant | 16384 | 0 | Single | 4096 | 98304 | 98304 | | 1674 | mysql/slave_worker_info | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 98304 | 98304 | +-------+-------------------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+ 2 rows in set (0.00 sec) mysql> drop table slave_worker_info; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM information_schema.INNODB_SYS_TABLESPACES WHERE name like '%slave_worker_info%'; +-------+-------------------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+ | SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | +-------+-------------------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+ | 5 | mysql/slave_worker_info | 0 | Antelope | Compact or Redundant | 16384 | 0 | Single | 0 | 0 | 0 | +-------+-------------------------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+ 1 row in set (0.00 sec)
