Bug #24418 GRANT statement causes server crash when specific table is specified
Submitted: 18 Nov 2006 22:30 Modified: 19 Feb 2007 14:59
Reporter: Brian Moureaux Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.26 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[18 Nov 2006 22:30] Brian Moureaux
When granting any privileges on specific tables of databases, the server crashes. However, granting of privileges to all tables (.*) of a database works fine.
This occurs with any databse and any user on any host.

I'm running Gentoo Linux with berkdb, extraengine, ssl, and perl support, if that matters.

How to repeat:
[Noted in description]
[20 Nov 2006 15:38] Valeriy Kravchuk
Thank you for a problem report. Please, send exact SQL statements that I should use to reproduce the crash each and every time from mysql command line client.
[20 Nov 2006 22:24] Brian Moureaux
This is easily reproduced by issuing commands such as:

GRANT SELECT ON mydatabase.mytable TO 'someuser'@'somehost';


GRANT INSERT ON someotherdb.thetable TO 'user'@'anotherhost';

However, something like:

GRANT SELECT ON someotherdb.* TO 'someuser'@'somehost';

will not crash the server because no specific table was specified.

I originally encountered this problem while setting up phpmyadmin's database/tables. Importing the commands from a file led to a server crash (it used to work just fine with a 4.x server). I typed the commands manually at the prompt via the mysql console client and the server crashed again on the GRANT statements. I went further from there to see what I could rule out and I ended up finding that users and hosts were not the problem. I also found that which specific privileges were being granted didn't matter nor did the database. The only thing which made any difference was whether or not I was granting permissions on all tables of a database (using the wildcard - *) or just a specific one.

Hopefully this clarifies things a bit.
[21 Nov 2006 10:27] Valeriy Kravchuk
As you mentioned version 4.x, I have additional question. Have you upgrade to 5.0.26 from version 4.x? How you did it? Have you dumped and restored all the databases?

I am not able to repeat crashes on "clean" 5.0.x:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.32-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables like 't%';
| Tables_in_test (t%)  |
| t                    |
| t1                   |
| t_2                  |
| tb                   |
| ti1                  |
| ti2                  |
| ti3                  |
| ti4                  |
| ti5                  |
| tracking_noid_myisam |
10 rows in set (0.01 sec)

mysql> GRANT SELECT ON test.t1 TO 'user1'@'';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT INSERT ON test.t1 TO 'user1'@'suse';
Query OK, 0 rows affected (0.00 sec)

hence the questions.
[21 Nov 2006 17:47] Brian Moureaux
Yes, this was an upgrade from version 4.x which was handled by Gentoo's Portage package management system. The problems were first noticed with operations on tables in the mysql database itself but only when a specific table was specified and not when all tables were targeted. I then tested it on another database which  contained a single table (this database was clean as far as 5.0.26 was concerned and was being actively used for INSERT, SELECT, etc). GRANT led to a server crash again on that database when I used it to research this bug - but only when the table was explicitly specified; it was fine with the wildcard (bills.* versus bills.bill_items).
[22 Nov 2006 8:10] Valeriy Kravchuk
Please, try to repeat on clean installation (without tables in mysql database left from 4.x.y) of 5.0.26 (or 5.0.27), MySQL binaries (not Gentoo), and inform about the results.
[23 Nov 2006 2:35] Brian Moureaux
Ok. I removed my install of mysql (I had downgraded for the time being) and tried messing around with getting 5.0.27 running, gave up and decided to move my old /var/lib/mysql/ out of the way, installed a fresh version of 5.0.26 (from Gentoo) and went from there restoring other databases from a dump I had on file from yesterday. The crash no longer occurs which makes me believe that 5.x doesn't seem to handle an old version of the mysql database well (privileges, etc) but that's just a logical guess.

So now that takes us to the question of why does the server crash - obviously something in the code somewhere which doesn't handle this condition properly. What part of it? I don't know.

Best upgrading practices here would include doing a dump on --all-databases and then importing everything again that way after the upgrade (which is best so that those binlog things catch it all too).

