MYSQL DB Repair Commands – Linux Admin, WebDev, Comms & IT

Find DB collation:
SELECT @@character_set_database, @@collation_database;

and for Engine:
SHOW TABLE STATUS\G

Diff Engines are not a prob in same DB.

GRANT ALL PRIVILEGES ON ElectronicsStuff TO stevee@localhost IDENTIFIED BY pword ;

SHOW GRANTS FOR 'stevee'@'localhost';

systemctl restart mysql.service

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

stevee@localhost:/var/www/ElectronicsStuff$ mysqlcheck -c ElectronicsStuff -u stevee -p
Enter password:
ElectronicsStuff.wp_Counterize OK
ElectronicsStuff.wp_Counterize_Countries OK
ElectronicsStuff.wp_Counterize_Keywords OK
ElectronicsStuff.wp_Counterize_Outlinks OK
ElectronicsStuff.wp_Counterize_Pages OK
ElectronicsStuff.wp_Counterize_Referers OK
ElectronicsStuff.wp_Counterize_UserAgents OK
ElectronicsStuff.wp_commentmeta OK
ElectronicsStuff.wp_comments OK
ElectronicsStuff.wp_edd_customermeta OK
ElectronicsStuff.wp_edd_customers OK
ElectronicsStuff.wp_links OK
ElectronicsStuff.wp_options OK
ElectronicsStuff.wp_postmeta OK
ElectronicsStuff.wp_posts OK
ElectronicsStuff.wp_term_relationships OK
ElectronicsStuff.wp_term_taxonomy OK
ElectronicsStuff.wp_termmeta OK
ElectronicsStuff.wp_terms OK
ElectronicsStuff.wp_usermeta OK
ElectronicsStuff.wp_users OK
ElectronicsStuff.wp_xubg_commentmeta OK
ElectronicsStuff.wp_xubg_comments OK
ElectronicsStuff.wp_xubg_links OK
ElectronicsStuff.wp_xubg_options OK
ElectronicsStuff.wp_xubg_postmeta OK
ElectronicsStuff.wp_xubg_posts OK
ElectronicsStuff.wp_xubg_term_relationships OK
ElectronicsStuff.wp_xubg_term_taxonomy OK
ElectronicsStuff.wp_xubg_termmeta OK
ElectronicsStuff.wp_xubg_terms OK
ElectronicsStuff.wp_xubg_usermeta OK
ElectronicsStuff.wp_xubg_users OK
ElectronicsStuff.wpnt_commentmeta OK
ElectronicsStuff.wpnt_comments OK
ElectronicsStuff.wpnt_links OK
ElectronicsStuff.wpnt_options OK
ElectronicsStuff.wpnt_postmeta OK
ElectronicsStuff.wpnt_posts OK
ElectronicsStuff.wpnt_term_relationships OK
ElectronicsStuff.wpnt_term_taxonomy OK
ElectronicsStuff.wpnt_termmeta OK
ElectronicsStuff.wpnt_terms OK
ElectronicsStuff.wpnt_usermeta OK
ElectronicsStuff.wpnt_users OK
ElectronicsStuff.wpnt_wprss_logs OK

