DBA Blogs

Pythian at the 2016 Percona Live Data Performance Conference

Pythian Group - Mon, 2016-03-07 10:22

The Percona Live Data Performance Conference in Santa Clara is being held April 18-22, 2016. It is quickly approaching, and Pythian is going to show you how we Love Your Data in a big way!

We have an awesome lineup of speakers this year:

Mark these down in your schedule because you are not going to want to miss any of them! Although, you might have a tough time choosing between the tutorials if you can’t clone yourself.

Also, please join us for the 2016 Annual Community Dinner @ Pedro’s. You can register now through EventBrite.

Categories: DBA Blogs

RSA Conference 2016

Pythian Group - Mon, 2016-03-07 08:41

RSAConference 2016 Where the world talks security 

40,000 attendees, 500+ vendors and 700 sessions

RSAC is my annual check in to learn new approaches to information security, discover new technology, learn from industry experts and build my community.

In the three years that I have been attending RSAC, I have learned that Pythian is unique and so are our clients. Each year, we continue to improve our security program with our clients in mind.

RSAC Day 1

It’s Day 1 of the RSAConference 2016. Monday’s are typically a quiet day with vendors setting up in the expo halls, conference staff getting organized, attendees registering and a few press/analysts looking for optimal interview spots. It has been the calm before the storm of attendees descend on San Francisco and RSAC.

This Monday was a whirlwind of activity; CSA Summit, DevOps Connect, Information Security Leadership Development and IAPP: Privacy and Security to name only a few. Chances are you may have missed sessions if you weren’t early enough.

Privacy and Security were hot topics given the European General Data Protection Regulation (GDPR) agreement reached December 2015.

For more information on the event visit the RSAC website, or follow the conference on Twitter.

 

 

 

 

Categories: DBA Blogs

COMPRESSION -- 2 : Compressed Table Partitions

Hemant K Chitale - Sun, 2016-03-06 09:38
A Partitioned Table can choose to have a mix of COMPRESS and NOCOMPRESS Partitions.

As in this case where the first 3 partitions are defined as COMPRESS and the last as NOCOMPRESS :  :

[oracle@localhost Hemant]$ sqlplus hemant/hemant@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 6 23:20:19 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Sun Mar 06 2016 23:19:11 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @create_SALESHIST
SQL> spool create_SALESHIST
SQL>
SQL> drop table SALESHIST;

Table dropped.

SQL>
SQL> alter session set "_partition_large_extents"=FALSE;

Session altered.

SQL>
SQL> create table SALESHIST
2 (
3 PROD_ID NUMBER NOT NULL ,
4 CUST_ID NUMBER NOT NULL ,
5 TIME_ID DATE NOT NULL ,
6 CHANNEL_ID NUMBER NOT NULL ,
7 PROMO_ID NUMBER NOT NULL ,
8 QUANTITY_SOLD NUMBER(10,2) NOT NULL ,
9 AMOUNT_SOLD NUMBER(10,2) NOT NULL
10 )
11 NOCOMPRESS LOGGING
12 TABLESPACE USERS
13 PARTITION BY RANGE (TIME_ID)
14 (
15 PARTITION SALES_1998
16 VALUES LESS THAN (TO_DATE('1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
17 'NLS_CALENDAR=GREGORIAN'))
18 SEGMENT CREATION IMMEDIATE
19 COMPRESS BASIC NOLOGGING
20 TABLESPACE SALES_1998 ,
21 PARTITION SALES_1999
22 VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
23 'NLS_CALENDAR=GREGORIAN'))
24 SEGMENT CREATION IMMEDIATE
25 COMPRESS BASIC NOLOGGING
26 TABLESPACE SALES_1999 ,
27 PARTITION SALES_2000
28 VALUES LESS THAN (TO_DATE('2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
29 'NLS_CALENDAR=GREGORIAN'))
30 SEGMENT CREATION IMMEDIATE
31 COMPRESS BASIC NOLOGGING
32 TABLESPACE SALES_2000 ,
33 PARTITION SALES_2001
34 VALUES LESS THAN (TO_DATE('2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
35 'NLS_CALENDAR=GREGORIAN'))
36 SEGMENT CREATION IMMEDIATE
37 NOCOMPRESS NOLOGGING
38 TABLESPACE SALES_2001 )
39 /

