Converting Timezone-Specific Times in MySQL

Use below MySQL function to convert one timezone to another timezone.

Ex:

SELECT CONVERT_TZ(now(),'UTC','Europe/paris') as d;

SELECT CONVERT_TZ(now(),''US/Eastern','US/Central') as d;

LOAD DATA INFILE Error Code : 13 - When using mysqlimport command in ubuntu

mysqlimport: Error: 29, File '/CSVFile' not found (Errcode: 13 - Permission denied), when using table: TableName

• This error occurs due to the permission issue. 
• I used following steps to resolve this issue. 

1. Execute sudo aa-status command to check MySQL's profile is in enforcing mode.Because in recent Ubuntu server editions ship with AppArmor and MySQL's profile might be in enforcing mode by default.

What is AppArmor?

("Application Armor") is a Linux kernel security module that allows the system administrator to restrict programs's capabilities with per-program profiles. Profiles can allow capabilities like network access, raw socket access, and the permission to read, write, or execute files on matching paths.

# sudo aa-status
5 profiles are loaded.
5 profiles are in enforce mode.
   /usr/lib/connman/scripts/dhclient-script
   /sbin/dhclient3
   /usr/sbin/tcpdump
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/sbin/mysqld
0 profiles are in complain mode.
1 processes have profiles defined.
1 processes are in enforce mode :
   /usr/sbin/mysqld (1089)
0 processes are in complain mode.

If mysqld is included in enforce mode, then it is the one probably denying the write. Entries would also be written in /var/log/messages when AppArmor blocks the writes/accesses. What you can do is edit /etc/apparmor.d/usr.sbin.mysqld and add /data/ and /data/* near the bottom like so:

I have added  /home/charith/* r,   entry in to the file. Upload the csv file to the /home/charith/ directory and 

...
/usr/sbin/mysqld {
    ...
    /var/log/mysql/ r,
    /var/log/mysql/* rw,
    /var/run/mysqld/mysqld.pid w,
    /var/run/mysqld/mysqld.sock w,
    /data/ r,
    /data/* rw,

 /home/charith/* r,  

}

And then make AppArmor reload the profiles.

# sudo /etc/init.d/apparmor reload

WARNING: the change above will allow MySQL to read and write to the /data directory. We hope you've already considered the security implications of this.

Import CSV File Into MySQL Table in Ubuntu server

These are the steps that i followed to import a CSV data file to MySQL database

• Create a table in MySQL database. CSV file name should be equivalent to the table name that you are going to import.  

EX: Table name is : PS_EMPLIDS

• Execute following command  

Command to import csv file to MySQL :

mysqlimport --user=mysqlUsername --password=mysqlPassword --ignore-lines=1 databaseName ~/PS_EMPLIDS.csv


References:
http://dev.mysql.com/doc/refman/5.6/en/mysqlimport.html

Solution for [RuntimeException] The HOME or COMPOSER_HOME environment variable must be set for composer to run correctly - AWS Elastic beanstalk

Recently we got this error while we deploying our PHP Symfony2 application to AWS via elastic beanstalk environment.

Root cause for this issue

• Composer have made a change in which they have deprecated COMPOSER_HOME

Solution 1:

The quick fix is to COMPOSER_HOME to HOME.

commands:
  01_update_composer:
    command: export HOME=/root && /usr/bin/composer.phar self-update

option_settings:
  - namespace: aws:elasticbeanstalk:application:environment
    option_name: HOME
    value: /root

Solution 2: 

Another option on a new instance is to lock the composer version.

Here is an example ebextension:

commands:

  01_update_composer:

    command: export HOME=/root && export COMPOSER_HOME=/root && /usr/bin/composer.phar self-update 1.0.0-alpha11

option_settings:

  - namespace: aws:elasticbeanstalk:application:environment

    option_name: COMPOSER_HOME

    value: /root

  - namespace: aws:elasticbeanstalk:application:environment

    option_name: HOME

    value: /root