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.

Filter by
Sorted by
Tagged with
4
votes
1answer
892 views
0
votes
0answers
25 views

Magento 2.4 - Port must be configured within host parameter Pls Help

I can start the setup via Command Line. And I get no Errors. But if i enter the homepage i got this error: [2020-11-03 23:11:17] main.CRITICAL: Port must be configured within host parameter (like ...
0
votes
2answers
28 views

how do I change this sql query to magento get data method?

I have below sql query, how do I turn this to Magento get data method to retrieve data? Please help SELECT sales_order.increment_id, sales_order_payment.additional_information FROM sales_order LEFT ...
1
vote
2answers
2k views

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

Last night my host experienced what they call "InnoDB engine server is broken". It took awhile but they did a recovery but had some issues with missing tables. I also found out that we didn't have ...
0
votes
1answer
5 views

SQL query to get products enabled, in stock without images?

I'm running a Magento 2.3 and I would like to run a query in my database to export some products data that I don't have natively via the admin panel, the attributes are: Status Stock Images How ...
0
votes
1answer
18 views

Change stock availability of products based on qty

I'm looking for a query to get all my products that have "qty > 2 and 'stock availability = 0'" and change the stock availability to 1. A crash on my inventory update routine did a mess ...
1
vote
1answer
141 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 ...
4
votes
8answers
1k views

How to improve magento site performance

I have setup multi-store magento website, also added full page caching plugin to speedup performance in front-end but still front-end is very slow. Some reason I figured out from my analysis : 1) ...
0
votes
1answer
11 views

Set all categories to use Config Settings for 'Default Product Listing Sort By' option via SQL

I want to set all of my Magento 2 categories to 'Use Config Settings' for the 'Default Product Listing Sort By' within the Category Display Settings. I don't want to do this manually as there are ...
0
votes
0answers
34 views

Magento 2.3 crashing on never-ending search_tmp_* queries

I'm using Magento 2.3 and a production site that has a decently large number of products. The site has been running fine for over a year but in the last week we suddenly have an issue where a runaway ...
0
votes
0answers
11 views

Magento 2 with redis site not loading

I have configured the Redis cache as per Magento documentation. But if I run the cache:flush command the site loads lot of time and I'm using AWS RDS, The CPU of the RDS instance is 100% used. I also ...
0
votes
1answer
843 views

Magento 2.2.2 error: The configuration file has changed. Run app:config:import or setup:upgrade command to synchronize configuration

I am getting this error when attempting to install Magento locally: The configuration file has changed. Run app:config:import or setup:upgrade command to synchronize configuration. When running "...
0
votes
0answers
27 views

export magento 2 database with different method

i used Ubuntu OS and now mysql is not work, when i export database using ssh command in local terminal, i used this command for export DB : mysqldump -u root -p fastestsample > fastestsample_bkp....
0
votes
1answer
846 views

How to Insert Data in magento 2 database table programatically

We want to insert data in database table. My site is in magento 2. Any one help me for insert data in database table. namespace Agtech\Productstockupdate\Block\Index; class Index extends \Magento\...
1
vote
1answer
11 views

where I can find all the order numbers from the Database table?

where I can find all the order numbers from which Database table? I did some research, but I don't see sales_flat_order in my database. Please help
0
votes
0answers
25 views

How do I change the code in a Magento 2 extension without editing. I need to import csv file, but it will reflect the two tables in database

How do I change the code in a Magento 2 extension without editing it's files directly. I need to import csv file, it will reflect the two tables in database.
0
votes
1answer
19 views

Find all SKU products with tier price?

I want to compare products with tier prices from two databases. From database Magento 1 and Magento 2. Compare them to generate a list of products from Magento 1 that do not have tier prices in ...
4
votes
3answers
5k views

Truncate table using resource model in Magento 2

I have a custom module and i want to add remove every data including the increment id, using resource model in magento 2, i've read the solution in here but this is for magento 1, i'm not sure how to ...
2
votes
5answers
780 views

Why local.xml always use <host><![CDATA[localhost]]></host>?

I want to see what database a Magento installation was using. If i observe the file /public_html/app/etc/local.xml Surprisingly, it shows a database that doesn't exist, even my hosting server is ...
0
votes
1answer
55 views

Magento 2: How to Show different quantity for specific product type in product grid?

I have a custom table for specific product type to store the product qty, the query to select the qty is like this for example: SELECT a.qty FROM custom_product_table WHERE product_id = 1; i noticed ...
0
votes
1answer
51 views

Magento 2 Missing Wishlist Tables

I am working with a Magento 2 database that is missing the 3 wishlist tables. wishlist wishlist_item wishlist_item_option Is there a mysql script I can run to create these tables? Thanks
0
votes
0answers
13 views

Magento 2.3.5 - Flat Catalog module has a limit of 64$d filterable and/or sortable attributes

I'm getting the error below during my setup:upgrade: The Flat Catalog module has a limit of 64$d filterable and/or sortable attributes. Currently, there are 67$d of them. Please reduce the number of ...
0
votes
0answers
21 views

I should use sql query or Magento function to get data from table?

I create a custom module, it will run by cron job. Suppose I want to write a sql query to get data from the 'sales_order_payment' table. I read a lot online, it is not a recommend way to get data from ...
0
votes
1answer
19 views

How can I know from which file the table error is coming for?

I updated some modules and went up this "SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento_prod.amazon_nding_authorization' doesn't exist, query was: DESCRIBE ...
4
votes
2answers
814 views

Run .sql external file when installing extension in Magento 2

I have an .sql file which contain a lot of insert data, and i want import this .sql file to current magento 2 database file, everytime my custom extension finish installing and creating all custom ...
0
votes
1answer
20 views

How to have a CSV with customer, order and shipping information since a date?

