MySQL explain plan cheat sheet

Explanation of explain output:

• select_type: SIMPLE/SUBQUERY/UNION/DERIVED
• partitions: NULL ??
• type: (from worst to the best): ALL, index, range, ref, eq_ref, const, system
• possible_keys: food,bar,baz,id (good)
• key: foo(good)
• key_len: 334 ??
• ref: const,const,const ??
• rows: 1 (lower is better. must be less than total rows)
• filtered: 100.00 (lower is better, but only used if there's a join)
• Extra: NULL

Bad extra values:

• using where
• using temporary
• using filesort

Good extra values:

• using index

Quick guide: How to set up MySQL and PostgreSQL databases for Perl development

# The following means mysql is not running:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

MySQL

INSTALL

sudo apt install -y libmysqlclient-dev mysql-client mysql-server

START

$ sudo service mysql start

SETUP

CREATE USER zaphod;

ALTER USER 'zaphod'@'%' IDENTIFIED BY 'some_pass';

GRANT ALL PRIVILEGES ON *.* TO 'zaphod'@'%'; -- makes zaphod an admin

FLUSH PRIVILEGES;

ACCESS

$ mysql -u zaphod -D some_db -p

ADMIN ACCESS

$ sudo mysql

PostgreSQL

INSTALL

sudo apt install -y postgresql libpq-dev

START

$ sudo service postgresql start

SETUP

create user zaphod;

\password zaphod

create database some_db;

grant all privileges on database some_db to zaphod;

ACCESS

$ psql -U zaphod -h localhost -d some_db

# specifying the host forces md5 (password) authentication. Otherwise default is "peer"

ADMIN ACCESS

$ sudo su postgres

$ psql

When Postgres allows any password

Problem: Postgres does not check my password, i.e. it accepts all passwords.

Solution: pg_hba.conf and change trust to md5. Restart postgres. That is all.

(source)

Mac clients for AWS RedShift

What is the best SQL client on Mac for working with Redshift?

JetBrains just released DataGrip ( JetBrains DataGrip: Your Swiss Army Knife for Databases and SQL ). I've been using it full time during the EAP (aka "beta"). It's expensive ($90/yr or $9/mth) but very, very good. Never loses your work. Starts up right where it left off. Dark and light themes. DB specific syntax highlighting. Good stuff.

Before that I was using Navicat Premium Essentials. I got it for ~$20. It's now ~$150 which is too much given it's limited functionality and spotty reliability IMO.

re:dash is overly simple for serious use IMO. Edit: We're actually using this now. It's more of service than an SQL client. It allows casual users (e.g. not full time analysts) to run queries without any setup. Great for centralised dashboards and sharing queries. Try this before you commit to something like Periscope, Looker or Mode.

Portico (was PG Commander) looks interesting but I (personally) need to work with multiple database flavors so it's a non-starter.

SQuirreL and SQL Workbench/J are both pretty crunchy and old feeling Java apps. If you've been on a Mac for a while they will make your eyes bleed.

There is also Toad on the Mac App Store. The screenshots look Mac native but it's pretty crunchy and unpleasant. It feels like going back in time.

Update 2016–09–02: I would suggest that you also look into the “code notebook” applications that are available now, e.g. Jupyter, Zeppelin, and Beaker. You may well find these tools more useful unless you’re a full time DBA. We have replaced Re:Dash with Zeppelin internally.

See also https://www.quora.com/Which-IDE-is-the-best-to-connect-to-AWS-Redshift

On which table should the foreign key be defined?

For simple relational databases the foreign key is usually defined on one table only:

• For join tables (linking tables), put the foreign keys on the join table itself.
• For lookup tables, don't put the foreign key on them, put it on the other (main) table.

Flat file vs database

Flat file

• Easy to set up, only have to consider local file permissions
• Easy to implement in an ad-hoc way, ideal for a prototype
• Plain text for very simple things like a list
• JSON/YAML/Perl for complex data structures
• Doesn't work when the app is load balanced across multiple servers
• Not automatically backed up
• Amazon S3 is relatively expensive
• Have to make your own model

Database

• Requires up-front schema design - more work, but forces you to consider design of data 
• You can put business logic in the ResultSet models
• Requires an instance to be provisioned
• Easy cross-referencing of data
• Works when the app is load balanced across multiple servers
• Backup-as-a-service (i.e. replication)
• Amazon RDS is cheaper than S3
• Get the model for free with ORM

Conclusion

For production services that have redundancy (load balanced), always use a database unless the overhead of setting one up for the first time is considered too high for the business.

Database schema management systems

A list:

• Squitch - ?
• DBIx::Class::Migration - verbose but powerful. Use dbic-migration alongside dbicdump
• DBIx::Migration - not the same as, or as good as DBIx::Class::Migration
• DBIx::Migration::Directories
• DBIx::Patcher (used at NAP)
• perl-Monorail - ?
• App::JESP - ?
• Migraine
• Mojolicious::Command::migration - thin wrapper for dbic-migration
• Database::Migrator
• SQL::Schema::Versioned
• mimi
• gh-ost - massive overkill for small apps

Which data file format?

List:

• CSV
• TSV
• INI
• XML
• YAML
• JSON
• RDB
• custom

Oracle commands for MySQL/PostgreSQL users

Oracle tips for MySQL users:

• Simple range: SELECT * FROM (SELECT * FROM foo) WHERE ROWNUM <= 100; -- equivalent of MySQL's LIMIT clause, only for start of table
• Wrong range: SELECT * FROM (SELECT rownum r, f.bar FROM schema.foo f) WHERE r > 100 AND r <= 200; -- Selects a range, but order will be inconsistent, even if you add ORDER BY to inner select
• Right range:

  SELECT * FROM (
      SELECT q.*, ROWNUM r FROM (
          SELECT * FROM schema.foo ORDER BY id
      ) q
  ) WHERE r >= 100 AND r < 200

  (source)
• SELECT last_name FROM employees WHERE last_name LIKE '%d_g\_cat%' ESCAPE '\';
• matches dog_cat, foodig_catbar, etc.
• _ = any single character
• % = any characters
• \_ = literal _ (underscore)
• ESCAPE '\'; -- set the escape character to \ (backslash)
• SELECT ...... WHERE REGEXP_LIKE (instance_name, '^Ste(v|ph)en$');
• ALTER USER foo IDENTIFIED BY "newpassword456!" REPLACE "oldpassword123!"; -- change password (REPLACE is new in 9.2)

(Oracle docs, basic elements, datatypes, conditions)
(source)

Lock rows with DBIx::Class

$schema->txn_do(sub{

    $foos_rs->search({}, {for => 'update'})->all; # Lock rows

    # Check status of something

    # Update it
});

(source, source, source)

Code works on one environment but not another

What can differ between environments? Check the following:

* Your code (obviously)
* Versions of other dependent packages - both in-house and third-party
* Versions of other installed in-house modules
* Versions of other Perl CPAN modules
* Processes which didn't die when you restarted the app
* Data in the database
* Number of rows in tables, i.e. is some limit being hit?
* Schema of the database
* Browser cache
* Server side web cache
* Files on disk, e.g. cached print documents

(source: a decade's experience building software)

Find and kill slow postgres queries

SELECT query_start,datname,procpid,current_query FROM pg_stat_activity;
SELECT NOW();

SELECT pg_cancel_backend($PID);
-- where $PID is the number from the procpid column in the first query

How to implement a link table in DBIC

# Define many-to-many relationship between foo and bar tables
# using the foos_bars linking table

# in the database:


TABLE foo ( id INTEGER, something TEXT );

TABLE bar ( id INTEGER, another_thing TEXT );
TABLE foo_bar ( foo_id INTEGER, bar_id INTEGER );

# in Result/Foo.pm:

__PACKAGE__->many_to_many(
    'bars', # name of the relationship you're creating in foo
    'foos_bars', # name of the relationship in foo that points to the link table
    'bar' # name of the relationship to bar on the link table
);

# in some nearby code:

@bars = $foo->bars;


CPAN docs

Display postgres enum values

• To see all enums:

select n.nspname as enum_schema,  
    t.typname as enum_name,
    string_agg(e.enumlabel, ', ') as enum_value
from pg_type t 
    join pg_enum e on t.oid = e.enumtypid  
    join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
group by enum_schema, enum_name;

thanks, StackOverflow

• To see one enum:

SELECT enumlabel  FROM pg_enum WHERE enumtypid = 'your_enum_here'::regtype;

Use variables with Postgres, just like MySQL

It's a little more difficult than MySQL, as you have to create a function to contain the logic:

DROP FUNCTION get_column(integer);
CREATE FUNCTION get_column(row_id integer) RETURNS text AS $$
DECLARE
    t1_row foo%ROWTYPE;
BEGIN
    SELECT * INTO t1_row FROM foo WHERE foo.status != 'closed' limit 1;
    RETURN t1_row;
END;
$$ LANGUAGE plpgsql;

SELECT get_column(3);

References: Stackoverflow, Stackoverflow again, the Postgres docs

Find all tables with a particular column

Postgres:

SELECT table_name, column_name FROM information_schema.columns WHERE column_name like '%foo%';

information_schema is documented here:

http://www.postgresql.org/docs/devel/static/information-schema.html

DBI connection code

use DBI;

my $dsn = "dbi:Pg:dbname=xxxxxx";
my $dbh = DBI->connect($dsn, 'postgres', '');
                                                                               
my $statuses = $dbh->selectall_hashref("SELECT id, status, name FROM statuses", "id");

Rollback within a txn_do for DBIC

try {
    $self->schema->txn_do(sub {
        # something went bad
        die 'argh';
    });
}
catch ($e) {
    # log me
    # no roll back needed
}

Complex joins with DBIC in Perl

In a DBIx::Class ResultSet, sometimes you want to return all rows from aaa that have foreign keys to rows in bbb, that have foreign keys to rows in ccc.

This returns rows from ccc which is not what you want:

    return $_->search({
        some_id => { '!=' => undef },
    })
        ->search_related('aaa')
        ->search_related('bbb')
        ->search_related('ccc');


...so rather than doing it in a Perly way which returns an array not a resultset, not to mention many more queries than strictly necessary:

    return grep {
        $_->search_related('aaa')
            ->search_related('bbb')
            ->search_related('ccc')->all;
        } $self->search({
             some_id => { '!=' => undef },
        });


...instead do it the DBIC way, which returns a resultset, and is further chainable, etc.:

    return $self->search({
        'me.some_id' => { '!=' => undef },
        'ccc.some_other_id' => { '!=' => undef },
    }, {
        join => { 'aaa' => { 'bbb' => 'ccc' } }
    });


See docs: DBIx/Class/Manual/Joining.pod#COMPLEX_JOINS_AND_STUFF

View current queries in postgres

Edit config

sudo vim /var/lib/pgsql/9.0/data/postgresql.conf

Add this line:

stats_command_string = true

Restart

pg_ctl reload

Then

SELECT datname,procpid,current_query FROM pg_stat_activity

Thanks