More tips and a check list for that elusive Percona Xtra DB (pxc) (galera) cluster SST or IST

While it's clear that SST/IST are the magic sauce of Galera and their implementation in MySQL by Percona there are dragons lurking that can prevent this from working.  It seems that lots of people have this issue so I created this checklist you can use to try and resolve your IST/SST issues.  At one time or another every one of these issues has caused me to have an SST/IST fail.

 In general, when debugging SST/IST issues it is best to use a specific donor for your joiner node so that you can isolate your troubleshooting and testing to a specific set of nodes.

Checklist

• Are the nodes in the same cluster?  Check wsrep_cluster_name
• Is the server_id the same for all nodes in the cluster?
• Is wsrep_node_address correct?  A typo here causes very interesting errors.
• Is wsrep_node_name correct?  Again, a simple error here causes SST to fail.
• Is gcom the same for all nodes?  Triple check this.  Use nslookup on every address to check for correctness.   Do a reverse nslookup to make sure fqdn resolves to the expected ip address.  
• Is wsrep_sst_donor a fqdn or node name?  Do not use an ip address in this field.  Refer to the documentation for methods to code this. 
• Are ports 3306, 4444, 4567, and 4568 open?  This is a biggie so see below for tips on how to test.
• Are you using xtrabackup_v2 for your wsrep_sst_method?  If not you should.  My experience with rsync, mysqldump, and the deprecated xtrabackup (no _v2) have been poor.  
• Are xtrabackup versions the same on all nodes?  
• Is /etc/hosts fqdn and ip address correct on all nodes?  Test using dns for every name and ip address you see.
• Make sure you examine every ERROR in the MySQL error log and sometimes the WARNINGS are also worth a look.  Infrequently another error can cause SST/IST to fail for issues actually unrelated to either SST or IST.  
• is socat working?  (see below on how to use socat to test ports._)
• will the MySQL server start at all?  If you have a valid install you can try something simple like mysqld --wsrep-recover to see if MySQL will even start.  If your datadir is invalid then blow it away and reinitialize MySQL using mysqld initialize_insecure to bootstrap mysql using your my.cnf and then try to start it using mysqld --wsrep-recover only to make sure it will start.  It may uncover other issues you have with MySQL that are not specific to SST/IST.  This helped me once when an error existed in my.cnf which was not logged to the error log until I ran this standalone test.  

Port testing

Now about those pesky ports.  The best way to test this is using socat which is the same utility that sst/ist uses to move data between nodes.  The socat utility established a bi-directional tunnel between hosts and should be tested on both hosts.   Substitute your ip address below for donor and joiner.  You must test this in both directions to be certain SST/IST will work.

run this pair of tests for each port 4444, 4567, and 4568.  

from the joiner run this
    socat TCP4-LISTEN:4444 STDOUT    # this starts a listener on the port

from the donor run this.  You should see hello on your joiner

    echo "hello" | socat -t 30 tcp:

:4444 -

Now reverse this and do the same where the donor becomes the listener.  Repeat for all three pots.  If your socat works then you have eliminated ports as an issue.  So go back and check the items in the checklist once again.  

For port 3306 use the MySQL client from each host to contact the other host.  Since your joiner is down you may need to start it without using the cluster in order to ensure that port 3306 is open.  (See the checklist on how to get a bare bones mysqld setup to run.) 

I hope this is useful.

See also this article (somewhat dated and redundant) from Percona.
https://www.percona.com/blog/2014/12/30/diagnosing-sst-errors-with-percona-xtradb-cluster-for-mysql/

percona SST fails with broken pipe or no route to host

I ran into a very strange set of issues when trying to build a PXC cluster node using SST.  The SST kept failing either due to "broken pipe" on both the donor and joiner or "no route to host" which was seen frequently on the joiner and less so on the donor.

Lots of head scratching and work with the network team finally figured this one out.  Percona support also played an important role.  We first started digging into the normal port issue.  PXC needs 3306, 4444, 4567, and 4568 open.  For SST the critical port is 4567.  We were on hosts that were located in two data centers and all hosts were in the DMZ.  Because of this, the networking was more complex than normal with two sets of firewalls and NATting going on as well.  I am no network person but thankfully I had good support.

