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
-1
votes
1answer
20 views

How to query products without weight in magento 2?

I have been able to SQL query my products that have weight in Magento 2 select * from catalog_product_entity_decimal where attribute_id=80 and value is not null However, when I try to look up ...
0
votes
1answer
20 views

How to solve slow performance, slow and long running CRONs in Magento Cloud?

I'm having these two main issues below in my Magento 2.3.2 Enterprise Cloud. Heavy SQL load and site performance issues. Long-running and stuck CRON. How could I solve it?
2
votes
1answer
22 views

Query to change the status of bulk magento orders

I have a lot of Magento Orders with the wrong status Is there a way to change that bulk using a sql query? Thanks in advanced
0
votes
0answers
11 views

Convert SQL Select to Magento getCollection statement; Dates and multiple joins

I am not familiar with Magento and need to translate a sql select query into a Magento's equivalent getCollection statement. Thanks for assistance SELECT ordertot.entity_id as order_id, `...
0
votes
0answers
5 views

Get shipping from order

I'm doing a cronjob and I need to get the shipping value, this one SELECT "shipment_label_id" FROM "shipment_label" WHERE "order_id" = 66591 The shipping is saved like that in just that table atm ...
0
votes
0answers
31 views

Magento 2 In Stock Out of Stock not changing CSV

I am really stuck on this issue - I have a Magento 2.2.2 store and trying to use default import/export and also other extensions for importing and updating stock and qty. However I have found that ...
1
vote
1answer
23 views

Can't reset the AUTO_INCREMENT value of the custom table [closed]

I can't set the value of the auto-increment to '1' (table is already truncated) the query is: ALTER TABLE 'mam_color_codes' AUTO_INCREMENT=1; and if I check the table status: SHOW TABLE STATUS FROM ...
0
votes
1answer
18 views

magneto sql unknown query

