BowerStudios.com

  • My Lab
  • Family
  • Friends
  • Professional
  • About
Home » The Lab » Programming

SQL

MySql / ORM Research

daniel —Fri, 02/08/2019 - 11:01am

Mysql enums perfomance

Guid performance

How to use the unsigned Integer in Java 8 and Java 9?

How to generate unique Long using UUID

Problems mapping UUID in JPA/hibernate

Mysql 8.0: UUID support

How to format uuid string from binary column in MySQL/MariaDB

Percona Toolkit - Duplicate key checker

Optimistic Locking in MySql

JPA Performance Benchmark

High Performance Hibernate

  • SQL
  • Read more about MySql / ORM Research
  • Log in to post comments

Get table create statement from command line (mysql)

daniel —Fri, 10/12/2018 - 10:03am

describe tablename;

  • SQL
  • Read more about Get table create statement from command line (mysql)
  • Log in to post comments

MyISAM = no transactions

daniel —Thu, 08/30/2018 - 10:56am

Let's see if I can remember it this time thanks to this answer from RandomSeed

Was testing a change to some transaction handling and was confused at the results. Turns out the random table I chose to test with was MyISAM instead of Innodb.

But now I know how to setup general query logging and slow query logging on MariaDb and Mysql on a mac.

I had to use some help from: ewalshe and Drew on how to locate my.cnf on a mac.
It ended up being in
/usr/local/etc/my.cnf
The config added:
[mysqld]
general-log
general-log-file=queries.log
log-output=file
slow_query_log=1

If you installed with homebrew, you'll have to restart the service with:
brew services stop mariadb; brew services start mariadb
Using the restart target won't work.

Can then tail those logs:
tail -f /usr/local/var/mysql/myhostname.* /usr/local/var/mysql/queries.log
Change myhostname as appropriate. Best to just do an ls in that dir.

The format of the general query log was interesting too as it wasn't straightforward that the id referenced a thread for the database

What's in the slow query log: MariaDb docs

As a bonus, some Isolation Level Docs for MariaDb

  • SQL
  • Read more about MyISAM = no transactions
  • Log in to post comments

Automysqlbackup on Mac

daniel —Wed, 05/16/2018 - 12:48pm

This is great for making daily backups of your database, so that you can roll back to a known config.

Install
Use homebrew to install it.
brew install automysqlbackup

The config file is in /usr/local/etc/automysqlbackup/automysqlbackup.conf
You'll want to modify the excludes line to contain the following:
CONFIG_db_exclude=( 'information_schema' 'performance_schema' 'mysql')

create the directory /usr/local/var/backup:
mkdir -p /usr/local/var/backup

Usage
You can run manually with:
automysqlbackup

The databases should be saved to
cd /usr/local/var/backup/db
Optional: The "tree" command can be useful exploring that directory at the command line. You might need to install it via brew as well.

Generally, you can load those backups by first unzipping:
gunzip mydb_2018_04-04_10h00m.Friday.sql.gz

and then you can load it:
mysql < mydb_2018_04-04_10h00m.Friday.sql
or, with a password:
mysql -u myusername --password=THE_PASSWORD_GOES_HERE < mydb_2018_04-04_10h00m.Friday.sql

If the database already exists, you'll need to remove it.

Scheduling
Verify it is loaded in the mac scheduler with:
launchctl list | grep homebrew
Macs don't really use cron. They favor launchctl.

If it isn't there, you may need to copy:
cp /usr/local/Cellar/automysqlbackup/3.0-rc6/homebrew.mxcl.automysqlbackup.plist ~/Library/LaunchAgents
and then
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.automysqlbackup.plist

  • SQL
  • Read more about Automysqlbackup on Mac
  • Log in to post comments

Mysql links

daniel —Fri, 09/29/2017 - 11:53am

Big Deletes by Rick James

Mysql Internal Locking

View processes locking tables in Mysql

Create temporary tables in mysql

Getting the disk size of a table in mysql

Specifying index to use for a query in mysql

Interpreting Explain Query output in mysql

Performing online updates with mysql

  • SQL
  • Read more about Mysql links
  • Log in to post comments

Change fonts size for SquirrelSql and other Java Swing Apps for hidpi monitors

daniel —Wed, 09/27/2017 - 11:20am

From stackoverflow

Boils down to adding a couple of Java switches to the start script:
-Dswing.plaf.metal.controlFont=Tahoma-18 -Dswing.plaf.metal.userFont=Tahoma-18

For example, in squirrel-sql.sh, modify this line to include the above:
$JAVACMD -Xmx256m -Dswing.plaf.metal.controlFont=Tahoma-24 -Dswing.plaf.metal.userFont=Tahoma-24 -cp "$TMP_CP" $MACOSX_SQUIRREL_PROPS -splash:icons/splash.jpg net.sourceforge.squirrel_sql.client.Main --log-config-file "$UNIX_STYLE_HOME"/log4j.properties --squirrel-home "$UNIX_STYLE_HOME" $NATIVE_LAF_PROP $SCRIPT_ARGS

  • SQL
  • Read more about Change fonts size for SquirrelSql and other Java Swing Apps for hidpi monitors
  • Log in to post comments

Performance between Postgresql and Mongo

daniel —Wed, 05/10/2017 - 3:03pm

Link is a little older, but looks interesting: From enterprisedb.com

  • SQL
  • Read more about Performance between Postgresql and Mongo
  • Log in to post comments

Postgres Timezone Info

daniel —Fri, 05/06/2016 - 9:24am

Postgres stores time in UTC, if you want to see the value of a date for a particular timezone, use one of the following:

SELECT now() AT TIME ZONE current_setting('TimeZone');
SELECT now() AT TIME ZONE 'EST5EDT';
SELECT now() AT TIME ZONE 'UTC';

  • SQL
  • Read more about Postgres Timezone Info
  • Log in to post comments

Export to csv from psql (postgres)

daniel —Thu, 06/25/2015 - 4:57pm

From David Larochelle's blog

You can use:
COPY TABLE_NAME TO STDOUT WITH CSV HEADER
to push the output to a csv file.

  • SQL
  • Read more about Export to csv from psql (postgres)
  • Log in to post comments

Change the UI font for Oracle Sql Developer

daniel —Tue, 04/14/2015 - 11:54am

thatJeffSmith.com

  • SQL
  • Read more about Change the UI font for Oracle Sql Developer
  • Log in to post comments

Pages

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • next ›
  • last »
Subscribe to RSS - SQL

Navigation

  • Search
  • Recent content
  • Contact Me
  • Mail
  • Pass Hasher
  • Bower Studios Admin

Quotes

daniel —Mon, 02/26/2007 - 12:29pm

To invent, you need a good imagination and a pile of junk.

—

Thomas A. Edison

  • Log in to post comments
  • daniel's quotes

Popular content

Last viewed:

  • Merry Christmas
  • Google Advanced Search Operators
  • ReCaptcha
  • Make a field read-only in Spring3 Web
  • Mirror Owner permissions to Group

Copyright 2022 Daniel Bower
  • My Lab
  • Family
  • Friends
  • Professional
  • About