Our first dig into the problem showed we had ports closed and it was unclear on which end it was breaking.  We were able to use tcp dump to finally convince ourselves that traffic could flow across the network on the critical ports.  We did this by running

tcpdump -n -o eth0  > output_file.

We ran this on the joiner with the ip address of the donor and we ran it on the donor with the address of the joiner.  This gives you a view of traffic flow and can confirm that you are making the connection.

After much work by the network team to open firewalls we finally were able to see the traffic flow between the two hosts.  During our investigation it was decided that we wanted to pin down the joiner and donor so that we would always know what hosts to investigate.  Here comes our first major error.  We added the
wsrep_sst_donor= 
on our joiner.  We used the ip address of the donor!  Don't do this.  It turns out that Galera has specifically recommended against using the ip address and either wants the wsrep_node_name or the hostname of the donor!  Once we fixed this we saw success of our SST.  But our woes were not over.

We then turned our attention to another node.  It too failed SST and we started head scratching.  Yes, the ports were open.  Yes, we had the hostname in the donor attribute.  After digging in Mr. Google we found a post that mentioned a failed SST due to a version difference.  Viola!  Our puppet manifest had accidentally put the wrong version of MySQL on one of the hosts and that was the reason for the SST failure.  Once the node was upgraded correctly the SST worked.

Hopefully this will help someone someday.

You can read the full Galera post on why not to use ip address as the donor here:
http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html#wsrep-sst-donor3

create table duplicate key! What?

I ran into an interesting and informative edge case involving asynchronous replication in MySQL and Percona's pt-online-schema_change utility.

In our 5.7 MySQL environment we were adding columns to an 18M 500G table.  Unfortunately this not only copies the table to complete the alter it also blocks the world from writing.  So we had to come up with another way to manage this change.  We have used Percona's pt-osc utility before and have great praise for it.  Our setup involved a PXC cluster and a single standalone slave.

pt-osc creates a new table _table_new with foreign keys renamed to _old_name, e.g., an underscore is added to make the fk in the new table unique.  But, we had previously run a pt-osc on this table and had swapped the old/new table successfully so we did not check that those fk names had been changed.  When we ran a new pt-osc it replicated through to the slave and failed with duplicate key on the create table ddl.

The fix was simple; drop and recreate the foreign keys without the leading _ (use set foreign_key_checks=NO to prevent scanning on the fk rebuild).  Worked like a charm and we resumed the slave.

I confess that it was not simple to figure this out and thankfully Percona was around to help me.

with percona xtradb, mysqldump can create inconsistent ddl missing auto increment for example!

When running Percona's XtraDB cluster and using pxc_strict_mode=ENFORCNG (the default) lock tables will generate an error.

mysqldump defaults to --opt which includes --lock-tables wherin the error gets thrown.  Of course you can temporarily change pxc_strict_mode to DISABLED or PERMISSIVE to get around the error however there can be side affects of this in a live system.

A better approach is to use mysqldump without --opt by specifying --no-opt however this can generate incorrect DDL because of the embedded option (in --opt) of --create-options.  The safe method if you want to use --no-opt is to specify the options individually and omit the --lock-tables implied by --opt.

As shown by help 

--opt               Same as --add-drop-table, --add-locks, --create-options,

                      --quick, --extended-insert, --lock-tables, --set-charset,

                      and --disable-keys. Enabled by default, disable with

                      --skip-opt.

So omit --lock-tables and include the others and your ddl will be consistent.

Color me unhappy when I copied a schema from one pxc cluster to another and missed this so that my copied schema omitted auto increment and automatic update of timestamp.  

Have fun.

when is free software not free

We all have the luxury of using various open source and free software products.  I'm a huge user of PostgreSQL, MySQL, various os/x utilities, etc.  I recently started a gig at a government site and have found they have a strange, but understandable, relation to open source software.

I was trying to get them to use a Wiki type product instead of  Microsoft's SharePoint.  At the same time we were considering moving some databases to Cassandra.  In both cases, the requests went through the rather lengthy approval process only to come back rejected.

