I had an issue come up recently that involved some confusion over permissions for the same user, connecting through different interfaces. For example, say you have a server with the public IP address of 192.168.0.1. You could connect to it from the local machine using the following commands:
shell> mysql -h localhost # Connects through the socket file
shell> mysql -h 127.0.0.1 # Connects through the loopback interface
shell> mysql -h 192.168.0.1 # Connects through the network interface
They all connect to the local server, but they can all have different permissions. Here are a couple of rules to make your life easier:
- Don’t use @127.0.0.1, unless you absolutely can’t use the socket file for some reason. Connecting through @localhost is usually faster than the loopback device, and it’s easier to type.
- Only connect through the network interface if you’re planning on moving the application to a different server later on.
That’s all. 🙂
It will only cause you pain.
To give a brief description of the problems I had:
- Apache is horribly slow. To the point that some RSS readers were having problems subscribing to my blog.
- Apache setup is weird, and causes all sorts of problems.
- FTP access is mind-boggling slow, only allows one connection at a time, and at one point, wouldn’t let me download directories.
- MySQL is sickeningly slow.
- More expensive than better hosts.
- Really bad web interface for site management.
Now, I’m happily away from them. Never again.
A problem we occasionally see is Relay Log corruption, which is most frequently caused by network errors. At this point in time, the replication IO thread does not perform checksumming on incoming data (currently scheduled for MySQL 6.x). In the mean time, we have a relatively easy workaround: encrypt the replication connection. Because of the nature of encrypted connections, they have to checksum each packet.
Solution 1: Replication over SSH Tunnel
This is the easiest to setup. You simply need to do the following on the Slave:
shell> ssh -f firstname.lastname@example.org -L 4306:master.server:3306 -N
This sets up the tunnel. slave.server:4306 is now a tunnelled link to master.server:3306. So now, you just need to alter the Slave to go through the tunnel:
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO master_host='localhost', master_port=4306;
mysql> START SLAVE;
Everything else stays the same. Your Slave is still connecting to the same Master, just in a different manner.
This solution does have a couple of downsides, however:
- If the SSH tunnel goes down, it won’t automatically reconnect. This can be fixed with a small script that restarts the connection if it fails. The script can be added to your init.d setup, so it automatically opens on server startup.
- If you use MySQL Enterprise Monitor, it won’t be able to recognize that the Master/Slave pair go together.
Solution 2: Replication with SSL
Replication with SSL can be trickier to setup, but it removes the two downsides of the previous solution. Luckily, the MySQL Documentation Team have done all the hard work for you.
If you’re seeing corruption problems in your Relay Log, but not in your Master Binary Log, try Solution 1. It’s quick to setup and will determine if encryption is the solution to your problem. If it works, setup Solution 2. It will take a little bit of fiddling around, but is certainly worth the effort.
My partner comes from a large family. At Christmas time, rather than making everyone buy presents for everyone else (a rather expensive venture), we have a Secret Santa tradition. Unfortunately, as the years have gone on, it has become more difficult to organize for everyone to be in the one place to “pick a name out of a hat”.
And so, in my never-ending quest to remove the magic and joy from Christmas, I wrote a script to automatically assign everyone their Santa/Santee, and send them an SMS. It has a few cool things to it:
- Allows you to prevent some people from being paired up. For example, you might not want a couple in the family to be assigned each other, because they’ll probably buy each other presents anyway.
- Makes sure that everyone has a Santee before it starts sending messages, to prevent SMS spam.
- Checks that all messages went through successfully.
I found that ValueSMS worked nicely for sending messages in Australia, you might need to use a different one for your country. Any gateway that has a HTTP(S) interface will do the job.
To set it up for yourself, you’ll need to do the following:
- Edit the @names array to create a list of everyone participating (beginning line 12).
- Edit the @people array to give the details of everyone participating (beginning line 20).
- Edit in your ValueSMS username/password, or setup the HTTP(S) URL for your preferred SMS provider (beginning lines 101, 139).
Here it is, for your enjoyment:
0.1 – 2009-02-16: secretsanta.pl.gz
One request I occasionally see is for a case insensitive version of REPLACE() for MySQL. I wrote this a while back, but here it is now for all of you to play around with. It uses a basic naïve string search algorithm, so can be slow under some circumstances.
[code lang=”sql”]DELIMITER $$
DROP FUNCTION IF EXISTS
replace_ci ( str TEXT,needle CHAR(255),str_rep CHAR(255))
DECLARE return_str TEXT DEFAULT ”;
DECLARE lower_str TEXT;
DECLARE lower_needle TEXT;
DECLARE pos INT DEFAULT 1;
DECLARE old_pos INT DEFAULT 1;
IF needle = ” THEN
SELECT lower(str) INTO lower_str;
SELECT lower(needle) INTO lower_needle;
SELECT locate(lower_needle, lower_str, pos) INTO pos;
WHILE pos > 0 DO
SELECT concat(return_str, substr(str, old_pos, pos-old_pos), str_rep) INTO return_str;
SELECT pos + char_length(needle) INTO pos;
SELECT pos INTO old_pos;
SELECT locate(lower_needle, lower_str, pos) INTO pos;
SELECT concat(return_str, substr(str, old_pos, char_length(str))) INTO return_str;
SELECT replace_ci( ‘mySQL’, ‘M’, ‘M’ );[/code]
It’s also available on MySQL Forge.
There’s a change of behaviour in MySQL 5.1.31 for Row Based Replication, if you have InnoDB transactions that also write to a MyISAM (or other non-transactional engine) table. It’s a side effect of fixing Bug #40116. Take this simple example:
Transaction 1: INSERT INTO myisam_tbl (item, val) VALUES (1, 0);
Transaction 1: INSERT INTO innodb_tbl (item, val) VALUES (1, -1), (2, -1);
Transaction 1: START TRANSACTION;
Transaction 1: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
Transaction 1: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=1;
Transaction 2: START TRANSACTION;
Transaction 2: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
Transaction 2: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=2;
Transaction 2: COMMIT;
Transaction 1: COMMIT;
After this, the Master innodb_tbl would look like this:
And the Master myisam_tbl will look like this:
In 5.1.30 and earlier, the Slave tables will be correct. However, in 5.1.31, the Slave myisam_tbl will be correct, but the innodb_tbl will look like this:
As a bonus, there’s no workaround. Statement Based Replication has never worked for this case. For an SBR Slave (In MySQL 5.0.x and 5.1.x), the Slave myisam_tbl will be correct, but the Slave innodb_tbl will look like this:
And so, we come to the moral of the story. Don’t use non-transactional tables in the middle of a transaction. Ever. You will only cause yourself more pain than you can possibly imagine. Instead, move the writes to the non-transactional tables outside of the transaction.