Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

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
2
votes
1answer
3k views

How to add Custom fields to sales_flat_order in magento [duplicate]

I am new to magento, If I want to add custom fields to sales_flat_order table how Can I do? sales_flat_order sales_flat_order_address sales_flat_order_grid sales_flat_order_item ...
1
vote
1answer
3k views

What's the better way to set an initial customer increment id?

This SO Question has two answers: Set the AUTO_INCREMENT for the customer_entity table. Set the increment_prefix and increment_last_id for the customer entity in the entity_store table. The second ...
2
votes
1answer
1k views

Magento “INSERT IGNORE INTO”

I'm looking for a "INSERT IGNORE INTO" option with Magento. I've created a custom table, and this table is updated every time an order is successfull (this is working). However, I want it only to ...
1
vote
0answers
133 views

Is there a way to mass reduce prices of specified products? And their tier pricing too? [closed]

We have a live catalogue of just over 125,000 products currently. We are looking to reduce the selling price of all products that sell for £78.99 or less by exactly £5.00. We’ll then be adding a £4....
3
votes
3answers
665 views

Error while assigning product to category?

I am trying to assign category to product through a simple script and it's throwing an error i have tried doing through admin panel and it works there but as i have to do this for thousands of ...
3
votes
1answer
9k views

User Error: Some transactions have not been committed or rolled back

