This is a living document that I will continue to update and will add new database platforms to as I go along in my career. I spend a lot of time translating database platforms I’ve worked in for all tiers of the technical business. It just seems natural that we might need a location on the web where that information is kept.
I’ll add some diagrams at a (more...)
This Week’s log buffer edition covers some of the useful blog posts from Oracle, SQL Server and MySQL.
ASM disks – lsdg compared with the v$asm_diskgroup view
Can a query on the standby update the primary ?
What should I know about SQL?
Setting Environment Variables in Application Server/Process Scheduler Tuxedo Domains
Oracle HEXTORAW Function with Examples
When it comes to building database applications and solutions, developers, DBAs, engineers and architects have a lot of new and exciting tools and technologies to play with, especially with the Hadoop and NoSQL environments growing so rapidly.
While it’s easy to geek out about these cool and revolutionary new technologies, at some point in the development cycle you’ll need to stop to consider the real-world business implications of the application you’re proposing. After all, you’re (more...)
This post shows you how to add the menu option and GUI to set users and groups. It’s quite a bit easier than mastering all the command-line syntax. It makes setting up the required user and group accounts for an Oracle Enterprise or MySQL database solution much easier.
You add the utility by calling the yum (Yellowdog Updater, Modified) utility like this:
yum installed -y system-config_users
You should see the following:
Loaded plugins: langpacks Usage: (more...)
This Log Buffer Edition goes through various blogs, and selects some of the top posts from Oracle, SQL Server and MySQL.
MOS Note:136697.1 – New HCHECK.SQL for Oracle Database 12c
ORAchk / EXAchk questions.
Cloud control won’t start!
ASMLib is an optional utility that can be used on Linux systems to manage Oracle ASM devices.
ORA-56841: Master Diskmon cannot connect to a CELL.
Oracle BITAND Function with Examples.
I published my 2014 presentation “Making MySQL highly available using Oracle Grid Infrastructure” in Slideshare. You can view it here:
At last night's WWCode Cincinnati panel, I recommended that developers talk to their DBA about what advanced capabilities their RDBMS can offer, so that they don't end up reimplementing functionality in the app that are already available (better and more efficiently) in the database itself. Devs can waste a lot of effort by thinking of databases as dumb, inert data boxes.
I was asked an excellent question: "Where can a dev quickly familiarize herself with (more...)
I last wrote about Couchbase in November, 2012, around the time of Couchbase 2.0. One of the many new features I mentioned then was secondary indexing. Ravi Mayuram just checked in to tell me about Couchbase 4.0. One of the important new features he mentioned was what I think he said was Couchbase’s “first version” of secondary indexing. Obviously, I’m confused.
Now that you’re duly warned, let me remind you of aspects of (more...)
In MySql world it can cause confusion or possible problems with synchronisation in Galera cluster configuration.
Let’s check some examples.
I have MySQL database with datadir=/data in configuration file. I have deleted lost+found directory and restarted MySQL service.
When I list my databases this is result:
Using sysbench to performance test AWS RDS MySQL hardware is an easy three step operation.
Sysbench creates synthetic tests and they are done on a 1 mil row 'sbtest' table that sysbench creates in the MySQL database you indicate. The test doesn't intrude with your database schema, and it doesn't use your data, so it is quite safe. The test is an OLTP test trying to simulate event operations in the database as it (more...)
In this post I will talk about how to measure replication lag and check/fix replication inconsistencies with this tools:
I am using environment from previous blog post.
Master-Master replication with MariaDB 10.0.16 database on Debian 7.
Install Percona Toolkit on both nodes:
$ sudo wget percona.com/get/percona-toolkit.deb
Another method is to set up replication in both directions called master-master replication. But you must be aware that this setup brings some potential issues as data changes are happening on (more...)
ERROR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction
You get the error because your allocated time to hold a DML lock in a transaction exceeds the set limit. Usually the default limit to hold a DML row lock, set by innodb_lock_wait_timeout db parameter, is 50 seconds. If your transaction doesn't commit/rollback within 50 seconds (more...)
I’m taking a few weeks defocused from work, as a kind of grandpaternity leave. That said, the venue for my Dances of Infant Calming is a small-but-nice apartment in San Francisco, so a certain amount of thinking about tech industries is inevitable. I even found time last Tuesday to meet or speak with my clients at WibiData, MemSQL, Cloudera, Citus Data, and MongoDB. And thus:
1. I’ve been sloppy in my terminology around “geo-distribution”, in (more...)
I've never had a tool I really liked that would extract a chunk of a large production database for testing purposes while respecting the database's foreign keys. This past week I finally got to write one: rdbms-subsetter.
rdbms-subsetter postgresql://user:passwd@host/source_db postgresql://user:passwd@host/excerpted_db 0.001
Getting it to respect referential integrity "upward" - guaranteeing every needed parent record would be included for each child row - took less than a day. Trying to get it to also guarantee (more...)
A Pandas DataFrame has a nice to_sql(table_name, sqlalchemy_engine) method that saves itself to a database.
The only trouble is that coming up with the SQLAlchemy Engine object is a little bit of a pain, and if you're using the IPython %sql magic, your %sql session already has an SQLAlchemy engine anyway. So I created a bogus
PERSIST pseudo-SQL command that simply calls
to_sql with the open database connection:
%sql PERSIST mydataframe
The result is (more...)
PyOhio gave my lightning talk on ddlgenerator a warm reception, and Brandon Lorenz got me thinking, and PyOhio sprints filled my with py-drenaline, and now ddlgenerator can inspect your data and spit out SQLAlchemy model definitions for you:
$ cat merovingians.yaml
name: Clovis I
name: Childebert I
$ ddlgenerator --inserts sqlalchemy merovingians.yaml
from sqlalchemy import create_engine, Column, Integer, Table, Unicode
I've had it on github for a while, but I finally released ddlgenerator to PyPI.
I've been frustrated for years that there was no good open-source way to set up RDBMS tables from flat data files. Sure, you could import the data - after setting up the DDL by hand. ddlgenerator handles that; in fact, you can go from zero, setting up and populating a table in a single line. Nothing up my sleeve:
I have also spent time working with MySQL a relational database with open source roots, now part of Oracle. In this post I want to talk about my impressions of MySQL and some tips and tricks I learned working with it.
First and for all, you will have to be ready (more...)