We don’t often see this option configured (default: unlimited) but it might be a good idea to set it. What it does is limit the amount of disk space the combined relay logs are allowed to take up.
A slave’s IO_Thread reads from the master and puts the events into the relay log; the slave’s SQL_Thread reads from the relay log and executes the query. If/when replication “breaks”, unless it’s connection related it tends to be during execution of a query. In that case the IO_Thread will keep running (receiving master events and storing in the relay log). Beyond some point, that doesn’t make sense.
The reason for having two separate replication threads (introduced in MySQL 4.0) is that long-running queries don’t delay receiving more data. That’s good. But receiving data is generally pretty fast, so as long as that basic issue is handled, it’s not necessary (for performance) to have the IO_Thread run ahead that far.
So you can set something like relay-log-space-limit=256M. This prevents slave disk space from getting gobbled up in some replication failure scenarios. The data will still be available in the logs on the master (provided of course the log expiration there isn’t too short – replication monitoring is still important!).
Conclusion: the relay log as a cache. Don’t leave it at “Unlimited”, that’s inefficient (and potentially problematic) use of resources. If you do run out of diskspace, the relay log can get corrupted – then you have to reposition, which will re-read the data from the master anyway.
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 (yes the error string is exactly this, very long – sorry I did not paste this string into the original post – updated later):
“Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.”
In other similar cases the error message is about something else but the query it shows with it makes no sense. To me, that essentially says the same as the above.
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?
This Thursday (October 22nd, 13:00 UTC), Walter Heck (of Open Query) will present Dual Master Setups With MMM. MMM (Multi-Master Replication Manager for MySQL) is a set of flexible scripts to perform monitoring/failover and management of MySQL master-master replication configurations (with only one node writable at any time). Session slides (PDF).
The toolset also has the ability to read balance standard master/slave configurations with any number of slaves, so you can use it to move virtual IP addresses around a group of servers depending on whether they are behind in replication. For more
information, see mysql-mmm.org.
For MySQL University sessions you point your browser here. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don’t have to.
The favourite Open Query course modules as well as reworked and brand new ones, with November/December 2009 dates for Brisbane, Sydney, Canberra and Melbourne listed below. You can register for days/modules individually, to suit your time, budget and current needs. Your trainers are Sean, Ray and Arjen (see OQ people).
For the Canberra and Melbourne days which are DBA/HA, registrations for all of the modules in a series before 15 October will receive a copy of the “High Performance MySQL” book (normal bookstore price is AUD 105).
- Thu 19 Nov: MySQL Query Performance Optimisation and Tuning
- Fri 20 Nov: MySQL Server Performance Optimisation and Tuning
I arrived yesterday in St. Augustin, near Bonn in Germany. After a good day of hitchhiking (weather is beautiful here) I stayed with my Pakistani Couchsurfing host and we had an extremely interesting evening talking about the gigantic cultural differences between western civilization and Pakistani civilization. It beats staying in a hotel by about a million points 🙂
This morning I headed to the FrOScon HQ at the fachhochschule to help out with whatever was needed. Turns out that was a bit premature (misunderstanding on my part), so I have had some time to catch up on mail and give some more attention to my talk on Saturday. I’ll be helping out throughout the and the whole day tomorrow with things though.
I’ll be talking about MySQL MMM, a project that I have invested quite a bit of time in getting to know. My talk will outline what MMM is, what it’s not and an example of our setup at Open Query. It’s a full hour long, so it should be very interesting to be able to go into that much detail.
If you are near St. Augustin, make sure to come by for Froscon, as it’s schedule has some very interesting talks and you’ll also have a good chance to meet fellow MySQL-geeks in the OpenSQLCamp dev-room.
This post doesn’t contain a tip, I’m asking the q and I don’t know the answer yet. Could just be a logic error on my part, in which case it was just a personal mystery and I thank you for helping!
MySQL error 1449 “There is no ‘username’@'host’ registered”
You can get a replication fail with this error if you have triggers, views or stored routines that run as their creator (which is usual) but the slave does not have that user. So far so good.
We just encountered a replication fail on multiple slaves (i.e. not a single isolated mishap) with this error on an UPDATE statement accessing a single base table (i.e. no view), and no triggers defined or routines called in the query. Indeed the slave did not have the reported user (intentionally), but that shouldn’t have mattered. Creating the user allowed replication to continue successfully – that’s no surprise either.
The question is, what condition actually caused this error?
For ref, the original error message; obfucated but structurally unchanged:
[ERROR] Slave: Error ‘There is no ’someuser’@'%’ registered’ on query. Default database: ’somedb’. Query: ‘UPDATE `t1` SET `name` = ’some text string’, `description` = ‘another text string’, `date_taken` = ”, `place_taken` = ”, `modified` = ‘2009-06-24 12:06:16′, `updated_by` = 1, `modified_on` = 1245773176 WHERE `t1`.`id` = 361′, Error_code: 1449
UPDATE (2009-06-26) ok it was an Arjen fail, on a recheck of all the possible situations where permissions are checked (views, stored procs, triggers), I found that there’s 1 trigger in the entire system and it happens to be on this table. So, no bug and my mistake. Issue resolved! Thanks to all those helping!
I spent some time earlier this week trying to debug a permissions problem in Drupal.
After a lot of head-scratching, it turned out that Drupal assumes that when you run INSERT queries sequentially on a table with an auto_increment integer column, the values that are assigned to this column will also be sequential, ie: 1, 2, 3, …
This might be a valid assumption when you are the only user doing inserts on a single MySQL server, but unfortunately that is not always the situation in which an application runs.
I run MySQL in a dual-master setup, which means that two sequential INSERT statements will never return sequential integers. The value will always be determined by the auto_increment_increment and auto_increment_offset settings in the configuration file.
In my case, one master will only assign even numbers, the other only uneven ones.
My patch was accepted, so this problem is now fixed in the Drupal 7 (and hopefully soon in 6 as well) codebase.
The moral of the story is that your application should never make such assumptions about auto_increment columns. A user may run the application on a completely different architecture, and it may break in interesting and subtle ways.
If you want to use defined integers like Drupal does, make sure you explicitly insert them. Otherwise, you can retrieve the assigned number via the mysql_insert_id() function in PHP or via SELECT LAST_INSERT_ID() in MySQL itself.
Have you checked your code today?
A little challenge for you… given an existing app that does not know about separate master/slave connections, and you want to enable working in a replicated infrastructure. Simply redirecting all SELECTs to the slave connection will not work. Why?
Hint: there are at least two reasons, depending on other factors. There may be more.
Comments are set to be moderated so providing answers will not spoil it for others. I’ll leave it run for a bit and then approve all comments.
Well isn’t that interesting, hidden all the way at the end of the MySQL 5.4 information are two words that really peaked my interest: Replication Heartbeat. And it wasn’t even using caps or other highlighting in the original text. Reading through the feature list of 5.4, I’m very impressed. All necessary/useful stuff for the real world, no marketing or enterprise blah.
Of course we’ll have to explore it in detail to have more opinion. Proof is in production, not paper. As this is the first most of us have heard/seen of it, it’ll take time to explore. Someone who tried to install the tarball this morning got an assertion during the system table installation. That’s not the best first impression, but that might be a build issue. I’m really pretty excited about the lineup of actual useful features.
Update… ok so at the end of the announcement it refers to a Preview Release (Edwin, can you please murder this ghastly oxymoronic abomination in OSS Marketing?) and then a note that apart from some InnoDB performance patches the described enhancements are not actually in the version that was just made available. There will be future announcements/releases. Was I just giving compliments for actual delivery, and it turns out it’s vaporware after all. Aaargh!!!
So, we’ll be perusing https://launchpad.net/mysql-server/5.4 instead. The old “show us the code” works best. Let’s hope the code is actually there.