MySQL date display format conversion

MySQL date time conversion functions: 

• UNIX_TIMESTAMP(date) docs
• FROM_UNIXTIME(epoch) docs

Examples:

select name, from_unixtime(time_added, '%Y-%m-%d %h:%i')

from company

order by date_added desc

limit 10; -- list the most recently added companies

You may also omit the '%Y-%m-%d %h:%i' format string, to get the default format YYYY-MM-DD HH:MM:SS, e.g. `2023-09-07 09:43:51`

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

How to use DBIx::Class::Migration

There is a tutorial (note: links are broken), but this is a cheat sheet:

Use case A: Developing - initial setup

Step 0: Set up a database instance

Use dbdeployer, it's great.

Step 1: Generate the DBIx::Class Result classes

• dbicdump
• dbicdump -o dump_directory=lib -o components='["InflateColumn::DateTime"]' Smurf::Foo::DB 'dbi:mysql:database=foo;host=127.0.0.1;port=8022;user=msandbox;password=msandbox'
• ...this will create:
• lib/Smurf/Foo/DB.pm
• Manually add: our $VERSION = 1; to this at the end.
• lib/Smurf/Foo/DB/Result/Table1.pm, etc.

Step 2: Generate the DBIx::Class::Migration files

• dbic-migration prepare
• dbic-migration -I lib prepare --schema_class Smurf::Foo::DB --target_dir=share --dsn='dbi:mysql:database=foo;...etc'
• ...this will create:
• share/migrations/_source/deploy/1/001-auto.yml
• share/migrations/_source/deploy/1/001-auto-__VERSION.yml
• share/migrations/MySQL/deploy/1/001-auto-__VERSION.sql
• share/migrations/MySQL/deploy/1/001-auto.sql
• share/fixtures/1/conf/all_tables.json

Notes and gotchas

• If you see a definition for dbix_class_deploymenthandler_versions anywhere, i.e. in lib/Smurf/Foo/DB/Result/DbixClassDeploymenthandlerVersion.pm then you're gonna have a bad time. This could mean you accidentally ran dbicdump after dbic-migration prepare. It will cause migrations to fail when they try to create the dbix_class_deploymenthandler_versions table from both 001-auto-__VERSION.sql and 001-auto.sql

Use case B: Making changes to the database

Step 3: Make changes and update files

• Do not write any SQL
• Make changes to the DBIC classes under `lib/Smurf/Foo/DB/Result`, but DO NOT MODIFY THE FIRST PART OF THE FILE (see DBIx::Class::Schema::Loader comments in those files)
• Bump the version in `lib/Smurf/Foo/DB.pm` (or add it - at the bottom outside the auto-generated section: `our $VERSION = 2;`
• Run `dbic-migration prepare --schema_class Smurf::Foo::DB --target_dir=share --dsn='dbi:mysql:database=foo;...etc'` to create a new migration in the `share` directory
• Run `dbicdump -o dump_directory=lib -o components='["InflateColumn::DateTime"]' Smurf::Foo::DB 'dbi:mysql:database=foo;...etc'` to see if it makes any updates to the first part of the DBIC classes under `lib/Smurf/Foo/DB/Result`

Use case C: Not making changes to the database

Step 4: Install and upgrade as needed

• Run dbic-migration install --schema_class Smurf::Foo::DB --target_dir=share --dsn='dbi:mysql:database=foo;...etc'
• Run dbic-migration upgrade ...etc

Hack your own MySQL to log in as root

$> sudo service mysql stop

$> mysqld --skip-grant-tables

$> mysql

mysql> FLUSH PRIVILEGES;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

mysql> SELECT host, user, plugin FROM mysql.user;

(source)

How to use dbdeployer to easily deploy multiple versions of MySQL for testing

Download dbdeployer, it is awesome.

Caveat: For best results, you should have a recent version of your operating system installed.

dbdeployer cheat sheet

export PATH=/path/to/dbdeployer:$PATH

dbdeployer downloads tree --flavour mysql --OS linux

dbdeployer downloads get-unpack [filename here]

dbdeployer deploy single 8.0.22

dbdeployer sandboxes

dbdeployer use msb_8_0_22

dbdeployer sandboxes --full-info

Note: Your databases will be installed into e.g. ~/opt/mysql

And a special set of controls will be put in ~/sandboxes

Working with dbdeployer

~/sandboxes/msb_8_0_22/my sql -h 127.0.0.1 --port 8022 ...etc

~/sandboxes/msb_8_0_22/metadata socket

Compiling DBD::mysql for Perl on Mac OSX

I will tell you how:

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

MySQL basics - creating a user, etc.

Creating a user:

CREATE USER 'user'@'hostname';

ALTER USER 'user'@'hostname' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON dbName.* To 'user'@'localhost' IDENTIFIED BY 'password';

(source)

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.

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)

Perl: Fix DBI::db->disconnect invalidates 1 active statement handle

Add this subroutine to the module:

sub DESTROY {
    my ($self) = @_;
    # Finish off our statement handle, so we don't get warnings like:
    # DBI::db->disconnect invalidates 1 active statement handle
    my $sth = $self->{data_handle};
    if (blessed($sth) && $sth->can('finish')) {
        $sth->finish;
    }
}

Run an SQL script

• (a) pipe it into the program:  mysql < script.sql
• (b) source it from within mysql:  source script.sql

See also batch commands

Automatically diagram an SQL database

Use cases:

• Designing a new schema from scratch, using a GUI
• Automatically rendering a diagram of an existing SQL schema

2010:

• The least worst option is MySQL Workbench. The GUI is poor: http://wb.mysql.com/
• This one uses Javascript. No features, but it works: http://code.google.com/p/database-diagram/

See also database-schema-diagrams-in-2022

Remove all login restrictions for MySQL

Start mysqld (or mysqld_safe) with the --skip-grant-tables option

Use DBUnit

Add to the classpath:

• dbunit-2.4.7
• slf4j-api-1.6.1
• slf4j-simple-1.6.1
• mysql-connector-java-5.0.8

Example program:

import java.io.FileOutputStream;
import java.sql.*;
import org.dbunit.database.*;
import org.dbunit.dataset.*;
import org.dbunit.dataset.xml.*;
// database connection
Class driverClass = Class.forName("com.mysql.jdbc.Driver");
Connection jdbcConnection = DriverManager.getConnection("jdbc:mysql://host:port/dbname","user","pass");
IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
// prevent error: Potential problem found: The configured data type factory
// 'class org.dbunit.dataset.datatype.DefaultDataTypeFactory' might cause problems with the current database 'MySQL'
// (e.g. some datatypes may not be supported properly).
connection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory());
// full database export
IDataSet fullDataSet = connection.createDataSet();
FlatXmlDataSet.write(fullDataSet, new FileOutputStream("dbname"));

Add a password to a MySQL login

Many applications require there to be a password.

SELECT host, user FROM mysql.user;

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

Case sensitive MySQL column

Make a case sensitive varchar column:

CREATE TABLE `page_dm` (
`url` varchar(500) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Remove MySQL user password

root@dsdsdsdsd:~# mysql -p
Enter password:
mysql> use mysql;
mysql> update user set Password='' where User='yourusername';
mysql> commit;
mysql> flush privileges;
mysql> quit;

Reasons not to use stored procedures in databases

http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html

Can't connect to MySQL

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

Reason: mysqld is not running!