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
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
547 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
190 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
236 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
602 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
411 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 ...
1
vote
2answers
1k views

Backup large database and avoid website slowness and downtime (and keeping data integrity)

My goal is to be able to backup a large database on a fairly popular Magento site without causing performance problems to the live site. Data integrity is also important to me. Hoping that somebody ...
3
votes
2answers
165 views

What is necessity of two separate database table for grid and normal

In Magento CE 1.7.0.2 in database there are two separate tables for example In case of order, data store in sales_flat_order and sales_flat_order_grid tables but if you see column then ...
1
vote
1answer
1k views

Restoring Catalog & Product Data

We Have a problem where by a category was deleted and then recreated causing problems as the id changed, Is it possible to restore just the product tables as do not want to loose changes which have ...
0
votes
2answers
984 views

An error in my database when i copy my website in an other host

I copied my website from my linux server to windows server (wamp). I export the database and i import it to my windows server.When i try to access my magento site i have this error. SQLSTATE[42S02]: ...
2
votes
3answers
5k views

Get number of products faster

I get the number of products: Mage::getModel('catalog/product')->getCollection()->count() But this takes >10 seconds. Maybe because indexer is running or what ever. Can I query a table ...
1
vote
2answers
528 views

It is possible to save only one column in a database table?

I want a helper which will save only one specific column in database table(not eav).
0
votes
1answer
5k views

Problems with reindexing products

Magento tells me that I need to reindex, but Product Prices fails with either "There was a problem with reindexing process." or "Cannot initialize the indexer process". I have tried removing ...
2
votes
1answer
3k views

Magento site shows error SQLSTATE[HY000]: General error: 5 Out of memory

One of our store show following error and stop working at all. SQLSTATE[HY000]: General error: 5 Out of memory and Mysql / Innodb Error InnoDB: ERROR: the age of the last checkpoint is 943460 [...
1
vote
1answer
2k views

save to multiple tables

For the last four hours I have been trying to save data to two tables. I realise that this should be a simple task, due to Magento's style of saving data, but I cannot work out how to do it. On a ...
1
vote
4answers
247 views

Upgrading Magento From Version 1.6.2 to 1.7

Im currently running 1.6.2 now for about a year, this is from day one introductory into magento, so over the year I have gained more knowledge with magento, and I would like to move over to new ...
0
votes
1answer
1k views

select row based on foreign key

How do you select a row based on that tables foreign key? My table and primary key is set and works as expected but now I need data from table to based on that foreign key. I have managed to get it to ...
1
vote
1answer
370 views

Magento Bundles (Long MYSQL Query)

I have been working with magento, our store only uses bundle products, and as we have started to have a large amount of data (10,000 simple products) (around 1500 bundle products) I Have noticed that ...
0
votes
1answer
117 views

Installing Magento Community Edition (free version) as two web sites in Apache

I need to install Magento Community Edition (free version) as two web-sites in my web server (Apache 2.2), one MagentoCE with the sample data and one Magento CE without sample data. How to achieve ...
0
votes
1answer
1k views

Selecting a table and column

I am trying to get data from a custom table which I have added to the database. But when I call data from it I do not get anything from that table. My page displays data I had previously requested, ...
0
votes
1answer
2k views

Products tagged as 'out of stock' regardless of admin settings

When our site was created for us, it was set to manage inventory levels. When the developers handed it off to us, I turned inventory off in the System Configuration. Since then, the items I've added ...
1
vote
1answer
8k views

displaying data from two tables in one grid

I have a grid for my admin module and it works fine, but I now would like to add more data onto that grid, but the additional data is held in a second table in my database. To display this additional ...
1
vote
2answers
5k views

Unable to reindex Product Prices - Foreign Key Constraint Fails

I've just recently upgraded my magento store from 1.4.2 to 1.7.0.2.. And The upgrade went smoothly but when I try to reindex data after the upgrade.. It fails on the Product Prices.. I've tried using ...
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 ...
2
votes
1answer
3k views

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away on running cron job magento

I am working on site .suddenly i got this message on running cron job multiple times.i check my website for the same error but it works.I remove the cache and try to exit the code but after the error ...
2
votes
0answers
706 views

MySQL fails to start [closed]

I was testing a Magento shop and suddenly mysql stopped working and fails to restart. Any ideas how to get it working again? I have magento running on Apache and Varnish if that's useful information. ...
0
votes
1answer
1k views

Sku field null in category_product_flat tables

We have a store, Magento 1.6 with circa 4500 products. Half of the products approx are having this issue: Symptoms... Admin Product Grid -> sort by SKU causes a fatal error. Admin Product Grid -> ...
2
votes
1answer
1k views

How to create delete url to delete row from table

How to create an URL for a link (on frontend),that ,when clicked, will call a function that deletes a row from the table. Manually it works :) public function deleteMessage($id) { Mage::...
1
vote
1answer
1k views

Update Product Names To Title Case

After exporting my previous database, allot of my product names where upper case. I now want to run a sql query to update the product names to title case. What is correct way to do this
1
vote
1answer
727 views

How do you set Allow Countries directly in the database, programmatically or using n98-magerun?

I have a list of over 200 countries I need to allow for an EE 1.13 site. I'd prefer not to do this through the admin interface because it is tedious and I am error prone. Is it possible to set Allow ...
0
votes
2answers
1k views

Tables indexes - too little or too many

we have a Magento installation @ Mage 1.7. We've been around since 1.3 ... What we are seeing more and more is that certain indexes appear that are no longer in Mage 1.7. For example we have an ...
0
votes
1answer
204 views

How to update the child table

I am currently struggling through my first magento project and it seems to be leaving me with asking more questions than it has answered. My current hurdle is adding default data to a child table. I ...
1
vote
1answer
1k views

Magento - Can't connect to Mysql server error frequently

We have a dedicated server with a virtual instance running the application server with Apache, PHP and a virtual instance with DB on it. We are running Magento 1.7.0 version on the application server ...
0
votes
1answer
633 views

addFieldToFilter doesn't return the right product for selected attribute values

I am currently programming a product configurator module for Magento. Everything is working fine so far besides the core functionality: choosing the right product from the database for the selected ...
1
vote
1answer
3k views

Not saving data to database in magento

Hi i write valid queries for data insertion but not successeded.If i print that query and excute it on sql server it works.I didn't figure out why this not works. This is my table structure. CREATE ...
2
votes
1answer
2k views

Reorder problem with custom quote attribute

I've defined a custom attribute called productcomment on sales_flat_quote_item and in sales_flat_order_item and the problem is when i want to reorder an specific order, the values are not copied. The ...
0
votes
1answer
4k views

“Database connection error” when installing 1.7.0.2 on Ubuntu

I'm installing Magento version 1.7.0.2 on an Ubuntu VM, hosted in Azure - I'm getting "Database connection error" in the Magento Installation Wizard. I've installed PHP & MySQL as instructed here....
0
votes
1answer
4k views

Help connecting Magento to a third-party REST API

I need to update Magento products using a third party API. What is the best method of doing so. Should I trigger an API call when an event/observer hits on product detail page? There is no OAuth yet, ...
0
votes
1answer
569 views

Update Option Type Checkbox to Dropdown

I want to edit my Magento option type for 181 products, changing checkbox and radio to dropdown Can i just create a mySQL query to change field in catalog_product_option, replacing checkbox and radio ...