MySQL Common Issues

Posted 04th октомври, 2018

This guide sets out possible issues with MySQL, and common fixes. It can be used as a cheatsheet for the most frequent issues related to MySQL.

Contents

• Hosting Setup
• MySQL Query Examples
• Connection Methods and Strings
• • Remote Connection
• • Code Connection
• • Ports and Whitelists
• Keys and Indexes
• Speed and Performance
• Database Management Tools
• • PHPMyAdmin
• Importing and Exporting
• Locking, Crashing and Repairing
• Server Settings

MySQL Fundamentals

MySQL is a database technology. Each database can have infinite tables. Each table has a structure which defines set fields. Fields can be certain types, the most common of which are:

MySQL is used to run a large number of websites online, from Wordpress sites to Facebook. It is simple to use, to query, and to import/export.

Hosting Setup

Most hosting providers do not offer the ability to create a database from within MySQL directly. Instead, database management is handled through the system graphical user interface. The three options are usually:

• Create a database.
• Create a user and manage those user permissions on database X.
• Create a database and user for that database in one step (wizard).

Almost all providers will sensibly lock down database access to local users only. Remote access is enabled only for whitelisted IP addresses.

Different hosting providers and panels have a variety of MySQL quirks and setups.

cPanel

• The database is usually local, running on localhost or 127.0.0.1. cPanel does support a remote database server, but this is infrequent.
• A wildcard (%) allow may be blocked from working, despite the remote access page saying that it is allowed. If a remote connection fails, add the actual remote IP.
• If CSF is installed, the remote IP may require whitelisting globally (csf -a 1.1.1.1).
• Users and Database names are precursed with the cPanel username (username_dbname), except in the case where they have been imported from another panel such as Plesk.

Gridhost

• Database servers are remote, accessed with a local IP internally, or a port remotely.
• IP addresses require whitelisting for each user.
• Users and Database names precursed with linux username (username_dbname)

MySQL Query Examples

MySQL uses a simple syntax. These queries can be run directly against a database either using a command line, using server software such as PHPMyAdmin, desktop software such as Navicat, or through an application after connecting using a connection string.

Example CRUD queries are as follows:

Create

INSERT INTO `tablename` SET `column3` = 'value';

Read

SELECT `column1`, `column2` FROM `tablename` WHERE `column3` = 'value';

Update

UPDATE `tablename` SET `column1` = 'value' WHERE `column3` = 'value2';

Delete

DELETE FROM `tablename` WHERE `column1` = 'value'

Frequently used MySQL operators include:

Connection Methods and Strings

Connections from applications can be made in a number of ways. Here are the most frequent connection methods and common issues.

PHP -Standard MySQLi Connect

This connection should look as follows:

mysqli_connect('dbname','username','password')

PDO

Remote Connections

Ports

The default MySQL port is 3306.

Keys and Indexes

Keys are a way of structuring data in a MySQL table.

• A primary key is a unique identifier, often incremental, which allows to each row in a table. You can only have one primary key per table. This allows a query to run as:

SELECT * FROM tablename WHERE id = 100

instead of

SELECT * FROM tablename WHERE column1 = 'value' AND column2 < '6' AND column3 = 'value2'

• A unique identifier ensures that each value in a column is unique.

Indexes can also be added to

Speed and Performance

Database Management Tools

PHPMyAdmin

Navicat

Importing and Exporting

Import Time Outs

Import Errors

Force Export or Import

Encoding Issues

Capitalisation

Database Locking

Database Crashing and Repairs

Server Settings