-
Deleting Rows From a Table Referenced in the Query
If you do a fair amount of SQL, then every now and then, you’ll likely run into a situation where you need to delete something from a table; but need to also reference that table in the query as well. The trouble is MySQL won’t let you do this, in many circumstances. Fortunately, there is…
-
Using MySQL Queries to Dump/Format Data to Delimited Files
It is often useful to utilize MySQL queries, to export data from tables then write this data into files. MySQL offers the ability to customize many of the details of these exports, which should cover most situations. In this example, we are selecting data from a table, applying some formatting and writing it to a…
-
RSYNC a File Through a Remote Firewall
One of my recent tasks was to set-up a new automatic backup script, which dumps out the MySQL database on the remote host at a regular time, at a later time, it is RSYNC’d from the backup server through a remote firewall. I must say that I was a little surprised, to discover that the…
-
How To Execute a Script After a Running Process Completes
Most people who are familiar with Linux, realize that there are ways of chaining processes to run one after another. Typically this is done by writing a script, or using && to daisy chain additional commands on command line. There is, however, another way to do this; if you’ve already issued a command and want…
-
Efficiently Loading Paginated Results From MySQL with Just One Query Per Page
There are many situations in which pagination of query results is very useful, especially for performance optimization. In most of these kinds of situations, the paginating of results requires you to determine the total number of results, so the application knows the number of pages available. The most common way to do this, is to…
-
Bash Shell Script to Convert All MySQL Tables to use the Innodb Engine
The following, when run in the Bash shell, will convert the engine for all of your MySQL tables to InnoDB. mysql -B -N -e “SHOW TABLES” -u –password= | while read table; \ do \ echo “+ Converting Table $table”; \ mysql -B -N -e “alter table $table engine=innodb” -u –password= ; \ done; Often,…
-
Locating Duplicate Entries In A MySQL Table
If you don’t have unique indexes on your table, it is likely that you will occasionally have entries that are duplicated. This can often happen because of a software bug or possibly a user error. Some applications even choose not to have unique indexes for performance reasons; though this happens at the cost of data…
-
Eliminating Duplicate Records From MySQL Tables
Anyone who works with database driven development to any extent, will occasionally run into a situation where duplicate information is added to their database. I have personally run into such a problem on many occasions since I started working with database driven software. Being able to quickly undo data corruption is extremely important in production…