[Maria-developers] Doubt in bin logging for CREATE VIEW
Hi All, I was checking the behavior of bin logging in various methods. I found out CREATE VIEW differs a little bit than others in bin logging. The query is written into bin log if query is executed successfully. If there is any error the query is not logged. This behavior is observed across all the commands. In case of CREATE VIEW (method: mysql_create_view file: sql/sql_view.cc), while the bin logging is skipped for most of the errors by jumping to "err" label, it is not skipped in following code snippet (line number: 616) res= mysql_register_view(thd, view, mode); /* View TABLE_SHARE must be removed from the table definition cache in order to make ALTER VIEW work properly. Otherwise, we would not be able to detect meta-data changes after ALTER VIEW. */ if (!res) tdc_remove_table(thd, TDC_RT_REMOVE_ALL, view->db, view->table_name, false); if (mysql_bin_log.is_open()) { // this is where the log is written } If an error occurs in mysql_register_view, e.g. ER_OUT_OF_RESOURCES, ER_TABLE_EXISTS_ERROR, etc. the query is still logged. Is there a specific reason why it is logged even though an error occurs? Or is it a bug? Thanks & Regards, Sriram
sriram patil <spsrirampatil@gmail.com> writes:
In case of CREATE VIEW (method: mysql_create_view file: sql/sql_view.cc), while the bin logging is skipped for most of the errors by jumping to "err" label, it is not skipped in following code snippet (line number: 616)
res= mysql_register_view(thd, view, mode);
If an error occurs in mysql_register_view, e.g. ER_OUT_OF_RESOURCES, ER_TABLE_EXISTS_ERROR, etc. the query is still logged.
Is there a specific reason why it is logged even though an error occurs? Or is it a bug?
Looks like a bug to me. If you want to verify it, you can make a test case in the following way: 1. Add a DBUG_EXECUTE_IF("simulate_register_view_failure") that causes mysql_register_view to return an error. 2. Create a test case with master-slave replication that sets simulate_register_view_failure in @@debug_dbug and attempts to create the view on the master. 3. If the bug is as it looks from the code and from your explanation, we should see that the view is missing from the master, but present on the slave. - Kristian.
Hi Kristian, I have not yet tried setting up the replication and writing the tests for it. I am reading about it. I will look into setting up the replication and writing test cases for it. Will get back to you when I have the test results ready. Thanks, Sriram On Thu, Jun 19, 2014 at 2:15 PM, Kristian Nielsen <knielsen@knielsen-hq.org> wrote:
sriram patil <spsrirampatil@gmail.com> writes:
In case of CREATE VIEW (method: mysql_create_view file: sql/sql_view.cc), while the bin logging is skipped for most of the errors by jumping to "err" label, it is not skipped in following code snippet (line number: 616)
res= mysql_register_view(thd, view, mode);
If an error occurs in mysql_register_view, e.g. ER_OUT_OF_RESOURCES, ER_TABLE_EXISTS_ERROR, etc. the query is still logged.
Is there a specific reason why it is logged even though an error occurs? Or is it a bug?
Looks like a bug to me.
If you want to verify it, you can make a test case in the following way:
1. Add a DBUG_EXECUTE_IF("simulate_register_view_failure") that causes mysql_register_view to return an error.
2. Create a test case with master-slave replication that sets simulate_register_view_failure in @@debug_dbug and attempts to create the view on the master.
3. If the bug is as it looks from the code and from your explanation, we should see that the view is missing from the master, but present on the slave.
- Kristian.
Hi Sriram. The good news ... nice catch, you earned your GSoC salary! The mysql_register_view() has a dozen different ways to fail: - RAM: my_error(ER_OUT_OF_RESOURCES, MYF(0)); - disk: sql_create_definition_file() - name collision - etc. So the create view statement can definitely fail on the master, and should not replicate when that happens. Note that warnings can also be set, so investigate if that affects replication: push_warning(thd, Sql_condition::WARN_LEVEL_WARN, ER_WARN_VIEW_MERGE, ER(ER_WARN_VIEW_MERGE)); The bad news ... I read over the create view source, and there's tons of work that hasn't been finished yet (just read the comments about union and restrict/ cascade for starters.) Another future project! Thanks, James. -------------------------------------------- On Thu, 6/19/14, sriram patil <spsrirampatil@gmail.com> wrote: Subject: [Maria-developers] Doubt in bin logging for CREATE VIEW To: maria-developers@lists.launchpad.net Date: Thursday, June 19, 2014, 12:54 AM Hi All, I was checking the behavior of bin logging in various methods. I found out CREATE VIEW differs a little bit than others in bin logging. The query is written into bin log if query is executed successfully. If there is any error the query is not logged. This behavior is observed across all the commands. In case of CREATE VIEW (method: mysql_create_view file: sql/sql_view.cc), while the bin logging is skipped for most of the errors by jumping to "err" label, it is not skipped in following code snippet (line number: 616) res= mysql_register_view(thd, view, mode); /* View TABLE_SHARE must be removed from the table definition cache in order to make ALTER VIEW work properly. Otherwise, we would not be able to detect meta-data changes after ALTER VIEW.*/ if (!res) tdc_remove_table(thd, TDC_RT_REMOVE_ALL, view->db, view->table_name, false); if (mysql_bin_log.is_open()) { // this is where the log is written} If an error occurs in mysql_register_view, e.g. ER_OUT_OF_RESOURCES, ER_TABLE_EXISTS_ERROR, etc. the query is still logged. Is there a specific reason why it is logged even though an error occurs? Or is it a bug? Thanks & Regards,Sriram -----Inline Attachment Follows----- _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Hi James, Well, I liked the good news part. :) I will share the test results with you too. And yeah, will look into the create view comments. Thanks, Sriram On Thu, Jun 19, 2014 at 2:48 PM, James Briggs <james.briggs@yahoo.com> wrote:
Hi Sriram.
The good news ... nice catch, you earned your GSoC salary!
The mysql_register_view() has a dozen different ways to fail:
- RAM: my_error(ER_OUT_OF_RESOURCES, MYF(0)); - disk: sql_create_definition_file() - name collision - etc.
So the create view statement can definitely fail on the master, and should not replicate when that happens.
Note that warnings can also be set, so investigate if that affects replication:
push_warning(thd, Sql_condition::WARN_LEVEL_WARN, ER_WARN_VIEW_MERGE, ER(ER_WARN_VIEW_MERGE));
The bad news ... I read over the create view source, and there's tons of work that hasn't been finished yet (just read the comments about union and restrict/ cascade for starters.)
Another future project!
Thanks, James.
-------------------------------------------- On Thu, 6/19/14, sriram patil <spsrirampatil@gmail.com> wrote:
Subject: [Maria-developers] Doubt in bin logging for CREATE VIEW To: maria-developers@lists.launchpad.net Date: Thursday, June 19, 2014, 12:54 AM
Hi All, I was checking the behavior of bin logging in various methods. I found out CREATE VIEW differs a little bit than others in bin logging. The query is written into bin log if query is executed successfully. If there is any error the query is not logged. This behavior is observed across all the commands.
In case of CREATE VIEW (method: mysql_create_view file: sql/sql_view.cc), while the bin logging is skipped for most of the errors by jumping to "err" label, it is not skipped in following code snippet (line number: 616)
res= mysql_register_view(thd, view, mode); /* View TABLE_SHARE must be removed from the table definition cache in order to make ALTER VIEW work properly. Otherwise, we would not be able to detect
meta-data changes after ALTER VIEW.*/ if (!res) tdc_remove_table(thd, TDC_RT_REMOVE_ALL, view->db, view->table_name, false); if (mysql_bin_log.is_open())
{ // this is where the log is written}
If an error occurs in mysql_register_view, e.g. ER_OUT_OF_RESOURCES, ER_TABLE_EXISTS_ERROR, etc. the query is still logged.
Is there a specific reason why it is logged even though an error occurs? Or is it a bug? Thanks & Regards,Sriram -----Inline Attachment Follows-----
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Hi Sriram. To clarify, the missing view features (union, restrict/cascade, subquery) are major projects outside the scope of your GSoC project, so don't bother spending time on it. It would take months just to test changes that large. Just admire how much is not done even after 20 years. :) Thanks, James. -------------------------------------------- On Thu, 6/19/14, sriram patil <spsrirampatil@gmail.com> wrote: Subject: Re: [Maria-developers] Doubt in bin logging for CREATE VIEW To: "James Briggs" <james.briggs@yahoo.com> Cc: "maria-developers@lists.launchpad.net" <maria-developers@lists.launchpad.net> Date: Thursday, June 19, 2014, 2:46 AM Hi James, Well, I liked the good news part. :) I will share the test results with you too. And yeah, will look into the create view comments. Thanks,Sriram On Thu, Jun 19, 2014 at 2:48 PM, James Briggs <james.briggs@yahoo.com> wrote: Hi Sriram. The good news ... nice catch, you earned your GSoC salary! The mysql_register_view() has a dozen different ways to fail: - RAM: my_error(ER_OUT_OF_RESOURCES, MYF(0)); - disk: sql_create_definition_file() - name collision - etc. So the create view statement can definitely fail on the master, and should not replicate when that happens. Note that warnings can also be set, so investigate if that affects replication: push_warning(thd, Sql_condition::WARN_LEVEL_WARN, ER_WARN_VIEW_MERGE, ER(ER_WARN_VIEW_MERGE)); The bad news ... I read over the create view source, and there's tons of work that hasn't been finished yet (just read the comments about union and restrict/ cascade for starters.) Another future project! Thanks, James.
Hi folks. I created this MDEV based on the "bad news" in my previous comment about create view (actually things are worse than I thought, since implementing the missing options will break user applications and affect replication backward compatibility): (MDEV-6365) CREATE VIEW Ignores RESTRICT/CASCADE Options - New Generally, statement options should not parse if they're ambiguous in syntax or code. Thanks, James. ---------- [...]
The bad news ... I read over the create view source, and there's tons of work that hasn't been finished yet (just read the comments about union and restrict/ cascade for starters.)
Hi, Sriram! On Jun 19, sriram patil wrote:
Hi All,
I was checking the behavior of bin logging in various methods. I found out CREATE VIEW differs a little bit than others in bin logging.
The query is written into bin log if query is executed successfully. If there is any error the query is not logged. This behavior is observed across all the commands.
In case of CREATE VIEW (method: mysql_create_view file: sql/sql_view.cc), while the bin logging is skipped for most of the errors by jumping to "err" label, it is not skipped ... Is there a specific reason why it is logged even though an error occurs? Or is it a bug?
Looks like a bug to me too. It's easy to see in the history - git blame shows that this binlog-writing chunk was added in commit e6eef5c1a765d27e1a8d1c7d707fb416dfa0ec2f. Commit comment and changes to other files in the same commit show that the goal was to move binlogging code inside mysql_create_view (and other similar functions), it was outside before. And before that commit no binlogging was done if mysql_create_view() failed. So, apparently that commit has introduced a bug that you've now found. Good job :) Regards, Sergei
Hi All, Indeed it is a bug. Here is what I did to verify. I installed MariaDB at two paths on my machine (I could have had multiple data dirs but that struck me later). Wrote a DBUG_EXECUTE_IF as suggested by Kristian in mysql_register_view, which returned ER_OUT_OF_RESOURCES. Ran one mysql server as master and the other as slave. And the result was clear. Even though CREATE VIEW returned error on the master, the VIEW was replicated at the slave. Have not written the test case yet but verified it manually. During this verification I also figured out that there is a crash too. If mysql_register_view returns ER_OUT_OF_RESOURCES. The *view->definer.host* and *view->definer.user* are not set. And the current code tries to write into bin log the details and crashes due to NULL pointer. Anyways, this will be fixed once the bin log issue is resolved. I have not yet figured out how to write automated test cases for replication, but will do that soon. Thanks, Sriram On Mon, Jun 23, 2014 at 3:01 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Sriram!
On Jun 19, sriram patil wrote:
Hi All,
I was checking the behavior of bin logging in various methods. I found out CREATE VIEW differs a little bit than others in bin logging.
The query is written into bin log if query is executed successfully. If there is any error the query is not logged. This behavior is observed across all the commands.
In case of CREATE VIEW (method: mysql_create_view file: sql/sql_view.cc), while the bin logging is skipped for most of the errors by jumping to "err" label, it is not skipped ... Is there a specific reason why it is logged even though an error occurs? Or is it a bug?
Looks like a bug to me too. It's easy to see in the history - git blame shows that this binlog-writing chunk was added in commit e6eef5c1a765d27e1a8d1c7d707fb416dfa0ec2f.
Commit comment and changes to other files in the same commit show that the goal was to move binlogging code inside mysql_create_view (and other similar functions), it was outside before. And before that commit no binlogging was done if mysql_create_view() failed.
So, apparently that commit has introduced a bug that you've now found. Good job :)
Regards, Sergei
sriram patil <spsrirampatil@gmail.com> writes:
I have not yet figured out how to write automated test cases for replication, but will do that soon.
Here is one existing test case that should be a good simple example as a starting point for this, when you get to it: mysql-test/suite/rpl/t/rpl_locale.test You run the test case like this (in mysql-test/ sub-directory): ./mtr rpl.rpl_locate There are many more existing tests with many more details, but this tests shows the basic of getting mysql-test-run to setup the two servers for replication, and for waiting for the slave to catch up with the master. Hope this helps, - Kristian.
participants (4)
-
James Briggs -
Kristian Nielsen -
Sergei Golubchik -
sriram patil