Blog

Last rowcount in MySQL

#mysql

Suppose you want to count the number of lines returned by the last SQL statement issued. For select statements you can use the FOUND_ROWS construct: SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever; SELECT FOUND_ROWS(); This will return the number of rows in the last select query (or if the first query has a LIMIT clause, ... Read more

20 Sep 2016 - less than 1 minute read

Implement an MD5sum function in Oracle

#oracle

A few days ago, I was looking for a way to get an MD5sum function to work directly in Oracle, like the MD5() function in MySQL. After some searching I found out that there was no direct way to generate an MD5 checksum in Oracle. However, the function exists in the Oracle supplied package dbms_obfuscation_toolkit, so here’s the way to make a usab... Read more

20 Aug 2016 - less than 1 minute read

MySQL punch user creation DDL

#mysql

You can get the DDL necessary to duplicate an existing user with the following system command: MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>'' " | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUse... Read more

20 Jul 2016 - less than 1 minute read

InnoDB table rebuild

#mysql

PROBLEM On a MySQL 5.6 database server, the ibdata1 file includes 5 InnoDB tables in the mysql schema. mysql> select table_name from information_schema.tables -> where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table_stats | ... Read more

20 Jun 2016 - 1 minute read

Transfer multiple files simultaneously using SCP

#linux

I have been using the Secure Copy (scp) utility for copying files between my local server and development server. Sometimes I have to copy more than one file. Copying each file can be very annoying, as you have to type the password every time you use the command. But it is possible to copy multiple files using scp, just like the copy (cp) utilit... Read more

20 May 2016 - 1 minute read

Resolve the ORA-01157 Error

#oracle

Another day at the office, I was investigating a performace issue when I encountered the following error: Select count(*) from purchase.table where CREATION_DATE > SYSDATE - 40; Execution Plan ---------------------------------------------------------- An uncaught error happened in fetching the records : ORA-01157: cannot identify/lock data ... Read more

20 Apr 2016 - less than 1 minute read

Kill locked sessions

#oracle

Sometimes, when there’s many database sessions connected and depending on the user activity, locks can occur on a database. While this may only affect a few users, the problem is that with many sessions locked the server load will increase. And there’s a risk of crashing the whole database if this happens to many user sessions at the same time. ... Read more

20 Mar 2016 - 1 minute read

MySQL regexp Replace

#mysql

MySQL provides you with a useful string function called REPLACE that allows you to replace a string in a column of a table by a new string. The REPLACE function is very handy to search and replace text which affects multiple records such as obsolete URL, spelling mistake and such. The syntax of REPLACE function is as follows: UPDATE tbl_name S... Read more

20 Feb 2016 - 1 minute read

PostgreSQL Survival Guide

#postgresql #database

PostgreSQL has been gaining a lot of popularity these days, so let’s have a look at the basics of administering a PostgreSQL database. We’ll cover the structure exploration, object manipulation and some user managment. Quick remainder of some mainly used PostgreSQL datatypes: Name Storage size Description ... Read more

20 Jan 2016 - 3 minute read

Extract tablespace DDL

#oracle

Should you need to perform a large data export and import on different Oracle databases, you’ll need to make sure that the tablespace configuration matches your export and import parameters. Whether you keep the same tablespace configuration, or should you decide to change it. Here’s how to get the DDL necessary to check the structure or eventu... Read more

20 Dec 2015 - less than 1 minute read

Fixing the ORA-00600: [ktfbtgex-7] error

#oracle

It was a normal day at the office, when I noticed an ORA-00600 on a production database. Upon investigation, I found the following details in the alert.log file: ORA-00600: internal error code, arguments: [ktfbtgex-7], [2031625], [128], [2031624], [], [], [], [] This error was related to extent management bitmap for locally managed tablespace... Read more

20 Nov 2015 - 1 minute read

GNU Screen Handbook

#linux

GNU Screen is a very useful tool to run a long script on a server and make sure the script will run even if your computer gets disconnected, runs out of battery or whatever. In this article, I’ll assume you’re already familiar with the basic concepts of GNU screen, and I’ll just provide you with some tips that I use to make this tool even more ... Read more

20 Oct 2015 - less than 1 minute read