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.

Filter by
Sorted by
Tagged with
74
votes
13answers
77k 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....
16
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 -> ...
23
votes
4answers
21k 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
6
votes
4answers
40k views

How to move magento shop to another server

I was having my site in shared hosting, I took the entire backup, its structure is like addons homedir mysql resellerpackages suspendinfo bandwidth homedir_paths ...
34
votes
2answers
105k 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 ...
24
votes
11answers
29k 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, ...
47
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 ...
16
votes
6answers
36k 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')->...
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?
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() ->...
30
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
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 ...
9
votes
2answers
8k views

How to “add to cart” a product with custom input field and save it to Database?

I had created a custom module in which I am overriding the add to cart form on the product page, and I successfully did it with catalog_product_view.xml file. Now I can see a custom input field on the ...
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? ...
13
votes
5answers
21k 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 ...
24
votes
3answers
24k 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 ...
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 ...
28
votes
2answers
35k 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
46k 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 ...
14
votes
6answers
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 ...
10
votes
3answers
8k views

MySQL keeps hanging (queries stuck on sending data)

I have the following situation: About 5 times a week (not related to any specific situation like cache clear, traffic spike) some queries are stuck on sending data (show processlist): > ...
8
votes
2answers
14k views

Collections - Filtering multiple multi-select attribute values

That's not a typo. I am aware that I need to use 'finset' to filter my multi-select attributes; however, I am trying to filter multiple values at once and getting: Incorrect parameter count in the ...
0
votes
0answers
237 views

sql error while adding any products to category : M2

Something went wrong while saving the category.SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (mydb....
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
2answers
25k 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.
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, ...
15
votes
3answers
12k 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
4answers
16k views

How to handle huge sales_flat_quote tables - remove abandoned cart refuse

Situation In our magento database we have three tables that are growing too big. sales_flat_quote_shipping_rate: 3045MB sales_flat_quote_address: 1688MB sales_flat_quote: 1897MB That's more than ...
23
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.
7
votes
3answers
11k views

Magento 2 - Moving site - 'No such entity'

After I moved a site from staging.site.nl to site.nl I received a weird error. If I try to reindex the site I get this error: No such entity. If I try to login I get this message: a:4:{i:0;s:15:"No ...
8
votes
2answers
11k views

Find the attribute_id by attribute_code

I need to find the attribute_id values of the attributes image, small_image and thumbnail. I know them for my database - 85, 86 and 87, but I need to make my query dynamic, instead of with hard-coded ...
5
votes
2answers
1k views

Modify core tables vs custom tables and joins

I'm aware that there are multiple different ways of adding a custom column to the order grid, from overloading the block to custom layout handle with event observer for loading data. My question is ...
5
votes
3answers
3k views

How to disable report viewed products in magento

Our magento store is writing about 50 rows per second to the report_viewed_product_index table. How can I disable this event? I have log cleaning cron enabled but that doesn't prevent the ...
3
votes
4answers
4k views

What is core_file_storage table in Magento?

I have core_file_storage table in Magento with very huge size. What is it's functionality? Can I remove and replace functionality? Is it affects to the speed of my database? Thanks.
7
votes
1answer
4k views

Magento 2 Tables Being Created Ending in “_cl”

After doing a diff on the tables from a production server to a local server I have noticed that new tables have been created all ending with _cl. These are: # TABLE: ...
3
votes
2answers
3k views

Magento 2 : Notice Error - Array to String conversion

I've been trying to save Multi Select field values to DB. I've added the field to sales rule form using ui_component. <fieldset name="exclude_brands"> <argument name="data" xsi:type="...
-1
votes
2answers
26k views

SQLSTATE[28000] [1045] Access denied for user 'ec2-user_din'@'localhost' (using password: YES)

Hi I am trying to connect my MySQL database with my Magento site. For further information I am using Magento 1.8.1, Apache 2.2.15, MySQL 5.5, PHP 5.3.28 & PhpMyAdmin and I am using Amazon Ec2 ...
5
votes
3answers
25k views

Model save using setData and addData not adding to database

I have a model and I was trying to add a model to the database. When I execute the php script to add everything works fine and no exception are thrown. But the data is not added to the database, which ...
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 ...
7
votes
2answers
23k views

Configuring the local.xml file to connect securely to an external MySQL?

I was wondering how one might go about setting the local.xml file (if it's even possible) for a Magento install to connect to a MySQL server over a secure connection. I currently have my webserver ...
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. +----------------------------------------+ | ...
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 ...
11
votes
2answers
17k views

Magento Database Diagram

I found this great diagram for Magento Database Tables, however, I would like to know if this is still up to date. I am running Magento 1.9. It was published in 2009 and several Magento updates have ...
8
votes
2answers
17k views

How to get my custom table data from magento 2

I have created a new table using custom module and stored value to this table, now, i need to get table data in block php file from database in magento 2, How to achieve this? TableName="email_format"...
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 ...
8
votes
4answers
15k views

How to overcome timezone problem in Magento?

I used this method, to add date and time picker in magento backend. Now the input time is getting stored to my database correctly. The problem is my magento store's timezone is set to Indian Standard ...
4
votes
3answers
11k views

An error occurred while saving the URL rewrite

I have a store running 1.7.0.2 (updated from a clean install of 1.6). The store keeps running into the "An error occurred while saving the URL rewrite" error. The store is a very simple installation ...
2
votes
2answers
4k views

Website infected by virus

I am new to Magento and few days ago my website is hacked by spammers. Now I want to rebuilt my droplet. Can someone please tell me step by step process. I can install LAMP and Magento template, only ...
2
votes
2answers
8k views

Magento using Postgres?

I'm trying to use Postgres instead of the default MySQL setup. I understand that Magento 2 should support Postgres out of the box (once it is released). From the looks of it there is a layer of ...
8
votes
1answer
8k views

Change the scope of a attribute

A extension created a extra tab in my category menu. With this code: $setup->addAttribute('catalog_category', 'menutopdescription1', array( 'group' => 'General', 'input' ...