Thursday, March 17, 2016

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;

Wednesday, March 9, 2016

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