How to dump Magento 2 Enterprise Edition Database

1. curl -sS https://accounts.magento.cloud/cli/installer | php
2. magento-cloud list
3. login without root
4. magento-cloud db:dump
5. Open login URL in browser like : http://127.0.0.1:5000
6. Update to version 1.23.0? [Y/n] y
7. Continue? [Y/n] y
8. Enter a number to choose a project:
1.  [0] ProjectName (Project) (tx2ia23dv5uk)
2.   Environment ID [staging]: (Press enter key)
9. Enter a number to choose a relationship:
1.   [0] database
2.   [1] database-slave
10. (Press 0 and Enter key) 

sql_mode=only_full_group_by is incompatible in PHP mysql query with GROUP BY codeigniter

#1055 - Expression #24 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'customers.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Add $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));") line above your query.

See Example :

$this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
$this->db->select('*, customers.first_name, customers.last_name, customers.id');
$this->db->select("(SELECT count(video_id) FROM customer_videos WHERE c.customer_id = customer_videos.customer_id AND (created_at >= '$from' OR created_at <= '$todate')) as seen");
$this->db->join('customers', 'customers.id = c.customer_id', 'left');
$this->db->group_by('c.customer_id');
$this->db->from('customer_videos as c');
$query = $this->db->get();
echo "<pre/>"; print_r($query->result());exit;
return $query->result_array();

No space left on device in server | PHPMysql not connect session | Full Inodes 100% | Check CPU Process and MEMORY

Issues

1. open(/var/lib/php/sessions/sess_, O_RDWR) failed: No space left on device (28)
2. PHPMysql not connect
3. Database not connect

Solusions

Check CPU and MEMORY usage of server from terminal

ps -eo pid,ppid,cmd,%mem,%cpu --sort=-%cpu | head

df -h

Check file size

find /home/ -size +100M 

 

 Check INODES in server (Inode mean total files into server)

df -i

Search INODES from system and perticular folder (Get number of file in perticular directory)

for i in /var/www/html/*; do echo $i; find $i |wc -l; done

Check INODES into specific directory

ls -l /var/lib/php/sessions | wc -l

Remove all files from specific directory

rm -r /var/lib/php/sessions/ 

If you are using PHP so you can create code in any existing file

$path = '/var/lib/php/sessions/';
$dir = opendir($path);
$i = 0;
while ($dir && ($file = readdir($dir)) !== false) {
    unlink($path.$file);
    echo $i.' - '.$path.$file.'<br>';
    if($i == 50000){
        exit;
    }
    $i++;
}

How to create snapshot and attach with another instanse

1. Stop current instanse
2. Create new Snapshot (Please check volume properly) from current volume
3. Create new instanse with greter or equal to current volume (GB) from old instanse
4. Stop New instanse
5. Goto spanshot and create volume with (us-east-1d)
6. Goto volume and detach currnet volume of new instanse
7. attach new volume which you have created from snapshot (us-east-1d) make sure same root (/dev/sda1)

For your ref : https://www.youtube.com/watch?v=W89C_OqlOwE

How to generate new SSH key and adding it to the ssh-agent

1. Open Terminal

2. Paste the text, change email address. $ ssh-keygen -t rsa -b 4096 -C "jaydipkansagra@example.com"

3. Enter a file in which to save the key (/root/.ssh/id_rsa): [Press enter] (Recommend press enter)

4. Enter passphrase (empty for no passphrase): [Type password] (Recommend keep blank)

5. Enter same passphrase again: [Type password again] (Recommend keep blank)

6. For ubuntu
     Start the ssh-agent - $ eval "$(ssh-agent -s)"
   For windows
     1. $ ssh-agent bash
     2. $ eval "$(ssh-agent -s)" 

7. $ ssh-add ~/.ssh/id_rsa

8. Open  ssh-keygen.pub

9. Copy key from ssh-keygen.pub

10. Add into ssh-agent

11. Now, get data from your repository : git clone git@github.com:whatever

How to log all | full SQL | Mysql queries in Magento 2

Show ALL | Full magento 2 query with Sorting, Pagination and Filter in Magento 2

Open : /var/www/html/magento/jaydip kansagra/app/etc/di.xml

Find 

<preference for="Magento\Framework\DB\LoggerInterface" 

Replace

<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\File"/>
 <type name="Magento\Framework\DB\Logger\File">
      <arguments>
          <argument name="logAllQueries" xsi:type="boolean">true</argument>
          <argument name="debugFile" xsi:type="string">log/sql.log</argument>
      </arguments>
 </type>

Open : /var/www/html/magento/frye-magento/var/log/sql.log

Order saving error: SQLSTATE[HY000]: General error: 1449 The user specified as a definer in Magento 2

Run following query into your phpmyadmin

GRANT ALL ON *.* TO 'username'@'%' IDENTIFIED BY 'Jaydip@123';
FLUSH PRIVILEGES;