Articles

Creating databases and using phpMyAdmin Almost every site that isn't just static pages needs a database. In cPanel you create one in about a minute - and...
What time the server is on, and why PHP and MySQL can disagree A form that records the wrong time, an order dated yesterday, a scheduled job firing two hours early - it is nearly...
Backing up the database of a Drupal site Drupal files are the skeleton; the database is everything else. Pages, users, menus, permissions, site configuration...
Connecting to your MySQL database with MySQL Workbench MySQL Workbench is a program you install on your own computer to work on a database the way you would work in a...
When the site says it cannot connect to the database It is one of the more frightening messages a site can show, because there is no page at all behind it: just a line...
Remote access to PostgreSQL: what works and what does not PostgreSQL runs on our servers, cPanel carries its tools, and a PHP website can talk to it. There are two things to...
Where each application keeps its database connection details Every website that stores anything has, somewhere in its files, four lines holding the database name, the user, the...
Importing a large database over SSH phpMyAdmin handles most imports. But there is a size beyond which it gives up: either the file will not upload, or...
Error establishing a database connection: the causes, in order The page shows one white line saying «Error establishing a database connection» and nothing else. It is not a...
Finding out which database this WordPress uses The account lists five databases and none of the names says anything. You need to know which one belongs to the...
Cleaning and optimising the database in phpMyAdmin A site with a few years behind it carries a database full of things nobody reads: a revision of every single save,...
The max_user_connections error: what it is, and why it is not a limit we set The site stops, or one page stops, and the error log carries a line like this one: User «account_user» has exceeded...
MySQL server has gone away: the causes, in order of likelihood It is one of the most misleading messages there is, because it seems to say the database server fell over. It almost...
The database has grown: where to see its size, and what can go One day you notice the account is fuller than it should be, and that a good part of it is the database. The right...
Emptying a database without deleting it You are about to reinstall the application and you want a clean start. The obvious route looks like deleting the...
MySQL or MariaDB: which one runs here, and does it matter Shared hosting here runs MariaDB, not Oracle’s MySQL Server. At the time of writing the version is 10.11.16. For...
Creating a database user and giving it the right privileges A database without a user is no use to anyone, and a user with too many privileges is an open door. In cPanel you do...
Exporting a database with phpMyAdmin In cPanel open phpMyAdmin, click the database name in the left column, go to the Export tab, leave the method on...
Importing a database with phpMyAdmin, and the size limit that stops you Create an empty database, open it in phpMyAdmin, go to the Import tab, choose the file (.sql, .zip or .gz) and click...
Moving a database to another account or server Export the database from the source, create an empty database and user at the destination, import the file and point...
Connecting to MySQL from PHP: mysqli and PDO, with a working example PHP has two ways of talking to the database: mysqli (MySQL and MariaDB only) and PDO (which also serves PostgreSQL...
Connecting to your database from Python and Node.js In Python use PyMySQL (or mysql-connector-python); in Node.js use mysql2. In both the host is localhost, the...
Letting an outside address reach your MySQL: Remote MySQL in cPanel cPanel has a tool called Remote MySQL, where you type the IP address of whoever may connect to your database. There...
Reaching MySQL or PostgreSQL through an SSH tunnel, step by step The database ports (3306 for MySQL, 5432 for PostgreSQL) are not open from outside. An SSH tunnel solves that: it...
Installing PostgreSQL on a VPS On a VPS running Ubuntu 22.04 LTS, as root, you install PostgreSQL with apt install postgresql postgresql-contrib....
Creating a PostgreSQL user and database, and connecting with psql On a VPS with PostgreSQL already installed, enter as the system user postgres with sudo -u postgres psql, then...
Allowing remote PostgreSQL connections on a VPS, safely By default PostgreSQL listens only on 127.0.0.1. Accepting connections from outside takes three adjustments: tell it...
PostgreSQL in cPanel: creating a database and connecting from PHP On shared hosting PostgreSQL 13.23 is active and has tools in cPanel. Create the database and user under PostgreSQL...
PostgreSQL or MySQL: which one for your project If you are installing WordPress, Joomla, PrestaShop or another ready-made application, use MySQL (here, MariaDB):...
Backing up with mysqldump and pg_dump, and restoring the copy Over SSH, you copy a MySQL or MariaDB database with mysqldump and a PostgreSQL database with pg_dump. Each produces...
Character sets: utf8mb4, collation, and the question marks in your text If accented text shows up as ??? or as é instead of é, the database, the connection and the page are not speaking...
Slow queries: how to find them and the fixes that work most often A site that is slow because of the database almost always has one or two queries doing all the work, and the most...
A table marked as crashed: what it means and how to repair it The message Table is marked as crashed and should be repaired means a table, almost always of the MyISAM type, has...
Error 1045 Access denied for user: the causes, in order Error 1045 means one thing: the connection reached the database, and the database refused the username and password...
Error 1044 Access denied for user to database: when your file tries to create the database The 1044 nearly always turns up when you import an .sql file. It says that the user, already accepted, has no...
Error 1064 You have an error in your SQL syntax: how to find the line The 1064 is the database saying it did not understand the SQL statement: it is written in a way the database does...
Error 2002 cannot connect to the database server: host, socket and port The 2002 is different from the 1045: here the application never reached the database server. No password was...
Database indexes in phpMyAdmin: the types, the column order, and how to add one An index is a sorted list of one column (or several) that the database consults instead of reading the whole table....
Backing up or restoring only some tables of a database It is not always worth copying the whole database. Before updating a plugin, deleting old orders or fiddling with a...
SQLite or MySQL: which one fits a small project The short answer: SQLite fits a small application with few users writing at the same time and nobody from outside...