Solution and reflection on 1067 errors during MySql startup on windows

I think there may be more than one reason for this problem, so I have collected some online friends to solve this problem for your reference. I hope you can help me. I,

1. Open the my. ini file, find the line default-storage-engine = InnoDB, and change it to default-storage-engine = MyISAM.

2. Delete ib_logfile0 and ib_logfile1 in the Data directory under the MySQL installation directory.
3. Find the specified InfoDB directory when configuring the MySQL server and delete ibdata1

According to the my. ini file:

# *** INNODB Specific options ***
Innodb_data_home_dir = "D :/".
4. Restart MySQL Service 

Based on my own practices, the problem can be solved in step 3.

 

II,

 Err file content:

0904179:02:55InnoDB: Error: unable to create temporary file; errno: 2
090417At 9:02:55 [ERROR] Plugin 'innodb' init function returned error.
0904179:02:55 [ERROR] Plugin 'innodb' registry as a storage engine failed.
090417At 9:02:55 [ERROR] Unknown/unsupported table type: INNODB
0904179:02:55 [ERROR] Aborting

0904179:02:55 [Warning] Forcing shutdown of 1 plugins
0904179:02:55 [Note] MySQL: Shutdown complete

    After Google, I found out the cause of the failure: MySQL does not automatically initialize tmpdir (temporary file directory) during installation. Therefore, add the following content to the configuration file my. ini:

   [Mysqld]

  # Self-specified temporary file directory

  Tmpdir = "D:/MySQL Server 5.1/Temp"

   Starting MySQL again is normal. Some temporary files *. tmp are generated in the temp folder.

   In the end, I still have some questions: If tmpdir is not initialized, why is it not initialized during my first installation?

 

III,

       In the past, Windows Service of mysql5.0.16 was successfully installed manually. When I installed the service of 5.0.83 last night, a 1067 error was always prompted. Some people on the Internet say that I can put my. ini in c: \ windows, but the problem still persists. My solution is to replace -- defaults-file in the parameter with -- defaults-extra-file, as shown below:

 

       Mysqld -- install MySQL -- defaults-file = E:/mysql-5.0.83-win32/my. ini

       Change

       Mysqld -- install MySQL -- defaults-extra-file = E:/mysql-5.0.83-win32/my. ini

 

       Execute mysqld -- verbose -- help to view the usage of mysqld. The two parameters are described here.

 

 

 

   According to my own practice, the most likely cause of this problem is the data table storage engine (in short, the storage engine refers to the table type, that is, the table
Type

. MySQL database supports multiple data table storage engines. You can run the show engines command to view the data table storage engines. You can also view the data in the MySQL configuration file my. ini (MyISAM by default ):

# The default storage engine that will be used when create new tables when
Default-storage-engine = MyISAM.

 

   Common storage engines include InnoDB, which has multiple advantages. InnoDB provides MySQL tables with transaction, rollback, crash repair capabilities, and multi-version concurrency control. at the same time, InnoDB is the first MySQL engine to provide foreign key constraints, and the InnoDB Storage engine's transaction processing capabilities are incomparable to other MySQL storage engines. 

    Differences between InnoDB and MyISAM:

  The tables stored with the InnoDB Storage engine store the table structure. FRM is separated from the stored table data file (ibdata1). You can modify the File Location of the stored table data by modifying my. set the INI file by yourself:

 # *** InnoDB specific options ***

Innodb_data_home_dir = "D :/"

  Tables stored with the MyISAM storage engine include three files :. FRM (storage table structure) file ,. MYD (abbreviation of myoudata, storing table data) file ,. myi (the abbreviation of myindex, the index of the storage table). These three files are stored in the directory where data is stored during MySQL database installation, such as F: \ programdata \ mysql \ MySQL Server 5.1 \ data \ databasename.

  In addition, by solving this problem, I realized the importance of viewing error logs. When there is a problem, we should first think of viewing error logs, instead of worrying about Google or Baidu, we can get the cause of the problem from the error log and prescribe the right medicine to get rid of the disease. MySQL Error Log (. Err) is located at F: \ programdata \ mysql
In the Server 5.1 \ data directory, the file name is generally your computer name.