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
|
1 2 |
Host * ForwardAgent yes |
create […]
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 CPE LEFT JOIN catalog_product_entity_varchar AS CPEVN ON (CPE.entity_id = CPEVN.entity_id AND CPEVN.attribute_id = @name_id) LEFT JOIN catalog_product_entity_text AS CPEVSD ON (CPE.entity_id = CPEVSD.entity_id) AND CPEVSD.attribute_id = @short_desc_id LEFT JOIN catalog_product_entity_text AS CPEVD ON (CPE.entity_id = CPEVD.entity_id AND CPEVD.attribute_id = @desc_id); |
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 […]
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 […]
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 […]
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 […]
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, […]
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:
|
1 2 3 4 5 6 7 8 9 10 |
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; SELECT * FROM nicetable WHERE somthing=1; #should be the old value |
For further information see https://dev.mysql.com/doc/refman/5.0/en/commit.html and my answer on stackoverflow.com.
You can use the following query to get all duplicated records of a MySQL table having equal values for the respective field:
|
1 |
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 the amount of duplicates.
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:
|
1 2 3 4 |
SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC; |
This query selects all tables from all connected databases. If you want to select a certain […]
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:
|
1 |
mysql> STATUS; |
This will give you an output like that:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-------------- mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 1044395 Current database: Current user: niceguy@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.73-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 2 days 6 hours 26 min 44 sec Threads: 8 Questions: 399680642 Slow queries: 20292 Opens: 9697 Flush tables: 1 Open tables: 4096 Queries per second avg: 2039.145 -------------- |
As you can see, you got a lot more information like operation system data, MysSQL version or the number of slow […]
