Bash Script: Create MySQL Database & User with Optional Password

January 3, 2017  |  No Comments  |  by Raj (MagePsycho)  |  Linux, Mysql

I have been using DigitalOcean for my personal projects. And one repeated things that I had to do is the creation of MySQL database & user. I know I could have used some CPanel like GUIs and even some of them came to my mind like VestaCP, Ajenti, ServerPilot etc. But all comes with sort of things and not so developer friendly.

So I decided to create a simple bash script that can simply the database & user.

Bash Script Code

You can find the script at Gist:

How to use?

1# Download
Download the script from Gist – Bash Script: Create MySQL Database & User

2# Set Permission
You need to set the executable permission in order to execute the shell script:

chmod +x mysql-create-db-user.sh

3# Usage
Use the script as:

./mysql-create-db-user.sh [--host="<host-name>"] --database="<db-name>" [--user="<db-user>"] [--pass="<user-password>"]

So the only required parameter is database name. In the case of empty values for other parameters:

  • ‘host’ becomes localhost
  • ‘user’ takes value from database name
  • ‘password’ is randomly generated

For the following command:

./mysql-create-db-user.sh --database=bash_db2

The output looks like:

Bash Script: Create MySQL Database & User with Optional Password

Bash Script Console: Create MySQL Database & User with Optional Password

Hope you find this script useful.
Please do let us know any feedback in the comments below.

MySQL Issue: Table storage engine for ‘catalog_product_relation’ doesn’t have this option

July 30, 2016  |  1 Comments  |  by Raj (MagePsycho)  |  Linux, Mac OS-X, Magento, Mysql

When you export the Magento database dump from MySQL 5.5.x and try to import in MySQL 5.7.x, You are likely to face an error:

ERROR 1031 (HY000) at line 3002: Table storage engine for ‘catalog_product_relation’ doesn’t have this option

PROBLEM

This is probably due to the table option that you have in your CREATE TABLE DDL: ROW_FORMAT=FIXED

Let’s check if there is any such string in the SQL dump (Ex: magento-db-dump.sql).

cat magento-db-dump.sql | grep '=FIXED'

Which resulted as:

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Catalog Product Relation Table';
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Catalog Product To Website Linkage Table';

Refer – MySQL Row Format Option

SOLUTION

Removing ROW_FORMAT=FIXED option from CREATE TABLE DDL will fix the issue.
So let’s try possible solutions.

#1

sed -i 's/ROW_FORMAT=FIXED//g' magento-db-dump.sql

This didn’t work for me in MacOSx which resulted in following error:

sed: 1: “magento-db-dump.sql”: invalid command code m

#2

sed -i '' 's/ROW_FORMAT=FIXED//g' magento-db-dump.sql

And even this resulted as:

sed: RE error: illegal byte sequence

#3
But this one worked for me in MacOSx (Refer Mac OS X, Sed, and strange document encoding to know more about the issue)

LC_ALL=C sed -i '' 's/ROW_FORMAT=FIXED//g' magento-db-dump.sql

Re-check if the string has been removed or not:

cat magento-db-dump.sql | grep '=FIXED'

If the string is removed, now try to import as:

mysql -u <user> -p <db-name> < magento-db-dump.sql

Yay! Now it imported successfully.
Please do share and care if you liked this article.

Cheers!

How to set product attribute values to take values from default store?

February 7, 2014  |  No Comments  |  by Raj (MagePsycho)  |  Magento, Mysql

Have you ever ticked a ‘Use Default Value’ checkbox for Price (in store view level) and noticed the changes in the database?

If you tick the ‘Use Default Value’ checkbox for any product attribute (say Price) in store view level and save it. This will delete the related row in the database for that attribute, for the specific product, for that store id. And attribute will now take the default value.

Lets explain this scenario in detail.

1st Approach

Use Default Value - Before Tick

Fig: Before Checkbox Tick

Use Default Value Checkbox

Fig: After Checkbox Tick

So if you want to set the default value for any attribute, you can just tick the checkbox and save the product.
This process can be overhead in case of hundreds of product.

2nd Approach

Alternatively, you can use SQL approach which is actually the background operation.
1 Find entity_type_id

SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product';

2 Find attribute_id

SELECT attribute_id FROM eav_attribute WHERE entity_type_id = 10 AND attribute_code = 'price';

Suppose attribute_code = price and entity_type_id = 10
3 Check if row for that attribute exists