Why?  Because in the government you cannot use or install any open source product unless you have engaged a vendor to support you.  Supposedly it's the law.   Go figure.

Of course this almost defeats the purpose of open source software, but then if you look at it from the point-of-view of the vendors who make all their money from the government it makes perfect sense.  They have pressured someone to get this enacted to give them a steady stream of income.  Forget that it may make no sense, it is the rule and so without a contract, without a billing vehicle, and without a vendor there's tons of software we cannot use.  It really ties the hands of the government folks who want to do the right thing but are kept from doing so.

I love this job.

systemctrl seems to ignore my.cnf no matter where it lives. selinux is in the way!

I recently faced a daunting problem.  The systems group turned over a new host to me, mysql was installed but not running, my configuration file was present, and all seemed perfect.

I double checked /etc/my.cnf and tweaked a couple of settings and went to initialize the db and get it running.     mysqld --initialize-insecure
worked perfect, my new ibdata and ib_logfile files got created, the log was clean, and I thought whoopee, let's just start this up.
    systemctl start mysqld 
was my next command.

Whoa!.  What's going on here.  All the log messages are going to /var/log/messages and mysql is not starting but is cycling up and down up and down.  Pretty soon /var/log/messages was full and my bad hair day began.

Well there must be a renegade my.cnf file somewhere.  Checking mysqld to see where it thought the files might live I ran
    mysqld --help --verbose 
and found the lines
    Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

and so dutifully went off to see if one of those other files existed and was overwriting my options.  

No such luck.  The sound you hear is my hair being pulled out.  After 72 hours, reinstalling mysql, running initialize multiple time, making my.cnf small with just a few options I was no closer to a solution.  

But then (clue music) I remembered having something like this a long time ago.  selinux.  Checking 

/etc/selinux/config I found

    # This file controls the state of SELinux on the system.

    # SELINUX= can take one of these three values:

    #     enforcing - SELinux security policy is enforced.

    #     permissive - SELinux prints warnings instead of enforcing.

    #     disabled - No SELinux policy is loaded.

    SELINUX=disabled

    # SELINUXTYPE= can take one of three two values:

    #     targeted - Targeted processes are protected,

    #     minimum - Modification of targeted policy. Only selected processes are     protected.

    #     mls - Multi Level Security protection.

    SELINUXTYPE=targeted

which is exactly what I would expect EXCEPT that the time stamp was more recent than the latest uptime.   Sure enough, the os team had fixed selinux like I asked, but they forgot to reboot the host.  selinux was getting in my way and until it was resolved mysql would never start.

Now, after a reboot, all was well,  mysql reads my my.cnf and starts up just fine.  

Remember, after disabling selinux you must reboot.  I surely won't forget to check this again. 

mange the size of slow query logs

The mysql slow query log is great.  But if you don't use if often, or as is likely, never look at it until there is an issue, it can grow unwieldy.  Fortunately Percona's mysql clone has options to let you have n copies of the slow query log, each of a maximum size, so that it rotates the logs and keeps the size(s) under control.

You probably already have something like this in your my.cnf file
    slow_query_log=1
    slow_query_log_file=/somewhere/slow_query.log

So now add this to get additional copies and rotate amongst them.
    max_slowlog_size=500M
    max_slowlog_files=10

Thank you Percona for yet another great feature.

Mysql shutdown, dirty pages, and innodb_page_cleaners in 5.7

