Solved

Can't write indexes to table

Posted on 2014-10-06
6
678 Views
Last Modified: 2014-10-06
I'm using SQLyog and attempting to add an index to a table that consists of over 250,000,000 rows.

When I run this code:

ALTER TABLE Verizon ADD INDEX ( geo_coords_0 );

It runs for a little bit, but then I get this error:

Error Code: 1878
Temporary file write failure
(205999 ms taken)

Not sure how to proceed. Perhaps there's a timeout setting that I need to adjust? What do you think?

Thanks!
0
Comment
Question by:brucegust
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 500 total points
ID: 40364607
I think you need more space on your server. How big is this table and how much free space does the MySQL database have available for its use?

Edit: Doing some guesstimates I estimate that each index needs between 1 to 2 GB of space for 250 million rows
0
 

Author Comment

by:brucegust
ID: 40364619
283,000,000 rows and I don't know about free space, although I'm thinking in light of the company I'm working for, there should be plenty of room.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40364635
"Temporary file write failure" is what is says - an inability to write. That suggests either permissions or space to me. I cannot see it as being permissions as the MySQL database rarely has permission problems once it is up and running. That suggests space to me.

What OS are you running under?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:brucegust
ID: 40364640
Beverley, I think I've figured out the problem. I ran the exact same command, as far as adding an index, on a table with the same structure, but with fewer rows (500,000,000). It ran no problem.

That being the case, I think I'm going to create a new table with the indexes already in place and then move records over to that new table from the other one incrementally.

Thanks for your input!
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40364655
"Alter table" creates a copy of the table in the same folder as the original table. It means that for a certain period you will need as much free space as the original table. For nearly 300 million rows I am guessing you will need a few GB spare (35GB or thereabouts).

Creating a new table with indexes may not solve the problem unless you delete records from one table when you have safely moved them to the other table. Look at the filesize for the current table and then compare that with the amount of free space available.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40364665
Hi,

I just noticed that you accepted the solution whilst I was typing my previous reply. Good luck with it all and thanks for the points.
0

Featured Post

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question