Sunday, February 8, 2015

Bing Bot SSL SNI Support

When I migrated some website to use HTTPS, I noticed that not all browsers have SNI support and my website that migrated to SSL use shared IP address, so only browsers that support SNI can access the website.

The challenge is to make sure that my website is still exists in search result on the major search engines. Google Bot already support SNI, and how about Bing Bot? To make sure that Bing Bot support SNI, I read the log of my server and I got similar with this from the log file.

157.55.39.152 "Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)"

So, I got conclusion that Bing Bot 2.0 support SNI when crawl HTTPS website, and I checked in their search result that my HTTPS website with SNI is there.

Saturday, February 7, 2015

Full Outer Join in MySQL

In this case, I have 2 tables that need to join and show data in both tables even there is no data in one of the tables. For example, I have first table with data:

table1
id|male
1|Richard
2|Andy
3|Bagyo

and the other table

table2
id|female
2|Jane
3|Sulastri
4|Linda

I want to show the data like this:

id|male|id|female
1|Richard|-|-
2|Andy|2|Jane
3|Bagyo|3|Sulastri
-|-|4|Linda

By default, currently MySQL don’t support FULL OUTER JOIN. To achieve this, you can combine LEFT JOIN and RIGHT JOIN.

SELECT *
FROM `table1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

We can think of a UNION as meaning "run both of these queries, then stack the results on top of each other", with some rows will come from the first query and some from the second. UNION in MySQL will eliminate exact duplicates, so the result of the UNION only lists the same data once.

Friday, February 6, 2015

lftp Fatal error: Certificate verification: Not trusted

When I use lftp to transfer file between my Linux machine and my hosting server, I type the command in my Linux shell:

# lftp -u myuser,mypassword example.com
lftp myuser@example.com:~>ls
cd: Fatal error: Certificate verification: Not trusted

The problem is the server certificate it not valid on the server. To override this error forever, I just created file in my home folder.

# vim .lftp/rc
set ssl:verify-certificate no

Then, I got next error from lftp console.

Access failed : 521 Data connection cannot be opened with this PROT setting.

In tne file “.lftp/rc” I added more lines with:

set ftp:ssl-force true
set ftp:ssl-protect-data true

The problem then gone, and I can transfer the data from and to server without problem.