MySQL Common Issues

This is restricted content – not visible to the public. #

MySQL Common Issues #

Posted 04th October, 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:

Field Type Description Requirements
varchar Mixed characters A length must be set
int Numeric Integer
text A free text field
enum A series of options
timestamp A unix timestamp
datetime The date and time

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 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
  • 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:

Operator Description
COUNT(*) Count number of rows
SUM(*) Sum number of rows

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:



Remote Connections #

Ports #

The default MySQL port is 3306.

Keys and Indexes #

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

  • 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’

  • 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 #

Powered by BetterDocs