RDS Patrick Custom

From Training Material
Jump to navigation Jump to search

MySQL for Amazon RDS Training Course
Patrick Mazzotta

MySQL for Amazon RDS Training Course ⌘

Mysql-logo.jpg + AmazonWebServicesLogo.jpg

A developer's primer on MySQL with RDS orientation.

About Us⌘


  • Training & Consultancy Provider
  • Founded in 2005
  • Currently offering over 400 courses (remote & on-site)
  • Continuously exapnding through franchishing opportunities

About Me⌘


  • Consulting in IT & BI for 20 years
  • CEO & Co-Founder of Craedone
    • Analytics
    • Business Intelligence
    • Corporate Strategy/Red Team services
  • Partnered with NobleProg since 2013


  • customization vs. course standards
    • content
    • ugly slides
  • SQL Server -> MySQL high-level
  • Dialogue

MySQL Programs ⌘

    • mysqld - the MySQL server
    • mysqld_safe - a script which starts mysqld, recommended of starting the server
      • restarts the server when an error occur
      • logs run-time and failure information
    • mysqld_multi - script that can start or stop multiple servers installed on the

system (different ports or sockets)

    • mysql - a command line client
    • mysqladmin - administrative operations
      • relading the grant tables
      • creating/dropping database
      • flushing tables to disk
      • managing log files
      • shutdown the database

MySQL Client⌘

apt-get install mysql-client

We can get the RDS command line tools if we want:



EC2 vs RDS⌘

  • This is not a pros/cons scale!
  • driven by preference and tech needs
  • direct cost comparisons are hard







full admin control

traditional admin tasks are time consuming

less time on admin tasks

no ssh (no tweaking)

traditional SSH access and administration

plan for more (logs, patching, etc.)

automated patching/updates

apps must not be version-dependant

vertical scaling is easy

more planning for resources

very good performance out of the box

some simple things become much more complex

theoretically capable of better performance

uesr management is good, but may not be intuitive

debugging can be more work (logs)

RDS Server Configuration⌘

Amazon’s relational database service [RDS] offers multiple platforms for their customers:

  • MySQL
  • Oracle
  • SQL Server
  • PostgreSQL

RDS is syntactically the same as the platforms it’s runs, but there are some minor differences in administrative concerns.

Benefits of RDS⌘

There are a number of factors that make RDS an attractive choice when evaluating relational databases for your architecture.

Pre-configured (and reusable) parameters DB Event notices (SMS or email)
Built-in monitoring and metrics Multi-Availability Zone (off-site failovers)
Automatic updates/patches Provisioned IOPS
Automated backups Push-button scaling
Snapshot management Automatic Host Replacements
Isolation & Security Replication across Multi-AZ
Resource-level permissions Programmable instance control

Even Cheaper than RDS?⌘

Reserved instances ([pricing page]) are an option for RDS deployments known to be long-term solutions. From AWS: "Amazon RDS Reserved Instances give you the option to make a low, one-time payment for each DB instance you want to reserve and in turn receive a significant discount on the hourly charge for that instance."

  • much cheaper than dynamic instances
m3 type reserved price/h dynamic price/h
single AZ $0.089 $0.090
multi AZ $0.151 $0.180
  • can resell reserved instances through the marketplace
  • comes in light/medium/heavy utilization groupings
  • can still dynamically spin up or down multiple instances
    • as long as you're in your reserved instance zone, the instances will assume your reserved pricing rates
  • requires a one or three year commitment

The Server⌘

Deployment of all database instances in RDS can be managed through the Amazon Web Services (AWS) Console.

  • no SSH access
  • no direct log access


Launching MySQL Instance in RDS⌘

From [console.aws.amazon.com/rds/] we select the instance type.


Deployment Type⌘

  • When deploying an RDS instance you are asked if the instance is intended for production use or not.
  • This tells AWS where the instance should go, and what kind of options should be made available (i.e. the Free Usage Tier lacks provisioning options).

RDS prod prompt.png

Specify DB Details⌘


DB Details Field Summary⌘

