The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
0
votes
0answers
21 views

Magento | build magento query correctly

INSERT INTO table (first_name, second_name) VALUES ('my_value1', 'my_value2'), ('my_value1', 'my_value2'), ('my_value1', 'my_value2') how to create such a request according to the ...
1
vote
1answer
15 views

How to fix URL rewrites massively via SQL?

I had migrated my store from Magento 1 and I'm having many URLs that contain .html.html in the final of the string. I want to update all my 88187 URL rewrites, replacing .html.html to .html I ...
0
votes
1answer
15 views

SQL Error when importing bulk data. Product_id goes out of sync with Entity_id

When importing about 12000 rows of products from a .csv file, it suddenly gives me the following error: General system exception happened Additional data: SQLSTATE[23000]: Integrity constraint ...
0
votes
2answers
16 views

What is the attribute_id for tier_price used for in the catalog_product_entity_decimal table?

In the Magento 2.2.9 database, what is the 'tier_price' attribute used for in the 'catalog_product_entity_decimal' table? This seems to give some kind of error while trying to save new product ...
0
votes
0answers
17 views

How to create a category in Magento using SQL

My attempts at creating a category with PHP fails while flat_categories are enabled in the system. Reference here. So, I want to create a new category in Magento 2 using SQL. How would I do that?
0
votes
2answers
33 views

Why product collection model don't show real Sql query?

I want to understand how M2 loads products EAV attributes, so I wrote the following code: $productCollection = $this->productCollection->addFilter('entity_id', 7) ->...
0
votes
0answers
12 views

How to restore images of deleted products via sql?

I am using magento 2.2 version , I was deleting few products from admin dashboard , at the time of deleting my site crashed due to server issue and because of that when site goes up I see that 8 ...
0
votes
0answers
23 views

DB Error SQLSTATE[42S02]

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mavikmarine_l2li.m_catalog_product_flat_1_tmp_indexer' doesn't exist, query was: UPDATE m_catalog_product_flat_1_tmp_indexer AS et ...
1
vote
2answers
34 views

Magento 2: SQL get orders with specific option id

I'm trying to get all orders with a specific option id to see what percentage of orders are ordered with measurement of centimeters. Not very good with database queries and I can't seem to figure it ...
0
votes
0answers
45 views

Magento 2.3: Getting the salable quantity via SQL

I'm trying to get all stock data of a product via SQL for debugging. The part that I'm not 100% on is the salable quantity. Here is what I have: SELECT t1.`quantity` + SUM(t2.`quantity`) ...
0
votes
2answers
37 views

How to truncate database table using n98-magerun2?

I am trying to truncate the database table using n98-magerun2 version 3.2.0 and having some difficulties. I know there is a command with option to run an SQL query: db:query Executes an SQL query ...
0
votes
1answer
28 views

What is the “static” backend type in EAV attribute Magento 2?

In the "eav_attribute" table I discovered that some attributes have the backend type set to "static". Example, attribute_code = 'telephone' & entity_type_id = 2 resulted in a "static" backend ...
0
votes
0answers
32 views

Magento 2 : Filtering and sorting not working in one customer column “last_login_at” in customer grid backend

I want to show a customer's "last login date time" column in the customer listing in the backend with filtering and sorting functionality. I extended customer_listing.xml and added "updated_at" and "...
0
votes
1answer
20 views

How I can add security patch for magento 2.2.6 without updating magento

I have Magento 2.2.6 I want to update security patch I have lost my server databases (maybe SQL injection) my questions 1)How to update security patches? 2)Is it will helpful? 3)Is ...
0
votes
1answer
23 views

Magento 2 : How can I stop SQL attack through search bar

It's a bot that is continuously attacking, I try to hide these special characters suggestion form dashboard also try to delete them but all in vain, it comes again the next day(Magneto 2.3.1).
2
votes
1answer
44 views

Magento 2.3 Installation worked on Ubuntu 18.04, but after Ubuntu Restart Magento Page will not load any more. Any Ideas what could be the Problem?

