Due to the insane cost of bandwidth (compared to the rest of the developed world) in Australia, I've recently decided to move some of our hosting clients to Linode. This means they can move more data more cheaply and I don't need to come up with (and administer) a bandwidth accounting system for my Australian based web VM.

We pretty much exclusively use Drupal for hosting clients, so to make management a bit easier I decided to use Ægir on the new Linode. Installation was a relative breeze, after a quick google to find out how to specify that I didn't want to use Apache and wanted to use a separate server as dedicated MySQL host.

The problem (there is always a problem) arose when I needed to give a hosting client access to their Drupal installation, so they could manage themes …

Cross-region or cross-country datacenters are popular and often essential for databases that need enterprise-grade reliability and availability.
SchoonerSQL™ 5.1 provides an ideal solution for such scenarios with

• Automatic WAN failover
• Asynchronous parallel appliers across the data center for increased throughput 

Join this webinar tomorrow (November 8th - 10 am PST) to learn how SchoonerSQL 5.1:

• Maximizes service availability at high scale through its parallel threads approach
• Provides superior disaster recovery with automatic WAN failover 
• Ensures no lost data 
• Radically simplifies the management of MySQL clusters 

https://www3.gotomeeting.com/register/635284982


 

This is the third blog post in a series designed to assist companies who wish to migrate their code from Oracle to MySQL. You can read the previous post here.

I went over some of the difficult topics you’ll face when migrating from Oracle to MySQL. However, I left out the topic of database scalability (after all – this is a ScaleBase blog).

Oracle users are used to having a very clear scalability path. You start with an Oracle Standard edition, and if your budget allows, you increase hardware (memory, CPU), improve your storage speed, buy Oracle Enterprise edition and use portioning. If all that fails, you move to a distributed RAC environment. If you’re really on the high end, you buy ExaData2. This is where your journey ends. There is nothing “better”.

That’s great for enterprise …

News
Call for papers for Percona Live: MySQL Conference & Expo 2012 is open until Monday, December 5th. The MySQL Conference & Expo is Tuesday April 10 - Thursday, April 12, 2012 in Santa Clara, CA. To submit a paper, first register as a speaker at http://www.percona.com/live/mysql-conference-2012/user/register and then go to My Account -> Submit Proposal.

Normalization
Book: An Introduction to Database Systems by CJ Date

read more

The series Using X with PHP mysqli continues. After notes on calling stored procedures and using prepared statements, its time for a multiple statement quickstart. A mighty tool, if used with care…

Using Multiple Statements with mysqli

MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.

Multiple statements or multi queries must be executed with mysqli_multi_query(). The individual statements of the statement string are seperated by semicolon. Then, all result sets returned by the executed statements must be fetched.

The MySQL server allows having statements that …

If you work with bazaar, you have seen its URIs. You can find the complete list is in the bzr help urlspec. Although I commonly use only a subset of that, like bzr+ssh://bazaar.launchpad.net/~maria-captains/maria/5.2-serg/ and http://bazaar.launchpad.net/%2Bbranch/mysql-server/5.5/.

In addition I often use Launchpad aliases, such as lp:~maria-captains/maria/5.3-serg/, lp:maria/5.3, and lp:869001.

And finally, there are common abbreviations that we have used in MySQL, and others that we use in MariaDB, for example bug#12345 and wl#90.

What’s annoying, I need to remember that wl#90 corresponds to http://askmonty.org/worklog/?tid=90 and type the latter in the location bar of the …

One of the serious limitations in the fast index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX. Peter has already blogged about it before, here I’ll just briefly reiterate other cases that might benefit from that feature:

• when ALTER TABLE does require copying the data into a temporary table, secondary indexes are updated by inserting individual rows rather than sorting;
• OPTIMIZE TABLE could be faster if secondary indexes were temporarily dropped and then recreated using fast index creation;
• dumps produced by mysqldump …

This is a somewhat uncommon error, so I wanted to take a moment and post the error and an explanation in order to make it easier for those who run into this in the future.

The main error is this (full pasted below):

InnoDB: Assertion failure in thread 139838283589376 in file log/log0recv.c line 1094
InnoDB: Failing assertion: !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table)

Basically, this is what it can look like after a power outage. In fact, this is the only time I have seen it, but I won’t say that it is the only way you could encounter it.

How do I deal with this, you ask?

Well, the above error shows that the internal InnoDB dictionary is corrupt.

Unfortunately, in this situation, you must recreate the tablespace. And if you don’t have a current backup + binlogs, then that means you will also need to …

SSL-users in MySQL seem to cause quite a lot of problems for people. Quite a lot af bug reports have been posted to bugs.mysql.com over time that have most or all been classifed as ‘not a bug’. Numerous discussions exists on the Internet as well and people find weird workarounds like directly manipulating the mysql.user table.  I write this Blog because yesterday I found a discussion in a Forum where a dozen of self-appointed ‘experts’ tried to help a newbie with this causing only more confusion.

First problem: Let us try

DROP USER /*!70101  IF EXISTS */ ssss@localhost — just a ‘sidekick’, but not the point here
CREATE USER ssss@localhost;
GRANT UPDATE ON ttest.* TO ssss@localhost REQUIRE SSL;
GRANT SELECT ON ttest.* TO ssss@localhost;

Some people expect that a client would connect without encryption and would be able to SELECT from table …

Here are the slides to my second talk at last week's Percona Live event in London:

Fixed in drizzle
View more presentations from Henrik Ingo

read more