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.