I'm running a Magento Commerce Cloud B2B, and I need to get a CSV list of these data below, the most important data to me is the client's phone number which is inside the shipping information since a ...
0
votes
0answers
7 views

Magento 2 error: lock wait timeout exceeded. query was: update session set session expired =?

Some of my customers get this error randomly. And there are sleep query commands in mysql with no info. How can I solve this?
0
votes
2answers
19 views

How to add tables to database with foreign key?

I'm trying to extend costumers to add some wholesale info we need, so I'm trying to create a module with an InstallSchema to create, let's say, the table 'wholesale_info' with some columns and getting ...
2
votes
1answer
779 views

How to add select state dropdown field in custom phtml form and save in database(frontend) in magento2.2.5?

I have added a select state dropdown field in my custom phtml file. But not able to save that value in database in Magento2.2.5? /var/www/html/magento2/app/code/Amy/Contactform/Block <?php ...
0
votes
1answer
20 views

Update SKU, Product Name, Product URL key via database

I would like to ask for a help or hint, how to update SKU, Product Name, Product URL Key via database. My reference (key) is old sku and I have all data in excel sheet. I have about 600 products to be ...
9
votes
3answers
1k 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....
2
votes
3answers
899 views

How do i insert line breaks in product description with a SQL query

How can I replace (\n) in SQL with a Line Breaks? i want to do it with a SQL query, I want in table: catalog_product_entity_text replace the value with Line Breaks instead of (\n)
1
vote
1answer
987 views

Relationship between catalog_product_entity and cataloginventory_stock_item

I am trying to join these 2 tables together to get all the attributes I need from the group of Product Entity tables and join them with the remaining quantity in stock from the Stock Item table. ...
0
votes
0answers
14 views

Foreign key checks not working while running datapatch

I've created this datapatch to migrate some attributes (width, height, depth) from a varchar attribute to the decimal counterpart. Since both have been in de database for a long time I have to account ...
0
votes
1answer
13 views

Inventory View inventory_stock_1 causing database locks

After moving databases we are getting spikes in our database from PDP pages with the following query, and it ends up taking a full minute to query. SELECT `inventory_stock_1`.`quantity`, `...
0
votes
2answers
79 views

Invalid column data type when reindex Magento 2

I'm using Magento 2.3.4 for my website, when I try to reindex, the category flat was not reindex and appear error like this: I search some post in here and try this solution: magento 2.3.4 ...
0
votes
0answers
20 views

Filter product collection with nested OR/AND statements

I want to make a query as below WHERE manageStock = 0 OR (manageStock = 1 AND productStatus != 0) OR (manageStock = 1 AND backorderStatus = 1) but I can't use $collection->getSelect()->where() ...
0
votes
3answers
815 views

Billing name order in billing_name column in sales_flat_order_grid table

Can someone please tell me when a record is pushed into the "sales_flat_order_grid" table? How can I switch first name and last name in "billing_name" column of this table?
0
votes
1answer
948 views

how to import .csv file in defined table in command line?

I need to import.csv file throught windwos command line terminal in defined table. I trying this command : C:\xampp\mysql\bin\mysql -u root -p dbname dbtablename < C:\xampp\htdocs\site\csvfolder\...
1
vote
1answer
2k views

Magento - correct configuration of my.cnf

I'm trying to tweak my MySQL config on my Magento 1.9 install, without Full Page Cache installed on the server it run's really slow and I'm pretty sure its something to do with the MySQL Config. ...
0
votes
0answers
15 views

Internal Server Error

I just hosted on Godaddy, and i downleded magento 2.3.6 from magento official site and i uploaded it into filemanager, when i run the URL, it is showing 500 error. Can you please guys help me this. ...
1
vote
1answer
25 views

Upgrade Magento 2.3 and store code base is not found

I upgraded my Magento 2.3.1 to 2.3.6, I'm seeing this message in the frontend. The website with code base that was requested wasn't found. Verify the website and try again. How could I fix it?
3
votes
7answers
2k views

Magento 2.1.3 Extremely High MySQL CPU Usage

We recently started having big issues with our website slowing down quite often due to maxed out CPU usage (MySQL process using 200% CPU). The CPU usage is constantly at 60-85% and maxes out multiple ...
0
votes
1answer
34 views

Magento 2.4.0 Installation Error

I am getting this error in during installation of Magento 2.4.0 on Live server. Please help if you can. Current version of RDBMS is not supported. Used Version: 5.6.49-cll-lve. Su pported versions: ...
0
votes
2answers
375 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 ...
2
votes
4answers
2k views

Magento 2: How to dump database from Magento cloud

How to dump the database to local system from Magento Cloud server with Magento 2 Commerce. I have only access to the ssh but that ssh user doesn't have write permission into the server. So by any how ...
0
votes
2answers
37 views

How to get All product data from magento 1.9 database

I want to gather all my product from magento 1.9 database and upload on other site of magento 1.9 any one know how to do that ? Thanks
0
votes
0answers
34 views

Reindex process hangs and blocks website

I have a problem in my Magento 2 (version 2.3.4) shop that reindex process sometimes runs indefinitely and brings the whole site down (502 nginx error or timeouts). I noticed on the DB level, that in ...
0
votes
2answers
225 views

Magento 2.3.5 - Large table scconnector_google_feed_cl

We found a 'large' table on our completely clean Magento 2.3.5 store, that we run some indexes after we added a few products. Is there anyone who can tell us what table scconnector_google_feed_cl is ...
0
votes
1answer
17 views

Magento 2.3 localhost Blank page Frontend and admin

I'm trying to transfer my test site from web space to local (I'm running low on maximun allowed size) I've downloaded my database and a copy of the site. Through Xampp I've imported the database, I've ...

1
2 3 4 5
70