MySQL

Laravel – Carbon and Queries

Carbon is probably the most effective way to handle DateTime strings flawlessly and implement PHP code that everybody can easily extend or debug.

echo Carbon::now()->subMinutes(2)->diffForHumans(); // '2 minutes ago'

1	echo Carbon::now()->subMinutes(2)->diffForHumans(); // '2 minutes ago'

In the following example, I would like to demonstrate how you can filter a collection in Laravel by using Carbon. As you can see, the below example will simply return all People who have registered in the past two weeks.

$from = Carbon::now()->subWeeks(2);
$to = Carbon::now();

$people = (new People())
    ->whereBetween('registered_at', [$from, $to])
    ->orderBy('registered_at', 'desc')
    ->get();

$from = Carbon::now()->subWeeks(2);

$to = Carbon::now();

$people = (new People())

->whereBetween('registered_at', [$from, $to])

->orderBy('registered_at', 'desc')

->get();

The method subWeeks() will do all the work for you and return a valid DateTime string from 14 days ago which can be used for the whereBetween query method. If you prefer using days, you can simply use subDays() instead.

One possible use case could be a front-end controller that handles AJAX requests.

<?php namespace Your\Plugin\Controllers\Frontend;

use Illuminate\Routing\Controller;
use Your\Plugin\Models\People;
use Response;
use Carbon\Carbon;

class Work extends Controller
{

    public function index($weeks)
    {
        try {

            $from = Carbon::now()->subWeeks($weeks);
            $to = Carbon::now();

            $people = (new People())
                ->whereBetween('registered_at', [$from, $to])
                ->orderBy('registered_at', 'desc')
                ->get()
                ->toArray();

            return Response::json($people, 200)->send();
        }

        catch (\Exception $ex) {
            $ex->getMessage();
        }
    }
}

<?php namespace Your\Plugin\Controllers\Frontend;

use Illuminate\Routing\Controller;

use Your\Plugin\Models\People;

use Response;

use Carbon\Carbon;

class Work extends Controller

