How to repair a MySQL database?

Hello! Good to see you.

This is going to be a very important article because we are about to discuss  “How to repair a MySQL database?”

Why repair MySQL Database?

Databases can become corrupted for any number of reasons, from software defects to hardware issues.

If this occurs, you can try to repair the database. Wondering how to do it? Just follow this article.

REPAIRING MYSQL DATABASES:

Let’s see how to repair MySQL database through Linux and Windows command line.

Repair MySQL Database through Windows command line.

 

  • Log in to your Windows VPS using Remote Desktop.
  • Go to Start and locate the Command Prompt. Right-click Command Prompt and select Run as Administrator option.
  • At windows command prompt navigate to MySQL bin directory as follows.
    cd C:\Program Files\MySQL\MySQL Server 5.5\bin  //  Replace your MySQL bin directory path here.
  • Run the following command to start the MySQL prompt.
    mysql -u root -p
  • Enter MySQL root password when you are being asked.
  • You will see MySQL prompt appearing. Now, to display all databases, type following command at MySQL prompt:
    show databases;
  • Enter the following command to repair MySQL database.
    mysqlcheck -r [database] // Replace database name with your database name.

Repair MySQL Database through Linux Terminal.

 

  • Login to your Linux VPS using Secure Shell (SSH) and connect to MySQL from the command line.
    mysql -uUsername -p // Replace Username with your username.
  • Enter MySQL user password and hit Enter. You will see MySQL prompt appearing. Now, in order to display all databases, type following command at MySQL prompt.
    show databases;
  • mysqlcheck enables you to check databases without stopping the entire MySQL service. -r argument is used to repair the corrupted tables. mysqlcheck utility efficiently works on both MyISAM and InnoDB database engines. Enter the following command to repair MySQL database.
    mysqlcheck -r [database] // Replace database name with your database name.

 

So, we covered this issue for both Windows and Linux.

Lastly please check our other articles on various topics, that we tried to address.

Thank You.

How to install WordPress manually through Cpanel?

Hello Everyone.

This article is a step by step guide to install WordPress manually on your site through Cpanel.

Now you might ask, Why would you install it manually? when I can use the auto installer. Offcourse auto-installer is a great feature which reduces our effort.

These installers work great in many cases, but often stuff your site full of unwanted plugins and themes. In addition, these automated tools have a reputation for timing out or being completely unavailable at times, which can be frustrating if you’re on a deadline.

Also by doing manual work you get a better understanding of the system.

So, let’s start.

Wp manual installation: Steps.

Step 1: Download WordPress.

To be able to use WordPress CMS, you would need to download it from the official WordPress site. (WordPress.org)

Step 2: Upload WordPress.

Upload the downloaded package to your hosting account. This can be done in the following three ways:

  • Uploading via File manager. Within the File Manager, locate and navigate to that directory. From the toolbar of the cPanel, select “Upload” and browse for the file you downloaded from WordPress.org.
  • Uploading via FTP (file transfer protocol).
  • Uploading SSH (secure socket shell).
Step 3: MySQL DataBase. 

Create a MySQL database and user. For storing the data you will need to create a database which can be done using the below procedure.

  • Login to your Cpanel.
  • Under the database section, select the MySQL database wizard.
  • Create a Database and enter the database name. Click Next Step.
  • Create Database Users and enter the username and password. Click Create User
Step 4: Connect WP to DataBase. 

Now is the time to connect the database with WordPress. In order to do that fill up the details form step 3 in WordPress too.

  • On starting WordPress it will ask for important details to connect the database.
  • Enter the database username.
  • Enter the database password.
  • Choose the Database Host. You can get this info from your web host.

Submitting all this will connect your database to WordPress account.

Step 5: Script installation.

The last job is to execute the installation script from the installation page. You can find the script using either of the below URLs:

  • http://yourdomain.com/wp-admin/install.php
  • http://yourdomain.com/blog/wp-admin/install.php

That’s it. You have successfully installed WordPress to your site. Enjoy the best wp themes and plugins now.

I hope that this article may have helped you. We may talk about Softaculous in the near future so stay tuned.

Thank You.

How to remove all tables from MySQL Database?

