Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [database]

Questions related to Magento's MySQL database, for example, the table schema, plain SQL queries or database configuration. For questions about the Magento database access layer, use other tags, like: eav, model, resource-model, collections, install-script. For questions about SQL queries in particular, the SQL tag is appropriate too.

71
votes
13answers
76k views

Clearing Magento after testing

I am currently doing a lot of tests in Magento so I can get familiar with it. Unfortunately the database is extremely cluttered with my tests. I wish to clear up the database so I can start over again....
46
votes
7answers
17k views

Price re-index causes DB deadlocks during checkout

I'm experiencing an issue where I believe the Product Price re-indexing process is causing a deadlock exception in the checkout process. I caught this exception in the checkout process: Order ...
32
votes
2answers
101k views

How do I fix my Base URLs so I can access my Magento site?

I changed Base Urls and now my website is inaccessible I moved everything to a new server, didn't change the base_url and my website is inaccessible How do I change or restore base_url my BaseURL ...
29
votes
1answer
17k views

Which MySQL Server provides a better performance for Magento

What do you use as a MySQL Server for Magento? MySQL (Oracle) Percona others (MariaDB) Percona provides a set of improvments for InnoDB storage used by Magento intensively, but these improvements do ...
28
votes
3answers
30k views

Delete custom EAV attributes from the database

I want to remove unused EAV attributes directly from the database before I move my store live. Attributes can be found in eav_attribute table, can I delete attributes from this table? Is it safe? Or ...
28
votes
4answers
10k views

How to optimise database architecture for high volume sites?

Question is less about specific Mysql configuration items, but more about handling multiple databases, splitting read and writes to multiple database servers, master+master? Master+Multiple Slaves? ...
26
votes
1answer
10k views

Magento ER diagram of 1.9.x

I am looking for ER diagram of magento 1.9.x so that i can understand magento database flow and table-relationships. if anyone can provide me COMPLETE ER diagram that will really help me. Like i saw a ...
25
votes
5answers
15k views

How to fix mysql error 1031 - Table storage engine for 'catalog_product_relation' doesn't have this option?

I'm trying to make a new development version of our Magento store. I made a clean install of El Capitan and got apache running with PHP, Mysql(version 5.7.10) etc. I also have phpmyadmin running and ...
24
votes
2answers
32k views

Magento2 : How to database schema upgrade

I am working on magento custom module, in module have Setup\InstallSchema.php file which is installed before. i have added some more database fields in InstallSchema.php so i want to update table ...
23
votes
3answers
45k views

How to add new column to existing table in Magento programmatically?

How can I add a new column to existing Magento core table via install script? (without using pure SQL) I want to use the Magento way which is using alias methods to create install script. So far I ...
23
votes
3answers
23k views

Why and when to use “ Flat Catalog”?

While doing bit research for speed issue of magento website i got some links where people says enable "Flat Catalog" to increase performance and speed . Issue is that why it is not enabled by default ...
22
votes
4answers
20k views

How to log all Magento SQL?

Is there any easy way to log all query executed by Magento ? The goal is to print all the SQL executed, for example during page load
22
votes
3answers
10k views

How to truncate magento table using collection?

I have a custom table. I want to truncate the table using Magento collection without SQL query. Hope someone will provide some useful information.
21
votes
1answer
12k views

How to add a foreign key to a table programmatically?

I am trying to add a set of new tables and I'm trying to add a foreign key to link these tables. I have looked online to try and find some information about addForeignKey() but cannot find anything. ...
21
votes
2answers
5k views

Install scripts: Creating tables vs. updating existing ones

have one question, recently I was developing one module with a lot of tables in DB, and concept was changing often, so was need to change existing tables in DB, and I noticed difference in table ...
21
votes
2answers
1k views

Magento Each New Invoice Increment Id Increment By 5?

I need to increment each new invoice id by 5, i have tried below code. But changed all invoices, shipments, orders and credit memos. I need each new invoice id increment by 5, shipments increment by ...
19
votes
3answers
1k views

The purpose of 'eav_' tables

I've always wondered what is the meaning of the tables : eav_entity eav_entity_datetime eav_entity_decimal eav_entity_int eav_entity_store eav_entity_text They are always empty. They are created ...
17
votes
10answers
26k views

Magento 2 - What is the correct way to change default Order Id?

Magento 2. After fresh install the new Order id will be 100000001, 100000002 and so on. What is the correct way to change that increment id so it would be a higher number? For example 155555552, ...
17
votes
3answers
16k views

how to connect another database from magento

Is it possible to connect to another database from Magento and accessing data? If I need to create a module, how can I create a module to access another database? Is there any tutorial which tells ...
17
votes
4answers
2k views

Magento Development Workflow: how to “source control” databases and update the live Magento installation from the Test magento installation?

I am posting this question because I would like to know what the best development workflow is for someone who wants to manage all aspects of an online store. As with all web development of course, ...
16
votes
2answers
23k views

Magento 2: Diagram of database tables, such as Orders, Products, etc. and their relationships

I'm looking for a diagram or schema of all Magento 2 database tables, such as Orders, Products, etc. and their relationships with each other.
16
votes
6answers
33k views

Magento 2 - How to get attribute options value of eav entity?

How can I get the attribute options values of eav entity? I found solution only for magento 1.x but M2 I don't know. M1: $attr = Mage::getResourceModel('eav/entity_attribute_collection')->...
16
votes
5answers
3k views

Index error after upgrade to EE 1.14.2.0: table catalog_product_entity_tmp_indexer doesn't exist

After I've upgraded my Magento to EE 1.14.2.0 the cron enterprise_refresh_index always gets this error: exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 ...
16
votes
3answers
4k views

Magento 2: What is the difference between the row_id and entity_id in the %_entity tables?

