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.

880 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
2answers
1k views

enterprise_refresh_index Sleeping MySQL Connection

There are times that the indexer that is ran by cron seems to get stuck at the MySQL as Sleeping connections can be seen. Indexer runs for several hours and this actually not running at all. I've ...
7
votes
1answer
578 views

Magento Too many tables error bulk updating products

This morning while selecting and enabling some one hundred products in magento i got this mysql too many table error Below is the snippet from the log file a:5:{i:0;s:92:"SQLSTATE[HY000]: General ...
5
votes
2answers
534 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....
5
votes
4answers
920 views

SQL to get order items straight from the database?

How do we get a list of order items and their data (name, sku, quantity, price, weight, and dimensions)? Without using Magento's own framework components. The problem is if the order contains 5 ...
5
votes
0answers
238 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
0answers
214 views

what populates data in cataloginventory_stock_status table and from which table

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 cataloginventory_stock_item but ...
5
votes
2answers
664 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 ...
5
votes
0answers
394 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 ...
4
votes
1answer
824 views

Magento 2 : Foreign key constraint is incorrectly formed, ALTER TABLE customer_group

After upgrade from magento 2.1.7 to 2.2.3 and am stuck. I have tried everything. In Pdo.php line 235: SQLSTATE[HY000]: General error: 1025 Error on rename of './zwwnzwzjhe/#sql-1bd1_25f9' to ...
4
votes
0answers
40 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 ...
4
votes
0answers
309 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
1answer
41 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
1answer
510 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 ...
4
votes
1answer
572 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 ...
4
votes
8answers
603 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
1answer
469 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 ...
4
votes
3answers
1k 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
0answers
65 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
2answers
28 views

How to add new customer in magento 2 admin

I'm trying to add a new customer in magento admin, after creating the credentials I can't seem to log in as per there is no password field upon creating. Can anybody tell me how to add a new customer ...
3
votes
2answers
587 views

Magento 2: Adding “updated_at” column does not update current time stamp

I am using Magento 2.2.6 version. Am just trying to add an extra column updated_at in setup/upgradeschema.php. But on saving data, it is not updating the timestamp automatically. It is null always. ...
3
votes
0answers
310 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: ...
3
votes
0answers
318 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 ...
3
votes
1answer
285 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 ...
3
votes
1answer
320 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 ...
3
votes
0answers
186 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 = "...
3
votes
3answers
622 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-&...
3
votes
1answer
679 views

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

How can I achieve round price value in Grand Total?
3
votes
0answers
711 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 ...
3
votes
0answers
1k 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`.`...
3
votes
3answers
2k views

Create coupon code programmatically (in Magento 2.x)

I'm trying to port over tens of thousands of coupons from a custom e-commerce website into Magento 2.0.8 and I'm having a hard time getting it to work. These are simple coupons either fixed $ or % off ...
3
votes
0answers
954 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
0answers
213 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 ...
3
votes
0answers
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 ...
3
votes
1answer
1k 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 ...
3
votes
1answer
276 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 { ...
3
votes
1answer
548 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 ...
2
votes
0answers
37 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 ...
2
votes
1answer
39 views

`MySQL server has gone away` during long running cron

I have a series of stock importers, all of which run of mostly the same code, each with a few tweaks, with regards to how they fetch their stock data from different suppliers. These all run off cron, ...
2
votes
0answers
34 views

Impossible to find core_config_data table

A very noob question: it's my first time working on Magento2 e-commerce and I would like to move pages from IP to the domain name. Reading the documentation, I've found that I've to edit the ...
2
votes
0answers
19 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 ...
2
votes
0answers
18 views

What is the scope of source model extend and implement?

What is the scope of source model extend and implement between these two? \Magento\Eav\Model\Entity\Attribute\Source\AbstractSource \Magento\Framework\Data\OptionSourceInterface Attribute options ...
2
votes
0answers
49 views

Magento 2 change database collation

I am wondering if it is safe to change a magento database collation from utf-8 general_ci to utf-8 unicode_ci. I can do it from phpmyadmin or directly in mysql... I just don't know if it is safe ...
2
votes
1answer
78 views

Suddenly customer_grid_flat table delete in magento 2

I am using Magento 2.2.6. My site is working fine. But today morning I show customer_grid_flat table delete in my database. Please, someone, let me know why it's doing. Also in Admin side customer ...
2
votes
0answers
173 views

Magento2: How to convert weight unit to grams from kg?

I have a website in Magento2. Mageto2 provides product weight unit in KG, how an I convert it into unit Gram. I want to change the product weight unit from KG to Gram how it is possible ?.
2
votes
0answers
53 views

Magento 2.2 - How to remove product Meta Keywords maxlength validation from backend?

I want to remove validation for product meta_keyword from backend or else to increase the length of the product meta_keyword. I also tried to remove maxlength validation from vendor/magento/module-ui/...
2
votes
1answer
58 views

Magento 2 master slave connection for DB

I am planning to enable Master and slave connection on my Magento site. Magento team suggest me to check my 3rd party and custom modules support this master/slave config or not. I am not sure how can ...
2
votes
1answer
315 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 ...
2
votes
0answers
93 views

Redis does not improving performance

I'm trying to find the best setup for one really slow site. For education purpose. I set with AWS CloudFront like full page cache, not only static files. Also Varnish and Redis, and performance are ...
2
votes
1answer
371 views

Magento 2 - read and write connection for custom query

I am using Magento 2.2.5 EE. I need to write some custom queries Select and Insert I know how to write custom query, its simple: Create new connection: $objectManager = \Magento\Framework\App\...