we are seeing this sql query in our DB and are unsure what is genereting this, we have flat catalog index enabled so it should not be asking for this sql query select `e`.*, if(at_visibility.value_id ...
1
vote
0answers
18 views

Add all products to websites/storeview by sql

I have more then 10k products in base store and want now assign all to new website as multistore in system with/by sql how i assign all products to the new websites/storeviews using sql? use Magento ...
0
votes
1answer
15 views

Possible raw SQL statement detected. delete from $tableName WHERE sku = $productSku

the code is working well and doing it's job but pipeline on bitbuckets drops an error Possible raw SQL statement "delete from $tableName WHERE sku = $productSku " detected. my function where this ...
0
votes
0answers
20 views

How to use a SQL query to export all the products?

I'm having issues when exporting all my products via the admin panel, how could I get all the products data via a SQL query using Magento 2.3?
0
votes
1answer
19 views

how to enable font-family tool in WYSIWYG editor in magento 2

i want display fontfamily in the wysiwyg editor in magento2
0
votes
0answers
18 views

When i run Setup upgrade CLI i got SQLSTATE[42S02]: Base table or view not found

Please give me the solution for this problem, I didn't change any modules i can't catch this error how it occured > SQLSTATE[42S02]: Base table or view not found: 1146 Table > 'amtdb....
1
vote
0answers
30 views

How to fix error while running setup:upgrade command Magento 2

When I run setup:upgrade then I got this error after Schema creation/updates. If you know any solution then let me know. I have 'wishlist_item' this table on 461. Thanks SQLSTATE[42000]: Syntax ...
0
votes
0answers
15 views

Upgrading data.. SQLSTATE[HY093]: Invalid parameter number: parameter was not defined, query was: INSERT INTO

when I execute an insert sql command inside 'UpgradeData.php' it gives me this error, can anyone tell me where is the problem here ? if(version_compare($context->getVersion(), '1.0.2', '<')) {...
0
votes
1answer
63 views

Magento2 write updated query using connection & Zend format

I am trying to add below SQL query in the form of Zend format using Magento connection object Can you please let me know the syntax of how to follow... update eav_attribute_set set attribute_set_name ...
0
votes
0answers
43 views

Magento 2: Unable to reindex catalogsearch_fulltext_scope1

I've migrated from Magento 1.9 to Magento 2.3. While running reindex command for catalogsearch_fulltext_scope1 I'm getting the following error: Catalog Search indexer process unknown error: [1064] ...
0
votes
0answers
15 views

Slow MySQL on category pages queries after importing product reviews

I recently imported over 1 thousand client reviews. Right after importing categories and filters went extremely slow. I found the following SQL to be cause, but I cannot find how to change it to keep ...
0
votes
0answers
28 views

magento 2 get top level category id by product-Id or SKU in sql query

Am trying to get Top level category id of a product. but in below query am getting all category_ids those related to product. how to get the top level category Id? $sql = "SELECT e.entity_id AS 'id', ...
0
votes
0answers
18 views

check unused product images

How to check unused product images? I have used this query but it also remove the used images. query="SELECT value FROM catalog_product_entity_media_gallery; What is not from the results of this ...
0
votes
0answers
28 views

insert custom option from sql query

I want to insert custom options for a product via sql query. I tried with these queries: INSERT INTO catalog_product_option ( sku, product_id, image_size_x, file_extension, option_id, image_size_y, ...
1
vote
1answer
42 views

Magento2 - Default datetime value add column

I'm using magento Magento2.3.4, I tried to add new column with datetime type and default value of current datetime like this: $columns['created_time'] = [ 'type' => \...
1
vote
1answer
40 views

Magento 2 addFieldToFilter check for NULL not working

What i am doing ->addFieldToFilter('value', array(['eq' => '', array('isnull' => true)])) What i want in the query - '((value = '' )is NULL)` Can anyone help me on this Thanks.
0
votes
2answers
58 views

SQLSTATE[HY000] [2002] No such file or directory in magento 1.9

for no reason in getting this error on There has been an error processing your request SQLSTATE[HY000] [2002] No such file or directory
1
vote
2answers
35 views

QLSTATE[42S02]: Base table or view not found: 1146 Table 'mysite.vgroup_testimonial' doesn't exist, query was: DESCRIBE `vgroup_testimonial`

I have installed an extension for testimonial by V Group Inc. after the installation when i tried to add testimonies, the extension was giving me this error SQLSTATE[42S02]: Base table or view not ...
0
votes
1answer
31 views

Sql to delete category and its descendants

I am working with a Magento 2 store with a very large category tree (over 10000 categories). I used CategoryRepository and deleted one by one but this is very time-consuming since delete a brand with ...
0
votes
1answer
29 views

How to assign guest orders to registered customers by email via SQL?

I'm using this SQL below to manually assign guest orders to registered users. UPDATE sales_order SET customer_id = {YOUR CUSTOMER ID}, customer_is_guest = 0 where entity_id = {YOUR ORDER ID}; UPDATE ...
0
votes
2answers
39 views

SQL Insert error [SQLSate[4200]

We are using an extension called Mass Product Import & Update. My colleague gets SQL error when he is trying to upload a CSV file to add/update products/group products in this extension, but I ...
0
votes
1answer
34 views

How to save customer custom attribute default value to all Customers

I created a custom attribute for the customer entity. I also have the adminhtml_customer form, where the value can be changed and saved. The problem which I am facing is that, by default for all the ...
0
votes
1answer
16 views

Error : Source Model not found while creating a new attribute in customer entity

I wrote a simple sql upgrade script to add a custom attribute to the customer table. My script : <?php $installer = $this; $installer->startSetup(); $installer->addAttribute('customer', '...
0
votes
1answer
72 views

How to export orders by date via SQL?

I'm using a Magento 2.3 instance, how could I export my orders in a CSV file by date range via SQL? I want to have the shippingId, orderId and paymentId included in it.
1
vote
1answer
151 views

Magento 2: Multiple Product Type Not Sorting Due To Amasty Extension

I'm facing a problem due to Amasty Extension. I have a category which has 1 simple product and some configurable products, but when I try to sort them, simple product always comes first although ...
0
votes
0answers
155 views

Magento 2 Error: Base table or view not found: 1146 Table 'dbname.table_name' doesn't exist, while database import

I have a fresh setup of Magento ce 2.3.2, while importing the database with sample data in fresh database and I faced this error: Uncaught PDOException: SQLSTATE[42S02]: Base table or view not ...
0
votes
1answer
34 views

How to put a 'select' text inside a dropdown in the admin section? Magento2

I have a form in the admin section that has a phone numbers dropdown. This dropdown is pulling its values from a phone number table. I added a 'Select--' to the first option of the dropdown so the ...
1
vote
1answer
12 views

Magento 1.8 Some orders did not generate an invoice (flat tables were not disabled)

Culprit: Human mistake: Flat Tables Were Enabled when order was set to 'process': Invoice is not generated. I need to retrieve all processed orders with no invoice. An SQL query could do ...
0
votes
0answers
41 views

Magento 1.9 - What is the best Practice to Display Top Selling products list?

I have to display best selling products list, just confused what is the best practice. Create Module & CMS Page Getting best selling values from database using products id display product ...
0
votes
0answers
44 views

SQLSTATE[42S02]: Base table or view not found: 1146 in admin grid

I have an error in my module. when I edit a data in admin Grid its show Base table or view not found: 1146 Table 'magento.is_question' doesn't exist public function execute() { $rowId = (int)...
0
votes
0answers
20 views

Transfer products from store to another Magento 1.9

I want to transfer products from website to another in Multistore Website Anyone has an idea? I am using Magento 1.9 SET FOREIGN_KEY_CHECKS=0; UPDATE catalog_product_website INNER JOIN ...
1
vote
0answers
30 views

Linking Magento 2.3.2 to an SQL DB?

Hoping for some help - its my first post here! I am looking to link my newly created Magento website (using v2.3.2) to an Microsoft SQL server database.I would like to Database connection with ...
0
votes
0answers
44 views

Get products within certain period with limit in sql query Magento 2

I am trying to SQL query (not Magento collection) to get at most 5 products day-wise which lie under my SQL date comparison check . $sql = "SELECT `*` FROM `sales_bestsellers_aggregated_daily` AS `...
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
25 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
20 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
76 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
40 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
79 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
20 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 ...
1
vote
0answers
28 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
48 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 ...
1
vote
0answers
165 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`) ...

1
2 3 4 5
13