I need your help. I am new to Magento 2.3 and first everything worked perfect after the Installation on my Ubuntu 18.04 Maschine, but after my first Ubuntu restart the Magento Shop Page and Admin Page ...
1
vote
1answer
43 views

How can I update all products names via sql query in magento2?

Hi I have a list of sku's of all products at my store , I want to update all the names of products I have on store via sql query with respect to it's sku . There is a table name : ...
0
votes
1answer
25 views

How to get all transaction_email ,transaction status from sql query Magento 2

I am trying to get all transactions,transactions_id,transaction_email,transaction status by SQL query. I found the table where all transactions are stored sales_payment_transaction, ...
1
vote
1answer
28 views

sql query for shipping countries from my sales order

I need to write a SQL query to get all countries where we have shipped al orders. Which table has all sales order countries? I am not able to find all the countries.
0
votes
0answers
24 views

Category SQL error

I recently found the following error in Magento2 log. Reading the query is missing there's an error in where condition --> WHERE (()) How I can debug? Tryed a crude "grep" in my filesystem but I ...
0
votes
1answer
25 views

Create the SQL script for the changing the instance_type parameter in the database widgets_instances magento 2

I have to change like this Magento\Cms\Block\Widget\Block to Custom\General\Block\Widget\Block. I am using the Replace function in the SQL , But it is not working.
2
votes
3answers
100 views

How to write a Conditional Selection SQL QUERY in Magento2?

$collection->getSelect()->where($getMainTableAlias.'.entity_id IN (SELECT catText.entity_id FROM '.$catalogCategoryEntityTextTable.' as catText where catText.attribute_id="'.$attributeId.'" AND ...
3
votes
2answers
92 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 ...
0
votes
1answer
76 views

Integrity constraint violation: 1062 Duplicate entry '3-2-9' for key 'PRIMARY', query was: ALTER TABLE `catalog_category_product_index_tmp`

Integrity constraint violation: 1062 Duplicate entry '3-2-9' for key 'PRIMARY', query was: ALTER TABLE catalog_category_product_index_tmp ADD CONSTRAINT PRIMARY KEY (category_id,product_id,store_id), ...
0
votes
0answers
10 views

Magento 2 Categories SQL Import statement just placing meta data

I am trying to place meta data on already existing categories. I received the names of categories and the new meta data that is all. I was wondering if there is fast sql statement that will find ...
0
votes
1answer
98 views

Magento 2.2.2 Products Not Showing Category and List Page

I have a real hard time solving this issue. The issue is products Not showing on front end Category Pages and Product Listing Pages after import via CSV - Just shows We can't find products ...
2
votes
1answer
78 views

Magento 2 Error while running Cronjob : Integrity constraint violation

I have 2 cronjobs that serve data from suppliers to a Magento store (Magento 2.2 (Community)). This has been running for a few weeks without any issue, but this morning I got a whole list of error ...
0
votes
1answer
29 views

Understand EAV structure and related tables to get any attribute

For products I have created new attribute brix_level which is a text field. Now I need products data using SQL. I am good at quries, but the EAV structure is beyond my level :D Can anyone explain ...
1
vote
1answer
22 views

Pull data of orders having non-zero subtotal for each customer

I want the count of the orders which each customer has placed. Only those order which has non-zero subtotal should be counted. How can I get that data? Please let me know if we can use any SQL query ...
0
votes
0answers
16 views

Magento SQL Query Tuning

My Magento Store that I have taken over my another dev is currently displaying a high Time to First Byte (TTFB) of around 1s. I'm aware that lots more goes into this number that SQL queries alone, ...
0
votes
0answers
19 views

Fetch list of customer address having unique firstname and lastname from some specific customer group

I need to get list of address form specifc customer group, below is condition; 1) firstname & lastname should be unique in customer address group. I have tried below but it is not working: ...
1
vote
1answer
139 views

row_id vs entity_id in magento 2.3 EE

in magneto 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 enity_id in EAV tables
0
votes
0answers
207 views

Magento2 Database Lock wait timeout exceeded

