PHP Mavericks, XAMPP and Sendmail Oh My!

First back up the file we’ll edit -

cp /etc/postfix/main.cf  /etc/postfix/main_orig.cf

then edit it -

sudo vi /etc/postfix/main.cf

and set

relayhost = [smtp.theserver.edu]

you may be tempted to edit myhostname, or mydomain but just leave them commented out.
save the file
Back up your php.ini file -

cp /Applications/XAMPP/etc/php.ini  /Applications/XAMPP/etc/php_orig.ini

then edit it -

sudo vi /Applications/XAMPP/etc/php.ini

search for SMTP, it may be commented out, but change it to this -

SMTP=localhost

uncomment

smtp_port=25

then change

sendmail_path = /usr/sbin/sendmail -t -i

save the file

Deleted rows duplications in MSSQLsever

WITH CTE AS(
   SELECT N,asofdate,
       RN = ROW_NUMBER()OVER(PARTITION BY N ORDER BY N)
   FROM dbo.CSVCBC08TO0214 where asofdate='31012014'
)
DELETE FROM CTE WHERE RN > 1

Count Column in SQLServer by sql statement

SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_CATALOG = 'CB201312' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Main_CSV_DEL_ERROR_TOUAT_2'

Change the stored procedure definer

UPDATE `mysql`.`proc` p SET definer = 'localhost@localhost:88' WHERE definer='localhost@%';

show procedure status;

Show query log in MYSQL

You can log every query to a long file really easily:

mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+

mysql> SET GLOBAL general_log = 'ON';

Do your queries (on any db). Grep or otherwise examine /var/run/mysqld/mysqld.log
Then don't forget to

mysql> SET GLOBAL general_log = 'OFF';

or the performance will plummet and your disk will fill!

What's the difference between MyISAM and InnoDB?

MYISAM:

1. MYISAM supports Table-level Locking
2. MyISAM designed for need of speed
3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
6. MYISAM supports fulltext search
7. You can use MyISAM, if the table is more static with lots of select and less update and delete.

INNODB:

1. InnoDB supports Row-level Locking
2. InnoDB designed for maximum performance when processing high volume of data
3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4. InnoDB stores its tables and indexes in a tablespace
5. InnoDB supports transaction. You can commit and rollback with InnoDB

Mysql permission to single table

If the user account already exists: 
GRANT SELECT,INSERT,UPDATE,DELETE ON database_name.table_name TO 'username'@'localhost';

 If the user account hasn't been created yet you can set it up at the same time as its permissions:

GRANT SELECT,INSERT,UPDATE,DELETE ON database_name.table_name TO 'username'@'localhost' IDENTIFIED BY 'password';