[SOLVED] TAKING A LONG TIME : ( IS THIS NORMAL ?

[grissom]

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

[gizmola]

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.

[grissom]

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

[gizmola]

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.

[grissom]

Brilliant - it's all working perfectly npw - A BIG THANK YOU !!