krypted.com

Tiny Deathstars of Foulness

Slow queries can cause poor database performance. MySQL has a slow query log, which lets you log queries that take too long (with too long a user-configurable setting). This allows you to quickly find craptastic queries in your logs. To enable the slow query log, use SET GLOBAL and then set the slow_query_log option to ON:

SET GLOBAL slow_query_log = 'ON';

By default, the slow query log identifies any query that takes 10 seconds or longer. To change long_query_time using SET GLOBAL. Here, we’ll set it to 7 seconds:

SET GLOBAL long_query_time = 7;

You can also set the log file to something other than the default location of /var/lib/mysql/hostname-slow.log. Again, use SET GLOBAL and then provide a path for slow_query_log_file:

SET GLOBAL slow_query_log_file = '/var/logs/slowquery.log';

To disable the slow query log:

SET GLOBAL slow_query_log = 'OFF';

March 19th, 2016

Posted In: SQL

Tags: , , , ,

You can grant access to certain columns to view in SQL without providing access to specific users to see the whole database. This is pretty useful when delegating reporting to users, without giving them access to all of the data in your database. For example, a user might be able to see a column with an address, but not a column with a credit card number, increasing database security while allowing you to delegate certain tasks when appropriate.

In this article, we’ll use the same “Customers” table from our first articles, signupdate:

ID Site Contact Address City Zip Country SignupDate
1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01
2 Apple Tim Cook spaceship Cupertino 95014 US 2015-12-05
3 Microsoft Satya Nadella campus Redmond 98053 US 2014-11-01
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 2010-03-10
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 2016-01-01

Next, we’ll create a view called SignupDate that only has customers that signed up on January 1st of 2005. This view returns the data set of contacts and signup dates:

CREATE VIEW signupdate AS SELECT * FROM Customers WHERE OrderDate='2005-01-01';

The syntax is similar to a SELECT, but with CREATE VIEW followed by the name of the view and then AS followed by the SELECT statement. The view is a virtual table containing the output of the query rather than data. Once created, use use the signupdate view in a query:

SELECT * FROM signupdate;

This SQL statement returns the following results:

1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01

You can also use the view to contain a query with just the columns you want, according to how you structure your query, thus granting access to specific columns, without granting access to all of the columns in a table.

March 14th, 2016

Posted In: SQL

Tags: , , , , , ,

If you have growing sets of data, one of the best ways to speed up database performance is to make sure each column in larger tables is indexed. You can easily index a column following this syntax (using the name of your table in the place of tablename and the name of your column in the place of columnname):

ALTER TABLE tablename ADD INDEX (columnname);

So if you have a table called Customers and the following columns (as in the case of my Customers database from the previous exercises):

ID Site Contact Address City Zip Country

You would index them all as follows:

ALTER TABLE Customers ADD INDEX (ID);
ALTER TABLE Customers ADD INDEX (Site);
ALTER TABLE Customers ADD INDEX (Contact);
ALTER TABLE Customers ADD INDEX (Address);
ALTER TABLE Customers ADD INDEX (City);
ALTER TABLE Customers ADD INDEX (Zip);
ALTER TABLE Customers ADD INDEX (Country);

March 8th, 2016

Posted In: SQL

Tags: , , , , ,

SQL has some really great features for managing dates. Given that dates are a very common thing to store in databases, that’s a pretty logical thing to get to a good, mature point. But the most challenging aspect of working with date and time in SQL is formatting. There are lots of different ways to format dates around the world, and even different structures (e.g. epoch) that are often complicated by adding time to records. But, you need to do things in a consistent fashion with how SQL expects them, if you’ll be using built in functions to manage dates. Dates can be stored in a variety of formats, which include the following:

  • DATE: Appears as YYYY-MM-DD
  • DATETIME: Appears as YYYY-MM-DD HH:MI:SS
  • TIMESTAMP: Appears as YYYY-MM-DD HH:MI:SS
  • YEAR: Appears as YY or YYYY

What are those functions?

  • CONVERT: Shows the date/time data in the specified format
  • CURATE: Shows the current date
  • CURTIME: Shows the current time
  • DATE: Extracts the date from a date/time field
  • DATE_ADD: Adds the time to a date
  • DATE_SUB: Removes the time from a date
  • DATE_FORMAT: Shows the date and time data in different formats
  • DATEDIFF: Shows the days between two specified dates
  • EXTRACT: Shows part of a date and time formatted value
  • NOW: Shows the current date and time, frequently used to capture the date during signups, etc

