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
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
319 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
280 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
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
189 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
596 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
409 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
163 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
526 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
367 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
705 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
998 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
721 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 ...