I have executed below query in my magento database. CREATE TABLE IF NOT EXISTS `catalog_product_super_attribute_image` ( `value_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Value ID', `...
1
vote
1answer
677 views

Magento Create Category While Installing a Module

While creating the category via installer, category not created but the table is created, below i added my installer script, any wrong in this script? $installer = $this; $installer->startSetup()...
2
votes
1answer
208 views

I want to create a custom model that pulls data from an external system via web service calls

I want to be able to page through this data and I was wondering if Magento supports the ability to force a new call into the model whenever a user clicks on the next button, or the previous button. E....
4
votes
4answers
3k views

Drop tables in Magento

It is ok to drop tables that end in _cl and _tmp Does anyone have a comprehensive table clean up list? For example, can I clean up all the sales_flat_quote tables that are more than 30 days old? ...
8
votes
1answer
497 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 ...
1
vote
4answers
1k views

Advanced search is partially broken after MySQL upgrade to v. 5.6

Maybe someone have been experienced the same problem and have solution for this? My hosting provider was upgrading recently the MySQL version to 5.6 and after that the Advanced Search can not find ...
0
votes
1answer
530 views

override table_prefix in custom module

I develop a module with custom connection to custom database, and i'm using it through Mage_Core_Model_Abstract, Collection etc. But in exception.log i see Exception message: SQLSTATE[42S02]: Base ...
1
vote
5answers
11k views

how to fix database inconsistency for magento 1.7.0.2

I've run into the reason why I am unable to import a backup of a magento store into my development environment and its quite an obvious one: There are multiple instances of foreign key constraints ...
1
vote
1answer
1k views

Is there a way to speed up multiple Joins in Magento Order Grid?

I am having an issue with load times in the Sales_Order_Grid after join creating three joins to add additional columns. This has significantly increased loading times and I was wondering if there is ...
0
votes
1answer
720 views

Custom module mysql4 model _beforeSave function throw error

I am creating custom module ,whose resource model is mysql4.In mysql4 file i am trying to call _beforeSave but it show error beforeSave() should be compatible with that of ...
-2
votes
1answer
91 views

How do I connect to database from admin?

I created a custom table in the database called "referrals" where I store generated referral codes and link them from user to user. Upon registration, it adds the data just fine and when you log in, ...
6
votes
3answers
9k views

Add to cart / checkout performance issues

I am getting slow page loads (upwards of 7 - 10 seconds) when a visitor adds a product to their cart. The rest of my site loads quickly having already implemented: APC, MEMCACHED and just recently ...
2
votes
0answers
163 views

Dedicate Magento ADMIN virtual machine and Percona Cluster [closed]

i want to add to my current infrastructure a dedicated virtual machine only for /admin. In the same time, i'm adding a node to my percona single database, to create a master/master configuration. My ...
2
votes
0answers
582 views

US Tax Rates based on Zip+4 and performance [closed]

I have been looking at various solutions for applying proper sales tax to orders in the US. Looking at one of the simplest solutions, I worry that MySQL performance will struggle. This solution is ...
4
votes
1answer
5k views

Find out Magento version having only database dump

My problem is to find out the exact version of Magento (should be one of CE) having only a database dump and no filesystem. So the method with the backend / Mage.php is not an option.
0
votes
1answer
177 views

New Database Table Name

After Install Magento I can see TABLE in database like: admin_assert admin_role admin_rule admin_user For example, I has new CREATE TABLE as listed below, how could I point Magento to READ this new ...
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 ...
0
votes
1answer
3k views

Mass replace SKU values from another attribute via mysql

I need to mass replace all my store SKU values. The values I want to set are from another attribute called InternalSKU. Attribute SKU is contained in table catalog_product_entity and the Attribute ...
0
votes
2answers
108 views

Where inside module to save single for of single field?

I need to save a date of last imported data. This is one single row of single field. Is there any other way or do I need only for this single row and field create new table?
1
vote
1answer
3k views

Which table contains settings for countries available for shipping

I'm trying to figure out, which database table is storing settings for enabled countries for shipping. I have to disable some countries with sql install script
1
vote
1answer
1k views

delete an image from media folder

Is there any method to delete files in magento? I have a custom module using which I can upload images to the folder media. Now I am planning to provide a delete option under each uploaded images(...
0
votes
1answer
57 views

Integration with a development server

We are currently in the process of redoing our main store front. I have set up a development server which currently replicates our production server. We would like to use the development server to re-...
0
votes
1answer
1k views

Delete Magento database or site settings config?

I want to start my Magento installation from scratch. I'm unfamiliar with Magento, but in Drupal you can delete a settings.php and you db and it will be as if no Drupal has ever been installed. Then ...
0
votes
1answer
317 views

Copy array from product to quote item and order item

I am copying single valued attributes from product to quote item and converting to order item. But is it possible to do that with an array? If yes - what's the process and what type should the table ...
-2
votes
1answer
5k views

Magento connect to database from a module?

I am just a newbie in magento. Currently I am developing a small extension. In that I have put some ajax request. So for that I need to connect to the database in that file. I want to fetch the data ...
0
votes
1answer
2k views

MSQL Query to list Simple Products for missing images

Line 3 is apparently incorrectly written. Can a MySql expert offer fixed query? Thanks! SELECT `rscno` FROM `catalog_product_entity` WHERE `type_id` = 'simple' AS a INNER JOIN `eav_attribute` AS ...
4
votes
2answers
5k views

Add new column to an EAV table `catalog_product_entity`

I tried to add new column to the table catalog_product_entity via a setup resource. But when i try to save a value to table (using setMyColumnName()), the value is not reflected in the underlying ...
1
vote
1answer
279 views

Do have sales_flat* tables a lifecycle like catalog_product_flat* tables?

I'm quite new to magento development and could not find any information on this theme. I know that the catalog_product_flat* tables are indexed and are only existing, if magento is set to use flat ...
1
vote
0answers
1k views

Possible Bug - Hidden tax amount on quote and order item but no discount amount [closed]

I came accross a problem with one of our customers order on magento 1.7.0.2. There seems to be two problems. In backend there 5 items. 3 of the items has discount applied on them. 2 items do not ...
3
votes
1answer
3k views

Is it safe to modify database table column datatype directly?

I am able to change Catalog Price Rules' from_date and to_date from "date" datatype into "datetime", by overriding the classes: Mage_Adminhtml_Block_Promo_Catalog_Edit_Tab_Main ...
2
votes
1answer
544 views

Mysql joins 'e' v 'main_table' - flat v eav

When you guys are doing mysql joins on product and category collections how do you plan/deal with the differences in eav and flat collection? The main thing i see is that eav using 'e' for the column ...
2
votes
1answer
39 views

Non-volatile data store

I often find myself wanting to store a simple piece of information such as 'last exported id' or 'last import date' for retrieval at a later point in time. This is the kind of information that will ...
1
vote
1answer
62 views

How to create a model with submodels?

I have 4 tables: Animals -- Race ---- Color -------- Name I know how I can load each table one by one but since the tables are related I want to load all elements and the whole hierarchy at once. ...
0
votes
1answer
182 views

Sometimes products are missing category ID's, which they had before

I have a problem with Magento Community 1.7.0.2. Sometimes we find out that products don't have a category ID anymore (but they had before). We have seen this multiple times now (3rd time in 1 year or ...
1
vote
1answer
235 views

Huge MySql Query processed from frontend

We have several web servers and MySql Server. Once in a while there is a huge query that being requested from one of the web servers (which means it comes from frontend). We have a full page cache ...
1
vote
1answer
570 views

What is the meaning of the “mage_catalog_compare_item” Database-Table in Magento 1.5?

In my Magento Database I have a table named mage_catalog_compare_item. For what is this table good? It has some entries and the columns are: +-------------------------+----------------------+------+--...
3
votes
1answer
404 views

innodb_file_per_table setting on or off?

I've run into the bloated one innodb tablespace before and since then have opted for a file per table in innodb, innodb_file_per_table = 1. Because Magento has so many tables they say to only turn it ...
1
vote
1answer
18k views

What is causing SQLSTATE[42000]: Syntax error or access violation: 1064 when trying to Group Tables?

I have been trying to figure out what is causing the exception when attempting to create a join for my Collections and group them with the main_table. Here is the code from my Collection.php <?...
3
votes
2answers
11k views

How can I resolve Foreign Key constraint 'SQLSTATE[23000]: Integrity constraint violation:

I have been trying to create a simple module to enable order comments at checkout. I know there are a number of free extensions that do this but I need to add additional functionality so decided to ...
0
votes
2answers
4k views

Can't save custom data to “sales_order” table

I'm running Mage 1.4.0.1. With a mysql setup script, I have created a new column "asyncupload_validated" (tinyint(1) not null default 0) to the "sales_order" table. I try to set this data to 1 and ...