Category: MySQL

Different types of MySQL Storage Engines Compared

Download PDF version here

Notes:

1. Implemented in the server, rather than in the storage engine.

2. Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

3. Implemented in the server via encryption functions.

4. Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest tablespace encryption is supported.

5. Support for foreign keys is available in MySQL Cluster NDB 7.3 and later.

6. InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.

7. InnoDB support for geospatial indexing is available in MySQL 5.7 and later.

8. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

9. See the discussion later in this section.

Reference – https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

Some of the most important SQL commands.

SQL, Structured Query Language, is a programming language designed to manage data stored in relational databases. SQL operates through simple, declarative statements. This keeps data accurate and secure, and it helps maintain the integrity of databases, regardless of size.

Most of the actions you need to perform on a database are done with SQL statements.

SQL commands are grouped into four major categories depending on their functionality:

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Transaction Control Language (TCL)
  • Data Control Language (DCL)

COMMANDS:

SELECT

SELECT column_name 
FROM table_name;

SELECT statements are used to fetch data from a database (
extracts data from a database ). Every query will begin with SELECT.

ALTER TABLE

ALTER TABLE table_name 
ADD column_name datatype;

ALTER TABLE lets you add columns to a table in a database.
Modifies a table.

CASE

SELECT column_name,
  CASE
    WHEN condition THEN 'Result_1'
    WHEN condition THEN 'Result_2'
    ELSE 'Result_3'
  END
FROM table_name;

CASE statements are used to create different outputs (usually in the SELECT statement). It is SQL’s way of handling if-then logic.

CREATE TABLE

CREATE TABLE table_name (
  column_1 datatype, 
  column_2 datatype, 
  column_3 datatype
);

CREATE TABLE creates a new table in the database. It allows you to specify the name of the table and the name of each column in the table.

We have an entire article on creating tables in MySQL. https://tutes.in/creating-a-table-in-phpmyadmin-xampp-server/

INSERT

INSERT INTO table_name (column_1, column_2, column_3) 
VALUES (value_1, 'value_2', value_3);

INSERT statements are used to add a new row to a table.

WHERE

SELECT column_name(s)
FROM table_name
WHERE column_name operator value;

WHERE is a clause that filters the result set to include only rows where the specified condition is true.

UPDATE

UPDATE table_name
SET some_column = some_value
WHERE some_column = some_value;

UPDATE statements allow you to edit rows in a table.

AND

SELECT column_name(s)
FROM table_name
WHERE column_1 = value_1
  AND column_2 = value_2;

AND is an operator that combines two conditions. Both conditions must be true for the row to be included in the result set.

OR

SELECT column_name
FROM table_name
WHERE column_name = value_1
   OR column_name = value_2;

OR is an operator that filters the result set to only include rows where either condition is true.

These were the most common and basic SQL commands. There are many more commands in SQL.

Thank You.

Creating a table in PhpMyAdmin Xampp server.

Hello People.

In this post we will discuss that how you can create a table in phpmyadmin xampp server for your php project.

Before creating a table first you must create a database, because without database you can not create a table.

steps to create a table in phpmyadmin.

  • Start your Xampp server and open the following link in your browser.
    localhost/phpmyadmin . Now click on the New button.
  • Give name to your database and hit create button. As shown in the image below.

Our database has been created and now we can start creating the table.

  • Click on the database that you just created in the previous step. Then give name to your table and specify the number of columns under the structure menu.
  • Next you just need to fill your table with the details of your project.

You can also create table using MySql query. For that go to the SQL menu and and write your query. If your query is correct the table will be created. An example is shared below.

CREATE TABLE persons (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    phone varchar(70) NOT NULL UNIQUE
);

So, that’s all for now.

Thank You.

Connection timeout with MySQL database.

MySQL disconnects automatically after some time.

If you experience MySQL timeouts, it could be due to heavy or very long MySQL queries.

You can try using mysql_reconnect command before every query, and it should be fine.

MySQL server timeout can occur for many reasons but most commonly it is caused by either an application bug, a network timeout issue, or due to the MySQL server restarting.

These steps could solve the issue. (By setting no time to MySQl.)

  • Edit your my.cnf (MySQL config file)
sudo nano /etc/mysql/my.cnf
  • Add the timeout configuration and adjust it to fit your server.
wait_timeout = 28800
interactive_timeout = 28800
  • Save the changes (CTRL + X , Y , ENTER)
  • Restart MySQL
sudo service mysql restart

The new changes will be applied once it restarts. Hope this will help you.

Thank You.

Your PHP installation appears to be missing the MySQL extension, required by WordPress

The error message “Your PHP installation appears to be missing the MySQL extension which is required by WordPress” can appear when you are using a PHP version which is incompatible with your scripts version. Meaning your WordPress installation is most likely outdated.

Since the ‘MySQL’ extension is no longer supported from PHP 7.0 and beyond, changing your PHP version to 5.6 or lower will fix the issue. This can be done via your website’s control panel’s PHP Configuration section.

So, let’s do it first and then we will see some probable causes of this error.

Steps:

Step 1- Login to your Cpanel.


Step 2- Find PHP PEAR Packages and click on it. and then click on show available modules.

Step 3- Find MYSQL and install the 3 extensions shown in the image above.

Step 4- Now Go to select PHP version.

Step 5- Now find MySQL (Use Ctrl + F), select it and save it.

That’s it. The problem is fixed. Let’s get to some probable causes of this error.

Probable causes of this error.

1. PHP’s MySQL extension not installed.

The quickest way to check if your PHP has MySQL support is to put the following code into a file called info.php in your site and access it from a browser. 

<?
phpinfo();
?>

A MySQL section like this will be shown.
If it’s not shown there, then you don’t have the PHP’s MySQL extension installed.

In Ubuntu servers and RedHat compatible servers, use the following commands respectivly:

# apt-get install php5-mysqlnd

# yum install php-mysql

2. Check if PHP’s MySQL extension directory is misconfigured.

Another cause of the ‘Your PHP installation appears to be missing the MySQL extension which is required by WordPress’. could be that the PHP extension directory is not configured properly.

In order to fix this, locate which PHP.ini file is being loaded and ensure that the appropriate “extension_dir” value is being used. So let’s open up the info.php file we created in Step 1 via our web browser.

Look for the line called “Loaded Configuration File”. The file path noted there is the actual location to the PHP configuration file.

Now, look for the entry called “extension_dir“. It should either be commented, or it should have the correct path to PHP extensions. It should never be left blank.

If you are not sure, just comment the line, and restart the web server.

Simply updating PHP and WordPress might also solve the problem.

In case you are running PHP 5, run the following commands:

apt-get update
apt-get install php-mysql

Restart the appropriate service for the changes to take effect.

This may have solved your problem. Feel free to comment below.

Thank You.

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 abcd@1234. 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`;