Field Description
License Model MySQL has only one license model: general-public-license
DB Engine Version Unless specifically required, you should select the default version of MySQL. Note that RDS supports different versions based on region of deployment.
DB Instance Class This correlates directly with the instances in the RDS pricing table (see [aws.amazon.com/rds/pricing]). You should know [approximately] what class of instance you'll need before launching.
Multi-AZ Deployment This instructs AWS to create a failover replica instance. Strongly recommended for production environments.
Allocated Storage The amount of physical storage (in GB) your DB will be provisioned with. In some cases, allocating a higher amount of storage for your DB instance than the size of your DB can improve I/O performance (ref: [Amazon Relational Database Service Features].
Use Provisioned IOPS Provisioned IOPS (I/O operations Per Second) setting. This is typically used for high-performance storage needs in applications that are I/O intensive; such as transactional (OLTP) database workloads.
DB Instance Identifier The name for the DB instance that is unique for your account in the region you selected. You may chose to add some intelligence to the name such as including the region and DB engine you selected, for example west2-mysql-instance1 [helpful for sysops work].
Master Username Type a name using alphanumeric characters that you will use as the master user name to log on to your DB instance. This will be the user name you use to logon to your DB on the instance for the first time.
Master Password/Confirmation 8-16 ASCII characters (excluding /, *, and @) for your master user password.


Combo lock.jpg

AWS Identity & Access Managment⌘

Identity and Access Management (IAM) is how we control access to AWS (and thereby RDS).

  • it's a permission system
  • rather elaborate (complicated but comprehensive)
  • can manage access to:
    • DB instances
    • DB snapshots
    • Read replicas
    • Reserved instances
    • DB security groups
    • DB option groups
    • DB parameter groups
    • Event subscriptions
    • DB subnet groups
    • and all sorts of EC2 and other AWS stuff!

Standard IAM Practice⌘

  1. Create IAM users (user identities) under your AWS account for all users who will manage your Amazon RDS resources. Each user can have a separate password (for console access) and access keys (for programmatic and CLI access). You can organize IAM users into groups, which makes it easier to manage permissions for multiple users at a time.
  2. Determine what tasks each user and group will have regarding your Amazon RDS resources. For example, you could have groups for administrators, security personnel, DBAs, and developers.
  3. Optionally, you can tag the Amazon RDS resources you want to control access to. You can assign a tag, a key-value pair, to any Amazon RDS resource, and use that tag as a way to specify a particular resource in an IAM policy..
  4. Create the IAM policies that define the actions a user can take, and specify the Amazon RDS resources required for each task using Amazon Resource Names (ARNs). If you have used tags for your Amazon RDS resources, you can add conditions to the policy to test for those tag values.
  5. Attach the policies to the applicable users or groups.

IAM Policies⌘

Here there be dragons.jpg

IAM Policies (con't)⌘

  • By default newly created IAM users do not have permission to access any AWS resources
  • users require IAM policies to access the required Amazon RDS API (or any AWS) actions and resources
  • policies can be attached to users, groups, or roles
  • An IAM policy is a JSON document that consists of one or more statements
    "Action": "rds:CreateDBInstance",
    "Condition": {"streq":[
  • The Version is required, and the value should be "2012-10-17"
    • this is the date of the most current version of the AWS IAM policy system
  • The Effect element is set to either "Allow" or "Deny"
    • Actions are denied by default, so you have to specify "Allow"
  • The Action element lists which AWS APIs the policy will allow.
    • here the Action element lists one action from the Amazon RDS API - so it will be the only action allowed by this policy statement
    • Note that the action is identified by both service name (rds) and action (CreateDBInstance)
    • policies can list actions from any AWS service. You can use wildcards (*) to specify actions
      • i.e. the action rds:Describe* would allow the user to perform any Amazon RDS action that begins with Describe (DescribeDBInstances, DescribeDBLogFiles, DescribeDBParameterGroups,DescribeDBSnapshots, etc.)
  • The Resource element lets you specify which resources the user can perform the actions on or with
    • here the user can only create DB instances that have the prefix "test" in the DB instance name
    • You specify resources using an Amazon Resources Name (ARN) that includes the name of the service that the resource belongs to (rds), the region (us-east-1), the account number, and the type of resource (a DB instance)
  • Finally, the optional Condition element lets you specify additional restrictions on the policy such as:
    • date/time
    • source IP address
    • region
    • tags
  • In this example the Condition element indicates that the actions are allowed only on instances with the MySQL DB engine and the micro DB instance class

Attaching Policies⌘

Policies can be attached to

  • an individual IAM user
    • in this case, only that user would be allowed to perform the listed actions
  • an IAM group
    • then every IAM user in that group would have these permissions
  • or a role
    • where delegated or federated users could perform the action

Users vs. Roles vs. Groups⌘

  • Users
    • have permanent long-term credentials
    • are used to directly interact with AWS services
  • Roles
    • do not have any credentials
    • cannot make direct requests to AWS services
    • meant to be "assumed" by authorized entities, such as IAM users, applications, or an AWS service like EC2
  • Groups
    • are a collection of IAM users that share the same permissions
    • primarily a management convenience to manage the same set of permissions for a set of IAM users
    • groups cannot have roles added to themselves (yet)

Can I Haz Consoles?⌘

The following example policy statement shows permissions that users typically need in order to work in the Amazon RDS console. Notice that this includes RDS actions that start with the word "Describe," a number of EC2 and CloudWatch actions that likewise pertain to describing (listing) resources, and all SNS actions.

    "Effect": "Allow",
    "Action": [
   "Resource": "*"

Authorization Request Chain⌘

  • an IAM authorization request is generated for each resource identified (or implied) in the request
  • easy to trigger multiple resource authorizations, i.e.a point-in-time-restore to a new database instance will generate two authorization requests:
    1. An authorization request will be generated for the target database instance.
    2. An authorization request will be generated for the snapshot that is being restored
  • a policy can be used to limit the possible values a resource can have

Keys and Tags⌘

When creating an IAM policy, you can specify conditions in two ways

  1. You can create a condition that is based on a tag associated with a resource
  2. You can use a predefined key, such as the DB engine type or the DB engine class.

The following tables shows the predefined keys you can use when defining IAM policy for Amazon RDS. Note that tag key/value pairs and predefined keys are case sensitive.

Predefined Keys (AWS)⌘

AWS Predefined Key Description Value Type
aws:CurrentTime The current time. Used for date conditions. Date/Time
aws:EpochTime The current time in epoch or UNIX time format. Used for date conditions. Date/Time
aws:principaltype The type of principal (user, account, federated user, etc.) for the current request. String
aws:SourceIp The requester's IP address (see IP Address ). Note that if you use aws:SourceIp, and the request comes from an Amazon EC2 instance, the instance's public IP address is evaluated. IP Address
aws:UserAgent The requester's client application. String
aws:userid The requester's user ID. String
aws:username The requester's user name String

Predefined Keys (RDS)⌘

Amazon RDS also has predefined keys that you can include in Condition elements in an IAM policy. Amazon RDS predefined keys do not apply to all actions; the Amazon RDS predefined keys apply to the following actions:

  • CreateDBInstance
  • ModifyDBInstance
  • DeleteDBInstance
  • DescribeDBLogFiles
  • AddTagsToResource
  • RemoveTagsFromResource
  • RestoreDBInstanceToPointInTime
  • RestoreDBInstanceFromDBSnapshot
  • DownloadDBLogFilePortion
  • DescribeDBInstances
RDS Predefined Key Description Value Type
rds:DatabaseClass The DB instance class of a DB instance String
rds:DatabaseEngine The DB engine of the DB instance String
rds:DatabaseName The name of the database on the DB instance String
rds:MultiAz Indicates if the DB instance is running in multiple availability zones. 1 indicates that the DB instance is using multi-AZ. Integer
rds:Piops This key will be present when a request is made for a DB instance with PIOPs enabled. The value will contain the number of provisioned IOs that an instance supports. 0 indicates does not have PIOPs enabled. Integer
rds:StorageSize The storage volume size (in GB) Integer
rds:Vpc Indicates if the database instance is running in a virtual private cloud Boolean

Conditional Elements⌘

You can add your own tags to an Amazon RDS resource and then create policies that use the tag values in determining whether to grant or deny access. Amazon RDS resource tags are formatted slightly differently than predefined keys in aCondition element. For example, the following Condition element uses a predefined key and specifies that the condition applies to the DB engine MySQL:

"Condition":{"streq":{"rds:DatabaseEngine": "mysql" } }

The following Condition element uses an Amazon RDS tag and specifies that the condition applies to a tag with a key named environment and a value of production.

"Condition":{"streq":{"rds:db-tag/environment": ["production"]} }

More on the IAM policy Condition element [here]

Example IAM Policies for RDS⌘

Example 1: Permit a user to perform any Describe action on any RDS resource⌘

The following statement allows a user to run all the actions whose names begin with "Describe," which shows information about an RDS resource such as a DB instance. Note that the “*” in the Resource element indicates that the actions are allowed for all Amazon RDS resources.


Example 2: Permit a user to create a DB instance that uses a specified DB engine⌘

The following statement uses a predefined Amazon RDS key and allows a user to create only DB instances that use the MySQL DB engine. The Condition element indicates that the DB engine requirement is MySQL.

"Action": "rds:CreateDBInstance",
"Resource": "*",

Example 3: Permit a user to create a DB instance that uses the specified DB parameter and security groups⌘

The following statement allows a user to only create a DB instance that must use the mysql-production DB parameter group and the db-production DB security group.

"Action": "rds:CreateDBInstance",
"Resource": [ 
    "arn:aws:rds:us-east-1:1234567890:secgrp:db-production" ]

Example 4: Prevent a user from creating a DB instance that uses specified DB parameter groups⌘

The following statement prevents a user from creating a DB instance that uses DB parameter groups with specific tag values. You might apply this policy if you require that a specific customer-created DB parameter group always be used when creating DB instances. Note that statements that use Deny are most often used to restrict access that was granted by a broader statement.

  "Action": "rds:CreateDBInstance",
  "Resource": "*",
  "Condition": {"streq": {"rds:db-tag/usage" : "prod" } }

Example 5: Prevent users from creating DB instances for certain DB instance classes and from creating DB instances that use Provisioned IOPS⌘

The following statement prevents users from creating DB instances that use the DB instance classes m2.2xlarge and m2.4xlarge, which are the largest and most expensive instances. This example also prevents users from creating DB instances that use Provisioned IOPS, which is an additional cost.

    "Resource": "*",
    "Condition":{"streq":{"rds:DatabaseClass":["db.m2.4xlarge", "db.m2.2xlarge"]}}
    "Resource": "*",

You can add a tag to an Amazon RDS resource, and then use that tag in a policy to specify a particular resource. The following examples use Amazon RDS resource tags as part of the IAM policy to specify a particular resource.

Example 6: Permits a user to perform an action on a resource tagged with two different values⌘

This following statement allows a user to perform the ModifyDBInstance and CreateDBSnapshot actions on instances with either the “stage” tag set to “development” or “test.”

"Action": [
    "rds:CreateDBSnapshot" ],
"Condition":{"streq":{"db-tag/stage": [ "development", "test" ] } }

Example 7: Permits a user to perform actions on a DB instance with a DB instance name prefixed with the user name⌘

This following statement allows a user to perform any action (except to add or remove tags) on a DB instance that has a DB instance name that is prefixed with the user's name and that has a tag called "stage" equal to "devo" or that has no tag called "stage."

  "NotAction": ["rds:AddTagsToResource","rds:RemoveTagsFromResource"],
  "Resource": "arn:aws:rds:*:314195462963:db:${aws:username}*",

RDS Security Groups⌘

Security groups control the access that traffic has in and out of a DB instance.

Three types of security groups are used with Amazon RDS:

  1. DB security groups
  2. VPC security groups (virutal private network cloud)
  3. EC2 security groups

DB Security Groups⌘

Each DB security group rule enables a specific source to access a DB instance that is associated with that DB security group. The source can be a range of addresses (e.g.,, or an EC2 security group. When you specify an EC2 security group as the source, you allow incoming traffic from all EC2 instances that use that EC2 security group. Note: DB security group rules apply to inbound traffic only; outbound traffic is not currently permitted for DB instances.

  • You do not need to specify a destination port number when you create DB security group rules; the port number defined for the DB instance is used as the destination port number for all rules defined for the DB security group.
  • DB security groups can be created using the Amazon RDS APIs or the Amazon RDS page of the AWS Management Console.

VPC Security Groups⌘

  • Each VPC security group rule enables a specific source to access a DB instance in a VPC that is associated with that VPC security group.
    • The source can be a range of addresses (e.g.,, or another VPC security group
    • specifying a VPC security group as the source allows incoming traffic from all instances (typically application servers) that use the source VPC security group
    • VPC security groups can have rules that govern both inbound and outbound traffic, though the outbound traffic rules do not apply to DB instances
    • Note: you must use the Amazon EC2 API or the Security Group option on the VPC Console to create VPC security groups.
  • DB instances deployed within a VPC can be configured to be accessible from the Internet or from EC2 instances outside the VPC
    • if a VPC security group can specify a port access such as TCP port 22 you will not be able to access the DB instance
      • the firewall for the DB instance provides access only via the IP addresses specified by the DB security groups the instance is a member of and the port defined when the DB instance was created

You should use TCP as the protocol for any VPC security group created to control access to a DB instance. The port number for the VPC security group should be the same port number as that used to create the DB instance.

DB Security Groups vs. VPC Security Groups⌘

The following table shows the key differences between DB security groups and VPC security groups.

DB Security Group VPC Security Group
Controls access to DB instances outside a VPC Controls access to DB instances in VPC.
Uses Amazon RDS APIs or Amazon RDS page of the AWS Management Console to create and manage group/rules Uses Amazon EC2 APIs or Amazon VPC page of the AWS Management Console to create and manage group/rules.
When you add a rule to a group, you do not need to specify port number or protocol. When you add a rule to a group, you should specify the protocol as TCP, and specify the same port number that you used to create the DB instances (or Options) you plan to add as members to the group.
Groups allow access from EC2 security groups in your AWS account or other accounts. Groups allow access from other VPC security groups in your VPC only.

Security Group Scenario⌘

A common use of an RDS instance in a VPC is to share data with an application server running in an EC2 instance in the same VPC and that is accessed by a client application outside the VPC. For this scenario, you would do the following to create the necessary instances and security groups. You can use the RDS and VPC pages on the AWS Console or the RDS and EC2 APIs.

  1. Create a VPC security group (for example, "sg-appsrv1") and define inbound rules that use as source the IP addresses of the client application. This security group allows your client application to connect to EC2 instances in a VPC that uses this security group.
  2. Create an EC2 instance for the application and add the EC2 instance to the VPC security group ("sg-appsrv1") you created in the previous step. The EC2 instance in the VPC shares the VPC security group with the DB instance.
  3. Create a second VPC security group (for example, "sg-dbsrv1") and create a new rule by specifying the VPC security group you created in step 1 ("sg-appsrv1") as the source.
  4. Create a new DB instance and add the DB instance to the VPC security group ("sg-dbsrv1") you created in the previous step. When you create the instance, use the same port number as the one specified for the VPC security group ("sg-dbsrv1") rule you created in step 3.

The following diagram shows this scenario.

Security Group Scenario⌘

Rds group security.png

Working with DB Security Groups⌘

A DB security group controls network access to a DB instance that is not inside a VPC. By default, network access is turned off to a DB instance. You can specify rules in a security group that allows access from an IP address range, port, or EC2 security group. Once ingress rules are configured, the same rules apply to all DB instances that are associated with that security group. You can specify up to 20 rules in a security group.

If you are a new customer to Amazon RDS or if you are an existing customer who is using a new region, your DB instance is most likely in a default VPC. You cannot use a DB security group for a DB instance inside a VPC; you must create a VPC security group. For information on creating a VPC security group, see Security Groups for Your VPC. To determine if you have a default VPC, see step 2 in the following procedure. Read More:

Creating DB Security Groups⌘

To create a DB security group, you need to provide a name and a description.

From AWS Management Console:

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
  2. Determine what platforms are supported for your AWS account in your current region.

If Supported Platforms indicates EC2,VPC, your AWS account in the current region does not use a default VPC. You can continue following the steps below to create a DB security group that will enable access to your DB instance. Secgroupstep2.png

Creating DB Security Group (con't)⌘

If Supported Platforms indicates VPC, your AWS account in the current region uses a default VPC. This means that you must create a VPC security group to enable access to a DB instance instead of a DB security group. For information on creating a VPC security group, see [Security Groups for Your VPC]. Secgroupstep2b.png

Creating DB Security Group (con't)⌘

  1. Click Security Groups in the navigation pane on the left side of the window.
  2. Click Create DB Security Group.


Creating DB Security Group (con't)⌘

  1. Type the name and description of the new DB security group in the Name and Description text boxes. Note that the security group name cannot contain spaces and cannot start with a number.


  1. Click Yes, Create. The DB security group will be created. Note that a newly created DB security group does not provide access to a DB instance by default. You must specify a range of IP addresses or an Amazon EC2 security group that can have access to the DB instance. To specify IP addresses or an Amazon EC2 security group for a DB security group, see Authorizing Network Access to a DB Security Group from an IP Range.

Creating DB Security Group (CLI)⌘

Use the command rds-create-db-security-group with the following parameters:

$>rds-create-db-security-group mydbsecuritygroup -d "My new security group"

Note that a newly created DB security group does not provide access to a DB instance by default. You must specify a range of IP addresses or an Amazon EC2 security group that can have access to the DB instance. To specify IP addresses or an Amazon EC2 security group for a DB security group, see [Authorizing Network Access to a DB Security Group from an IP Range].

Creating DB Security Group (API)⌘

  • Call CreateDBSecurityGroup with the following parameters:
    • DBSecurityGroupName = mydbsecuritygroup
    • Description = "My new security group"
	&AWSAccessKeyId=<AWS Access Key ID>

Note that a newly created DB security group does not provide access to a DB instance by default. You must specify a range of IP addresses or an Amazon EC2 security group that can have access to the DB instance. To specify IP addresses or an Amazon EC2 security group for a DB security group, see [Authorizing Network Access to a DB Security Group from an IP Range].

Listing Available DB Security Groups⌘

You can list which DB security groups have been created for your AWS account.

  • AWS Console
    1. Sign in to the AWS Management Console and open the Amazon RDS console athttps://console.aws.amazon.com/rds/.
    2. Click Security Groups in the navigation pane on the left side of the window.
    3. The available DB security groups appear in the DB Security Groups list.
  • CLI
    • Use the command rds-describe-db-security-groups to list all available DB security groups for your AWS account
  • API
    • Call DescribeDBSecurityGroups with no parameters
	&AWSAccessKeyId=<AWS Access Key ID>

Viewing DB Security Group⌘

You can view detailed information about your DB security group to see what IP ranges have been authorized.

  • AWS Console - select the details icon for the DB security group you want to view


  • CLI - use the rds-describe-db-security-groups to view a DB security group specifying the DB security group you want to view
PROMPT>rds-describe-db-security-groups <mydbsecuritygroup>
  • API - call DescribeDBSecurityGroups with the parameter DBSecurityGroupName = <mydbsecuritygroup>
	&AWSAccessKeyId=<AWS Access Key ID>


Amazon RDS supports SSL connections with DB instances running the MySQL database engine.

  • Amazon RDS creates an SSL certificate and installs the certificate on the DB instance when Amazon RDS provisions the instance
  • These are signed by a certificate authority. The SSL certificate includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks. The public key is stored at https://rds.amazonaws.com/doc/mysql-ssl-ca-cert.pem.
  • To encrypt connections using the default mysql client, launch the mysql client using the --ssl_ca parameter to reference the public key, for example:
mysql -h myinstance.c9akciq32.rds-us-east-1.amazonaws.com --ssl_ca=rds-ssl-ca-cert.pem --ssl-verify-server-cert
  • You can use the GRANT statement to require SSL connections for specific users accounts. For example, you can use the following statement to require SSL connections on the user account encrypted_user:
GRANT USAGE ON *.* TO 'encrypted_user'@'%' REQUIRE SSL

And many more!⌘


  • You can't authorize an Amazon EC2 security group that is in a different AWS Region than your DB instance
  • By default network access is turned off to a DB instance - if you want to access a DB instance that is not in a VPC, you must set access rules for a DB security group to allow access from specific EC2 security groups
  • revoking rules and permissons aren't always immediate (users vs ingress rules)

MySQL Log Files⌘

Nope.jpg (kinda)

MySQL Logging Formats⌘

Binary log traditionally uses three formats:

  • Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.
  • In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected. You can cause the server to use row-based logging by starting it with --binlog-format=ROW.
  • A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED.

"With statement-based replication, there may be issues with replicating nondeterministic statements. In deciding whether or not a given statement is safe for statement-based replication, MySQL determines whether it can guarantee that the statement can be replicated using statement-based logging. If MySQL cannot make this guarantee, it marks the statement as potentially unreliable and issues the warning 'Statement may not be safe to log in statement format.'"

Logging in RDS⌘

  • all access is through AWS console or configured through AWS console
  • Amazon RDS supports mixed and row-based binary logging (version-dependant)
  • viewing and listing logs: [DRY]

Slow Query Log⌘

  • enable via DB Parameters in the RDS console
    • Rds slow query log.png
  • default setting for long_query_time is 10 seconds
  • query against mysql.slow_log
  • rotate the "log" by calling stored procedure CALL rds_rotate_slow_log
  • can log queries that are faster than the long_query_time by enabling log_queries_not_using_indexes

You can monitor the MySQL error log, slow query log, and the general log directly through the Amazon RDS console, Amazon RDS API, Amazon RDS CLI, or AWS SDKs. You can use the mysqlbinlog utility to download or stream a binary log. The MySQL error log is generated by default; you can generate the slow query and general logs by setting parameters in your DB parameter group. Amazon RDS rotates all of the MySQL log files, the intervals for each type are given below

So how to manage it?⌘

  • Splunk
  • Logstash
  • old-school

MySQL vs. SQL Server⌘


Advantages of Mysql over SQL Server in RDS⌘

Or, "Preaching to the choir".

  1. Read-Replicas: You can utilize the mysql built-in replication features For read-heavy database workloads by creating Read Replicas.
  2. Availability: To increase database availability you could use the Multi-azfeature.In the event of DB Instance failure or an Availability Zone failure, Amazon RDS will automatically failover to the standby so that database operations.
  3. VPC's: Only Mysql instances can be configured in VPC.With Amazon VPC, you can define a virtual network topology and customize the network configuration to closely resemble a traditional network that you might operate in your own datacenter.
  4. High SLA: Amazon RDS recently came out of Beta and also offered a higher SLA(99.95%) for Multi-az instances which is currently available only for MysQl and Oracle

Amazon RDS SLA

The Comp Train⌘

The following slides compare the different platforms and related features. Where possible, some best practices are included.


The latest official version of SQL is SQL:2008.

MS SQL Server

MS SQL Server 2005 on Windows XP. Microsoft's SQL implementation is sometimes namedTransact-SQL, or TSQL. In this document, I'll generally write MSSQL as a short-hand for Microsoft's SQL Server product.


MySQL Database Server 5.0.18 on Fedora Linux (i.e. MySQL AB's "classic" DBMS product�not MaxDB).

The products are running with their default settings. This is important for MySQL and MSSQL: Their interpretation of SQL may be changed rather drastically by adjusting certain configuration options, potentially increasing the level of standard



Views are part of the standard, and they may be updated, as long as it 'makes sense'.SQL:2008 has a rather complicated set of rules governing when a view is updatable, basically saying that a view is updatable, as long as the update-operation translates into an unambiguous change.SQL-92 was more restrictive, specifying that updatable views cannot be derived from more than one base table.


Conforms to at least SQL-92.


Conforms to at least SQL-92.

Join types and features⌘

All the DBMSes support basic INNER JOINs, but vary in their support for other join types. In the following feature chart, a ✓ means yes; an empty table cell means no.

Join type/feature



Natural joins (only tested: NATURAL LEFT JOIN)


FULL joins1 (tested: SELECT...FULL JOIN...ON...=...)

Explicit CROSS JOIN (cartesian product)

Note that FULL joins may be emulated with a union of a left and a right join.

Copying structure⌘

Objective: An existing table, t1 needs to be copied to a new table, t2, without copying data. I.e., only the structure/definition of the table is copied.


Optional feature T171 defines LIKE clause in table definition:
CREATE TABLE t2 ( LIKE t1 )The DBMS may support an extension of this (feature T173) which allows for more table properties to be copied:
CREATE TABLE t2 ( LIKE t1 INCLUDING IDENTITY INCLUDING DEFAULTS INCLUDING GENERATED )If INCLUDING DEFAULTS is not specified, column defaults will not be part of t2; likewise with IDENTITYand GENERATED properties.Triggers, CHECK constraints, and other 'non-trivial' table features are not copied to the new table.


Does not support the standard. Instead, MSSQL has a special SELECT ... INTO ... FROM ...construct which can be combined with an impossible WHERE-clause to copy structure only:
SELECT * INTO t2 FROM t1 WHERE 1<>1The source (t1) may be a view, as well as a table.SELECT ... INTO copies NOT NULL column attributes, but nothing else.


Complies with the core of the feature (T171), but not with the extended features (T173).MySQL does not allow you to copy the structure of a view into a table.

Ordering result sets⌘


The SQL-standard states that relations are unordered, but result sets may be ordered when returned to the user through a cursor:DECLARE cursorname CURSOR FOR
ORDER BY' column_name1''','''column_name2''',...The DBMS may additionally allow ORDER BY outside cursor definitions (optional feature IDs F850, F851, F852, F855).(Since SQL:2008)The standard doesn't specify how NULLs should be ordered in comparison with non-NULL values, except that any two NULLs are to be considered equally ordered, and that NULLs should sort either above or below all non-NULL values. However, the DBMS may optionally (as part of feature ID T611, "Elementary OLAP operations") allow the user to specify whether NULLs should sort first or last:


As well as in cursor definitions, it allows ORDER BY in other contexts. NULLs are considered lowerthan any non-NULL value.DOCUMENTATION


As well as in cursor definitions, it allows ORDER BY in other contexts.NULLs are considered lower than any non-NULL value, except if a - (minus) character is added before the column name and ASC is changed to DESC, or DESC to ASC; this minus-before-column-name feature seems undocumented.

Simple limit⌘

Objective: Want to only get n rows in the result set. Usually only makes sense in connection with an ORDER BY expression.

Note: This is not the same as a top-n query � see next section. Also: Some of the queries below may not be legal in all situations, such as in views or sub-queries. Read more at Use The Index, Luke!


The SQL standard provides three ways of performing a 'simple limit':

  • Using FETCH FIRST:(since SQL:2008)Non-core feature IDs F856, F857, F858, and F859 describe using
    SELECT ... FROM ... WHERE ... ORDER BY ... FETCH FIRST'''n'''ROWS ONLYYou may write ROW instead of ROWS.
  • Using a Window function:(since SQL:2003)Non-core Feature ID T611 specifies window functions, of which one is ROW_NUMBER() OVER:

    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    FROM tablename
    ) AS foo
    WHERE rownumber <= n
  • Using a cursor:If your application is stateful (in contrast to web applications which normally have to be seen as stateless), then you might look at cursors (core feature ID E121) instead. This involves:
    • DECLARE cursor-name CURSOR FOR ...
    • OPEN cursor-name
    • FETCH ...
    • CLOSE cursor-name


Supports the ROW_NUMBER()(since MSSQL 2005) and cursor standards-based approaches; doesn't support FETCH FIRST.MSSQL 2000 didn't support ROW_NUMBER(). Instead, a MSSQL 2000-specific syntax was needed:
SELECT 'TOP' n columns
FROM tablename
The TOP construct is still available in MSSQL 2008, and it's handy for casual SQL work.


Doesn't support the standard. Alternative solution:SELECT columns
FROM tablename

=Top-n query⌘

Objective: Like the simple limit-query above, but include rows with tie conditions. Thus, the query may return more than n rows. Some call this a quota-query. The following examples are based on this table:

|        7 | Hilda       |  12 |
|        8 | Bill        |  12 |
|        4 | Joe         |  23 |
|        2 | Veronica    |  23 |
|        3 | Michael     |  27 |
|        9 | Marianne    |  27 |
|        1 | Ben         |  50 |
|       10 | Michelle    |  50 |
|        5 | Irene       |  77 |
|        6 | Vivian      |  77 |

Now, we only want the three (n=3) youngest persons displayed, i.e. a result set like this:

|        7 | Hilda       |  12 |
|        8 | Bill        |  12 |
|        4 | Joe         |  23 |
|        2 | Veronica    |  23 |

=Top-n query (con't)⌘


With standard SQL, there are two principal ways to obtain the wanted data:

  • The fast variant:One of the major additions in SQL:2003 was the addition of non-core (i.e. optional) OLAP (online analytic processing) features. If the DBMS supports elementary OLAP (feature ID F611), then the top-n query may be formulated using a window function, such asRANK() OVER:SELECT * FROM (
    RANK() OVER (ORDER BY age ASC) AS ranking,
    FROM person
    ) AS foo
    WHERE ranking <= 3(Change ASC to DESC in the position marked like this in order to get a top-3 oldest query instead.)
  • The slow variant:If the DBMS doesn't support the elementary OLAP features, then the top-n solution may be obtained in an alternative way which is so slow that it's not a real option in most situations:Correlated subquery method, mentioned in the book Practical Issues in Database Management (chapter 9: Quota Queries) by Fabian Pascal (who, again, quotes Date for the solution):SELECT * FROM person AS px
    WHERE (
    FROM person AS py
    WHERE py.age < px.age
    ) < 3The query may make more sense if the objective is re-phrased as "Find all persons (px) such that the number of younger, other persons (py) is less than 3".(Change < to > in the position marked like this in order to get a top-3 oldest query instead.)In the article Going To Extremes by Joe Celko, there is a description of yet another principle for performing quota queries, using scalar subqueries. Scalar subqueries are more tedious to write but might yield better performance on your system.


Supports the fast standard SQL variant.MSSQL 2000 supported the slow standard SQL variant. In practice, a MSSQL-only expression had to be used instead, in order to obtain acceptable query performance:
FROM person
(Change ASC to DESC in the position marked like this in order to get a top-3 oldest query instead.)


Supports the slow standard SQL solution. In practice, this MySQL-specific solution should be used instead, in order to obtain acceptable query performance:SELECT *
FROM person
WHERE age <= COALESCE( -- note: no space between "COALESCE" and opening parenthesis
FROM person
LIMIT 1 OFFSET 2 -- 2=n-1
FROM person
)(Change <= to >= and ASC to DESC and MAX to MIN in the positions marked like this in order to get a top-3 oldestquery instead.)The offset-value 2 is the result of n-1 (remember: n is 3 in these examples).The second argument to the COALESCE call makes the query work in cases where the cardinality of the table is lower than n.

Limit with offset⌘

Objective: Want to only get n rows in the result set, and we want the first skip rows in the result set discarded. Usually only makes sense in connection with an ORDER BY expression.

In the recipes below, basic ordering is ASCending, i.e. lowest-first queries. If you want the opposite, then change ASC->DESC and DESC->ASC at the places emphasized like this.


The SQL standard provides three ways of performing 'limit with offset':

  • Using OFFSET and FETCH FIRST:(since SQL:2008)SELECT...
    FROM ...
    WHERE ...
    ORDER BY ...
    n ROWS ONLY'You may write ROW instead of ROWS.
  • Using a window function:(since SQL:2003)Non-core Feature ID T611 specifies window functions, one of which is ROW_NUMBER() OVER:

    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownum,
    FROM tablename
    ) AS foo
    WHERE rownum > skip AND rownum <= (n+skip)
  • Using a cursor:You may use a cursor (core feature ID E121), if the programming environment permits it. This involves:
    • DECLARE cursor-name CURSOR FOR ...
    • OPEN cursor-name
    • FETCH RELATIVE number-of-rows-to-skip ...
    • CLOSE cursor-name


Supports the window function and cursor based approaches.MSSQL 2000 didn't support ROW_NUMBER(); instead, a MSSQL-specific syntax had to be used:
SELECT TOP z columns -- (z=n+skip)
FROM tablename
) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
) AS BAR ORDER BY key ASC -- ('BAR' may be anything)Documentation


Doesn't support the standard approaches. Alternative solution:

SELECT columns
FROM tablename
LIMIT' n 'OFFSET' skipIn older versions of MySQL, the LIMIT-syntax is less clear:
... LIMIT [skip,] n
(i.e. the skip argument is optional).
The old syntax is still supported by later MySQL versions (the old syntax is widely used).

Inserting several rows at a time⌘


An optional SQL feature is row value constructors (feature ID F641). One handy use of row value constructors is when inserting several rows at a time, such as:INSERT INTO tablename
VALUES (0,'foo') , (1,'bar') , (2,'baz');� which may be read as a shorthand forINSERT INTO tablename VALUES (0,'foo');
INSERT INTO tablename VALUES (1,'bar');
INSERT INTO tablename VALUES (2,'baz');


Supported.(since version 2008)





The BOOLEAN type is optional (has feature ID T031), which is a bit surprising for such a basic type. However, it seems that endless discussions of how NULL is to be interpreted for a boolean value is holding BOOLEAN from becoming a core type.The standard says that a BOOLEAN may be one of the following literals:

  • TRUE
  • UNKNOWN or NULL (unless prohibited by a NOT NULL constraint)The DBMS may interpret NULL as equivalent to UNKNOWN. It is unclear from the specification if the DBMS must support UNKNOWN, NULL or both as boolean literals. In this author's opinion, you should forget about the UNKNOWN literal in order to simplify the situation and let the normal SQL three-way logic apply.It's defined that TRUE > FALSE (true larger than false).


Doesn't support the BOOLEAN type.Possible alternative type: the BIT type which may have 0 or 1 (or NULL) as value. If you insert an integer value other than these into a field of type BIT, then the inserted value will silently be converted to 1.Rudy Limeback has some notes about oddities with the MSSQL BIT type.


Offers a non-conforming BOOLEAN type. MySQL's BOOLEAN is one of many aliases to its TINYINT(1) type.(Take care if you use TINYINT(1) and JDBC with MySQL and expect to get non-boolean values from it.)MySQL accepts the literals TRUE and FALSE as aliases to 1 and 0, respectively. However, you may also assign a value of � e.g. � 9 to a column of type BOOLEAN (which is non-conforming).If you use JDBC with MySQL, then BOOLEAN is the preferred type for booleans: MySQL's JDBC-driver implicitly converts between Java's boolean and MySQL's pseudo-BOOLEAN type.Side note: MySQL has a BIT type which may be interesting for people with enormous amounts of boolean-type data.

The CHAR type⌘

For the following section, I have used this test-SQL to try to illuminate differences (unfortunately, even standard SQL as simple as this has to be adjusted for some products): Test steps:

CREATE TABLE chartest (
  charval1 CHAR(10) NOT NULL,
  charval2 CHAR(10) NOT NULL,
  varcharval VARCHAR(30) NOT NULL
INSERT INTO chartest VALUES ('aaa','aaa','aaa');
INSERT INTO chartest
  VALUES ('aaaaaa      ','aaa','aaa'); -- should truncate to 'aaaaaa    '
INSERT INTO chartest
  VALUES ('aaaaaaaaaaaa','aaa','aaa'); -- should raise error
SELECT * FROM chartest; -- should show two rows
DELETE FROM chartest WHERE charval1='aaaaaa';
SELECT * FROM chartest; -- should show one row
SELECT * FROM chartest WHERE charval1=varcharval;
SELECT charval1 || 'X' AS res FROM chartest;
SELECT CHAR_LENGTH(charval1 || charval2) AS res FROM chartest;
SELECT CHAR_LENGTH(charval1) + CHAR_LENGTH(charval2)
  AS res
  FROM chartest;

Expected results:

SELECT * FROM chartest; -- should show two rows
========== ========== ==============================
aaa        aaa        aaa
aaaaaa     aaa        aaa

DELETE FROM chartest WHERE charval1='aaaaaa';
SELECT * FROM chartest; -- should show one row
========== ========== ==============================
aaa        aaa        aaa
SELECT * FROM chartest WHERE charval1=varcharval;
========== ========== ==============================
aaa        aaa        aaa 
SELECT charval1 || 'X' FROM chartest AS res;
aaa       X
SELECT CHAR_LENGTH(charval1 || charval2) AS res FROM chartest;
SELECT character_length(charval1) + character_length(charval2)
AS res
FROM chartest;

Actual Results⌘


  • Return with an exception state if the inserted string is too long, unless the characters exceeding the limit are all spaces.
  • Pad CHAR columns with spaces if the inserted string is shorter than the specified CHAR-length.
  • Pad with trailing spaces as needed when casting or comparing to other string-like values (e.g. VARCHARs).


Generally follows standard, but (conceptually) truncates trailing white-space before performing some functions (at least before LEN()).


Breaks the standard by silently inserting the string, truncated to specified column CHAR-length.
(It's actually not completely silent, as it issues warnings if values were truncated: If you manually check for warnings, you will know that something bad happened, but not which of the rows are now invalid.)

Violates the standard by effectively truncating all trailing spaces.
The documentation states that MySQL truncates trailing spaces when CHAR values are retrieved. That may be true, but it seems that truncation even happens before the CHAR values are used as input in functions like CONCAT,CHAR_LENGTH, etc.Documentation



Part of the Core requirements, feature ID F051-03.
Stores year, month, day, hour, minute, second (with fractional seconds; default is 6 fractional digits).
Extension to Core SQL (feature ID F411): TIMESTAMP WITH TIME ZONE which also stores the time zone.Examples of TIMESTAMP literals:

  • TIMESTAMP '2003-07-29 13:19:30'
  • TIMESTAMP '2003-07-29 13:19:30.5'Examples of TIMESTAMP WITH TIME ZONE literals:
  • TIMESTAMP '2003-07-29 13:19:30+02:00'
  • TIMESTAMP '2003-07-29 13:19:30.5+02:00'It's strange that TIMESTAMP WITH TIME ZONE literals are not represented as, e.g.,TIMESTAMP WITH TIME ZONE '2003-07-29 13:19:30+01:00', but according to Melton & Simon's book, they aren't.


Note that MSSQL's choice of words related to date and time is confusing: In MSSQL's vocabulary, datetime is a concrete data type, whereas in the SQL standard, datetime is a general term covering the DATE, TIME and TIMESTAMP types.MSSQL has a strange pseudo-type called TIMESTAMP, but has deprecated it; don't use it in new code.The closest match to the SQL standard's TIMESTAMP type is DATETIME. This type stores the combination of date and time. It has a maximum of three fractional digits for seconds.Performs good sanity checks on inserted timestamp values; e.g. this will work:
INSERT INTO tablename (columnname)
VALUES ('2003-02-28 00:05:00')
while this will fail:
INSERT INTO tablename (columnname)
VALUES ('2003-02-29 00:05:00')


No matter what date/time data type chosen in MySQL, storage of fractional seconds and time zones are not supported (the TIME type accepts time literals with fractional seconds, but discards the fractional part when storing the value). You will have to invent your own systems for such information.
Note also, that MySQL's choice of words related to date and time is confusing: In MySQL's vocabulary, datetime is a concrete data type, whereas in the SQL standard, datetime is a general term covering the DATE, TIME and TIMESTAMP types.MySQL has a type called TIMESTAMP, but it is quite different from the standard TIMESTAMP: It's a 'magic' data type with side effects in that it's automatically updated to the current date and time if some criteria are fulfilled.MySQL has a type called DATETIME. Like MySQL's TIMESTAMP type, it stores a combination of date and time without fractional seconds. There are no side effects associated with the DATETIME type�which makes it the closest match to the SQL standard's TIMESTAMP type.By default, MySQL's sanity checks with regard to dates and time are (deliberately) poor. For example, MySQL accepts DATETIME values of '2003-02-29 00:05:00' and '2003-01-32 00:00:00'. Such values yield warnings (which you must check for if you want to be warned), but result in a value of zero being stored.

SQL Functions⌘

Character Length


If the optional feature T061 is implemented, the function may be augmented with an indication ofstring unit:
CHARACTER_LENGTH(argument USING string-unit)
string-unit may be UTF8, UTF16, UTF32.Returns NUMERIC. Returns NULL if the input is NULL.
The argument may be of type CHAR or VARCHAR.
Part of the Core SQL requirements (feature ID E021-04).
Related function: OCTET_LENGTH.


Doesn't have CHARACTER_LENGTH. Provides the LEN and DATALENGTH functions instead (the latter is especially valid for 'special' data types like the TEXT type).
Note that MSSQL's LEN-function removes trailing (not leading) spaces from CHAR values before counting; MSSQL's DATALENGTH doesn't discard spaces.


Note that MySQL removes trailing (not leading) spaces from CHAR values before counting.



The standard defines two variants of the SUBSTRING function:1. To comply with Core SQL (Feature E021-06), the DBMS must support an 'ordinary' SUBSTRING function which extracts characters from a string:
SUBSTRING(input FROM start-position [FOR length])
Strings start at position 1. The start-position argument is a numeric value, as is the optional length-argument. If no length parameter is indicated, length becomes infinite(The standard specifies an extra optional argument�USING x�that has to do with Universal Character Sets, e.g. Unicode. x may be one of OCTETS or CHARACTERS.)The result is NULL if any of the arguments is NULL.Some cases of out-of-range values for start-position and length are allowed. Examples:o SUBSTRING('12345' FROM 6) yields the empty string.o A start-position less than 1 effectively sets start-position to 1 and reduces the value of length by 1+abs('start-position').
I.e., if start-position is -3 and length is 6, then the length value becomes 2.

Another way to put it is that when start-position is negative, a bunch of arbitrary/blank characters are prepended to the input-value. bunch=1-'start-position.For an exact definition: see item three in the "General Rules" part of section 6.29 in the standard.2. The DBMS may optionally offer a regular expression variant (Feature T581) of SUBSTRING:
SUBSTRING(input SIMILAR pattern ESCAPE escape-char)
Pattern deserves some explanation. It's a string which needs to consist of three parts: A part matching before the wanted sub-string, the wanted substring, and a part matching afterthe wanted substring.
The parts must be separated by a combination of the indicated escape-char (escape-character) and a double-quote ("). Example:
SUBSTRING('abc' SIMILAR 'a#"b#"c' ESCAPE '#')
should yield
The pattern description rules in SQL don't completely resemble POSIX regular expressions, as far as I can see.


MSSQL has a SUBSTRING function, but its syntax differs from that of the standard. The syntax is:SUBSTRING(input, start, length)where start is an integer specifying the beginning of the string, and length is a non-negative integer indicating how many characters to return.MSSQL has no regular expression functionality.


MySQL supports the standard's ordinary SUBSTRING function, with some twists (see below). No regular expression based substring extraction is supported.

MySQL breaks the standard when negative values are used as either start-position or length:

  • According to the standard, SUBSTRING('abc' FROM -2 FOR 4) should yield 'a';in MySQL, the result is 'bc'.
  • According to the standard, SUBSTRING('abc' FROM 2 FOR -4) should yield an error; MySQL returns an empty string.



Not mentioned. May be obtained through a combination of other functions (have a look at the OVERLAY, POSITION and CHARACTER_LENGTH functions).

A de facto standard seems to have emerged with regard to REPLACE:

REPLACE (haystack:string,needle:string,replacement:string)

which means 'replace needle with replacement in the string haystack'. Replacement is done case-sensitively unless otherwise stated.

The REPLACE function may be handy for correcting spelling errors (and other situations):
UPDATE tablename
SET fullname=REPLACE(fullname,'Jeo ','Joe ')


Follows de facto standard with the exception that MSSQL by default works case insensitively.


Follows de facto standard.
MySQL even works case sensitively.1
Note that the REPLACE-function is different from MySQL's non-standard REPLACE INTO expression.



Core SQL feature ID E021-09: TRIM(where characters FROM string_to_be_trimmed)where may be one of LEADING, TRAILING or BOTH�or omitted which implies BOTH.characters indicates what character(s) to remove from the head and/or tail of the string. It may be omitted which implies the value ' ' (space character).In other words, the shortest form is TRIM(string_to_be_trimmed) which in effect meansTRIM(BOTH ' ' FROM string_to_be_trimmed).Trimming NULL returns NULL.


Doesn't support the standard TRIM function.Provides
RTRIM(string_to_be_trimmed)Documentation: LTRIM and RTRIM


Follows the standard.


It's often important to get the value of current date and time. Below are the functions used to do that in the different implementations.


The current timestamp (without time zone) is retrieved with the LOCALTIMESTAMP function which may be used as:SELECT LOCALTIMESTAMP ...
SELECT LOCALTIMESTAMP(precision) ...Note that "SELECT LOCALTIMESTAMP() ..." is illegal: If you don't care about the precision, then you must not use any parenthesis.If the DBMS supports the non-core time zone features (feature ID F411), then it must also provide the functions CURRENT_TIMESTAMP and CURRENT_TIMESTAMP(precision) which return a value of type TIMESTAMP WITH TIME ZONE. If it doesn't support time zones, then the DBMS must notprovide a CURRENT_TIMESTAMP function.


Doesn't have the LOCALTIMESTAMP function.Instead, it has CURRENT_TIMESTAMP which�however�doesn't return a value of TIMESTAMP WITH TIME ZONE, but rather a value of MSSQL's DATETIME type (which doesn't contain time zone information).


Follows the standard.



Core feature ID E021-07:
Concatenating two strings is done with the || operator:string1 || string2If at least one operand is NULL, then the result is NULL.It's unclear to me if the DBMS is allowed to try to automatically cast the operands to concatenation-compatible types.


Breaks the standard by using the '+' operator instead of '||'.Does not automatically cast operands to compatible types. If an operand is NULL, then the result is NULL.


Badly breaks the standard by redefining || to mean OR.Offers instead a function, CONCAT(string, string), which accepts two or more arguments.Automatically casts values into types which can be concatenated. If an operand is NULL, then the result is NULL.

Constraint handling⌘

The UNIQUE constraint


As the constraint name indicates, a (set of) column(s) with a UNIQUE constraint may only contain unique (combinations of) values.A column�or a set of columns�which is subject to a UNIQUE constraint must also be subject to a not NULL constraint, unless the DBMS implements an optional "NULLs allowed" feature (Feature ID 591). The optional feature adds some additional characteristics to the UNIQUE constraint:1. Columns involved in a UNIQUE constraint may also have NOT NULL constraints, but they do not have to.2. If columns with UNIQUE constraints do not also have NOT NULL constraints, then the columns may contain any number of NULL-'values'. (Logical consequence of the fact that NULL<>NULL.)
In the standard-parlance, the constraint is satisfied, ifthere are no two rows in [the relation] such that the value of each column in one row is non-null and is not distinct from the value of the corresponding column in the other row


Follows the standard�with a twist:MSSQL offers the NULLs allowed feature, but allows at most one instance of a NULL-'value', if NULLs are allowed; i.e. breaks characteristic 2 in the above description of the standard.


Follows the standard, including the optional NULLs allowed feature.

Automatic key generation⌘

It's sometimes handy to have the platform handle generation of keys. The platforms offer various means for this.


The standard specifies a column attribute of:
GENERATED ... AS IDENTITY (non-core feature ID T174+T175).When creating a table, an IDENTITY clause may be declared for certain types of columns (INTEGER being one):CREATE TABLE tablename (
)orCREATE TABLE tablename (
)The column with the IDENTITY attribute will be given values in increasing order, possibly with 'holes' (...,3,4,7,...).A base table may at most contain one column with the IDENTITY attribute. NOT NULL is implied for an IDENTITY column. Normally, a column declared with IDENTITY will also be declared PRIMARY KEY, but it's not implied.The examples differ in their 'ALWAYS' vs. 'BY DEFAULT' clauses:

  • When ALWAYS is specified, the user cannot specify a value for the column which means that the DBMS can guarantee successful insertion of a unique value on each table insert.
  • When BY DEFAULT is specified, the user may manually specify what value to put in the identity field of a row. The flip side is that the DBMS cannot guarantee that this will work.The standard specifies several extended options which may be declared for a generated IDENTITY column.


MSSQL offers IDENTITY as a column property, but with a different syntax than the standard's specification. An example of creating a table with an IDENTITY column:CREATE TABLE tablename (
)With MSSQL's IDENTITY attribute, the user cannot manually insert the value, unless the user has first run SET IDENTITY_INSERT tablename ON
MSSQL refuses to update values in IDENTITY columns.I.e., MSSQL's IDENTITY type is closest to the standard's GENERATED ... ALWAYS AS IDENTITYvariant.


MySQL doesn't support the standard's IDENTITY attribute.As an alternative, an integer column may be assigned the non-standard AUTO_INCREMENTattribute:CREATE TABLE tablename (
)Columns with the AUTO_INCREMENT attribute will�under certain conditions�automatically be assigned a value of <largest value in column>+<at least 1>. Look in MySQL's documentation for the (rather extensive) details.A table can have at most one column with the AUTO_INCREMENT attribute; that column must be indexed (it doesn't have to be a primary key, as in the example SQL above) and cannot have a DEFAULT value attribute.It's probably not too far fetched to think of MySQL's AUTO_INCREMENT feature as this equivalence:
CREATE TABLE tablename (

Standard SQL:
CREATE TABLE tablename (
columnname INTEGER DEFAULT some_func() PRIMARY KEY
where some_func() is a function which finds 1 plus the currently largest value of columnname.The nice thing about this approach is that the automatic value insertion should never fail, even though some of the column's values might have been manually set�i.e. the combined advantages of the standard's ALWAYS and BY DEFAULT variants.The drawback is that it might result in more house-keeping: The system may need extra table locks when performing row updates/insertions to protect against ghost updates in concurrent transactions�thus slowing down the system in case of many concurrent updates/insertions.


It's sometimes useful to be able to remove all rows from a large table in a quick way. And often, DELETE isn't as quick as we want. So several DBMSes implement a TRUNCATE operation. Typically, truncating means that deletion isn't associated with triggers which may exist for the table, and typically, truncating involves little (if any) transaction log activity.


The SQL standard defines the TRUNCATE TABLE tablename statement (optional feature ID F200, new in SQL:2008) as:
Delete all rows of a base table without causing any triggered action.Unfortunately, the standard doesn't specify1. whether TRUNCATE TABLE should be allowed in a transaction involving other statements, or not2. whether TRUNCATE TABLE should imply an immediate COMMIT, or not


Follows the standard.In MSSQL, TRUNCATE TABLE is allowed in a transaction involving other operations, andTRUNCATE TABLE does not imply an immediate COMMIT operation.You need to have at least ALTER-permission on the table to be truncated.


MySQL has a TRUNCATE TABLE statement, but it doesn't always follow the standard.Note that in some cases, MySQL's truncate command is really the equivalent of an unrestricted DELETE command (i.e.: potentially slow and trigger-invoking). Its behaviour depends on whichstorage engine the table is managed by.When using InnoDB (transaction safe) tables, TRUNCATE TABLE is allowed in a transactioninvolving other operations, however TRUNCATE TABLE implies an immediate COMMIT operation.

Command line procedures / metadata⌘

The following are not necessarily SQL operations, but rather a description of how different operations are performed in the command line interface provided by each product.

  • some people hate using CLI
  • some people can't imagine not using it for everything

Starting the command line interface⌘


Not defined.


The command line interface is started by running
sqlcmdsqlcmd is not nice to work with. It's bad at formatting result sets. It doesn't have command line completion. You have to say go after your commands. A positive thing about sqlsmd: It has command history, so you may press arrow-up for previous commands in the current sqlsmd session.In MSSQL 2000, the command line interface was started by running osql.An alternative to osql�apart from HenPlus, mentioned above�is SQSH which should work on any modern open source operating system, except it doesn't seem to support Kerberos, so you need to log into the database using a database-account (not a Windows-account).


mysqlIf you need help on the optional command line options, see the man page.On platforms like Linux and FreeBSD (which have decent readline-capabilities), MySQL's command line interface is simply great; not much else to say. MySQL's command line interface is said to be rather poor on Windows, though.

Databases vs. Schemas⌘

The SQL specification makes clear what an "SQL schema" is; however, different databases implement it incorrectly. To compound this confusion the functionality can, when incorrectly implemented, overlap with that of the parent-database. An SQL schema is simply a namespace within a database, things within this namespace are addressed using the member operator dot ".". This seems to be a universal amongst all of the implementations.

Who cares?⌘

  • example problem: former MySQL users may create multiple databases for one project. In this context, MySQL databases are analogous in function to Postgres-schemas, insomuch as Postgres lacks off-the-shelf cross-database functionality that MySQL has. Conversely, PostgreSQL has applied more of the specification implementing cross-table, cross-schema, and then left room for future cross-database functionality.
  • MySQL aliases schema with database behind the scenes, such that CREATE SCHEMA and CREATE DATABASE are analogs. It can therefore be said that MySQL has implemented cross-database functionality, skipped schema functionality entirely, and provided similar functionality into their implementation of a database. In summary, Postgres fully supports schemas but lacks some functionality MySQL has with databases, while MySQL does not even attempt to support true schemas

Getting a list of databases⌘


Not specified. (FYI: The SQL standard doesn't have the concept of adatabase as a container of schemas; instead, the standard specifies that schemas are contained in a catalog.)





Getting a list of schemas⌘




Follows the standard.


MySQL doesn't support schemas. (but there's a hack in the mysql DB)

Getting a list of tables⌘


Part 11 of the SQL standard specifies the INFORMATION_SCHEMA schema which must be part of all database catalogues. The schema may be used like this:SELECT * FROM INFORMATION_SCHEMA.TABLES
AND TABLE_SCHEMA='SCHEMA-NAME'See a warning about potential case sensitivity problems below.


Follows that standard.
Sometimes, the SP_TABLES system stored procedure is easier to use.


Follows the standard, except that MySQL doesn't support schemas, so one might say that MySQL's INFORMATION_SCHEMA is really an 'INFORMATION_DATABASE or 'INFORMATION_CATALOGUE.In command-line context, it's easier to use the following non-standard SQL:

Getting a table description⌘


Part 11 of the SQL standard specifies the INFORMATION_SCHEMA schema which must be part of all database catalogues. The schema may be used like this:
SELECT column_name,data_type,column_default,is_nullable
information_schema.tables AS t
information_schema.columns AS c ON
t.table_catalog=c.table_catalog AND
t.table_schema=c.table_schema AND
t.table_name='TABLE-NAME'�or like this (more verbose):SELECT
information_schema.tables as t
information_schema.columns AS c ON
t.table_catalog=c.table_catalog AND
t.table_schema=c.table_schema AND
c.table_name='TABLE-NAME'To get information about constraints, involved columns and (possibly) referenced columns, a query like this may be used:
If you don't care about potential namespace conflicts, you may leave out the lines commented with "-- see remark".


Follows the standard, except that

  • MSSQL uses non-standard names for some standard datatypes, i.e. varchar instead of the standard's CHARACTER_VARYING
  • MSSQL's INFORMATION_SCHEMA doesn't have all SQL:2008's columns (an example: MSSQL's INFORMATION_SCHEMA.COLUMNS view does not contain the IS_IDENTITY column)Often, the SP_HELP tablename system stored procedure is easier to use.


Follows the standard, except that

  • MySQL doesn't support schemas, so one might say that MySQL's INFORMATION_SCHEMA is really an 'INFORMATION_DATABASE or 'INFORMATION_CATALOGUE.
  • MySQL's INFORMATION_SCHEMA doesn't have all SQL:2008's columns (an example: MySQL's INFORMATION_SCHEMA.COLUMNS view does not contain the IS_IDENTITY column).
  • As MySQL's namespaces don't match the SQL standard fully, the standard queries mentioned above will not work. The reason is that in MySQL, the value of TABLE_CATALOG isNULL for all tables and columns. To obtain the wanted information, you need to remove the table_catalog join-conditions. I.e., the first (and simplest) of the above queries must be re-written to:SELECT column_name,data_type,column_default,is_nullable
    information_schema.tables AS t
    information_schema.columns AS c ON
    t.table_schema=c.table_schema AND
    t.table_name='TABLE-NAME'In command-line context it's easier to use this non-SQL command:
    DESCRIBE tablename

Of course, there's always

  • describe
  • show create

Getting a query explanation⌘


Not standardized.


MSSQL can be put in a query explanation mode where queries are not actually executed, but a query explanation is returned instead:
SET SHOWPLAN_TEXT ONThe query explanation mode is turned off by running


EXPLAIN <query>

Turning on query timing⌘


Not standardized.




MySQL's command line interface prints query times by default.



Microsoft's driver: sqljdbc.jar - read more
Alternative: The open source JTDS driver: jtds - read more


The MySQL Connector/J driver: mysql-connector-java-[version]-bin.jar

Dummy table⌘

Some DBMSes let you perform a query like this:




With other DBMSes, you need to insert a dummy-table expression to obtain the same result:

SELECT 1+1 FROM dummy-table


No need for dummy-table.


No need for dummy-table, although MySQL allows you to refer to a DUAL dummy-table (for Oracle compatibility).

Getting Versions⌘




MSSQL's implementation of the IMPLEMENTATION_SCHEMA doesn't seem to include the SQL_IMPLEMENTATION_INFO view. In stead, you may use
(just the version), or
(verbose, harder to parse).


MySQL's INFORMATION_SCHEMA doesn't include the SQL_IMPLEMENTATION_INFO view.Work-around: