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.

1
vote
3answers
7k views

Error When Adding Attribute: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 1

I am trying to add a new attribute, but seem to have a problem with what I think is the increment IDs of the primary key of the table eav_attribute, which is attribute_id. Every time I try to add a ...
0
votes
1answer
188 views

Admin module custom select not saveing

Ok so I've created a custom module that has a single table model with a active select (no, yes) and a title and image. Now when I save the title saves and updates fine, the image is deleted or updated ...
3
votes
1answer
2k views

Magento Reviews - When do they get aggregated?

Can somebody provide some insight for when reviews get put into the rating_option_vote_aggregated table? I'm trying to filter through my reviews based on four stars or higher and I was looking to use ...
0
votes
1answer
47 views

Multiple Where Statements

I have the following line of code $select = $read->select() ->from($batchTable,array('id','batch','strength','flavor','brand')) ->where('status', 1) ->...
0
votes
2answers
534 views

Importing large inventory kills the mysql server

I am using Magento data flow profiles for importing a very large inventory (has about 13000 products) and updating it three times a week.. i saw that after importing the products, the site is going ...
1
vote
2answers
3k views

SQL install script doesn't run when caches are turned on

I created an SQL installation script for my extension and it ran just fine. I was developing the extension with the caches turned off. When I turn the caches on, the installation script doesn't get ...
1
vote
3answers
1k views

How can I filter Reviews based on their average rating to only display above a certain amount?

I'm trying to change up the review list block on our website such that it only displays the top three latest four star or higher reviews. Currently I am trying to go about this in the following ...
5
votes
4answers
831 views

LEMP Stack Suggestions for a 10k + Item Magento Community Install

I operate a Magento Community install that currently contains 30k SKUs (6k configurable with 24k variants). We are having an ongoing issue with pervasive CPU overloads that result in MYSQL ...
0
votes
1answer
524 views

How to add a custom field to Magento catalog_product_option_type_value table

I'm just wondering if there is an easier way? What I was going to do is create my own custom table with a reference to the option_type_id from the catalog_product_option_type_value because I don't ...
6
votes
1answer
364 views

Magento: what is the `customer_payment` entity type used for?

Short version: In our Magento database we have 40 records in our customer_entity table that have an entity_type_id that maps to an entity_type_code of 'customer_payment' in the eav_entity_type table. ...
4
votes
2answers
2k views

1.7 bug - MySQL hanging due to top navigation menu caching bug

I'm running a Magento store with ~45K products and ~3700 categories. I've noticed that my store/server takes huge performance dips multiple times per day as a result of this query: SELECT `...
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 ...
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 ...
5
votes
2answers
496 views

Combine Magento TAF with EcomDev_PHPUnit fixtures

I want to use the Magento Test Automation Framework to create a Smoke Test Suite, i.e. a test suite that quickly tests a cross section of the store to check for serious errors, not to test the correct ...
4
votes
1answer
4k views

Magento running under MSSQL

Has anyone managed to get Magento running under MSSQL? Since 1.6 there is the possiblity to change the PDO driver, using some steps: Rename Mysql.php files to Mssql.php app/code/core/mage/Core/Model/...
1
vote
1answer
124 views

Setup Script Not Working Properly

I need to add attribute through a setup script.Attribute properties are working,but the frontend properties are not applied as specified in the setup script.For example, visible_on_front ...
2
votes
2answers
517 views

How to enable database logging without modifying the core?

I am afraid that this might be a rhetorical question, but: Is there any other and cleaner way to enable DB logging then modifying the file Varien/Db/Adapter/Pdo/Mysql.php and set $_debug and $...
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): > ...
6
votes
1answer
2k views

Configurable Products - attribute label, “use default” issue

When I create a configurable product, in the tab "associated products" the checkbox for "use default", label for attribute is unchecked. I have fixed this altering the product.js (line 404), adding ...
8
votes
1answer
245 views

For a cms/block entity, why are the subordinate data deleted from cms_page_store explicitly?

cms/block entities have a join table cms_block_store, the records in which link a given block to one or more stores. Prior to CE 1.6, the resource model Mage_Cms_Model_Mysql4_Block (link) does not ...
8
votes
3answers
3k views

Cron crashes MySQL

After moving to a new server I am getting the MySQL crash [1] issue once a day, which is coming to my email and annoying not to mention potential impact. Any hints how to debug this issue? Obviously ...
1
vote
4answers
3k views

Setting a product to in-stock raises foreign key exception

When executing the following: $product = Mage::getModel('catalog/product')->load($id); $stock_item = $product->getStockItem(); $stock_item->setIsInStock(true); $stock_item->save(); //......
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
6answers
18k views

How to retrieve full customer name with SQL?

Can anyone provide an SQL command that retrieves a customer's first and last name in one row? All my attempts at JOINs have resulted in multiple rows, with first name in one row and last name in the ...
2
votes
1answer
7k views

How to save form data to multiple tables in database

I have created a form in admin panel with various fields. Now the fields here doesn't belong to one single table. On the save, I want some values go into one particular table while others to go into ...
1
vote
3answers
2k views

Error on the dailyCatalogUpdate cronjob

I'm writing to you with a problem to which I haven't been able to find a solution anywhere. The error occurs on execution of dailyCatalogUpdate cronjob. The execution time on the server was already ...
1
vote
2answers
3k views

Custom module with custom database table and per store settings

I am creating a custom module with a custom database table using the following tutorial. This module is a custom contact form, and I would like the settings for that custom contact form to be on a ...
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 ...
8
votes
1answer
473 views

Customized Customer Grid causing MySQL table scans and filesorts aka slow performance

EDIT: I had this question originally posted on Stackoverflow that never got fully answered, I'm posting in here in hopes to shed anymore light on why the massive table scans and any means to prevent ...
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 ...
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 ...
5
votes
3answers
3k views

What is the best database backend?

Magento offers support for different database back-end. I wonder which one of the OpenSource DB Backend (like MySQL or MariaDB ...) has better performance with Magento architecture. I know that ...
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? ...