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.

1,087 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
9 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....
6 votes
2 answers
1k views

Magento MySQL Engine choice

Some of the tables in the Magento 2.1 DB have "MEMORY" as their engine. This is a problem for the DB cluster we are using (Percona-XtraDB-Cluster with pxc_strict_mode = ENFORCING). Can I change the ...
6 votes
0 answers
431 views

Split Database: Is It Possible to Append Database Name to All Queries?

Magento 2 EE comes with a split database feature, where three master databases with their own distinct tables work for the platform. Has anybody tried to do something similar in Magento 1 (CE)? I'm ...
  • 6,810
5 votes
0 answers
516 views

Declarative schema uses padding in 2.3, gone in 2.4, do we have to use it?

In a module we're developing we declare a new database table with a foreign key relationship to the sales_order table. We want to support Magento 2.3 and 2.4, so we use the declarative schema (...
5 votes
1 answer
1k views

Magento 2, Unable to save products (Catalog rule indexing failed. See details in exception log.)

I receive the error "Catalog rule indexing failed. See details in exception log." when I try to save a product, and get the error below in the exception log. [2018-08-26 05:09:05] main.CRITICAL: ...
  • 83
5 votes
0 answers
277 views

How to migrate WooCommerce to Magento 2?

I would like to migrate all data as possible to my Magento 2, I wish a module, code snippet or step-by-step tutorial. It will be very useful, thank you!
5 votes
0 answers
337 views

what populates data in cataloginventory_stock_status table and from which table

In magento2 - I would like to understand what populates data in cataloginventory_stock_status table and from which table the data is transferred in this table. I tried searching in ...
  • 1,476
5 votes
1 answer
605 views

Migration from Magento 1.x EE to Magento 2.1.x CE

I have a project on Magento 1.14.3.1 EE and meed to migrate it to Magento 2.1.5 CE to test out the performance improvement the Magento 2.1.x version has to offer. I was hoping to use the Magento's ...
5 votes
0 answers
3k views

SQLSTATE[HY000]: General error: 1813 Tablespace for table dbname.design_config_grid_flat exists

I'm getting the following error when I try to run the php bin/magent command and I'm not sure how to fix it SQLSTATE[HY000]: General error: 1813 Tablespace for table '`magento_e`.`...
4 votes
1 answer
340 views

Magento 1 on Percona DB produces errors

We are trying to migrate our DB to MySQL 5.7 on Percona XtraDB cluster. In the MySQL log file there are errors preventing some operations to be performed. The errors visible with the current setting ...
  • 115
4 votes
0 answers
43 views

Importing from Custom E-Commerce Platform to Magento 2 (MySQL to M2)

I need to import Products, Customers and Order History from a custom e-commerce platform into a new Magento 2 implementation. Ideally the extension or cloud service would allow us to connect directly ...
  • 41
4 votes
0 answers
616 views

Magento 2.2.5 - Categories are not getting saved in the database

In my Magento 2.2.5 Store, When I try to edit the existing categories content and press the save button, the categories content stays the same, there is no change being reflected in the admin panel,...
4 votes
1 answer
82 views

Custom Page Form and db insertion magento 2

I have created a custom Form and try to store details in databse. but the product url field and image url is not saved in db. app/code/Dyode/Pricebeat/Controller/User/Post.php <?php namespace ...
4 votes
0 answers
539 views

Random integrity constraint errors saving orders in Magento 2.1 after payment

I have a system that generates quotes, then submits the quotes, which processes a payment. The system is pretty standard: nothing really complicated is going on. The quotes have a single product in ...
4 votes
1 answer
669 views

Error message when I open the product page in admin backend

I transferred my database to a new site and now get an error message when I open product page in admin dashboard "Invalid entity_type specified:" and other products have this error "No such entity ...
  • 311
4 votes
1 answer
704 views

Magento Frontend suddenly very slow (Backend fast) issue MySQL related

I have a very strange Magento problem. We run Magento since 7 years and moved it a couple month ago to a new server. The performance was really good since then. But now suddenly since 4 days, the ...
  • 51
4 votes
0 answers
510 views

search_tmp processes slowing whole site and making it inaccessible

Magento 2.1.8 installation, left catalog search index running overnight. It appeared to be successful and all products were showing this morning in the store. However shortly after the site has become ...
  • 163