SELECT * FROM catalog_product_entity_decimal WHERE entity_type_id = 10 AND attribute_id = 99 AND store_id = 3 AND entity_id = 123;

4 Delete that particular row

DELETE FROM catalog_product_entity_decimal WHERE entity_type_id = 10 AND attribute_id = 99 AND store_id = 3 AND entity_id = 123;

Now try to run SQL #3, will now give you the empty result.

So in general, You can use the following SQL

DELETE FROM {table_name}
WHERE entity_type_id = {entity_type_id}
AND attribute_id = {attribute_id}
AND store_id = {store_id}
AND entity_id = {entity_id}

In case you want to set the default value for all the products in that store just remove the ‘AND entity_id = {entity_id}’ filter.

Caution:
In order to save you from worst-case scenario, just take a backup of your DB.

Last but not least, must thank @MariusStrajeru for his valuable suggestions and willing to help nature.

How to find the size / rows of Magento database & tables?

February 17, 2013  |  1 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

Introduction

Magento is a giant e-commerce application having more than 300 tables. It uses an eav model concept and provides different complex features which makes the database huge.

Sometimes you may wonder what’s the database size of your Magento database or individual tables so that you can work on some optimization task or freeing some of your server space.
Here we are going to discuss some SQL queries which seems to be helpful.

SQL Snippets

1. Find size & rows of Magento database

SELECT 
  TABLE_SCHEMA AS "Database",
  SUM(TABLE_ROWS) AS "Rows #",
  ROUND(
    SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
    2
  ) AS "Size (MB)" 
FROM
  information_schema.TABLES 
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name' 
 GROUP BY TABLE_SCHEMA;

2. Find size & rows of Magento database tables

SELECT 
  TABLE_NAME AS "Table",
  TABLE_ROWS AS "Rows #",
  ROUND(
    (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
    2
  ) AS "Size (MB)" 
FROM
  information_schema.TABLES 
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name' 

3. Find size & rows of Magento log tables

SELECT 
  TABLE_NAME AS "Table",
  TABLE_ROWS AS "Rows #",
  ROUND(
    (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
    2
  ) AS "Size (MB)" 
FROM
  information_schema.TABLES 
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name' 
  AND (
    TABLE_NAME LIKE 'log_%' 
    OR TABLE_NAME LIKE 'report_%' 
    OR TABLE_NAME LIKE 'dataflow_%' 
    OR TABLE_NAME = 'catalog_compare_item'
  ) 
ORDER BY TABLE_ROWS DESC 

Magento log tables info

These queries can be useful for optimizing your database. Suppose say if log tables are huge with large no of data then you can optimize the log tables by truncating them using following SQL:

TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;
TRUNCATE catalog_compare_item;

Caution: Always take a DB backup before performing truncate operation.

Hope you found this article useful.
Thanks for reading!

Getting super attributes info using raw SQL in Magento

April 22, 2012  |  5 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

Introduction

While creating a configurable product, you need to create an attribute(s) which will be used as select option(s) for the customer(For Example: Color, Size etc.). These kinda attributes are called super attributes or configurable attributes.

In this article we will be discussing how to get different info about super attributes using raw SQL.

1. Getting Super Attribute Info

SELECT 
  attribute_id,
  attribute_code,
  frontend_label 
FROM
  eav_attribute 
WHERE attribute_id IN 
  (SELECT 
    attribute_id 
  FROM
    catalog_product_super_attribute 
  WHERE product_id = $productId)

2. Getting Super Attribute Labels

SELECT 
  value 
FROM
  catalog_product_super_attribute_label 
WHERE product_super_attribute_id IN 
  (SELECT 
    product_super_attribute_id 
  FROM
    catalog_product_super_attribute 
  WHERE product_id = $productId)

3. Getting Super Attribute Prices

SELECT 
  is_percent, 
  pricing_value 
FROM
  catalog_product_super_attribute_pricing 
WHERE product_super_attribute_id IN 
  (SELECT 
    product_super_attribute_id 
  FROM
    catalog_product_super_attribute 
  WHERE product_id = $productId)

4. Getting Super Attribute Products (Associated Simple Products)

SELECT 
  product_id 
FROM
  catalog_product_super_link 
WHERE parent_id = $productId

Note: $productId = product id of configurable product

5. Super Attributes Schema

Super Attributes Schema

Hope this article gave at least some info about Super Attributes.
Thanks for reading!

Cheers!!