Connect to your mysql host and select the database to be used
example: use notfavoritedb; where notfavoritedb is your database name, and then enter the sql commands given below:

SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
  FROM information_schema.tables
  WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables,'dummy') INTO @tables;

SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;
exit;

Reference: StackOverflow

[Forgot Password] [Can’t Log In] How to reset WordPress admin password without sending reset email?

So, admit it. We put really strong passwords and then we forget them.
And you end up being locked out of your own website.

So here is how you can reset it quickly.
Go to your hosting control panel and then go to phpMyAdmin if available.

Look into the database which is being used for your wordpress installation and click on Users table

Inside users table, find the row responsible for your admin account. (check the user_nicename column for username match)
and Press edit.

On the editing screen, put your new password again the column password and Choose MD5 in left column FUNCTION

Now scroll down and save this password.

And that’s it. Go to your domain / wp-admin path and login with the new password you just saved.

What to do if you dont have phpMyAdmin?

You gotta have some kind of access to MySQL, so connect to it using the credentials inside wp-config.php file

Use the following command after replacing your details:

UPDATE `wppl_users` SET `user_pass` = 'e0a8aa81eb1762d529783cf587f6f422' WHERE `wppl_users`.`user_nicename` = 'admin';

If you remember the email address only, use:

UPDATE `wppl_users` SET `user_pass` = 'e0a8aa81eb1762d529783cf587f6f422' WHERE `wppl_users`.`user_email` = '[email protected]';

The above command will set your password to [email protected]. After this command you can login to wordpress admin panel and then reset your password to anything you want. Or use md5.cz to generate md5 for any password string and replace it with e0a8aa81eb1762d529783cf587f6f422

Do NOT forget to replace “wppl_” with your table prefix (stored in wp-config.php file)

Simple joining of data from two different tables on basis of a common key

SELECT a.user_id,a.meta_value,b.user_login FROM `e_usermeta` a,`e_users` b where a.meta_key="phoneno" and a.user_id=b.ID
SELECT a.user_id,a.meta_value,b.user_pass,b.user_login FROM `e_usermeta` a,`e_users` b where a.meta_key="phoneno" and a.user_id=b.ID order by b.user_login
UPDATE `wp_users` a,`tmptable` b SET a.`user_pass`=b.`usp` where a.`user_login`=b.`ulogin`;

How to change WordPress user roles in MySQL database using PhpMyAdmin?

We will be editing the database rows using PhpMyAdmin tool given with cPanel by many hosting companies

You can change the WordPress user roles by following the given steps:

Step 1: Open phpMyAdmin on cPanel.

Step 2: Open the wp_usermeta or xxx_usermeta table in the database as highlighted. (wp_ is just prefix, your table prefix may be different)

wordpress tables in database

Step 3: In wp_usermeta table, you can find wp_capabilities under meta_key column.

User Roles in usermeta table

Step 4: The roles are saved in form of serialized PHP array. Change the user role you want to assign to the current user by changing the meta_value as:

Subscriber
a:1:{s:10:"subscriber";b:1;}

Contributor
a:1:{s:11:"contributor";b:1;}

Author
a:1:{s:6:"author";b:1;}

Editor
a:1:{s:6:"editor";b:1;}

Administrator
a:1:{s:13:"administrator";b:1;}

MySQL error 1449: The user specified as a definer does not exist

Why this error happens?

Most of the times, reason is that your database dump through command line or PhpMyAdmin or even other libraries can have SQL’s Definer statements. Now, as per MySQL’s Official Documentation:

The DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER characteristic.

MySQL Reference Manual v8.0

Solution: MySQL mysqldump create a safe backup to ensure no corruption

Use the following command to create backup of your mysql database. Put the command in terminal

mysqldump -hHOST -uUSERNAME -p DATABASE_NAME | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > BACKUP_NAME.sql

With gzip compression > backup

mysqldump -hHOST -uUSERNAME -p DATABASE_NAME | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | gzip > BACKUP_NAME.sql

Reference: https://stackoverflow.com/a/9447215/2229148

How to tweak Magento 2 for two letter search in fulltext mode

The problem:

Magento 2 wont return any search results if the search token is 2 letters in length.

Solution:

Alter MySQL conf file. Edit my.cnf file on your server
In CentOS, use command

