Tag: MySQL
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 Host * […]
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: SET @store := 1, @name_id := 71, @short_desc_id := 72, @desc_id := 73; SELECT CPE.sku AS sku, CPEVN.value AS name, CPEVSD.value AS short_description, CPEVD.value AS description FROM catalog_product_entity AS […]
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 […]
Dump wildcard selected tables
This little tutorial shows how to dump wildcard selected tables from a MySQL database. That’s useful if you don’t want to dump the whole database. Background Recently I had to export a few selected tables from a very huge database. For that I could have run a query like that to export only the required […]
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: START TRANSACTION; SELECT * FROM nicetable WHERE somthing=1; UPDATE nicetable SET nicefield=’VALUE’ WHERE somthing=1; SELECT * FROM nicetable WHERE somthing=1; #check COMMIT; # or if you want to reset changes ROLLBACK; […]
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: SELECT fieldname, COUNT(*) FROM tablename GROUP BY fieldname HAVING COUNT(*) > 1 This query will give you a list of all the entries having the same value for fieldname. It also will show […]
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: SELECT table_name AS “Table”, round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB” FROM information_schema.TABLES […]