Table created.

SQL>
SQL> spool off
SQL>
SQL> col partition_name format a30
SQL> select partition_name, compression, compress_for
2 from user_tab_partitions
3 where table_name = 'SALESHIST'
4 order by partition_position;

PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
SALES_1998 ENABLED BASIC
SALES_1999 ENABLED BASIC
SALES_2000 ENABLED BASIC
SALES_2001 DISABLED

SQL>
SQL> select compression, compress_for
2 from user_tables
3 where table_name = 'SALESHIST';

COMPRESS COMPRESS_FOR
-------- ------------------------------


SQL>


The Compression attributes actually apply to the Segments so the Partition Segments have the definition but the Table, being segmentless, does not show the definition.

Note that I am still demonstrating BASIC Compression. So the compression is applied only on Direct Path INSERT.  As in :

SQL> insert /*+ APPEND */ into saleshist select * from sh.sales;

918843 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select partition_name, tablespace_name, bytes/1024
2 from user_segments
3 where segment_name = 'SALESHIST'
4 and segment_type = 'TABLE PARTITION'
5 order by 1;

PARTITION_NAME TABLESPACE_NAME BYTES/1024
------------------------------ ------------------------------ ----------
SALES_1998 SALES_1998 3072
SALES_1999 SALES_1999 4096
SALES_2000 SALES_2000 4096
SALES_2001 SALES_2001 11264

SQL>


This demonstrates that it is possible to
a) Have some Partitions defined as COMPRESSed and others as NOCOMPRESSed
b) Create different Partitions in different Tablespaces

Before the advent of (and still without using) the 12c ILM features, this was and is a method to manage historical data with compression and moving or placing data (i.e. the respective tablespace datafile(s)) in low-cost storage as desired.

Note : For the purposes of this demo, I used the parameter "_partition_large_extents"=FALSE.  Oracle's default Partition size since 11.2.0.2 has been 8MB for Table Partitions and that would have been excessively large for this demo.  If you are using Hybrid Columnar Compression and/or Exadata, Oracle advises against this.
.
.
.

Categories: DBA Blogs

Enabling archive log mode -12c

Learn oracle 12c database management - Fri, 2016-03-04 11:05
Since the Redologs are created at container database level in 12c and not at pluggable database level. (Enabling archivelog at pluggable database level is not possible). Archiving is done at CDB's.

You can check archive log mode either by querying v$database or archivelog list 

SQL> select name,open_mode,log_mode from v$database;

NAME  OPEN_MODE       LOG_MODE
--------- -------------------- ------------
ORCL  READ WRITE       NOARCHIVELOG

(OR)

SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Disabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence       13

*************** ***************
To enable the Archvielog mode
*************** ***************

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select name,open_mode,log_mode from v$database;

NAME  OPEN_MODE       LOG_MODE
--------- -------------------- ------------
ORCL  READ WRITE       ARCHIVELOG


SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence       13
Categories: DBA Blogs

open all pluggable databases -12c

Learn oracle 12c database management - Fri, 2016-03-04 09:48
SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       READ ONLY
PDBORCL3       READ WRITE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.


SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       MOUNTED
PDBORCL3       MOUNTED


SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       READ WRITE

Categories: DBA Blogs

Clone the pluggable database - 12c

Learn oracle 12c database management - Fri, 2016-03-04 09:40
********************* *********************
   Select a pluggable database to clone
********************* *********************

SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ ONLY
PDBORCL2       READ WRITE

SQL> alter pluggable database pdborcl1 close;

Pluggable database altered.

SQL> alter pluggable database pdborcl1 open read only;

Pluggable database altered.


********************* *********************
  Lets clone the pluggable database now
********************* *********************

SQL> create pluggable database pdborcl3 from pdborcl1 file_name_convert=('/u01/app/oracle/oradata/orcl/pdborcl1/'
,'/u01/app/oracle/oradata/orcl/pdborcl3/');  

Pluggable database created.

SQL> alter pluggable database pdborcl3 open;

Pluggable database altered.


SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ ONLY
PDBORCL2       READ WRITE
PDBORCL3       READ WRITE


SQL> alter pluggable database pdborcl1 close;

Pluggable database altered.

SQL> alter pluggable database pdborcl1 open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ WRITE
PDBORCL2       READ WRITE
PDBORCL3       READ WRITE


