Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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

2
votes
0answers
16 views

Magento Installation Error: SQL max_allowed_packet value

I am trying to install Magento 2.3.0 CE with Sample data, but the installer fails because my hosting have the max_allowed_packet set to 16M. In this post of someone with the same problem the issue is ...
2
votes
1answer
36 views

SQL Error when I try to reindex Magento 2

I have an issue in Magento 2.2.7 where my site has the message "One or more indexers are invalid." and looking at the indexers I see the following: However when I try to run php bin/magento indexer:...
0
votes
1answer
28 views

Clearing database logs in Magento 2

We've recently migrated from Magento 1 to Magento 2 and it seems that there is a large amount of information in our database files. We'd like to try to mitigate this by clearing the log files but ...
1
vote
1answer
12 views

SQL Query Newsletter subscription status with custom tables

I currently have the following script that someone else wrote for me that exports custom Magento data to a CSV file. It takes data from the two tables 'perfume_fragrance' & '...
1
vote
0answers
20 views

magento 1.9 CE error when duplicating a product

When I use the method to duplicate an object sometimes I get this error message, the product is created: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-0-0-1-4-24884-0' for ...
1
vote
1answer
18 views

Magento 2.2.5 Delete Url Rewrites older than 1 Year

Is it possible to delete Url_rewrites older than one year? We have migrated from Magento1 to Magento 2 and kept all URLs for SEO reasons. BUT the table in Mage1 and Mage2 is really huge. Now we want ...
2
votes
1answer
24 views

Magento 1.9 - Change Tax in created invoice

Unfortunately somebody changed the tax class for many of our products. We needed 2 weeks to recognize that. Now I got 150 orders with wrong tax. I need to fix that and resend all invoices to the ...
1
vote
1answer
26 views

Generate Magento 2 Product Feed with MySQL Query

I hope you all having a nice day. I am wondering is it possible to generate below feed with SQL on Magento 2 Database? I want to run this query from Google Apps scripts using jdbc:mysql. Expected ...
1
vote
2answers
75 views

error on running raw sql query in upgradeData.php magento 2

I have created my own module and in UpgradeData.php script I am writing my own, mysql query but it gives me an error when I run bin/magento setup:upgrade to run the setup script files Recoverable ...
0
votes
1answer
23 views

how to enable disabled products via query?

Hi I want to enable all products I have in my Magento 2 store, is there any query to enable all products we have using a single sql query ? Thanks
0
votes
1answer
20 views

Magento Freezing because of SQL Query

Currently this query is slowing the entire site down: Spawns in irregular times: SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`...
1
vote
0answers
13 views

Magento 2.2 - Want to implement blackfire for community edition

I have heard about blackfire php profiler and automated performance tool that can be used in three environments. If i wanted to implement in Magento 2.2 based website using nginx server then is it ...
0
votes
1answer
74 views

Error SQL[HY000] Error 1205 with import_products

I have a problem with a job which manage import products and customers every night. Randomly return an error when import products from SAP. If u see the picture u can see the material "SB-FHA125A-...
3
votes
1answer
63 views

Do bound parameters get escaped using the Core/Resource model?

Consider the following example: $connection = Mage::getSingleton('core/resource')->getConnection('core_read'); $email_address = $_GET['email']; $sql = 'SELECT * FROM `foo` WHERE `email_address` = ...
0
votes
1answer
32 views

Magento2 use Multiple LIKE operator in Join Query

I have used Join query,in which i'm able to fetch only one row by below query where $productId = 10 $collection->getSelect()->where('paproduct.product_id = (?)', $productId); I want to use ...
0
votes
1answer
37 views

Deleted ShipperHQ module causing error in “All Customers” section of Magento 2

I'm currently trying to build a Magento 2 site to replace an existing Magento 1 site. As we currently use the MatrixRates Module we decided to give ShipperHQ a try but ultimately decided against using ...
0
votes
0answers
21 views

How to run query subdate(now(), INTERVAL 1 DAY) in Magento Helper

in Magento 1.9 Community, I have this query created_at >= subdate(now(), INTERVAL 1 DAY) Group By product_id order By sum(qty_ordered) desc How do I do this in Helper/Data.php? Currently this is ...
0
votes
0answers
32 views

How can I convert this raw sql segment into a function to use in magento 2?

This sql segment works fine but meqp validator reports the possible raw sql error 'select `review_id foreach ($post['cons'] as $key => $value) { $array[] = $key; } $inCond = $...
0
votes
1answer
21 views

