The Young DBA

Hi friend, I am continuing my post over my series article "Is it time to revisit PostgreSQL and MySQL with JSON support"  . Today I am sharing my 4th post which is related to " Changes in RDBMS after JSON " JSON bring changes on various vendors and technologies so new kind of challenges came to introducing this feature in their technology. To cover-up these challenge RDBMS started inclusion of JSON data type in their engine. Sooner or later all major RDBMS vendor (eg PostgreSQL, mySQL and SQL Server) added JSON as a data type to keeping document in relational table.  These are the database version when JASON introduced :- . Technology Postgres mySQL SQL Server Version PostgreSQL 9.3 MySQL 5.7.8 SQL 2016 Now a new heterogeneous database kind of structure started creating with relational engine. Static and dynamic data structure in RDBMS started introducing. Mean Relational Table =Normalization + denormalized (JSON/Array).  JSON data Passing done via stan

MySQL was created by a Swedish company, MySQL AB, founded by David Axmark, Allan Larsson and Michael "Monty" Widenius. Original development of MySQL by Widenius and Axmark began in 1994. The first version of MySQL appeared on 23 May 1995. Its name is a combination of "My", the name of co-founder Michael Widenius's daughter,and "SQL", the abbreviation for Structured Query Language. ·          23 May 1995 - First internal release ·          Year 1996 - Version 3 o     Simple CRUD operations o     January 1997 Windows version was released on 8 January 1998 for Windows 95 and NT o     production release 1998, from www.mysql.com ·          Year 2002 - Version 4 o     MyISAM o     unions o     Tracking o     B-trees o     subqueries o     prepared statements ·          Year 2005 - Version 5.0 o     cursors, stored procedures, triggers, views, o     Federated Storage Engine o     Event scheduler,  partitioning , p

Sometimes it happens that you need to ignore a table from backup due to any reason like 1. Table size is big, we keep log in that data. 2. Table that don't need to copy in backup. 3. Database copying from prod to test and some tables don't need in backup file. Generally when we create backup in single dump file it consolidate all db schema and data and if we need to exclude any table of sets of tables, then we need to use "--ignore-table" clause in mysqldump command. Syntax is as below. mysqldump -h <host> -u <username> -p <schema> --no-create-info --ignore-table=schema.table1  > db-data.sql Thanks for reading Plz dont forget to like Facebook Page.. https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

Hello Friends, How are you, hope you are doing good. Cold have been started in the country, specially northern India and I also started mysql 8.0 on AWS RDS instance. Yesterday I was trying to give full permission to new created user from all the IP in mysql 8, It started giving me error 1406 . I was using below query to give permission, as the same we were using earlier in all versions. grant all privileges on *.* to 'youngdbauser'@'%' with grant option; Above query gives error 1406 in mysql 8.0. After lots of trying and dig inside error and mysql portal. Then I created user with full permission in create statement only. This is the new statement with create user which I seen in mysql document portal. create user ''youngdbauser''@'%' identified by 'mypass'; grant all privileges on *.* to 'youngdbauser'@'%' with grant option; It resolved my issue. Summary : Now you can must specify the user's local during

Hello friends, Many MySQL developers and administrators are waiting since long for mysql 8.0 on AWS RDS.  Now Amazon RDS for MySQL started supports MySQL Community Edition major version 8.0 in all AWS Regions. Please upgrade your instance to test new product performance. MySQL 8.0 is the latest major version release and offers new query functionality and enhancements for better performance, reliability, security, manageability, and international and mobile support. Here is MySQL 8.0 Version Summary: MySQL Document Store. JSON enhancements. New Window functions. New Common Table Expression & Recursive CTE. Better with documents and JSON. New Database Roles. Index Hiding, a.k.a “Invisible” Indexes Configuration Persistence. Unicode UTF-8 Encoding. InnoDB NO WAIT & SKIP LOCKED. Thanks for reading Plz dont forget to like Facebook Page.. YoungDBA on Facebook

AWS documentation also says horizontal physical partitioning of large table data is best practices instead keeping all in one physical file. There are advantages and disadvantages to using InnoDB file-per-table tablespaces, depending on your application. To determine the best approach for your application, go to InnoDB File-Per-Table Mode in the MySQL documentation. We don't recommend allowing tables to grow to the maximum file size. In general, a better practice is to partition data into smaller tables, which can improve performance and recovery times. One option that you can use for breaking a large table up into smaller tables is partitioning. Partitioning distributes portions of your large table into separate files based on rules that you specify. For example, if you store transactions by date, you can create partitioning rules that distribute older transactions into separate files using partitioning. Then periodically, you can archive the historical transaction data th

