Skip to main content


Real Life Oracle Fiction over a Large data set

After being SQL DBA 4+ years now since 2 years I am trying to be a DBA mean cross platform database administrator. So trying multiple technologies. MySQL, mongodb and Oracle too. My today's post is on oracle, true fiction working on large data-set. Actually I was testing CRUD and aggregations on oracle 11g over 21 millions records (21326826). I had a table employee having huge amount of data and there is a column city which have null values on 21 millions records. The story begins when I was trying to update these null value with some information. Column have already secondary index we have created on city column because result of group by was slow on city so for better response time we have created index on city column. Physical architecture of database and tables space are default . Table is present in user tablespace. We have not changed any configuration of oracle before, I am totally new on oracle.

Migrating MongoDB data to MySQL using Python

The database developers/DBAs go through the task of database migration very often in various projects. Most of the times, such tasks have source and destination using the same technology, for example, MS SQL Server to MS SQL Server or MySQL to MySQL, etc. But, in some challenging projects, we encounter some situations in which, the source and destination technologies differ, leaving us in a mess, for example, MySQL to MongoDB, etc. Even this situation can be handled, where we have to migrate from structured to non-structured database technology. But, when it comes to move data from non-structured to structured technology, it becomes a tedious task, for example, MongoDB to MySQL.  As a database developer, I was working on one such project where I was required to migrate data from MongoDB to MySQL. I tried the following steps at the initial level for connecting the source (MongoDB) to destination (MySQL) using Python Requirements : 1. Python 2.7 2. MongoDB 3.2 3. MySQL 5.7 4. My

CRUD Operations with MongoDB

CRUD operations indicates to the basic Insert, Read, Update and Delete operations. It is the most common requirement for any database how this will input and output. Today I want to share how to do CRUD operation with mongodb database, which is fast growing NoSQL Database. 1.) How to create or open any database in mongodb use <Database> 2.) How to list all collections show collections 3.) Read / find / search from documents all documents db.emp.find() where dept = CS db.emp.find({Dept:"CS"}) db.emp.find({"_id" : ObjectId("53eb50ab3bcd8479d0c302e3")}) Search name only db.EmpDetail.find ( { },  { name: 1 }) 3.) Search Documents Where dept = cs and parent is pradeep db.emp.find({Dept:"DOT", parent:"Avinash"}) 4.) Search Documents where Parent is Deepak or dept is java db.emp.find( { $or:[{parent:"Deepak"},{Dept:"java"}] } ) 5.) How to update to all users db.use

MySQL Error Lock wait timeout exceeded

Cross platform database support is really challenging for any dba, specially for them who is working on sql server since long time because who knows what question may raise tomorrow from which database or version or edition. Last week a developer came with her database issue on one of mysql database. Error she was facing “ Lock wait timeout exceeded; try restarting transaction ” during update on  large table having around 15 millions records, she was updating records through joins from another table. She tried 2-3 times on production, after 1-2 minutes this error was throwing.  I said try it on development/local with same data size, result was still same erro. Then started googling found some positive configurations and did some changes on db instance level settings. But result was  just few seconds improvements only & final out put was error. There are below early actions we have taken : SET join_buffer_size = 1024 * 1024 * 42; SET innodb_write_io_threads = 16; SET innodb_l

mysql database size

Workbench is good but its dead when long running query or table having million of records. If you are experimenting something new on large data sets, then I guess the popular error everyone facing which is “Error Code: 2013. Lost connection to MySQL server during query”. So this is the time to forget workbench and come to command shell. Actually today I was finding list of the database with size, running on my local system. This is the query which show results very quickly from command shell.  SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size   (MB)" FROM information_schema.TABLES GROUP BY table_schema ;

Postgres is also an option than MySQL

Last month I got a chance to optimize some queries of a postgre sql database in my current organization. I was totally new to this database. SQL Server, mySQL, MongoDB, and now, postgres. I  never say a 'no' if some work related to database comes to me, and especially, if the database belongs to the RDBMS family, then, I can never say a 'no', as I am always keen to learn new and new trends. My thought for postgres was wrong earlier. I used to think that it is less popular than mySQL, but, I was wrong. When I started working on it, I found it much better mySQL in some scenarios, like - query optimization(using execution plan and statistics). Technically, Postgres is "better SQL" as it is more standardized. With better query planning, so that you can follow the relational model more closely if it suits you. Socially, MySQL has been very popular for a long time and many people are familiar with it. Postgres is neither owned by a major conglomerate with a qu

SQL Server Configuration Manager cannot connect to WMI

Sometime before I faced different kind of issue on one of staging SQL Server. I required to change the TCP port of an instance where multiple instance were running, I just open compmgmt.msc and connected SQLServer Configuration Manager , then I started receiving error : “ Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010], SQL Server Cannot Connect to WMI Provider permissions or server is unreachable. ” This may caused by an issue with the compiled SQL Configuration Manager GUI’s communication with WMI. If we have multiple instance running on SQL Server on a single windows server or we have un-installed any instances then this kind of issues we may face to connect.