How to clone a table structure in MySQL
I have often needed to clone a table without it’s data in MySQL. Oftentimes this has been because I have a continually growing temporary table… Read More »How to clone a table structure in MySQL
MySQL
pt-online-schema-change Add Index
Percona has a great toolkit that allows you to perform schema changes on a MySQL or MariaDB database without any downtime, it works by creating… Read More »pt-online-schema-change Add Index
How To Create a User and Grant Permissions in MySQL
How to create a user How to delete a user How to grant permissions How to apply those permissions Going a bit deeper Different types… Read More »How To Create a User and Grant Permissions in MySQL
Faster alternative to MySQL Delete Table Contents / Truncate
From time to time you might have some rather big tables that you want to delete all the data quickly and start afresh. You have… Read More »Faster alternative to MySQL Delete Table Contents / Truncate
Moving a MySQL Database without downtime
At AO Analytics we found ourselves in the position where we needed to move our master MySQL database without ANY downtime and for anyone who’s… Read More »Moving a MySQL Database without downtime
ORDER BY RAND() – Faster Alternative
MySQL’s ORDER BY RAND() function can be so useful for returning random items from a table, in fact, we have used it a million times… Read More »ORDER BY RAND() – Faster Alternative
How to Bring Back GROUP BY in MySQL
If you are suddenly not able to perform GROUP BY statements as you used to after a MySQL/MariaDB version update, then you will have noticed… Read More »How to Bring Back GROUP BY in MySQL
How to Purge the BinLog for MySQL and MariaDB
If you see lots of disk space quickly disappearing on your MySQL or MariaDB server, then you may want to look into the BinLog directory,… Read More »How to Purge the BinLog for MySQL and MariaDB
How to Delete from a Table where rows match in Another Table – MySQL
So you have a table where you want to delete a bunch of rows, based on a particular column being matched in another table. This… Read More »How to Delete from a Table where rows match in Another Table – MySQL
REPLACE INTO instead of INSERT INTO – MySQL
There are many occasions where you need to INSERT a record into a MySQL database table, but the record already exists. INSERT INTO queue_domains (domain)… Read More »REPLACE INTO instead of INSERT INTO – MySQL
When to use FLUSH PRIVILEGES in MySQL
So if you’ve ever created users and given them specific permissions from with SQL or the commandline, you will be familiar with a statement called… Read More »When to use FLUSH PRIVILEGES in MySQL
MySQL Community Server MSI Installer Hangs on Windows 10
I was trying to install MySQL Community Server for Windows 10 using the standard .MSI installer provded from the MySQL website and the installer just… Read More »MySQL Community Server MSI Installer Hangs on Windows 10
Let Joomla and MySQL interact!
I often need a quick and easy few lines to retrieve some data from MySQL using Joomla without all the MVC nonsense that usually goes… Read More »Let Joomla and MySQL interact!
How to backup all mysql databases
In order to backup all mysql databases, you can run the following command in your linux command line: The example below is configured with username… Read More »How to backup all mysql databases
Unable to connect to any of the specified MySQL hosts.
If you get the following error while trying to connect to a MySQL database using a third party client such as TOAD or SQL Workbench,… Read More »Unable to connect to any of the specified MySQL hosts.
LEN in MySQL
In MySQL 5.1 LEN is called LENGTH, you use it exactly the same and pass in the link, so: LENGTH(link).
MySQL Error: Can’t get hostname for your address
This error is thrown by MySQL because it tries to do a DNS lookup on the address connecting to the server and it fails due… Read More »MySQL Error: Can’t get hostname for your address
..near 'option o ON (po.option_id = o.option_id) LEFT JOIN option_description od ON (o.o' at line 1
So you’ve just installed Opencart 1.5.0 and when attempting to edit a product or category or etc you get the following error: Error: You have… Read More »..near 'option o ON (po.option_id = o.option_id) LEFT JOIN option_description od ON (o.o' at line 1
MSSQL Select Not Null
I mainly use MySQL these days, so get screwed over for a few minutes when I have to do something in MSSQL that is slightly… Read More »MSSQL Select Not Null
MySQL Main Query Types used in PHP to select, insert, update and delete data
MySQL Main Query Types SELECT SELECT * FROM tablename INSERT UPDATE DELETE Note You will use a lot of WHERE clauses as well along with… Read More »MySQL Main Query Types used in PHP to select, insert, update and delete data
Connect to mysql database from php
If you need to connect to a mysql database from php you can do it like this: This allows you to make a connection to… Read More »Connect to mysql database from php
Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource
Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource You have just gotten a warning when trying to check if an inserted row was… Read More »Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource