JSON Configuration File ~ Notes

AWS uses JSON extensively for configuration as do many other modern platforms.

JSON is pretty straight forward.

http://json.org

Here are some JSON examples including what the examples look like in JSON compared to XML:

http://json.org/example

If you want some Java objects to parse JSON:

http://json.org/java/

This nifty class can convert XML to JSON and vice versa:

https://github.com/douglascrockford/JSON-java/blob/master/XML.java

There are no comments in JSON. Your comments are the data but you could create some generic comment field/value pair that your application ignores.

Google has a JSON style guide:

http://google-styleguide.googlecode.com/svn/trunk/jsoncstyleguide.xml

JSON Simple is a JSON Java toolkit:

http://code.google.com/p/json-simple/

Nice example of parsing a JSON file with Java:

http://answers.oreilly.com/topic/257-how-to-parse-json-in-java/

Parse web request with line breaks:

        HttpResponse response; // some response object
        BufferedReader reader = new BufferedReader
            (new InputStreamReader(response.getEntity().getContent(), 
               "UTF-8"));
        StringBuilder builder = new StringBuilder();
        for (String line = null; (line = reader.readLine()) != null;) {
            builder.append(line).append("\n");
        }
        JSONTokener tokener = new JSONTokener(builder.toString());
        JSONArray finalResult = new JSONArray(tokener);

Without line breaks:

        HttpResponse response; // some response object
        BufferedReader reader = new BufferedReader
          (new InputStreamReader(response.getEntity().getContent(), 
            "UTF-8"));
        String json = reader.readLine();
        JSONTokener tokener = new JSONTokener(json);
        JSONArray finalResult = new JSONArray(tokener); 

Decoupled Design for AWS Applications

When designing an application that uses AWS (or any third party components) you'll want to consider how your code will work if that component is unavailable or you want to switch it out for something else.

Reasons to consider decoupling when designing your AWS application:

• You might want to develop some code on AWS because you have quick access to servers, unlike the traditional large corporate environment where you put in a request and wait for weeks or months. It might be that you are still required to use the in-house corporate servers for production but you want to get started on development in the AWS environment. Decoupling your design allows for minimal changes and code that can switch from one environment to the other.

• Provide flexibility for the possibility (heaven forbid!) that someday you move off AWS to some other platform. You'll want to minimize the changes you have to make to your application should that become necessary for one or more of the AWS components you are using.

• On the flip side you might want to build and internal application that has the flexibility to scale to AWS servers when necessary and run with AWS IAM roles when in the cloud. You want your application to be dynamic enough to run in either location.

• Finally there is the best practice of writing unit tests which might need to run with or without AWS resources being available. By designing to interfaces or decoupling your application from AWS using proxy classes that can run with or without AWS you can run unit tests without access to AWS.

 So how do you go about decoupling your design? There are various ways you can do this, some more complicated than others.

One way to decouple your design is to create a set of proxy services that get called and forward requests to AWS. This is more work and possibly more error prone due to network issues. It creates complexity that will make it harder to maintain the application and requires more resources, but can provide separation of duties in the case where you want some people writing your AWS code and other people writing the applications that use the AWS code but not accessing AWS components directly to maintain decoupled applications. This option is nice because no changes to your application code or recompilation is required should whatever is behind the proxy change.

Another option is to create a set of interfaces used by your code. You could have the AWS objects extend these interfaces so you can use polymorphism and some configuration file to determine which class to use at the time the program runs.  The down side of this and why I don't really recommend it is because I prefer not to get in and tweak vendor files unless I'm going to completely take ownership of that code and maintain it going forward. Amazon is going to keep changing their libraries and I want to keep using those libraries.

Another option would be to create wrapper or proxy classes that interact with AWS components. Put all your proxy classes in a separate project and create a separate library that has a dependency on the AWS components. This is nice because none of your application directly calls AWS code and should not include any AWS libraries to prevent any developers from inadvertently creating dependencies.

As for configuration you have a few different options to tell your application which classes to use:

• Define your classes in configuration files similar to the way Spring defines the actual class to use and gives it a name so you can swap out classes behind the scenes. Have a configuration file for AWS and running without AWS where you use mock objects or something else.
• Define a switch in your config file which says "use AWS - yes or no" basically. Then create a factory that loads the appropriate object based on this switch - this would allow you to change your factory to extend your application or to easily swap out classes used should you introduce new vendors or functionality.

If you're trying to write an application that scales to the cloud, and additionally support online/offline development, you might want to consider dynamically determining if your application is on an AWS EC2 instance or not. That way you can instantiate the correct AWSCredentialsProvider on the fly and take advantage of the additional security provided by using IAM roles for applications running on EC2 instances.

These are just a few ideas. There are many ways to solve this problem. Just something to keep in mind if you are design applications on AWS.

Connecting to MySQL with Java Driver

Set up  a user with permissions to connect:

Add a user to your database if you need to, or use root (not recommended) user name and password to connect.

Create a new user and grant privileges to access database. For my test I'm giving full access to the user but you'll obviously want to be more selective on live systems and limit what your user can do with more restrictive GRANT statement.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newuser';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost'
        WITH GRANT OPTION;

You can test out your user by connecting to your database on command line or using MySQL workbench to make sure that's working independent of any Java code.

Download MySQL Java driver

Go to the Oracle download page for the MySQL Java driver.

You will probably need to create a user name and password on Oracle site to access this.

http://dev.mysql.com/downloads/connector/j/

I changed the "select platform" to platform independent and chose zip file.

Expand the zip file.

In the root of the expanded folder find this jar (or something like it if there's a newer version by the time you read this): mysql-connector-java-5.1.27-bin.jar

If you want to you can move this jar to a different location where you store third party jars accessible to your Eclipse projects (a repository, a folder, etc) or just leave it where it is.

Set up Java project in Eclipse and connect to database using driver

Open eclipse and create a new Java project.

Right click on the project name and click properties.

Choose Java Build Path in the left menu of the dialog box.

Click on the Libraries tab.

Click on "Add external jars..." on right side of screen.

Navigate to and select the driver jar we located and/or moved above  mysql-connector-java-5.1.27-bin.jar

Click open. Click OK.

Add a new class to your java project that makes the connection to the database. Some people will probably not like my error handling. Whatever. It's a test and will save that argument for later.

This class below assumes you are connecting to "localhost" which is the machine you're typing this all on and running an instance of MySQL on that machine on the default port (3306).

The things you may need to change if connecting to a database elsewhere are the things in red below:

localhost = IP address or domain name of server on which database is located
3306 =  port your database is listening on
yourdatabasename = name of the database to which you want to connect
newuser, newuser = user name + password

If you have problems connecting to a remote database might be easier to test the code locally first. If connecting to a database on an EC2 or RDS you'll need to make sure your VPC allows connections from your IP address on the specified port. If a database on EC2 you'll also need to make sure the OS firewall allows this connection. If you don't want to allow connections from the Internet set up a bastion host and upload your Java code to that machine to test the connection.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectToMySql {
   
    public boolean connect() {
        Connection con = null;
      
        try {
          
            Class.forName("com.mysql.jdbc.Driver");
            String url ="jdbc:mysql://localhost:3306/yourdatabasename";
            con = DriverManager.getConnection(url, "newuser", "newuser");
            System.out.println("connection Established");
            return true;
          
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //close the connection in a finally block 
            //so we don't kill the database with open connections
            close(con);
        }
      

    }

    private void close(Connection con){
        try{

            if (con != null) con.close();
        }catch(Exception e){
            //do nothing ...failed to close connection but whatever just testing
        }
    }
}

Create a unit test to test the connection. Yes, I know unit tests typically don't have database connections and should use mock objects, but this was a handy, simple way to run my code and make sure I can connect to the database.

To get the JUnit jar added to your project the easiest way to create the test is to right click on the class above and select  "New" and then "JUnit Test Case". Click the "Next" button, then check the "connect" method in the list. Then click "Finish".

Now alter the class just created to look like this:

import static org.junit.Assert.*;

import org.junit.Test;

public class ConnectToMySqlTest {

    @Test
    public void testConnect() {
       
        boolean connected = false;
       
        ConnectToMySql c = new ConnectToMySql();
        connected = c.connect();
       
        assertTrue(connected);
    }

}

Right click on ConnectToMySqlTest and choose "Run As" and "JUnitTest".

If all is well then you should have a green bar that tells you the test ran successfully. If not look at the error message in the output window. If you can't connect make sure you have tested connecting with the user name and password you are trying to test with outside of the Java project to make sure there's not a general problem with the user name, password, firewall, database connection itself.

SQL Server to MySQL Syntax Differences

I recently converted a large set of procedures from SQL Server 2008 Syntax to MySQL.

For basic conversion of SQL Server stored procedures to MySQL see this post:

http://websitenotebook.blogspot.com/2013/11/converting-sql-server-strored.html

Here are some of the other syntax changes I needed to make to get  my SQL Server stored procedures working on MySQL:

• For getDate() use CurDate() or Now()
• Use Length() instead of len()
• If statements - replace first BEGIN with THEN and END with END IF; use ELSE and ELSEIF in between
• use ifnull instead of isnull; isnull returns take one parameter and returns 1 or 0 to indicate if value is null;
• All the columns in your HAVING clause must be in the SELECT column list
• Instead of TOP N use LIMIT N at the end of your select statement
• SQL Server money data type needs to be changed to decimal with appropriate precision (say decimal(19, 2) where 2 is 2 decimal places) 
• Can't cast to varchar. Use char: CAST(id as CHAR(10))
• MySQL wants your DECLARE statements always at the top of the stored procedure.  
• If trying to get record count use row_count() function: set RecordCount = row_count();
• A procedure with multiple select statements will return the first recordset first when calling a stored procedure through MySQL JDBC driver so make sure the first select statement is the recordset you want the returned or handle multiple recordsets. Use SET instead of SELECT when setting values.
• To exec SQL inside a stored procedure:
SET @s = 'INSERT into mytable select x from other table'
PREPARE stmt1 FROM @s; 
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;  
• Use CONCAT instead of + to concatenate values - and note that if you put a space between CONCAT and the parenthesis won't work:

  CONCAT('%', @email, '%')  

• CASE statement has CASE WHEN ... THEN ... WHEN...THEN...ELSE... END (don't forget THEN and doesn't end with END CASE even though IF end with END IF). Also if you have ELSE CASE WHEN in your code replace with just WHEN
• Instead of UPDATE x=y FROM (join tables) put the join after the update and before the set statement: UPDATE table1 t1 JOIN table2 t2 on t1.pk = t2.fk SET t1.t1_field = t2.t2_field WHERE t1.some_field=some_value
• If using two dashes for comment must have space or tab after the dashes
• Can't do a FULL OUTER JOIN in MySQL but can emulate with a UNION of two left join select statements 
• Temporary tables hang around for the life of a client connection. They aren't automatically dropped at the end of a stored procedure run so you'll have to manually delete them if you want to use the same table again for a particular connected session.
• Can't alter procedure SQL so have to drop and recreate which creates issues with high-availability systems as noted in this blog post (though there may be alternate solutions and in some cases can cause problems in your app anyway if changing signatures - so you'd have to find some alternate to this to coordinate with your app code): https://www.codeofhonor.com/blog/a-better-way-to-update-sql-stored-procedures
• SQL Server lets you get away with having all your joins before all your ON statements and figures out which JOIN goes with which ON. In MySQL you need to put the ON statement right after the JOIN statement to which it refers. 
• Use AUTOINCREMENT instead of IDENTITY creating tables: NewColumn INT IDENTITY(1,1) NOT NULL becomes NewColumn INT NOT NULL AUTO_INCREMENT
• Instead of select returnId = @@identity use SELECT (@@Identity AS id) or SELECT  id = (@@Identity);
• Stored procedures with no parameters need () after procedure name
• You may need to use varbinary(1000) or appropriately length instead of binary data type in stored procedure parameter list if you get truncation error
• Instead of PATINDEX use POSITION or LOCATE
• Can't use keywords as variable names. For example can't use: Declare sql varchar(1000) - you'll have to pick another variable name
• Temporary tables do not have # in front of the name; instead use syntax: create temporary table or drop temporary table;
• Derived tables do not have # in front of name; make sure to use AS when defining the alias: i.e. instead of select... #t select.... as t
• Execute instead of exec; If you are calling one stored procedure inside another use CALL instead of EXEC
• If you see 'expecting end$' in error message when creating a stored procedure make sure you have a begin and end around the lines of SQL within the body of the procedure. 
• Unexpected IDENT_QUOTED: Declare the delimiter for the procedure at the top of the procedure to be something other than semicolon (;). Set the delimiter back to semicolon at the end of the procedure. See DELIMITER in sample code above.
• Use backticks (`) instead of square brackets [] for db objects with spaces in 
  names

Distributed Databases - Seattle Scalability Meetup Notes

At Seattle Scalability Meetup: distributed database design

separate query from distributed computation

Build for high concurrency

Network io most precious resource

Prefer predictable degradation to complex recovery scenarios

Build every component to be distributed, do not reuse old RDBMS architecture

Netty - java network library

ProtoBuff, Thrift faster than RPC in Hbase

Shard - have to ask every server. Every query hits every machine.

Global indexes faster for distributed systems. Tells you which server has data you need based on primary index telling ranges on each server.

Backup AWS MySQL RDS Instance and Restore on Remote Machine

Was looking for a way to back up MySQL RDS to local copy to do some work a bit faster offline.

If you want to use replication to move data from one database to another - if you have the appropriate network access to do so:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html

I used another option since my database was pretty small and I run my database access through a bastion host.

I have set up network access to RDS database from the bastion host and installed MySQL Workbench on it. I connected to my RDS instance in MySQL workbench, then click on "Data Export" in left menu and followed the prompts to dump a file.

Then I compressed and copied the dump files to my local machine.

Then reversed the process with local copy of MySQL workbench and choose "Data Import/Restore" on the local machine.

This is a quick hack. If doing this repeatedly would optimize and automate.

Can't connect to MySQL server on 'localhost' (10061)

Installed MySQL on Windows 2008 and could not connect to database running on local host via command line.

Can't connect to MySQL server on 'localhost' (10061)

Turns out if you ping localhost you'll get an IPV6 response [::1] instead of IPV4 127.0.0.1 and mysql can't translate.

To fix map vp4 to vp6 using netsh:

netsh interface portproxy add v4tov6 listenport=3306 connectaddress=::1 connectport=3306

If you see this error:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Make sure MySQL is running in your services list. If you did something dumb like I did and didn't install it as a service for some unknown reason, you can add it to your services list using the following:

"C:\Program Files\MySQL\MySQL Server x.x\bin\mysqld" --install

Converting SQL Server Stored Procedures to MySQL

I ran the MySQL migration tool to import my tables and data into MySQL from SQL server as noted in a prior post. It went decently well.

The conversion of stored procedures was not quite as simple. Here are some modifications I needed to make to the output script from the conversion tool:
 

• Remove 'or Replace' after CREATE (see drop/create syntax below)
• Remove AS (Views require AS, procedures don't
• Put parentheses around parameter list
• Remove @ from parameter names
• Add IN, OUT, etc. to parameter list
• Set the delimiter before to something after than semicolon and then back to semicolon 
• Semicolon after statements within the stored procedure 
• Remove 'set nocount on' 
• Remove default values for parameters (not supported) 
• Declare a delimiter which is used like GO (e.g. DELIMITER $$ and put $$ where you would normally put GO)

So for example this procedure generated by the migration tool (which won't compile):


----------------------------------------------------------------------------
Routine mydb.SP_CUSTOMER
----------------------------------------------------------------------------
DELIMITER $$

USE `mydb`$$
 CREATE or REPLACE PROCEDURE mydb.SP_CUSTOMER

@id int

AS

 Select * from mydb.customer WHERE customer_id = @id

$$

DELIMITER ;


Becomes this


/*****************************************
Routine mydb.SP_CUSTOMER
*****************************************/
DELIMITER $$

USE `raddata`$$
CREATE PROCEDURE mydb.SP_CUSTOMER

(IN id int)

Select * from mydb.customer WHERE customer_id = id;

$$

DELIMITER ;

Syntax for Altering Stored Procedures

DROP PROCEDURE IF EXISTS foo;
    delimiter $$
    create PROCEDURE foo (args)
    begin
      blah
    end$$
    delimiter ;

Deciphering Error messages
 
First of all if you're looking for error messages in the output you'll often find a generic error message like:

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near [...code here]

To get a closer clue as to what the problem is, if you are using MySQL Workbench there's a red symbol next to the line that has the problem and the error code is underlined. The red symbol does nothing. However over the text underlined in red and a more descriptive (possibly still not helpful) box pops up.

Other MySQL and SQL Server Syntax Differences

For some additional syntax differences between MySQL and SQL Server check out this post:

http://websitenotebook.blogspot.com/2013/12/sql-server-to-mysql-syntax-differences.html

Required option -O is missing using AWS CLI (Command Line Interface)

If you get error required option -O is missing using AWS CLI it's because you need to provide the

AWS Access Key ID and AWS Secret Access Key when sending commannds.

You could add these keys to every command you type by tacking them on when you type out the command but that will get a bit tiresome.

The other option is to add environment variables which contain the keys: AWS_ACCESS_KEY and AWS_SECRET_KEY

Or you can put the configuration in a file and set the AWS_CONFIG_FILE environment variable pointing to the file as explained here:

http://websitenotebook.blogspot.com/2013/11/setting-aws-command-line-interface-on.html 

Note that if you are trying to run an application on an EC2 instance you should probably be using roles: 

http://websitenotebook.blogspot.com/2013/11/aws-keys-and-login-credentials-roles.html

AWS CLI [Errno 10060] - Max retries exceeded with url: Caused by class'socket.error'

If you are trying to run issue commands to the AWS Command Line Interface and get an error like this:

HTTPSConnectionPool(host='xxx', port=443): Max retries exceeded with url: / <Caused by <class'socket.error'>: [Errno 10060] A connection attempt failed because thee connected party did not properly response after a period of time, or an established connection failed because connected host has failed to respond)

The problem is the machine doesn't have https access to the AWS url it's trying to reach to issue your command.

If you're trying to run the command from an EC2 instance:
 
First of all your machine needs to have an elastic IP and be opened up to the Internet for some services. Check the service you are trying to access to determine if public Internet access is required or not.

Next make sure the security group your machine is in can access the service IP address on port 443.

Finally make sure the OS firewall is configured with 443 access to the IP of the AWS service you are trying to reach.

For example -- if you are trying to run this command:

ec2-describe-availability-zones --region us-east-1

You'll get the error message above which will contain this address (at time of this writing) as the address the service is trying to reach to run your command.

ec2.us-east-1.amazonaws.com

You can ping that address to get the specific IP address for that domain and add it to all the rules above, or you can open up your systems and security groups for the machine you are running the Command Line Interface tool to have outbound 443 access for any IP address.

The problem with plugging in a specific IP address to your rules is that Amazon might decide to change the IP address for the above domain name. You might want to limit your configuration and risk by creating a NAT instance as explained VPC documentation for all outbound traffic on port 443 and only allow that particular machine full access out, while configuring all other machines to send outbound traffic on port 443 to your NAT instance.

Creating Triggers and Stored Procedures on MySQL AWS RDS instances

MySQL Server Parameters MySQL has a bunch of server parameters you can configure for your database (use with care because can adversely affect security, performance and data integrity):

http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html

AWS RDS Parameter Groups
In order to configure these values on an AWS RDS instance, you'll need to use AWS DB Parameter Groups:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html

There's a default parameter list which you cannot modify. You can create your own parameter group and save the settings, then apply that group to your RDS instance(s).

Allow Creation of Triggers and Stored Procedures

You can't create triggers or stored procedures out of the box on AWS RDS instances because you won't get super access to the database server so you'll get an error like this:

ERROR 1419 (HY000) at line 49: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

To get around this you can set this server parameter to 1 (true): log_bin_trust_function_creators

Note that this can adversely affect replication if you don't know what you're doing.

Then apply it to your RDS instance using command line tools

rds-modify-db-instance --db-instance-identifier [your instance name here] --db-parameter-group-name [your group name here] --apply-immediately

Or AWS command line tools

aws rds modify-db-instance --db-instance-identifier bmxdb --db-parameter-group-name bmx --apply-immediately
 
Restart your instance.

Installing Command Line Interface:

http://websitenotebook.blogspot.com/2013/11/setting-aws-command-line-interface-on.html

Caveats Enabling These Settings - Impact on Replication

There are some issues with replication developers should be aware of when enabling this setting, if setting up fault tolerant master-slave architecture with replication.

http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html

... The implication is that although a user must have the CREATE ROUTINE privilege to create a function, the user can write a function containing a dangerous statement that will execute only on the slave where it is processed by a thread that has full privileges...
 

• To create or alter a stored function, you must have the SUPER privilege, in addition to the CREATE ROUTINE or ALTER ROUTINE privilege that is normally required. (Depending on the DEFINER value in the function definition, SUPER might be required regardless of whether binary logging is enabled. See Section 13.1.15, “CREATE PROCEDURE and CREATE FUNCTION Syntax”.)
• When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
  By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:
  

  ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
  or READS SQL DATA in its declaration and binary logging is enabled
  (you *might* want to use the less safe log_bin_trust_function_creators
  variable) 

  ....

  If you do not want to require function creators to have the
            SUPER privilege (for example,
            if all users with the CREATE
            ROUTINE privilege on your system are experienced
            application developers), set the global
            log_bin_trust_function_creators
            system variable to 1. 

AWS Keys and Login Credentials + Roles

This is an interesting post on the different types of AWS keys and login credentials you'll need with AWS:

http://alestic.com/2009/11/ec2-credentials

Only thing I'd also add is the use of IAM roles which are basically set up so you don't have to store credentials on a machine running AWS services. The services can make use of the role which is built into AWS EC2 instances when running on an EC2 instance -- that way you don't need to store the credentials anywhere on the machine:

http://docs.aws.amazon.com/AWSSdkDocsJava/latest/DeveloperGuide/java-dg-roles.html

Setting Up AWS Command Line Interface on Windows 2008 64 bit EC2 instance

The AWS documentation combines a lot of information for different operating systems to install AWS command line interface.

http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-set-up.html#cli-test-the-setup

Abbreviated version for Windows 2008 64 bit EC2 instance:

1. Download and install the Windows 64 bit version of the command line tools -

http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-set-up.html#install-msi-on-windows

2. Open a new command window (go to start, run, type cmd on Windows 2008)

3. Type "aws help" to verify CLI is working.

4. Type "aws configure" and enter info when prompted -- will look something like this but use your key:

AWS Access Key ID [None]: AKIAIOSFODNN7EXAMPLE
AWS Secret Access Key [None]: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
Default region name [None]: us-west-2
Default output format [None]: json

Then you'll need to add an environment variable to tell the tool where to find your configuration file:

Start, Control Panel
Search box in top right - type 'Env' and search
Click Edit the System Environment Variables
Click Environment Variables button
Click New button under bottom box
 Enter name or variable: AWS_CONFIG_FILE
Enter the value which is the path to your conifg file, something like this: C:\Users\UserNameHere\.aws\config
Enter, ok, yada yada

Configuration Details:

http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html

If you forgot your access key: Go to Services - IAM - Click on Users -- choose an existing user and create a new key or create a new user and a new key depending on what you're doing.

Your region name is part of the availability zone name listed for your EC2 instance in the details - Regions:

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-zones.html#concepts-regions-availability-zones

Important

The configuration file may contain confidential information, such as your security credentials. We recommend setting the file permissions of the configuration file so that it is readable only by the current user.

Install Python on Amazon EC2 Windows 2008 Instance

Download and Install Python:

http://python.org/download/

That page has all the versions with or without source for different operating systems - the lasted for windows without source at time of this writing:

http://python.org/ftp/python/3.3.3/python-3.3.3.msi

Add python to your Path environment variable:
-- Go to start, control panel
-- In search box on top right type "env" and hit enter.
-- Click on Edit the environment variables
-- Click on Environment Variables button
-- In bottom box scroll down until you see the "Path" environment variable - click on it and then edit button
-- At the of the path string add a semicolon and the path to your Python folder. Mine by default was: ;C:\Python33\;
-- hit all the ok, yeah, sure buttons...

Open a new command line window and type the following to see that Python is running:

python --help

You'll see a bunch of output like this line followed by a bunch of commands you can run:

usage: python [option] ...  

Setting up a Bastion Host on AWS

Edit: 5/20/2017 There's another option for creating a bastion host on AWS here: https://aws.amazon.com/blogs/mt/replacing-a-bastion-host-with-amazon-ec2-systems-manager/

In order to reduce attack vector and secure assets on AWS you may want to limit RDP and/or SSH access to instances through a bastion host. Bastion hosts are for inbound traffic and fully exposed to attack. These are typically computers that you want to harden very carefully and audit regularly.

http://en.wikipedia.org/wiki/Bastion_host

The nice thing on AWS is that you can simply turn off your bastion host when not in use and fire it back up as needed.

In my case I would set up one for Linux and one for Windows.

- create bastion security group(s) within your VPC
- create instances for bastion host(s) in bastion security group
- only allow remote access traffic bastion hosts to other instances in your VPC

Create one bastion security group if same people manage all the servers. If you have different people managing different server groups may further break it down based on separation of duties, say Linux server managers and Window server managers or server management and db access to RDS.

For example:

Create a bastion-windows security group. Allow traffic in only from machines allowed to access servers via RDP by limiting to IP range, VPN, etc.

Create a bastion-linux security group. Allow trafffic only from machines allowed to SSH into Linux instances at AWS.

Limit RDP to any windows boxes to only be allowed from the bastion-windows security group.

Limit SSH access to any linux machines only from the bastion security group.

That will funnel all your remote access through one channel to limit your attack vector and access points to manage.

Other options:

You might install client tools to access database on this machine or force people to log into this machine, then remote to the machine that has the client db tools depending on what type of separation of duties and groups you require.

You might want to segregate this further based on your organizational needs for separation of duties or set up additional instances to handle the load.

For example, you may want developer traffic to go straight to their instances and not through the bastion hosts for production machines.

You might also want to break it down to limit exposure of third party software security flaw. For example if you created a bastion host for accessing MySQL RDS and installed MySQL WorkBench on that host but limited that hosts access to any other machine remotely, that limits exposure if any remote execution flaws exist in MySQL WorkBench. (Not that there are any that was just an example of third party software).

Note that putting any extraneous software on your bastion host is a security risk and would be better to avoid altogether if possible.

About arbitrary execution flaws:
http://en.wikipedia.org/wiki/Arbitrary_code_execution

Can't install MySQL WorkBench on AWS instance - Solved

If you get an error stating you have to install 32 bit version of MySQL workbench when you try to install it on an AWS instance, try installing this, then attempt the MySQL workbench install again.

Microsoft Visual C++ 2010 Redistributable Package (x86)

http://www.microsoft.com/download/en/details.aspx?id=5555

Migrating SQL Server to AWS MySQL RDS Instance

Just went through the process of migrating a SQL Server database hosted on an EC2 instance to an RDS instance of MySQL. Installing Microsoft SQL Server database on AWS EC2 instance is just like putting it on any other server. Did that a while ago to test out functionality. Next wanted to migrate that data over to RDS.

Here's how I did it.

1. Created a user on my database with appropriate permissions and SQL Server password (as opposed to Windows login).

2. Set up Windows EC2 instance in private database subnet/security group where my SQL Server database was located.

3. Added the security group to itself for inbound and outbound connections to ensure instances in the group could connect to each other (see previous post).

4. Set up an instance of MySQL on RDS in database specific subnet but same security group as my SQL Server database.

5. Set up ODBC connection (Administrative tools / ODBC data sources) on EC2 instance to SQL Server database, connected and tested it out. Just created a user connection, enter IP address of EC2 instance running SQL Server, user name, password.

6. Downloaded MySQL installer for Windows from Oracle web site that does not require Internet connection.

7. Copy MySQL installer to EC2 instance and installed. Ran and failed.Said I have to run 32 bit version (which I was).

9. Spent way too long figuring out that I need to instsall this to get MySQL workbench running on small/medium AWS EC2 instances.

Microsoft Visual C++ 2010 Redistributable Package (x86)

http://www.microsoft.com/download/en/details.aspx?id=5555

10. Replace the batteries in my mouse (Just seeing if you are still paying attention. It's been a long day.).

11. Run MySQL Workbench - go to Database Menu and choose Migration Wizard.

12. Click Start Migration

13. For Source RDBMS selected:
Database System = Microsoft SQL Server
Connection Method=ODBC
Selected DSN I created above
Entered user name
Clicked store in vault and entered password
> Test Connection
> click next

14. For destination RDBMS:
Connection Method: Stardard TCP/IP
Host Name: domain name of RDS instance from AWS console
Default Port
Enter user name and password
> Test connection (at bottom)
> Next

15. Next screen - runs the script that creates the schema in the RDS instance based on analysis of MS SQL database. Note in previous post I ended up deleting a table to work around a problem with the reverse engineering of the schema step.
> Next

16. Select schemas and options from databases you want to migrate.
> Next

17. Choose objects you want to migrate
> Next

18. No migration errors.
> Next

19. Check create schema in target RDBMS (I also opted for script file)
> Next

20. Review. No errors or warnings.
> Next

21. Online copy of data to target database
> Next

22. This is where the bulk data copy occurs and the server gets really busy...
...wait...

23. At this point my MySQL WorkBench locked up and machine CPU utilization spiked to 100%  for a long period of time. I ran it for a few hours, then fired up MySQL WorkBench on another instance and spot checking see that my tables and data seem to be present on the other machine.

24. Killed it - was taking too long. Could see most of tables were created by no stored procedures.

Round 2 - tried running the whole process again with new machine and only selected tables. Also corrected my selection and chose to keep my schema in tact this time around.

25. Checked logs here by default [insert your user name] while process was running because it appeared when I hit "show logs" mysql workbench locked up:

C:\Users\[user name]\AppData\Roaming\MySQL\Workbench\log

26. Saw that my script seems to lock up on dbo objects starting with dtproperties.

27.Checked tables and all in my own schema (not dbo) appear to have data.

28. Attempted to run stored procedures, functions and views - kept hanging.

29. Tried to create a script for procedures, functions and views and output to file.

30. Ran script and figured out user account created in RDS set up process does not have permissions to execute.

31.How to enable triggers and stored procedures:

http://websitenotebook.blogspot.com/2013/11/enabling-triggers-and-stored-procedures.html

32. Got stored procedure migration script output to a file but there are numerous issues and syntax differences not handled you will need to work through to get the stored procedures to run:

http://websitenotebook.blogspot.com/2013/11/converting-sql-server-strored.html

33. For loading large amounts of data consider these recommendations...my machine kept locking up using the migration tool:

http://aws.amazon.com/articles/2933

Error in MySQL Migration Tool: UnicodeDecodeError("'utf16' codec can't decode byte 0x2e in position 38: truncated data")

Using MySQL WorkBench 6.0.7.11215 on Windows 2008 tried running the migration tool to migrate SQL Server 2008 R2 database to local MySQL database and got this error:

ERROR: Reverse engineer selected schemata: UnicodeDecodeError("'utf16' codec can't decode byte 0x2e in position 38: truncated data"): error calling Python module function DbMssqlRE.reverseEngineer

The script failed at a particular table which only had a few rows of data in it and really wasn't needed anymore so I deleted all the rows - that didn't solve the problem as expected because I figured it was only copying the schema.

I checked the column names and nothing odd there - so I just dropped the whole table since I didn't need it and that solved the problem.

There may have been something in the meta data for that table which caused the problem such as single quote, but brute force method worked for me.

Copying Files to AWS Instance Using RDP

If you want to copy files to AWS EC2 instance...

Before you connect to the instance, wander through the tabs and find check the box under "Local drives and resources" for your C: drive. Once connected to the instance, open Windows Explorer and in the address bar, type "\\tsclient\c" (without quotes). your local PC's C: drive will (slowly) open up in Explorer and you can drag&drop.

This doesn't require any additional ports to be opened up; it goes over the RDP connection you already using for remote desktop.

AWS Instances in Same Security Group Can't Connect to Each Other

I was setting up two databases in the same security group to try to migrate data from one to the other - both installed on EC2 instances, not RDS. I couldn't initially ping one machine from the other, even though I had opened up the firewall. I guess I assumed two machines in the same security group would be able to talk to each other...not the case. To resolve this I put the security group into itself in the security group configuration for both inbound and outbound traffic and then it worked.

In other words:

Edit your security group, click on details and copy the group id.

Click on the inbound tab.

Add a rule to allow whatever traffic you want to go from one server in the group into another server in the group and add the security group id as the source.





Click on the outbound tab and repeat.

Assuming you've set up appropriate firewall rules you should now be able to ping one box from another to verify the connection is working.

The Login Packet Used to Open The Connection is Structurally Invalid. 17832 SQL Server

I set up an ODBC connection to SQL Server 2008 R2 running on an AWS instance and got this error in my SQL Server logs (the login on the client was more cryptic):

The Login Packet Used to Open The Connection is Structurally Invalid. 17832 SQL Server

I had Downloaded the SQL Server ODBC driver version 11.

This article led me down a wild goose chase setting token sizes and other things unrelated to my particular SQL Server login problem:

http://support.microsoft.com/kb/169521

I was just about to try another driver version when I noticed there was a default driver in the AWS Windows 2008 instance I fired up (yes it has been a very long time since I installed MS ODBC for SQL Server - been using Java). And I had a former operations guy at my house geeking out with me who told me I needed to download the driver...so I will just blame him because it's convenient. Ha.

I set up the connection using the default ODBC driver that came in Windows 2008 and the problem magically went away. Connection successful.

Running Java Application as Daemon on EC2 Instance

To run an application as a daemon (similar to Windows service) these are the primary options:

Java Service Wrapper

http://wrapper.tanukisoftware.com/doc/english/launch-nix.html

Apache Jakarta Commons Daemon

http://commons.apache.org/proper/commons-daemon/

Shell Script

http://www.source-code.biz/snippets/java/7.htm

http://stackoverflow.com/questions/11809191/linux-launch-java-program-on-startup-ec2-instance

AWS Linux EC2 instance: java.lang.UnsupportedClassVersionError Unsupported major.minor version 51.0 on

If you are getting this error trying to run a Java application you compiled on Windows and are trying to run on a Linux AWS EC2 instance, it is probably because the AWS EC2 instance comes with an OpenJDK version of Java that is not compatible:

Exception in thread "main" java.lang.UnsupportedClassVersionError: com/radicalsoftware/Main : Unsupported major.minor version 51.0
        at java.lang.ClassLoader.defineClass1(Native Method)
        at java.lang.ClassLoader.defineClass(ClassLoader.java:634)
        at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
        at java.net.URLClassLoader.defineClass(URLClassLoader.java:277)
        at java.net.URLClassLoader.access$000(URLClassLoader.java:73)
        at java.net.URLClassLoader$1.run(URLClassLoader.java:212)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:205)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:321)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:294)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:266)
Could not find the main class: com.radicalsoftware.Main. Program will exit.

To fix this problem you could attempt to remove that version of Java and change JAVA_HOME to point to the version of Java you want, however I am not sure if that will cause anything AWS related to malfunction on the Linux instance.

An alternative is just to specify the path to the version of java you want to run when you run your application

So instead of:

java -jar com.radicalsoftware.jar

Use this:

/usr/java/jdk1.7.0_25/java -jar com.radicalsoftware.jar

You'll need to install the Oracle JDK of course as noted in this post:

http://websitenotebook.blogspot.com/2013/09/install-oracle-java-jdk-on-amazon-linux.html

Java Static Initializers

It's always interesting to explore other people's code and learn new constructs or see different ways of doing things. I was just looking at some code that uses static initializers in Java.  Personally I have never used this and wondered if there were any pros and cons to doing things this way.

The pros are that a static initializer will load something that is required by your class once and only once assuming your class was only loaded once over the life of your program. If a class is loaded and unloaded the static initializer will be called twice as noted in this article (and also if the class is loaded by multiple class loaders):

http://www2.sys-con.com/itsg/virtualcd/java/archives/0305/maso/index.html
 
With a static initializer you can throw an exception as well during instantiation of the class.

http://docs.oracle.com/javase/tutorial/java/javaOO/initial.html

An example is in the code I am looking at which loads a resource for a program:

 static
    {
        try
        {
            ResourceBundle props= ResourceBundle.getBundle("/com/x/y");
            Enumeration<?> i = props.getKeys();
            while(i.hasMoreElements())
            {
                String prop = (String)i.nextElement();
                String m = props.getString(prop);
                map.put(prop,m);
            }
        }
        catch(MissingResourceException e)
        {
            //do some logging
        }
    } 

Another important point with static initializers is that the variable initialization code in the class will get executed in the order it is written. Therefore you need to have the static initializer before any code that would wipe it out, or use it.

More on static initializers from the Java Language Specification:

http://docs.oracle.com/javase/specs/jls/se7/html/jls-8.html#jls-8.7

As for the use of static variables in general...here are some interesting articles discouraging the use of static:

http://www.offthehill.org/articles/2011/06/03/java-static-variables-are-wrong-almost-always/

http://sensualjava.blogspot.com/2008/12/case-against-static-initializers.html

Statics make testing a nightmare:

http://www.jusfortechies.com/java/core-java/static-blocks.php

Static variables can break unit tests

http://erlend.oftedal.no/blog/?blogid=53

More problems with static variables:

http://gbracha.blogspot.com/2008/02/cutting-out-static.html

Do not use public non final static variables for security reasons:

https://www.securecoding.cert.org/confluence/display/java/OBJ10-J.+Do+not+use+public+static+nonfinal+variables

Global variables are bad

http://c2.com/cgi/wiki?GlobalVariablesAreBad

More notes on security and static variables (initialization to be specific):

http://www.securingjava.com/chapter-seven/chapter-seven-1.html

Install Oracle Java JDK on Amazon Linux AMI

The version of Java on Amazon AWS Linux AMI is not the version of Oracle's JDK.

If you want to use the Oracle / Sun version and upgrade Java version:

wget --no-cookies --header "Cookie: gpw_e24=xxx;" http://download.oracle.com/otn-pub/java/jdk/7u25-b15/jdk-7u25-linux-x64.rpm

install...

sudo rpm -i jdk-7u1-linux-i586.rpm 

Check Java home and will probably be: /usr/lib/jvm/jre

echo $JAVA_HOME

change to path to JDK you installed:

export JAVA_HOME=/usr/java/jdk1.7.0_25

export PATH=$PATH:/usr/java/jdk1.7.0_25/bin

More here:

https://gist.github.com/tankchintan/1335220

and here:

http://www.cyberciti.biz/faq/linux-unix-set-java_home-path-variable/ 

Copying Files to an AWS Linux EC2 Instance from Windows

You have a couple options for transferring files from a Windows to a Linux server.

One is to use Putty using the Putty Secure Copy client.

The other is to use WinSCP. 

Both are documented here:

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/putty.html

You'll want to first launch your instance, make sure your firewall (security group) rules are set appropriately to allow traffic on the appropriate ports and make sure your instance is assigned an Elastic IP so it is accessible from the Internet.

Java Application Using AWS IAM Roles

Using AWS IAM roles prevents having to store credentials on your EC2 instances. More about the benefits here:

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/iam-roles-for-amazon-ec2.html

Setting up your application and instance to use IAM roles is pretty easy.

1. Change your Java application to use these constructs wherever you are instantiating an AWS client for some service:

In the sample code you'll see instantiation of AWS clients for various services like this:

 AmazonSQS sqs = new AmazonSQSAsyncClient(
   new ClasspathPropertiesFileCredentialsProvider());
       
Replace the ClassPathPropertiesFileCredentialsProvider (which gets your AWS credentials from a file) with the InstanceProfileCredentialsProvider (which gets temporary security tokens from the service running on the EC2 instance).

 AmazonSQS sqs = new AmazonSQSAsyncClient(
   new InstanceProfileCredentialsProvider());  

2.Create a role which has the appropriate permissions. From services, choose IAM. Then choose Roles on the left and click Create New Role on the top as shown below. Give your role a name and click continue.



Add permissions to the role- choose Amazon EC2.


 Select service you want your EC2 instance to access.



Customize the policies if you need - continue.



And...Create Role



If you want to add additional permissions you can attach more policies.



3. Launch and instance and assign the role - assuming here you know how to launch an instance. That is covered in previous blog entries.



4. Copy your code up to the instance and run your application. If it worked with the security credential provider on your local machine with a file on the class path it should work with the instance security provider on the EC2 instance.