MySQL Replication is a powerful tool and it’s hard to find a production system not using it. On the other hand debugging replication issues can be very hard and time consuming. Especially if your replication setup is not straightforward and you are using filtering of some kind.
Recently we got an alert from our monitoring system that replication stopped on production slave with the following error:
Can't find record in 'some_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000011, end_log_pos 735766642
This means that a ROW-based replication event was going to be applied on slave, but could not find the row it was supposed to be applied to. This is something I like about ROW format — it allows you to catch such data synchronization issues right away. In this particular case MIXED format was used, but if[Read more...]
A few days ago, we faced an interesting problem on one of our customer’s slave mysqld servers. An Alter for adding a new column was run on master server took 542 seconds where as it took few hours on the slave server to complete due to a SELECT blocking the Alter was not allowed to complete.
Here is the the select on the master server and it’s execution time from the binary logs.
# at 825737566 #110720 19:55:21 server id 31415 end_log_pos 825737730 Query thread_id=83250629 exec_time=542 error_code=0 use collect/*!*/; SET TIMESTAMP=1311216921/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; ALTER TABLE `track` ADD `source_track_id` int(10) UNSIGNED NULL DEFAULT NULL AFTER `vendor_track_id` /*!*/;
Alter statement completed well on the[Read more...]
Yesterday I was discussing with a fellow DBA about ways to check the status of existing and/or past RMAN jobs. Good backup scripts usually write their output to some sort of log file so, checking the output is usually a straight-forward task. However, backup jobs can be scheduled in many different ways (crontab, Grid Control, Scheduled Tasks, etc) and finding the log file may be tricky if you don’t know the environment well.
Furthermore, log files may also have already been overwritten by the next backup or simply just deleted. An alternative way of accessing that information, thus, may come handy.
Fortunately, RMAN keeps the backup metadata around for some time and it can be accessed through the database’s V$ views. Obviously, if you need this information because your database just crashed and needs to be restored, the method described here is useless.[Read more...]
Fahd states that “Oracle Exadata Database Machine” has most changed his life – changing the game, and setting very high standards of performance, support, scalability, reliability and unification.
Shout out to Fahd from your peers at Pythian!
I guess there might be just a little truth to Pythian’s growing reputation as an “Oracle ACE Factory” ;), as recently mentioned by Justin Kestelyn in the May 11, 2011 OPN PartnerCast:[Read more...]
It’s a busy summer at Pythian, with our continuing wave of speaking sessions at upcoming community and regional industry events.
Coming to a city near you, watch for Pythian presenting hot Oracle and Microsoft SQL Server database topics:
Oracle Technology Days – Montreal
August 9, 2011 – 8:30am – 1pm, Hilton Montreal Bonaventure
Oracle Technology Days – Toronto
August 25, 2011
Just a few days ago I received a reminder email from Burke Scheld for the “AUSOUG National Conference Series – Perth 2011 – Call for Papers”. I had an event-related conversation with several Oracle guys in my professional networks and the answers I received triggered this blog post. Some of the very good Oracle professionals I personally respect said “…I am not sure what I would get out of it …” or “…I haven’t done anything exciting for the last FEW MONTHS …”.
The answers I received shocked me a bit. Typically I am in the opposite situation where I have so many good things happening I would love to share with the world that I
On many of our clients, we have a need to run XtraBackup as a regular OS user. Aside from running into the issue where tar4ibd was not provided with Percona’s xtrabackup-1.6.2.tar.gz package, our main issues have been with permissions when attempting a streaming backup.
I have found the following:
The other day while trying to move a schema from one MySQL server to another, I encountered a very odd issue. The schema to be moved contained both MyISAM and InnoDB tables, so the only option I had was to dump the schema using mysqldump on the source server and import it on the destination server. The dump on the source server went fine with absolutely no issues but it failed to import into the second server, and the error message was:
Can't create/write to file ‘/disk1/activity.MYI’ (Errcode: 2)
This was an extremely odd message as the data directory on the destination server was properly setup in terms of ownership and permission. The source and destination MySQL servers have been running without issues for months. Prior to the error, four tables in the dump file were imported into the destination server without any issues whatsoever.
As the birds have started their yearly migration back to their homes from the warmer areas to the relative less cooler areas in summer, bloggers are also touching base with the technologies which they cherish most and coming back with some master strokes. This new cool edition of Log Buffer, the coolest blog carnival covering hottest topics encompass that home coming. Now Chill with Log Buffer #222!!!
Charles Hooper blogs about an Overly Complicated Use Case Example regarding Row Values to Comma[Read more...]