Change MySQL Database to InnoDB?

[Bashed]

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.

| 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]

If they're MyISAM tables the following can be used: MySQL :: MySQL 8.0 Reference Manual :: 7.6.4 MyISAM Table Optimization

To convert them you can use the following: MySQL :: MySQL 8.0 Reference Manual :: 15.6.1.5 Converting Tables from MyISAM to InnoDB