Yesterday friend of mine showed me this video:
http://www.youtube.com/watch?v=emgJtr9tIME
which is, at least for me, basically anti-commercial for MySQL. Author claims and then demonstrates how MySQL server will digest everything you give it regardless of data validity and how it will destroy or store incorrect data and do it all without ever raising any kind of error.
Now, I know that nothing is perfect on this world and that each product, of whichever kind, must have at least some issues. But, what bothered me in this particular case is incomplete story. That is something I really do not like - telling only one side of a story. Well, by this post, I will try to demonstrate another vantage point. It is not that I'm some great fan of MySQL but I have been using it along with Oracle, MSSQL and few more DBMS and I know it can get the job done. After all, I can testify that MySQL can even handle databases with over 1.000.000 tables containing several millions of records each....
So, let me first confirm all things that were demonstrated in video mentioned above.
Let's begin with repeating some of the steps - first I will create same table:
mysql> create table medals(
-> id int primary key auto_increment not null,
-> country varchar(50) not null,
-> event_name varchar(50) not null,
-> golds int not null
-> );
Query OK, 0 rows affected (0.00 sec)
And now I will replicate first data insert:
mysql> insert into medals (country) values ('sweden');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> select * from medals;
+----+---------+------------+-------+
| id | country | event_name | golds |
+----+---------+------------+-------+
| 1 | sweden | | 0 |
+----+---------+------------+-------+
1 row in set (0.00 sec)
So far so good - I'm getting the same result. Well, here I will only mention that it is true that MySQL will not raise an error but even in this case it did not simply and silently digest all data. If you take a better look at MySQL response for data insert you will notice that engine reported 2 warnings. I will come back to this a little bit later. For now, let me replicate few more issues demonstrated in the video.
mysql> alter table medals add bribes_paid decimal(10,2) not null;
Query OK, 1 row affected (0.01 sec)
mysql> select * from medals;
+----+---------+------------+-------+-------------+
| id | country | event_name | golds | bribes_paid |
+----+---------+------------+-------+-------------+
| 1 | sweden | | 0 | 0.99 |
+----+---------+------------+-------+-------------+
1 row in set (0.00 sec)
Once more, everything did happen as demonstrated in video. After column definition change actual data in database was modified to fit in new column definition. However, once more, MySQL did raise warning.
Finally,
mysql> update medals set golds = 'a lot' where id = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
There - I have successfully replicated steps done in video. Now let me ask one obvious question - why? Why is this happening and why is MySQL not raising errors and stopping query execution in those cases? Well, unlike many other DBMS engines, this behavior is something that is fully configurable in MySQL. Behavior or, simply put, the way MySQL will handle warnings and errors, is defined by sql_mode variable. And, as you can see in MySQL documentation, there are many options available:
http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html
So, this is simply a matter of style and wish. Let me first verify that my MySQL server was not set to be in strict mode:
And, now, let's say that I want MySQL server to raise an error when we try to give it bogus data. What I need to do to make this happen is rather easy - I will simply update MySQL configuration file (on my machine it is /etc/mysql/my.cnf file) and in it define sql_mode variable:
sql_mode = TRADITIONAL
After that, I only need to restart MySQL server and that's it. Well, just to be sure, let me verify this:
mysql> show global variables like '%mode%'\G
*************************** 1. row ***************************
Variable_name: innodb_autoinc_lock_mode
Value: 1
*************************** 2. row ***************************
Variable_name: innodb_strict_mode
Value: OFF
*************************** 3. row ***************************
Variable_name: slave_exec_mode
Value: STRICT
*************************** 4. row ***************************
Variable_name: sql_mode
Value: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE, NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
4 rows in set (0.00 sec)
(Note: Yes, I changed query output format to make data more readable...)
What do you think how will MySQL behave now? How will it react if I try to do all things I did previously? Hmmmm.... There is only one way to find out - try and see. And let me remind you once more that I made only single change here - I've only changed value of sql_mode variable. Nothing else.
So, let me start with famous data insert:
mysql> insert into medals (country) values ('srbija');
ERROR 1364 (HY000): Field 'event_name' doesn't have a default value
Hm, wait a second...MySQL actually raised and error?!?! Is this possible? And not only that it raised error but it also clearly pointed what the error was.
Let me move on and try other queries. I will now reset precision of bribes_paid field and then try once more to change column definition that might cause data loss.
mysql> alter table medals modify column bribes_paid decimal(10,2);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> update medals set golds = 'a lot' where id = 1;
ERROR 1366 (HY000): Incorrect integer value: 'a lot' for column 'golds' at row 1
Hmmmm.... As you can see, MySQL is now raising all errors and aborting all actions that would cause data corruption, incorrect data inserts and updates and so on. So, MySQL always "new" what was happening but it also obeyed user's directives regarding MySQL settings. At first, MySQL did not raise ERRORS but only WARNINGS because it was set that way. As soon as I changed this setting, MySQL changed behavior and started raising ERRORS.
You see, story is actually a little bit different from what was showed in video.
There are many programming styles and paradigms and sometimes developers want to separate data layer from business logic layer. In many cases, developers even want to create software that is not DBMS dependent. In those cases developers try to put all logic and all checks in business logic layer so they do not want database to raise errors and stop execution but only to notify them by raising warnings. They want to catch exceptions in business logic layer and not database layer. Actually, good developers would always use transactions ensuring that sql queries are actually committed and executed only and only if complete transaction was successful and error / warning free.
So, there you have it. It is true that, if you set it that way, MySQL will do all those nasty things with data. However, it is not single possible MySQL behavior and, as I clearly demonstrated, you can easily fine tune MySQL how to react on different events and when it will raise an error and when it will only raise warning. After all, I don't believe that, for example, you wash all your clothes using same wash machine program, let alone "default" one?? It is same thing with MySQL - if you want it to raise errors every time you try to make it do something incorrect or "stupid" simply set it up in that way.
http://www.youtube.com/watch?v=emgJtr9tIME
which is, at least for me, basically anti-commercial for MySQL. Author claims and then demonstrates how MySQL server will digest everything you give it regardless of data validity and how it will destroy or store incorrect data and do it all without ever raising any kind of error.
Now, I know that nothing is perfect on this world and that each product, of whichever kind, must have at least some issues. But, what bothered me in this particular case is incomplete story. That is something I really do not like - telling only one side of a story. Well, by this post, I will try to demonstrate another vantage point. It is not that I'm some great fan of MySQL but I have been using it along with Oracle, MSSQL and few more DBMS and I know it can get the job done. After all, I can testify that MySQL can even handle databases with over 1.000.000 tables containing several millions of records each....
So, let me first confirm all things that were demonstrated in video mentioned above.
Let's begin with repeating some of the steps - first I will create same table:
mysql> create table medals(
-> id int primary key auto_increment not null,
-> country varchar(50) not null,
-> event_name varchar(50) not null,
-> golds int not null
-> );
Query OK, 0 rows affected (0.00 sec)
And now I will replicate first data insert:
mysql> insert into medals (country) values ('sweden');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> select * from medals;
+----+---------+------------+-------+
| id | country | event_name | golds |
+----+---------+------------+-------+
| 1 | sweden | | 0 |
+----+---------+------------+-------+
1 row in set (0.00 sec)
So far so good - I'm getting the same result. Well, here I will only mention that it is true that MySQL will not raise an error but even in this case it did not simply and silently digest all data. If you take a better look at MySQL response for data insert you will notice that engine reported 2 warnings. I will come back to this a little bit later. For now, let me replicate few more issues demonstrated in the video.
mysql> alter table medals add bribes_paid decimal(10,2) not null;
Query OK, 1 row affected (0.01 sec)
mysql> update medals set bribes_paid = 1000 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from medals;
+----+---------+------------+-------+-------------+
| id | country | event_name | golds | bribes_paid |
+----+---------+------------+-------+-------------+
| 1 | sweden | | 0 | 1000.00 |
+----+---------+------------+-------+-------------+
1 row in set (0.00 sec)
mysql> alter table medals modify column bribes_paid decimal(2,2);
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 1
+----+---------+------------+-------+-------------+
| id | country | event_name | golds | bribes_paid |
+----+---------+------------+-------+-------------+
| 1 | sweden | | 0 | 0.99 |
+----+---------+------------+-------+-------------+
1 row in set (0.00 sec)
Once more, everything did happen as demonstrated in video. After column definition change actual data in database was modified to fit in new column definition. However, once more, MySQL did raise warning.
Finally,
mysql> update medals set golds = 'a lot' where id = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> select * from medals;
+----+---------+------------+-------+-------------+
| id | country | event_name | golds | bribes_paid |
+----+---------+------------+-------+-------------+
| 1 | sweden | | 0 | 0.99 |
+----+---------+------------+-------+-------------+
1 row in set (0.00 sec)
There - I have successfully replicated steps done in video. Now let me ask one obvious question - why? Why is this happening and why is MySQL not raising errors and stopping query execution in those cases? Well, unlike many other DBMS engines, this behavior is something that is fully configurable in MySQL. Behavior or, simply put, the way MySQL will handle warnings and errors, is defined by sql_mode variable. And, as you can see in MySQL documentation, there are many options available:
http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html
So, this is simply a matter of style and wish. Let me first verify that my MySQL server was not set to be in strict mode:
mysql> show global variables like '%mode%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | |
+--------------------------+--------+
4 rows in set (0.02 sec)And, now, let's say that I want MySQL server to raise an error when we try to give it bogus data. What I need to do to make this happen is rather easy - I will simply update MySQL configuration file (on my machine it is /etc/mysql/my.cnf file) and in it define sql_mode variable:
sql_mode = TRADITIONAL
After that, I only need to restart MySQL server and that's it. Well, just to be sure, let me verify this:
mysql> show global variables like '%mode%'\G
*************************** 1. row ***************************
Variable_name: innodb_autoinc_lock_mode
Value: 1
*************************** 2. row ***************************
Variable_name: innodb_strict_mode
Value: OFF
*************************** 3. row ***************************
Variable_name: slave_exec_mode
Value: STRICT
*************************** 4. row ***************************
Variable_name: sql_mode
Value: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE, NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
4 rows in set (0.00 sec)
(Note: Yes, I changed query output format to make data more readable...)
What do you think how will MySQL behave now? How will it react if I try to do all things I did previously? Hmmmm.... There is only one way to find out - try and see. And let me remind you once more that I made only single change here - I've only changed value of sql_mode variable. Nothing else.
So, let me start with famous data insert:
mysql> insert into medals (country) values ('srbija');
ERROR 1364 (HY000): Field 'event_name' doesn't have a default value
Hm, wait a second...MySQL actually raised and error?!?! Is this possible? And not only that it raised error but it also clearly pointed what the error was.
Let me move on and try other queries. I will now reset precision of bribes_paid field and then try once more to change column definition that might cause data loss.
mysql> alter table medals modify column bribes_paid decimal(10,2);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> update medals set bribes_paid = 1000 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from medals;
+----+---------+------------+-------+-------------+
| id | country | event_name | golds | bribes_paid |
+----+---------+------------+-------+-------------+
| 1 | sweden | | 0 | 1000.00 |
+----+---------+------------+-------+-------------+
1 row in set (0.00 sec)
mysql> alter table medals modify column bribes_paid decimal(2,2);
ERROR 1264 (22003): Out of range value for column 'bribes_paid' at row 1
Wow! I think we're on to something. Once more, MySQL did not perform query action, it raised error and, again, "explained" what was the cause of error. Just to verify that data was not changed or lost:
mysql> select * from medals;
+----+---------+------------+-------+-------------+
| id | country | event_name | golds | bribes_paid |
+----+---------+------------+-------+-------------+
| 1 | sweden | | 0 | 1000.00 |
+----+---------+------------+-------+-------------+
1 row in set (0.00 sec)
And, finally, let me test last point:
ERROR 1366 (HY000): Incorrect integer value: 'a lot' for column 'golds' at row 1
Hmmmm.... As you can see, MySQL is now raising all errors and aborting all actions that would cause data corruption, incorrect data inserts and updates and so on. So, MySQL always "new" what was happening but it also obeyed user's directives regarding MySQL settings. At first, MySQL did not raise ERRORS but only WARNINGS because it was set that way. As soon as I changed this setting, MySQL changed behavior and started raising ERRORS.
You see, story is actually a little bit different from what was showed in video.
There are many programming styles and paradigms and sometimes developers want to separate data layer from business logic layer. In many cases, developers even want to create software that is not DBMS dependent. In those cases developers try to put all logic and all checks in business logic layer so they do not want database to raise errors and stop execution but only to notify them by raising warnings. They want to catch exceptions in business logic layer and not database layer. Actually, good developers would always use transactions ensuring that sql queries are actually committed and executed only and only if complete transaction was successful and error / warning free.
So, there you have it. It is true that, if you set it that way, MySQL will do all those nasty things with data. However, it is not single possible MySQL behavior and, as I clearly demonstrated, you can easily fine tune MySQL how to react on different events and when it will raise an error and when it will only raise warning. After all, I don't believe that, for example, you wash all your clothes using same wash machine program, let alone "default" one?? It is same thing with MySQL - if you want it to raise errors every time you try to make it do something incorrect or "stupid" simply set it up in that way.
No comments:
Post a Comment