In this article, we’ll use the same “Customers” table from our first articles, but we’ll add a column for signupdate:

ID Site Contact Address City Zip Country SignupDate
1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01
2 Apple Tim Cook spaceship Cupertino 95014 US 2015-12-05
3 Microsoft Satya Nadella campus Redmond 98053 US 2014-11-01
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 2010-03-10
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 2016-01-01

Let’s show (SELECT) records with an SignupDate of 2005-01-01 from the table above.

SELECT * FROM Customers WHERE OrderDate='2005-01-01'

Overall, dates are pretty easy. Times are a little more challenging, and take up much more space in a database. But overall, provided you build the column with the right format you want to use, the functions make managing date tasks all the easier. I’m not going to cover all of the options, but let’s look at EXTRACT real quick. Here, we’ll serialize the year, month, and day from the previous select. To do so, we’ll use the SELECT statement, call the EXTRACT function, and then split the three parts of the date into variables we’re making up (so you can use your own strings for the names), as SignupYear, SignupMonth, and SignupDay:

SELECT EXTRACT(YEAR FROM SignupDate) AS SignupYear,
EXTRACT(MONTH FROM SignupDate) AS SignupMonth,
EXTRACT(DAY FROM SignupDate) AS SignupDay,
FROM Customers
WHERE OrderDate='2005-01-01'

Obviously, we knew the OrderDate, but we could have used any other column and pattern to match for that column in the WHERE to serialize date information based on other parameters. Fun stuff.

March 6th, 2016

Posted In: SQL

Tags: , , , , , , ,

Flyway is a tool that allows you to perform version-controlled management of SQL schemas. You can download flyway at http://flywaydb.org. It doesn’t require a build, so once downloaded, drop it where you want it to live and you can just summon the binary in scripts. Once installed, the following is the basic structure of commands for Flyway:

flyway [options] command

By default, the configuration will be read from conf/flyway.conf.
Options passed from the command-line override the configuration.

Commands
========
migrate : Migrates the database
clean : Drops all objects in the configured schemas
info : Prints the information about applied, current and pending migrations
validate : Validates the applied migrations against the ones on the classpath
baseline : Baselines an existing database at the baselineVersion
repair : Repairs the metadata table

Options (Format: -key=value)
=======
driver : Fully qualified classname of the jdbc driver
url : Jdbc url to use to connect to the database
user : User to use to connect to the database
password : Password to use to connect to the database
schemas : Comma-separated list of the schemas managed by Flyway
table : Name of Flyway’s metadata table
locations : Classpath locations to scan recursively for migrations
resolvers : Comma-separated list of custom MigrationResolvers
sqlMigrationPrefix : File name prefix for Sql migrations
sqlMigrationSeparator : File name separator for Sql migrations
sqlMigrationSuffix : File name suffix for Sql migrations
encoding : Encoding of Sql migrations
placeholderReplacement : Whether placeholders should be replaced
placeholders : Placeholders to replace in Sql migrations
placeholderPrefix : Prefix of every placeholder
placeholderSuffix : Suffix of every placeholder
target : Target version up to which Flyway should use migrations
outOfOrder : Allows migrations to be run “out of order”
callbacks : Comma-separated list of FlywayCallback classes
validateOnMigrate : Validate when running migrate
cleanOnValidationError : Automatically clean on a validation error
baselineVersion : Version to tag schema with when executing baseline
baselineDescription : Description to tag schema with when executing baseline
baselineOnMigrate : Baseline on migrate against uninitialized non-empty schema
configFile : Config file to use (default: conf/flyway.properties)
configFileEncoding : Encoding of the config file (default: UTF-8)
jarDirs : Dirs for Jdbc drivers & Java migrations (default: jars)

Add -X to print debug output
Add -q to suppress all output, except for errors and warnings

Example
=======
flyway -target=1.5 -placeholders.user=my_user info

More info at http://flywaydb.org/documentation/commandline

March 5th, 2016

Posted In: SQL

Tags: , , ,