4 votes
5 answers
2k views

try to get all products by category id but get only enable products in magento2

How can i get all the products(including disable) programmatically? in Magento2. try to get all products by category id but got only enable status products. My code is : $category = $objectManager-&...
  • 253
4 votes
1 answer
1k views

How to get round Price Value in Grand Total Magento 1.9.2?

How can I achieve round price value in Grand Total?
  • 323
4 votes
1 answer
842 views

slow INSERT query in sales_flat_order_grid

We see in newrelic some peaks if orders are inserted into sales_flat_order_grid. I tried to figure out why, but couldn't find the reason. This peaks appear ~ 1 of 100 orders. First of all I thought ...
  • 1,096
4 votes
8 answers
2k 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) ...
4 votes
3 answers
2k views

Magento backup taking too long

So, I decided to make a database backup from the magento admin panel. But it´s taking too long and there´s no way to know if the backup is actually working or if it got stucked. Is there a way to stop ...
3 votes
4 answers
386 views

customer_id = NULL in sales_order table?

I need to get orders data directly from the database. I have got all orders (about 18,000) since January 2020. I need to do a join to customer_entity however, I realized that about 300 records were ...
  • 609
3 votes
0 answers
184 views

Using --no-tablespace option when backing up Magento 2 databases

I run a nightly cron job to back-up to backup my SQL databases using mysqldump. This has started to display the following error on all my databases: Error: 'Access denied; you need (at least one of) ...
3 votes
0 answers
1k views

Magento 2.3 - Add a new column after one column into the table using db schema

I need to add a new column after one of the particular columns in the existing table using DB schema & checked this dev doc link, they didn't mention about the before or after column. Is there ...
3 votes
0 answers
53 views

Database - Our system detected a lock condition in your database

We are getting an issue with the database. Below is the message we are getting from the server. Our system detected a lock condition in your database. Multiple queries were running too long. ...
3 votes
0 answers
33 views

MySql file per table idb file 65 rows 88GB in size?

I was hoping someone else may have come across this situation. I have a table called index_event which is part of Magento 1.9.3 and contains around 65 rows. Every so often the physical space taken ...
3 votes
0 answers
384 views

Setting up AWS RDS Read Replica on Magento

My Magento DB hosted on AWS MySQL RDS is running at 100% utilization. I have created a Read Replica so that read query can be served by it. But do not know how to configure it. Does anyone have any ...
3 votes
0 answers
81 views

Magento 2 I have added the custom field in the product attributes field but that not get save value at edit time in product attributes form

I have add the custom text field at the stores->attributes>product in form field that save into database but when i return edit that attributes that value not set at the edit times. Here is the ...
3 votes
1 answer
625 views

Magento 2.2.4 enterprise edition in `catalog_product_entity_int` table they use 'row_id` instead of `entity_id

I am afraid, i just want to know in catalog_product_entity_int table they use row_id instead of entity_id. and also other table use row_id I don't understand difference why they use row_id instead of ...
  • 5,916
3 votes
0 answers
425 views

How Do I Merge Two Separate Magento Databases

Problem I am trying to combine two Magento version 1 stores from separate domains. My goal is to combine the two existing sites into one so that it can be hosted on a new domain and after all the ...
3 votes
2 answers
2k views

Column not found: 1054 Unknown column 'at_status_default.value'

I'm actually kind of desperate.I tried many times to update my Community Edition from 2.2.1 to 2.2.5, always failing due to what I guess is a database inconsistency.After the update procedure - done ...
's user avatar
3 votes
1 answer
663 views

Magento 2 extension attributes

I'm a bit lost regarding the Magento 2 extension attributes. It's clear they offer a flexible way to extend the API/interfaces with your own attributes, but what I don't get is how to save the value ...
  • 131
3 votes
2 answers
4k views

Do you enable query_cache for Magento?

All tutorials for optimizing Magento performance I read suggest that we should enable query cache in mysql -> my.cnf However, I read a lot of other articles and even the mysqltuner script also ...
  • 31
3 votes
0 answers
225 views

Magento1.9 Connect to External Mssql Database using Pdo_dblib

