MySQL Bugs: #45249: Backup database fails with error " Can't enumerate stored routines in database"

Bug #45249	Backup database fails with error " Can't enumerate stored routines in database"
Submitted:	1 Jun 2009 18:56	Modified:	28 Oct 2009 22:17
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	mysql-6.0-backup, mysql-6.0	OS:	Linux
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
Create databases db1 and db2
Create tables and stored functions in db1 and db2 using grammar file.
Create reporter.pm file which will execute periodic backup and restore operation
Execution of backup fails with an error "Can't enumerate stored routines in database 'db'"

How to repeat:
Run the following script with RQG load as follows,

perl runall.pl --basedir=/export/home/tmp/wl4195c/mysql-6.0-backup/ --grammar=/export/home/tmp/perl_new/mysql-test-extra-6.0/mysql-test/gentest/conf/mytest2_onlyfun.yy --reporter=inter_backup

The test fails at backup only with random generation of stored functions and not stored procedures.

I am not able to find the root cause for this failure. I will keep debugging further and add more comments if I find any.

Please take a look at the attached grammar file

Attachment: mytest2_onlyfun.yy (application/octet-stream, text), 1.52 KiB.

The reporter file is attached below:

Attachment: inter_backup.pm (application/octet-stream, text), 3.06 KiB.

Thank you for the report.

Verified as described.

Note that error talks about database 'db' but grammar works with databases 'db1' and 'db2'. Is it a typo or indicates a problem?

This is typo. Correct error is:

DBD::mysql::db do failed: Can't enumerate stored routines in database `db2` at lib/GenTest/Reporter/inter_backup.pm line 25.

How can one run this test? Please include directions on how to run this test to show this bug. I have the mysql-test-extra-6.0 tree, but see no way to run the test without errors. Also, does this require a specific platform? Can it run on Windows and if so how?

Please read about usage at http://forge.mysql.com/wiki/RQG

RQG can run on any platform with Perl and modules DBI and DBD::mysql are installed including Windows.

It's a problem in the generator. It is attempting to create a function with a reserved name. I was able to trace the problem to the si_objects.cc code which is designed to have backup fail if there are warnings returned from a query of IS tables. Thus, the cannot enumerate error from backup.

Here is a manual representation of the error generated in the random query generator:

mysql> CREATE FUNCTION y () RETURNS INT RETURN (SELECT COUNT(*) FROM db1.table_or_view) ;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Note  | 1585 | This function 'y' has the same name as a native function | 
+-------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

Note the warning generated from attempting to create a function with a name of 'y'. 

The generator should not use a known list of reserved names for any object.

Here is a manual example of how the backup code in si_objects.cc fails:

mysql> SELECT routine_name, routine_schema FROM INFORMATION_SCHEMA.ROUTINES;
+-----------------+----------------+
| routine_name    | routine_schema |
+-----------------+----------------+
| add_suppression | mtr            | 
| check_testcase  | mtr            | 
| check_warnings  | mtr            | 
| force_restart   | mtr            | 
| n               | test           | 
| y               | test           | 
+-----------------+----------------+
6 rows in set, 1 warning (0.04 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Note  | 1585 | This function 'y' has the same name as a native function | 
+-------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

Here is the code that is designed to fail in this instance:

template <typename Iterator>
Iterator *create_row_set_iterator(THD *thd, const LEX_STRING *query)
{
  Ed_connection ed_connection(thd);
  Ed_result_set *ed_result_set;
  Iterator *it;

  if (run_service_interface_sql(thd, &ed_connection, query) ||
      ed_connection.get_warn_count())
  {
    /* There should be no warnings. */
    return NULL;
  }

  DBUG_ASSERT(ed_connection.get_field_count());

  /* Use store_result to get ownership of result memory */
  ed_result_set= ed_connection.store_result_set();

  if (! (it= new Iterator(ed_result_set)))
  {
    delete ed_result_set;
    return NULL;
  }

  return it;
}

Which is called from:

Obj_iterator *get_db_stored_procedures(THD *thd, const String *db_name)
{
  String_stream s_stream;
  s_stream <<
    "SELECT '" << db_name << "', routine_name "
    "FROM INFORMATION_SCHEMA.ROUTINES "
    "WHERE routine_schema = '" << db_name << "' AND "
    "routine_type = 'PROCEDURE'";

  return create_row_set_iterator<Db_stored_proc_iterator>(thd, s_stream.lex_string());
}

///////////////////////////////////////////////////////////////////////////

Obj_iterator *get_db_stored_functions(THD *thd, const String *db_name)
{
  String_stream s_stream;
  s_stream <<
    "SELECT '" << db_name << "', routine_name "
    "FROM INFORMATION_SCHEMA.ROUTINES "
    "WHERE routine_schema = '" << db_name <<"' AND "
    "routine_type = 'FUNCTION'";

  return create_row_set_iterator<Db_stored_func_iterator>(thd, s_stream.lex_string());
}

I've found a workaround. If one does this in the grammar file:

function_name:
           letter;

Then the letter 'Y' will be used. However, if the grammar file contains something more reasonable like:

function_name:

           f1 | f2 | f3 | f4 | f5 | f6 | f7 |f8 | f9 | f10 ;

Then the warning is not generated because 'y' is not used and therefore backup does not crash as a result of a random query. ;)

Recommendations: 
a) We close this bug and document the limitation
- or -
b) Assign the bug to someone working on the random query generator and modify it to exclude known reserved names.

We need to document this limitation with the backup feature that backup will fail when objects are created using reserved names. In any case, I feel that the error message reported for this issue is confusing and should be modified.

I think that if a user is able to create a function with a certain name, we should be able to backup/restore that function.

I agree to the above comment. When user has an ability to create function then one should be able to execute backup / restore as well. On the otherhand, when a procedure is created using reserved words, this problem doesn't exist and backup is successful.  I strongly feel that this bug needs to be fixed instead of documenting with the limitation.

The problem is that these two methods return NULL if their queries generate warnings: 

* si_objects.cc#create_row_set_iterator 
* si_objects.cc#Stored_program_obj::do_serialize

Changing the code in do_serialize (similar in create_row_set_iterator) to

if (run_service_interface_sql(thd, &ed_connection, s_stream.lex_string()) ||
    ed_connection.get_warn_count())
{
  // Begin modified code
  thd->warning_info->append_warnings(thd, ed_connection.get_warn_list());
  //  DBUG_RETURN(TRUE);
  // End modified code
}

...I am able to make BACKUP/RESTORE complete successfully:

mysql> BACKUP DATABASE db TO '1.bak';
backup_id
276
Warnings:
Note	1585	This function 'y' has the same name as a native function
Note	1585	This function 'y' has the same name as a native function
Note	1585	This function 'y' has the same name as a native function

mysql> RESTORE FROM '1.bak' OVERWRITE;
backup_id
277
Warnings:
Note	1585	This function 'y' has the same name as a native function
mysql> SELECT routine_name FROM INFORMATION_SCHEMA.ROUTINES where routine_schema='db';
routine_name
y
Warnings:
Note	1585	This function 'y' has the same name as a native function

Two questions remain:

1) Warning 1585 is shown three times for the BACKUP command because the two modified methods are called multiple times for each function. Is this a problem we need to fix?
2) There are other places in si_object.cc that return NULL in the same way that cause this problem. Are there any scenarios where it is OK to proceed in the presence of a warning from any of these? I.e, should any of these methods be changed as well:
 a) Database_object::do_serialize ("show create database" statement)
 b) Table_obj::do_serialize       ("show create table" statement)
 c) get_view_create_stmt          ("show create view" statement)
 d) check_user_existence          (select from I_S)
 e) find_tablespace               (select from I_S)
 f) find_tablespace_for table     (select form I_S)
 g) num_slaves_attached           (select from I_S)

Replies to questions above:

1) I think the error message should be reported only once.
2) I do not think this bug should attempt to fix other issues like this.
   I think it is fine for the bug to fix only the procedure and function
   return calls for warnings. Others are yet to be determined if they
   are needed.

My opinions:

1) Agree that only one message should be shown, but I expect it to be difficult to implement, possibly requiring re-thinking of how errors/warnings are handled by si_objects. So, I'd be inclined to accept this ugliness for now.

2) My guts feeling is that it is safe to ignore warnings in all these si_objects functions. I lean toward ignoring warnings everywhere and see if any problems will appear because of that.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76837