The following are options you can use when calling the mysql command:
–auto-rehash Enable or disable automatic rehashing
–auto-vertical-output Automatically display output of commands vertically rather than horizontally
–batch Do not write to the history file
–binary I use this when I have large results timing out due to blobs being in there
–bind-address Specify a network interface when connecting to the server
–character-sets-dir Indicate a custom directory where character sets are installed
–column-names Output column names when running queries
–column-type-info Show metadata
–comments Enable or disable commenting when sending statements to the server
–compress Compresses data sent to/from client and server
–connect_timeout Seconds of inactivity before a timeout
–database Defines the database you are connecting to
–debug Writes a debugging log
–debug-check Shows debugging information
–debug-info Shows memory and CPU information
–default-auth Authentication plugin to use
–default-character-set Default character set
–defaults-extra-file Read in options from a second config file
–defaults-file Read in options from a single config file
–delimiter Define a delimiter
–enable-cleartext-plugin Allow cleartext authentication
–execute Run a given statement and then stop mysql
–force Keep going, even in the event of an error
–help Show help information
–histignore Define some statements that will not get logged
–host Define a MySQL server to connect to
–html Show results in HTML
–ignore-spaces Ignore spaces after functions
–init-command Run listed statements when running a command
–line-numbers Use line numbers with errors
–login-path Use any login path options defined in a .cnf file
–max_allowed_packet Maximum packet length to use with a server
–max_join_size Limit joins with –safe-updates
–named-commands Allow named mysql commands
–net_buffer_length Buffer TCP/IP sockets
–no-auto-rehash Turn automatic rehashing off
–no-beep Don’t beep on errors
–no-defaults Don’t use any config files when loading
–one-database Only allow statements run on the specified database
–pager Show output for a statement one page at a time
–password The password to use when connecting to a server
–pipe Connect to servers via named pipes (any time you see named pipes it means Windows)
–plugin-dir Location (URI) of plugin files
–port TCP/IP port number
–print-defaults Show defaults
–prompt Prompt for a format
–protocol Defines the protocol to use to connect to the server
–quick Don’t cache every statement
–raw Show columns without escaping
–reconnect Reconnect if a connection is lost
–safe-updates Only process UPDATE and DELETE statements when they have values
–secure-auth Send passwords securely
–select_limit The limit for SELECT statements with –safe-updates
–server-public-key-path Path to a file with a public key
–shared-memory-base-name The name used for shared-memory connections
–show-warnings Display warnings per statement
–sigint-ignore Don’t allow SIGINT signals to stop the daemon
–silent Run silently (necessary when daemonizing)
–skip-auto-rehash Turn off automated rehashing
–skip-column-names Don’t use column names as headers
–skip-line-numbers Don’t use line numbers on errors
–skip-named-commands Turns off named commands
–skip-pager Turn off paging
–skip-reconnect Don’t reconnect
–socket Define a socket file for localhost connections
–ssl Enable SSL
–ssl-ca File with a list of trusted CAs
–ssl-capath Directory with trusted CA certificates in PEM
–ssl-cert Directory with X509 certificates in PEM
–ssl-cipher Allowed ciphers
–ssl-crl File used to define CRLs (certificate revocation lists)
–ssl-crlpath Path of directory that contains certificate revocation list files
–ssl-key Path to file with an X509 key formatted as PEM
–ssl-mode Display the security mode on a connection to a server
–ssl-verify-server-cert Verify Common Name (CN) and actual hostname when establishing a connection
–syslog Logs out to syslog
–table Tabular (and maybe tubular while we’re at it) format
–tee Output to a file
–tls-version Protocols used to secure connections
–unbuffered Constantly flush buffers
–user The user name to use when connecting to server (SQL users are not the same as users on an OS)
–verbose As with most other commands, output verbosely
–version Show the MySQL version number
–vertical Show one line per column in query outputs
–wait Retry when a connection fails
–xml Output data into an XML format

March 4th, 2016

Posted In: SQL