In a busy Mysql environment, often the shutdown is delayed for a long time by the innodb page cleaner.  On some very busy systems this delay can be hours and you will get tired of watching this message in your error log:
                   [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool

Prior to 5.7 all you could do was watch the dirty pages and see if you could force the number down by setting the sever to flush pages thusly:
                   set global innodb_max_dirty_pages_pct = 0;

You can then watch the dirty page count drop until it reaches a low point (likely never near zero in a busy system) where you can shut it down by monitoring the dirty pages thusly:
                     mysqladmin ext -i10 |grep pages_dirty

But in 5.7 you can now have multiple page cleaners (the above trick still works) to help get the server in a state for a quick shutdown by setting
                     innodb_page_cleaners=n

Like all good things there is no free lunch, so if you kill the server rather than waiting for the page cleaner to do it's job, you will have to pay on the startup.

Enjoy

Huge databases and Percona Xtradb Cluster

In my latest endeavor  I have been working with Percona's Xtradb Cluster product.   All things considered I like it better than native Oracle MySQL clustering.  It's easy to set up and maintain and I've found it very resilient to the noisy failures we all are accustomed to.

But one of our challenges has been dealing with slow disks (the dreaded SAN), mediocre networks, and a huge database (~4T).  Moving this database around, getting backups, building the cluster, recovering the cluster have all presented issues.  Here are some observations which might help you but remember ymmv.

Avoid SST at all costs
If you lose a node in the cluster and you run out of time to do an incremental rebuild your node will be completely rebuilt.  (As I found out on one unfortunate incident if you mess up your my.cnf file this too can trigger an SST.)   So if your databases are in the G size, it's probably no big deal.  But in my current world it takes about 2 days to build a 4T node (don't ask).   Basically we are doing anything we can do to avoid an SST.  Here are two things to do.  One,  make your gcache as big as you can afford so you can do an incremental build.  Two, be very very careful when you stop the node.  Check your grastate.dat file for some valid non negative value and save it somewhere in case you make a mistake (so you can restore it).

Xtrabackup is your savior
If you have to do an SST then your only real option for a huge db is xtrabackup.  We use xtrabackup_v2 in the my.cnf file.  At least it does not lock one of the nodes.  I've also been using this to build fresh nodes or reconstruct a broken node.  To rebuild a node manually you can do this and maybe it's faster than letting SST do it for you.

We also tried using mydumper/myloader for building clusters with the big advantage that it shrinks our innodb data file but it's just too slow for this database; we're stuck with xtrabackup for the sheer speed.

Flow control will kill you
We are finding in our three node world that flow control due to heavy updates on one node can kill you.  Obviously you can work with your developers to break up those 1M row updates. (Changing the wsrep options to limit rows or data does not seem to work well in our environment.)    But sometimes you have nasty DDL to run.  pt-online-schema-change works well (would you believe we ran a pt-osc that took 4 days and 18 hours.  Don't ask.)  Be sure you get the latest version of pt-osc and turn on --max-flow-control so you don't slam your other nodes; for the above mentioned 4+ day pt-osc we used --max-flow-control=0 so that other dml was not affected by our massive DDL.  Thank goodness for pt-osc!  

Asynchronous replication is not recommended
We have three systems right now as we move to PXC.  We have two three node clusters and an existing master-slave environment.  To try and keep these all in sync (very similar to herding cats) we run asynchronous replication in various configurations.  We've tried master -> cluster 1 -> cluster 2 as well as master -> cluster 1 and master -> cluster 2.  Both have their challenges.  But key is to make sure you only have one that has read/write and the others have read_only=1 set.  For auto-increment columns you need to disable the automatic feature of PXC and do the whole thing manually.  I do not recommend this approach and once we finally get the old master/slave system out I am going to try and combine these two clusters into one across the WAN.

I'll update this as I get more experience but thought I would share some preliminary ideas.

Huge databases and Percona Xtradb Cluster

In my latest endeavor  I have been working with Percona's Xtradb Cluster product.   All things considered I like it better than native Oracle MySQL clustering.  It's easy to set up and maintain and I've found it very resilient to the noisy failures we all are accustomed to.

But one of our challenges has been dealing with slow disks (the dreaded SAN), mediocre networks, and a huge database (~4T).  Moving this database around, getting backups, building the cluster, recovering the cluster have all presented issues.  Here are some observations which might help you but remember ymmv.

Avoid SST at all costs
If you lose a node in the cluster and you run out of time to do an incremental rebuild your node will be completely rebuilt.  (As I found out on one unfortunate incident if you mess up your my.cnf file this too can trigger an SST.)   So if your databases are in the G size, it's probably no big deal.  But in my current world it takes about 2 days to build a 4T node (don't ask).   Basically we are doing anything we can do to avoid an SST.  Here are two things to do.  One,  make your gcache as big as you can afford so you can do an incremental build.  Two, be very very careful when you stop the node.  Check your grastate.dat file for some valid non negative value and save it somewhere in case you make a mistake (so you can restore it).

