|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
I'm trying to optimize tables on a new server.
I have root access via phpMyAdmin, but when I try to optimize the tables, I get this:
The handler for the table doesn't support check/repair
The mySQL version seems to be a bit outdated: 3.23.46
Is this a permissions thing I'm missing?
|
|
|
|
Joined: Apr 2001
Posts: 3,266
Member
|
Member
Joined: Apr 2001
Posts: 3,266 |
Do you have shell access? You can go in directly and try it. However I do beleive that does look like the user does not have all the permissions we normally see.
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
Same thing: <br />mysql> OPTIMIZE TABLE `w3t_Users; <br />+-----------------+----------+----------+--------------------------------------------------------+ <br />| Table | Op | Msg_type | Msg_text | <br />+-----------------+----------+----------+--------------------------------------------------------+ <br />| forum.w3t_Users | optimize | error | The handler for the table doesn't support check/repair | <br />+-----------------+----------+----------+--------------------------------------------------------+ <br /> <shrug> Thought it would make my upgrade go faster - it's coming from 5.5.1 - but I might just have to wing it.
|
|
|
|
Joined: Apr 2001
Posts: 3,266
Member
|
Member
Joined: Apr 2001
Posts: 3,266 |
Have you tried myisamchk that should work when the normal ones dont
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
I'm not familiar with their use. do you just type myisamchk tablename at the mysql prompt?
|
|
|
|
Joined: Apr 2001
Posts: 3,266
Member
|
Member
Joined: Apr 2001
Posts: 3,266 |
You can. There are some switches you can use
Like -a is for analize
|
|
|
|
Joined: Apr 2001
Posts: 3,266
Member
|
Member
Joined: Apr 2001
Posts: 3,266 |
Okay you can type
myisamchk tbl_name to check the tables
Then only on the tables that need repair you can type this
myisamchk -r -q tbl_name
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
Thanks. What about optimizing?
I like to do that with upgrades/server moves. Tends to reduce the table size.
|
|
|
|
Joined: Apr 2001
Posts: 3,266
Member
|
Member
Joined: Apr 2001
Posts: 3,266 |
That command should also optimize the table however there are some more switches one can use. I already stated the -a for analyze
This is right off the mysql site
4.5.6.10 Table Optimization
To coalesce fragmented records and eliminate wasted space resulting from deleting or updating records, run myisamchk in recovery mode:
shell> myisamchk -r tbl_name
You can optimize a table in the same way using the SQL OPTIMIZE TABLE statement. OPTIMIZE TABLE does a repair of the table and a key analysis, and also sorts the index tree to give faster key lookups. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use OPTIMIZE TABLE. See section 4.6.1 OPTIMIZE TABLE Syntax.
myisamchk also has a number of other options you can use to improve the performance of a table:
* -S, --sort-index * -R index_num, --sort-records=index_num * -a, --analyze
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
Just a follow up to what solved this. Didn't catch it before, and not sure why, but the tables that wouldn't optimize where ISAM instead of MyISAM. Once I changed the tables to that type - all was fine.
|
|
|
Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.
Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
|
|
Posts: 1,157
Joined: July 2001
|
|
Forums63
Topics37,573
Posts293,925
Members13,849
|
Most Online5,166 Sep 15th, 2019
|
|
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
|
|
|
|