How to repair Magento 2 database

While Magento 1 offers an official tool designed to repair a database, nothing similar exists for Magento 2. You cannot find issues and fix them as quickly as most of us got used to. Indeed, there is an unofficial replacement for Magento 1 Database Repair Tool. Below, we shed light on how to repair the Magento 2 database in the most user-friendly manner. Besides, you will find out how to integrate Magento 2 with any external databases.

How to repair the Magento 2 database

This article was inspired by a topic on StackExchange started by Rafael Corrêa Gomes. He was looking for a replacement for Magento 1 Database Repair Tool and discovered two solutions developed to fix the Magento 2 database. First of all, you can streamline the Magento CLI to solve an existing problem. Then, you can rely on Magerun 2 to repair the Magento 2 database. Let’s explore each approach.

Repair Magento 2 database via CLI:

Use the following command to installs and upgrades data in the Magento 2 database:

To installs and upgrades the Magento 2 database schema, leverage the command below:

If you need to checks if an upgrade for your Magento 2 database schema or data is required, use this command:

Repair Magento 2 database via Magerun 2:

If you need to check Magento 2 database tables, streamline the following Magerun 2 command:

Of course, running these commands is not as intuitive as doing everything in Magento 1 Database Repair Tool, but it doesn’t seem that there is something complicated in the existing approach. Continuing the topic of Magento 2 databases, we’d also like to draw your attention to the ability to connect your e-commerce website to third-party DBs to exchange data between them.

How to import data from an external database to Magento 2

Magento doesn’t offer any user-friendly way of importing data from an external database to Magento 2. Usually, you have to create an output manually, modify it according to the Magento requirements, and then import everything into your e-commerce store. If any information is unsupported, the data transfer is interrupted by errors. In this post – How to debug and log Magento 2 import and export process and errors, we shed light on how to simplify the entire process of import/export logging and debugging, but let’s return to our mutton.

If you don’t want to deal with the complexity of the traditional procedure of connecting to an external database, use a third-party solution. Once, we faced the difficulties of moving data from a remote database to Magento 2 and decided to simplify this procedure completely and thoroughly. As a result, the Improved Import & Export Magento 2 extension was born.

This module reduces the number of steps necessary to move data from any remote databases to Magento 2, revamps attributes matching, and increase the import speed. Thus, you forever forget about headaches caused by the traditional approach to database import to Magento 2. Below, you can see the first significant advantage of our solution:

Direct Database Connection

The Improved Import & Export extension allows establishing a direct connection between Magento 2 and external databases. As a result, the use of data files is eliminated. Thus, you can forget about the first stage of the traditional approach to the import procedure: there is no need to create an output file from your database.

Furthermore, it is no longer necessary to edit it! You still have to match attributes but the Improved Import & Export Magento 2 extension lets you apply changes within a few clicks due to mapping presets. And don’t forget about the opportunity to create schedules automating every transfer from a remote database to Magento 2!

Mapping & data import from a remote database to Magento 2

The improved import procedure looks as follows:

1. Open Magento 2 admin and go to the import grid of Improved Import & Export.
2. Create a new Import profile:
   1. Specify a schedule of updates;
   2. Select a file format and destination;
   3. Enable other features.
3. Choose a database and a table you want to import.
4. Map attributes.
5. Run the update.

As you can see, the process of importing data from a remote database to Magento 2 turns into a piece of cake. There is no need to export data files, edit them, and run multiple import processes manually. Export processes are eliminated. Attributes matching is performed within your Magento admin. Import is fully automated with the help of schedules. You configure the connection between Magento 2 and an external database only once. The Improved Import & Export Magento 2 extension does other tasks for you. Let’s describe other features of our plugin that simplify data transfers.

Get Improved Import & Export Magento 2 Extension

Improved Import & Export features

The Improved Import & Export extension has already saved hundreds of merchants worldwide. Below, we shed light on its core features that dramatically improve your daily routine helping to connect Magento 2 to external databases. Let’s take a look at the automation of data transfers first.

Automated Import & Export Processes

The Improved Import & Export extension provides two different ways of automating data transfers. The module allows you to create schedules or specify event-based triggers that launch import and export processes.

Schedules

All schedules are based on cron. The technology behind them allows creating any custom update schedules. You only have to apply the default cron syntax. At the same time, there is a list of predefined intervals, which are fully customizable. You can also create profiles that have no schedules. They are especially useful if there is no need to run frequent updates, a single data transfer is required, or updates are provided asynchronously. But data transfers between Magento 2 and external databases are usually performed regularly, so schedules dramatically improve your daily routine. At the same time, every profile can be launched manually despite any automation. The image below shows how to configure a schedule:

Events

As for events, they represent an alternative way of automating import and export processes between Magento 2 and external databases or other systems. The Improved Import & Export extension offers several handy improvements enabling real-time updates. Note that you can not only select system events but specify custom ones. After a trigger is defined, create conditions to make its work more efficient. For further information, read this article: How to Run Magento 2 Import or Export After Specific System Event or Process.

Advanced Mapping Features

Most import issues are caused by the problem of different attribute standards that prevents you from direct transfers into your e-commerce store. The same problem is quite frequent when you want to transfer a table from a remote database to Magento 2. Since Magento relies on its common data model definition, and external systems are not compatible with it, you have to map attributes and sometimes their values. As for the Improved Import & Export Magento 2 extension, it dramatically simplifies all data transfers with the help of the following mapping features:

