sql:mysql:insert multiple rows into table:ERROR 1136 (21S01) at line 15: Column count doesn't match value count at row 1

To insert multiple rows into a table at one go, use the following syntax -

Example:

INSERT INTO numericTypeDesc    (cTypeName,     iType,     cDescription)
                                          values     ("tenure",           30,          "One month"),
                                                         ("tenure",           90,          "Three months"),
                                                         ("tenure",           360,        "One year");

Do not enclose the rows within a parent set of braces, as would be the normal tendency, like -
INSERT INTO numericTypeDesc    (cTypeName,     iType,     cDescription)
                                          values     (
                                                         ("tenure",           30,          "One month"),
                                                         ("tenure",           90,          "Three months"),
                                                         ("tenure",          360,         "One year")
                                                         );

sql:mysql:LOAD DATA INFILE:csv file with date field

While importing data from a csv file into a table; if the csv file contains a date field(s) that is not in the MySql format (yyyy-mm-dd), problems arise.
The following method can be used to overcome this -

Example csv file:

,Donald Grump,MV,04/30/2004,1
,Ed Burner,RD,03/15/2010,1

Command:

LOAD DATA INFILE '/usr/share/data/candidates.csv'
INTO TABLE candidates
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(candidateId, name, type, @csvDate, isActive)
SET joinDate = str_to_date(@csvDate, '%Y-%m-%d');

Explanation:
The date field in the csv file is loaded into the variable 'csvDate'.
The 'str_to_date' MySql function is used convert the date string in 'csvDate' to the MySql format (yyyy-mm-dd) and the 'joinDate' column of the candidates table set to this acceptable date, all in one stroke!

Note:
The sequence of commands is important here.
The column names of the candidates table are to be listed ONLY AFTER THE FIELD OPTIONS. The column names and the "set" options are to be at the END of the LOAD DATA INFILE command.

sql:mysql:change default mysql directory

The default mysql directory is "/var/lib/mysql"

This can be changed by the following steps -

1. Change the default directory path in the mysql configuration file.

sudo vi /etc/mysql/my.cnf
datadir        = /mynewdir/mydefaultdir

2. Since mysql is now going to be using a new resource (/mynewdir/mydefaultdir), use apparmor to notify the system so.

sudo vi /etc/apparmor.d/usr.sbin.mysqld
/usr/sbin/mysqld {
  #include <abstractions/base>
  /etc/mysql/conf.d/ r,
  /etc/mysql/conf.d/* r,
  /etc/mysql/*.cnf r,
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,
  /usr/sbin/mysqld mr,
  /usr/share/mysql/** r,
  /var/log/mysql.log rw,
  /var/log/mysql.err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  /mynewdir/ r,           # add this line to give read permission to mynewdir
  /mydefaultdir/ r,      # add this line to give read permission to mydefaultdir
  /mydefaultdir/* rw,  # add this line to give read,write permissions to all the
                                  # files in mydefaultdir

3. sudo /etc/init.d/apparmor reload
4. sudo /etc/init.d/mysql restart

Note: For step 4, some systems have "mysqld" instead of "mysql"

sql:mysql:Error 29 (HY000): File not found (Errcode: 13)

While using the "LOAD DATA INFILE" facility, a common error is -

 

"ERROR 29 (HY000): File '/mydir/myfile.txt' not found (Errcode: 13)"

 

Here is the more proper solution to this issue (other shortcuts exist) -

 

Use apparmor to indicate which resources (files) mysql can use.

 

Step a:

sudo vi /etc/apparmor.d/usr.sbin.mysqld

 

Step b: Add the lines highlighted in blue 

/usr/sbin/mysqld {
#include <abstractions/base>
/etc/mysql/conf.d/ r,
/etc/mysql/conf.d/* r,
/etc/mysql/*.cnf r,
/usr/lib/mysql/plugin/ r,"
/usr/lib/mysql/plugin/*.so* mr,
/usr/sbin/mysqld mr,
/usr/share/mysql/** r,
/var/log/mysql.log rw,
/var/log/mysql.err rw,
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/var/log/mysql/ r,
/var/log/mysql/* rw,
/mydir/ r,                      # this line gives read permission to the mydir directory
/mydir/* rw,                 # this line gives read,write permission to all the files in mydir

Step 3: 

sudo /etc/init.d/apparmor reload

sql:mysql:show variables

To display the mysql system variables and their values -
1. Login to mysql - "mysql -uUSERNAME -p"

2. Type "show variables" to display all the system variables and their values.

3. Type "show variables like 'my_pattern'" to display only a variable(s) matching this pattern.
Example - 
"show variables like '%commit%';"

4. Type "select @@autocommit" to display the value of the autocommit variable

sql:mysql:login

To login to mysql -
1. Type "mysql -uYourUsername -p"
2. Type the password for your_username

sql:mysql:mysqld running

To find out if mysql is running -
1. Type "mysqladmin -u your_username -p status"
2. Enter the password for your_username
3. If the mysql daemon (mysqld) is running, you should see something like this -
"
Uptime: 192241  Threads: 1  Questions: 122  Slow queries: 0  Opens: 99  Flush tables: 1  Open tables: 23  Queries per second avg: 0.0
"