In Magento 1, there wasn't a column called row_id in the entity tables, you just had the entity_id: CREATE TABLE `catalog_category_entity` ( `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT ...
15
votes
4answers
18k views

Add Column to a grid (observer) - Column ‘store_id’ in where clause is ambiguous issue

I'm adding a column to the order grid using observer approach: On the event -> sales_order_grid_collection_load_before I'm adding a join to the collection On the event -> ...
15
votes
1answer
3k views

Mechanics of the new Changelog Tables (ex: catalog_category_product_cat_cl)

I just spotted the mentioned tables in the database. I think they are new in Magento EE 1.13 and might be related to the new indexing. +----------------------------------------+ | ...
15
votes
1answer
2k views

Create database table from CSV

I am working on a module that requires access to some tabular data provided from an external source. It's only two columns but has roughly 40000 rows. Currently, my module is just parsing the CSV in ...
15
votes
3answers
6k views

Some Magento tables are not InnoDB, is it safe to convert all tables to InnoDB?

I am using AWS RDS Read Replica. It constantly has issues with Magento's Memory engine tables. For backup and read replicas RDS loves InnoDB. Can I safely change all tables to InnoDB? In addition I ...
15
votes
3answers
31k views

Base Url Change Magento2.1

I have changed Base url in Magento 2.1, but it does not works. While installing I have set base url to www.domain1.net. I need to change it to www.domain2.net. What I tried: Update baseurl in ...
15
votes
5answers
5k views

Convert a production database into test data

The closer a test is to production, the better it can emulate production behavior. I would like to copy the database backups from production into our test environments, but what do I need to change so ...
15
votes
2answers
1k views

PDO exception while executing cron: newsletter queue send fails (Unknown column 'main_table.data' in 'field list')

the cron job fails when processing the newsletter queue. I debugged the PDO calls and it seems like Magento is mixing up fields here. I have no clue where Magento (or Zend PDO) get the fields to ...
15
votes
1answer
2k views

What is the best practice way in Magento 2 for creating Many To Many relationships?

I've looked around the core and seen a few examples of many to many relationships between models, but I can't see a definitive answer on this. As an example, say we create a new model and we want to ...
14
votes
3answers
20k views

How to list all disabled products with sql?

I need to list all disabled products by using a MySQL query. I have too many products in my database to be using something like this (creating a collection, loading it and looping over it): http://...
14
votes
5answers
21k views

Magento MySQL Has Gone Away Error

I'm having loads of weird issues on Magento CE 1.7.0.2. During normal operations, the site will occasionally produce a Magento Error Page (There has been an error processing your request) on both ...
14
votes
1answer
1k views

How to use Database as slow_backend instead of Files in Magento EE 1.12?

In Magento EE 1.12.0.0 It would seem that no matter what configuration changes I make to app/etc/local.xml, the default file cache continues to be used (which is evidenced by var/cache/ always filling)...
14
votes
1answer
561 views

Magento Backend 404 for all but two “Website” configuration scopes

In our Multiwebsite / Multistore(view) Magento 1.9.2.2 configuration one of the websites, including it's store and storeview had to be removed. While the removal itself went fine (I've done this ...
13
votes
5answers
19k views

Magento 2: How to reset Customer Password from Database

It's hash for customer password in DB. So MD5 & Sha1 is not working. UPDATE `customer_entity` SET `password` = MD5('test123') WHERE `email` = 'X@X.com'; So how to update password using database ...
13
votes
3answers
10k views

How to log all Magento 2 SQL queries? [duplicate]

Want to get all Sql queries during the page load. can any one help me on How to log all Magento 2 SQL queries ?
13
votes
10answers
39k views

SQLSTATE[HY000] [2002] No such file or directory error

There has been an error processing your request SQLSTATE[HY000] [2002] No such file or directory Getting the above error please someone provide me solution to solve this.
13
votes
1answer
2k views

Integrity constraint violation: 1062 Duplicate entry for key 'UNQ_SALES_FLAT_INVOICE_INCREMENT_ID'

I'm helping a merchant track down the root cause for some failed payment transactions (during a heavy order day), that failed with the following error SQLSTATE[23000]: Integrity constraint ...
12
votes
1answer
15k views

Add varchar column to order address

I am trying to add a few varchar fields to the sales/order_address table but am coming up against a column definition error. I have the following code: $oInstaller->getConnection() ->...
12
votes
4answers
3k views

How to remove a duplicate sku via database?

I get this error when attempting to edit and save a few of my Magento products via admin: "The value of attribute "SKU" must be unique." When I view my products in Magento admin a duplicate sku is ...
12
votes
2answers
1k views

how to save image custom attribute in magento 2

I need to display few images of product in frontend based on condition: use for virtual mirror should be checked. <?php /** * Copyright © Magento, Inc. All rights reserved. * See COPYING.txt for ...
12
votes
1answer
799 views

Magento 2: What are `resources.xml` Files Used For?

In Magento 2, one of the XML configuration files a module may have is resources.xml file. For example, the sales module has one #File: vendor/magento/module-sales/etc/resources.xml <?xml version="...
12
votes
1answer
936 views

Does the Magento Database Upgrade happen in a “transaction”?

We have this issue atm: A client gets his shop upgraded from CE 1.4 to CE 1.8. The file-upgrade went well, and the database upgrade went also well on our development machine. When we try to upgrade ...
11
votes
4answers
3k views

Difference between InstallSchema and InstallData

I want to know the difference between InstallSchema and InstallData in details. I also need to know when can I use InstallSchema.php and InstallData.php?
11
votes
2answers
7k views

Ok to truncate report_viewed_product_index

I was reading through the list of tables that are ok to truncate (https://stackoverflow.com/questions/12205714/list-of-tables-to-safely-truncate-in-magento) and I didn't see ...