stevee@localhost:/var/www/ElectronicsStuff$ mysqlcheck -c -u stevee -p --all-databases
Enter password:
DebianAdmin.wp_xubg_commentmeta OK
DebianAdmin.wp_xubg_comments OK
DebianAdmin.wp_xubg_ewwwio_images OK
DebianAdmin.wp_xubg_ewwwio_queue OK
DebianAdmin.wp_xubg_links OK
DebianAdmin.wp_xubg_options OK
DebianAdmin.wp_xubg_postmeta OK
DebianAdmin.wp_xubg_posts OK
DebianAdmin.wp_xubg_pvc_daily OK
DebianAdmin.wp_xubg_pvc_total OK
DebianAdmin.wp_xubg_snippets OK
DebianAdmin.wp_xubg_term_relationships OK
DebianAdmin.wp_xubg_term_taxonomy OK
DebianAdmin.wp_xubg_termmeta OK
DebianAdmin.wp_xubg_terms OK
DebianAdmin.wp_xubg_usermeta OK
DebianAdmin.wp_xubg_users OK
ElectronicsStuff.wp_Counterize OK
ElectronicsStuff.wp_Counterize_Countries OK
ElectronicsStuff.wp_Counterize_Keywords OK
ElectronicsStuff.wp_Counterize_Outlinks OK
ElectronicsStuff.wp_Counterize_Pages OK
ElectronicsStuff.wp_Counterize_Referers OK
ElectronicsStuff.wp_Counterize_UserAgents OK
ElectronicsStuff.wp_commentmeta OK
ElectronicsStuff.wp_comments OK
ElectronicsStuff.wp_edd_customermeta OK
ElectronicsStuff.wp_edd_customers OK
ElectronicsStuff.wp_links OK
ElectronicsStuff.wp_options OK
ElectronicsStuff.wp_postmeta OK
ElectronicsStuff.wp_posts OK
ElectronicsStuff.wp_term_relationships OK
ElectronicsStuff.wp_term_taxonomy OK
ElectronicsStuff.wp_termmeta OK
ElectronicsStuff.wp_terms OK
ElectronicsStuff.wp_usermeta OK
ElectronicsStuff.wp_users OK
ElectronicsStuff.wp_xubg_commentmeta OK
ElectronicsStuff.wp_xubg_comments OK
ElectronicsStuff.wp_xubg_links OK
ElectronicsStuff.wp_xubg_options OK
ElectronicsStuff.wp_xubg_postmeta OK
ElectronicsStuff.wp_xubg_posts OK
ElectronicsStuff.wp_xubg_term_relationships OK
ElectronicsStuff.wp_xubg_term_taxonomy OK
ElectronicsStuff.wp_xubg_termmeta OK
ElectronicsStuff.wp_xubg_terms OK
ElectronicsStuff.wp_xubg_usermeta OK
ElectronicsStuff.wp_xubg_users OK
ElectronicsStuff.wpnt_commentmeta OK
ElectronicsStuff.wpnt_comments OK
ElectronicsStuff.wpnt_links OK
ElectronicsStuff.wpnt_options OK
ElectronicsStuff.wpnt_postmeta OK
ElectronicsStuff.wpnt_posts OK
ElectronicsStuff.wpnt_term_relationships OK
ElectronicsStuff.wpnt_term_taxonomy OK
ElectronicsStuff.wpnt_termmeta OK
ElectronicsStuff.wpnt_terms OK
ElectronicsStuff.wpnt_usermeta OK
ElectronicsStuff.wpnt_users OK
ElectronicsStuff.wpnt_wprss_logs OK
myphpsite.text OK
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK
mysql.engine_cost OK
mysql.func OK
mysql.general_log OK
mysql.global_grants OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.password_history OK
mysql.plugin OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.replication_asynchronous_connection_failover OK
mysql.replication_asynchronous_connection_failover_managed OK
mysql.replication_group_configuration_version OK
mysql.replication_group_member_actions OK
mysql.role_edges OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
phpmyadmin.pma__bookmark OK
phpmyadmin.pma__central_columns OK
phpmyadmin.pma__column_info OK
phpmyadmin.pma__designer_settings OK
phpmyadmin.pma__export_templates OK
phpmyadmin.pma__favorite OK
phpmyadmin.pma__history OK
phpmyadmin.pma__navigationhiding OK
phpmyadmin.pma__pdf_pages OK
phpmyadmin.pma__recent OK
phpmyadmin.pma__relation OK
phpmyadmin.pma__savedsearches OK
phpmyadmin.pma__table_coords OK
phpmyadmin.pma__table_info OK
phpmyadmin.pma__table_uiprefs OK
phpmyadmin.pma__tracking OK
phpmyadmin.pma__userconfig OK
phpmyadmin.pma__usergroups OK
phpmyadmin.pma__users OK
sys.sys_config OK
text.sometext