2827 Jorgen Loland	2009-06-22
      Bug#45249 - Backup database fails with error "Can't enumerate stored routines in database"
      
      Before, BACKUP would fail with an error if a database contained a function with a name already used (e.g, 'y'). The reason for this was that the when BACKUP queried the server for information about this function, the server reported a warning. A warning was automatically treated by BACKUP as failure.
      
      Now, the warning is pushed to the BACKUP error stack, and BACKUP continues.
     @ mysql-test/suite/backup/r/backup_functions.result
        Add function with same name as a native function ('y'). This will create a warning for BACKUP when processing the function. This warning should not make BACKUP fail.
     @ mysql-test/suite/backup/t/backup_functions.test
        Add function with same name as a native function ('y'). This will create a warning for BACKUP when processing the function. This warning should not make BACKUP fail.
     @ mysql-test/suite/backup/t/backup_views.test
        Link error message to a bug.
     @ sql/si_objects.cc
        Push warning from server to BACKUP error stack without automatically failing when query for stored procedure information succeeds with a warning.

Patch approved (see commit reply).

Good to push.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76992

2832 Jorgen Loland	2009-06-24
      Bug#45249 - Backup database fails with error "Can't enumerate
                  stored routines in database"
            
      Before, BACKUP would fail with an error if a database contained a
      function with a name already used (e.g, 'y'). The reason for this
      was that the when BACKUP queried the server for information about
      this function, the server reported a warning. A warning was
      automatically treated by BACKUP as failure.
     @ mysql-test/suite/backup/r/backup_functions.result
        Add function with same name as a native function ('y'). This will
        create a warning for BACKUP when processing the function. This
        warning should not make BACKUP fail.
     @ mysql-test/suite/backup/t/backup_functions.test
        Add function with same name as a native function ('y'). This will
        create a warning for BACKUP when processing the function. This
        warning should not make BACKUP fail.
     @ mysql-test/suite/backup/t/backup_views.test
        Link error message to a bug.
     @ sql/si_objects.cc
        Push warning from server to BACKUP error stack without
        automatically failing when query for stored procedure information
        succeeds with a warning.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77276

2834 Jorgen Loland	2009-06-26
      Bug#45249 - Backup database fails with error 
                  "Can't enumerate stored routines in database"
      
      Followup patch due to PB2 test error
      
      SELECT from function y() produces an error on most platforms, but 
      not all. The test added for this bug therefore fails on some 
      platforms in PB2. This patch disables warnings in 
      backup_functions.test when selecting from function y().
     @ mysql-test/suite/backup/t/backup_functions.test
        disable warnings when selecting from function y() since this produces a warning only on some platforms.

Followup fix good to push provided that result set changes are also included.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77277

2834 Jorgen Loland	2009-06-26
      Bug#45249 - Backup database fails with error 
                  "Can't enumerate stored routines in database"
      
      Followup patch due to PB2 test error
            
      SELECT from function y() produces an error on most platforms, but 
      not all. The test added for this bug therefore fails on some 
      platforms in PB2. This patch disables warnings in 
      backup_functions.test when selecting from function y().
     @ mysql-test/suite/backup/r/backup_functions.result
        Disable warnings when selecting from function y() since this produces a warning only on some platforms.
     @ mysql-test/suite/backup/t/backup_functions.test
        Disable warnings when selecting from function y() since this produces a warning only on some platforms.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090707140519-svplog8kcfejbzbe) (version source revid:jorgen.loland@sun.com-20090626073220-1h8umraypenp1zmi) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090707140519-svplog8kcfejbzbe) (version source revid:jorgen.loland@sun.com-20090626073220-1h8umraypenp1zmi) (merge vers: 5.4.4-alpha) (pib:11)

Not in any released version. No changelog entry needed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83996

2871 Hema Sridharan	2009-09-21
      BUG#45249 (backup_objects_dependency test is non determinstic
      The new patch will remove the wait_condition and sleep
      from the test. It also removes all the dependencies of 
      events  and check for events execution.
      modified:
        mysql-test/suite/backup/r/backup_objects_dependency.result
        mysql-test/suite/backup/t/backup_objects_dependency.test

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091025133616-ca4inerav4vpdnaz) (version source revid:ingo.struewing@sun.com-20090928125502-9t9uqhzsp87vmgnx) (merge vers: 6.0.14-alpha) (pib:13)

Noted in 6.0.14 changelog.

BACKUP DATABASE failed if a database contained a stored function with
the same name as a built-in function.