The problem of SQL-commands with Magento

Running SQL-queries within the Magento database is not as simple as it might seem. The Magento database is based on the principle EAV (Entity-Attribute-Value) which basically means that one database-record is not written to one single database-table, but to several database-tables instead. This makes it a lot harder (but not impossible) to run manual queries.

Magento has released itself a wiki-page describing the procedure of resetting the Magento database through SQL. Other blogs on the internet describe the same procedure as well.

Command-line or phpMyAdmin

Most people are using phpMyAdmin to access the MySQL database of their site. Within phpMyAdmin you can Import a file containing SQL-commands. To do so, download the SQL-file available in the bottom of this page. Alternatively you can past a list of queries under SQL.

Alternatively, you can also import the SQL-command from the command-line. But for this to work you need shell-access to your hosting environment (for instance: SSH).

SQL queries

The following queries reset the database to a default. Note that these queries are based on the following assumptions:

• Vm2Mage is installed, so Vm2Mage tables need to be cleansed as well
• No orders are created (except for orders migrated by Vm2Mage)
• No database table prefix is used
• Magento 1.3 or 1.4 is used

Note that these queries are designed to use Vm2Mage. They need further modification if used without Vm2Mage.

SQL-commands for resetting customer-data:

<script src="https://gist.github.com/yireo/5525071.js"></script>

SQL-commands for resetting category-data:

<script src="https://gist.github.com/yireo/5525061.js"></script>

SQL-commands for resetting product-data:

<script src="https://gist.github.com/yireo/5525042.js"></script>

Same problem, different task

On the Internet you can find different SQL-commands as well to reset something in the Magento database, and all these suggestions boil down to the same problem: Running queries in the Magento EAV-based database is hard. All these solutions vary in the task. The above is ment specifically for Vm2Mage, while other solutions focus on generic Magento databases.

Also important to note is that some SQL-commands focus on emptying out the order-tables. But if you're running Magento in a production environment, this is certainly not the thing you want to do - you will loose all your orders. For deleting orders in a production environment, there are two extensions that can help you:

The Boutik Circus Delete Orders is free and allows you to delete orders with the status "Pending". The Yireo Delete-Any-Order module is commercial and allows you to delete any order that has a status other than "Pending", meaning that it will also delete shipping information, invoices and so on.