We encountered an interesting error on a client database this past few days, where MySQL was issuing random errors, complaining about:


These errors were coming up on various tables at different times in the day, but the crazy thing is - the duplicate key error is on an auto-increment field!

That's not normal.

When you have a table defined as


There's no way you can generate a duplicate key error on the 'id' field, unless you manually pass the value of the auto_increment id field (which we were not)

Running a mysqlcheck reported all tables are OK. We proceeded to issue OPTIMIZE TABLE statements (this rebuilds indexes) as well as manually running REPAIR TABLE for each table, but nothing helped.

The table indexes were obviously corrupted in some way, although MySQL tools reported all is well...

How to fix this problem

The only way we could fix it, is by rebuilding the entire database from scratch.

Dropping all tables and reloading them from a backup.

Step 1

Create a backup of the database by issuing:


Replace USER with your MySQL username, PASSWORD with your MySQL password and DBNAME with the databasename you are looking to repair.

Step 2

Drop and recreate the database


Step 3

Reload the import



View 4 Comment(s)

Rsync is a great command-line sync tool, that allows fast replication from one web server to another.

If you are running more than one Rsync command in a cronjob, replicating several folders into a target machine, watch out for the Rsync endless loop bug.

There are two steps you can take to avoid this:

Step 1:

Consider this scenario:

Destination machine B has these two cronjobs, set to run every 5 minutes:


Since the folder /usr/home/something/ is also included by the first cronjob, this can easily cause the infamous Rsync endless loop bug.

The fix, add an --exclude to the first cronjob, like this:


Step 2:

If it takes more than 5 minutes for Rsync to complete, before the next instance runs, you're in trouble.

To be safe, write a php script that will check if the Rsync daemon is running, prior to launching another one.

This tutorial goes over all the necessary steps to restore a MySQL backup via the shell or command line.

Determine The Basics About Your Database

We need some basic information about the MySQL installation before we get started restoring the backup.

First, lets determine the location of the mysql databases. It is generally located at /var/mysql/data but it is always good to double check. Issue the following command to see where it is:


This will output something like the following and tells us that the mysql databases are located at /usr/local/var. This location will be used for the remainder of the tutorial so please be sure to replace it with the location that you received from the above command.



Remove The Old Database(s)

If MySQL is not corrupted and your backup files are set to drop the table before inserting the new data. You can skip this section. If you are not sure it will not hurt to complete the following steps but we will need to take the database offline for a few minutes.

First we need to shutdown mysql with the following command:


Now we need to backup the current mysql data directory and create a new one. This can be done with the following 2 commands:


Next, we will install the basic mysql table by running the mysql_install.sh script. Generally, it is located at /usr/local/mysql/bin/mysql_install_db. If it is not there you can use the following command to find it.


Note: you may need to update your locate database if the script is not found with the above command. To update your database issue one of the following commands and then repeat the above command.


Now that we have found the script we can run it


Restore the Backup

The first step is to take your backup file and uncompress it.


If mysql.sql was not listed in the backup you can either manually reset the permissions or copy it from the backup you just made.


Now lets set the ownership of the directory and all files in the directory. Make sure to use the appropriate group and user with the below command.


Now create any databases that you are going to restore with a command like the following (repeat the command for every database that you will restore):


Restore the backup with the below command and repeat the command for every sql file/database that you will restore.


Startup MySQL

Whether you are an individual user sending email to friends & colleagues, or use a commercial email-sending service like Constant Contact, it is important to regularly check your sending ip address hasn't been black listed.

Today we released a simple tool that lets you easily test if your mail server is black listed with any popular black-list DNS service:

Email mail server Blacklist Checker



-

Don't know your sending mail server ip address? Send an email to yourself, then view the message headers.

View 1 Comment(s)

Everyone knows video helps conversions. But most people who incorporate a video on their landing pages, use Amazon S3 to host the video, or use one of the dozen video sites like Viddler, Vimeo, Veoh etc.



Back in 2007, Adrian wrote about how we were migrating all of our videos from YouTube to ScreenCast.

Back then (maybe) it made sense... YouTube didn't have High Quality and we were doing primarily screen-cast type videos.

But nowadays, if you're not hosting your landing-page videos on YouTube, you're missing out big time!

5 reasons to use YouTube for your landing page videos

1. YouTube is where your traffic is



2. YouTube now offers full high definition

3. YouTube is the longest standing video service. Owned by Google, it's future is rock solid while some of the other video-hosting sites are facing financial difficulties.

4. YouTube channels and subscriptions actually work. Offer great content and you'll build a loyal following.

5. PPC traffic to Youtube videos on your landing page equals free traffic.

This is Key:

If you embed a YouTube video on your landing page (use &autoplay=1 to get the video to automatically play as son as the page loads), every page view translates to 1 view on YouTube.

The more views your video gets, the higher it will rank in YouTube and Google organic searches! Tag your video with the right keywords and include them in the video title. Pretty soon, you'll find you're getting lots of free clicks from Google search results.

You can even turn-off your PPC traffic later on and the traffic will continue.

View 2 Comment(s)

In a previous post, Kyle covered How to fix MySQL Replication, by using a script that looks up records in the master/slave, inserting any missing records as well as updating any ones that don't exactly match.

But how can you tell your replicated MySQL databases are no longer in sync?

Since some queries are never safe to run in a replicated environment (i.e. DELETE FROM table LIMIT 1), without having the proper experience, it's easy to develop replication inconsistencies that could lead to fatal errors.

The easiest way to monitor replicated databases are in-sync is by using MySQL built-in CHECKSUM function, that calculates a checksum for each table based on the values, number of rows and number of columns in the table.

Calculating a table checksum is a very quick operation!

If a table on the master generates a different checksum than the same table on the slave, it means the two are not in sync and you should look deeper.

The code below handles verifying two databases (master/slave or master/master), printing a list of all tables where the checksum doesn't match:


I strongly recommend setting this up on a cronjob, so that you identify any replication issues as quickly as possible.

Be sure to also read my post about a Better MySQL Replication monitor. Don't trust MySQL built-in "Seconds behind slave" reading, it's not reliable.