Mapping Presets

The Improved Import & Export extension lets you solve the problem of different attribute standards within a few clicks. Our module offers a list of presets that match external designations to the corresponding internal ones automatically. Your duties are reduced to selecting and applying a preset related to your integration. The process is automated since our extension analyzes the input data and matches all third-party attributes to the corresponding ones used in Magento 2. Thus, you dramatically simplify the integration with remote databases.

In the case of the default attribute editing, it is necessary to transfer data via files and edit their content manually. The more attributes they include – the more work should be done; the bigger a database is, the more time you have to spend on its editing. But the Improved Import & Export Magento 2 module lets you forget about that. Check the following image that illustrates the simplicity of the process:

Matching Interface

At the same time, you can match attributes manually connecting your Magento 2 store to a database. With our module, you can map attributes in a user-friendly interface right in your admin. Select a third-party designation and specify the corresponding internal one in front of it. Do that for every external attribute. The process has the following look:

As for hardcoded attribute values from “Default Value,” they are provided to all items related to the attribute to which you add them.

Attribute Values Mapping

To make data transfers from a remote database to Magento 2 seamless, the Improved Import & Export Magento 2 extension allows you to map attribute values in the same way we’ve just matched attributes.

The Improved Import & Export Magento 2 module provides the easiest solution, which is based on the same logic as the attributes matching described above. Of course, it is not as fast as selecting and applying a preset, but much quicker than doing everything in other ways.

So, you only need to select a third-party designation and specify the internal one in front of it. Note that the process should be applied to all external attributes. That’s how you connect your Magento 2 store to external databases and other systems. For further information, follow this link: Attribute Values Mapping.

Attribute Values Editing

To improve your interaction with remote databases and attribute values stored in them, we’ve also introduced the ability to edit values in bulk. The Improved Import & Export Magento 2 extension offers the following rules:

• Add a prefix to attribute values in bulk;
• Add a suffix to attribute values in bulk;
• Split multiple attribute values;
• Merge multiple attribute values.

Feel free to combine them and create conditions to increase the efficiency of their use. For further information, read this article: How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

When it comes to moving product data from a database to your Magento 2 store, you may face lots of inconveniences caused by external catalogs. Although external product categories never prevent import processes, they may be a reason for unnecessary time expenditures. Since the update may contain categories unrelated to yours, you add them to your frontend after the import is complete. Next, it is necessary to edit the occasionally modified catalog.

Instead, the Improved Import & Export Magento 2 extension lets you match the external categories to the ones used internally right during the import process. The following image illustrates the procedure:

If necessary, you can even create new categories on the fly: select a parent category and specifying the name of a new one. That’s it! Transferring product data from a remote database to Magento 2 has never been easier! Both features are described here: Category Mapping.

Attributes On The Fly

The lack of attributes also prevents a database import into Magento 2. To solve the problem, our module lets you create missing attributes on the fly. With the Improved Import & Export Magento 2 extension, you can specify all the necessary parameters using the following general form:

Attribute|attribute_property_name:attribute_property_value|…

The full process is quite complex, so we described in more details here: Product attributes import.

Extended Connectivity Options

The standard data synchronization between Magento 2 and an external system is based on CSV files. If an update file is provided in another format, you have to convert it into the supported one. How does Improved Import & Export solves the problem? It adds support for multiple file formats and various connectivity options.

Multiple File Standards

The Improved Import & Export Magento 2 extension radically increases the number of supported standards. While Magento is bound to CSV, our tool adds support for XML, JSON, ODS, and Excel. You can freely import and export data from any remote databases using these formats. All transfers can be fully automated, and you can efficiently match attributes and their values despite the format of the imported file.

Multiple File Sources

To make file transfers more flexible, the Improved Import & Export Magento 2 extension offers several connection points:

• FTP/SFTP connection allows leveraging a local or remote server as a place of transfers.
• The same functionality is possible with a Dropbox account. Alternatively, you can rely on Box, OneDrive, Google Drive, iCloud, and Amazon Drive.
• When you want to transfer data to your website, a direct URL is enough.

It is also worth mentioning that our module lets you import file archives. It extracts compressed data automatically, so you no longer need to do that manually.

Alternative Ways of Import & Export

And don’t forget that our extension lets you establish a direct API connection between your website and an external database or another system. The Improved Import & Export Magento 2 module leverages REST, SOAP, and GraphQL APIs to synchronize your e-commerce store with any third-party platforms.

At the same time, the module allows using different online sheets for various data exchange purposes. Create a bridge between your Magento 2 website and external platforms using Google Sheets, Office 365 Excel, or Zoho Sheet. Integrations on the basis of the first service look as follows:

Watch the following video to see the extension in action:

Conclusion

You can repair the Magento 2 database even without any official tools. The procedure is easy and intuitive if you don’t feel afraid to execute CLI commands. As for connecting Magento 2 to external databases, it is even more user-friendly when you use Improved Import & Export. Our extension lets you connect to remote databases directly automating all data transfers. For further information, follow the link below and contact our support:

Get Improved Import & Export Magento 2 Extension