{

public function index($weeks)

{

try {

$from = Carbon::now()->subWeeks($weeks);

$to = Carbon::now();

$people = (new People())

->whereBetween('registered_at', [$from, $to])

->orderBy('registered_at', 'desc')

->get()

->toArray();

return Response::json($people, 200)->send();

}

catch (\Exception $ex) {

$ex->getMessage();

}

Missing required argument $name of Xtento\ProductExport\Logger\Logger.

If you are using Xtento in combination with an automated product importer, you may have seen the following error before.

Missing required argument $name of Xtento\ProductExport\Logger\Logger.

1	Missing required argument $name of Xtento\ProductExport\Logger\Logger.

The error message appears right after saving a product. The reason is that, at least one of your products in Products > Catalog has no Name. To fix this, you can update the name manually via MySQL queries or simply delete the products and import again correctly.

Bulk Update Customizable Product Options in Magento2

Today I had to bulk-update the Price and Max Characters in the tab Customizable Options for hundreds of simple products based on the title.

I knew that it could be done programmatically but I figured out that it can be done just by updating the table catalog_product_option and catalog_product_option_price. First I got a comma separated list of option_id’s based on a specific title.

SELECT GROUP_CONCAT(option_id SEPARATOR ",") AS option_ids FROM catalog_product_option_title WHERE title = 'Some Message';

1	SELECT GROUP_CONCAT(option_id SEPARATOR ",") AS option_ids FROM catalog_product_option_title WHERE title = 'Some Message';

277,281,284,286,289,292,293,294,295

1	277,281,284,286,289,292,293,294,295

With the comma separated list I was able to update the value of the Price and Max Characters as follows.

1. Price

UPDATE catalog_product_option_price SET price = 5.000 WHERE option_id IN (277,281,284,286,289,292,293,294,295);

1	UPDATE catalog_product_option_price SET price = 5.000 WHERE option_id IN (277,281,284,286,289,292,293,294,295);

2. Max Characters

UPDATE catalog_product_option SET max_characters = 300 WHERE option_id IN (277,281,284,286,289,292,293,294,295);

1	UPDATE catalog_product_option SET max_characters = 300 WHERE option_id IN (277,281,284,286,289,292,293,294,295);

Possible that you have to flush the cache ( php/magento cache:flush ) and re-index everything ( php/magento indexer:reindex ) to see the results.

Change Order, Invoice, Credit Memo or Shipment Number Prefix or Suffix in Magento2

In some cases shop owners have to change their default order number with a custom prefix or suffix. Customising order numbers sounds complicated, but it can be done with only few MySQL queries.

First, backup your database. After that, get a list of meta_id’s and store_id’s with the following query for order numbers only.

SELECT * FROM sales_sequence_meta WHERE entity_type = 'order';

1	SELECT * FROM sales_sequence_meta WHERE entity_type = 'order';

+---------+-------------+----------+------------------+
| meta_id | entity_type | store_id | sequence_table   |
+---------+-------------+----------+------------------+
|       1 | order       |        0 | sequence_order_0 |
|       5 | order       |        1 | sequence_order_1 |
+---------+-------------+----------+------------------+
2 rows in set (0.00 sec)

+---------+-------------+----------+------------------+

+---------+-------------+----------+------------------+

| 1 | order | 0 | sequence_order_0 |

| 5 | order | 1 | sequence_order_1 |

+---------+-------------+----------+------------------+

2 rows in set (0.00 sec)

The meta_id is pretty much the identifier you need for the next UPDATE query which will add the letter A as a prefix. For example: A100000001

UPDATE sales_sequence_profile SET prefix = 'A' WHERE meta_id = 1;

1	UPDATE sales_sequence_profile SET prefix = 'A' WHERE meta_id = 1;

The same query can be used to change the suffix. Also, you can change the prefix or suffix for any entity_type such as invoice, creditmemo or shipment.

That’s it. Create a new order to see the results.

Class not found in AbstractFactory

Today I had some trouble with removing a module on Magento2 and its custom product attributes. After removing the code of Mageplaza_Seo I was not able to edit products, categories or even checkout anymore.

Fatal error: Uncaught Error: Class 'Mageplaza\Seo\Model\Source\Robots' not found in /home/www/client/src/vendor/magento/framework/ObjectManager/Factory/AbstractFactory.php:93 Stack trace: #0
/home/www/client/src/vendor/magento/framework/ObjectManager/Factory/Compiled.php(88): Magento\Framework\ObjectManager\Factory\AbstractFactory->createObject('Mageplaza\\Seo\\M...', Array) #1
/home/www/client/src/vendor/magento/framework/ObjectManager/ObjectManager.php(57): Magento\Framework\ObjectManager\Factory\Compiled->create('Mageplaza\\Seo\\M...', Array) #2
/home/www/client/src/vendor/magento/framework/Validator/UniversalFactory.php(36): Magento\Framework\ObjectManager\ObjectManager->create('Mageplaza\\Seo\\M...', Array) #3
/home/www/client/src/vendor/magento/module-eav/Model/Entity/Attribute/AbstractAttribute.php(551): Magento\Framework\Validator\UniversalFactory->create('\\Mageplaza\\Seo\\...') #4
/home/www/client/src/vendor/magento/module-catalog/Mode in /home/www/client/src/vendor/magento/framework/ObjectManager/Factory/AbstractFactory.php on line 93

Fatal error: Uncaught Error: Class 'Mageplaza\Seo\Model\Source\Robots' not found in /home/www/client/src/vendor/magento/framework/ObjectManager/Factory/AbstractFactory.php:93 Stack trace: #0

/home/www/client/src/vendor/magento/framework/ObjectManager/Factory/Compiled.php(88): Magento\Framework\ObjectManager\Factory\AbstractFactory->createObject('Mageplaza\\Seo\\M...', Array) #1

/home/www/client/src/vendor/magento/framework/ObjectManager/ObjectManager.php(57): Magento\Framework\ObjectManager\Factory\Compiled->create('Mageplaza\\Seo\\M...', Array) #2

/home/www/client/src/vendor/magento/framework/Validator/UniversalFactory.php(36): Magento\Framework\ObjectManager\ObjectManager->create('Mageplaza\\Seo\\M...', Array) #3

/home/www/client/src/vendor/magento/module-eav/Model/Entity/Attribute/AbstractAttribute.php(551): Magento\Framework\Validator\UniversalFactory->create('\\Mageplaza\\Seo\\...') #4

/home/www/client/src/vendor/magento/module-catalog/Mode in /home/www/client/src/vendor/magento/framework/ObjectManager/Factory/AbstractFactory.php on line 93

The reason was an attribute called mp_meta_robots which was still pointing to the class \Mageplaza\Seo\Model\Source\Robots. I found the attribute by checking the table eav_attribute as follows.

mysql> SELECT * FROM eav_attribute WHERE source_model LIKE '%Mageplaza%'; 
+--------------+----------------+----------------+-----------------+---------------+--------------+---------------+----------------+----------------+----------------+----------------+------------------------------------+-------------+-----------------+---------------+-----------+------------------------+
| attribute_id | entity_type_id | attribute_code | attribute_model | backend_model | backend_type | backend_table | frontend_model | frontend_input | frontend_label | frontend_class | source_model                       | is_required | is_user_defined | default_value | is_unique | note                   |
+--------------+----------------+----------------+-----------------+---------------+--------------+---------------+----------------+----------------+----------------+----------------+------------------------------------+-------------+-----------------+---------------+-----------+------------------------+
|          222 |              4 | mp_meta_robots | NULL            | NULL          | varchar      | NULL          | NULL           | select         | Meta Robots    | NULL           | \Mageplaza\Seo\Model\Source\Robots |           0 |               0 | NULL          |         0 | Added by Mageplaza.com |
|          223 |              3 | mp_meta_robots | NULL            | NULL          | varchar      | NULL          | NULL           | select         | Meta Robots    | NULL           | \Mageplaza\Seo\Model\Source\Robots |           0 |               0 | NULL          |         0 | Added by Mageplaza.com |
+--------------+----------------+----------------+-----------------+---------------+--------------+---------------+----------------+----------------+----------------+----------------+------------------------------------+-------------+-----------------+---------------+-----------+------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM eav_attribute WHERE source_model LIKE '%Mageplaza%';

+--------------+----------------+----------------+-----------------+---------------+--------------+---------------+----------------+----------------+----------------+----------------+------------------------------------+-------------+-----------------+---------------+-----------+------------------------+

2 rows in set (0.00 sec)

Removing this via backend or MySQL has fixed the problem.

mysql> delete from eav_attribute where attribute_code = 'mp_meta_robots';
Query OK, 2 rows affected (0.01 sec)

1 2	mysql> delete from eav_attribute where attribute_code = 'mp_meta_robots'; Query OK, 2 rows affected (0.01 sec)

Update Order Status With SQL Queries in Magento2

Today I had to update order numbers which were processed already, but not updated in Magento. Here is how I changed the order status directly in the database based on the increment_id.

The first query will update the state and status on the table sales_order.

mysql> UPDATE sales_order SET state='complete', status='complete' WHERE increment_id IN (000043719,000044003,000044117,...);
Query OK, 15 rows affected (0.08 sec)
Rows matched: 16  Changed: 15  Warnings: 0

mysql> UPDATE sales_order SET state='complete', status='complete' WHERE increment_id IN (000043719,000044003,000044117,...);

Query OK, 15 rows affected (0.08 sec)

Rows matched: 16 Changed: 15 Warnings: 0

The second one will update the status in the grid view in Sales > Orders.

mysql> UPDATE sales_order_grid SET status='complete' WHERE increment_id IN (000043719,000044003,000044117,...);
Query OK, 15 rows affected (0.03 sec)
Rows matched: 16  Changed: 15  Warnings: 0

mysql> UPDATE sales_order_grid SET status='complete' WHERE increment_id IN (000043719,000044003,000044117,...);

Query OK, 15 rows affected (0.03 sec)

Rows matched: 16 Changed: 15 Warnings: 0

Delete Quote For Specific Customer In Magento2

Sometimes a quote can be linked with product id’s which doesn’t exists anymore. In some cases it can cause broken MySQL joins. Here is how you can flush the quote for a specific customer in Magento2.

mysql> DELETE FROM quote WHERE customer_email = 'customer@gmail.com';

1	mysql> DELETE FROM quote WHERE customer_email = 'customer@gmail.com';

Remove the WHERE clause to flush the quote for all customers.

AWS RDS – You Need (at least one of) The SUPER Privilege(s)

Today I had the opportunity to move a local Magento database to an Amazon RDS. Based on the well-documented article Creating a DB Instance Running the MySQL Database Engine I was optimistic to finish the migration pretty soon. But as you know, there is always something that goes wrong.

After creating the database instance class ( db.m3.large ) I have decided to proceed with some test imports before I change any database credentials in Magento.

$ mysql -h customer.1xcpdspit6hz.ap-southeast-2.rds.amazonaws.com -u customer -p
$ source ~/project_20161014_1135.sql

1 2	$ mysql -h customer.1xcpdspit6hz.ap-southeast-2.rds.amazonaws.com -u customer -p $ source ~/project_20161014_1135.sql

Good decision because the first import didn’t run through.

ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Query OK, 0 rows affected (0.01 sec)

1 2	ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) Query OK, 0 rows affected (0.01 sec)

I was able to fix the problem by adding the missing parameter log_bin_trust_function_creators as explained here. Before the next import I had to delete the database and create a new one.

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
Query OK, 0 rows affected (0.00 sec)

1 2	ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation Query OK, 0 rows affected (0.00 sec)

Unfortunately there was another problem with a DEFINER which tried to create a trigger as a different user. On Amazon RDS only a user with SUPER privileges can do that. Therefore, I had to remove the DEFINER from the MySQL schema you see below.

$ sed -i 's/DEFINER=[^*]*\*/\*/g' project_20161014_1135_toimport.sql

1	$ sed -i 's/DEFINER=[^]\/\/g' project_20161014_1135_toimport.sql

That’s it. Now I was able to import my DB on Amazon RDS without errors.

Query to get size of database tables in MB

If your hosting environment has no database monitoring, you should be able to monitor it manually once in a while or with your own shell script to prevent rapidly growing MySQL tables, because the result of large MySQL tables ( I am talking about 2 – 10 GB with millions of rows ) are often random performance issues, especially if you are using community modules with not well written MySQL queries.

SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "{{{YOUR_DATABASE}}}"
ORDER BY (data_length + index_length) DESC;

SELECT TABLE_NAME, table_rows, data_length, index_length,

round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"

FROM information_schema.TABLES WHERE table_schema = "{{{YOUR_DATABASE}}}"

ORDER BY (data_length + index_length) DESC;

This is the output of the query.

| enterprise_logging_event                      |     122389 |    15939572 |      3270656 |      18.32 |
| catalog_product_index_eav                     |     324014 |     4212182 |     18053120 |      21.23 |
| log_visitor_info                              |     182838 |    19706548 |      3599360 |      22.23 |
| catalog_product_index_eav_idx                 |     324014 |     4522245 |     21139456 |      24.47 |
| catalog_product_entity_varchar                |     255303 |    10852180 |     15244288 |      24.89 |
| log_url                                       |     228886 |    10966704 |     15636480 |      25.37 |
| log_url_info                                  |     228886 |    80258352 |      7542784 |      83.73 |
| enterprise_logging_event_changes              |      69184 |   160619752 |      1499136 |     154.61 |
+-----------------------------------------------+------------+-------------+--------------+------------+
586 rows in set (0.05 sec)

| enterprise_logging_event | 122389 | 15939572 | 3270656 | 18.32 |

| catalog_product_index_eav | 324014 | 4212182 | 18053120 | 21.23 |

| log_visitor_info | 182838 | 19706548 | 3599360 | 22.23 |

| catalog_product_index_eav_idx | 324014 | 4522245 | 21139456 | 24.47 |

| catalog_product_entity_varchar | 255303 | 10852180 | 15244288 | 24.89 |

| log_url | 228886 | 10966704 | 15636480 | 25.37 |

| log_url_info | 228886 | 80258352 | 7542784 | 83.73 |

| enterprise_logging_event_changes | 69184 | 160619752 | 1499136 | 154.61 |

+-----------------------------------------------+------------+-------------+--------------+------------+

586 rows in set (0.05 sec)

Table is marked as crashed and should be repaired

Don’t freak out if your Magento is not working and because of the following error message.

SQLSTATE[HY000]: General error: 145 Table './magento/log_visitor_info' is marked as crashed and should be repaired

1	SQLSTATE[HY000]: General error: 145 Table './magento/log_visitor_info' is marked as crashed and should be repaired

The reason of a crashed MySQL table is mostly related to a server issue. For example:

Any kind of hard-disk failure
Sudden server reboot (power outage in hosting)
Hard server reboot (ACPI shutdown)

In most cases you can repair the affected table with a simple repair command, which comes with MySQL.

mysql> repair table log_visitor_info;
+-----------------------+--------+----------+----------+
| Table                 | Op     | Msg_type | Msg_text |
+-----------------------+--------+----------+----------+
| magento.log_visitor_info | repair | status   | OK       |
+-----------------------+--------+----------+----------+
1 row in set (0.54 sec)

mysql> repair table log_visitor_info;

+-----------------------+--------+----------+----------+

+-----------------------+--------+----------+----------+

+-----------------------+--------+----------+----------+

1 row in set (0.54 sec)

This command looks pretty easy and safe, but keep in mind that you should always create a backup before you change anything in your database, because probably more MySQL tables are affected.