Archive for Linux

sed tricks

I helped a charity to rebuild a MySQL server and to restore a database with a lot of data of longblob type in the last two days. Fortunately there was a dump backup file for the database in question.

However, tables with longblob column(s) were not defined with “ROW_FORMAT=COMPRESSED”. I’d like to restore that database with row compression before inserting the data. Therefore I need to modify the dump sql file. The problem is that the file is 2.5 GB and the server only has 4 GB memory. So editing it is a challenge. Fortunately, Linux has sed to save the day. Don’t you love open source free software?

I am power Vi/Vim user, so I am familiar with sed and have used it in the past. But there are still a few things that I searched around for quick answers. So I’ll record noteworthy points here. I couldn’t remember how many times my own past blog entries helped me over the years. And I hope you’ll find this helpful too!

  • The -n switch is important. sed is a stream editor. In many cases you’d like to supress data streaming to stdout, and -n does that. This was especially important in my case, because a) the file is large, b) it contains blob that may or may not “fit to print”;
  • To see a particular line, say line a, use the p (print) command: sed -n 'ap' file
  • To see all lines between line a and b: sed -n 'a,bp' file
  • To see multiple, non-adjacent lines, say line a, e, g: sed -n 'ap;ep;gp' file
  • To edit big files, you’d like to make in-place changes. Therefore the -i switch. For example, to put in InnoDB row compression, this is the command I used: sed -i 's/CHARSET=utf8;/CHARSET=utf8 ROW_FORMAT=COMPRESSED;/' file
  • Similarly, to delete line a: sed -i 'ad' file You can also do range delete as well

By the way, when restore InnoDB database with a lot of blob data, it makes a lot of sense to enable the following settings in my.cnf, if they are not enabled already. It’ll make administration much easier down the road:
innodb_file_per_table
innodb_file_format = Barracuda

You may also need to tweak the max_allowed_packet and innodb_log_file_size parameters for successful restore.

Something else to pay attention to:
If you use:

mysql -uuser -p database < dump.sql

to restore the database back, the program may report the wrong line where it had loading problems. In most cases, you need to search the surrounding lines to find where the problem is.

Additionally, if you are in a hurry and want to load data in without troubleshooting loading issues, you can try adding -f switch to the command above so the restore ignores errors it encountered and jump to the next line.

Comments

WordPress post update services and Nginx upstream time out

Starting around last Friday, I think, this site became irresponsive. Checking Tengine/Nginx error logs, here is a sample of what I saw:

2014/03/xx 08:43:40 [error] 3837#0: *6149 connect() to unix:/var/run/php-fastcgi/php-fastcgi.socket failed (11: Resource temporarily unavailable) while connecting to upstream, client: xxx.xx.xxx.xxx, server: haidongji.com, request: "POST /xmlrpc.php HTTP/1.0", upstream: "fastcgi://unix:/var/run/php-fastcgi/php-fastcgi.socket:", host: "www.haidongji.com"
2014/03/xx 08:45:13 [error] 3837#0: *6151 upstream timed out (110: Connection timed out) while reading response header from upstream, client: x.xxx.xx.xxx, server: haidongji.com, request: "GET /wiki/doku.php?id=wiki:dokuwiki HTTP/1.0", upstream: "fastcgi://unix:/var/run/php-fastcgi/php-fastcgi.socket", host: "www.haidongji.com", referrer: "http://www.haidongji.com/"
2014/03/xx 08:46:00 [error] 3837#0: *6159 upstream timed out (110: Connection timed out) while reading response header from upstream, client: xx.xxx.xx.xxx, server: haidongji.com, request: "GET /category/technology/mysql/feed/ HTTP/1.1", upstream: "fastcgi://unix:/var/run/php-fastcgi/php-fastcgi.socket", host: "www.haidongji.com"

So I started researching. A number of web pages, including StackOverflow, recommended increasing values for some or all of the following Tengine/Nginx parameters, so time out wouldn’t occur:

  • fastcgi_read_timeout
  • proxy_connect_timeout
  • proxy_read_timeout
  • proxy_send_timeout

Those suggestion appeared to be reasonable. So I tried raising those parameter values one at a time. Their default value is 60 seconds. I raised them to 600 just to test.

In my case, the problem persisted. The only difference was that the site suffered a slower death and the few minutes it was able to stay alive, the site was terribly slow and unacceptable.

At this point I’d spent hours debugging this. I decided to give myself a break and come back to it the next day. Before falling asleep, I wrote down a note of checking some reputable forums the search engines may not necessarily able to get, such as Linode’s forums. Failing that, I am determined to read up on more documentation and code to get to the bottom of this.

It turned out that Linode’s forum provided the necessary clue to finally resolve this. The problem lies in the WordPress blog publishing update service. The idea behind it was to notify blog aggregators like Technorati/Feedburner (remember them?) as soon as you publish a blog. One of the aggregator sites listed in my WordPress setting decided to not accepting those notifications anymore. Not knowing this, WordPress still tries to send notification out in vain, which caused the whole mess. After removing that URL, everything went back to normal.

On your WordPress admin page, you can find which URL your blog pings to by going to: Settings -> Writing -> Update Services. Update: The same problem happened again. So I removed pingomatic.com also. Now my WordPress does not ping or update anybody. So far the site has been stable.

Hope this helps!

Lesson learned/reinforced:
After working on a problem for a long time and you are stuck, take a break, go for a walk, find a friend to talk to and describe the issue and what you’ve done thus far. You’d be surprised at how effective this can be.

