Bruce Gust
asked on
Can't write indexes to table
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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?
What OS are you running under?
ASKER
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!
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!
"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.
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.
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.
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.
ASKER