How does Mysql delete a temporary table starting with "# SQL,

How does Mysql delete a temporary table starting with "# SQL,

Author: skate
Time: 2014/09/28

 

How does Mysql delete a temporary table starting with "# SQL -"?

 

Phenomenon: after re-indexing, the disk space of the Mysql server is almost full.

Run the following command to re-create the index:

Mysql> alter table skatetab add unique index (id, uid), drop primary key, add primary key (uid, id );

During index reconstruction, due to insufficient space, Mysql server reboot was found to have less space than 100 GB after restart. So I checked which directory occupied the 100 GB and found it in the data directory.
There are many SQL-*. ibd temporary files and # SQL-*. frm files with the same file name. Now that you know it is a temporary table, delete it. You must not delete it directly through rm, because the dictionary information and Undo letter are saved in ibdata.
After the database is restarted, an error is reported.

 

Deletion method:

During the alter table process, if Mysql suddenly crash, some intermediate tables will exist in the data directory. These intermediate tables are temporary tables starting with "# SQL, in your data directory, you will see
# SQL -*. ibd and the corresponding # SQL -*. frm, if # SQL -*. ibd and # SQL -*. if both frm files exist in the data directory, you can directly drop the table, similar:

Mysql> drop table '# mysql50 # sql-928_76f7 ';

Prefix "# mysql50 #" indicates that Mysql ignores the secure encoding of file names. This prefix is introduced in Mysql5.1.

 

Because # SQL -*. ibd and # SQL -*. both frm files are saved, so you can directly drop them and reclaim more than 100 GB of disk space, as shown below:

Mysql> drop table '# mysql50 # sql-928_76f7 ';
Query OK, 0 rows affected (16.28 sec)

Note: If the data directory contains only # SQL-*. ibd, but not # SQL-*. frm, special processing is required.
1. Create a table structure (including the same columns and indexes) that is the same as the table to be deleted in another data schema)

Mysql> create database test
Mysql> create table test. tmp like skatetab; // only copy the structure and index of the table, without copying data

2. Copy the. frm file of the newly created temporary table to the data directory to be deleted, and modify the file name that is the same as "# SQL-*. ibd ".

Shell> cp test/tmp. frm # sql-928_76f7.frm

3. confirm that the # SQL-*. ibd and # SQL-*. frm files are saved and then drop directly, as shown below:
 
Mysql> drop table '# mysql50 # sql-928_76f7 ';

Reference: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html

------ End ------

How to completely delete mysql

Applicable only to Windows
-- Delete the MySQL program in the control panel
-- Then delete the MySQL folder in drive C.
-- In the MySQL Data zone of the user, I am in: D: \ Documents ents and Settings \ All Users \ Application Data \ MySQL
-- The last step is to delete mysql from the registry.
Open the Registry (enter regedit in cmd), open it, and delete the file in HEKY_CURRENT_USER \ Software \ Microsoft \ Windows \ CurrentVersion \ Explorer \ MenuOrder \ Programs \ MySQL.

How to completely delete mysql

Applicable only to Windows
-- Delete the MySQL program in the control panel
-- Then delete the MySQL folder in drive C.
-- In the MySQL Data zone of the user, I am in: D: \ Documents ents and Settings \ All Users \ Application Data \ MySQL
-- The last step is to delete mysql from the registry.
Open the Registry (enter regedit in cmd), open it, and delete the file in HEKY_CURRENT_USER \ Software \ Microsoft \ Windows \ CurrentVersion \ Explorer \ MenuOrder \ Programs \ MySQL.

-------------------------------------------------------------------------
Let's talk about how to delete Oracle
I. Linux platform
It is very easy to uninstall Oracle on Linux, that is, to delete all files and folders under the Oracle installation directory.
Ii. Windows Platform
In fact, I mainly want to explain how to thoroughly uninstall Oracle on Windows.
Since it is quite troublesome to uninstall Oracle in Windows, if it cannot be fully uninstalled, it may affect future re-installation! The general method of uninstallation is to run the built-in uninstallation program of Oracle. Unfortunately, I cannot unmount it completely during uninstallation. When I install Oracle again, there will be some inexplicable problems, in addition, this method is troublesome and slow. Next I will introduce a method that is faster and can thoroughly uninstall Oracle.
1. Disable all oracle services. You can disable it in the windows Service Manager;

2. Open the Registry: regedit open path:
HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \
Delete all service names starting with oracle in this path. This key identifies various services registered by Oracle in windows!
3. Open the registry and find the path:
HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE
Delete the oracle directory where the software installation information of the Oracle database is registered.
4. Delete the registered oracle Event Log and open the Registry
HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \ Eventlog \ Application
Delete all items starting with oracle in the registry.
5. Delete the oracle content in the environment variable path.
Right-click my computer> Properties> advanced> environment variable> PATH variable.
Delete the content of Oracle in this value. Note: A Bunch Of Operating System directories are recorded in the path. In windows, directories are separated by semicolons (;). Note the following when deleting directories.
Suggestion: When deleting the Oracle value in the path environment variable, copy all the values to the text editor, find the corresponding Oracle value, and then copy the modified string, paste it to the path environment variable, which is relatively safe.
6. Restart the operating system.
Above 1 ~ After completing the five steps, restart the operating system.
7. After the operating system is restarted, all Oracle-related processes will not be loaded. Delete all data under Oracle_Home. (Oracle_Home refers to the installation directory of the Oracle program)
8. Delete C:... the remaining full text>