我创建的以“dotNetChat_"开头的表格；重启 MySQL 后消失 [英] Tables I create that start with "dotNetChat_" disappear after restarting MySQL
问题描述
根据新信息，我创建了一个新问题 表示:
ERROR_SHARING_VIOLATION32 (0x20)该进程无法访问该文件，因为它正被另一个进程使用.正如我所说，这意味着文件被另一个进程锁定.当您重新启动 MySQL 时会发生这种情况，因为当您创建它们时，MySQL 将它们打开，而另一个进程无法锁定它们.一旦 MySQL 服务停止(重启)，另一个进程就会锁定文件，并且 MySQL 再次启动时无法打开它们.
要查看系统上锁定的文件，以及哪个进程将其锁定，您可以使用 http://technet.microsoft.com/en-us/sysinternals/bb896655.aspx
您需要以管理员身份运行 CMD Prompt，然后键入:
handle.exe thepwf_哪个应该显示哪个进程将文件锁定.
告诉我你发现了什么.
In light of new information, I have created a new question here that better explains the problem that I aim to fix.
I am trying to create a collection of tables in MySQL that all start with the prefrix dotNetChat_. Originally I had these tables set up in another server and everything was working fine despite the fact that they are configured with the same my.ini file. Whenever I first create these tables, they appear normally, but after restarting MySQL, they "disappear", and try to load the table contents using MySQL Workbench produces the following error:
Error Code: 1146. Table 'thepwf_prgminteractions.dotnetchat_testtable' doesn't exist
I checked my error log file, and here is what I found:
2014-03-05T23:05:31.224646Z 1 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2014-03-05T23:05:33.755361Z 2 [ERROR] InnoDB: Failed to find tablespace for table "thepwf_prgminteractions"."dotnetchat_testtable" in the cache. Attempting to load the tablespace with space id 24.
2014-03-05 18:05:33 0x1f30 InnoDB: Operating system error number 32 in a file operation.
InnoDB: The error means that another program is using InnoDB's files.
InnoDB: This might be a backup or antivirus software or another instance
InnoDB: of MySQL. Please close it to get rid of this error.
2014-03-05T23:05:33.760362Z 2 [ERROR] InnoDB: Could not find a valid tablespace file for 'thepwf_prgminteractions/dotnetchat_testtable'. See http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2014-03-05 18:05:33 0x1f30 InnoDB: cannot calculate statistics for table "thepwf_prgminteractions"."dotnetchat_testtable" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html
2014-03-05 18:05:34 0x1f30 InnoDB: cannot calculate statistics for table "thepwf_prgminteractions"."dotnetchat_testtable" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html
2014-03-05 18:05:34 0x1f30 InnoDB: cannot calculate statistics for table "thepwf_prgminteractions"."dotnetchat_testtable" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html
2014-03-05 18:05:35 0x1e54 InnoDB: cannot calculate statistics for table "thepwf_prgminteractions"."dotnetchat_testtable" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html
Before thinking to check my log file, I thought that something might have been wrong with the server, so I uninstalled the server, removed all server data files, reinstalled it, put the my.ini configuration file that I already have made up into place, and started things back up. What could be the problem here? Whenever I try to delete any schemas that have or had tables such as this one in them, I get the following error from MySQL Workbench:
EDIT: I have continued to play around with this for a while now, and after using IOBit to make sure all MySQL files were removed before reinstalling it, I am getting an error when I try to import the tables. The error from MySQL Workbench is useless, but here's something from the error log that might help.
2014-03-06T01:38:55.459658Z 0 [ERROR] InnoDB: Table thepwf_prgminteractions/p2p_messagedata in the InnoDB data dictionary has tablespace id 25, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2014-03-06T01:38:55.464671Z 0 [ERROR] InnoDB: Table thepwf_prgminteractions/p2p_onlineusers in the InnoDB data dictionary has tablespace id 26, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2014-03-06T01:38:55.468672Z 0 [ERROR] InnoDB: Table thepwf_prgminteractions/p2pchat_betaaccesskeys in the InnoDB data dictionary has tablespace id 24, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
I would say that for some reason, MySQL is denied access to InnoDB files, which it then cannot load, and continues without them. Can you verify that your process is not run twice, and that MySQL user is run under account that has access to MySQL lib directory.
I base my findings on this error:
[ERROR] InnoDB: Failed to find tablespace for table "thepwf_prgminteractions"."dotnetchat_testtable" in the cache. Attempting to load the tablespace with space id 24.
2014-03-05 18:05:33 0x1f30 InnoDB: Operating system error number 32 in a file operation.
InnoDB: The error means that another program is using InnoDB's files.
InnoDB: This might be a backup or antivirus software or another instance
InnoDB: of MySQL. Please close it to get rid of this error.
And error 32, based on http://msdn.microsoft.com/en-us/library/windows/desktop/ms681382(v=vs.85).aspx means:
ERROR_SHARING_VIOLATION
32 (0x20)
The process cannot access the file because it is being used by another process.
Which means, as I said, that files are being locked by another process. And it is happening when you restart MySQL because when you create them, MySQL has them open, and another process cannot lock them. As soon as MySQL service is stopped (for restart), another process locks the files, and MySQL cannot open them when it starts up again.
To see locked files on your system, and which process is holding it locked you can use http://technet.microsoft.com/en-us/sysinternals/bb896655.aspx
You would need to run CMD Prompt as administrator, and type something like:
handle.exe thepwf_
Which should show which process is holding the files locked.
Let me know what you find.
这篇关于我创建的以“dotNetChat_"开头的表格；重启 MySQL 后消失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