nano /etc/my.cnf

and add the following lines:

ft_min_word_len=2
innodb_ft_min_token_size=2

Save using CTRL+x and then restart mysqld service.
Now we need to regenerate the index in order to get updated search results. Use the following SQL commands to regenerate:

Don’t forget to replace “jack_db1” with your database name

ALTER TABLE `jack_db1`.`catalogsearch_fulltext_scope1` DROP PRIMARY KEY, ADD PRIMARY KEY (`entity_id`, `attribute_id`) USING BTREE;
ALTER TABLE `jack_db1`.`catalogsearch_fulltext_scope1` DROP INDEX `FTI_FULLTEXT_DATA_INDEX`, ADD FULLTEXT `FTI_FULLTEXT_DATA_INDEX` (`data_index`);
ALTER TABLE `jack_db1`.`catalog_eav_attribute` DROP PRIMARY KEY, ADD PRIMARY KEY (`attribute_id`) USING BTREE;
ALTER TABLE `jack_db1`.`catalog_eav_attribute` DROP INDEX `CATALOG_EAV_ATTRIBUTE_USED_IN_PRODUCT_LISTING`, ADD INDEX `CATALOG_EAV_ATTRIBUTE_USED_IN_PRODUCT_LISTING` (`used_in_product_listing`) USING BTREE;
ALTER TABLE `jack_db1`.`catalog_eav_attribute` DROP INDEX `CATALOG_EAV_ATTRIBUTE_USED_FOR_SORT_BY`, ADD INDEX `CATALOG_EAV_ATTRIBUTE_USED_FOR_SORT_BY` (`used_for_sort_by`) USING BTREE;

References:
https://magento.stackexchange.com/a/157478/32283
https://dev.mysql.com/doc/refman/5.7/en/fulltext-fine-tuning.html

Magento 2 : How to fix tablespace for table exists | base table or view already exists

Fixing General error: 1813 Tablespace for table xx Please DISCARD the tablespace before IMPORT and SQLSTATE[42S01]: Base table or view already exists

Recently I encountered this error while upgrading an extension on Magento 2.2.2
The error was

SQLSTATE[HY000]: General error: 1813 Tablespace for table '`jack_db1`.`jill_table`' exists. Please DISCARD the tablespace before IMPORT., query was: CREATE TABLE IF NOT EXISTS `jill_table`

When I tried to fix the above error, I received:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table '`jack_db1`.`jill_table`' already exists, query was: CREATE TABLE IF NOT EXISTS `jill_table`

So here is how to fix it:

Important Note: Replace jack_db1 with your database Name and jill_table with your table Name

PART 1 – Fixing MySQL backend

First log in to server and check the mysql data folder (mine was /var/lib/mysql/jack_db1)
say your database name is jack_db1 and your table is jill_table
so issue

ls /var/lib/mysql/jack_db1

You will find .frm and .idb files
Note that these files are present as a couple (each frm file has a corresponding idb file)
But the table that is causing you issues will have one of them missing.
.frm was missing in my case, so copy any other frm file and name it as jill_table.frm (i copied wishlist.frm using following command )
cd /var/lib/mysql/jack_db1;cp wishlist.frm jill_table.frm
use the following command to fix ownership

chown -R mysql:mysql /var/lib/mysql/jack_db1/*

Part 2: Fixing Magento Backend

Login to phpMyAdmin if available or use MySQL CLI to perform the following actions.
In the table setup_module of your magento installation
Delete the row which is related to your corrupted extension

DELETE FROM `setup_module` WHERE module="Custom_Module"

you can also do the same from phpMyAdmin

Now delete the table which was causing the issue, (you would not be able to view the table in phpmMyAdmin)
Just run the following query in SQL

DROP TABLE IF EXISTS `jill_table`;

Part 3: Upgrade the Magento using php cli

run the following commands:

rm -rf var/cache/*
rm -rf generated/*
php bin/magento module:enable Custom_Module --clear-static-content
php bin/magento setup:upgrade
#  Hopefully this time you wont see any errors, proceed as usual if upgrade command worked.
#  run
php bin/magento setup:di:compile
#  and 
php bin/magento setup:static-content:deploy -f

Good Luck with Magento 🙂