Tunnel MySQL through SSH

Recently I had the challenge, that the MySQL database of one of the Magento shops I maintain wasn’t accessible directly through the Net. The access was restricted to the Webserver only. So I had to tunnel MySQL through SSH. And that worked like that: edit the ssh config (~/.ssh/config) on the remote Webserver

create  […]

Save typing effort for complex MySQL queries with custom variables or procedures

To save time and because it’s not that smart to repeatedly retype the same things multiple times, you can user custom variables in MySQL like that:

You first have to run the SET query. Afterwards the variables are available for the whole session. Then you can fire the SELECT with the custom variables. Another […]

Drop all tables from MySQL database

From time to time I stumbled over the case, that I had to drop all tables from a MySQL database. In phpMyAdmin or other GUI-based tools – in general – you only have the possibility to drop the whole database. But then you have to recreate it from the scratch.  Or you have to click-drop each table […]

MySQL server has gone away error fix for Magento

Today I wanted to test a tiny shell script, that didn’t do such magic stuff. It only generates a few websites, stores and storeviews and associates approximately 200 products to 4 websites. When I ran it locally I had no problems at all. But on the staging server I got the following error message when […]

Stop running indexes of Magento

Sometimes Magento indexes hang up unexpectedly. They are shown as running in Magento backend but looking at the process status (@see https://www.ask-sheldon.com/list-processes-after-cpu-usage/), you recognize that the respective process doesn’t do anything. Other common problems are, that there are payment transactions failures when price index is running (catalog_product_price @see http://magento.stackexchange.com/questions/260/price-re-index-causes-db-deadlocks-during-checkout) or that the search doesn’t work properly, […]

Simulate a MySQL query before running it

It is possible to Simulate a MySQL query before running it with the use of  MySQL-Transactions and the build in ROLLBACK-Feature of MySQL: For example:

For further information see https://dev.mysql.com/doc/refman/5.0/en/commit.html and my answer on stackoverflow.com.

Find duplicate entries in a MySQL Table

You can use the following query to get all duplicated records of a MySQL table having equal values for the respective field:

This query will give you a list of all the entries having the same value for fieldname. It also will show the amount of duplicates.

Get table or DB size im Megabyte

Sometimes it is really useful to know how large a certain database table has become. With the following SQL statement you get a list of all tables of the currently connected database with their rounded size in MB:

This query selects all tables from all connected databases. If you want to select a certain […]

Get current user, mysql version and other status information

To get information about the currently connected user and more MySQL status information, you can call the following command if you are already logged in:

This will give you an output like that:

As you can see, you got a lot more information like operation system data, MysSQL version or the number of slow […]