Xtrabackup is your savior
If you have to do an SST then your only real option for a huge db is xtrabackup.  We use xtrabackup_v2 in the my.cnf file.  At least it does not lock one of the nodes.  I've also been using this to build fresh nodes or reconstruct a broken node.  To rebuild a node manually you can do this and maybe it's faster than letting SST do it for you.

We also tried using mydumper/myloader for building clusters with the big advantage that it shrinks our innodb data file but it's just too slow for this database; we're stuck with xtrabackup for the sheer speed.

Flow control will kill you
We are finding in our three node world that flow control due to heavy updates on one node can kill you.  Obviously you can work with your developers to break up those 1M row updates. (Changing the wsrep options to limit rows or data does not seem to work well in our environment.)    But sometimes you have nasty DDL to run.  pt-online-schema-change works well (would you believe we ran a pt-osc that took 4 days and 18 hours.  Don't ask.)  Be sure you get the latest version of pt-osc and turn on --max-flow-control so you don't slam your other nodes; for the above mentioned 4+ day pt-osc we used --max-flow-control=0 so that other dml was not affected by our massive DDL.  Thank goodness for pt-osc!  

Asynchronous replication is not recommended
We have three systems right now as we move to PXC.  We have two three node clusters and an existing master-slave environment.  To try and keep these all in sync (very similar to herding cats) we run asynchronous replication in various configurations.  We've tried master -> cluster 1 -> cluster 2 as well as master -> cluster 1 and master -> cluster 2.  Both have their challenges.  But key is to make sure you only have one that has read/write and the others have read_only=1 set.  For auto-increment columns you need to disable the automatic feature of PXC and do the whole thing manually.  I do not recommend this approach and once we finally get the old master/slave system out I am going to try and combine these two clusters into one across the WAN.

I'll update this as I get more experience but thought I would share some preliminary ideas.

the three dba walk into a bar joke....

You've all heard it.

Three DBAs walk into a bar.  They leave because they can't find a table.

Ha ha.  Very good, but what about all the excitement today about NoSQL.  Sure no table is fine but what exactly are we using to store our data?

My experience so far with NoSQL components is limited but from what I see they do not offer the mathematical rigor of a SQL database as envisoned by Date and Codd.  But I do see that for some quick key/value look ups they are great.

the three dba walk into a bar joke....

You've all heard it.

Three DBAs walk into a bar.  They leave because they can't find a table.

Ha ha.  Very good, but what about all the excitement today about NoSQL.  Sure no table is fine but what exactly are we using to store our data?

My experience so far with NoSQL components is limited but from what I see they do not offer the mathematical rigor of a SQL database as envisoned by Date and Codd.  But I do see that for some quick key/value look ups they are great.

the three dba walk into a bar joke....

You've all heard it.

Three DBAs walk into a bar.  They leave because they can't find a table.

Ha ha.  Very good, but what about all the excitement today about NoSQL.  Sure no table is fine but what exactly are we using to store our data?

My experience so far with NoSQL components is limited but from what I see they do not offer the mathematical rigor of a SQL database as envisoned by Date and Codd.  But I do see that for some quick key/value look ups they are great.

setting up MySQL master-master replication; the complete checklist

Seems like almost all new MySQL dbas stumble a bit trying to set up master-master replication.  Here's my (hopefully) fool proof check list to try and make it work on the first try.

Given two mySql instances we'll call them host1.mydomain.com and host2.mydomain.com and we presume they are using the standard port 3306 to communicate, that you have a super mysql user we'll call root with password rootpw (name and password of course could be different), and that you can ssh to the hosts.  (Sorry but Windows is not my forte however the commands below should be very similar.)

Commands are in bold.

begin by checking on access on host1.mydomain.com and access from host1.mydomain.com to host 2.mydomain.com

1. ssh host1.mydomain.com
2. telnet host2.mydomain.com 3306      You should get something like "connected".  Then ctrl+v and quit  If it hangs then get someone on your network team to help you figure out why you cannot reach the other host.  In my experience this is 75% of the issues setting up replication. 
3. which mysql (is the mysql client binary in your path?)  if not fix your path or in any mysql command specify the fully qualified location, something like /usr/bin/mysql
4. login to mysql thusly  mysql -h host1.mydomain.com -u root -p     you will be prompted for the password
5. record your server id.  show global variables like 'server_id';
6. make sure you have binary logging as show binary logs;  If it says you are not using binary logging then add log-bin to your /etc/my.cnf file and bounce mysql. 
7. exit from this instance   exit;
8. try to reach host2 from host1.mydomain.com using the mysql client as   mysql -h host2.mydomain.com -u root -p you will be prompted for the password.  If this fails figure out why you cannot login.  Is it a user or password issue?  Are you sure the ports are open between the two hosts?  Is the root user not allowed to do remote logins in which case you may need another super user (select user from mysql.user where user='root'; and there should be a wildcard for host1 and host2 should be in the host name field)

now check on access on host2 and access to host1 from host2

1. ssh host2.mydomain.com
2. telnet host1.mydomain.com 3306      You should get something like "connected".  Then ctrl+v and quit  If it hangs then get someone on your network team to help you figure out why you cannot reach the other host.  In my experience this is 75% of the issues setting up replication. 
3. which mysql (is the mysql client binary in your path?)  if not fix your path or in any mysql command specify the fully qualified location, something like /usr/bin/mysql
4. login to mysql thusly  mysql -h host2.mydomain.com -u root -p     you will be prompted for the password
5. record your server id.  show global variables like 'server_id';
6. make sure you have binary logging as show binary logs;  If it says you are not using binary logging then add log-bin to your /etc/my.cnf file and bounce mysql.
7. exit from this instance   exit;
8. try to reach host1.mydomain.com from host2.mydomain.com using the mysql client as   mysql -h host1.mydomain.com -u root -p you will be prompted for the password.  If this fails figure out why you cannot login.  Is it a user or password issue?  Are you sure the ports are open between the two hosts?  Is the root user not allowed to do remote logins in which case you may need another super user (select user from mysql.user where user='root'; and there should be a wildcard for host1 and host2 should be in the host name field)

At this point we have proved that host1.mydomain.com and host2.mydomain.com can communicate to and from each other.  And we have the server ids, which must be different between the two hosts.  If the server ids are undefined or the same for the two hosts you must update your /etc/my.cnf configuration file to set a valid number and bounce the mysql instance.  The entry in the /etc/my.cnf file should look something like:

     server_id=900

and the numbers are not really important as long as all hosts participating in a replication setup have a unique number. 

Create a replication user on each mysql instance. We use the same user and password on both instances to keep this simple.  Do this on both host1 and host2.

     grant replication slave on *.* to 'repl'@'%' identified by 'password';

     grant replication client on *.* to 'repl'@'%' identified by 'password'; 

I'm using the user repl and you should pick a nice password.  (Purists will argue about allowing access to all schemas from all hosts; you can certainly restrict this but for simplicity I suggest you get this working first then make granularity adjustments.)

As a final check, try logging into each server from the other server using your new repl user and password.  You should be able to connect. 

Now let's define the first master and slave.  On host1.mydomain.com we find our master log position and log file name by doing show master status;   We use the file name and file position in the command below. 

On host2 we define host1.mydomain.com as our master by doing these three commands:

     change master to master_host='host1.mydomain.com',master_user='repl',master_password='password';

     change master to master_log_file='log file name from show master status';

     change master to master_log_pos=number_from_the_show_master_status;

Note the log_pos is not quoted since it's a number. 

Now start the slave by doing start slave;  Check your values by doing show master status\G which will show you a tabular list of all the settings and the status of the slave.

If all your settings are correct look for 

     Slave_IO_Running: Yes

     Slave_SQL_Running: Yes

which says the threads for replication are now running on your host1.mydomain.com and receiving data from host2.mydomain.com. If they are not Yes then check your mysql error log which will likely pinpoint the problem. Most of the time the errors are due to host1.mydomain.com and host2.mydomain.com not being able to talk to one another or binary logging not running or server ids not being set.  

Now repeat this process on host2.mydomain.com.   You will do the change master commands on  host2.mydomain.com  and you will do the show master status command on  host1.mydomain.com      

mysql and golden gate to oracle

While installing Golden Gate for mysql replication I ran into two issues which, while documented, were buried in the books so I didn't see them. Maybe this will help someone.

1. be sure your socket file is in /tmp or you have a sym link from tmp.  Golden gate only looks here so if no socket, no connection.  Now that's just plain wrong.

2. Golden gate needs row based replication not function based.  That means you're stuck with some post 5.1.x release which maybe won't be an issue but forced us to do an in-place upgrade.  Don't ask.

oracle dataguard reports ORA-01017: invalid username/password; logon denied

Wow, there are a ton of these on the web, so why post one more.  Because we  hit an issue which I did not find and which hopefully will help someone in the future.

In setting up dataguard on our 11g system we got the following errors after

connect /
show configuration

Configuration Status:
ORA-01017: invalid username/password; logon deniedORA-16625: cannot reach database ""DGM-17017: unable to determine configuration status


OK, this seems simple enough.  Our password file must be the problem.  After searching, regenerating the password file, and pulling out our hair we found this in our oracle parameters:


redo_transport_user    string    SYSTEM

which means, that dataguard will use SYSTEM as the connection to the database and not SYS.  Now, that's interesting but it gets more interesting.

If you generate the password file using orapwd it is likely you won't find SYSTEM in the file and that is required for dataguard to work.  In other words, merely creating the password file using orapwd and copying it to your standby site does not guarantee that it will work.

Why?  This is because with 11g a system grant such as

grant sysoper to SYSTEM;

gets written to the password file but if you come along and generate the file using orapwd you will lose this entry.

So, make certain your password file contains the correct user with sysoper privileges for dataguard to work.  You can use this query together with the redo_transport_user parameter to verify all is well.

select * from v$pwfile_users;

If the user in the redo_transport_user  is not in this list and does not have sysoper you won't be able to connect and you will get the ORA-01017 error.

Good luck.

How to create a histogram from count data using sql

I am often asked to generate summary counts from various tables and frequently asked to "bucketize" the results for the user.  Once you do it the technique is pretty easy, but getting over that first hump can be challenging.  So here's a tutorial from a recent request (this is mysql but the sql is pretty standard).  (If you only have a few thousand rows of data it might be easier just to use the histogram function of Excel but if you have more than some maximum number of rows, this technique will always work.)

1. First get your counts, something like
         select some_key,count(*) from table1 group by some_key;

2. and stick these values into an intermediate table (yes, not required but easier to explain)
         create table kount as select some_key,count(*) as value from table1 group by some_key;

3. now decide what kinds of "buckets" you want for your histogram.  Be sure that you cover your min and max values from the previous count query.  So in my case I'll make buckets that represent orders of magnitude.  You create a table with these buckets and then populate a min and max value for each bucket thusly.
       create table bins (min_value int, max_value int);
       insert bins values(0,10),(10,100),(100,1000),(1000,10000),(10000,100000);

Our resultant buckets look like this:

+-----------+-----------+
| min_value | max_value |
+-----------+-----------+
|         0 |        10 |
|        10 |       100 |
|       100 |      1000 |
|      1000 |     10000 |
|     10000 |    100000 |
+-----------+-----------+

4. Now we do a join and get the counts for each bucket.  

      select b.min_value,b.max_value,count(*) from bins b 

          left outer join kounts a on a.value between b.min_value and b.max_value

          group by b.min_value 

          order by b.min_value

And our histogram values look like the following and can be put into your favorite charting product and made into a pretty graph.

+-----------+-----------+----------+

| min_value | max_value | count(*) |

+-----------+-----------+----------+

|         0 |        10 |  3189118 |

|        10 |       100 |   239142 |

|       100 |      1000 |     9004 |

|      1000 |     10000 |      208 |

|     10000 |    100000 |        4 |

+-----------+-----------+----------+

I hope this is useful to some new sql user.   Enjoy.  

operations sanity for your databases

Permit me to put on an operations hat for a moment and provide budding developers with some thoughts on how to keep out of trouble in your database environment. Here are some rules that I've found to be useful:

1. track database changes (versions etc.) in the database.

2. make all database changes backward compatible.

3. avoid triggers.

4. use referential integrity sparingly.

Now I'm sure some eyebrows will be raised by the last two recommendations. After all, how can this possibly be related to the operation of your production database. Let's look at these one at a time.

1. track database changes. I have found that trying to figure out what version, release, etc. is in my database is usually hard. You normally can't just look at a table and say, "oh, yea, that's release 2.01". An easy way to fix this is to incorporate a small table in each database (or schema) that tracks the changes. Here's the table (using a mysql format) I've used with some success.

create table db_history (

id int not null auto_increment,

version varchar(20) not null,

backed_out datetime null,

created datetime not null,

comment varchar(50) null,

primary key (id))

When I install a new release I automatically do an insert into this table to note the release installation. In the event I back it out, then I update the backed_out column. Quick and easy and any time I want to see what's been installed I simply do a select * from db_history. I can even use this table to document non-release type changes that might be made in the database simply by adding a comment.

For those of you using ruby-on-rails, they automatically implement a version table but the data contained there is pretty minimal. Of course you can backtrack from the version information to the sql but even in this environment I've found my table to be of more help.

2. make all database changes backward compatible. If I deploy a new version of my favorite web application, and later decide it wasn't quite up to snuff, then backing it out is usually as easy as moving a symlink or downloading a new tar ball. Unfortunately, this may be impossible or impractical in the case of a database change. For example, changing a column type or dropping a column can easily make your application fail. If the change requires an hour to apply to your large table, then it can easily take that long or longer to back it out. The rules to avoid this issue are pretty simple:

a. new columns and new tables should never break your code.

b. changed columns should be added and referenced by the new name but retain the old column for backward compatability and remove it in the next release.

c. dropped columns should only be removed after the second release which no longer uses them.

d. if a procedure or function has new parameters, in or out, then create a new procedure or function (e.g., foo_v2) and keep the old procedure or function around.

3. avoid triggers. Usually the functionality of a trigger can be duplicated in a procedure. Then when there is a problem, the code is right in front of the dba trying to debug the issue. This is a religious discussion and many people will disagree but I have found at 2:00 AM when things are broke that I can easily overlook a trigger.

4. use referential integrity sparingly. Again, this is a religious argument and some will disagree. But in my experience I have found many cases where an unanticipated table scan due to some RI goes un-noticed during development but haunts you in production. Sometimes RI cannot be avoided but by using it sparingly you can save yourself lots of late night head-scratching wondering why you are suddenly scanning our favorite 1M row table. Usually a check for existance of a key before an insert is all that's needed.

Have fun.

how to use replication to forward transactions

Normal replication goes from host A to host B. But using the mysql my.cnf directive log_slave_updates you can store and forward transactions from an intermediary log to another host. So in this example, B is a slave of A; B specifies log_slave_updates, and then C as a slave of B gets all transactions from both A and B. Not quite multi-master support.

I have used this when cutting over a new system where existing hosts X & Y are replicating and I want to add a new host that will set up a new pair A & B.

Hope this is useful.

corrupt frm files for innodb tables

I've encountered a new mysql error for many (all) tables when innodb was starved for memory. The error:

Incorrect information in file: '...frm'

and any attempt to repair the table failed.

Much consternation!

But this occurred not because the table was corrupt but because I tried to give too much memory to the innodb buffer pool.

The real culprit in the error log was

InnoDB: Error: cannot allocate 2516598784 bytes of InnoDB: memory with malloc!

InnoDB: by InnoDB 228832092 bytes. Operating system errno: 12

Reducing the innodb-buffer-pool allocation to a more sane level for the box eliminated the apparent table corruption.