Change MySQL Database to InnoDB?

Bashed

Well-Known Member
Dec 18, 2013
146
4
68
cPanel Access Level
Root Administrator
Customer is using old MyISAM format for his database and it's been locking up, crashing his site daily. Can someone please explain how I can go about doing this easily, safely?

cPanel support said the below:

After further review, I find the optimize command in PHPMyAdmin is taking too long to the point of reaching the cPanel service timeout. This timeout isn't configurable.

[2020-06-16 11:06:06 -0400] info [cpaneld] Internal Server Error: "POST /cpsess2787875175/3rdparty/phpMyAdmin/db_structure.php HTTP/1.1" 500 The subprocess (php) failed to send content during the allowed timeframe.

Optimizing tables with a lot of records can take a while sometimes so I would recommend performing the optimize in CLI if possible. I notice that this table is currently locked.

Code:
| 5673387 | user_siteuser         | localhost | user_sitedata        | Query   | 9465 | User sleep                      | select distinct entity_id from properties inner join entities on entity_id = property_entity_id wher | 0.000    |
| 5673399 | user_siteuser         | localhost | user_sitedata        | Query   | 9465 | Waiting for table level lock    | update properties set property_value = '4797779' where property_entity_id = 14305 and property_name  | 0.000    |
| 5673444 | user_siteuser         | localhost | user_sitedata        | Query   | 9465 | Waiting for table level lock    | select * from properties where property_entity_id = 119311 order by property_order                   | 0.000    |
| 5673446 | user_siteuser         | localhost | user_sitedata        | Query   | 9465 | Waiting for table level lock    | select * from properties where property_entity_id = 14305 order by property_order                    | 0.000    |
| 5673450 | user_siteuser         | localhost | user_sitedata        | Query   | 9465 | Waiting for table level lock    | select property_entity_id from properties where property_name = 'Template Name' and property_value = | 0.000    |
| 5673703 | user_siteuser         | localhost | user_sitedata        | Query   | 9464 | Waiting for table level lock    | select property_entity_id from properties where property_name = 'Template Name' and property_value = | 0.000    |
| 5673718 | user_siteuser         | localhost | user_sitedata        | Query   | 9464 | Waiting for table level lock    | select property_entity_id from properties where property_name = 'Template Name' and property_value = | 0.000    |
| 5675542 | user_siteuser         | localhost | user_sitedata        | Query   | 9441 | Waiting for table level lock    | select property_entity_id from properties where property_name = 'Template Name' and property_value = | 0.000    |
| 5675617 | user_siteuser         | localhost | user_sitedata        | Query   | 9440 | Waiting for table level lock    | select property_entity_id from properties where property_name = 'Template Name' and property_value = | 0.000    |
| 5676331 | user_siteuser         | localhost | user_sitedata        | Query   | 9424 | Waiting for table level lock    | select property_entity_id from properties where property_name = 'Template Name' and property_value = | 0.000    |
| 5676627 | user_siteuser         | localhost | user_sitedata        | Query   | 9414 | Waiting for table level lock    | select property_entity_id from properties where property_name = 'Template Name' and property_value = | 0.000    |
| 5678398 | user_siteuser         | localhost | user_sitedata        | Query   | 9393 | Waiting for table level lock    | select property_entity_id from properties where property_name = 'Template Name' and property_value = | 0.000    |
| 5678579 | user_siteuser         | localhost | user_sitedata        | Query   | 9388 | Waiting for table level lock    | select property_entity_id from properties where property_name = 'Template Name' and property_value = | 0.000    |
It has been locked for a very long time also. Looks like a select was performed and it locked the table for a really long time.

Table level locks are a common thing for MyISAM tables. It may be best to convert this database and table to InnoDB if the database structure supports it.
 

cPanelLauren

Product Owner
Staff member
Nov 14, 2017
13,296
1,271
313
Houston