Tax_class_id per product (through SQL)

hoping you are well, I need your help with the following topic: I want to make a report that extracts all the products in MAGENTO that does NOT have tax_class_id = '2' (taxable goods) and although I ...
0
votes
0answers
13 views

how can i change the value of state column in code(sales_order) table from new to processing ?

Hi i have made a relation between two order statuses in my magento 2.2 system , the first status of the order is "pending" and the second is "processing" , i goto (stores->settings->order statuses) ...
0
votes
1answer
19 views

Extension installed - SQLSTATE[42S02]: Base table or view not found

I have a rather odd problem I haven't encountered before. I installed the Premium MatrixRate extension on Magento 1.9 according to their guide. When I go to Configuration > Shipping Methods (to try ...
0
votes
1answer
75 views

Delete All products

I have recently performed a data migration from my M1 store to my Magento 2.3 store. Along with the migration i carried over all products, somewhere around 5,000. A lot of these products are old and ...
-1
votes
1answer
18 views

Magento 1.x addFieldToFilter using AND instead of OR (how can I join search using AND)

I am trying to use AND and OR based on user input. OR = success But I an unable to setup AND queries for addFieldToFilter question: how can I add the second (looped filter) using AND to join $...
0
votes
2answers
108 views

How to mass update custom dropdown customer attribute to all customers? PHP or SQL

I created a new customer attribute (Drop Down) and added it to the registration form. Attribute details found in eav_attribute table: Everything work as expected, attribute is added to the customer ...
2
votes
1answer
37 views

Remove all customers in a certain group

I have just migrated my M1 site to a newer M2 site, however, this new site will be for wholesale only customers, each of whom are assigned to a different group to reflect their prices. how can I ...
0
votes
1answer
19 views

addAttributeOption intger value not add in database using code

I have try to addAttributeOption() integer value add in database using my code. But i have facing on error message when i have add integer value in CSV (1) Not Working CSV:- 70024,70014,70010,70624,...
1
vote
1answer
34 views

Export image information via SQL

I'm trying to export a set of image information from Magento 1.9 via SQL. I'm trying to get an output format like this: sku, value, position 1234, a/b/example.jpg, 2 Tables involved: ...
1
vote
1answer
65 views

Exception message: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

2018-11-20T09:31:12+00:00 DEBUG (7): Exception message: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0-hunderefleksvest-str-m' for key '...
0
votes
1answer
57 views

cron Scheduler requests remains in pending , eventually site crashed why?

Hi we have a site on magento 2.2 version , the site usually goes down for about 3 to 4 minutes after within every 4 or 5 hours , than goes up when cron scheduler finishes it job . when we dig in ...
2
votes
2answers
229 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....
1
vote
2answers
599 views

How to get products name and sku and url key using sql query in magento 2?

How to get products name and sku and url key using sql query in magento 2 ?
1
vote
1answer
38 views

Magento 2 Order number increment

By default, in Magento 2 the order number starts from 100000001. But if I want to increase it by 55555. So it would look like 100055555, what would be the SQL query to directly modify the table ...
2
votes
1answer
180 views

Magento 2.2.5: How to join a table with a repository

I am trying to join table 'cataloginventory_stock_status' with order item table using OrderItemRepositoryInterface and searchCriteriaBuilder. $searchCriteria = $this->searchCriteriaBuilder ...
2
votes
1answer
41 views

How to get sum of rows of different id's at a time?

what would be possible query of in sql ? if we want separate sum of each different item id's with multiple where clauses like if we have a table persons : ----------------- id | Amount 1 | 1.0 1 | 2....
2
votes
2answers
146 views

Magento2 Query Param Binding

->having( 'COUNT(*) ' . '<= 3 ' ) Hardcoded value 3 need to come From: This is the query to return most viewed products biggest views number: SELECT COUNT(report_event.object_id)...
1
vote
1answer
50 views

Making MySQL accessible to remote IPs

How do I make MySQL accessible to only certain specified IP addresses? I'd like to access the database through a remote server
3
votes
1answer
283 views

Website down: main.CRITICAL: SQLSTATE[28000] [1045] Access denied for user '(removed)'@'localhost' (using password: YES)

The following is the complete error in the log: main.CRITICAL: SQLSTATE[28000] [1045] Access denied for user '(removed)'@'localhost' (using password: YES) {"exception":"[object] (...
0
votes
1answer
48 views

SKU Report for null attribute values

I am getting all product sku's that does not have a attribute value 'image_link'. I have written this code in magento : $products = Mage::getModel('catalog/product') ->getCollection() ->...
2
votes
0answers
27 views

In how many tables, product custom attribute entries saved?

Suppose I have a product custom attribute 'brand', after choosing one of the brand options & saving the product in how many tables this product attribute will be saved? attribute "brand" type is ...
1
vote
1answer
40 views

How would I go about updating qty through SQL?

We have a custom application being written that communicates with Magento, we're looking to update inventory at a certain frequency and are looking to do this through SQL communicating directly with ...
0
votes
0answers
387 views

Cannot add or update a child row: a foreign key constraint fails

When I ran the manual query:- INSERT INTO `catalog_product_entity_varchar` (`attribute_id`, `store_id`, `entity_id`, `value`) VALUES (611, 0, 11, 559); from the database then DB then getting ...
1
vote
2answers
676 views

User already has more than 'max_user_connections

Yesterday I get issue : [2018-09-09 13:04:02] main.CRITICAL: SQLSTATE[HY000] [1203] User user_database already has more than 'max_user_connections' active connections {"exception":"[object] (...
0
votes
1answer
70 views

Wrong attribute set ID Mage_Eav_Model_Entity_Setup

I have been receiving this error. Error: mysql4-install-1.0.php" - Wrong attribute set ID";i:1;s:1016:"#0 /Applications/MAMP/htdocs/flicdes/app/code/core/Mage/Core/Model/Resource/Setup.php(644): ...
1
vote
2answers
43 views

How i can call SQL statement directly with magento 2

how to call SQL statements directly to database with Magento 2, This is not recommended, but in some situation we need to do this, for some very special tasks
0
votes
0answers
28 views

'magento.catalog_product_index_group_price' doesn't exist' magento 1.9

Please, I am a newbie in magento, just got a job to maintain a client project,each time I try to index, I keep receiving this error. `magento.catalog_product_index_group_price' doesn't exist` Please,...
1
vote
1answer
31 views

Show all products from a specific store? [closed]

Is it possible to show all articles from a specific store? I know I can filter for website, but it does not seem to be possible to filter for store. Maybe there is a SQL query which I can use?
0
votes
1answer
42 views

What tables are updated when a new product return is made?

I need to know because when a customer is requesting a product return on the frontend there are no notification that shows up and let our employee know that a new product return has been created.
0
votes
1answer
128 views

How to delete all orders

Hello i want to delete all order from magento 2.2.5 i used that code but it didnt work... any advice? SET FOREIGN_KEY_CHECKS=0; # Clean order history TRUNCATE TABLE `...
1
vote
1answer
35 views

Magento 1.9 : I have a very strange Magento problem

Error : SQLSTATE[42S02]: Base table or view not found: 1146 Table 'i1115580_mg3.sales_flat_shipment_track' doesn't exist Query: SELECT track_number FROM sales_flat_shipment_track
0
votes
1answer
51 views

Retrieve custom fields from customer table using SQL

I have two custom fields in my customer table, namely nickname and sponsor. I would like to create an sql query to select the two custom fields that I have in the table. What kind of query should I do?...