************************** **************************
Check this below link on how to drop a pluggable database :
************************** **************************

DROPPING-PLUGGABLE-DATABASE








Categories: DBA Blogs

Dropping a pluggable database -12c

Learn oracle 12c database management - Fri, 2016-03-04 09:38
********************* *********************
Dropping a pluggable database :
********************* *********************

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       READ WRITE



SQL> DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES;
DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES
*
ERROR at line 1:
ORA-65025: Pluggable database PDBORCL3 is not closed on all instances.

You need to close the database before dropping.

SQL> alter pluggable database PDBORCL3 close;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       MOUNTED

SQL> DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES;

Pluggable database dropped.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE


Lets make sure by looking at datafile location

[oracle@Linux03 pdborcl3]$ pwd
/u01/app/oracle/oradata/orcl/pdborcl3

[oracle@Linux03 pdborcl3]$ ls -ll
total 0


[oracle@Linux03 pdborcl3]$ 


Categories: DBA Blogs

Links for 2016-03-03 [del.icio.us]

Categories: DBA Blogs

2016 Annual Pythian MySQL Community Dinner

Pythian Group - Thu, 2016-03-03 10:26

Once again, Pythian is organizing an event that by now may be considered a tradition: The MySQL community dinner at Pedro’s! This dinner is open to all MySQL community members since many of you will be in town for Percona Live that week. Here are the details:

What: The MySQL Community Dinner

When: Tuesday April 19, 2016 –  7:00 PM at Pedro’s (You are welcome to show up later, too!)

Where: Pedro’s Restaurant and Cantina – 3935 Freedom Circle, Santa Clara, CA 95054

Cost: Tickets are $40 USD, Includes Mexican buffet, non-alcoholic drinks, taxes, and gratuities (see menu)

How: Purchase your ticket below or RSVP through Eventbrite

Pythian Attendees:

Derek Downey
Alkin Tezuysal
Okan Buyukyilmaz
Emanuel Calvo
John Schulz
Martin Arrieta
Gabriel Cicilliani
Christos Soulios
Theresa Nova

Categories: DBA Blogs

Connecting to a pluggable database -12c

Learn oracle 12c database management - Thu, 2016-03-03 10:05
******* ********** *********
USING EZCONNECT:
******* ********** *********

