grissom Posted September 12, 2009 Share Posted September 12, 2009 I have a very large mysql table, in fact it contains around 250,000 rows each of about 20 fields. After having extracted the most recent data that I wanted out of it, I decided to rename the table from 'results' to 'results_archive' To do this, I used MySql Query Browser, used the "edit table" option and changed the table name. Since then (it was about an hour ago !!) the table seems locked, and I cannot access the schema in which it resides through MySql Query Browser. In short - should table of this size take over an hour just to rename ? At what point in time should I start getting worried that something has gone wrong and if it has, how do I repair it. My engine is Innodb. Very many thanks Quote Link to comment https://forums.phpfreaks.com/topic/174005-solved-taking-a-long-time-is-this-normal/ Share on other sites More sharing options...
gizmola Posted September 12, 2009 Share Posted September 12, 2009 Yes, something is wrong in all probability. I would suggest you restart mysql and see if that frees things up. SQL statements are the best approach to making DDL changes like table renaming. You don't know what assumptions might be built into a tool, and it's possible that the tool created a lock somewhere and this is what caused your issue. FWIW, I think the safest and best way to do what you did is: CREATE TABLE results_archive AS SELECT * FROM results when results_archive is done you can then either drop table results or truncate table results if you want to empty out the data and begin to use it again. Quote Link to comment https://forums.phpfreaks.com/topic/174005-solved-taking-a-long-time-is-this-normal/#findComment-917255 Share on other sites More sharing options...
grissom Posted September 12, 2009 Author Share Posted September 12, 2009 Thanks gizmola. I guess I have to log on to the server to re-start mysql ? My front end screens are in CPanel, can I be cheeky and ask if you happen to know what bit of CPanel I can use to restart mysql ? many thanks Quote Link to comment https://forums.phpfreaks.com/topic/174005-solved-taking-a-long-time-is-this-normal/#findComment-917259 Share on other sites More sharing options...
gizmola Posted September 12, 2009 Share Posted September 12, 2009 Yes, you have to restart the mysql process under linux. There's a variety of ways to do it depending on the distro, from getting to a shell, finding the parent process and sending a kill, to doing service mysqld stop in RHEL and Centos distros. To restart with CPanel, you need to use the WHM interface. At the bottom there's a restart services section, and one of them is Restart SQL Server, which willl restart the MySQL server. Quote Link to comment https://forums.phpfreaks.com/topic/174005-solved-taking-a-long-time-is-this-normal/#findComment-917271 Share on other sites More sharing options...
grissom Posted September 12, 2009 Author Share Posted September 12, 2009 Brilliant - it's all working perfectly npw - A BIG THANK YOU !! Quote Link to comment https://forums.phpfreaks.com/topic/174005-solved-taking-a-long-time-is-this-normal/#findComment-917347 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.