stevee@localhost:/var/www/ElectronicsStuff$ mysqlcheck -a ElectronicsStuff -u stevee -p
Enter password:
ElectronicsStuff.wp_Counterize Table is already up to date
ElectronicsStuff.wp_Counterize_Countries Table is already up to date
ElectronicsStuff.wp_Counterize_Keywords Table is already up to date
ElectronicsStuff.wp_Counterize_Outlinks Table is already up to date
ElectronicsStuff.wp_Counterize_Pages Table is already up to date
ElectronicsStuff.wp_Counterize_Referers Table is already up to date
ElectronicsStuff.wp_Counterize_UserAgents Table is already up to date
ElectronicsStuff.wp_commentmeta Table is already up to date
ElectronicsStuff.wp_comments Table is already up to date
ElectronicsStuff.wp_edd_customermeta Table is already up to date
ElectronicsStuff.wp_edd_customers Table is already up to date
ElectronicsStuff.wp_links Table is already up to date
ElectronicsStuff.wp_options Table is already up to date
ElectronicsStuff.wp_postmeta Table is already up to date
ElectronicsStuff.wp_posts Table is already up to date
ElectronicsStuff.wp_term_relationships Table is already up to date
ElectronicsStuff.wp_term_taxonomy Table is already up to date
ElectronicsStuff.wp_termmeta Table is already up to date
ElectronicsStuff.wp_terms Table is already up to date
ElectronicsStuff.wp_usermeta Table is already up to date
ElectronicsStuff.wp_users Table is already up to date
ElectronicsStuff.wp_xubg_commentmeta OK
ElectronicsStuff.wp_xubg_comments OK
ElectronicsStuff.wp_xubg_links OK
ElectronicsStuff.wp_xubg_options OK
ElectronicsStuff.wp_xubg_postmeta OK
ElectronicsStuff.wp_xubg_posts OK
ElectronicsStuff.wp_xubg_term_relationships OK
ElectronicsStuff.wp_xubg_term_taxonomy OK
ElectronicsStuff.wp_xubg_termmeta OK
ElectronicsStuff.wp_xubg_terms OK
ElectronicsStuff.wp_xubg_usermeta OK
ElectronicsStuff.wp_xubg_users OK
ElectronicsStuff.wpnt_commentmeta Table is already up to date
ElectronicsStuff.wpnt_comments Table is already up to date
ElectronicsStuff.wpnt_links Table is already up to date
ElectronicsStuff.wpnt_options Table is already up to date
ElectronicsStuff.wpnt_postmeta Table is already up to date
ElectronicsStuff.wpnt_posts Table is already up to date
ElectronicsStuff.wpnt_term_relationships Table is already up to date
ElectronicsStuff.wpnt_term_taxonomy Table is already up to date
ElectronicsStuff.wpnt_termmeta Table is already up to date
ElectronicsStuff.wpnt_terms Table is already up to date
ElectronicsStuff.wpnt_usermeta Table is already up to date
ElectronicsStuff.wpnt_users Table is already up to date
ElectronicsStuff.wpnt_wprss_logs Table is already up to date

OPTIMISE - shows FAULT:
stevee@localhost:/var/www/ElectronicsStuff$ mysqlcheck -o ElectronicsStuff -u stevee -p
Enter password:
ElectronicsStuff.wp_Counterize OK
ElectronicsStuff.wp_Counterize_Countries OK
ElectronicsStuff.wp_Counterize_Keywords OK
ElectronicsStuff.wp_Counterize_Outlinks OK
ElectronicsStuff.wp_Counterize_Pages OK
ElectronicsStuff.wp_Counterize_Referers OK
ElectronicsStuff.wp_Counterize_UserAgents OK
ElectronicsStuff.wp_commentmeta OK
ElectronicsStuff.wp_comments OK
ElectronicsStuff.wp_edd_customermeta OK
ElectronicsStuff.wp_edd_customers OK
ElectronicsStuff.wp_links OK
ElectronicsStuff.wp_options OK
ElectronicsStuff.wp_postmeta OK
ElectronicsStuff.wp_posts OK
ElectronicsStuff.wp_term_relationships OK
ElectronicsStuff.wp_term_taxonomy OK
ElectronicsStuff.wp_termmeta OK
ElectronicsStuff.wp_terms OK
ElectronicsStuff.wp_usermeta OK
ElectronicsStuff.wp_users OK
ElectronicsStuff.wp_xubg_commentmeta
note : Table does not support optimize, doing recreate + analyze instead
status : OK
ElectronicsStuff.wp_xubg_comments
note : Table does not support optimize, doing recreate + analyze instead
error : Invalid default value for 'comment_date'
status : Operation failed
ElectronicsStuff.wp_xubg_links
note : Table does not support optimize, doing recreate + analyze instead
error : Invalid default value for 'link_updated'
status : Operation failed
ElectronicsStuff.wp_xubg_options
note : Table does not support optimize, doing recreate + analyze instead
status : OK
ElectronicsStuff.wp_xubg_postmeta
note : Table does not support optimize, doing recreate + analyze instead
status : OK
ElectronicsStuff.wp_xubg_posts
note : Table does not support optimize, doing recreate + analyze instead
error : Invalid default value for 'post_date'
status : Operation failed
ElectronicsStuff.wp_xubg_term_relationships
note : Table does not support optimize, doing recreate + analyze instead
status : OK

REPAIR:

stevee@localhost:/var/www/ElectronicsStuff$ mysqlcheck -r ElectronicsStuff -u stevee -p
Enter password:
ElectronicsStuff.wp_Counterize OK
ElectronicsStuff.wp_Counterize_Countries OK
ElectronicsStuff.wp_Counterize_Keywords OK
ElectronicsStuff.wp_Counterize_Outlinks OK
ElectronicsStuff.wp_Counterize_Pages OK
ElectronicsStuff.wp_Counterize_Referers OK
ElectronicsStuff.wp_Counterize_UserAgents OK
ElectronicsStuff.wp_commentmeta OK
ElectronicsStuff.wp_comments OK
ElectronicsStuff.wp_edd_customermeta OK
ElectronicsStuff.wp_edd_customers OK
ElectronicsStuff.wp_links OK
ElectronicsStuff.wp_options OK
ElectronicsStuff.wp_postmeta OK
ElectronicsStuff.wp_posts OK
ElectronicsStuff.wp_term_relationships OK
ElectronicsStuff.wp_term_taxonomy OK
ElectronicsStuff.wp_termmeta OK
ElectronicsStuff.wp_terms OK
ElectronicsStuff.wp_usermeta OK
ElectronicsStuff.wp_users OK
ElectronicsStuff.wp_xubg_commentmeta
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wp_xubg_comments
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wp_xubg_links
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wp_xubg_options
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wp_xubg_postmeta
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wp_xubg_posts
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wp_xubg_term_relationships
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wp_xubg_term_taxonomy
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wp_xubg_termmeta
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wp_xubg_terms
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wp_xubg_usermeta
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wp_xubg_users
note : The storage engine for the table doesn't support repair
ElectronicsStuff.wpnt_commentmeta OK
ElectronicsStuff.wpnt_comments OK
ElectronicsStuff.wpnt_links OK
ElectronicsStuff.wpnt_options OK
ElectronicsStuff.wpnt_postmeta OK
ElectronicsStuff.wpnt_posts OK
ElectronicsStuff.wpnt_term_relationships OK
ElectronicsStuff.wpnt_term_taxonomy OK
ElectronicsStuff.wpnt_termmeta OK
ElectronicsStuff.wpnt_terms OK
ElectronicsStuff.wpnt_usermeta OK
ElectronicsStuff.wpnt_users OK
ElectronicsStuff.wpnt_wprss_logs OK

ALL switches:

-A, –all-databases Consider all the databases
-a, –analyze Analyze tables
-1, –all-in-1 Use one query per database with tables listed in a comma separated way
–auto-repair Repair the table automatically it if is corrupted
-c, –check Check table errors
-C, –check-only-changed Check tables that are changed since last check
-g, –check-upgrade Check for version dependent changes in the tables
-B, –databases Check more than one databases
-F, –fast Check tables that are not closed properly
–fix-db-names Fix DB names
–fix-table-names Fix table names
-f, –force Continue even when there is an error
-e, –extended Perform extended check on a table. This will take a long time to execute.
-m, –medium-check Faster than extended check option, but does most checks
-o, –optimize Optimize tables
-q, –quick Faster than medium check option
-r, –repair Fix the table corruption

Missing comma before start of a new alter operation. (near "DEFAULT CHARSET" at position 47)

OK, let’s try this. When displaying the database in phpMyAdmin, you will see a list of tables. One of them is wp_posts. Click on that table, and then click on the “Operations” tab. In the Table Options box, make sure the collation is set to utf8mb4_unicode_520_ci and there is a check in the “Change all column collations” checkbox, then click the “Go” button.