[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxxx@localhost:1521/pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:12:17 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDBORCL1

******** ********* *********
USING TNSNAMES:
******** ********* *********

Create a tnsnames entry for the pdb that you want to connect

[oracle@Linux03 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

pdborcl1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Linux03)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
 



[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxx@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:15:10 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDBORCL1

Categories: DBA Blogs

MySQL on FreeBSD: old genes

Pythian Group - Thu, 2016-03-03 10:02

Maintaining mission critical databases on our pitchfork wielding brother, the “Daemon” of FreeBSD, seems quite daunting, or even absurd, from the perspective of a die-hard Linux expert, or from someone who has not touched it in a long time. The question we ask when we see FreeBSD these days is “why?”.  Most of my own experience with FreeBSD was obtained 10-15 years ago.  Back then, in the view of the team I was working on, a custom compiled-from-source operating system like FreeBSD 5.x or 6.x was superior to a Linux binary release.

Package managers like YUM and APT were not as good.  They did not always perform MD5 checks and use SSL like today’s versions. RedHat wasn’t releasing security updates 5 minutes after a vulnerability was discovered. Ubuntu didn’t exist. Debian stable would get so very old before receiving a new version upgrade. FreeBSD was a great choice for a maintainable, secure, free open source UNIX-like OS with tight source control and frequent updates.

Most people do not understand why FreeBSD remains a great choice for security and stability. The main reason is that the entire source of the base OS and the kernel (not just the kernel) are tightly maintained and tested as a whole, monolithic, distribution.

FreeBSD 10.2 is different than versions I worked on many years ago, in a good way, at least from the standpoint of getting started. First, “pkg” has gotten quite an overhaul, making installing packages on FreeBSD as easy as with YUM or APT.  portsnap and portmaster make port upgrades much easier than they used to be. freebsd-update can take care of wholesale updates of the operating system from trusted binary sources without having to “build the world”. These are welcome changes; ones that make it easier to get to production with FreeBSD, and certainly made the task of rapidly building and updating a couple of “lab” virtual machines easier.

In my effort to get re-acquainted with FreeBSD, I hit some snags. However, once I was finished with this exercise, FreeBSD had re-established itself in my mind as a decent flavor to host a mission critical database on. Open Source enthusiasts should consider embracing it without (much) hesitation. Is there some unfamiliar territory for those who only use MySQL on MacOS and Linux? Sure. But it is important to remember that BSD is one of the oldest UNIX like operating systems. The OSS world owes much heritage to it. It is quite stable and boring, perhaps even comfortable in its own way.

Problem 1: forcing older versions of MySQL

I needed to install MySQL 5.5 first, in order to test a mysql upgrade on FreeBSD.  However, when installing percona-toolkit either via “pkg install” (binary) or /usr/ports (source), the later 5.6 version of the mysql client would inevitably be installed as a dependency. After that point, anything relating to MySQL 5.5 would conflict with the 5.6 client. If I installed in the opposite order, server first, percona-toolkit second, the percona-toolkit installation would ask me if it is OK to go ahead and upgrade both server and client to 5.6.

TIP: don’t forget make.conf

my /etc/make.conf:
MYSQL_DEFAULT?= 5.5

Once I added MYSQL_DEFAULT into make.conf, the installations for MySQL 5.5 became seamless. Note: if you want another flavor of MySQL server such as Percona Server, install the server “pkg install percona55-server” prior to “pkg install percona-toolkit” so that the client dependencies are met prior to installation.

Problem 2: Some tools don’t work

pt-diskstats does not work, because it reads from /proc/diskstats, which does not exist on FreeBSD. Other favorites like htop don’t work right out of the box. So far I have had good luck with the rest of the Percona toolkit besides pt-diskstats, but here’s how you get around the htop issue (and perhaps others).

TIP: Get the linux /proc mounted

dynamic commands:
# kldload linux
# mkdir -p /compat/linux/proc
# mount -t linprocfs linproc /compat/linux/proc

to make permanent:
# vi /boot/loader.conf (and add the following line)
linux_load="YES"
# vi /etc/fstab (and add the following line)
linproc /compat/linux/proc linprocfs rw 0 0

As you may have determined, these commands ensure that the linux compatibility kernel module is loaded into the kernel, and that the linux style /proc is mounted in a different location than you might be used to “/compat/linux/proc”. The FreeBSD /proc may also be mounted.

Problem 3: I want bash

# pkg install bash
… and once that’s done
# pw user mod root -s /usr/local/bin/bash
…and repeat ^^ for each user you would like to switch. It even comes with a prompt that looks like CentOS/RHEL.
[root@js-bsd1 ~]#

Problem 4: I can’t find stuff

BSD init is much simpler than SysV and upstart init frameworks so your typical places to look for start files are /etc/rc.d and /usr/local/etc/rc.d. To make things start on boot, it’s inevitably a line in /etc/rc.conf.

In our case, for MySQL, our start file is /usr/local/etc/rc.d/mysql-server. To have MySQL start on boot, your rc.conf line is:
mysql_enable="YES".

If you do not wish to make MySQL start on boot, you may simply say "/usr/local/etc/rc.d/mysql-server onestart"

Notes on binary replacement

Please note, just like in the Linux world, MariaDB and Percona Server are drop in replacements for MySQL so, the startfiles and enable syntax does not change. Your default location for my.cnf is /etc/my.cnf just like in the rest of the known universe.

This command lists all installed packages.
pkg info -a

use pkg remove and pkg install to add new versions of your favorite mysql software.

I ran into no greater issues with pkg than I would with yum or apt doing binary removals and installations, and no issues at all with mysql_upgrade. Remember: If you had to alter make.conf like I did earlier, remember to update it to reflect versions you want to install.

For those who like ZFS, the FreeBSD handbook has a very detailed chapter on this topic. I for one like plain old UFS. It might be the oldest filesytem that supports snapshots and can be implemented very simplistically for those who like low overhead.

Happy tinkering with FreeBSD and MySQL, and thanks for reading!

Categories: DBA Blogs

Sydney Gets New AWS Availability Zone

Pythian Group - Thu, 2016-03-03 09:47

On a scorching November day in 2012, Sydneysiders were bracing themselves for yet another heat wave when all of a sudden they became pleasantly surprised as an elastic cloud occupied the tech skies. On November 12, 2012, Amazon announced  the New Asia Pacific (Sydney) Region in Australia.

Before that, Australian customers had to reach out to Japan or Singapore for their cloud needs. That was not really feasible, as it increased up-front expenses, long-term commitments, and scaling challenges. Amazon recognized that and Sydney became another region in the world.

They have now taken it a step further. They have rendered a new Availability Zone (AZ) in Sydney. Availability zone (AZ) is basically an isolated location within data centre regions from which public cloud services originate and operate.

The new availability zone is ap-southeast-2c. This is all set to provide enhanced performance and sociability to Australian customers. This will enable them to fully leverage the potential of technologies like Lambda, the Elastic File System shared filesystem, and Amazon RDS for MS SQL Server.

Pythian’s established presence in Australia and New Zealand coupled with round the clock and world class support for AWS, SQL Server, and other cloud technologies, enables it to support Australian and New Zealand customers from the word go.

Categories: DBA Blogs

Links for 2016-03-02 [del.icio.us]

Categories: DBA Blogs

SQL Injection with MySQL SLEEP()

Pythian Group - Wed, 2016-03-02 11:40

Recently we’ve received an alert from one of our clients that running threads are high on one of their servers. Once we logged in, we noticed that all the selects were waiting for table level read lock. We scrolled through the process list, and found the selects which were causing the problems. After killing it, everything went back to normal.
At first we couldn’t understand why the query took so long, as it looked like all the others. Then we noticed, that one of the WHERE clauses was strange. There, we found a SLEEP(3) attached with OR to the query. Obviously, this server was the victim of a SQL injection attack.

What is SQL injection?

I think most of us know what SQL injection is, but as a refresher, SQL injection is when someone provides malicious input into WHERE, to run their own statements as well.
Typically this occurs when you ask a user for input, like username, but instead of a real name they give you a MySQL statement that will be run by your server without you knowing it.
Exploits of a Mom
Based on the picture, let’s see a few examples.
We have a simple table:

mysql> describe post;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| test  | varchar(127)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from post;
+----+--------+
| id | test   |
+----+--------+
|  1 | text1  |
|  2 | text2  |
|  3 | text3  |
|  4 | text4  |
|  5 | text5  |
|  6 | text6  |
|  7 | text7  |
|  8 | text8  |
|  9 | text9  |
| 10 | text10 |
+----+--------+
10 rows in set (0.00 sec)

Lets run a select with LIKE, which we know for sure won’t have a match:

mysql> select * from post where test like '%nomatch%';
Empty set (0.00 sec)

But what, happens if we don’t filter the inputs and someone wants to get all the data?
mysql> select * from post where test like '%nomatch ' || '1==1' && '1%';
+----+--------+
| id | test   |
+----+--------+
|  1 | text1  |
|  2 | text2  |
|  3 | text3  |
|  4 | text4  |
|  5 | text5  |
|  6 | text6  |
|  7 | text7  |
|  8 | text8  |
|  9 | text9  |
| 10 | text10 |
+----+--------+
10 rows in set, 2 warnings (0.00 sec)

That was a very mild injection, but it could be much more malicious: we could drop another table!

mysql> show tables;
+----------------------+
| Tables_in_injecttest |
+----------------------+
| game                 |
| post                 |
+----------------------+
2 rows in set (0.01 sec)

mysql> select * from post where test like '%nomatch'; drop table game;-- %';
Empty set (0.00 sec)

Query OK, 0 rows affected (0.28 sec)

mysql> show tables;
+-----------------------+
| Tables_in_inject_test |
+-----------------------+
| post                  |
+-----------------------+
1 row in set (0.00 sec)

mysql>

If we don’t know the name of the table, we can still cause trouble by blocking access to the database
If we insert SLEEP() in the WHERE part, then it will be executed for every matching row… if we inject it like: “OR SLEEP(n)”, it will be executed to every row in the table!
Okay, this will be “just” a long running select. It shouldn’t cause much trouble thanks to InnoDB and transaction isolation, unless something needs a table lock.

Some common examples of what causes table locks are:

  • explicit lock table
  • insert/update/delete on MyISAM
  • ALTER table on InnoDB

Once statements start waiting for lock on the table, all proceeding selects will wait for the previous locking statement to finish

Terminal 1:
mysql> select * from post where test like '%nomatch ' OR sleep(300) AND '1%';
….
Terminal 2:
mysql> alter table post engine=innodb;
...
Terminal 3:
mysql> select SQL_NO_CACHE count(*) from post;
…
Processlist:
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
| Id       | User                 | Host      | db                 | Command | Time  | State                           | Info                                                                  |
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
| 17170817 | root                 | localhost | janitest           | Query   |    19 | User sleep                      | select * from post where test like '%nomatch ' OR sleep(300) AND '1%' |
| 17170918 | root                 | localhost | janitest           | Query   |    11 | Waiting for table metadata lock | alter table post engine=innodb                                        |
| 17170957 | root                 | localhost | janitest           | Query   |     4 | Waiting for table metadata lock | select * from post                                                    |
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

As we see in the example, ALTER table will wait until it can get a lock on post table, and this blocks every other select from now on to the table.
Or, if you are using MyISAM table, a simple update/insert will block access to the table, because it needs table level lock during them.

How can we defend ourselves from SQL injection?

There are several ways to secure yourself from SQL injection.

  • First of all, validate the input. If you expect only letters and numbers, filter it with regexp for example, to make sure there are no special characters there. Also escape the inputs on application side; programming languages have built-in function to do that (eg.: mysql_real_escape_string() in PHP)
  • Use prepared statement! It won’t allow 2 clause if you specified only 1. When you use prepared statements, the variables are transmitted as MySQL variables. Even if the string is not escaped, it will end up in one variable, and MySQL treats is as a longer string.
    (For more details see: http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html )
  • Use a tool like MySQL Enterprise Firewall, which is a plugin for MySQL and can filter your statements to make sure there are no things like: || 1==1

I would like to start a little talk about this, so if you encountered SQL injection before, would you share it with us, how they did it, or in general how do you prevent SQL injections in your application?

 

Categories: DBA Blogs

prvf-0002 : could not retrieve local node name

Learn DB Concepts with me... - Wed, 2016-03-02 09:43

prvf-0002 : could not retrieve local node name



PRVF-0002 : could not retrieve local node name
check if the hostname is correct in sysconfig/network & /etc/hosts files:
[oracle@Linux03 ~]$ cat /etc/sysconfig/network | grep HOSTNAME
HOSTNAME=Linux03

[oracle@Linux03 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

[oracle@Linux03 ~]$ vi /etc/hosts

[oracle@Linux03 ~]$ su root
Password:
[root@Linux03 oracle]# vi /etc/hosts

[root@Linux03 oracle]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.215.xxx.xx Linux03   <<<--- added hostname and ip


An alternate solution is to add the hostname in bash_profile file in home dir:
[oracle@Linux03 ~]$ cat .bash_profile | grep HOSTNAME
export ORACLE_HOSTNAME=Linux03
Categories: DBA Blogs

Oracle Infrastructure Cloud Partner Briefings

Oracle Infrastructure Cloud  Webcasts - Partner Briefings ...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Log Buffer #463: A Carnival of the Vanities for DBAs

Pythian Group - Tue, 2016-03-01 15:27

As the winter in the Northern hemisphere is giving way to spring, slowly but surely, blog posts are blooming in the gardens of Oracle, SQL Server and MySQL. This Log Buffer plucks some of them for your reading pleasure.

Oracle:

Providing A Persistent Data Volume to EMC XtremIO Using ClusterHQ Flocker, Docker And Marathon

There is sliced bread in SQL.

Oracle Cloud – Your service is suspended due to exceeding resource quota !

EM12c Compliance ‘Required Data Available’ flag – Understanding and Troubleshooting

How can I see my invisible columns

SQL Server:

Auto Generate Your Database Documentation

A Lightweight, Self-adjusting, Baseline-less Data Monitor

Keeping POST and GET Separated

How often should I run DBCC CHECKDB?

Disabling SQL Server Optimizer Rules with QUERYRULEOFF

MySQL:

MySQL Contributions status

Planets9s: Building scalable database infrastructures with MariaDB & HAProxy

High availability with asynchronous replication… and transparent R/W split

mysql_real_connect is not thread safe

Now available in swanhart-tools: NATIVE asynchronous query execution for any MySQL client!

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs