Open Query now has its own @openquery account on Twitter and Identi.ca so you can conveniently follow us there for announcements and tips – and also ask us questions! All OQ engineers can post/reply. The OQ site front page also tracks this feed.
Previously I was posting from my personal @arjenlentz account with #openquery hashtag, but that’s obviously less practical.
With the exhaustion of IPv4 address space looming sometime in 2012; probably earlier rather than later, it makes sense to ease on into IPv6 land. Without straying into tunnel broking and endpoint shenanigans 6to4 is a method of wrapping up IPv6 inside of IPv4.
(note that MySQL does not currently support IPv6 itself – but what we’re discussing here is about externally facing systems, like your web/application servers)
6to4 performs three functions:
As such its pretty easy to implement, especially on our good friend Debian (and its better looking cousin Ubuntu).
I am going to step through setting up a Debian host at Linode.
Now, the first caveat is that you must be running a 2.6.20+ kernel (At the time of writing the latest linode kernel for Debian was : 2.6 Paravirt (2.6.34-x86_64-linode)). The default ‘Etch’ release kernel (2.6.18) supports IPv6 but woefully implements IPv6 stateful connection tracking, which is just not good enough for a decent firewall. If you have a look under your Linode Configuration Profile you can see what Kernel you are running, and change it to one that is supported; obviously a reboot would be in order if you change it. The linode kernels have IPV6 support compiled in.
Any IPv6 address that begins with the 2002::/16 prefix is known as a 6to4 address, as opposed to a native IPv6 address which does not use that prefix. The Internet Assigned Numbers Authority (IANA: www.iana.org) has set aside this address space just for 6to4. IPv6 addresses are assigned based upon your IPv4 address; for instance, 74.207.254.16 would become 2002:4acf:fe10::/48
We need some tools to help us calculate our IPV6 address, luckily there is a package for this
$ sudo apt-get ipv6calc
Now its a matter of plugging in your IPv4 address into ipv6calc to determine your reserved IPv6 address range.
$ ipv6calc -q --action conv6to4 --in ipv4 74.207.254.16 --out ipv6
and voila your IPv6 address range appears:
2002:4acf:fe10:: (/48)
You get given an address range with a prefix length of 48 bits, which leaves room for a 16-bit subnet field and a 64 bit host address within the subnet.
You now need to edit your network configuration file /etc/network/interfaces file
auto tun6to4 # make sure this interface comes up on boot
iface tun6to4 inet6 v4tunnel
address 2002:4acf:fe10::1 #first host in this address range
netmask 16
gateway ::192.88.99.1 #special anycast address for 6to4 (2002:c058:6301::)
endpoint any
local 74.207.254.16
mtu 1472
ttl 255
Restart your interfaces:
$sudo /etc/init.d/networking restart
Now it’s fairly important (read as critical) to firewall IPv6 stuff as it is with IPv4. Here is a small sample of a firewall that will at the very least not leave you hanging in the breeze. Needless to say you can add your own rules and make this as complex as you need.
# Initialize all the chains by removing all the rules
iptables --flush
iptables -t nat --flush
iptables -t mangle --flush
ip6tables --flush
ip6tables -t mangle --flush
# The loopback interface should accept all traffic
iptables -A INPUT -i lo -j ACCEPT
iptables -A OUTPUT -o lo -j ACCEPT
ip6tables -A INPUT -i lo -j ACCEPT
ip6tables -A OUTPUT -o lo -j ACCEPT
#Allow IPV6 packets to come over the tunnel
iptables -A INPUT -p ipv6 -i eth0 -j ACCEPT
iptables -A OUTPUT -p ipv6 -o eth0 -j ACCEPT
# Allow outbound DNS queries from the FW and the replies too
iptables -A OUTPUT -p udp -o eth0 --dport 53 --sport 1024:65535 -j ACCEPT
iptables -A INPUT -p udp -i eth0 --sport 53 --dport 1024:65535 -j ACCEPT
ip6tables -A OUTPUT -p udp -o tun6to4 --dport 53 --sport 1024:65535 -j ACCEPT
ip6tables -A INPUT -p udp -i tun6to4 --sport 53 --dport 1024:65535 -j ACCEPT
# Accept and reply to ICMP ping
iptables -A OUTPUT -p icmp --icmp-type echo-request -j ACCEPT
iptables -A OUTPUT -p icmp --icmp-type echo-reply -j ACCEPT
iptables -A INPUT -p icmp --icmp-type echo-request -j ACCEPT
iptables -A INPUT -p icmp --icmp-type echo-reply -j ACCEPT
# IMPORTANT!!!! Allow all icmpv6 because they make IPV6 work
ip6tables -A OUTPUT -p icmpv6 -j ACCEPT
ip6tables -A INPUT -p icmpv6 -j ACCEPT
# Allow previously established connections
iptables -A OUTPUT -o eth0 -m state --state ESTABLISHED,RELATED -j ACCEPT
ip6tables -A OUTPUT -o tun6to4 -m state --state ESTABLISHED,RELATED -j ACCEPT
# Allow port 80 (www) and 51515 (SSH) connections to the firewall
iptables -A INPUT -p tcp -i eth0 --dport 51515 --sport 1024:65535 -m state --state NEW -j ACCEPT
iptables -A INPUT -p tcp -i eth0 --dport 443 --sport 1024:65535 -m state --state NEW -j ACCEPT
iptables -A INPUT -p tcp -i eth0 --dport 80 --sport 1024:65535 -m state --state NEW -j ACCEPT
ip6tables -A INPUT -p tcp -i tun6to4 --dport 51515 --sport 1024:65535 -m state --state NEW -j ACCEPT
ip6tables -A INPUT -p tcp -i tun6to4 --dport 443 --sport 1024:65535 -m state --state NEW -j ACCEPT
ip6tables -A INPUT -p tcp -i tun6to4 --dport 80 --sport 1024:65535 -m state --state NEW -j ACCEPT
# Allow port 80 (www) and 443 (https) connections from the firewall
iptables -A OUTPUT -j ACCEPT -m state --state NEW,ESTABLISHED,RELATED -o eth0 -p tcp -m multiport --dport 51515,80,443 -m multiport --sport 1024:65535
ip6tables -A OUTPUT -j ACCEPT -m state --state NEW,ESTABLISHED,RELATED -o tun6to4 -p tcp -m multiport --dport 51515,80,443 -m multiport --sport 1024:65535
# Allow previously established connections
iptables -A INPUT -j ACCEPT -m state --state ESTABLISHED,RELATED -i eth0 -p tcp
ip6tables -A INPUT -j ACCEPT -m state --state ESTABLISHED,RELATED -i tun6to4 -p tcp
# The policy should be to drop it
iptables -A INPUT -j DROP
iptables -A OUTPUT -j DROP
iptables -A FORWARD -j DROP
ip6tables -A INPUT -j DROP
ip6tables -A OUTPUT -j DROP
ip6tables -A FORWARD -j DROP
I usually create a directory called /etc/iptables (owner root:root / permissions 750) and drop firewall up and down scripts in there.
Then it is a simple matter of adding the following scripts to the bottom of your eth0 interface definition stanza in /etc/network/interfaces to invoke them on boot or whenever:
pre-up /etc/iptables/firewall_up.sh
post-down /etc/iptables/firewall_down.sh
IMPORTANT: Just a quick note don’t block icmpv6 because it is the glue that holds IPv6 together.
I am not going to over explain this one because everyone has an opinion on how to setup DNS but in essence you need to add a line like this to your zone file. There are plenty articles outlining this stuff.
hyosine AAAA 2002:4acf:fe10::1
You now need to setup reverse DNS for your address, so using our example of 2002:4acf:fe10 you will have to configure the zone of ”0.0.0.0.0.1.e.f.f.c.a.4.2.0.0.2.ip6.arpa” in your name servers. The zone should have PTR records for your hosts just like an in-addr.arpa zone for IPv4, but with hex digits of the IPv6 address backwards, separated by dots. Using our example, the 6to4 host will have a ::1 suffix, so a reverse DNS record looks like:
1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.1.e.f.f.c.a.4.2.0.0.2.ip6.arpa. PTR hyosine.openquery.com.
You will need to register this zone and its servers with the 6to4 reverse zone authority. eg https://6to4.nro.net/
The ping6 utility is probably best to test whether your host is now working. It’s probably best to try the IPv6 address first:
$ ping6 2002:4acf:fe10::1
PING 2002:4acf:fe10::1(2002:4acf:fe10::1) 56 data bytes
64 bytes from 2002:4acf:fe10::1: icmp_seq=1 ttl=60 time=1.59 ms
64 bytes from 2002:4acf:fe10::1: icmp_seq=2 ttl=60 time=1.42 ms
Now you can try with the DNS name you just setup.
$ ping6 hyosine.cloudcaster.com
PING hyosine.cloudcaster.com(2002:4acf:fe10::1) 56 data bytes
64 bytes from 2002:4acf:fe10::1: icmp_seq=1 ttl=60 time=1.41 ms
64 bytes from 2002:4acf:fe10::1: icmp_seq=2 ttl=60 time=1.34 ms
At this year’s conference, I was pleasantly surprised with the high level of interest in Open Query’s proactive services for MySQL and MariaDB, and specifically our focus on preventing problems, while explicitly not offering emergency services.
I’ll describe what this is about first, and why I reckon it’s interesting. When you think about it, most IT related support that includes emergency (24×7) operates similar to this:
You have a house that has the front and back doors wide open with no locks, and you take out an insurance policy for the house contents. After a short time you call the insurance company “guess what, the most terrible thing happened, my TV got stolen.” Insurance company responds “that’s dreadful, you poor soul, let us fix it all up for you with getting a new TV and installing it. It’ll be our pleasure to serve you.” A few weeks later you call the insurance company again “guess what …” and they help you in the same fabulous way.
You get the idea, it’s rather silly because it’s very predictable. If you leave your doors open, you’re very close to actually being the cause of the problem yourself and insurance companies tend to not cover you under such circumstances – yet most IT support arrangements do. If IT support were actually run like insurance, premiums would be based on a risk assessment, and consequentially most companies would have to pay much higher premiums.
Much of this is actually about company processes as much as the technical setup. Depending on how you arrange things in your business, you can actually be very “emergency prone”. Since company processes are notoriously hard to change, many businesses operate in a way that is fundamentally not suitable for Open Query to do business with. That’s a fact and we’re fine with it, the market is big enough. We have clients all around the world, but so far very few from Silicon Valley. My presumption was that this was due to the way those businesses are often set up, making them simply incompatible for our services. But a significant number of companies we spoke with at and around the conference were very interested in our services exactly because of the way we work, and so that to me was interesting news. A good lesson, making attending the conference extra worthwhile. It’s also a good vote of confidence in the way we’ve set up our service offering.
Walter and I are giving a tutorial on Monday morning, MySQL (and MariaDB) Dual Master Setups with MMM, I believe there are still some seats available – tutorials are a bit extra when you register for the conference, so you do need to sign up if you want to be there! It’s a hands-on tutorial/workshop, we’ll be setting up multiple clusters with dual master and the whole rest of the MMM fun, using VMs on your laptops and a separate wired network. Nothing beats messing with something live, breaking it, and seeing what happens!
Then on Tuesday afternoon (5:15pm, Ballroom F), Antony and I will do a session on the OQGRAPH engine: hierarchies/graphs inside the database made easy. If you’ve been struggling with trees in SQL, would really like to effectively use social networking in your applications, need to work with RDF datasets, or have been exploring neo4j but otherwise have everything in MySQL or MariaDB, this session is for you.
We (and a few others from OQ) will be around for the entire conference, the community dinner (Monday evening) and other social events, and are happy to answer any questions you might have. You’ll be to easily recognise us in the crowds by our distinct friendly Open Query olive green shirts (green stands out because most companies mainly use blue/grey and orange/red).
Naturally we would love to do business with you (proactive support services, OQGRAPH development), but we don’t push ourselves on to unsuitable scenarios. In fact, we’re known to refer and even actively introduce clients to competent other vendors where appropriate. In any case, it’s our pleasure and privilege to meet you!
See you all in Santa Clara in a few days.
On this big trip, I made particular effort to finally visit Monty at his home near Helsinki. Somehow, in all my years at MySQL AB, this never happened – a sad omission. So, I spent the Easter days with Monty, Anna and now 5yo Maria.
I’m not a fan of most meetings, and in many cases in-person meetings are not actually necessary to get things organised or done, but I think this was both most enjoyable as well as productive for our respective businesses and joint interests. Good company, discussion, food, drink, sauna… fabulous.
It’s a great pity we live on opposite sides of the planet, as we do get along very well together. We definitely don’t agree on everything, but we’re always absolutely direct with each other, and try to provide good arguments whenever we disagree, to explore things further.
Matt Asay writes today in Oracle loses some MySQL mojo about Ken Jacobs leaving Oracle. For me, that’s a major bummer. Ken has been a long-time visitor of the MySQL Conference and that’s where I first met him: a friendly and knowledgeable person, on database technology in general but also about MySQL. When Innobase Oy got bought by Oracle, InnoDB got placed under Ken’s leadership and did pretty well there. We’d occasionally exchange emails, and I’ve always found him to be responsive and helpful.
I think it was kinda presumed by people that the technical part of MySQL at Oracle would also reside with Ken. Obviously now, that’s not going to be the case. What that means exactly, I don’t know as I am not familiar with the other person (Edward Screven). We’ve got to know Ken over the years, so it would’ve been nice to keep going with him. Ohwell.
Now we’ll see what Edward does with it all, and how will interact with the MySQL community. And I wonder what new adventures Ken might be off to, if any?
At DrupalSouth 2010 (Wellington) after LCA2010, Peter and I implemented a Drupal module as a practical example of how the OQGRAPH engine can be used to enable social networking trickery in any website. The friendlist_graph module (available from GitHub) extends friendlist, which implements basic functionality of friends (2-way) and fans (1-way) for Drupal users.
The friendlist_graph module transposes the friendlist data using an OQGRAPH table, allowing you to query it in new and interesting ways. By adding some extra Drupal Views, it allows you to play Six Degrees of Kevin Bacon with your Drupal users or find out how two arbitrary users are connected. It can find a path of arbitrary length near-instantly. Previously, you’d just avoid doing any such thing as it’s somewhere between impossible/limited/slow/painful in a regular relational schema.
Now think beyond: retrieve/share connections using Open Social, FOAF, Twitter/Identi.ca, logins with OpenID, and you “instantly” get a very functional social networking enabled site that does not rely on localised critical mass!
We tested with about a million users in Drupal (and approx 3.5 million random connections), which worked fine – the later demo at the DrupalSouth stuffed up because I hadn’t given the demo VM sufficient memory.
Naturally, you could do the same in Joomla! or another CMS or any site for that matter, we just happened to be at DrupalSouth so a Drupal module was the obvious choice. Take a peek at the code, it’s pretty trivial. Just make sure you run a version of MySQL that has the OQGRAPH engine, for instance 5.0.87-d10 (Sail edition!) from OurDelta.
MySQL requires special consideration in the Oracle+Sun merger, otherwise both Oracle and MySQL users and vendors will literally pay the price. If you agree, please sign this petition now.
To be very clear, Open Query is in favour of the merger, we feel that overall it’s a good fit. We would also like to see it happen quickly, as obviously this is best for Sun employees and clients, as well as Oracle’s broad business prospects. Read more
Many configs have thread_stack_size configured explicitly, but that can cause rather bad trouble:
Let mysqld sort it out, on startup it does a calculation based on the CPU architecture, and that’s actually the most sensible. So for almost all setups, remove any thread_stack_size=… line you might have in my.cnf.
We got good responses to the “identify this query profile” question. Indeed it indicates an SQL injection attack. Obviously a code problem, but you must also think about “what can we do right now to stop this”. See the responses and my last note on it below the original post.
Got a new one for you!
You find a system with broken replication, could be a slave or one in a dual master setup. the IO thread is still running. but the SQL thread is not and the last error is something weird, the query makes no sense and appears to contain some garbage. The server appears to have been restarted recently.
What’s wrong, and what’s your quickest way to get replication going again given this state?