I am trying to connect an external database to get few data into Magento. I am using pdo_dblib to connect to the database. try { $hostname = "hostname"; $port = port; $dbname = "dbname"; $username = "...
  • 1,030
3 votes
0 answers
787 views

How to add input form to custom checkout step in magento 2

I created new checkout step in magento 2 using this link custom checkot step Now I have only a button on my new step which is declared here in mystep.html <!--The 'step_code' value from the .js ...
  • 2,612
3 votes
0 answers
1k views

Working with Models and Databases in Magento 2

So I've been following along with these questions : https://stackoverflow.com/questions/31983546/in-magento-2-what-is-the-correct-way-for-getmodel/31984198 https://stackoverflow.com/questions/...
3 votes
0 answers
282 views

Unique SKU for Product entity (Mage2)

Why SKU field in product table (catalog_product_entity.sku - VARCHAR(64)) does not have UNIQUE index (just simple INDEX for searching) and uniqueness is provided by PHP code? Postfix "-1" is added ...
  • 1,959
3 votes
0 answers
2k views

Dealing with "Lock wait timeout exceeded; try restarting transaction" error in Percona Server

I recently upgraded our magento 1.7 database server from MySQL 5.4 to Percona 5.6 in the interest of performance. As expected, the site and general performance of magento has increased vastly, but at ...
  • 1,018
3 votes
1 answer
2k views

Magento 2 call table stock_item from database

I working to Magento 2.0.2 and i trying to call the table stock_item of the product. When i worked in Magento 1.9 i used this code: loadByProduct($_product)->getQty();?> But now the class Mage is ...
  • 487
3 votes
1 answer
299 views

What is the "database compatible mode" in Magento?

I've run over some comment in a core file which is about database compatible mode: In www/app/code/core/Mage/Core/Helper/Data.php: class Mage_Core_Helper_Data extends Mage_Core_Helper_Abstract { ...
  • 2,751
2 votes
1 answer
16 views

Syntax error or access violation: 1071 Specified key was too long when running setup:upgrade

I get the following syntax error when running setup:upgrade after the update of a specific module through composer: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; ...
2 votes
0 answers
181 views

Magento 2 : Get product name using mysql query

I am using Magento ver. 2.4.2 Is their any way we can get product attributes by id using mysql query ? I also need some custom attributes for product in same query THE BELOW SQL QUERY WORKS BUT GET ...
  • 397
2 votes
0 answers
629 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 ...
  • 121
2 votes
0 answers
98 views

Magento 2.3 How to apply coupon code discount on regular price

When I apply a coupon code on the cart, it gives a discount on the final price if a special price is set. But I want to apply a discount on a regular price ( If a special price is set on that product) ...
  • 384
2 votes
0 answers
205 views

How to switch from Mysql 5.7 to 8.0 in Magento 2.4

I was developing Magento 2 with MySql 5.7 but on production server there is MySql 8 installed which is giving me some query errors. After following a guide of migrating from MySql 5.7 to MySql 8.0 it ...
  • 214
2 votes
0 answers
158 views

After Upgrate Magento 2 from version 2.2.5 to 2.3.4 Integrity constraint violation: 1452 Cannot add or update a child row

I am trying to upgrade Magento 2 version from 2.2.5 to 2.3.4 using CLI. Version up-gradation is done. After version upgrade when I run bin/magento setup:upgrade it will give an error as follow: ...
2 votes
0 answers
217 views

reindexing DB corruption?

I have been issues for a little while with my indexer not running. I cleaned up most of the errors that I saw by running "bin/magento indexer:reindex" manually. In that case, from googling around, ...
  • 183
2 votes
1 answer
297 views

How to create stored procedure with Magento 2.3.x setup script.?

I'm trying to create MySQL stored procedure using Magento 2.3.x for one of my requirements. I found that that there is no way in Magento 2.3 declarative schema by which I can create that. But if the ...
2 votes
0 answers
120 views

Data migration: "Init vector must be a string of 16 bytes" due to old weakly encrypted data in the database - what to do?

I'm migrating a Magento 1 store to Magento 2.3.4 and I'm encountering an error at the data migration "Map Step". In Mcrypt.php line 81: [Magento\Framework\Exception\LocalizedException] Init ...
  • 1,158

1
2 3 4 5
22