Azure DB for MySQL released in the year 2017, this given the new advantages for those customers whose environment is SQL Server along with open source RDBMS. Now cloud database platform changes the technology parameter cutting-edge technology to bleeding edge technology, everything is being crossed platform. Recently one of the clients has thinking to shift their in-house MySQL to Azure environment. Their 70% database platform is on SQL Server and rest are on MySQL and MongoDB. We got chance to migrate existing database to Azure. The application was 3 years old and database was mix of the plug gable engine of  on tables like MyISAM, InnoDB, and csv. Like all other Azure services  MySQL  on AzureDB is also easy to setup in few clicks. We did the same but when we started migrating database, it raised the error " Azure doesn't support MyISAM ".  Actually, Azure did good for Database point of view but for existing system we required to do some changes. We changes the tab

When we run group by in mysql " group by list doesn't require as selected  list " . This is another myth and confusing for mysql users. There is a table structure and some test data which I ran on mysql and sql server simultaneously. Create table groupby_test ( id int,desg varchar (20), location varchar(20)); insert into groupby_test values (1,'Jr Software','Delhi'); insert into groupby_test values (2,'Jr Software','Noida'); insert into groupby_test values (3,'Software Software','Delhi'); insert into groupby_test values (4,'Sr Software Software','Gurgaon'); insert into groupby_test values (5,'Software Software','Gurgaon'); Then Executed below group by query on mysql and sql server. select desg, location from groupby_test group by desg; Result in MySQL; Result in SQLServer Msg 8120, Level 16, State 1, Line 9 Column 'groupby_test.location' is invalid in the selec

Hi, Many times mysql users asking me "show processlist showing list of many sleep queries on their environment". This post is for mysql user. Sleep meaning that thread is do nothing .  Its not a query waiting for connection. It's a connection pointer waiting for the timeout to terminate. and it doesn't have an impact on performance . To varify on your instance run below query.. show variables like '% wait_timeout%'   Then we will see setting is  default wait_timeout=28800 or something else as configured; So we can set values smaller,eg 10-20. We can also kill the thread status maked to sleep, that will not impact system. Thanks

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

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

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 ;

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

A MySQL event is a task that runs based on a predefined schedule therefore sometimes it is referred to as a scheduled event. MySQL event is also known as “temporal trigger” because it is triggered by time, not by table update like a trigger. A MySQL event is similar to a cron job in UNIX or a task scheduler in Windows. You can use MySQL events in many cases such as optimizing database tables, cleaning up logs, archiving data, or generate complex reports during off-peak time.

When we start developing a database, we have to use the most appropriate data types for all the columns to store data values of the respective tables in it. For dealing with the floating type data values, most of the times, we get confused about which data type to go for, as we have Float, Decimal and Double as well. I, myself, as a new DBA, got confused about the usage of the same. So, i have re-searched through some websites over internet and have concluded some facts about these data types' usage. i want to share my experience with all of you.

MySQL is very handy database and tool to manage than sql server.  Today I will backup Mysql Database from command prompt. So 1st switch to bin folder c:\programfiles\mysql\mysql server 5.5\bin:\> Now give command mysqldump -u root -p theYoungDBA d:\data\dump\ theYoungDBA .sql ;;;here < theYoungDBA > is the database which backup we will took. <d:\data\dump\ theYoungDBA .sql> is the target path where backup dump will keep. Thanks https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

During database review most useful object I used information_ schema and show tables. Now I feel ease to work on mysql, either from command line or most popular tool workbench.  Since 2014 mysql added into my skill set and till now 3-4 projects.  Last year I have posted   5-important-shortcut-and-tips-to-work on workbench and today I am sharing  5 more commands what I am using for development and review on mysql database projects. 1.        How to insert or populate data in one table from another table? 2.        How to update one table from another table? 3.        How to create and use temporary table? 4.        How to Check Table Property from command line? 5.        How to check all indexes from one table? How to insert or populate data in one table  from another table? insert into user2  SELECT * FROM setting.user; How to update one table from another table? UPDATE temp p, word pp SET pp.name= p.name, pp.describe = p.describe WHERE pp.id = p.id;

Friends, Till now I haven’t got chance to vote in any election for PM or CM. This month everywhere talking about elections and here I am busy with my work connect-share-discover with database and applications..  Today I want to share How to Create Read-Only User in MySQL using workbench. First Go to Users and Privileges option from Workbench Managements Tab. Click on Add account and give account name and their respective password, after that apply. In Users and Privileges box there are 2 other tabs, Administrative Roles and schema privileges. Administrative Roles is Similar to Fixed Server roles of SQL Server. Schema privileges is schema level authorization process. Select Schema privileges and click on Add Entry. Then a Dialog box will come with schema available in server. Now select any schema which privilege we have to configure. After that give the appropriate rights which we have to give. As I have selected on select. Finally click on Apply.

Last Month Faced an Issue on one of MySQL DB Server. System Date was showing wrong while System time was correct. I compared both time like below:

Workbench is a free tool for MySQL but it is collection of issues in working environment. So I came back on SQL Server management studio. But think if our requirement is something like what , data will be at same place in MySQL and we have to access from SQL Server. Then we can use SQL Server's  Server object’s Link server .  Today I am going to share 7 steps how I have made linked server from SQL Server to MySQL.