Questions tagged [sql]

Questions about using raw SQL queries. For questions about the database in general, use the database tag instead, for questions about the Magento database access layer use the appropriate tag, like: eav, model, resource-model, collections, install-script

Filter by
Sorted by
Tagged with
23 votes
4 answers
25k views

How to log all Magento SQL?

Is there any easy way to log all query executed by Magento ? The goal is to print all the SQL executed, for example during page load
Fra's user avatar
  • 6,965
23 votes
4 answers
20k views

How to use setup scripts for your module?

I realize, you can use setup scripts for your own module by declaring them in the etc/config.xml file of your module within the <global> space like this: <resources> <catalog_setup&...
mpaepper's user avatar
  • 4,700
18 votes
3 answers
4k views

Magento running on Percona Server with XtraDB

Does anyone have any experience with Magento db running on Percona Server with XtraDB? Benchmarking, stres testing, numbers... anything
Vedran Subotić's user avatar
17 votes
7 answers
64k views

How to call Direct SQL Queries and join to collection In Magento2

I just want to call direct sql query in Magento2 as like in magento1.x
Pradeep Kumar's user avatar
15 votes
1 answer
2k views

SQL injections vulnerabilities when using SQL models of the Zend Framework

When joining tables, I use SQL models of the Zend Framework. As example I modified my actual code, but I think you will get the point: $this->getSelect()->join( array('...
JohnyFree's user avatar
  • 1,539
14 votes
2 answers
49k views

Magento 1 SQL Queries

I am in the process of setting up some reporting functionality for my company, Im gathering data from the likes of Google, Moz and our Courier. As part of the reporting I also want to get data from ...
Will Wright's user avatar
12 votes
3 answers
2k views

SQL query kills entire server

I caught the following query via mysql's SHOW FULL PROCESSLIST; which was eating the whole CPU / RAM on my server (32GB Ram, i7 hexacore CPU) rendering the entire server unusable for more than 3 hours....
koseduhemak's user avatar
10 votes
7 answers
56k views

Get full list of categories using sql

How would I format a SQL query to get a list of all categories (id, name, and url_path) from my Magento database? Alternatively, if you know the tables I need to do this, a list and some sort of ...
develophper's user avatar
  • 2,214
10 votes
9 answers
22k views

Magento 2: How to delete all categories at once?

We have been experimenting with syncing our existing database(not Magento) to Magento 2. In the process, we have created over 1000 categories, but then we rearranged all the IDs to sync with our ...
Justin Blayney's user avatar
10 votes
2 answers
8k views

magento 1.7 : Cannot reindex product_flat_data

I get the following error while trying to reindex my magento database.. Product Flat Data index process unknown error: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint ...
David Vendryes's user avatar
9 votes
5 answers
18k views

How to log SQL query of the collection?

I want to log the SQL query for a custom collection. I tried the following code. But did not work. Any suggestions will be appreciated. Mage::log($collection->getSelect(),null,'test.log',true);
Sukeshini's user avatar
  • 9,955
9 votes
2 answers
11k views

magento2 search_tmp join leads to empty results in category listing

I just imported products into fresh magento2 instance and encountered issue that category product listing block are empty, although the products successfully appear in database . I have debugged list....
Ilja's user avatar
  • 1,268
9 votes
2 answers
4k views

How to Load Model data joining 2 tables?

Suppose I have a simple Model A that target table A now I have to add some extra info so I decide to create a Model B and save those extra info in a Table B with a foreign key pointing to Table A ...
Fra's user avatar
  • 6,965
9 votes
2 answers
12k views

How to create multi select attribute for customer using install script?

I'm following many articles but i'm not able create multi select attribute. and i'm getting following error. a:5:{i:0;s:50:"Source model "" not found for attribute "exinent1"";i:1;s:3008:"#0 C:\...
sivakumar's user avatar
  • 267
8 votes
2 answers
39k views

display a sql query being generated [duplicate]

Im trying to learn how to join two tables using this tutorial but I'm returning a sql error. In order to work what ive done wrong I would like to see the query being generated, how do I display it? I'...
tony09uk's user avatar
  • 1,707
8 votes
5 answers
26k views

Magento 2: How to get product name by sql query using product ID

I want to access the product names by query database using product ID. How can I achieve it?
Nitin Pawar's user avatar
  • 1,546
8 votes
2 answers
771 views

Decrement value instead of setting it like `number = number - 1`. Is it possible in Magento?

I need to decrement a value with an atomic database operation, is it possible using Magento models? setNumber($number) works like number = $number, but I need it to be decremented in SQL query. Is ...
tmm's user avatar
  • 359
8 votes
1 answer
8k views

Remove category attribute

I inserted 2 category attributes using a module with the file mysql4-install-0.1.0.php in a sql folder: $installer = $this; $installer->startSetup(); $installer->addAttribute('...
Claudiu Creanga's user avatar
8 votes
4 answers
2k views

Example of Code and Configuration Needed for a Simple Magento 2 CRUD Model?

How can I, an end-user-programmer, create a new "CRUD" model in Magento 2? That is, I want to create the PHP class files and XML configuration files that will Let me Create, Read, Update and Delete ...
Alana Storm's user avatar
  • 44.4k
7 votes
3 answers
27k views

Geting Fatal error: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

For a long time my Magento application was working fine. But suddenly it started showing this below error. Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error:...
Harish Kumar's user avatar
7 votes
1 answer
2k views

What is the 'data' folder in magento modules?

Recently I downloaded some free extensions in magento CE 1.7. One of them had a data folder inside the module folder structure. It contained some data upgrade scripts. At the same time the module had ...
Sukeshini's user avatar
  • 9,955
7 votes
1 answer
2k views

How to optimize the query: the product qty bought with each of the products

I need to collect the data about the products which are bought together with the existing product on the store (in case there are any). The data should be taken from the table, storing the order items....
Siarhey Uchukhlebau's user avatar
7 votes
4 answers
39k views

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'core_store' doesn't exist

Appreciate if anyone could help me out, as I've been searching for a solution for hours, but they are all pretty vague. When I attempt to access my website receive the following error: There has ...
shopstelle's user avatar
7 votes
3 answers
8k views

How can I use UNION for 2 collections ? Or merge collections ? (for use in adminhtml grid)

First of all, what I'm trying to do : Get a collection with customers who does not have orders yet and newletters subscribers who dot not have customers account. In mysql FULL JOIN and FULL OUTER ...
Thomas Seres's user avatar
6 votes
2 answers
13k views

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

Running on Magento 1.8 We are experiencing this error while creating product in our backend. It hinders the process of creating products and we have to keep trying in order to have the one product ...
rzrz's user avatar
  • 61
6 votes
2 answers
4k views

Array of product ids for a given category (respecting is_anchor)

I am trying to find the fastest way of getting a list/array of product id's for any given category id. I understand this is quite fast $category = new Varien_Object(array('id'=>$_cat)); $positions ...
ShirtsofHolland's user avatar
6 votes
2 answers
3k views

How does magento create bundle option selections?

I've been looking at programmatically creating bundle products, and got stuck on adding a selection to the options. The problem is that when you create a new selection (Mage_Bundle_Model_Selection) ...
Benubird's user avatar
  • 1,116
6 votes
2 answers
14k views

Magento 2 cron_schedule table increasing and CPU 100%

I updated to 2.2.1 from 2.1.10 and that looked good. But my CPU is increasing a lot and it went to over 100% in a few days. The problem is the cron jobs because also the table cron_schedule was very ...
Jilco Tigchelaar's user avatar
6 votes
3 answers
15k views

how to get product specific attribute codes & values using raw sql query?

The following query will give product all attribute codes & values.but I want specific attributes.and i want to store that result into one table.the table format like flat catalog product table. ...
Sivakumar K's user avatar
  • 2,013
6 votes
1 answer
4k views

Best sellers block from joining product collection with the monthly bestseller aggregation

So we're trying to implement a best sellers block on the Magento homepage using this example provided by Inchoo... Only problem is though, when I dissect the SQL select query, which is the below, the ...
zigojacko's user avatar
  • 1,754
5 votes
2 answers
13k views

Magento 2 : How To Create UNIQUE KEY Field in Custom Tabel

I want to create custom filed say myname with constraints UNIQUE KEY in custom extension table. In Magento1 we can define like this : UNIQUE KEY `myname` (`myname`) How can I do this in Magento2?
Dhiren Vasoya's user avatar
5 votes
1 answer
3k views

row_id vs entity_id in magento 2.3 EE

In Magento 2.3 EE, entity_id is missing in EAV tables, instead row_id is available Please explain the difference between row_id and entity_id and the removal of entity_id in EAV tables
manokarthick karthick's user avatar
5 votes
1 answer
1k views

Can I use a .sql file in my Magento module installer?

I have a large SQL file. Can I use this in my module installer?
Kurdt94's user avatar
  • 149
5 votes
2 answers
4k views

using sql queries is good practice in magento 2

using SQL queries is good practice in Magento 2 over ORM which is best one to while customizing 1)Security 2)Speed ORM vs SQL queries which one giving Security and speed while ...
prabhakaran7's user avatar
  • 1,087
5 votes
1 answer
6k views

Lock wait timeout exceeded

Quite often we get the following error in Magento when we try to make a change to one of our Categories. SQLSTATE[HY000] [1205] Lock wait timeout exceeded; try restarting transaction, query was: ...
Holly's user avatar
  • 4,873
5 votes
1 answer
4k views

Magento 2 - SQL Injection

How to avoid sql injection when running a native sql query with parameter input? e.g. $sql = "Select * from eav_attribute where attribute_id = {$attribute_id} "; $result = $this->_connection->...
TFS's user avatar
  • 4,319
5 votes
1 answer
10k views

Query to return customer order history?

I am trying to dump historical customer order data from a legacy ecommerce site that we restored from an AMI. Ideally this would grab customer ID, email, order date, product SKUs ordered, and order ...
Alex's user avatar
  • 53
5 votes
2 answers
9k views

Add Field to Customer Registation Form Magento 2

I want to add a field/attribute to Customer Registration Form on frontend. And I create a folder in my module , the folder is "Setup" and I put inside a file "InstallData.php" but this doesn't work. ...
Riccardo Amadio's user avatar
5 votes
1 answer
919 views

Magento getCollection , SQL injection

I installed an extension for a customer of ours and I'm just learning about SQL and SQL injection, and this extension uses all kind of AJAX calls. However, I checked the code and some of the AJAX ...
Marcel's user avatar
  • 243
5 votes
1 answer
2k views

Safely update SKU from DB Magento2

In a store of mine, built on Magento 2.1.x I need to perform a bulk update on product SKUs. I am thinking about doing an UPDATE directly on the DB, on the catalog_product_entity table, since I ...
giani.sim's user avatar
  • 1,473
5 votes
1 answer
2k views

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CONCAT( REPEAT [closed]

When i go to Magento Admin and then SM Menu Item Manager: There has been an error processing your request SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CONCAT( REPEAT( "--- ", (...
nonE's user avatar
  • 51
5 votes
1 answer
292 views

How is Varien_Db_Adapter_Pdo_Mysql::orderRand() optimized?

Magento comes with a method to randomly "order" collections: $collection->getSelect()->orderRand(); It takes an optional parameter $field, that is eventually used in ...
Fabian Schmengler's user avatar
4 votes
3 answers
6k views

Magento 2 adding constraints in the install script

I'm trying to build a custom module in Magento 2 v0.74.0-beta1. This module should contain 2 simple entities, each one with its table and a many to many relation between them through a third table. ...
Marius's user avatar
  • 198k
4 votes
3 answers
5k views

How to join a calculated column from a table to collection

I have a table name stat_customer look like this: I want to add a column page_view to customer collection to show at grid page. page_view is total page view of product page for each customer. My SQL ...
HungDQ's user avatar
  • 802
4 votes
2 answers
14k views

Magento 2: How can I remove all products URL rewrites?

I've used a script to TRUNCATE all tables related to products, so removing all products in my store. Now I want to remove the more than 4k URL rewrites related to those products removed, though, ...
Rafael Corrêa Gomes's user avatar
4 votes
1 answer
6k views

SQL Query for custom attribute

I have create custom attribute in product upc I want custom query (sql) to get SKUs and UPCs Can anyone tell me the related tables from where I can get data, or if anyone can share query that would ...
Muhammad Anas's user avatar
4 votes
1 answer
2k views

Rerun an extensions SQL files

Whats the best way to rerun an extensions SQL files for example mysql4-install-0.1.0.php mysql4-install-0.1.1.php mysql4-install-0.1.2.php mysql4-upgrade-0.1.0-0.1.1.php mysql4-upgrade-0.1.1-0.1.2....
Goose84's user avatar
  • 2,611
4 votes
4 answers
7k views

Updating stock causing foreign key constraint fail

So, I'm trying to update product quantities programmatically. On a standard magento install, it works flawlessly, but on the actual magento shop I'm developing it for, it fails with a SQLSTATE[23000]: ...
Tehnix's user avatar
  • 145
4 votes
3 answers
5k views

Module Database setData() makes UPDATE Query instead of INSERT?

in my own module I want to INSERT the orderId to my own DB Table. $data = array( 'order_id' => $order->getId() ); $model = Mage::getModel('magic/test')->setData($data); try { $model->...
user27294's user avatar
4 votes
2 answers
3k views

Magento indexing is failing, SQLSTATE[23000]: Integrity constraint violation

For some reason Magento indexing is failing, we haven't changed anything in the website.... And that is the weird thing! The error in shell using indexer.php: $ php indexer.php --reindex ...
xvilo's user avatar
  • 201

1
2 3 4 5
14