Index MySQL Database / Error Querying Data

Issue

If your installation of our software is a few years old and the MySQL database has hundreds of projects in it, you might notice a slowdown of performance the requires you to index the MySQL database.

You might also have received an error message similar to the following:

Error querying data: Got error 28 from storage engine. Solution >

You may also be getting getting one or both of the following errors:

• Error querying data: Automation error. Operation is not allowed when the object is closed.

• Error executing data request: Automation Error. Operation is not allowed when the object is closed.

Solution

To remedy this issue, you'll need to index the MySQL database and streamline the performance.

Automated Steps:

1. Download the MySQL index linked below.

2. Double-click the index.sql.zip file you just downloaded, which will unzip it.

3. Drag the unzipped index.sql file directly onto your C: drive.

4. Determine your version of MySQL Server by navigating to each of the following folder paths on your computer:

• C:\Program Files\MySQL
• C:\Program Files (x86)\MySQL

In one of those folder paths, you should see one of the following files:

• MySQL Server 5.1
• MySQL Server 5.5

Take note of which version of MySQL Server you have, as well as the folder path where you found it.

The MySQL Server file could be in either of the folder paths listed above, which is why you'll need to check both locations.

5. Open your Start menu and type cmd.exe into the search box.

 

 

 

 

Select the Command Prompt entry that appears at the top of the Start menu.

6. Enter one of the following strings of text at the Command prompt, depending on your version of MySQL Server and the location where you found it in Step 4:

MySQL Server 5.1 in C:\Program Files:

cd c:\program files\mysql\mysql server 5.1\bin

mysql -uroot -plandfx landfx < c:/index.sql

MySQL Server 5.1 in C:\Program Files (x86):

cd c:\program files (x86)\mysql\mysql server 5.1\bin

mysql -uroot -plandfx landfx < c:/index.sql

MySQL Server 5.5 in C:\Program Files :

cd c:\program files\mysql\mysql server 5.5\bin

mysql -uroot -plandfx landfx < c:/index.sql

MySQL Server 5.5 in C:\Program Files (x86):

cd c:\program files (x86)\mysql\mysql server 5.5\bin

mysql -uroot -plandfx landfx < c:/index.sql

This step will index your MySQL database for smoother performance.

7. Enter:

use landfx;

Then enter:

describe project_data;

The results should look like the example to the right.

 

Note the MUL listings in the Key column, which indicate that the index was created successfully.

Did you see a message that 'describe' is not recognized as an internal or external command at this point?

 

 

If so, paste the following two lines of text at the Command prompt, pressing Enter after each line:

mysql -uroot -plandfx landfx

describe project_data;

Alternate Manual Steps:

Open the MySQL Command line and enter your password. Then enter the following (pressing the Enter key after each line):

use landfx;

create index project_number on project_data(project_number);

create index data_group on project_data(data_group);

create index data_type on project_data(data_type);

create index data_code on project_data(data_code);

create index data_code2 on project_data(data_code2);

create index data_code3 on project_data(data_code3);

create index data_name1 on project_data(data_name1);

create index data_name2 on project_data(data_name2);

create index data_name3 on project_data(data_name3);

create index data_attrib on project_data(data_attrib);

create index data_detail on project_data(data_detail);

create index data_remarks on project_data(data_remarks(100));