Comments (2)

Setting up ownCloud

I’ve spent some of my spare time lately researching on ways to have better control of my calendar, contacts, important files that I sync to different places, and such. My objective is to move those things away from big cloud providers like Google, Microsoft, Dropbox, Skype, and so on. I am happy to report that the open source solution ownCloud provides just what I needed. I was able to set it up today and the tests were successful. Here are some noteworthy points.

  • ownCloud provides clients for Windows, Mac, Linux, iPhone, and Android devices, which is really nice;
  • In my case, the only php component missing was the php GD module. After installation, I restarted my VM and then it worked fine. For database, I used MySQL;
  • Setup was easy. I did the manual install by following instructions here. My web server was Tengine, a fork of Nginx. I used the sample Nginx configuration in the instruction page as the starting point, and it worked just fine;
  • ownCloud recommends using SSL, which I concur. I spent some time researching on certificate options, and decided to use self-signed certificate. The reason is simple: the intended customer is me and my family, therefore I don’t need a Certificate Authority (CA) to tell me that I am using a site that I own :) Not forking out the cash is added bonus. Note, though, that if you are running a commercial site that conducts transactions online, it is necessary to purchase certificates and enable SSL. That’s not the right time to be penny-pinching (or a 铁公鸡);
  • To generate my own self-signed certificate, I followed instructions here. I used the 2048 bit key. I followed the optional step 5 so that I can start Tengine without providing a password;
  • I think ownCloud will be great for companies and teams. If you have control to a web server, which most companies and teams do, then more than likely you’ve got the infrastructure to have your own cloud. For a company/team, I do recommend purchasing SSL certificates, unless your company/team is pretty small;
  • Don’t forget to renew your self-signed certificate as time moves closer to its expiration date. In my case, the validation period is one year. I’ve already set a reminder for me.
  • I’ve really enjoyed using dnsimple. Separating DNS and hosting service really gives me great flexibility in learning and testing out different web technology.

Hope this helps. Let me know if you have comments/suggestions.

Comments (2)

Fixing WordPress comment email notification problem by installing sendmail on Debian

In the past, whenever a reader commented on a blog entry, I would receive an email notification. Ever since I moved this site to a Linode VM, I noticed the comment email notification stopped working. Alas, blogs are not like what they used to be back in the day, now that so many people have mobile devices and there are so many ways to waste our time. As a result, not too many people bother to comment on blogs these days. So I postponed fixing this until today.

It turns out that WordPress uses PHPMailer for email notification. Skimming through the source code of wp-includes/class-phpmailer.php, I determined that installing sendmail on my Debian VM would fix the problem.

But sudo apt-get install sendmail gave me this first:

Reading package lists... Done
Building dependency tree       
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 sendmail : Depends: sendmail-bin but it is not going to be installed
            Depends: sensible-mda but it is not going to be installed
E: Unable to correct problems, you have held broken packages.

Ok, let’s fix the broken packages then:

sudo apt-get -f install

That didn’t find any broken package to fix.

So I decided to install the dependencies separately. First:

sudo apt-get install sendmail-bin

That command generated some error output but the last message was ok:

Errors in generating sendmail.cf
*** ERROR: FEATURE() should be before MAILER()
*** MAILER(`local') must appear after FEATURE(`always_add_domain')*** ERROR: FEATURE() should be before MAILER()
*** MAILER(`local') must appear after FEATURE(`allmasquerade')*** ERROR: FEATURE() should be before MAILER()

[ ok ] Starting Mail Transport Agent (MTA): sendmail.

So I proceeded with:

sudo apt-get install sensible-mda
sudo apt-get install sendmail

Both commands were successful. I then tested sending email to myself on the command with mail, which worked fine. A test comment on my blog also resulted an email notification. So all is well.

Hope this helps you also!

Comments

Web hosting with DNSimple and Linode

After about 8 years of hosting this site with Midphase, I’ve migrated my hosting service to Linode. I am using Debian 7 as my operating system. The web server is Tengine, a fork of Nginx. I have root access, therefore total control of this Linode VM. And I am using DNSimple to manage my domain, which is also very nice.

Apart from having total control of the server, I found Linode’s documentation to be pretty clear and easy to follow. I am currently on the Linode 1GB plan, which works very well so far for this WordPress site. It also hosts another site, www.sysmetrics.org. That site currently hosts a wiki I setup years ago, just for testing. And I have plans to host open source projects to gather and analyze system metrics data, initially focusing on Windows server and SQL Server administration. I recommend Linode highly. If you do want to use Linode service, I’d appreciate it if you could use my referral code so I could get some credit, 26cf3677a198d39b0728077d04c74651b0ee93c6

Most of web hosting companies bundle domain name service along with hosting service. Depending on your situation and technical knowledge, this may or may not be a good thing. I prefer separating those two nowadays, which gives me more flexibility and better control, especially when it comes down to moving hosting service somewhere else, due to lack of support and/or deteriorating performance. So for DNS, I’ve decided to use DNSimple. It has a pretty good interface and documentation. With the fixed IP address I get from Linode, I just filled out the appropriate information in my DNSimple accout for domain name resolution. I am pretty happy with it so far. Once again, if you do decide to use DNSimple, I’d appreciate it if you use my referral link so I can get some credit.

Keep up the good work, Linode and DNSimple. Don’t disappoint me and the good folks I send your way.

Comments (1)

« Previous entries