Bottom line is that something isn't being handled correctly in the server code, but having a clean mysql database after a major upgrade is a good idea in any event.
[23 Nov 2006 16:02] Valeriy Kravchuk
Have you (or portage system) run mysql_upgrade script, as required in the manual when you upgrade from 4.x.y? Please, check. If not, it is surely NOT a MySQL bug, as this step is clearly documented now (http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html).
[23 Nov 2006 17:46] Brian Moureaux
Well with that aside, I felt that there might have been some changes in the columns of the mysql database tables affecting permissions. I'd have to contact Gentoo to find out anything specific to the distribution.

However, whenever there is a reproducable crash in a program and there are no other hardware/power/etc issues affecting the mix (processing environment is stable), then there is a bug in said program as a crash should never happen because of reasons including a piece of software choking on data input/processing. This indicates an area of further needed development in the project or of a single module at least.

Yes, I'm sure that utility does correct some, if not all, of my problems. Yes, there is a possibility that my distribution has overlooked an upgrade detail in some form or another (perhaps it was intended not to be run automatically so as to not alter the mysql database until the administrator had read documentation relating to a proper upgrade of his server which includes making a backup of his databases before the upgrade).

The question here is: Should the server _crash_ when it runs into a case when it cannot properly alter its records pertaining to accounts and permissions (due to whatever reason)?

I'll take the blame for a slightly botched upgrade job but the project should acknowledge a _potential_ flaw in a module somewhere. Deal?
[24 Nov 2006 8:09] Valeriy Kravchuk
I agree with you. Crash is a bug anyway. Please, try to figure out the exact steps to repeat it and(!), please, better use MySQL binaries for that test case, not Gentoo portages, as we do not have control on what and how is done there.
[25 Nov 2006 2:23] Brian Moureaux
I switched my clean /var/lib/mysql out with a dirty backup copy of the directory. I ran mysqld through gdb with the same options as a normal run. This is what I came up with:


mysql> GRANT SELECT ON bills.bill_items TO 'statux'@'localhost';

(in gdb):

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread -1276916832 (LWP 11099)]
0x0844ad4b in list_walk ()
(gdb) bt
#0  0x0844ad4b in list_walk ()
#1  0x0844b283 in my_hash_insert ()
#2  0x08209199 in mysql_table_grant ()
#3  0x08194b8c in mysql_execute_command ()
#4  0x08197713 in mysql_parse ()
#5  0x08198536 in dispatch_command ()
#6  0x08198eb2 in do_command ()
#7  0x08199914 in handle_one_connection ()
#8  0xb7e00512 in start_thread () from /lib/libpthread.so.0
#9  0xb7c5ffce in clone () from /lib/libc.so.6

0844acfb <list_walk>:
 844acfb:       55                      push   %ebp
 844acfc:       89 e5                   mov    %esp,%ebp
 844acfe:       57                      push   %edi
 844acff:       56                      push   %esi
 844ad00:       53                      push   %ebx
 844ad01:       83 ec 0c                sub    $0xc,%esp
 844ad04:       8b 5d 08                mov    0x8(%ebp),%ebx
 844ad07:       8b 7d 0c                mov    0xc(%ebp),%edi
 844ad0a:       8b 75 10                mov    0x10(%ebp),%esi
 844ad0d:       eb 13                   jmp    844ad22 <list_walk+0x27>
 844ad0f:       89 74 24 04             mov    %esi,0x4(%esp)
 844ad13:       8b 43 08                mov    0x8(%ebx),%eax
 844ad16:       89 04 24                mov    %eax,(%esp)
 844ad19:       ff d7                   call   *%edi
 844ad1b:       85 c0                   test   %eax,%eax
 844ad1d:       75 09                   jne    844ad28 <list_walk+0x2d>
 844ad1f:       8b 5b 04                mov    0x4(%ebx),%ebx
 844ad22:       85 db                   test   %ebx,%ebx
 844ad24:       75 e9                   jne    844ad0f <list_walk+0x14>
 844ad26:       31 c0                   xor    %eax,%eax
 844ad28:       83 c4 0c                add    $0xc,%esp
 844ad2b:       5b                      pop    %ebx
 844ad2c:       5e                      pop    %esi
 844ad2d:       5f                      pop    %edi
 844ad2e:       5d                      pop    %ebp
 844ad2f:       c3                      ret
 844ad30:       55                      push   %ebp
 844ad31:       89 e5                   mov    %esp,%ebp
 844ad33:       53                      push   %ebx
 844ad34:       83 ec 24                sub    $0x24,%esp
 844ad37:       c7 45 f4 01 00 00 00    movl   $0x1,0xfffffff4(%ebp)
 844ad3e:       c7 45 f8 04 00 00 00    movl   $0x4,0xfffffff8(%ebp)
 844ad45:       8b 48 30                mov    0x30(%eax),%ecx
 844ad48:       8d 45 f8                lea    0xfffffff8(%ebp),%eax

 844ad4b:       8b 59 68                mov    0x68(%ecx),%ebx

 844ad4e:       89 44 24 10             mov    %eax,0x10(%esp)
 844ad52:       8d 45 f4                lea    0xfffffff4(%ebp),%eax
 844ad55:       89 44 24 0c             mov    %eax,0xc(%esp)
 844ad59:       8b 45 08                mov    0x8(%ebp),%eax
 844ad5c:       89 54 24 04             mov    %edx,0x4(%esp)
 844ad60:       89 44 24 08             mov    %eax,0x8(%esp)
 844ad64:       89 0c 24                mov    %ecx,(%esp)
 844ad67:       ff 53 24                call   *0x24(%ebx)
 844ad6a:       8b 45 f4                mov    0xfffffff4(%ebp),%eax
 844ad6d:       83 c4 24                add    $0x24,%esp
 844ad70:       5b                      pop    %ebx
 844ad71:       5d                      pop    %ebp
 844ad72:       c3                      ret

