Sometimes you may need to add a column at a particular ordinal position in the table. In SQL Server there is no option to do it via SQL although you can use Designer from SQL Server Management Studio. In MySQL you can do it as part of ALTER TABLE statement.
Suppose you want to add another column named modified_date between item_id and sales_date, you can use the following command
ALTER TABLE sales ADD COLUMN modified_date DATETIME after item_id;
Now the new column named modified_date will be added after item_id. You can check this using the following command
Field Type Null Key Default Extra
sales_id int(11) NO PRI auto_increment
item_id int(11) YES
modified_date datetime YES
sales_date datetime YES
sales_amount decimal(12,2) YES
This is sometimes useful. In relational database the order of column in a table should not matter. The database design and modeling should be such that there should not be any dependency on the order the columns are created inside MySQL table. This is the reason, in SQL Server we can’t do this operation via T-SQL and when we do with the help of SSMS, it practically creates and drop the entire table in the background – (a very bad thing).
MySQL supports Dynamic SQL with the help of EXECUTE and PREPARE statements. Suppose you have a scenario where you need to pass table name as parameter value and returns all column values, you can use Dynamic SQL.
Let us create this table and data.
CREATE TABLE TestTable (ID INT, Col VARCHAR(4)); INSERT INTO TestTable (ID, Col) SELECT 1, 'A' UNION ALL SELECT 1, 'B' UNION ALL SELECT 1, 'C' UNION ALL SELECT 2, 'A' UNION ALL SELECT 2, 'B' UNION ALL SELECT 2, 'C' UNION ALL SELECT 2, 'D' UNION ALL SELECT 2, 'E';
Now pass the table name as a parameter and returns all rows from it.
SET @table_name:='TestTable'; SET @sql:=CONCAT('SELECT * FROM ',@table_name); PREPARE dynamic_statement FROM @sql; EXECUTE dynamic_statement; DEALLOCATE PREPARE dynamic_statement;
The variable @table_name is assigned name of the table. The variable @sql forms SELECT statement concatenating the Table name so the actual SELECT statement becomes SELECT * FROM TestTable. The PREPARE statement prepares the statement for execution and assigns a name (in this case it is dynamic_statement). EXECUTE command executes the statement prepared by the prepared name dynamic_statement and produces the result. The DEALLOCATE command releases the prepared statement.
Data is the one of the most crucial things for any organization and keeping data safe is the biggest challenge for any DBA. This is true for any organizations. Think about the scenario that you have a database which is extremely important and suddenly you accidently delete the most important table from that database. I am sure this is a very difficult time. In times like this people often get stressed or just make even second mistake. In my career of 10 years I have done often this mistake and often got stressed out due to un-availability of the database backup.
In the SQL Server field, we have plenty of the help on this subject, but in MySQL domain there is not enough help. For the same reason I have build this MySQL course on Backup and Recovery.
Data is very important to any application and business. It is very important that every business plan for data safety. Database backup strategies are often discussed after the disaster has already happened. In this introductory course we will explore a few of the basic backup strategies every business should implement for data safely. We will explore how we can recover our server quickly after any unfriendly incident to our MySQL database. Click to View Course
Here are various important aspects which we have discussed in this course.
How to take backup of single database?
How to take backup of multiple database?
How to backup various database objects?
How to restore a single database?
How to restore multiple databases?
How to use MySQL Workbench for Backup and Restore?
How to restore Point in Time for any database?
What is the best time to backup?
How to copy database from one server to another server?
As learning about Backup and Recovery can be very much boring, I decided to create two fictitious characters and demonstrate the entire course based on their conversation. The story is about Mike and Rahul. Mike is Sr. Database administrator in USA and Rahul is an intern in India. Rahul aspires to become a senior database administrator and this is a story about his challenges and how he overcomes those challenges. I had a great time to build this course and I have got very good feedback on this course. I encourage all of you to attempt to learn MySQL Backup and Recovery Fundamental course with this innovative effort. It will be very valuable to know your feedback.
You will need a valid Pluralsight subscription to watch this course.
MySQL supports user defined variables to have some data that can be used later part of your query. You can save a value to a variable using a SELECT statement and later you can access its value.
Unlike other RDBMSs, you do not need to declare the data type for a variable. The data type is automatically assumed when you assign a value. A value can be assigned to a variable using a SET command as shown below
When you above command is executed, the value, MySQL is assigned to the variable called @server_type. Now you can use this variable in the later part of the code. Suppose if you want to display the value, you can use SELECT statement.
The result is MySQL. Once the value is assigned it remains for the entire session until changed by the later statements. So unlike SQL Server, you do not need to have this as part the execution code every time. (Because in SQL Server, the variables are execution scoped and dropped after the execution).
You can give column name as below
SELECT @server_type AS server_type;
You can also SELECT statement to DECLARE and SELECT the values for a variable.
The question of the day: “What can you do when a MySQL database needs to scale write-intensive workloads beyond the capabilities of the largest available machine on Amazon RDS?”
Let’s take a look.
In a typical EC2/RDS set-up, users connect to app servers from their mobile devices and tablets, computers, browsers, etc. Then app servers connect to an RDS instance (web/cloud services) and in some cases they might leverage some read-only replicas.
Figure 1. A typical RDS instance is a single-instance database, with read replicas. This is not very good at handling high write-based throughput.
As your application becomes more popular you can expect an increasing number of users, more transactions, and more accumulated data. User interactions can become more challenging as the application adds more sophisticated capabilities. The result of all this positive activity: your MySQL database will inevitably begin to experience scalability pressures.
What can you do?
Broadly speaking, there are four options available to improve MySQL scalability on RDS.
1. Larger RDS Instances – If you’re not already using the maximum available RDS instance, you can always scale up – to larger hardware. Bigger CPUs, more compute power, more memory et cetera. But the largest available RDS instance is still limited. And they get expensive.
“High-Memory Quadruple Extra Large DB Instance”:
68 GB of memory
26 ECUs (8 virtual cores with 3.25 ECUs each)
High I/O Capacity
Provisioned IOPS Optimized: 1000Mbps
2. Provisioned IOPs – You can get provisioned IOPs and higher throughput on the I/O level.
However, there is a hard limit with a maximum instance size and maximum number of provisioned IOPs you can buy from Amazon and you simply cannot scale beyond these hardware specifications.
3. Leverage Read Replicas – If your application permits, you can leverage read replicas to offload some reads from the master databases. But there are a limited number of replicas you can utilize and Amazon generally requires some modifications to your existing application.
And read-replicas don’t help with write-intensive applications.
4. Multiple Database Instances – Amazon offers a fourth option:
“You can implement partitioning,thereby spreading your data across multiple database Instances” (Link)
However, Amazon does not offer any guidance or facilities to help you with this. “Multiple database instances” is not an RDS feature. And Amazon doesn’t explain how to implement this idea.
In fact, when asked, this is the response on an Amazon forum:
Q: Is there any documents that describe the partition DB across multiple RDS?
I need to use DB with more 1TB but exist a limitation during the create process, but I read in the any FAQ that you need to partition database, but I don’t find any documents that describe it.
A: “DB partitioning/sharding is not an official feature of Amazon RDS or MySQL, but a technique to scale out database by using multiple database instances. The appropriate way to split data depends on the characteristics of the application or data set. Therefore, there is no concrete and specific guidance.”
So now what?
The answer is to scale out with ScaleBase.
Amazon RDS with ScaleBase: What you get – MySQL Scalability!
ScaleBase is specifically designed to scale out a single MySQL RDS instance into multiple MySQL instances.
Critically, this is accomplished with no changes to your application code. Your application continues to “see” one database. ScaleBase does all the work of managing and enforcing an optimized data distribution policy to create multiple MySQL instances.
With ScaleBase, data distribution, transactions, concurrency control, and two-phase commit are all 100% transparent and 100% ACID-compliant, so applications, services and tooling continue to interact with your distributed RDS as if it were a single MySQL instance.
The result: now you can cost-effectively leverage multiple MySQL RDS instance to scale out write-intensive workloads to an unlimited number of users, transactions, and data.
Amazon RDS with ScaleBase: What you keep – Everything!
And how does this change your Amazon environment?
1. Keep your application, unchanged – There is no change your application development life-cycle at all. You still use your existing development tools, frameworks and libraries. Application quality assurance and testing cycles stay the same. And, critically, you stay with an ACID-compliant MySQL environment.
2. Keep your RDS value-added services – The value-added services that you rely on are all still available. Amazon will continue to handle database maintenance and updates for you. You can still leverage High Availability via Multi A-Z. And, if it benefits youra application throughput, you can still use read replicas.
3. Keep your RDS administration – Finally the RDS monitoring and provisioning tools you rely on still work as they did before.
With your one large MySQL instance, now split into multiple instances, you can actually use less expensive, smallersmaller available RDS hardware and continue to see better database performance.
Amazon RDS is a tremendous service, but it doesn’t offer solutions to scale beyond a single MySQL instance. Larger RDS instances get more expensive. And when you max-out on the available hardware, you’re stuck. Amazon recommends scaling out your single instance into multiple instances for transaction-intensive apps, but offers no services or guidance to help you. This is where ScaleBase comes in to save the day.
It gives you a simple and effective way to create multiple MySQL RDS instances, while removing all the complexities typically caused by “DIY” sharding andwith no changes to your applications .
With ScaleBase you continue to leverage the AWS/RDS ecosystem: commodity hardware and value added services like read replicas, multi A-Z, maintenance/updates and administration with monitoring tools and provisioning.
Developer’s life is never easy. DBA’s life is even crazier.
When a developer wakes up in the morning, most of the time have no idea what different challenges they are going to face that day. Of course, most of the developers know the project and roadmap, which they are working on. However, developers have no clue what coding challenges which they are going face for that day.
DBA’s life is even crazier. When DBA wakes up in the morning – they often thank that they were not disturbed during the night due to server issues. The very next thing they wish is that they do not want to challenge which they can’t solve for that day. The problems DBA face every single day are mostly unpredictable and they just have to solve them as they come during the day.
Though the life of DBA is not always bad. There are always ways and methods how one can overcome various challenges. Let us see three of the challenges and how a DBA can use various tools to overcome them.
Challenge #1 Synchronize Data Across Server
A Very common challenge DBA receive is that they have to synchronize the data across the servers. If you try to manually write that up, it may take forever to accomplish the task. It is nearly impossible to do the same with the help of the T-SQL. However, thankfully there are tools like dbForge Studio which can save a day and synchronize data across servers. Read my detailed blog post about the same over here: SQL SERVER – Synchronize Data Exclusively with T-SQL.
Challenge #2 SQL Report Builder
DBA’s are often asked to build reports on the go. It really annoys DBA’s, but hardly people care about it. No matter how busy a DBA is, they are just called upon to build reports on things on very short notice. I personally like to avoid any task which is given to me accidently and personally building report can be boring. I rather spend time with High Availability, disaster recovery, performance tuning rather than building report. I use SQL third party tool when I have to work with SQL Report. Others have extended reporting capabilities. The latter group of products includes the SQL report builder built-in todbForge Studio for SQL Server. I have blogged about this earlier over here: SQL SERVER – SQL Report Builder in dbForge Studio for SQL Server.
Challenge #3 Work with the OTHER Database
The manager does not understand that MySQL is different from SQL Server and SQL Server is different from Oracle. For them everything is same. In my career hundreds of times I have faced a situation that I am given a database to manage or do some task when their regular DBA is on vacation or leave. When I try to explain I do not understand the underlying the technology, I have been usually told that my manager has trust on me and I can do anything. Honestly, I can’t but I hardly dare to argue. I fall back on the third party tool to manage database when it is not in my comfort zone. For example, I was once given MySQL performance tuning task (at that time I did not know MySQL so well). To simplify search for a problem query let us use MySQL Profiler in dbForge Studio for MySQL. It provides such commands as a Query Profiling Mode and Generate Execution Plan. Here is the blog post discussing about the same: MySQL – Profiler : A Simple and Convenient Tool for Profiling SQL Queries.
Well, that’s it! There were many different such occasions when I have been saved by the tool. May be some other day I will write part 2 of this blog post.
We all start somewhere when it is about database. There are different reasons, why we go for one database over another database. Usually the reason is cost and convenience. After a period of time when business is successful and traffic is growing, the same two reasons of cost and convenience start to become secondary goals. I have seen quite a lot of companies starting with free databases and after a while switching to another database as they want stability and service from the product company.
Microsoft has an excellent product which lets you migrate your database from the alternate database to SQL Server. It is called SQL Server Migration Assistant (SSMA) and earlier this week, it has been upgraded to support SQL Server 2014. Now you can migrate from your database to to all editions of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014. SQL Server Migration Assistant (SSMA) is a free supported tool from Microsoft.
Here is where you can download SSMA v5.3 for various databases.
MySQL supports Temporary tables to store the resultsets temporarily for a given connection. Temporary tables are created with the keyword TEMPORARY along with the CREATE TABLE statement.
Let us create the temporary table named Temp
CREATE TEMPORARY TABLE TEMP (idINT);
Now you can find out the column names using DESC command
The above returns the following result
This table can be accessed only for the current connection and it can be used like a permanent table and automatically dropped when the connection is closed. However, you can not find temporary tables using INFORMATION_SCHEMA. TABLES system view. It will only list out the permanent tables.
MySQL usually stores the data of temporary tables in memory and processed by Memory Storage engine. But if the data size is too large MySQL automatically converts this to the on – disk table and use MyISAM engine.
You can also create a permanent table with the same name of a temporary table in the same connection. However the structure of permanent table is visible only if the temporary table with the same name is dropped.
Let us create a permanent table with the same name Temp as below
CREATE TABLE TEMP (idINT, namesVARCHAR(100));
Now running the following command stills gives you the structure of the temporary table temp created earlier.
You can drop the temporary table using DROP TEMPORARY TABLE command;
DROP TEMPORARY TABLE TEMP;
After you executed the temporary table, run the following command
Now you will see the structure of the permanent table named temp
In summary – If there is a Temporary Table in MySQL it gets first priority over the permanent table in the session.
The Great Indian Developer Conference (GIDS) is one of the most popular annual event held in Bangalore. This year GIDS is scheduled on April 22, 25. I will be presented total four sessions at this event and each session is very different from each other. Here are the details of four of my sessions, which I presented there.
This event was a great event and I had fantastic fun presenting a technology over here. I was indeed very excited that along with me, I had many of my friends presenting at the event as well. I want to thank all of you to attend my session and having standing room every single time. I have already sent resources in my newsletter. You can sign up for the newsletter over here.
Indexing is an Art
I was amazed with the crowd present in the sessions at GIDS. There was a great interest in the subject of SQL Server and Performance Tuning.
Audience at GIDS
I believe event like such provides a great platform to meet and share knowledge.
Pinal at Pluralsight Booth
Here are the abstract of the sessions which I had presented. They were recorded so at some point in time they will be available, but if you want the content of all the courses immediately, I suggest you check out my video courses on the same subject on Pluralsight.
Slow Running Queries are the most common problem that developers face while working with SQL Server. While it is easy to blame SQL Server for unsatisfactory performance, the issue often persists with the way queries have been written, and how Indexes has been set up. The session will focus on the ways of identifying problems that slow down SQL Server, and Indexing tricks to fix them. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session.
Indexes are the most crucial objects of the database. They are the first stop for any DBA and Developer when it is about performance tuning. There is a good side as well evil side to indexes. To master the art of performance tuning one has to understand the fundamentals of indexes and the best practices associated with the same. We will cover various aspects of Indexing such as Duplicate Index, Redundant Index, Missing Index as well as best practices around Indexes.
SQL Server Performance Troubleshooting: Ancient Problems and Modern Solutions
Many believe Performance Tuning and Troubleshooting is an art which has been lost in time. However, truth is that art has evolved with time and there are more tools and techniques to overcome ancient troublesome scenarios. There are three major resources that when bottlenecked creates performance problems: CPU, IO, and Memory. In this session we will focus on High CPU scenarios detection and their resolutions. If time permits we will cover other performance related tips and tricks.
At the end of this session, attendees will have a clear idea as well as action items regarding what to do when facing any of the above resource intensive scenarios. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. To master the art of performance tuning one has to understand the fundamentals of performance, tuning and the best practices associated with the same. We will discuss about performance tuning in this session with the help of Demos.
Performance is one of the most essential aspects of any application. Everyone wants their server to perform optimally and at the best efficiency. However, not many people talk about MySQL and Performance Tuning as it is an extremely unexplored territory. In this session, we will talk about how we can tune MySQL Performance. We will also try and cover other performance related tips and tricks. At the end of this session, attendees will not only have a clear idea, but also carry home action items regarding what to do when facing any of the above resource intensive scenarios. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. To master the art of performance tuning one has to understand the fundamentals of performance, tuning and the best practices associated with the same. You will also witness some impressive performance tuning demos in this session.
Hidden Secrets and Gems of SQL Server We Bet You Never Knew
It really amazes us every time when someone says SQL Server is an easy tool to handle and work with. Microsoft has done an amazing work in making working with complex relational database a breeze for developers and administrators alike. Though it looks like child’s play for some, the realities are far away from this notion. The basics and fundamentals though are simple and uniform across databases, the behavior and understanding the nuts and bolts of SQL Server is something we need to master over a period of time.
With a collective experience of more than 30+ years amongst the speakers on databases, we will try to take a unique tour of various aspects of SQL Server and bring to you life lessons learnt from working with SQL Server. We will share some of the trade secrets of performance, configuration, new features, tuning, behaviors, T-SQL practices, common pitfalls, productivity tips on tools and more.
This is a highly demo filled session for practical use if you are a SQL Server developer or an Administrator. The speakers will be able to stump you and give you answers on almost everything inside the Relational database called SQL Server.