Installing MySQL on Linux is pretty easy. You can use yum (or your favorite package manager for most installs. Here, we’ll pull a list of packages from yum using repolist:

yum repolist enabled | grep "mysql.*-community.*"

You’ll then get a list of community edition MySQL packages that are available. Then let’s say you’re installing on RHEL 6, we’ll pull a string from the repolist of an appropriate package and then do a localinstall of it:

sudo yum localinstall mysql57-community-release-el6-157.noarch.rpm

We could also grab mysql and all the other stuffs we want to have with it:

yum install mysql mysql-server mysql-libs mysql-server

And then start it up:

service mysql start

March 3rd, 2016

Posted In: SQL

Tags: , ,

SQL has a number of built-in functions that can be used to find common query results, such as averaging data, summing up a column of data, rounding information off, formatting data, etc. SQL also has a number of options for building your own custom functions (and triggering them to run). I usually like to use functions when I’m looking for data and reporting. I don’t like using them in code, as the language I’m performing a task in is typically better suited to manage data than is SQL, comparably.

SQL functions come in three types. Aggregate, Scalar, and Custom. Aggregate functions (those that take multiple objects as an input):

  • AVG() – Show the average value in a defined set of data
  • COUNT() – Output the number of rows that match a pattern
  • FIRST() – Output the first value in a set of objects
  • LAST() – Returns the last value in a set of objects
  • MAX() – Show the largest value given a set of objects
  • MIN() – Output the smallest value of a set of objects
  • SUM() – Output the sum defined objects

Scalar functions, which return a value (such as a number or new text):

  • FORMAT() – Formats data in a field
  • LEN() – Shows the length of text
  • LCASE() – Converts the specified data into lower case
  • MID() – Pulls specified characters from text
  • NOW() – Shows the system date and time
  • ROUND() – Rounds a decimal number to the decimals indicated
  • UCASE() – Converts specified data into upper case

Now let’s look at actually using a SQL function. We’ve covered running a SELECT Statement in SQL. But what if you want to count the number of objects returned with that statement. You could dump the results into a csv and view them in Excel. Or, you could save yourself a little time by using the SQL COUNT() function, which shows the number of rows that match a given criteria. In its most basic form, the COUNT function is used as follows (where column is a column in a database and table is the table the column is in:

SELECT COUNT(column) FROM table;

To put this in action, you can look at all records in the Customers table using the following:

SELECT COUNT(*) FROM Customers;

In this article, we’ll use the same “Customers” table from our first articles to add and edit columns:

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

So the output of the first COUNT would be 5. To constrain, you can use WHERE  following the initial COUNT line. For example, the following SQL statement counts the number of customers from “City”=Minneapolis from the “Customers” table:

SELECT COUNT(City) AS InMinneapolis FROM Customers
WHERE City="Minneapolis";

You can also use the DISTINCT option to look at how many unique customer cities we have:

SELECT COUNT(DISTINCT City) AS CitieswithCustomers FROM Customers;

February 29th, 2016

Posted In: SQL

Tags: , , , ,

By default, MySQL allows other services on the computer you’re running the daemon to connect to the database and denies any connections from hosts outside that computer. However, it’s pretty easy to provide access to the database from another host (for example, if you’re splitting up the back-end and front-end of a site, clustering, etc. To get started, you’ll edit your my.cnf file and find the [mysqld] section of the file.

Then, locate the bind-address, which you will need to set as the IP of your server and comment out the line for skip-networking. Let’s say we’re going to open access for 192.168.2.2. The section would look similar to the following:

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 192.168.2.2
# skip-networking

Then restart MySQL and a listener should be running on the system. You can connect using the mysql command, with the -u option to define a user (root) and then the -h to define an IP (in this case 192.168.2.2):

mysql -u root –h 192.168.2.2 –p

You can also use the telnet command to attempt a connection into a given port, which in the MySQL case would be 3306:

telnet 192.168.2.2 3306

Now, just because you can connect remotely doesnt necessarily mean that another computer can actually get into any databases. Next, we’ll GRANT access to ALL resources for a user called krypted for all tables on a new database, coming from an IP of 192.168.2.3:

GRANT ALL ON *.* TO krypted@'192.168.2.3' IDENTIFIED BY 'mysupersecretpassword';

Or for an existing database called mydatabase, using the same IP and account as before:

UPDATE DB set Host='192.168.2.3' where Db='mydatabase';
UPDATE USER set Host='192.168.2.3' where user='krypted';

You’ll also need to open up port 3306 coming in, whether that’s using a firewall or opening traffic in from the internets. Once that’s done, you should be able to connect and use the database as needed.

February 25th, 2016

Posted In: SQL

Tags: , , , , ,

No, SQL top doesn’t look at the status of a database the way the TOP command in bash does. It looks at the top number of records returned in a query, so a bit more like head. This makes SELECT TOP useful with larger tables where this will be millions of records getting loaded into memory during a query.

In this article, we’ll use the same “Customers” table from our first articles to test out TOP:

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

The following SELECT TOP statement shows the top 20% of the records from “Customers”:

SELECT TOP 20 PERCENT * FROM Customers;

Or just SELECT TOP the first three records:

SELECT TOP 3 * FROM Customers;

 

February 23rd, 2016

Posted In: SQL

Tags: , , , ,

Next Page »