In my magento admin panel i am getting below error. SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: UPDATE flag SET state = ?, last_update = '...
2
votes
1answer
43 views

Join from custom table to customer_entity table and get all data customer in Magento 2

I used this code but it not working. Please help me! $customerEntity = $locationList->getTable('customer_entity'); $locationList = $this->mpSellerMapLocatorModel->getCollection(); $...
3
votes
2answers
166 views

{“0”:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.name'

Any Help Highly Admired, i'm using Magento 2.2.8 did all commands,upgrade, reindex, static file , etc still not worked {"0":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.name' in '...
1
vote
1answer
30 views

Magento 2 Custom table collection Conditions

I'm facing an issue with the dynamic parameters for the filter $collection = $this->_collectionFactory->create()->addFieldToSelect('field_name'); If I have below code it works fine if the ...
0
votes
0answers
43 views

Unique constraint violation found

when edition the product for the backend, I faced the unique constraint violation found in the Magento-2.3 . I have checked the exception log this shows SQLSTATE[23000]: Integrity constraint ...
0
votes
1answer
97 views

Bulk adding & updating product attribute

[M1] I have over 2000 products in my Magento store. I want to add a new attribute called 'Warranty Terms' to display it on the frontend. This is going to be the same for all products. I know how to ...
1
vote
2answers
127 views

How to check if column exist before trying to add it to the table?

We are using the Extension FollowUp E-Mail from AheadWorks. The Extension has bugs in the sql install scripts... they are trying to add a column to a table but don't check if the column does already ...
0
votes
1answer
37 views

Index sql error

When I run indexer:reindex I get the following SQL error SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your ...
0
votes
0answers
14 views

Magento 2 Data Migration SQL error 'Column not found' Column in m2 DB but not in m1 DB

im having this error: Its the first data migration step, the integrity step. Anyone knows what to do with that? Thanks!
0
votes
0answers
24 views

magento2 Sort is not working on PLP due to entity_id

We are working on Magento 2.2.7. On PLP, sort by price is not working. This is because in the query it first sorts the items by entity_id then only using the price field. Example query is given below ...
0
votes
0answers
24 views

Get the list of most seller combined products with its orders

I want to get the most frequently sold product combinations, along with number of orders ( and Orders if possible). I have customized the Magento tables for my warehouse activities and the sample ...
1
vote
0answers
25 views

How can I change multiple entries in DB?

In DB I have Rows: ID NAME PHONE Example: 1 Steave 33333333 2 Joe 77777 I can add and Edit any row, but I want when I click "save everyone" I rewrote all phones on DB example: 1 Steave 33333333 ...
0
votes
1answer
137 views

Insert, Update values in existing custom table and Select values from that table. Magento2

How can i Insert, Update values in existing custom table and Select values with Magento methods not sql requests ?
1
vote
1answer
147 views

Magento 2.3 introduced a view table which causes issue with import of mysql dump. What's it for?

If you mysql dump a magento 2.3 store and try to import it elsewhere you get an issue due the the format of the view import query. The problem part is this: CREATE ALGORITHM=UNDEFINED DEFINER=`...
2
votes
2answers
48 views

How get products URL in magento 2.2.5 sql database

I am using magento 2 enterprise edition i am trying to get products URL from sql database for this i have seen catalog_products_entity_varchar but in value attribute column products imgages are given ...
1
vote
1answer
33 views

How get products name magento 2 sql database

I am trying to get products name(value) and when i see my db table catalog_product_entity_varchar but in which in the column of value all base images are given so please tell accurate table or any ...
0
votes
1answer
20 views

Hiding products with £0.00

Magento 2.3, how can I hide products automatically via cron for products that have a £0.00 price? I use a 3rd party to calculate prices across my store and when some products cannot be matched in ...
0
votes
1answer
17 views

Magento 2 - Hide Products with no Images via PHP SQL Query

I have an SQL query that i run on the database manually which checks the catalogue for products that do not have an image and hides them, i want to run this via cron so have created a php with the ...