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.
3,729
questions
1
vote
1
answer
20k
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
<?...
- 2,483
6
votes
2
answers
6k
views
Disconnect & Reconnect MySQL core_read / core_write Connections
We have a process that runs within our Magento installation that:
Bootstraps Magento
Performs some actions which involve writing to a
table in mysql
Connects to a remote API, which takes a long time ...
- 5,027
0
votes
1
answer
3k
views
Best way to define status field in database tables [closed]
I find that status fields are defined in 2 ways.
int/smallint/tinyint : each value represent some status
varchar : We save status text in databse directly. In general practice Models are defined with ...
- 123
3
votes
2
answers
12k
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 ...
- 2,483
1
vote
2
answers
2k
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 ...
- 153
2
votes
3
answers
11k
views
PDO Exception error on index
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2002] No such file or directory' in lib/Zend/Db/Adapter/Pdo/Abstract.php:129
I get the following error on my local ...
- 6,104
3
votes
2
answers
1k
views
InnoDB on MySQL 5.6 with OSX/CE1.7
I am getting this error
Database server does not support the InnoDB storage engine.
with MySQL 5.6 on my MAC OSX Maverick
mysql> show engines;
+--------------------+---------+------------------...
- 6,104
4
votes
1
answer
406
views
addColumn() or addAttribute() ? What is the best for performance, migration & usability?
What is the best method of adding new attributes to the sales and/or quote tables in magento via an install script:
use: addColumn() or use addAttribute() ?
So the main questions are :
What is ...
- 113
3
votes
2
answers
229
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,649
4
votes
2
answers
5k
views
How do I get the MySQL affected rows using the Magento resource?
$conn = Mage::getSingleton('core/resource')->getConnection('core_write');
$sql = "UPDATE ...";
$result = $conn->query($sql);
How do I get the number of affected rows - i.e. mysql_affected_rows(...
- 285
0
votes
2
answers
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 ...
- 399
2
votes
1
answer
44
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 ...
- 5,027
1
vote
1
answer
2k
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
2
answers
1k
views
sales reports - directly to db
i would like to pull from database list of purchased items that within their description there is a specific text
the list of the items should contain the price they were sold at the time.
Thanks!
- 27
18
votes
3
answers
19k
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 ...
- 391
8
votes
1
answer
7k
views
How to create an index on a table according to Magento way?
How can I create an index on a existing table by using a script file? Any suggestions will be appreciated. Thanks in advanced.
- 9,955
1
vote
1
answer
112
views
Please some advanced users can help me?
Ok this is getting really serious here. I will continue explaining the code first.
This is my WSDL path connection:
$client = new SoapClient('http://xxxxxxxxxxxxx', array("connection_timeout"=>...
- 173
4
votes
4
answers
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?
...
- 6,104
0
votes
2
answers
989
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]: ...
- 441
-2
votes
1
answer
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 ...
- 149
17
votes
4
answers
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, ...
- 807
2
votes
1
answer
5k
views
Rebuilding catalogsearch_fulltext index stucked [duplicate]
I am trying to rebuild catalogsearch_fulltext index but the process always get stucked in the middle. Normally it was running 3-4 hours but now it did not finish after 19 hours. I already killed the ...
- 3,234
2
votes
3
answers
6k
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 ...
- 3,234
1
vote
1
answer
986
views
When are rows in catalog_product_super_link not revomed with product removals?
I found a strange behavior in the way Magento (EE 1.12) housekeeps the catalog_product_super_link table. When an associated simple product is deleted without being disassociated first, the entry ...
- 6,830
13
votes
1
answer
18k
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()
->...
- 27.2k
9
votes
5
answers
11k
views
Mysql access denied error
I have a common problem but the usual solutions are not working so apologies for what on first appearance may seem like a previously asked question.
I have the following error when trying to view my ...
- 2,214
2
votes
1
answer
559
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 ...
- 5,631
4
votes
2
answers
595
views
Extending newsletter functionality correctly
I have previously made a module which would allow a user to sign up for newsletters and decide on which type of newsletters they would receive e.g. promotion, new lines etc. This module was my first ...
- 1,707
1
vote
2
answers
663
views
Working with Database for Magento Certification [closed]
I am preparing for magento certification exam. As per MCD guide:
Working with Database 13%
Entity Attribute Value Model 13%
both these topic covered more than 25% of exam. but I am little ...
- 3,556
14
votes
6
answers
27k
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 ...
- 1,511
0
votes
1
answer
2k
views
Somehow all products were deleted - Magento
This one is quite unusual.
A client was bulk deleting products from Magento (latest build) and somehow nearly all products were deleted.
Here is the client's description:
Following a successful ...
- 11
1
vote
1
answer
4k
views
How to update attribute field value using mysql query?
I have to update attribute "search_sku". I have to update this attribute using sku. I have do this job using mysql. But I am not getting that how can I get this query?
Please help me.
Thanks in ...
- 315
1
vote
2
answers
763
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).
- 145
0
votes
1
answer
6k
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 ...
- 385
2
votes
2
answers
174
views
Magento setup with RAID10 [closed]
Our dedicated server is running out of disk space and RAM.
We are going to upgrade both.
We currently have a basic RAID setup, two drives, first one writing to second - not sure if that counts as ...
5
votes
7
answers
32k
views
Magento module is not creating the database table?
I am doing a module in Magento (1.7.2). In that module, I want to create a database table. I have used all the codes properly. But still, I can't see the database table in phpMyAdmin.
In /app/etc/...
- 149
2
votes
0
answers
979
views
#1062 - Duplicate entry '3-285-42-0' for key 'UNQ_CAT_CTGR_ENTT_INT_ENTT_TYPE_ID_ENTT_ID_ATTR_ID_STORE_ID' [closed]
I am picking up a site from a predecessor and I am creating a dev version of a live site.
After exporting the site via sequel pro or phpmyadmin (seperate attempts), the import process always bulks at ...
- 21
1
vote
1
answer
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,707
1
vote
3
answers
2k
views
Can't re-index after product import
I have a client call and say he could not re-index after importing 100 products. I attempted to re-index in shell and received this error
exception 'PDOException' with message 'SQLSTATE23000: ...
- 6,104
3
votes
2
answers
2k
views
How to deactivate the user automatically after 90 days if he had no purchase with in 90 days
How to deactivate or inactive the user automatically after 90 days if he had no purchase with in 90 days.
in our website
- 125
6
votes
3
answers
5k
views
Does magento clean cl tables on it's own
In the magento setup we are working on we've noticed some of the _cl tables are insanely huge, one being catalogsearch_fulltext_cl is 0.86GB in size having 30,001,369 records in it
We know that the ...
- 686
1
vote
4
answers
259
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 ...
- 178