Provided that there is only one list_walk() function in the mix:

int list_walk(LIST *list, list_walk_action action, gptr argument)
  int error=0;
  while (list)
    if ((error = (*action)(list->data,argument)))
      return error;
  return 0;

But after working all day at my retail job, my eyes are tired and I can't easily translate the one to the other. My apologies.

I crashed the server twice and found that it landed on the same instruction both times. I could probably donate my dirty database which is definitely known, by this point, to crash at least 5.0.26 (btw, the patchset which Gentoo uses doesn't touch anything relating to this side of the puzzle).

I apologise if I've violated any rules about not posting traces, dumps and code snippets but I feel that it may be useful to someone.
[25 Nov 2006 8:48] Valeriy Kravchuk
Please, upload your "dirty database" (either as file to the reports, or to our FTP server, with 24418 in a file name somewhere), and inform when you'll do it.
[28 Nov 2006 4:02] Brian Moureaux
I uploaded a tarball of my dirty /var/lib/mysql/mysql directory which, obviously, caused the crashes.
[19 Feb 2007 14:59] Valeriy Kravchuk
I was not able to repeat the behaviour described even with your mysql database content on latest 5.0.36-BK after I did the following (as recommended in the manual, http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html):

1. Just after copying your mysql database (from 4.1.x) to 5.0.36 data directory, I ran mysql_upgrade. It informed, among other things, about mysql.uaer table that needed REPAIR.

2. I started mysql_safe with --skip-grant option.

3. I conected as root and executed REPAIR TABLE mysql.user;

4. I reset root's password to empty as described in http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html (with UPDATE + FLUSH PRIVILEGES;

5. I had created database and table in it for your GRANT.

6. I stopped server with mysqladmin and started it as usual. Then:

openxs@suse:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT SELECT ON bills.bill_items TO 'statux'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> select host, user, password from mysql.user;
| host      | user    | password                                  |
| localhost | root    |                                           |
| statux    | root    |                                           |
| localhost |         |                                           |
| statux    |         |                                           |
| localhost | statux  | 50e92799731d0587                          |
| localhost | webuser | *3875CB0BDA3FAF092F2DEB9908F42D3FADDD9384 |
| localhost | pma     | *09DEFEAFFCEEE0A8CFFFB65222A6D4A68A402BF0 |
| localhost | amarok  | *7EB6FE281607DDD2EE77187949ABE3E7D311BCF8 |
8 rows in set (0.01 sec)

So, no crash in current version if you follow upgrade procedures.
[9 Nov 2007 12:36] Kristofer Pettersson
See also Bug#16470 crash on grant if old grant tables