I am getting SICK of this

[UKBusinessLive]

Marco the error was posted

jtablesession::Store Failed
DB function failed with error number 145
Table './webkinzc_jo151/jos_session' is marked as crashed and should be repaired SQL=INSERT INTO `jos_session` ( `session_id`,`time`,`username`,`gid`,`guest`,`client_id` ) VALUES ( 'fd773853ae5aa0aca6aa7c22c79dcbbd','1230945305','','0','1','0' )

How to repair all MySQL Databases on a cPanel Server

There can be different reasons why one wants to repair a MySQL database. Database corruption maybe due to a lack of disk space or an unexpected server shutdown that caused the database to go bad. Queries that worked before suddenly stopped working and overall the database is not behaving anymore as expected. This post is not a full database troubleshooting guide, but rather give you a hand on doing a MySQL repair on all databases on a cPanel/WHM server (or any other server running MySQL).

The situation: Your server crashed hard and when it is back up the MySQL side of the house is not working anymore. You do your normal troubleshooting. If all other troubleshooting and verifications are done, you come to the conclusion that you will need to run a repair as the next step. A full database restore from a (hopefully existing) good database backup would be your last choice.

Repairing one MySQL database is fairly easy, but how do you do this for an entire server holding20, 30, 50 or over 100 databases? You will need SSH access as the root user to the server running MySQL. So, log in as the root user and then do the following to repair your MySQL databases:

Option #1

Check if you any need DB repair:

myisamchk --check /var/lib/mysql/*/*.MYI

Then try a 'safe-recover' as the first step:

myisamchk --safe-recover /var/lib/mysql/*/*.MYI

If the ‘safe-recover’ does not work, run a full recover:

myisamchk --recover /var/lib/mysql/*/*.MYI

Then use the 'force' flag to get things back to normal:

myisamchk --safe-recover --extend-check --force /var/lib/mysql/*/*.MYI

or:

myisamchk --recover --extend-check --force /var/lib/mysql/*/*.MYI

Option #2

Use the mysqlcheck tool to repair your databases.

mysqlcheck --all-databases -r #repair

mysqlcheck --all-databases -a #analyze

mysqlcheck --all-databases -o #optimize

Option #3

1) Through cPanel:

Log in to your cPanel and go to MySQL Databases. In the section Modify Databases, select a database from the drop-down box next to Repair DB: and then click the Repair DB button.

If the database engine used by your database supports repair (for example, MyISAM supports it), you will see a list of the repaired tables within the database.

If your database engine does not support repair (e.g. InnoDB database engine), you will get the following error message:

note : The storage engine for the table doesn't support repair

2) Through phpMyAdmin:

Open your phpMyAdmin tool and select the database whose tables you wish to repair.

A list with all the database's tables will appear. Tick the tables you wish to repair, or simply click [Check All] to select all tables.

Click the box [With selected:] and choose Repair table. This will execute the REPAIR TABLE SQL query on the selected tables and they will be repaired.

There you have it. By using one of these threee options you should be able to repair and recover your databases. Please be advised that you should backup critical databases before running any of the commands above. If something goes wrong you can revert back and try again.

In general the idea is to get people feeling confident enough to be able to work out a solution to a problem rather than worrying that they have a major serious issue.

Hope that helps

[Marco van Herwaarden]

@UKBusinessLive

Post #1:

I keep getting Database errors!

No information here for even guessing what is wrong or what action (if any) should be taken.

Post #2:
You tell him to repair his database.

Why???? Based on what information? Do you already know what is wrong? Don't give advise if you have no clue yet. Ask for more info until you can decide what the problem is. Depending on the issue, running a repair can even corrupt a database more, it is not a 100% safe command.

Post #3:
Some tell him he should disable all his modifications.

Again: Why? We still don't know what is wrong or how we could troubleshoot it.

Post #4:

This is the first time the error is posted: Now we know the session table is corrupted. Now we can provide a solution. And you again posted that he should repair his database. A reasonable answer, but in this specific case wrong.

Post #10:
I make a reply.
- Questioning why people are giving suggestions without knowing what is wrong.
- And explaining how this error should be repaired and why repairing the database will not solve the problem.

Post 11:
You again tell him to repair the database. I just explained why that would not solve anything.

[UKBusinessLive]

Marco,

This is the error he had

jtablesession::Store Failed
DB function failed with error number 145
Table './webkinzc_jo151/jos_session' is marked as crashed and should be repaired SQL=INSERT INTO `jos_session` ( `session_id`,`time`,`username`,`gid`,`guest`,`clie nt_id` ) VALUES ( 'fd773853ae5aa0aca6aa7c22c79dcbbd','1230945305','' ,'0','1','0' )

The thing is that advice is not from me but from a guy who has a large Hosting company and deals with Mysql problems almost daily.

There are so many different ways to solve mysql errors that it would be idiot of me to simply guess, let alone putting another guys site at risk because of what i said.

The bonus was he repaired his database and his site was up minutes later.

If you read my post i also said that he needed to find the cause of the problem to ensure that it didn't happen again.

So based on the above error and a guy thats very confident in what he does for a living, the end result was that the original poster managed to get his site up and running the same night.

I can not tell him what caused the problem but i can tell him the way to solve it, and if i don't know then there are people out there that do.

I'm sorry if i've trodden on your toes here, I know you a technical kinda guy, and very clever too, But the guy was deperate to get his site up and running and with the information i gathered that night, a repair was the best option at that time.

[Marco van Herwaarden]

The thing is that advice is not from me but from a guy who has a large Hosting company and deals with Mysql problems almost daily.

And did he also know this is a HEAP table before giving that answer? An expert can only advise based on the information he gets. So gathering all needed info first is essential.

The bonus was he repaired his database and his site was up minutes later.

I think it is a misconception that repairing a database is always a good thing to do. I have seen many cases where repairing a database was causing corruption. If it is needed, you should run it, but if not needed i would not suggest to run it as it can also make things worse.

I'm sorry if i've trodden on your toes here, I know you a technical kinda guy, and very clever too, But the guy was deperate to get his site up and running and with the information i gathered that night, a repair was the best option at that time.

Don't take it too personal. I know i addressed this rant at you, but it could have been anyone. But it is true that it often irritates me when i see people telling members to run certain tasks (often risky) without analysing the problem first. I dont mind if they do so on their own forums, but i don't like it when i see it happen to boards of others.

Yes i do have more technical knowledge then the average (i am for sure not claiming all before someone thinks i feel myself above others) user. But my "complaint" is not really about the technical solutions suggested, but the lack of analysing before making asumptions.

PS You now have explained that you did more analysing then you posted. It does help a lot if you would have posted in your 2nd post: I checked your site, i found this detailed DB-error and the solution is...

Like this you also prevent the next user with a similar problem (ie. "DB-error") thining his error is the same and the solution will be the same.

[UKBusinessLive]

Well we're all on the same side here, and i agree with you 100% so its cool